Loading data from MySQL® to a ClickHouse® data mart

Data Transfer enables you to migrate your database from a MySQL® source cluster to ClickHouse®.

To transfer data:

1. Prepare the source cluster.
2. Set up and activate the transfer.
3. Test your transfer.
4. Query data in ClickHouse®.

If you no longer need the resources you created, delete them.

Required paid resourcesRequired paid resources

• Managed Service for MySQL® cluster: computing resources allocated to hosts, size of storage and backups (see Managed Service for MySQL® pricing).
• Managed Service for ClickHouse® cluster: use of computing resources allocated to hosts, size of storage and backups (see Managed Service for ClickHouse® pricing).
• Public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).
• Each transfer: use of computing resources and number of transferred data rows (see Data Transfer pricing).

Getting startedGetting started

Set up the infrastructure:

Prepare the source clusterPrepare the source cluster

1. If you created the infrastructure manually, you must now prepare your source cluster.

2. Connect to the Managed Service for MySQL® source cluster.

3. Add test data to the database.

   1. Create a table named x_tab:

   CREATE TABLE x_tab
   (
       id INT,
       name TEXT,
       PRIMARY KEY (id)
   );
   

   1. Populate the table with data:

   INSERT INTO x_tab (id, name) VALUES
       (40, 'User1'),
       (41, 'User2'),
       (42, 'User3'),
       (43, 'User4'),
       (44, 'User5');
   

Set up and activate the transferSet up and activate the transfer

Test the transferTest the transfer

1. Wait for the transfer status to change to Replicating.

2. Verify that the data has been transferred from the source Managed Service for MySQL® cluster to the Managed Service for ClickHouse® database:

   1. Connect to the cluster via clickhouse-client.

   2. Run this query:

      SELECT * FROM <ClickHouse®_database_name>.x_tab
      

      Result:

      ┌─id─┬─name──┬─__data_transfer_commit_time─┬─__data_transfer_delete_time─┐
      │ 40 │ User1 │         1661952756538347180 │                           0 │
      │ 41 │ User2 │         1661952756538347180 │                           0 │
      │ 42 │ User3 │         1661952756538347180 │                           0 │
      │ 43 │ User4 │         1661952756538347180 │                           0 │
      │ 44 │ User5 │         1661952756538347180 │                           0 │
      └────┴───────┴─────────────────────────────┴─────────────────────────────┘
      

      The table also contains the following timestamp columns: __data_transfer_commit_time and __data_transfer_delete_time.

3. In the source MySQL® table x_tab, delete the row with id = 41 and update the row with id = 42:

   1. Connect to the Managed Service for MySQL® source cluster.

   2. Run the following queries:

      DELETE FROM x_tab WHERE id = 41;
      UPDATE x_tab SET name = 'Key3' WHERE id = 42;
      

4. Make sure the changes have been applied to the x_tab table on the ClickHouse® target:

   SELECT * FROM <ClickHouse®_database_name>.x_tab WHERE id in (41,42);
   

   Result:

   ┌─id─┬─name──┬─__data_transfer_commit_time─┬─__data_transfer_delete_time─┐
   │ 41 │ User2 │         1661952756538347180 │                           0 │
   │ 42 │ User3 │         1661952756538347180 │                           0 │
   └────┴───────┴─────────────────────────────┴─────────────────────────────┘
   ┌─id─┬─name─┬─__data_transfer_commit_time─┬─__data_transfer_delete_time─┐
   │ 41 │ ᴺᵁᴸᴸ │         1661953256000000000 │         1661953256000000000 │
   └────┴──────┴─────────────────────────────┴─────────────────────────────┘
   ┌─id─┬─name─┬─__data_transfer_commit_time─┬─__data_transfer_delete_time─┐
   │ 42 │ Key3 │         1661953280000000000 │                           0 │
   └────┴──────┴─────────────────────────────┴─────────────────────────────┘
   

Query data in ClickHouse®Query data in ClickHouse®

For table recovery, ClickHouse® targets with replication use the ReplicatedReplacingMergeTree and ReplacingMergeTree engines. The following columns are automatically added to each table:

• __data_transfer_commit_time: Time in TIMESTAMP format when this row was last updated.

• __data_transfer_delete_time: Time in TIMESTAMP format when this row was deleted from the source table. A value of 0 indicates that the row is still active.

  The __data_transfer_commit_time column is essential for the ReplicatedReplacedMergeTree engine. It tracks changes by inserting a new version of a row upon any update or deletion, timestamped with the operation's commit time. Consequently, a query by a primary key may return multiple row versions with different __data_transfer_commit_time values.

The source data can be added or deleted while the transfer is in the Replicating status. To ensure an SQL query by a primary key returns a single record, always filter on __data_transfer_delete_time when querying tables transferred to ClickHouse®. For example, to query the x_tab table, use the following syntax:

SELECT * FROM <ClickHouse®_database_name>.x_tab FINAL
WHERE __data_transfer_delete_time = 0;

To simplify the SELECT queries, create a view filtering rows by __data_transfer_delete_time. Use this view for all your queries. For example, to query the x_tab table, use the following syntax:

CREATE VIEW x_tab_view AS SELECT * FROM <ClickHouse®_database_name>.x_tab FINAL
WHERE __data_transfer_delete_time == 0;

Delete the resources you createdDelete the resources you created

Some resources are not free of charge. To avoid paying for them, delete the resources you no longer need:

ClickHouse® is a registered trademark of ClickHouse, Inc.