Quick tip: Write numpy arrays directly to the SingleStore VECTOR data type
Tags: singlestoredb, vectordatabase, numpy
Published: 2024-05-01 09:47:39

## Abstract
The new SingleStore `VECTOR` data type was introduced in early 2024. It provides a number of benefits over using the `BLOB` type when working with vector data. Now, numpy arrays can be directly stored in a table with a `VECTOR` column. In this article, we'll see how.

The notebook file used in this article is available on [GitHub](https://github.com/VeryFatBoy/numpy-vector-demo/blob/main/numpy_vector_demo.ipynb).

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/singlestore/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps to create a free SingleStoreDB Cloud account. We'll use the following settings:

- **Workspace Group Name:** Iris Demo Group
- **Cloud Provider:** AWS
- **Region:** US East 1 (N. Virginia)
- **Workspace Name:** iris-demo
- **Size:** S-00

## Create a new notebook
From the left navigation pane in the cloud portal, we'll select **Develop > Notebooks**.

In the top right of the web page, we'll select **New Notebook > New Notebook**, as shown in Figure 1.

![Figure 1. New Notebook.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h8kybcs4swwqhzb2xn4u.png)
<center>Figure 1. New Notebook.</center>

We'll call the notebook **numpy_vector_demo**, select a **Blank** notebook template from the available options, and save it in the **Personal** location.

## Fill out the notebook
First, we'll import some libraries:

```python
import pandas as pd
import numpy as np
```

Next, we'll load the `iris` dataset:

```python
url = "https://gist.githubusercontent.com/VeryFatBoy/9af771d443f5ec4dd6eec8d69a062638/raw/c03ef25a97f23a48ee408ac02114195b663a2364/iris.csv"

df = pd.read_csv(url)
```

and print the first few rows:

```python
df.head()
```

Example output:

```
   sepal_length  sepal_width  petal_length  petal_width      species
0           5.1          3.5           1.4          0.2  Iris-setosa
1           4.9          3.0           1.4          0.2  Iris-setosa
2           4.7          3.2           1.3          0.2  Iris-setosa
3           4.6          3.1           1.5          0.2  Iris-setosa
4           5.0          3.6           1.4          0.2  Iris-setosa
```

We'll now create a copy of the original DataFrame:

```python
str_df = df.copy()
```

and then combine the first four columns into a new vector column in a string format and then drop the first four columns, as follows:

```python
str_df["vector"] = str_df.apply(lambda row: f"[{row['sepal_length']}, {row['sepal_width']}, {row['petal_length']}, {row['petal_width']}]", axis = 1)

str_df.drop(columns = ["sepal_length", "sepal_width", "petal_length", "petal_width"], inplace = True)
```

We now have two columns in our DataFrame.

We can also represent the data using numpy arrays instead of strings.

First, we'll create another copy of our original DataFrame:

```python
num_df = df.copy()
```

and then combine the first four columns into a new vector column in a numpy 32-bit format and then drop the first four columns, as follows:

```python
num_df["vector"] = num_df[["sepal_length", "sepal_width", "petal_length", "petal_width"]].apply(lambda row: np.array(row).astype(np.float32), axis = 1)

num_df.drop(columns = ["sepal_length", "sepal_width", "petal_length", "petal_width"], inplace = True)
```

We'll now create our database and two tables, as follows:

```sql
DROP DATABASE IF EXISTS iris_demo;
CREATE DATABASE IF NOT EXISTS iris_demo;

USE iris_demo;

DROP TABLE IF EXISTS iris_str;
CREATE TABLE IF NOT EXISTS iris_str (
     species VARCHAR(20),
     vector VECTOR(4) NOT NULL
);

DROP TABLE IF EXISTS iris_num;
CREATE TABLE IF NOT EXISTS iris_num (
     species VARCHAR(20),
     vector VECTOR(4) NOT NULL
);
```

We have one table where we will store the vectors using the string format and another table where we will store the vectors using the numpy format.

We'll now create the connection to SingleStore:

```python
from sqlalchemy import *

db_connection = create_engine(connection_url)
```

and write the first DataFrame using the string format:

```python
str_df.to_sql(
    "iris_str",
    con = db_connection,
    if_exists = "append",
    index = False,
    chunksize = 1000
)
```

and then the second DataFrame using the numpy format:

```python
num_df.to_sql(
    "iris_num",
    con = db_connection,
    if_exists = "append",
    index = False,
    chunksize = 1000
)
```

Using `"append"` ensures that the table structures we previously defined are preserved.

Both methods enable us to write the original dataset into SingleStore. With support for writing vector data in multiple formats, we can choose the best approach for our use case.

## Summary
Besides the existing support for writing vectors as strings, numpy arrays can now be directly written to the `VECTOR` data type in SingleStore.