SingleStore logo

  • Pricing

User Icon
LoginL
Start free
SingleStore logo
Notebook Icon

Try Out This Notebook to See What’s Possible in SingleStore

Get access to other groundbreaking datasets and engage with our community for expert advice.

Sign UpLog In
SingleStore Spaces/SingleStore Cheat Sheet
New

SingleStore Cheat Sheet

Notebook

SingleStore Notebooks

SingleStore Cheat Sheet

Note

This notebook can be run on a Free Starter Workspace. To create a Free Starter Workspace navigate to Start using the left nav. You can also use your existing Standard or Premium workspace with this Notebook.

SingleStore Database Cheat Sheet

List of useful commands for SingleStore SQL and Kai (MongoDB API) operations

Important Notes

SingleStore Core Concepts

  1. Every distributed and columnstore table MUST have a SHARD KEY defined

  2. Reference tables don't need a SHARD KEY as they are replicated to all nodes

  3. SingleStore supports both rowstore (default) and columnstore table types

  4. Hash indexes are recommended for equality comparisons

  5. JSON operations are optimized for performance in SingleStore

  6. Use Reference tables for lookup data that needs to be available on all nodes

Vector Operations Tips

  1. Vector dimensions must be specified at table creation

  2. Normalize vectors before cosine similarity calculations

  3. Choose appropriate index metric based on your use case

  4. Vector operations support AI/ML workloads

  5. Combine with full-text search for hybrid search capabilities

  6. Available both in SQL and through SingleStore Kai (MongoDB API)

For the most up-to-date information, refer to the official SingleStore documentation at https://singlestore.com/docs.

Database Operations

In [1]:

%%sql
# Show Databases
SHOW DATABASES;

Out [1]:

4 rows affected.

In [2]:

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

In [3]:

%%sql
# Use Database
USE database_name;

In [4]:

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

Table Operations

In [5]:

%%sql
# Create Distributed Table
CREATE TABLE posts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    SHARD KEY (id)
);

Out [5]:

In [6]:

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

Out [6]:

In [7]:

%%sql
# Create Columnstore Table
CREATE TABLE analytics (
    id BIGINT,
    event_type VARCHAR(50),
    timestamp TIMESTAMP,
    data JSON,
    SORT KEY (timestamp),
    SHARD KEY (id)  -- Required for columnstore tables
) ENGINE=columnstore;

Out [7]:

Table Management

In [8]:

%%sql
# Show tables
SHOW TABLES;


# Describe table
DESCRIBE posts;


# Drop table
DROP TABLE posts;

Out [8]:

3 rows affected.

Data Manipulation

In [9]:

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


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

In [10]:

%%sql
# Select Data
# Select all rows
SELECT * FROM posts;


# Select specific columns
SELECT title, category FROM posts;


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

In [11]:

%%sql
# Update Data
UPDATE posts
SET body = 'Updated body'
WHERE title = 'Post One';

In [12]:

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

SingleStore Pipelines

Pipelines are used to bring data into SingleStore tables from different sources, for example an S3 bucket

Create Pipeline

In [13]:

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

Start Pipeline

In [14]:

%%sql
START PIPELINE SalesData_Pipeline;

Check pipeline status

In [15]:

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

Out [15]:

DATABASE_NAMEPIPELINE_NAMESOURCE_TYPEFILE_NAMEFILE_SIZEFILE_STATE

Stop pipeline

In [16]:

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

Drop Pipeline

In [17]:

%%sql
DROP PIPELINE [IF EXISTS] pipeline_name;

SingleStore Specific Features

JSON Operations

In [18]:

%%sql
# Create table with JSON column
CREATE TABLE json_posts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    data JSON,
    SHARD KEY (id)
);


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


# Query JSON
SELECT JSON_EXTRACT_JSON(data, '$.title') as title
FROM json_posts;

Out [18]:

1 rows affected.

Vector Operations

In [19]:

%%sql
# Create table with vector column
CREATE TABLE embeddings (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    description TEXT,
    embedding VECTOR(1536),  -- Specify vector dimension
    SHARD KEY (id)
);

Out [19]:

In [20]:

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

Out [20]:

In [21]:

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


# Hybrid search combining full-text and vector search
SELECT id, description,
    DOT_PRODUCT(embedding, '[0.1, 0.2, ...]') as vector_score,
    MATCH(description) 'search terms' as text_score
FROM embeddings
WHERE MATCH(description) 'search terms'
ORDER BY (vector_score * 0.7 + text_score * 0.3) DESC;

SingleStore Kai (MongoDB API)

MongoDB Client Connection

mongodb://username:password@hostname:27017/database

In [22]:

%%sql
# MongoDB-style commands
# Show databases
show dbs


# Use database
use mydb


# Show collections
show collections


# Create collection
db.createCollection('users')

Details

About this Template

Get started with SingleStore quickly with common commands

Notebook Icon

This Notebook can be run in Shared Tier, Standard and Enterprise deployments.

Tags

starter

License

This Notebook has been released under the Apache 2.0 open source license.