Connecting ClickHouse with AWS RDS MySQL and Migrating Database

• Introduction
• Prerequisites
• Step 1: Creating a Table in ClickHouse
• Step 2: Connecting ClickHouse with AWS RDS MySQL
• Step 3: Migrating Data
• Step 4: Converting JSON to String
• Conclusion

#Introduction

In this comprehensive guide, we'll walk you through the process of connecting ClickHouse with AWS RDS MySQL, migrating data, and addressing a common scenario - converting MySQL JSON DataType column to a String DataType column. We'll ensure clarity for readers who may not be experienced in database management.

#Prerequisites

Before we begin, make sure you have the necessary access credentials for your AWS RDS MySQL instance and the ClickHouse environment.

#Step 1: Creating a Table in ClickHouse

Let's start by creating a table in ClickHouse that mirrors the structure of your MySQL table. Using the provided example, we'll create a table named default.feeds:

CREATE TABLE default.feeds (
    `id` UInt32,
    `uuid` String,
    `name` String,
    `identity_id` UInt32,
    `description` String,
    `tags` JSON,
    `parser` String,
    `created_at` DateTime DEFAULT now(),
    `updated_at` DateTime
)
ENGINE = MergeTree
PRIMARY KEY (id, created_at);

This ClickHouse table mirrors the structure of the MySQL table, with the tags column as a JSON data type.

#Step 2: Connecting ClickHouse with AWS RDS MySQL

Now, let's establish a connection between ClickHouse and AWS RDS MySQL. Utilize the following query, replacing the placeholders with your specific RDS details:

SELECT * FROM mysql('yourawsrdsdb.us-west-2.rds.amazonaws.com:3318', 'your_db', 'feeds', 'your_username', 'your_pwd')  
SETTINGS connect_timeout_with_failover_secure_ms = 1000;

This query allows ClickHouse to query the MySQL table, establishing a link between the two databases.

#Step 3: Migrating Data

With the connection established, we can now migrate data from AWS RDS MySQL to ClickHouse. Use the following query as an example:

INSERT INTO feeds
SETTINGS connect_timeout_with_failover_secure_ms = 1000
SELECT
    id,
    uuid,
    COALESCE(name, '') AS name,
    COALESCE(identity_id, 0) AS identity_id,
    COALESCE(description, '') AS description,
    COALESCE(tags, '{}') AS tags,
    COALESCE(parser, '') AS parser,
    created_at,
    updated_at
FROM mysql('yourawsrdsdb.us-west-2.rds.amazonaws.com:3318', 'your_db', 'feeds', 'your_username', 'your_pwd')
SETTINGS connect_timeout_with_failover_secure_ms = 1000;

This query retrieves data from the MySQL table and inserts it into the ClickHouse table.

#Step 4: Converting JSON to String

In some scenarios, you might want to convert a JSON DataType column to a String DataType column. Let's create another table for this purpose:

CREATE TABLE default.feeds_str (
    `id` UInt32,
    `uuid` String,
    `name` String,
    `identity_id` UInt32,
    `description` String,
    `tags` String,
    `parser` String,
    `created_at` DateTime DEFAULT now(),
    `updated_at` DateTime
)
ENGINE = MergeTree
PRIMARY KEY (id, created_at);

Now, let's populate this table by converting the tags column from JSON to String:

INSERT INTO feeds_str
SETTINGS connect_timeout_with_failover_secure_ms = 1000
SELECT
    id,
    uuid,
    COALESCE(name, '') AS name,
    COALESCE(identity_id, 0) AS identity_id,
    COALESCE(description, '') AS description,
    COALESCE(tags, '{}') AS tags,
    COALESCE(parser, '') AS parser,
    created_at,
    updated_at
FROM mysql('yourawsrdsdb.us-west-2.rds.amazonaws.com:3318', 'your_db', 'feeds', 'your_username', 'your_pwd')
SETTINGS connect_timeout_with_failover_secure_ms = 1000;

This query retrieves data from the MySQL table, converting the JSON column tags to a String and inserts it into the ClickHouse feeds_str table.

#Conclusion

Congratulations! You've successfully connected ClickHouse with AWS RDS MySQL, migrated data, and handled both JSON and String DataType scenarios. This step-by-step guide, designed for beginners, empowers you to navigate similar data migration tasks with confidence. Remember to adapt the queries to match your specific database configurations. Happy data migrating!