Sign inTry Free

Load Data and Run Queries into SingleStore

On this page

This tutorial shows how to create a database, load sample data, and run queries using SingleStore Helios workspace.

It tutorial includes the following steps:

  1. Create a SingleStore Helios workspace.

  2. Create a database and a table.

  3. Create an S3 pipeline and run it to load approximately 18 million rows of sample sales data for a retail store into the SingleStore database.

  4. Run queries against the loaded data.

Create a SingleStore Helios Workspace

If a workspace already exists, skip to the next step to create the database. Navigate to the Cloud Portal and create a workspace.

Create a Database and a Table

Connect to your SingleStore deployment and run the following SQL commands to create a database named sales and associated table. Free shared workspace users cannot create new databases.

For SingleStore Helios deployments, you can also run these commands in the SQL Editor, select Cloud Portal > Editor > Open SQL Editor.

Note

The SQL Editor only runs the queries that you select, so ensure you have them all selected before selecting Run.

CREATE DATABASE sales;
USE sales;


CREATE TABLE SalesData (
    Date TEXT COLLATE utf8mb4_bin,
    Store_ID BIGINT(20) DEFAULT NULL,
    ProductID TEXT COLLATE utf8mb4_bin,
    Product_Name TEXT COLLATE utf8mb4_bin,
    Product_Category TEXT utf8mb4_bin,
    Quantity_Sold BIGINT(20) DEFAULT NULL,
    Price FLOAT DEFAULT NULL,
    Total_Sales FLOAT DEFAULT NULL
);

Create and Run an S3 Pipeline

This step shows how to ingest the sample sales data from a public AWS S3 bucket into the SingleStore database using pipelines. Pipelines enable you to extract, transform, and load external data without needing third-party tools or middleware.

Note

For this example, you must set data_conversion_compatibility_level to a value of 7.5 or lower.

Run the following SQL command to create the 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;

Run the following SQL command to start the pipeline:

START PIPELINE SalesData_Pipeline;

The pipeline ingests 18 million rows of data, which takes some time.

Verify the Pipeline Success

Query the pipelines_files information schema view to inspect the progress of the pipelines. The following command to check whether the SalesData_Pipeline pipeline has finished ingesting data.

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

You can also run the pipelines in the foreground to easily verify that all the data has been ingested.

START PIPELINE <pipeline_name> FOREGROUND;

For SingleStore Helios deployments, you can also monitor the progress of your pipelines on the Cloud Portal, select Ingestion > Pipelines. Select your workspace from the list at the top of the page.

Run Queries on the Data

Once the data is loaded into your SingleStore database, the following queries can be used on the dataset.

Query 1: Total Sales for Each Product Category

This query finds the total sales for each product category.

SELECT Product_Category, SUM(Total_Sales) AS Total_Category_Sales
FROM SalesData
GROUP BY Product_Category
ORDER BY Total_Category_Sales DESC;
+------------------+----------------------+
| Product_Category | Total_Category_Sales |
+------------------+----------------------+
| Furniture        |            473073264 |
| Clothing         |            472839616 |
| Toys             |            472607632 |
| Groceries        |            472568544 |
| Pharmacy         |            472009800 |
| Electronics      |            471887584 |
| Pet Supplies     |            470901324 |
+------------------+----------------------+

Query 2: Top Five Best-Selling Products by Quantity Sold

This query identifies the top five products with the highest quantity sold.

SELECT Product_Name, SUM(Quantity_Sold) AS Total_Quantity_Sold
FROM SalesData
GROUP BY Product_Name
ORDER BY Total_Quantity_Sold DESC
LIMIT 5;
+--------------+---------------------+
| Product_Name | Total_Quantity_Sold |
+--------------+---------------------+
| Coats        |              542856 |
| Jeans        |              541885 |
| Vests        |              539984 |
| Sweaters     |              539455 |
| Jackets      |              538003 |
+--------------+---------------------+

Query 3: Monthly Sales Trend for a Specific Year

This query shows the monthly sales in the year 2024.

SELECT DATE_FORMAT(Date, '%Y-%m') AS Month, SUM(Total_Sales) AS Monthly_Sales
FROM SalesData
WHERE YEAR(Date) = 2024
GROUP BY Month
ORDER BY Month;
+---------+--------------------+
| Month   | Monthly_Sales      |
+---------+--------------------+
| 2024-01 | 559552342.1877503  |
| 2024-02 | 523234602.4183636  |
| 2024-03 | 560848984.9077282  |
| 2024-04 | 542002073.6334558  |
| 2024-05 | 560440739.4689851  |
| 2024-06 | 541728606.9727273  |
| 2024-07 |  36056514.9656029  |
+---------+--------------------+

Query 4: Top Three Stores with the Highest Sales in the Electronics Category

This query identifies the top three stores with the highest sales in the Electronics category.

SELECT Store_ID, SUM(Total_Sales) AS Electronics_Sales
FROM SalesData
WHERE Product_Category = 'Electronics'
GROUP BY Store_ID
ORDER BY Electronics_Sales DESC
LIMIT 3;
+----------+-------------------+
| Store_ID | Electronics_Sales |
+----------+-------------------+
|     1080 |   23858838.78125  |
|     1014 |   23779519.59375  |
|     1062 |   23777307.40625  |
+----------+-------------------+

Query 5: Total Revenue by Product Category for a Specific Store

This query finds the total sales revenue for each product category for a specific store (Store ID 1084).

SELECT
   Store_ID,
   Product_Category,
   SUM(Total_Sales) AS Total_Revenue
FROM
   SalesData
WHERE
   Store_ID = 1084
GROUP BY
   Store_ID, Product_Category;
+----------+------------------+----------------+
| Store_ID | Product_Category | Total_Revenue  |
+----------+------------------+----------------+
|     1084 | Toys             | 23508569.87500 |
|     1084 | Electronics      | 23364933.40625 |
|     1084 | Pharmacy         | 23310525.65625 |
|     1084 | Furniture        | 23373549.37500 |
|     1084 | Groceries        | 23128677.03125 |
|     1084 | Pet Supplies     | 23458593.21875 |
|     1084 | Clothing         | 23447724.09375 |
+----------+------------------+----------------+

Query 6: Top Three Most Profitable Days by Total Sales

This query identifies the top three days with the highest total sales across all stores.

SELECT Date, SUM(Total_Sales) AS Daily_Total_Sales
FROM SalesData
GROUP BY Date
ORDER BY Daily_Total_Sales DESC
LIMIT 3;
+------------+-------------------+
| Date       | Daily_Total_Sales |
+------------+-------------------+
| 2024-03-12 |   46100391.87500  |
| 2023-10-04 |   46064107.06250  |
| 2023-08-10 |   45997295.31250  |
+------------+-------------------+

Query 7: Yearly Sales Growth Comparison

This query compares the total sales by year to analyze growth.

SELECT YEAR(Date) AS Year, SUM(Total_Sales) AS Annual_Sales
FROM SalesData
GROUP BY YEAR(Date)
ORDER BY Year;
+------+--------------+
| Year | Annual_Sales |
+------+--------------+
| 2023 |   8225504096 |
| 2024 |   8308831376 |
+------+--------------+

Query 8: Most Expensive Product Sold

This query finds the most expensive product sold, along with its details.

SELECT Product_Name, Price, Total_Sales
FROM SalesData
ORDER BY Price DESC
LIMIT 1;
+----------------------+-------+-------------+
| Product_Name         | Price | Total_Sales |
+----------------------+-------+-------------+
| Ginkgo Biloba 120 mg |   100 |         700 |
+----------------------+-------+-------------+

Query 9: Average Quantity Sold per Product Category

This query calculates the average quantity sold for each product category.

SELECT Product_Category, AVG(Quantity_Sold) AS Average_Quantity_Sold
FROM SalesData
GROUP BY Product_Category;
+------------------+-----------------------+
| Product_Category | Average_Quantity_Sold |
+------------------+-----------------------+
| Groceries        |               10.5019 |
| Pet Supplies     |               10.4948 |
| Clothing         |               10.4956 |
| Electronics      |               10.5035 |
| Pharmacy         |               10.4952 |
| Toys             |               10.4935 |
| Furniture        |               10.4928 |
+------------------+-----------------------+

Last modified:

Was this article helpful?

On this page

Was this article helpful?

Verification instructions

Note: You must install cosign to verify the authenticity of the SingleStore file.

Use the following steps to verify the authenticity of singlestoredb-server, singlestoredb-toolbox, singlestoredb-studio, and singlestore-client SingleStore files that have been downloaded.

You may perform the following steps on any computer that can run cosign, such as the main deployment host of the cluster.

  1. (Optional) Run the following command to view the associated signature files.

    curl undefined

  2. Download the signature file from the SingleStore release server.

    • Option 1: Click the Download Signature button next to the SingleStore file.

    • Option 2: Copy and paste the following URL into the address bar of your browser and save the signature file.

    • Option 3: Run the following command to download the signature file.

      curl -O undefined

  3. After the signature file has been downloaded, run the following command to verify the authenticity of the SingleStore file.

    echo -n undefined |
        cosign verify-blob --certificate-oidc-issuer https://oidc.eks.us-east-1.amazonaws.com/id/CCDCDBA1379A5596AB5B2E46DCA385BC \
          --certificate-identity https://kubernetes.io/namespaces/freya-production/serviceaccounts/job-worker \
          --bundle undefined \
          --new-bundle-format -
    Verified OK

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