SingleStore logo

  • Pricing

LoginL
Start free
SingleStore logo

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
New

SingleStore Fundamentals

Notebook

SingleStore Notebooks

SingleStore Fundamentals

Note

This notebook can be run on a Free Starter Workspace. To create a Free Starter Workspace navigate to + 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. Reference tables don't need a SHARD KEY as they are replicated to all nodes

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

  3. Hash indexes are recommended for fast equality lookups on large tables

  4. JSON operations are optimized for performance in SingleStore

  5. 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 to length 1 before inserting them in the database when you are doing cosine similarity calculations (but note that many models produce length-1 vectors so this is often not necessary; check the documentation for your model)

  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]:

1%%sql2# Show Databases3SHOW DATABASES;

In [2]:

1%%sql2# Create Database3CREATE DATABASE database_name; # Note this will not work on free tier due to one DB constraint

In [3]:

1%%sql2# Use Database3USE database_name;

In [4]:

1%%sql2# Drop Database3DROP DATABASE database_name; # Use with extreme caution

Table Operations

In [5]:

1%%sql2# Create Distributed Table3CREATE 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);

In [6]:

1%%sql2# Create Reference Table3CREATE REFERENCE TABLE categories (4    id INT PRIMARY KEY,5    name VARCHAR(50)6    # No SHARD KEY needed for reference tables7);

In [7]:

1%%sql2# Create Columnstore Table3CREATE TABLE analytics (4    id BIGINT,5    event_type VARCHAR(50),6    ts DATETIME,7    data JSON,8    SORT KEY (ts),9    SHARD KEY (id)10);

Table Management

In [8]:

1%%sql2# Show tables3SHOW TABLES;

In [9]:

1%%sql2# Describe table3DESCRIBE posts;

In [10]:

1%%sql2# Drop table3DROP TABLE posts;

Data Manipulation

In [11]:

1%%sql2# Insert single row3INSERT INTO posts (title, body, category)4VALUES ('Post One', 'Body of post one', 'News');5
6# Insert multiple rows7INSERT INTO posts (title, body, category) VALUES8    ('Post Two', 'Body of post two', 'Technology'),9    ('Post Three', 'Body of post three', 'News');

In [12]:

1%%sql2# Select Data3# Select all rows4SELECT * FROM posts;

In [13]:

1%%sql2# Select specific columns3SELECT title, category FROM posts;

In [14]:

1%%sql2# Select with condition3SELECT * FROM posts WHERE category = 'News';

In [15]:

1%%sql2# Update Data3UPDATE posts4SET body = 'Updated body'5WHERE title = 'Post One';

In [16]:

1%%sql2# Delete Data3DELETE FROM posts WHERE title = 'Post One';

Action Required

Before running the pipeline examples below, be sure to select your database from the drop-down list at the top of this notebook. This updates the connection_url which is used by the %%sql magic command to connect to the selected database.

SingleStore Pipelines

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

Create Pipeline

In [17]:

1%%sql2# Create the destination table for the S3 sample data.3CREATE TABLE SalesData (4    Date TEXT COLLATE utf8mb4_bin,5    Store_ID BIGINT DEFAULT NULL,6    ProductID TEXT COLLATE utf8mb4_bin,7    Product_Name TEXT COLLATE utf8mb4_bin,8    Product_Category TEXT COLLATE utf8mb4_bin,9    Quantity_Sold BIGINT DEFAULT NULL,10    Price FLOAT DEFAULT NULL,11    Total_Sales FLOAT DEFAULT NULL12);

In [18]:

1%%sql2# Create Pipeline3CREATE PIPELINE SalesData_Pipeline AS4LOAD DATA S3 's3://singlestoreloaddata/SalesData/*.csv'5CONFIG '{ "region": "ap-south-1" }'6INTO TABLE SalesData7FIELDS TERMINATED BY ','8LINES TERMINATED BY '\n'9IGNORE 1 lines;

Start Pipeline

In [19]:

1%%sql2START PIPELINE SalesData_Pipeline;

Check pipeline status

In [20]:

1%%sql2SELECT * FROM information_schema.pipelines_files3WHERE pipeline_name = "SalesData_Pipeline";

Stop pipeline

In [21]:

1%%sql2STOP PIPELINE IF RUNNING SalesData_Pipeline;

Drop Pipeline

In [22]:

1%%sql2DROP PIPELINE IF EXISTS SalesData_Pipeline;

SingleStore Specific Features

JSON Operations

In [23]:

1%%sql2# Create table with JSON column3CREATE TABLE json_posts (4    id BIGINT AUTO_INCREMENT PRIMARY KEY,5    data JSON,6    SHARD KEY (id)7);

In [24]:

1%%sql2# Insert JSON3INSERT INTO json_posts (data)4VALUES ('{"title": "Post One", "tags": ["news", "events"]}');

In [25]:

1%%sql2SELECT JSON_EXTRACT_STRING(data, 'title') AS title3FROM json_posts;

Vector Operations

In [26]:

1%%sql2# Create table with vector column3CREATE TABLE embeddings (4    id BIGINT AUTO_INCREMENT PRIMARY KEY,5    description TEXT,6    embedding VECTOR(1536),  -- Specify vector dimension7    SHARD KEY (id)8);

In [27]:

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

In [28]:

1%%sql2# Generate three valid 1,536-dimensional test vectors.3SET @v1 = CONCAT('[1,', RPAD('0,', 3068, '0,'), '0]');4SET @v2 = CONCAT('[0,1,', RPAD('0,', 3066, '0,'), '0]');5SET @v3 = CONCAT('[', RPAD('0,', 3070, '0,'), '1]');

In [29]:

1%%sql2# Confirm that each test vector contains exactly 1,536 dimensions.3SELECT JSON_LENGTH(@v1) AS v1_dimensions,4       JSON_LENGTH(@v2) AS v2_dimensions,5       JSON_LENGTH(@v3) AS v3_dimensions;

In [30]:

1%%sql2# Remove prior test rows so the example can be rerun cleanly.3DELETE FROM embeddings4WHERE id IN (1, 2, 3);

In [31]:

1%%sql2# Insert sample descriptions and their corresponding test vectors.3INSERT INTO embeddings (id, description, embedding)4VALUES5(1, 'Vector search for product recommendations', @v1),6(2, 'Full-text search for product descriptions', @v2),7(3, 'Hybrid search combining vector and text search', @v3);

In [32]:

1%%sql2# Rank rows by similarity to the first test vector.3SELECT id, description,4       DOT_PRODUCT(embedding, @v1) AS similarity5FROM embeddings6ORDER BY similarity DESC7LIMIT 10;

SingleStore Kai (MongoDB API)

MongoDB Client Connection

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

To run these commands:

  1. Open Editor → Open Kai Shell.

  2. Select a Kai-enabled cluster.

  3. Run the commands directly in Kai Shell.

Kai Shell connects automatically, so no connection string or credentials are required.

show dbs
use mydb
show collections
db.createCollection("users")
show collections

Details

About this Template

Get started with SingleStore quickly with common commands

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

Tags

starter

See Notebook in action

Launch this notebook in SingleStore and start executing queries instantly.

License

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