New

Importing Data from S3 into SingleStore using Pipelines

Notebook


SingleStore Notebooks

Importing Data from S3 into SingleStore using Pipelines

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 notebook demonstrates how to create a sample table in SingleStore, set up a pipeline to import data from an Amazon S3 bucket, and run queries on the imported data. It is designed for users who want to integrate S3 data with SingleStore and explore the capabilities of pipelines for efficient data ingestion.

Demo Flow

Sample Table in SingleStore

Start by creating a table that will hold the data imported from S3.

In [1]:

%%sql
CREATE TABLE IF NOT EXISTS sample_table (
id INT,
name VARCHAR(255),
age INT,
address TEXT,
created_at TIMESTAMP
);

Create a Pipeline to Import Data from S3

You'll need to create a pipeline that pulls data from an S3 bucket into this table. This example assumes you have a CSV file in your S3 bucket.

Ensure that: You have access to the S3 bucket. Proper IAM roles or access keys are configured in SingleStore. The CSV file has a structure that matches the table schema.

Set Up Variables

Define the URL, REGION, ACCESS_KEY, and SECRET_ACCESS_KEY variables for integration, replacing the placeholder values with your own.

In [2]:

URL = 's3://your-bucket-name/your-data-file.csv'
REGION = 'your-region'
ACCESS_KEY = 'access_key_id'
SECRET_ACCESS_KEY = 'access_secret_key'

Using these identifiers and keys, execute the following statement.

In [3]:

%%sql
CREATE PIPELINE s3_import_pipeline
AS LOAD DATA S3 '{{URL}}'
CONFIG '{\"REGION\":\"{{REGION}}\"}'
CREDENTIALS '{\"AWS_ACCESS_KEY_ID\": \"{{ACCESS_KEY}}\",
\"AWS_SECRET_ACCESS_KEY\": \"{{SECRET_ACCESS_KEY}}\"}'
INTO TABLE sample_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
IGNORE 1 lines;

Start the Pipeline

To start the pipeline and begin importing the data from the S3 bucket:

In [4]:

%%sql
START PIPELINE s3_import_pipeline;

Select Data from the Table

Once the data has been imported, you can run a query to select it:

In [5]:

%%sql
SELECT * FROM sample_table LIMIT 10;

Check if all data of the data is loaded

In [6]:

%%sql
SELECT count(*) FROM sample_table

Conclusion

We have shown how to insert data from a Amazon S3 using Pipelines to SingleStoreDB. These techniques should enable you to integrate your Amazon S3 with SingleStoreDB.

Clean up

Remove the '#' to uncomment and execute the queries below to clean up the pipeline and table created.

Drop Pipeline

In [7]:

%%sql
#STOP PIPELINE s3_import_pipeline;
#DROP PIPELINE s3_import_pipeline;

Drop Data

In [8]:

%%sql
#DROP TABLE sample_table;

Details


About this Template

This notebook demonstrates how to create a sample table in SingleStore, set up a pipeline to import data from an Amazon S3 bucket.

Notebook Icon

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

Tags

starterloaddatas3

License

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