Migrating Data from Another Database

Things to Consider

For users starting with a database already hosted in a cloud provider like AWS, GCP, or Azure, you may already have your data sitting in object storage (if not, we walk through how to do this later). The feature called Pipelines, allows you to bring in data from any of these places very quickly. We’ll go through how to do this in a bit. Alternatively, you can use mysqldump, since SingleStore follows the MySQL wire protocol.

Now, of course, every database is different. Let’s talk a bit about database design differences.

Things to know, regardless of your current platform:

• SingleStore Helios can be deployed in any of the three major clouds, in any region – so you don’t have to worry about moving regions.

MySQL or MariaDB (for example, open source, AWS RDS MariaDB, Google Cloud SQL, Azure DB for MySQL, etc.)

• S2MS is MySQL wire-compatible, making it very easy to transition any of these above databases.

• S2MS is a distributed database system offering simple, powerful sharding capabilities through a shard key, which we will explain later.

• S2MS will not validate whether foreign keys exist when your data changes, though you can still have foreign keys in your app.

PostgreSQL (for example, open source, EnterpriseDB, CitusDB)

• S2MS primarily follows MySQL syntax, so you will have to re-write queries.

• S2MS is distributed, and provides robust support for all data types including JSON.

• S2MS will not validate whether foreign keys exist when your data changes, though you can still have foreign keys in your app.

MySQL Workbench

If you’re coming from MariaDB or MySQL, you may already be comfortable with Workbench. You can download MySQL Workbench here.

Note: When defining your connection, you will need to go to the Advanced tab and insert defaultAuth=mysql_native_password in the Others: field to ensure proper authentication.

MySQL Command Line

Within your Cloud Portal, your workspace details will include a MySQL Command section. This gives you an easy way to use the command line to connect to SingleStore. You can find the download for the MySQL shell here.

Note: When defining your connection, you will need to enter this at the MySQL command line: --defaultAuth=mysql_native_password to ensure proper authentication.

Data Types

If you’re familiar with relational databases, you may not need too much guidance on data types.

In addition to data types traditionally supported in relational databases, SingleStore also supports JSON and geospatial data types. Read about Data Types.

Shard Key

This key determines how data is distributed across the database workspace, and is critical to ensure that your data isn’t skewed. Skewed data can lead to longer query times. Data contained within unique values of your shard key will reside in individual partitions of the database.

• Users of commercial Postgres offerings like Citus will find this familiar to, but not exactly the same as, distribution keys.

• Users of MySQL in AWS RDS or GCP Cloud SQL will also be familiar with the concept of sharding.

• We offer free training on sharding if you’d like to learn more!

So how do I pick a shard key best for my application workload?

• If you have a primary key, make sure the shard key is a subset of it (because cardinality matters!).

• If your application queries include frequent joins or filters on a specific set of columns, make sure the shard key is a subset of those.

• Concurrency is very important with application workloads, so make sure your shard key allows your queries to be single partition, as explained below.

In this example, we use user_id as our shard key, which works nicely given its high cardinality as a part of this dataset. All records with the same user_id will be maintained together, which will improve query response time.

CREATE TABLE clicks (
        click_id BIGINT AUTO_INCREMENT,
        user_id INT,
        page_id INT,
        ts TIMESTAMP,
        SHARD KEY (user_id),
        SORT KEY (click_id, user_id))
);

Columnstore Key

In addition to identifying your shard key, it’s important to tell SingleStore how you would like to sort your data within each data segment. This helps enable segment elimination, which ensures a minimal amount of data needs to be read for each query. This also helps presort data for your queries.

So how do I pick a columnstore key best for my application workload?

• If you have common filter columns, make sure those are in the columnstore key.

• If you’re inserting in order by some column, it’s best to put that column first in the columnstore key.

• Lower cardinality columns should be first in the columnstore key.

In this example, we use price as our sort key, so items are sorted in order of that column when queried.

CREATE TABLE products (
     ProductId INT,
     Color VARCHAR(10),
     Price INT,
     Qty INT,
     SORT KEY (Price),
     SHARD KEY (ProductId)
);

Reference Tables

If you have small, infrequently changing table(s) that are required for joins, consider making them Reference Tables.

• Reference tables are a convenient way to recreate dimension tables that you may use in MySQL, MariaDB, or PostgreSQL.

• Reference tables are replicated to each leaf in the workspace ensuring data does not need to go across the network between partitions to join data.

• Reference table commands need to be run from the endpoint listed in the Cloud Portal.

SingleStore Pipelines

To use a Pipeline to import data into SingleStore, write a CREATE PIPELINE statement using our SQL Editor or a MySQL client.

A few things to consider:

• Make sure that your security settings in your blob storage will allow for access from SingleStore. For example, AWS S3 security settings can be found here.

• Make sure your buckets are not public, but you should be able to obtain an access and secret key using the AWS doc here.

• You can use wildcard notation when identifying your files from within the bucket.

Here’s an example of a CREATE PIPELINE statement:

CREATE PIPELINE library
AS LOAD DATA S3 'my-bucket-name'
CONFIG '{"region": "us-west-1", "suffixes": ["csv"]}'
CREDENTIALS '{"aws_access_key_id": "your_access_key_id", "aws_secret_access_key": "your_secret_access_key"}'
INTO TABLE `classic_books`
FIELDS TERMINATED BY ',';

GCP Documentation:

Load Data from Google Cloud Storage (GCS) Using a Pipeline

Bucket Security

Azure Documentation:

Azure Blob Pipelines Quickstart

Bucket Security

INSERT Statements

Perhaps you already have an application that is writing to your existing database, and you simply want to redirect the writes from that application to SingleStore. That’s great! Many of our users do this with the MySQL JDBC driver. See INSERT for more examples on writing your insert statements.

Running Queries

Hopefully at this point you have your data in SingleStore. You can check this by running some basic SELECT statements within the SQL Editor. Next, you may want to try out some queries that you were running with your last database. Generally, you’ll want to run queries twice to get a true understanding of the runtime as the first run must create and cache the query plan.

Visual Explain

One great feature of the Cloud Portal is our Visual Explain functionality. If you encounter a situation in which your query is taking longer than expected, highlight the desired query in the SQL Editor and click the Visual Explain icon to the left of the Run button. The icon resembles a tree. After clicking, you may then choose between EXPLAIN and PROFILE.

Once you identify a bottleneck, you should be able to make changes either to your schema or to your query itself in order to improve speed.

You can manually (non-visually) run EXPLAIN or PROFILE from any client; see the links above for details on the commands.

Benchmarking

If you’re moving over from any of the databases we’ve talked about so far, you are probably interested in improving performance. We have developed an easy-to-use tool for benchmarking called dbbench. You can check that out here.

Once you’ve installed the packages to your host machine, you can walk through this tutorial. All you will have to do is change the host from 127.0.0.1 to the endpoint listed for your workspace in the Cloud Portal (it should look something like: svc-xxx-dml.aws-virginia-1.db.memsql.com). The port will remain 3306, as listed. You can then move onto testing based on your application workload.