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;
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
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:
cargo build --target wasm32-wasi --release
Deploy
Once the code is built, we'll create an environment variable:
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:
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
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:
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:
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:
sudo apt install gfortran
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)
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();
Example output:
+---------------+----------------+----------------+---------------+
| true_positive | false_negative | false_positive | true_negative |
+---------------+----------------+----------------+---------------+
| 5 | 4 | 3 | 15 |
+---------------+----------------+----------------+---------------+
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;
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 |
+------+-------+-------+------+-------+-------+--------------------------+
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)