SingleStore Cheat Sheet

1
%%sql
2
# Show Databases
3
SHOW DATABASES;

1
%%sql
2
# Create Database
3
CREATE DATABASE database_name; -- Note this will not work on free tier due to one DB constraint

1
%%sql
2
# Use Database
3
USE database_name;

1
%%sql
2
# Drop Database
3
DROP DATABASE database_name; -- Use with extreme caution

1
%%sql
2
# Create Distributed Table
3
CREATE TABLE posts (
4
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
5
    title VARCHAR(255),
6
    body TEXT,
7
    category VARCHAR(50),
8
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
9
    SHARD KEY (id)
10
);

1
%%sql
2
# Create Reference Table
3
CREATE REFERENCE TABLE categories (
4
    id INT PRIMARY KEY,
5
    name VARCHAR(50)
6
    -- No SHARD KEY needed for reference tables
7
);

1
%%sql
2
# Create Columnstore Table
3
CREATE TABLE analytics (
4
    id BIGINT,
5
    event_type VARCHAR(50),
6
    ts DATETIME,
7
    data JSON,
8
    SORT KEY (timestamp),
9
    SHARD KEY (id)
10
);

1
%%sql
2
# Show tables
3
SHOW TABLES;
4

5
# Describe table
6
DESCRIBE posts;
7

8
# Drop table
9
DROP TABLE posts;

1
%%sql
2
# Insert single row
3
INSERT INTO posts (title, body, category)
4
VALUES ('Post One', 'Body of post one', 'News');
5

6
# Insert multiple rows
7
INSERT INTO posts (title, body, category) VALUES
8
    ('Post Two', 'Body of post two', 'Technology'),
9
    ('Post Three', 'Body of post three', 'News');

1
%%sql
2
# Select Data
3
# Select all rows
4
SELECT * FROM posts;
5

6
# Select specific columns
7
SELECT title, category FROM posts;
8

9
# Select with condition
10
SELECT * FROM posts WHERE category = 'News';

1
%%sql
2
# Update Data
3
UPDATE posts
4
SET body = 'Updated body'
5
WHERE title = 'Post One';

1
%%sql
2
# Delete Data
3
DELETE FROM posts WHERE title = 'Post One';

1
%%sql
2
#Create Pipeline
3
CREATE PIPELINE SalesData_Pipeline AS
4
LOAD DATA S3 's3://singlestoreloaddata/SalesData/*.csv'
5
CONFIG '{ "region": "ap-south-1" }'
6
INTO TABLE SalesData
7
FIELDS TERMINATED BY ','
8
LINES TERMINATED BY '\n'
9
IGNORE 1 lines;

1
%%sql
2
START PIPELINE SalesData_Pipeline;

1
%%sql
2
SELECT * FROM information_schema.pipelines_files
3
WHERE pipeline_name = "SalesData_Pipeline";

1
%%sql
2
STOP PIPELINE [IF RUNNING] pipeline_name [DETACH];

1
%%sql
2
DROP PIPELINE [IF EXISTS] pipeline_name;

1
%%sql
2
# Create table with JSON column
3
CREATE TABLE json_posts (
4
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
5
    data JSON,
6
    SHARD KEY (id)
7
);
8

9
# Insert JSON
10
INSERT INTO json_posts (data)
11
VALUES ('{"title": "Post One", "tags": ["news", "events"]}');
12

13
# Query JSON
14
SELECT JSON_EXTRACT_STRING(data, '$.title') as title
15
FROM json_posts;

1
%%sql
2
# Create table with vector column
3
CREATE TABLE embeddings (
4
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
5
    description TEXT,
6
    embedding VECTOR(1536),  -- Specify vector dimension
7
    SHARD KEY (id)
8
);

1
%%sql
2
# Create vector index using dot product as distance metric
3
ALTER TABLE embeddings ADD VECTOR INDEX idx_embedding (embedding)
4
INDEX_OPTIONS '{"metric_type": "DOT_PRODUCT"}';

1
%%sql
2
# Vector search examples
3
# Find similar vectors using dot product
4
SELECT id, description, DOT_PRODUCT(embedding, '[0.1, 0.2, ...]') as similarity
5
FROM embeddings
6
ORDER BY similarity DESC
7
LIMIT 10;
8

9
# Create a full-text index
10
ALTER TABLE embeddings ADD FULLTEXT USING VERSION 2 fts_idx(description);
11

12
# Hybrid search combining full-text and vector search
13
SELECT id, description,
14
    DOT_PRODUCT(embedding, '[0.1, 0.2, ...]') as vector_score,
15
    MATCH(table embeddings) AGAINST('description:("search terms")') as text_score
16
FROM embeddings
17
WHERE MATCH(table embeddings) AGAINST('description:("search terms")')
18
ORDER BY (vector_score * 0.7 + text_score * 0.3) DESC;

1
# MongoDB-style commands
2

3
# Show databases
4
show dbs
5

6
# Use database
7
use mydb
8

9
# Show collections
10
show collections
11

12
# Create collection
13
db.createCollection('users')