Important

Helios features are now enabled during weekly update windows and are no longer directly tied to SingleStore engine releases. Refer to the release notes to view the latest features available in your Helios cluster.

Load Data and Run Queries into SingleStore

Create a SingleStore Helios Cluster

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

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 cluster 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.

SQL

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

SQL

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:

SQL

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.

SQL

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.

SQL

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 cluster 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.

SQL

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.

SQL

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.

SQL

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.

SQL

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).

SQL

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.

SQL

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.

SQL

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.

SQL

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.

SQL

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 |
+------------------+-----------------------+

Load Data and Run Queries into SingleStore

On this page

Create a SingleStore Helios Cluster

Create a Database and a Table

Create and Run an S3 Pipeline

Verify the Pipeline Success

Run Queries on the Data

Query 1: Total Sales for Each Product Category

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

Query 3: Monthly Sales Trend for a Specific Year

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

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

Query 6: Top Three Most Profitable Days by Total Sales

Query 7: Yearly Sales Growth Comparison

Query 8: Most Expensive Product Sold

Query 9: Average Quantity Sold per Product Category

Was this article helpful?

On this page

Was this article helpful?