Quick tip: Cosine Similarity revisited in SingleStore
Tags: singlestoredb, cosinesimilarity, vectordatabase
Published: 2024-03-31 02:14:06

## Abstract
In a [previous article](https://dev.to/singlestore/quick-tip-using-the-new-vector-data-type-and-infix-operators-in-singlestoredb-4j08), we saw how to represent Cosine Similarity in SingleStore by combining the `DOT_PRODUCT` and `SQRT` functions. The SingleStore documentation also provides a way to implement a `COSINE_SIMILARITY` function. In this article, we'll see how.

## 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 Database and Table
In our SingleStore Cloud account, let's use the **SQL Editor** to create a new database. Call this `iris_db`, as follows:

```sql
CREATE DATABASE IF NOT EXISTS iris_db;
```

We'll also create the `iris` table using the new `VECTOR` data type, as follows:

```sql
USE iris_db;

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

The Iris data set contains 150 rows of data for three different species of flowers. Each flower has four columns of data: `sepal_length`, `sepal_width`, `petal_length` and `petal_width`. We can store these four column values together, as follows:

```sql
INSERT INTO iris VALUES
('[5.1,3.5,1.4,0.2]','Iris-setosa'),
('[4.9,3,1.4,0.2]','Iris-setosa'),
('[4.7,3.2,1.3,0.2]','Iris-setosa'),
('[4.6,3.1,1.5,0.2]','Iris-setosa'),
('[5,3.6,1.4,0.2]','Iris-setosa'),
...
('[6.7,3,5.2,2.3]','Iris-virginica'),
('[6.3,2.5,5,1.9]','Iris-virginica'),
('[6.5,3,5.2,2]','Iris-virginica'),
('[6.2,3.4,5.4,2.3]','Iris-virginica'),
('[5.9,3,5.1,1.8]','Iris-virginica');
```

Only the first five and last five rows are shown above. The complete `INSERT` code listing is available in a [GitHub Gist](https://gist.github.com/VeryFatBoy/f17e2fabdcf8618c87aec355f603b1d3).

## Create a Cosine Similarity Function
From the [product documentation](https://docs.singlestore.com/cloud/reference/sql-reference/vector-functions/cosine-similarity-and-cosine-distance/), we'll now define two functions that we can use:

```sql
DELIMITER //
CREATE OR REPLACE FUNCTION NORMALIZE(v VECTOR(4)) RETURNS VECTOR(4) AS
DECLARE
    squares VECTOR(4) = VECTOR_MUL(v, v);
    length FLOAT = SQRT(VECTOR_ELEMENTS_SUM(squares));
BEGIN
    RETURN SCALAR_VECTOR_MUL(1/length, v);
END //
DELIMITER ;
```

and

```sql
DELIMITER //
CREATE OR REPLACE FUNCTION COSINE_SIMILARITY(v1 VECTOR(4), v2 VECTOR(4)) RETURNS FLOAT AS
BEGIN
    RETURN DOT_PRODUCT(NORMALIZE(v1), NORMALIZE(v2));
END //
DELIMITER ;
```

In the SingleStore notebook environment, the code would be as follows:

```sql
CREATE OR REPLACE FUNCTION NORMALIZE(v VECTOR(4)) RETURNS VECTOR(4) AS
DECLARE
    squares VECTOR(4) = VECTOR_MUL(v, v);
    length FLOAT = SQRT(VECTOR_ELEMENTS_SUM(squares));
BEGIN
    RETURN SCALAR_VECTOR_MUL(1/length, v);
END;
```

and

```sql
CREATE OR REPLACE FUNCTION COSINE_SIMILARITY(v1 VECTOR(4), v2 VECTOR(4)) RETURNS FLOAT AS
BEGIN
    RETURN DOT_PRODUCT(NORMALIZE(v1), NORMALIZE(v2));
END;
```

In other words, remove the references to `DELIMITER` and `//`.

## Query 1
First, let's try a query where we want to find the name of the flower species using an exact match for the `sepal_length`, `sepal_width`, `petal_length` and `petal_width`. We'll use the values `[5.9,3,5.1,1.8]` from the last row of the `iris` table, shown above.

```sql
SELECT species
FROM iris
ORDER BY COSINE_SIMILARITY(vector, '[5.9,3,5.1,1.8]') DESC
LIMIT 1;
```

The result should be:

```
+----------------+
| species        |
+----------------+
| Iris-virginica |
+----------------+
```

## Query 2
Now, let's use some fictitious data values `[5.2,3.6,1.5,0.3]` to make a prediction.

```sql
SELECT species
FROM iris
ORDER BY COSINE_SIMILARITY(vector, '[5.2,3.6,1.5,0.3]') DESC
LIMIT 1;
```

The result should be:

```
+-------------+
| species     |
+-------------+
| Iris-setosa |
+-------------+
```

## Summary
SingleStore provides direct support for the `DOT_PRODUCT` and `EUCLIDEAN_DISTANCE` functions. We can easily implement our own `COSINE_SIMILARITY` function as shown in this example.