Integrate data from MemSQL (SingleStore) to Amazon Redshift using Matillion

Creating a User in SingleStore

To create a user in a SingleStore database, you typically use the

CREATE USER

1. Connect to the Database

Start by connecting to your SingleStore instance using a SQL client or the command line:

mysql -u root -p -h <hostname> -P <port>

Replace

<hostname>

<port>

2. Create the User

Execute the following SQL command to create a new user:

CREATE USER 'new_user'@'%' IDENTIFIED BY 'StrongPassword!';

• Replace

  'new_user'

  with the desired username.
• The

  '%'

  wildcard allows the user to connect from any host. For increased security, you may specify a particular IP address or hostname instead.
• Replace

  'StrongPassword!'

  with a securely chosen password.

Example

CREATE USER 'analytics'@'10.20.30.40' IDENTIFIED BY 'Comp!exPa$$w0rd';

This command creates the user

analytics

10.20.30.40

3. Grant Privileges (Optional)

By default, the user has no permissions. Grant appropriate privileges with the

GRANT

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'new_user'@'%';

To grant all privileges on a database:

GRANT ALL PRIVILEGES ON database_name.* TO 'new_user'@'%';

4. Apply Privilege Changes

After modifying privileges, run the following command to flush the privileges (optional, typically not required in SingleStore, but safe to include if integrating with legacy procedures):

FLUSH PRIVILEGES;

5. Verify User Creation

To list all users:

SELECT User, Host FROM INFORMATION_SCHEMA.USER_ATTRIBUTES;

The new user should appear in the results.

For more information, see the SingleStore documentation on user management.

Installing the JDBC Driver

At the time of writing, the SingleStore JDBC driver is not included by default in the Matillion Data Productivity Cloud due to licensing or redistribution restrictions. As a result, you must manually download and install the driver in order to connect Matillion to your SingleStore database.

1. Downloading the JDBC Driver

Navigate to the official SingleStore documentation at:
https://docs.singlestore.com/managed-service/en/reference/jdbc-driver.html

Here you will find download links for the SingleStore JDBC driver. When choosing a driver, prioritize a Type 4 JDBC driver, as this is a pure Java implementation and is best suited for JDBC-compatible applications like Matillion.

2. Preparing for Installation

Download the JDBC driver

.jar

3. Uploading the JDBC Driver to Matillion Data Productivity Cloud

Instructions for installing external JDBC drivers into Matillion can be found at:
https://docs.matillion.com/data-productivity-cloud/agent/docs/uploading-external-drivers/

Follow these steps as described in the documentation:

• Access your Matillion instance and navigate to the section for managing or uploading external drivers within your Matillion agent.
• Use the Matillion interface to upload the previously downloaded SingleStore JDBC

  .jar

  file.
• Ensure the driver appears in the list of available drivers and that it is correctly recognized by the platform.

If the Matillion environment is managed by your IT or DevOps team, you may need to coordinate with them to complete the installation.

4. Connecting to SingleStore Using Matillion

After the driver is successfully uploaded and installed, consult the detailed instructions for configuring and using database connectivity within Matillion at:
https://docs.matillion.com/data-productivity-cloud/designer/docs/database-query/

This documentation covers how to establish database connections, specify driver properties, and build queries using Matillion's interface to connect to your SingleStore instance.

By following these steps, you can enable SingleStore connectivity in your Matillion Data Productivity Cloud environment.

Checking network connectivity

To ensure successful integration between Matillion Data Productivity Cloud and your SingleStore database, you must allow incoming connections to your SingleStore instance based on your deployment type:

• Full SaaS agent configuration:
  Configure your SingleStore firewall or network security settings to permit incoming connections from the set of IP addresses used by Matillion's SaaS agents. The current list of these IP addresses can be found here: Matillion SaaS allowed IP addresses.

• Hybrid SaaS deployment:
  In this scenario, your Matillion agent operates within your own environment, typically inside a virtual private cloud (VPC). You must configure the SingleStore database to accept incoming connections from the relevant VPC IP ranges. To verify your setup or to troubleshoot connectivity, you can use the network access check utilities provided by Matillion: Matillion network access utilities.

Note:
If your SingleStore database is referenced using a DNS name (instead of a static IP address), ensure that the Matillion agent (whether Full SaaS or Hybrid SaaS) is able to resolve the DNS address to the correct IP. This may involve configuring DNS resolution within your environment or network as required.

Querying Data from a SingleStore Database

This guide outlines how to execute SQL queries against a SingleStore database, manage datatype conversion with Redshift, and implement efficient data extraction patterns—focusing on initial and incremental data loads.

1. Example: Query Data using SQL SELECT Statements

You can query data from SingleStore using standard SQL syntax. Below are some example queries:

``` -- Select all columns from the 'customers' table SELECT * FROM customers;

-- Get specified columns from the 'orders' table SELECT order_id, customer_id, amount, status FROM orders;

-- Filter results with a WHERE clause SELECT product_id, product_name, price FROM products WHERE price > 100 AND in_stock = TRUE;

-- Aggregate data SELECT region, COUNT(*) AS total_customers FROM customers GROUP BY region; ```

2. Datatype Conversion: SingleStore & Redshift

When moving data between SingleStore and Amazon Redshift, keep in mind that datatype conversion may be required. Common considerations include:

VARCHAR

VARCHAR

BIGINT

BIGINT

DATETIME

TIMESTAMP

BOOLEAN

BOOLEAN

FLOAT

DOUBLE PRECISION

Ensure any transformations account for these differences prior to or during extraction/ingestion.

3. Best Practice: Initial vs Incremental Data Loads

For both efficiency and data quality, it is best to design your process as:

1. Once-off initial load – extracts all data, typically performed only the first time.
2. Recurring incremental loads – extract only new or modified data since the last load.

You can use the same Matillion "Database Query" component for both steps.

A. Initial Load (No Filter)

During an initial load, query the entire table—no filtering clause is necessary:

SELECT *
FROM orders;

B. Incremental Load (With Filter)

Subsequent loads use a filter condition (often on a timestamp or an auto-incrementing ID) to retrieve only new or changed rows:

SELECT *
FROM orders
WHERE updated_at > '2024-06-01 00:00:00';

Use your replication checkpoint value in the filter clause. This pattern improves efficiency by minimizing scanned and transferred data.

Tip: For further guidance on incremental load strategies using Matillion, refer to:
Incremental Load Data Replication Strategy – Matillion Exchange

Data Integration Architecture

Loading data into Amazon Redshift in advance of integration exemplifies the "divide and conquer" strategy by separating the overall process into distinct stages—first, raw data ingestion (load), followed by integration (transform). This approach is a key advantage of the ELT (Extract, Load, Transform) architecture, as it allows organizations to decouple the heavy-lifting of data ingestion from the complexity of integration logic. Effective data integration relies on transformation, and the most robust way to achieve this is through the use of data transformation pipelines that orchestrate, schedule, and manage each step. With ELT, all transformations and integration processes take place directly within the Redshift database itself. This provides fast, on-demand, and highly scalable data processing, eliminating the need for separate transformation servers and allowing organizations to avoid additional costs and complexity associated with external data-processing infrastructure.