Abstract
An excellent article contains a discussion and code examples for using GraphQL with SingleStoreDB. WebAssembly is also used to build a UDF for sentiment analysis and is subsequently used in some queries. How can we try the demo ourselves? This article will describe the steps required to replicate the demo.
Introduction
SingleStoreDB is a very versatile and capable DBMS. We have seen examples of many use cases and integrations in previous articles. Continuing this trend, we'll discuss using SingleStoreDB with GraphQL in this article. Although we have previously seen how to build and use a very capable WebAssembly UDF for sentiment analysis, we'll build and use a simpler WebAssembly UDF for sentiment analysis in this article.
Create a SingleStoreDB Cloud account
A previous article showed the steps required to create a free SingleStoreDB Cloud account. We'll use GraphQL Demo Group as our Workspace Group Name and graphql-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 and Tables
In our SingleStoreDB Cloud account, we'll use the SQL Editor to create a new database, as follows:
CREATE DATABASE IF NOT EXISTS demo;
We'll also create two tables:
USE demo;
CREATE TABLE products(
id INT,
name VARCHAR(70),
category VARCHAR(70)
);
CREATE TABLE comments(
id INT,
ts DATETIME,
user VARCHAR(30),
pid INT,
text TEXT
);
Next, we'll insert some values into each table:
INSERT INTO products VALUES
(1,'running shoe','sporting goods'),
(2,'soccer ball','sporting goods'),
(3,'cotton balls','cosmetics');
INSERT INTO comments VALUES
(1,'2022-06-25 22:11:25','joe',1,'fantastic shoe'),
(2,'2022-06-25 22:58:01','sue',2,'ball has poor bounce'),
(3,'2022-06-25 22:59:00','amy',2,'amazingly durable ball, and it looks great'),
(4,'2022-06-25 23:05:10','mila',3,'cotton balls are nice and fluffy -- love them!'),
(5,'2022-06-25 23:06:37','joao',3,'cotton balls were not fluffy; I don''t like this brand');
The complete SQL code is listed in Appendix A.
Setup local Wasm development environment
We'll follow the steps described in the previous article to quickly create a local Wasm development environment.
All the code changes and the files that we need that differ from the previous article are listed in Appendix B.
We'll use the pushwasm tool to push our Wasm module into SingleStoreDB. After a short time, we should see the following message:
Wasm function was created successfully.
Next, we can test the function with some queries, such as:
SELECT id, text, sentiment(text) AS sentiment
FROM comments
ORDER BY sentiment DESC
LIMIT 5;
The output should be as follows:
+------+-------------------------------------------------------+---------------------+
| id | text | sentiment |
+------+-------------------------------------------------------+---------------------+
| 4 | cotton balls are nice and fluffy -- love them! | 0.8069730414548824 |
| 3 | amazingly durable ball, and it looks great | 0.6248933269389457 |
| 1 | fantastic shoe | 0.5573704017131537 |
| 5 | cotton balls were not fluffy; I don't like this brand | 0.20746990495811898 |
| 2 | ball has poor bounce | -0.4766576055745744 |
+------+-------------------------------------------------------+---------------------+
and a more complex example, such as:
SELECT c.id, c.user, p.name, c.text, sentiment(c.text) AS sentiment
FROM comments c, products p
WHERE p.id = c.pid AND (c.ts :> DATE) = "2022-06-25" AND p.category = "sporting goods"
ORDER BY sentiment DESC
LIMIT 3;
The output should be as follows:
+------+------+--------------+--------------------------------------------+---------------------+
| id | user | name | text | sentiment |
+------+------+--------------+--------------------------------------------+---------------------+
| 3 | amy | soccer ball | amazingly durable ball, and it looks great | 0.6248933269389457 |
| 1 | joe | running shoe | fantastic shoe | 0.5573704017131537 |
| 2 | sue | soccer ball | ball has poor bounce | -0.4766576055745744 |
+------+------+--------------+--------------------------------------------+---------------------+
We'll also create a view, as follows:
CREATE VIEW commentsWithSentiment AS
SELECT id, ts, user, pid, text, sentiment(text) AS sentiment
FROM comments;
GraphQL using StepZen
The final step is to use GraphQL. We'll create a free account on IBM. Once logged-in, we can access the Account, Admin Key and API Key.
The StepZen CLI requires NodeJS 14 or higher. Once we have checked that we have met this requirement, we'll create a new directory in our home folder:
cd
mkdir stepzen
cd stepzen
and we'll install the StepZen CLI, which may require sudo:
npm install -g stepzen
We'll log in using our account:
stepzen login us-east-a.ibm.stepzen.net -a <account>
We'll replace <account> with the value from our StepZen account.
Next, we'll be prompted for our Admin Key:
What is your admin key?:
If successful, we should see:
You have successfully logged in with the <account> account.
SingleStoreDB is MySQL wire-compatible, so we'll run:
stepzen import mysql
Next, we'll need to fill in the details for our connection, as follows:
- What would you like your endpoint to be called?: Take the default or enter a new value
-
What is your host? (and optional port as host:port):
<host> - What is the username?: admin
-
What is the password?:
<password> - What is your database name?: demo
- Automatically link types based on foreign key relationships using @materializer: Yes
We'll replace the <host> and <password> with the values from our SingleStoreDB Cloud account.
Once finished, we should see the message:
Successfully imported mysql data source into your GraphQL schema
In the stepzen directory, we'll see a mysql folder. Inside the mysql folder will be a file called index.graphql. We'll make a backup copy of this file:
cp index.graphql index.graphql.bak
In the original index.graphql, we'll replace the entire contents with Appendix C.
From the stepzen directory, we'll run the dashboard locally, as follows:
stepzen start --dashboard=local
After running the above command, a webpage can then be opened using the address provided. We should see a dashboard similar to Figure 1.
Let's copy-and-paste some queries into the StepZen editor on the left-hand side and then execute each query using the arrow button (▶). The query output will appear on the right-hand side.
- Get the
idandnameofproductswhere the category is sporting goods.
query {
products(filter: { category: { eq: "sporting goods" } }) {
id
name
}
}
The output should be as follows:
{
"data": {
"products": [
{
"id": 1,
"name": "running shoe"
},
{
"id": 2,
"name": "soccer ball"
}
]
}
}
This is shown in Figure 2.
- Get the
id,commentsandsentimentfor cotton balls.
query {
commentsForProduct(pid: "3") {
id
text
sentiment
}
}
The output should be as follows:
{
"data": {
"commentsForProduct": [
{
"id": 4,
"text": "cotton balls are nice and fluffy -- love them!",
"sentiment": "0.8069730414548824"
},
{
"id": 5,
"text": "cotton balls were not fluffy; I don't like this brand",
"sentiment": "0.20746990495811898"
}
]
}
}
This is shown in Figure 3.
- Similar to the first query, but we are returning information across the two tables.
query {
products(filter: { category: { eq: "sporting goods" } }) {
name
comments {
text
user
sentiment
}
}
}
The output should be as follows:
{
"data": {
"products": [
{
"name": "running shoe",
"comments": [
{
"text": "fantastic shoe",
"user": "joe",
"sentiment": "0.5573704017131537"
}
]
},
{
"name": "soccer ball",
"comments": [
{
"text": "ball has poor bounce",
"user": "sue",
"sentiment": "-0.4766576055745744"
},
{
"text": "amazingly durable ball, and it looks great",
"user": "amy",
"sentiment": "0.6248933269389457"
}
]
}
]
}
}
This is shown in Figure 4.
Summary
In this article, we have described how to create a simple Wasm UDF for sentiment analysis, set up a GraphQL environment and successfully tested some queries against SingleStoreDB. Check out the original article for further details.
Appendix A - SQL Code
CREATE DATABASE IF NOT EXISTS demo;
USE demo;
CREATE TABLE products(
id INT,
name VARCHAR(70),
category VARCHAR(70)
);
CREATE TABLE comments(
id INT,
ts DATETIME,
user VARCHAR(30),
pid INT,
text TEXT
);
INSERT INTO products VALUES
(1,'running shoe','sporting goods'),
(2,'soccer ball','sporting goods'),
(3,'cotton balls','cosmetics');
INSERT INTO comments VALUES
(1,'2022-06-25 22:11:25','joe',1,'fantastic shoe'),
(2,'2022-06-25 22:58:01','sue',2,'ball has poor bounce'),
(3,'2022-06-25 22:59:00','amy',2,'amazingly durable ball, and it looks great'),
(4,'2022-06-25 23:05:10','mila',3,'cotton balls are nice and fluffy -- love them!'),
(5,'2022-06-25 23:06:37','joao',3,'cotton balls were not fluffy; I don''t like this brand');
SELECT id, text, sentiment(text) AS sentiment
FROM comments
ORDER BY sentiment DESC
LIMIT 5;
SELECT c.id, c.user, p.name, c.text, sentiment(c.text) AS sentiment
FROM comments c, products p
WHERE p.id = c.pid AND (c.ts :> DATE) = "2022-06-25" AND p.category = "sporting goods"
ORDER BY sentiment DESC
LIMIT 3;
CREATE VIEW commentsWithSentiment AS
SELECT id, ts, user, pid, text, sentiment(text) AS sentiment
FROM comments;
Appendix B - Wasm UDF
sentiment.wit
sentiment: func(input: string) -> float64
Cargo.toml
[package]
name = "sentiment"
version = "0.1.0"
edition = "2021"
# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html
[dependencies]
wit-bindgen-rust = { git = "https://github.com/bytecodealliance/wit-bindgen.git", rev = "60e3c5b41e616fee239304d92128e117dd9be0a7" }
vader_sentiment = { git = "https://github.com/ckw017/vader-sentiment-rust" }
lazy_static = "1.4.0"
[lib]
crate-type = ["cdylib"]
lib.rs
wit_bindgen_rust::export!("sentiment.wit");
struct Sentiment;
impl sentiment::Sentiment for Sentiment {
fn sentiment(input: String) -> f64 {
lazy_static::lazy_static! {
static ref ANALYZER: vader_sentiment::SentimentIntensityAnalyzer<'static> =
vader_sentiment::SentimentIntensityAnalyzer::new();
}
let scores = ANALYZER.polarity_scores(input.as_str());
scores["compound"]
}
}
pushwasm
pushwasm udf --force --conn "mysql://admin:<password>@<host>:3306/demo" --wit ./sentiment.wit --wasm ./target/wasm32-wasi/release/sentiment.wasm --name sentiment
Appendix C - GraphQL
index.graphql
type Product {
id: Int!
name: String
category: String
}
type Comment {
id: Int!
ts: DateTime
user: String
pid: ID
text: String
}
type Query {
products(filter: ProductFilter): [Product]
@dbquery(type: "mysql", table: "products", configuration: "mysql_config")
comments(filter: CommentFilter): [Comment]
@dbquery(type: "mysql", table: "comments", configuration: "mysql_config")
}
extend type Comment {
sentiment: String
}
extend type Product {
comments: [Comment]
@materializer(
query: "commentsForProduct"
arguments: [{ name: "pid", field: "id" }]
)
}
type Query {
commentsForProduct(pid: ID!): [Comment]
@dbquery(
type: "mysql"
table: "commentsWithSentiment"
configuration: "mysql_config"
)
}
input StringFilter {
eq: String!
}
input IntFilter {
eq: Int
lt: Int
gt: Int
}
input IDFilter {
eq: ID
}
input DateTimeFilter {
eq: DateTime
}
input ProductFilter {
id: IntFilter
name: StringFilter
category: StringFilter
}
input CommentFilter {
id: IntFilter
ts: DateTimeFilter
user: StringFilter
pid: IDFilter
text: StringFilter
}
Top comments (0)