Quick tip: Using WebAssembly to implement Pearson Product-Moment Correlation in SingleStoreDB
Tags: singlestoredb, webassembly, correlation, rust
Published: 2023-02-22 16:46:30

## Abstract
In this short article, we'll use WebAssembly to extend SingleStoreDB with [Pearson Product-Moment Correlation](https://en.wikipedia.org/wiki/Pearson_correlation_coefficient).

## Introduction
Continuing our short series of articles on statistical computations, we'll focus on another example and see how we can utilise WebAssembly with SingleStoreDB.

## Create a SingleStoreDB Cloud account
A [previous article](https://dev.to/singlestore/quick-tip-using-dbt-with-singlestoredb-161g) showed the steps required to create a free SingleStoreDB Cloud account. We'll use **Stats Demo Group** as our Workspace Group Name and **stats-demo** as our Workspace Name.

Once we've created our database in the following steps, we'll make a note of our **password** and **host** name.

## Create a Database
In our SingleStoreDB Cloud account, we'll use the **SQL Editor** to create a new database, as follows:

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

## Setup local Wasm development environment
We'll follow the steps described in the [previous article](https://dev.to/singlestore/turbocharge-your-application-development-using-webassembly-with-singlestoredb-1oco) to quickly create a local Wasm development environment. We'll also install and use the `pushwasm` tool.

Next, let's clone the following GitHub repo:

```shell
git clone https://github.com/singlestore-labs/singlestoredb-statistics
```

### Compile
We'll now change to the `singlestoredb-statistics/correlation` directory and build the code, as follows:

```shell
cd singlestoredb-statistics/correlation

cargo build --target wasm32-wasi --release
```

### Deploy
Once the code is built, we'll create an environment variable:

```shell
export SINGLESTOREDB_CONNSTRING="mysql://admin:<password>@<host>:3306/test"
```

We'll replace the `<password>` and `<host>` with the values from our SingleStoreDB Cloud account.

Next, we'll use `pushwasm` to load the Wasm modules into SingleStoreDB, one-by-one:

```shell
pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./correlation.wit ./target/wasm32-wasi/release/correlation.wasm corr2_init

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./correlation.wit ./target/wasm32-wasi/release/correlation.wasm corr2_iter

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./correlation.wit ./target/wasm32-wasi/release/correlation.wasm corr2_merge

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./correlation.wit ./target/wasm32-wasi/release/correlation.wasm corr2_term

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./correlation.wit ./target/wasm32-wasi/release/correlation.wasm corr2_termd

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./correlation.wit ./target/wasm32-wasi/release/correlation.wasm corrmat_init

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./correlation.wit ./target/wasm32-wasi/release/correlation.wasm corrmat_iter

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./correlation.wit ./target/wasm32-wasi/release/correlation.wasm corrmat_merge

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./correlation.wit ./target/wasm32-wasi/release/correlation.wasm corrmat_term

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./correlation.wit ./target/wasm32-wasi/release/correlation.wasm vec_pack_f64

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./correlation.wit ./target/wasm32-wasi/release/correlation.wasm vec_unpack_f64
```

The output should be as follows:

```
Wasm UDF 'corr2_init' was created successfully.

Wasm UDF 'corr2_iter' was created successfully.

Wasm UDF 'corr2_merge' was created successfully.

Wasm UDF 'corr2_term' was created successfully.

Wasm UDF 'corr2_termd' was created successfully.

Wasm UDF 'corrmat_init' was created successfully.

Wasm UDF 'corrmat_iter' was created successfully.

Wasm UDF 'corrmat_merge' was created successfully.

Wasm UDF 'corrmat_term' was created successfully.

Wasm UDF 'vec_pack_f64' was created successfully.

Wasm UDF 'vec_unpack_f64' was created successfully.
```

### Load and run SQL
We'll use a MySQL CLI client to connect to SingleStoreDB:

```shell
mysql --local-infile -u admin -h <host> -P 3306 --default-auth=mysql_native_password -p
```

We'll replace the `<host>` with the value from our SingleStoreDB Cloud account.

Once connected, we'll switch to the `test` database:

```sql
USE test;
```

We'll then execute the SQL statements from the `correlation.sql` file, as follows:

```sql
SOURCE correlation.sql
```

This will create and load data into the `iris` table, and create some additional functions.

We can quickly check the table:

```sql
SELECT * FROM iris LIMIT 5;
```

The result should be similar to the following:

```
+--------------+-------------+--------------+-------------+---------+
| sepal_length | sepal_width | petal_length | petal_width | species |
+--------------+-------------+--------------+-------------+---------+
|          4.7 |         3.2 |          1.3 |         0.2 | setosa  |
|            5 |         3.4 |          1.5 |         0.2 | setosa  |
|          5.4 |         3.9 |          1.3 |         0.4 | setosa  |
|          5.4 |         3.4 |          1.7 |         0.2 | setosa  |
|            5 |           3 |          1.6 |         0.2 | setosa  |
+--------------+-------------+--------------+-------------+---------+
```

There are two aggregate functions:

1. **corr2d():** Returns the correlation coefficient between two variables
2. **corr2():** Returns more details of the relationship between two variables, including linear regression

### Run Wasm in the database
We can test the Wasm functions with some examples from the GitHub repo.

- Compute the correlation between `sepal_width` and `sepal_length`.

```sql
SELECT species, corr2d(sepal_width, sepal_length)
FROM iris
GROUP BY species;
```

The result should be similar to the following:

```
+------------+-----------------------------------+
| species    | corr2d(sepal_width, sepal_length) |
+------------+-----------------------------------+
| virginica  |                0.4572278163941236 |
| versicolor |                 0.525910717282782 |
| setosa     |                0.7467803732639016 |
+------------+-----------------------------------+
```

- Compute the correlation between `sepal_width` and `sepal_length` and return the results as JSON.

```sql
SELECT species, corr2(sepal_width, sepal_length)
FROM iris
GROUP BY species;
```

The result should be similar to the following:

```
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| virginica  | {"b0":3.9068364663867405,"b1":0.9015344766688834,"corr":0.4572278163941236,"n":50,"nmiss":0,"r2":0.2090572760845384,"sse":15.670790000392019,"x_avg":2.9739999999999993,"y_avg":6.587999999999999} |
| versicolor | {"b0":3.53973471502592,"b1":0.8650777202072483,"corr":0.525910717282782,"n":50,"nmiss":0,"r2":0.2765820825528903,"sse":9.444365595856668,"x_avg":2.7700000000000005,"y_avg":5.935999999999998}     |
| setosa     | {"b0":2.6446596755601486,"b1":0.6908543956816411,"corr":0.7467803732639016,"n":50,"nmiss":0,"r2":0.5576809258921721,"sse":2.6929269869833408,"x_avg":3.418000000000002,"y_avg":5.005999999999999}  |
+------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```

- Compute the matrix of correlation coefficients between `sepal_length`, `sepal_width`, and `petal_width`.

```sql
SELECT corrmat(vec_pack_f64([sepal_length, sepal_width, petal_width]))
FROM iris;
```

The result should be similar to the following:

```
+-----------------------------------------------------------------------+
| corrmat(vec_pack_f64([sepal_length, sepal_width, petal_width]))       |
+-----------------------------------------------------------------------+
| [1,-0.10936924995068921,1,0.81795363336916715,-0.35654408961381734,1] |
+-----------------------------------------------------------------------+
```

## Summary
In this short article, we have seen how we can extend SingleStoreDB with Wasm to provide support for statistical correlation.

## Acknowledgements
I thank [Oliver Schabenberger](https://www.linkedin.com/in/oschabenberger/) for his work on the Wasm modules and the code examples and documentation in the [GitHub repo](https://github.com/singlestore-labs/singlestoredb-statistics).