Get Started with the SingleStore Snowpark App

Introduction

SingleStore is a distributed SQL database with sub-second (and often < 100 or even < 10 ms) performance at scale across various challenging workloads, even when high concurrency is required. These include transactions, point reads and writes, real-time ingestion, relational and JSON analytics, full-text search, vector search, and more.

Snowpark Container Services (SPCS) is a fully managed container offering designed to facilitate the deployment, management, and scaling of containerized applications within Snowflake. This service allows containerized workloads to run directly within Snowflake, preventing the need to move data out of the Snowflake environment for processing.

SingleStore, in combination with Snowflake, provides a very capable foundation to power the next generation of intelligent apps and operational workloads. With SingleStore’s Snowflake Native App, combining streaming data with Iceberg data is now an option for providing real-time analytics to applications built on Snowflake. Best of all, SingleStore’s data platform can be utilized without shifting the entire database out of Snowflake.

You can get the SingleStore Native App for Snowflake, SINGLESTORE_DB_APP, from the Snowflake Marketplace. It facilitates the creation, management, and scaling of SingleStore clusters from within your Snowflake deployment. The SingleStore app includes a set of Docker images, helper SQL scripts, and a comprehensive interface for managing clusters.

The following diagram is an overview of the full system architecture.

Prerequisites

Set Up

1. After the SingleStore app has been activated, click Launch app to launch it.

   

2. After a few moments, a Welcome to SingleStore page is displayed.

   Click Start Dev Cluster to create a cluster.

   

3. On the Create Cluster page, create a cluster.

   • Provide a name for this cluster. Choose a unique name that will help identify the type of workload connected with this cluster.

   • Select a cluster size to fit the workload. Larger clusters are available on request.

     	Dev	Small
     Master Aggregators	1	1
     Child Aggregators	2	2
     vCPUs per Aggregator	1	3
     RAM per Aggregator (GB)	6	13
     Storage per Aggregator (GB)	128	128
     Leaf Nodes	2	2
     vCPU per Leaf Node	1	6
     RAM per Leaf Node (GB)	6	28
     Storage per Leaf Node (GB)	128	512

   • Provide a password for the default database user.

     Each cluster starts with a default database user named root, which requires a password to connect to the cluster.

     

4. Click Create Cluster. A progress bar is displayed as the cluster is created. It may take a few minutes to create the cluster.

   

USE ROLE ACCOUNTADMIN;
ALTER NETWORK RULE SINGLESTORE_DB_APP_APP_DATA.CONFIGURATION.SINGLESTORE_DB_APP_ALL_ACCESS_EAI_NETWORK_RULE SET
    VALUE_LIST=('<data-source-endpoint-or-IP-address>:<port>')

USE ROLE ACCOUNTADMIN;
ALTER NETWORK RULE SINGLESTORE_DB_APP_APP_DATA.CONFIGURATION.SINGLESTORE_DB_APP_ALL_ACCESS_EAI_NETWORK_RULE SET
    VALUE_LIST=('192.168.1.2:443')

Loading Data into SingleStore

The SQL Editor, which is included with each cluster, performs like a worksheet, where users can add multiple SQL queries and then select which queries to run. By default, the SQL Editor only runs the highlighted query. However, multiple queries can be highlighted and are then run sequentially.

To access the SQL Editor, which is part of SingleStore Studio:

1. Launch the SingleStore app.

2. Select your cluster from the drop-down.

3. Click Go to SingleStore Studio under the Advanced dropdown.

4. Connect to the selected cluster by using the root user and the password that you specified when the cluster was created.

   Note

   If an error is displayed while launching SingleStore Studio, try opening the link (as shown in the address bar) in a private/incognito window. You may be prompted to log into Snowflake.

Refer to SingleStore Studio SQL Editor for more information.

CREATE DATABASE bottomless_db ON S3 "bottomless_db_bucket/bottomless_db_folder"
 CONFIG '{"region":"us-east-1", "verify_ssl":false}' 
  CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}';

USE ROLE ACCOUNTADMIN;
ALTER NETWORK RULE SINGLESTORE_DB_APP_APP_DATA.CONFIGURATION.SINGLESTORE_DB_APP_ALL_ACCESS_EAI_NETWORK_RULE SET
    VALUELTSTVALUE_LIST=(
    '0.0.0.0:443',
    '0.0.0.0:80*',
    'pkc-rgm37.us-west-2.aws.confluent.cloud:9092',
    'b0-pkc-rgm37.us-west-2.aws.confluent.cloud:9092',
    'b1-pkc-rgm37.us-west-2.aws.confluent.cloud:9092',
    'b2-pkc-rgm37.us-west-2.aws.confluent.cloud:9092',
    'b3-pkc-rgm37.us-west-2.aws.confluent.cloud:9092',
    'b4-pkc-rgm37.us-west-2.aws.confluent.cloud:9092',
    'b5-pkc-rgm37.us-west-2.aws.confluent.cloud:9092'
}
    )

DROP PIPELINE IF EXISTS rideshare_kafka_trips;
CREATE OR REPLACE PIPELINE rideshare_kafka_trips AS
    LOAD DATA KAFKA 'pkc-rgm37.us-west-2.aws.confluent.cloud:9092/ridesharing-sim-trips'
    CONFIG '{"sasl.username": "username",
         "sasl.mechanism": "PLAIN",
         "security.protocol": "SASL_SSL",
         "ssl.ca.location": "/etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem"}'
    CREDENTIALS '{"sasl.password": "password"}'
    DISABLE OUT_OF_ORDER OPTIMIZATION
    REPLACE INTO TABLE trips
    FORMAT JSON
    (
        id <- id,
        rider_id <- rider_id,
        driver_id <- driver_id,
        status <- status,
        @request_time <- request_time,
        @accept_time <- accept_time,
        @pickup_time <- pickup_time,
        @dropoff_time <- dropoff_time,
        fare <- fare,
        distance <- distance,
        pickup_lat <- pickup_lat,
        pickup_long <- pickup_long,
        dropoff_lat <- dropoff_lat,
        dropoff_long <- dropoff_long,
        city <- city
    )
    SET request_time = STR_TO_DATE(@request_time, '%Y-%m-%dT%H:%i:%s.%f'),
        accept_time = STR_TO_DATE(@accept_time, '%Y-%m-%dT%H:%i:%s.%f'),
        pickup_time = STR_TO_DATE(@pickup_time, '%Y-%m-%dT%H:%i:%s.%f'),
        dropoff_time = STR_TO_DATE(@dropoff_time, '%Y-%m-%dT%H:%i:%s.%f');
START PIPELINE rideshare_kafka_trips;

-- Create a riders table and kafka pipeline that consumes the ridesharing-sim-riders topic and upserts data.
DROP TABLE IF EXISTS riders;
CREATE TABLE riders (
    id VARCHAR(255) NOT NULL,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    email VARCHAR(255),
    phone_number VARCHAR(255),
    date_of_birth DATETIME(6),
    created_at DATETIME(6),
    location_city VARCHAR(255),
    location_lat DOUBLE,
    location_long DOUBLE,
    status VARCHAR(20),
    PRIMARY KEY (id),
    SORT KEY (status, location_city)
);
DROP PIPELINE IF EXISTS rideshare_kafka_riders;
CREATE OR REPLACE PIPELINE rideshare_kafka_riders AS
    LOAD DATA KAFKA 'pkc-rgm37.us-west-2.aws.confluent.cloud:9092/ridesharing-sim-riders'
    CONFIG '{"sasl.username": "username",
         "sasl.mechanism": "PLAIN",
         "security.protocol": "SASL_SSL",
         "ssl.ca.location": "/etc/pki/ca-trust/extracted/pem/tls-ca-bundle.pem"}'
    CREDENTIALS '{"sasl.password": "password"}'
    DISABLE OUT_OF_ORDER OPTIMIZATION
    REPLACE INTO TABLE riders
    FORMAT JSON
    (
        id <- id,
        first_name <- first_name,
        last_name <- last_name,
        email <- email,
        phone_number <- phone_number,
        @date_of_birth <- date_of_birth,
        @created_at <- created_at,
        location_city <- location_city,
        location_lat <- location_lat,
        location_long <- location_long,
        status <- status
    )
    SET date_of_birth = STR_TO_DATE(@date_of_birth, '%Y-%m-%dT%H:%i:%s.%f'),
        created_at = STR_TO_DATE(@created_at, '%Y-%m-%dT%H:%i:%s.%f');
START PIPELINE rideshare_kafka_riders;

Ridesharing Demo

To help demonstrate what the combination of Snowflake data, the SingleStore app, and Iceberg tables can deliver, SingleStore has created a real-time ridesharing simulation. This simulation demonstrates how an existing analytics app built on top of Snowflake data can be augmented with real-time analytics powered by SingleStore. The Iceberg Integration video provides an overview of SingleStore’s Iceberg integration

The instructions in the GitHub repository show how to set up SingleStore and the ridesharing simulation in SPCS, ingest Snowflake data through Iceberg, and connect existing Kafka streams to feed-in real-time information, all without sacrificing the ownership of the Snowflake data.

To build the ridesharing simulation in your own cluster, refer to Ridesharing Simulation in Snowpark.

Manage the Cluster

CORE.GET_APP_S2_HOST_ENDPOINT(INSTANCE_NAME)

CORE.GET_APP_ENDPOINT(INSTANCE_NAME)

CORE.SQL_COMMAND(INSTANCE_NAME, USER, PASSWORD, SQL

CORE.LIST_APP_INSTANCE(INSTANCE_NAME)

CORE.CLUSTER_UPGRADE(INSTANCE_NAME)

CORE.TOOLBOX_SERVICE_UPGRADE(INSTANCE_NAME)

CORE.STOP_APP_INSTANCE(INSTANCE_NAME)

CORE.START_APP_INSTANCE(INSTANCE_NAME)

CORE.DROP_APP_INSTANCE(INSTANCE_NAME)

Obtain Support

To obtain support with the SingleStore app, either email spcs@singlestore.com or file a Support ticket.