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/Getting Started with SingleStore
New

Getting Started with SingleStore

Notebook

SingleStore Notebooks

Getting Started with SingleStore

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.

This Jupyter notebook provides a comprehensive overview and test drive of SingleStore's multi-model capabilities, showcasing how to efficiently manage and query diverse data types within a single database platform.

The notebook starts with a simple "Getting Started" example, guiding users through various standard SQL queries to interact with the database. It then progressively demonstrates how to add and query different data models, including vectors for machine learning, full-text search for unstructured data, JSON for hierarchical data, geospatial data for location-based queries, and time series data for temporal analysis. This hands-on approach offers an accessible way for users to explore SingleStore's versatility and powerful multi-model functionality.

Simple "Getting Started" example

This code checks whether the current database environment is using a "shared tier" and then conditionally drops and creates a database based on the result.

In [1]:

shared_tier_check = %sql SHOW VARIABLES LIKE "is_shared_tier"
if not shared_tier_check or shared_tier_check[0][1] == "OFF":
    %sql DROP DATABASE IF EXISTS multi_model;
    %sql CREATE DATABASE IF NOT EXISTS multi_model;

Action Required

Select the database from the drop-down menu at the top of this notebook.

Various standard SQL queries

Create some simple tables

This setup establishes a basic relational structure to store customer information and their corresponding orders.

In [2]:

%%sql
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS orders;


CREATE TABLE IF NOT EXISTS customers /* Creating table for sample data. */(
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    country VARCHAR(50)
);


CREATE TABLE IF NOT EXISTS orders /* Creating table for sample data. */(
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    product VARCHAR(50)
);

Insert some data

In [3]:

%%sql
INSERT INTO customers (customer_id, customer_name, country) VALUES
(1, "John Doe", "Canada"),
(2, "Jane Smith", "Canada"),
(3, "Sam Brown", "Canada"),
(4, "Lisa White", "Canada"),
(5, "Mark Black", "Canada");


INSERT INTO orders (order_id, customer_id, amount, product) VALUES
(101, 1, 150.00, "Book"),
(102, 2, 200.00, "Pen"),
(103, 3, 50.00, "Notebook"),
(104, 1, 300.00, "Laptop"),
(105, 4, 250.00, "Tablet");

Sum of amounts

In [4]:

%%sql
SELECT
    SUM(amount) AS total_sales
FROM
    orders;

Minimum amount

In [5]:

%%sql
SELECT
    MIN(amount) AS min_order_amount
FROM
    orders;

Maximum amount

In [6]:

%%sql
SELECT
    MAX(amount) AS max_order_amount
FROM
    orders;

Average amount

In [7]:

%%sql
SELECT
    ROUND(AVG(amount), 2) AS avg_order_amount
FROM
    orders;

Count the number of orders

In [8]:

%%sql
SELECT
    COUNT(*) AS number_of_orders
FROM
    orders;

Join customers and orders tables

In [9]:

%%sql
SELECT
    customers.customer_name,
    orders.order_id,
    orders.amount
FROM
    customers, orders
WHERE
    customers.customer_id = orders.customer_id
ORDER BY
    amount ASC;

Group by customer and calculate total amount spent

In [10]:

%%sql
SELECT
    customers.customer_name,
    SUM(orders.amount) AS total_spent
FROM
    customers, orders
WHERE
    customers.customer_id = orders.customer_id
GROUP BY
    customers.customer_name
ORDER BY
    total_spent DESC;

Add Vectors

Add a 3-dimensional vector to the orders table

In [11]:

%%sql
ALTER TABLE orders ADD COLUMN dimensions VECTOR(3);

Add some vector data

3 dimensions represent Length (L), Width (W) and Height (H) in cm

In [12]:

%%sql
UPDATE orders SET dimensions = '[8.5, 5.5, 1.0]' WHERE order_id = 101;
UPDATE orders SET dimensions = '[0.5, 0.5, 14.0]' WHERE order_id = 102;
UPDATE orders SET dimensions = '[21.0, 29.7, 0.5]' WHERE order_id = 103;
UPDATE orders SET dimensions = '[32.0, 22.0, 2.0]' WHERE order_id = 104;
UPDATE orders SET dimensions = '[24.0, 16.0, 0.7]' WHERE order_id = 105;

Show the vectors

In [13]:

%%sql
SET vector_type_project_format = JSON;


SELECT
    *
FROM
    orders;

Select orders using <*> which is Dot Product

The dot product is a way of multiplying two vectors to get a single number (a scalar).

In simple terms, the dot product provides a way to combine two sets of numbers into a single value that reflects how much the vectors "point" in the same direction.

In [14]:

%%sql
SET vector_type_project_format = JSON;


SELECT
    *,
    ROUND((dimensions <*> '[32.0, 22.0, 2.0]'), 2) AS score
--  ROUND(DOT_PRODUCT(dimensions, '[32.0, 22.0, 2.0]'), 2) AS score
FROM
    orders
ORDER BY
    score DESC;

Select orders using <-> which is Euclidean Distance

Euclidean distance is a way to measure how far apart two points are in space.

In simple terms, Euclidean distance provides a straight-line measurement of how far one point is from another, like using a ruler to measure the distance between two points on a map.

In [15]:

%%sql
SET vector_type_project_format = JSON;


SELECT
    *,
    ROUND((dimensions <-> '[32.0, 22.0, 2.0]'), 2) AS score
--  ROUND(EUCLIDEAN_DISTANCE(dimensions, '[32.0, 22.0, 2.0]'), 2) AS score
FROM
    orders
ORDER BY
    score ASC;

Add Full-Text

Add a description column to the orders table

In [16]:

%%sql
ALTER TABLE orders ADD COLUMN description VARCHAR(255);

Update orders table with descriptions

In [17]:

%%sql
UPDATE orders
SET description = CASE
    WHEN product = "Book" THEN "A high-quality book that offers insightful content and engaging narratives."
    WHEN product = "Pen" THEN "A smooth-writing pen designed for comfort and precision."
    WHEN product = "Notebook" THEN "A versatile notebook perfect for notes, sketches, and ideas."
    WHEN product = "Laptop" THEN "A powerful laptop with high performance and sleek design for all your computing needs."
    WHEN product = "Tablet" THEN "A compact tablet with a vibrant display and versatile functionality."
    ELSE "A product with excellent features and quality."
END;

Show the descriptions

In [18]:

%%sql
SELECT
    *
FROM
    orders;

Add a full-text index to the orders table

In [19]:

%%sql
ALTER TABLE orders ADD FULLTEXT USING VERSION 2 orders_ft_index (product, description);
OPTIMIZE TABLE orders FLUSH;

Search for a match on "vibrant" in the description part

In [20]:

%%sql
SELECT
    *
FROM
    orders
WHERE
    MATCH (TABLE orders) AGAINST ("description:vibrant");

Use various operators to show flexibility

+ (must appear), * (multiple wildcard), ? (single wildcard)

In [21]:

%%sql
SELECT
    product
FROM
    orders
WHERE
    MATCH (TABLE orders) AGAINST ("product:(+oo?) OR description:versa*");

Add JSON

Add a JSON column to the orders table

In [22]:

%%sql
ALTER TABLE orders ADD COLUMN additional_details JSON NOT NULL;

Update orders table with additional details in JSON format

In [23]:

%%sql
UPDATE orders
SET additional_details = CASE
    WHEN order_id = 101 THEN '{
        "invoice_number": "INV1001",
        "order_status": "Delivered",
        "shipping_address": {
            "street": "456 Elm St",
            "city": "Toronto",
            "state": "ON",
        "postal_code": "M5A 1A1",
        "country": "Canada"
        },
        "payment_method": "Credit Card",
        "discounts_applied": [{
            "discount_code": "WELCOME10",
            "amount": 10.00
        }],
        "order_date": "2024-07-01",
        "estimated_delivery_date": "2024-07-05",
        "tracking_number": "TRACK1001",
        "customer_notes": "Leave at the front desk."
    }'
    WHEN order_id = 102 THEN '{
        "invoice_number": "INV1002",
        "order_status": "Pending",
        "shipping_address": {
            "street": "789 Oak St",
            "city": "Vancouver",
            "state": "BC",
            "postal_code": "V5K 1A1",
            "country": "Canada"
        },
        "payment_method": "PayPal",
        "discounts_applied": [{
            "discount_code": "SPRING20",
            "amount": 20.00
        }],
        "order_date": "2024-07-02",
        "estimated_delivery_date": "2024-07-06",
        "tracking_number": "TRACK1002",
        "customer_notes": "Contact me before delivery."
    }'
    WHEN order_id = 103 THEN '{
        "invoice_number": "INV1003",
        "order_status": "Shipped",
        "shipping_address": {
            "street": "321 Pine St",
            "city": "Montreal",
            "state": "QC",
            "postal_code": "H2X 1Y4",
            "country": "Canada"
        },
        "payment_method": "Credit Card",
        "discounts_applied": [{
            "discount_code": "SAVE15",
            "amount": 15.00
        }],
        "order_date": "2024-07-03",
        "estimated_delivery_date": "2024-07-07",
        "tracking_number": "TRACK1003",
        "customer_notes": "Deliver after 5 PM."
    }'
    WHEN order_id = 104 THEN '{
        "invoice_number": "INV1004",
        "order_status": "Shipped",
        "shipping_address": {
            "street": "654 Maple St",
            "city": "Calgary",
            "state": "AB",
            "postal_code": "T2P 1N4",
            "country": "Canada"
        },
        "payment_method": "Credit Card",
        "discounts_applied": [{
            "discount_code": "NEWYEAR25",
            "amount": 25.00
        }],
        "order_date": "2024-07-01",
        "estimated_delivery_date": "2024-07-08",
        "tracking_number": "TRACK1004",
        "customer_notes": "Leave package at the back door."
    }'
    WHEN order_id = 105 THEN '{
        "invoice_number": "INV1005",
        "order_status": "Delivered",
        "shipping_address": {
            "street": "987 Birch St",
            "city": "Ottawa",
            "state": "ON",
            "postal_code": "K1A 0A1",
            "country": "Canada"
        },
        "payment_method": "PayPal",
        "discounts_applied": [{
            "discount_code": "HOLIDAY30",
            "amount": 30.00
        }],
        "order_date": "2024-07-03",
        "estimated_delivery_date": "2024-07-09",
        "tracking_number": "TRACK1005",
        "customer_notes": "Please ring the doorbell."
    }'
    ELSE '{}'
END;

Extract specific JSON fields

In [24]:

%%sql
SELECT
    order_id,
    additional_details::invoice_number AS invoice_number,
    additional_details::order_status AS order_status
FROM
    orders
ORDER BY
    order_id;

Find orders that have been "Delivered"

In [25]:

%%sql
SELECT
    order_id,
    additional_details::invoice_number AS invoice_number
FROM
    orders
WHERE
    additional_details::order_status = '"Delivered"'
ORDER BY
    order_id;

Aggregate data based on JSON fields

In [26]:

%%sql
SELECT
    additional_details::order_status AS order_status,
    COUNT(*) AS order_count
FROM
    orders
GROUP BY
    order_status;

Add Geospatial

Insert 2 more customers into customers table

In [27]:

%%sql
INSERT INTO customers (customer_id, customer_name, country) VALUES
(6, "Emily Davis", "Canada"),
(7, "Michael Johnson", "Canada");

Create neighborhoods table for geospatial data

In [28]:

%%sql
DROP TABLE IF EXISTS neighborhoods;


CREATE TABLE IF NOT EXISTS neighborhoods /* Creating table for sample data. */(
    id INT UNSIGNED NOT NULL,
    name VARCHAR(64) NOT NULL,
    population INT UNSIGNED NOT NULL,
    shape TEXT NOT NULL,
    centroid GEOGRAPHYPOINT NOT NULL,
    sort key (name),
    shard key (id)
);

Add some city data to the neighborhoods table

In [29]:

%%sql
INSERT INTO neighborhoods (id, name, population, shape, centroid) VALUES
(1, "Toronto", 2794356,
    "POLYGON((-79.6393 43.6777, -79.1152 43.6777, -79.1152 43.8554, -79.6393 43.8554, -79.6393 43.6777))",
    "POINT(-79.3832 43.6532)"
),
(2, "Vancouver", 662248,
    "POLYGON((-123.2247 49.1985, -123.0234 49.1985, -123.0234 49.3169, -123.2247 49.3169, -123.2247 49.1985))",
    "POINT(-123.1216 49.2827)"
),
(3, "Montreal", 1762949,
    "POLYGON((-73.9354 45.3991, -73.4757 45.3991, -73.4757 45.7044, -73.9354 45.7044, -73.9354 45.3991))",
    "POINT(-73.5673 45.5017)"
),
(4, "Calgary", 1306784,
    "POLYGON((-114.3160 50.8420, -113.8599 50.8420, -113.8599 51.2124, -114.3160 51.2124, -114.3160 50.8420))",
    "POINT(-114.0719 51.0447)"
),
(5, "Ottawa", 1017449,
    "POLYGON((-75.9274 45.2502, -75.3537 45.2502, -75.3537 45.5489, -75.9274 45.5489, -75.9274 45.2502))",
    "POINT(-75.6972 45.4215)"
);

Add a geospatial column to the customers table

In [30]:

%%sql
ALTER TABLE customers ADD COLUMN location GEOGRAPHYPOINT;

Update customers table with location data

In [31]:

%%sql
UPDATE customers SET location = "POINT(-79.3832 43.6532)" WHERE customer_id = 1;
UPDATE customers SET location = "POINT(-123.1216 49.2827)" WHERE customer_id = 2;
UPDATE customers SET location = "POINT(-73.5673 45.5017)" WHERE customer_id = 3;
UPDATE customers SET location = "POINT(-114.0719 51.0447)" WHERE customer_id = 4;
UPDATE customers SET location = "POINT(-75.6972 45.4215)" WHERE customer_id = 5;
UPDATE customers SET location = "POINT(-79.3832 43.6532)" WHERE customer_id = 6;
UPDATE customers SET location = "POINT(-123.1216 49.2827)" WHERE customer_id = 7;

Join the neighborhoods table to itself and measure distances between neighborhoods

In [32]:

%%sql
SELECT
    b.name AS town,
    ROUND(GEOGRAPHY_DISTANCE(a.centroid, b.centroid), 0) AS distance_from_center,
    ROUND(GEOGRAPHY_DISTANCE(a.shape, b.shape), 0) AS distance_from_border
FROM
    neighborhoods a, neighborhoods b
WHERE
    a.name = "Vancouver"
ORDER BY
    2;

Find out where you are

In [33]:

%%sql
SELECT
    name
FROM
    neighborhoods
WHERE
    GEOGRAPHY_INTERSECTS("POINT(-79.3770 43.7500)", shape);

Find customers within "Vancouver"

In [34]:

%%sql
SELECT
    c.customer_id, c.customer_name
FROM
    customers c, neighborhoods n
WHERE
    n.name = "Vancouver" AND GEOGRAPHY_CONTAINS(n.shape, c.location);

Add Time Series

Count orders by day

In [35]:

%%sql
SELECT
    DATE_FORMAT(STR_TO_DATE(additional_details::order_date, '"%Y-%m-%d"'), '%Y-%m-%d') AS order_date,
    COUNT(*) AS order_count
FROM
    orders
GROUP BY
    order_date
ORDER BY
    order_date;

Sum of order amounts by month

In [36]:

%%sql
SELECT
    DATE_FORMAT(STR_TO_DATE(additional_details::order_date, '"%Y-%m-%d"'), '%Y-%m') AS order_month,
    SUM(amount) AS total_amount
FROM
    orders
GROUP BY
    order_month
ORDER BY
    order_month;

Orders count by customer over time

In [37]:

%%sql
SELECT
    customer_id,
    DATE_FORMAT(STR_TO_DATE(additional_details::order_date, '"%Y-%m-%d"'), '%Y-%m-%d') AS order_date,
    COUNT(*) AS order_count
FROM
    orders
GROUP BY
    customer_id, order_date
ORDER BY
    customer_id, order_date;

Bonus

Create a map from geospatial city data

In [38]:

!pip install folium shapely --quiet

Action Required

Select the database from the drop-down menu at the top of this notebook. It updates the connection_url which is used by SQLAlchemy to make connections to the selected database.

In [39]:

from sqlalchemy import *


db_connection = create_engine(connection_url)

Get city data from neighborhoods table

In [40]:

import pandas as pd


query = """
    SELECT
        id,
        name,
        population,
        shape :> TEXT AS polygon,
        centroid :> TEXT AS point
    FROM
        neighborhoods
"""


df = pd.read_sql(
    query,
    db_connection
)

Convert the data to geospatial format for Python

In [41]:

from shapely import wkt


df["polygon"] = df["polygon"].apply(wkt.loads)
df["point"] = df["point"].apply(wkt.loads)

Plot the cities on a map

In [42]:

import folium


m = folium.Map(
    location = [56.1304, -106.3468],
    zoom_start = 4
)


for idx, row in df.iterrows():
    folium.Polygon(
        locations = [(point[1], point[0]) for point in row["polygon"].exterior.coords],
        color = "blue",
        weight = 2,
        fill = True,
        fill_color = "blue",
        fill_opacity = 0.1
    ).add_to(m)


    folium.Marker(
        location = (row["point"].y, row["point"].x),
        popup = row["name"]
    ).add_to(m)


html_content = m._repr_html_()

Save the map to stage

Stage Support

The following code will only work on the Standard Tier at this time.

In [43]:

from singlestoredb import notebook as nb


if not shared_tier_check or shared_tier_check[0][1] == "OFF":
    with nb.stage.open("map.html", "w") as st:
        st.write(html_content)

Cleanup

In [44]:

%%sql
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS neighborhoods;

In [45]:

shared_tier_check = %sql SHOW VARIABLES LIKE "is_shared_tier"
if not shared_tier_check or shared_tier_check[0][1] == "OFF":
    %sql DROP DATABASE IF EXISTS multi_model;

Conclusions

In this Jupyter notebook, we explored the robust multi-model capabilities of SingleStore, demonstrating how to efficiently manage and query a wide range of data types within a unified database platform. Beginning with a simple "Getting Started" guide, we progressively delved into various standard SQL queries and extended our exploration to include more advanced data models such as vectors for machine learning, full-text search for unstructured data, JSON for hierarchical data, geospatial data for location-based queries, and time series data for temporal analysis. Through these practical examples, users can appreciate SingleStore's versatility and powerful functionality, gaining the skills to effectively harness its multi-model capabilities for diverse applications.

Details

About this Template

Test Drive SingleStore Multi-Model Examples in One Notebook

Notebook Icon

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

Tags

startersqlvectorsfulltextjsongeospatialtimeseries

License

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