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, pipelines, JSON, and vector 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

SingleStore gives you one SQL platform for relational data, lookup data, semi-structured JSON and AI-oriented vector workloads—fewer separate systems to move data between.

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

Within three table definitions above, we've shown three different design choices: distributed relational data, replicated lookup data, and analytical columnstore data with JSON. The schema can match the workload instead of forcing every use case into one table pattern.

Show Tables

In [8]:

1%%sql2# Show tables3SHOW TABLES;

In [9]:

1%%sql2# Describe table3DESCRIBE posts;

In [10]:

1%%sql2-- Alternative: list table names as a result set3SELECT TABLE_NAME4FROM information_schema.TABLES5WHERE TABLE_SCHEMA = DATABASE()6ORDER BY TABLE_NAME;

Describe Table

In [11]:

1%%sql2# Drop table3DROP TABLE posts;

Data Manipulation

In [12]:

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

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

In [14]:

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

In [15]:

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

In [16]:

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

In [17]:

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 bring data into SingleStore tables from external sources such as Amazon S3. Instead of writing a separate download, parsing and insert program, you declare the source and loading rules in SQL—SingleStore handles the rest.

In [18]:

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

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

1%%sql2START PIPELINE SalesData_Pipeline;

Check pipeline status

In [21]:

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

Stop pipeline

In [22]:

1%%sql2STOP PIPELINE IF RUNNING SalesData_Pipeline;

Drop Pipeline

In [23]:

1%%sql2DROP PIPELINE IF EXISTS SalesData_Pipeline;

JSON and Vector Features

The same database that holds relational data can store JSON documents and high-dimensional vectors, making it possible to build AI search patterns without a separate vector store.

JSON Operations

In [24]:

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

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

In [26]:

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

Vector Operations

In [27]:

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

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

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

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

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

In [32]:

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

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;

Summary

This notebook has covered database and table design, data manipulation, S3 pipeline ingestion, JSON operations, and vector similarity search. These workloads can be developed and queried in one SingleStore platform—start with the section that matches your use case and adapt the examples to your own data.

In a real application, the vectors would come from your chosen embedding model, and you would retrieve matching records together with useful metadata. The notebook keeps the example self-contained with test vectors while showing the database-side search pattern.

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.