DEV Community

Cover image for Quick tip: Numeromancy, WebAssembly and SingleStoreDB Cloud
Akmal Chaudhri for SingleStore

Posted on • Edited on

Quick tip: Numeromancy, WebAssembly and SingleStoreDB Cloud

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, we have seen that there are multiple ways that we can test drive SingleStoreDB. Signing up for the free trial account 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 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 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:

CREATE DATABASE IF NOT EXISTS numeromancy;
Enter fullscreen mode Exit fullscreen mode

Setup local Wasm development environment

We'll follow the steps described in the previous article 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:

git clone https://github.com/singlestore-labs/numeromancy
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

with the following:

mysql = "23.0.1"
Enter fullscreen mode Exit fullscreen mode

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" }
Enter fullscreen mode Exit fullscreen mode

with the following:

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

Compile

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

cargo build --target wasm32-wasi --release
Enter fullscreen mode Exit fullscreen mode

Deploy

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

export SINGLESTOREDB_CONNSTRING="mysql://admin:<password>@<host>:3306/numeromancy"
Enter fullscreen mode Exit fullscreen mode

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:

pushwasm udf --force --conn $SINGLESTOREDB_CONNSTRING --wit ./problem/interface.wit --wasm ./target/wasm32-wasi/release/numeromancy_problem.wasm --name log_regression_infer

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

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

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

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

pushwasm udf --force --conn $SINGLESTOREDB_CONNSTRING --wit ./problem/interface.wit --wasm ./target/wasm32-wasi/release/numeromancy_problem.wasm --name vec_unpack_f64
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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:

mysql -u admin -h <host> -P 3306 --default-auth=mysql_native_password -p<password> --local-infile < setup.sql
Enter fullscreen mode Exit fullscreen mode

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:

cargo run --release -p numeromancy-driver
Enter fullscreen mode Exit fullscreen mode

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

sudo apt install gfortran
Enter fullscreen mode Exit fullscreen mode

Example output:

ArgminResult:
    param (best):  [58.03825988070382, 24.661290504279044, 19.29324847057098, -19.60090490482931, 3.895979130291091, 0.15106355682250283, -87.43343650294703], shape=[7], strides=[1], layout=CFcf (0xf), const ndim=1
    cost (best):   10.875326142858436
    iters (best):  43
    iters (total): 44
    termination: No change in cost function value
    time:        Some(23.559348753s)
Enter fullscreen mode Exit fullscreen mode

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:

USE cancer_remmission;

SELECT * FROM cancer_remission_confusion();
Enter fullscreen mode Exit fullscreen mode

Example output:

+---------------+----------------+----------------+---------------+
| true_positive | false_negative | false_positive | true_negative |
+---------------+----------------+----------------+---------------+
|             5 |              4 |              3 |            15 |
+---------------+----------------+----------------+---------------+
Enter fullscreen mode Exit fullscreen mode

and the Generated Inference Method:

SELECT cell, smear, infil, li, blast, temp,
    cancer_remission_infer([1, cell, smear, infil, li, blast, temp]) AS predicted_remission
FROM cancer_remission;
Enter fullscreen mode Exit fullscreen mode

Example output:

+------+-------+-------+------+-------+-------+--------------------------+
| cell | smear | infil | li   | blast | temp  | predicted_remission      |
+------+-------+-------+------+-------+-------+--------------------------+
|    1 |  0.63 |  0.63 |  1.1 | 1.072 | 0.986 |       0.6778427646783849 |
| 0.95 |  0.32 |   0.3 |  1.6 | 0.886 | 0.988 |        0.851096560929161 |
|  0.5 |  0.44 |  0.22 |  0.6 | 0.114 |  0.99 |   0.00006387248346936319 |
|    1 |  0.84 |  0.84 |  1.9 | 2.064 |  1.02 |       0.7257507508234592 |
|    1 |  0.73 |  0.73 |  0.7 | 0.398 | 0.986 |      0.27946870781344235 |
|  0.8 |  0.83 |  0.66 |  1.9 |   1.1 | 0.996 |        0.790661732185965 |
|  0.8 |  0.88 |   0.7 |  0.8 | 0.176 | 0.982 |      0.15557254876834747 |
|    1 |  0.87 |  0.87 |  0.7 | 1.053 | 0.986 |       0.2908573450870292 |
| 0.95 |  0.87 |  0.83 |  1.9 | 1.354 |  1.02 |        0.600525729529409 |
|    1 |  0.75 |  0.75 |    1 | 1.322 | 1.004 |      0.22816935123560103 |
|    1 |   0.6 |   0.6 |  1.7 | 0.964 |  0.99 |       0.9384652733591272 |
|    1 |  0.65 |  0.65 |  0.6 | 0.519 | 0.982 |      0.28007865692320666 |
| 0.95 |  0.97 |  0.92 |    1 |  1.23 | 0.992 |      0.37658295782152373 |
|    1 |  0.33 |  0.33 |  0.4 | 0.176 |  1.01 |     0.015910019504606465 |
|  0.9 |  0.36 |  0.32 |  1.4 |  0.74 | 0.992 |       0.4350908696255424 |
|    1 |  0.45 |  0.45 |  0.8 | 0.322 | 0.999 |      0.16527765831403227 |
| 0.95 |  0.36 |  0.34 |  0.5 |     0 | 1.038 |    0.0008578955974434244 |
|  0.7 |  0.76 |  0.53 |  1.2 | 0.146 | 0.982 |      0.16982726822480884 |
|  0.8 |  0.46 |  0.37 |  0.4 |  0.38 | 1.006 |    0.0009554334533288636 |
|    1 |   0.9 |   0.9 |  1.1 | 1.037 |  0.99 |       0.5758566835834682 |
|  0.2 |  0.39 |  0.08 |  0.8 | 0.114 |  0.99 | 0.0000005052130574395872 |
| 0.65 |  0.42 |  0.27 |  0.5 | 0.114 | 1.014 |   0.00005471734637759885 |
|    1 |  0.58 |  0.58 |    1 | 0.531 | 1.002 |      0.24768453374925858 |
|    1 |  0.69 |  0.69 |  0.9 | 0.398 | 0.986 |       0.4611791818041201 |
|  0.9 |  0.75 |  0.68 |  1.3 | 0.519 |  0.98 |       0.6970204491139145 |
| 0.85 |  0.39 |  0.33 |  0.7 | 0.279 | 0.988 |      0.02769337366811048 |
|  0.9 |  0.93 |  0.84 |  0.6 | 1.591 |  1.02 |     0.007444604052870347 |
+------+-------+-------+------+-------+-------+--------------------------+
Enter fullscreen mode Exit fullscreen mode

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 for developing the Numeromancy example and documentation in the GitHub repo.

Top comments (0)