Quick tip: Numeromancy, WebAssembly and SingleStoreDB Cloud
Tags: singlestoredb, webassembly, numeromancy, rust
Published: 2023-02-24 19:35:36

## Abstract
Numeromancy is a SingleStore Labs GitHub repo originally available for use with Visual Studio Code and the SingleStoreDB Dev Docker Image. In this short article, we'll see that it works equally as well with SingleStoreDB Cloud.

## Introduction
In a [previous article](https://dev.to/singlestore/quick-tip-test-drive-singlestoredb-using-the-query-playground-mkl), we have seen that there are multiple ways that we can test drive SingleStoreDB. Signing up for the free [trial account](https://www.singlestore.com/cloud-trial/) is a great way to test SingleStoreDB Cloud. In this short article, we'll take an existing SingleStore Labs project and demonstrate the ease with which it can be deployed and run on SingleStoreDB Cloud.

## Numeromancy
> The term numeromancy comes from Latin numerō (numerus, “number”) +‎ -mancy from Ancient Greek μαντεία (manteía, “divination”, “prophecy”, “fortune-telling”).
— https://numeromancy.com/

A [SingleStore Labs GitHub repo](https://github.com/singlestore-labs/numeromancy) is designed to optimize a problem for data stored in SingleStoreDB. In this project, WebAssembly is also used to extend SingleStoreDB with several operations for this optimization task. An example use-case is also provided for Cancer Remission.

## 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 **Numeromancy Demo Group** as our Workspace Group Name and **numeromancy-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 numeromancy;
```

## 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/numeromancy
```

### Edit
We need to make two small edits before we can build the code.

First, we'll change to the `numeromancy/driver` directory and make an edit to `Cargo.toml`, replacing the line:

```
mysql = "22.2.0"
```

with the following:

```
mysql = "23.0.1"
```

Second, we'll change to the `numeromancy/problem` directory and make an edit to `Cargo.toml`, replacing the line:

```
wit-bindgen-rust = { git = "https://github.com/bytecodealliance/wit-bindgen.git" }
```

with the following:

```
wit-bindgen-rust = { git = "https://github.com/bytecodealliance/wit-bindgen.git", rev = "60e3c5b41e616fee239304d92128e117dd9be0a7" }
```

### Compile
Whilst still in the `numeromancy/problem` directory we'll build the code, as follows:

```shell
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/numeromancy"
```

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

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

```shell
pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./problem/interface.wit ./target/wasm32-wasi/release/numeromancy_problem.wasm log_regression_infer

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./problem/interface.wit ./target/wasm32-wasi/release/numeromancy_problem.wasm log_regression_cost

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./problem/interface.wit ./target/wasm32-wasi/release/numeromancy_problem.wasm log_regression_gradient

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./problem/interface.wit ./target/wasm32-wasi/release/numeromancy_problem.wasm log_regression_hessian

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./problem/interface.wit ./target/wasm32-wasi/release/numeromancy_problem.wasm vec_pack_f64

pushwasm --force $SINGLESTOREDB_CONNSTRING --wit ./problem/interface.wit ./target/wasm32-wasi/release/numeromancy_problem.wasm vec_unpack_f64
```

All the Wasm UDFs should be successfully created.

### Load and run SQL
To run the example, we'll need to change to the `numeromancy/example/cancer_remission` directory and modify `config.toml`, as follows:

```
[database]
host = "<host>"
port = 3306
user = "admin"
password = "<password>"
database = "cancer_remission"
```

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

Whilst in the same directory, we'll run the commands in the `setup.sql` file to create the `cancer_remission` database, table, functions and load the data from the `training.csv` file:

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

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

### Run Wasm in the database
Next, we'll train an inference engine:

```shell
cargo run --release -p numeromancy-driver
```

**NOTE:** We may also need to run the following, required by OpenBLAS, to ensure a successful run of the above command:

```shell
sudo apt install gfortran
```

The result should be similar to the following:

```
ArgminResult:
    param (best):  [58.0382598807061, 24.661290504279627, 19.29324847057158, -19.600904904829974, 3.895979130291112, 0.1510635568225436, -87.43343650294993], shape=[7], strides=[1], layout=CFcf (0xf), const ndim=1
    cost (best):   10.875326142858473
    iters (best):  43
    iters (total): 44
    termination: No change in cost function value
    time:        Some(3.396346349s)
```

From the GitHub repo, there are several queries that we can run using the **SQL Editor** in our SingleStoreDB Cloud account. For example, returning the Confusion Matrix:

```sql
USE cancer_remmission;

SELECT * FROM cancer_remission_confusion();
```

The result should be similar to the following:

```
+---------------+----------------+----------------+---------------+
| true_positive | false_negative | false_positive | true_negative |
+---------------+----------------+----------------+---------------+
|             5 |              4 |              3 |            15 |
+---------------+----------------+----------------+---------------+
```

and the Generated Inference Method:

```sql
SELECT cell, smear, infil, li, blast, temp,
    cancer_remission_infer([1, cell, smear, infil, li, blast, temp]) AS predicted_remission
FROM cancer_remission;
```

The result should be similar to the following:

```
+------+-------+-------+------+-------+-------+--------------------------+
| cell | smear | infil | li   | blast | temp  | predicted_remission      |
+------+-------+-------+------+-------+-------+--------------------------+
|    1 |  0.87 |  0.87 |  0.7 | 1.053 | 0.986 |       0.2908573450870292 |
|  0.9 |  0.75 |  0.68 |  1.3 | 0.519 |  0.98 |       0.6970204491139145 |
| 0.95 |  0.97 |  0.92 |    1 |  1.23 | 0.992 |      0.37658295782152706 |
|  0.5 |  0.44 |  0.22 |  0.6 | 0.114 |  0.99 |    0.0000638724834693532 |
|    1 |  0.63 |  0.63 |  1.1 | 1.072 | 0.986 |       0.6778427646783881 |
|    1 |  0.58 |  0.58 |    1 | 0.531 | 1.002 |      0.24768453374925065 |
| 0.95 |  0.87 |  0.83 |  1.9 | 1.354 |  1.02 |       0.6005257295293988 |
|    1 |  0.45 |  0.45 |  0.8 | 0.322 | 0.999 |      0.16527765831402835 |
| 0.95 |  0.36 |  0.34 |  0.5 |     0 | 1.038 |    0.0008578955974432781 |
| 0.85 |  0.39 |  0.33 |  0.7 | 0.279 | 0.988 |     0.027693373668109336 |
|    1 |   0.9 |   0.9 |  1.1 | 1.037 |  0.99 |       0.5758566835834648 |
|    1 |  0.84 |  0.84 |  1.9 | 2.064 |  1.02 |       0.7257507508234508 |
|    1 |  0.75 |  0.75 |    1 | 1.322 | 1.004 |      0.22816935123559853 |
|    1 |  0.33 |  0.33 |  0.4 | 0.176 |  1.01 |     0.015910019504605355 |
| 0.95 |  0.32 |   0.3 |  1.6 | 0.886 | 0.988 |       0.8510965609291663 |
|    1 |  0.69 |  0.69 |  0.9 | 0.398 | 0.986 |       0.4611791818041166 |
|  0.9 |  0.36 |  0.32 |  1.4 |  0.74 | 0.992 |      0.43509086962553895 |
|  0.8 |  0.88 |   0.7 |  0.8 | 0.176 | 0.982 |      0.15557254876835122 |
|  0.7 |  0.76 |  0.53 |  1.2 | 0.146 | 0.982 |      0.16982726822480884 |
| 0.65 |  0.42 |  0.27 |  0.5 | 0.114 | 1.014 |  0.000054717346377588746 |
|  0.8 |  0.83 |  0.66 |  1.9 |   1.1 | 0.996 |        0.790661732185965 |
|    1 |  0.65 |  0.65 |  0.6 | 0.519 | 0.982 |      0.28007865692320666 |
|  0.8 |  0.46 |  0.37 |  0.4 |  0.38 | 1.006 |    0.0009554334533287413 |
|  0.2 |  0.39 |  0.08 |  0.8 | 0.114 |  0.99 | 0.0000005052130574394507 |
|  0.9 |  0.93 |  0.84 |  0.6 | 1.591 |  1.02 |     0.007444604052869717 |
|    1 |   0.6 |   0.6 |  1.7 | 0.964 |  0.99 |       0.9384652733591272 |
|    1 |  0.73 |  0.73 |  0.7 | 0.398 | 0.986 |      0.27946870781343947 |
+------+-------+-------+------+-------+-------+--------------------------+
```

## Summary
In this short article, we have successfully configured our environment and run the Cancer Remission example on SingleStoreDB Cloud.

## Acknowledgements
I thank [Carl Sverre](https://www.linkedin.com/in/carlsverre/) for developing the Numeromancy example and documentation in the [GitHub repo](https://github.com/singlestore-labs/numeromancy).