Migrating databases from MySQL® to ClickHouse® using Yandex Data Transfer

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, which includes computing resources allocated to hosts, storage and backup size (see Managed Service for MySQL® pricing).
• Managed Service for ClickHouse® cluster, which includes the use of computing resources allocated to hosts, storage and backup size (see Managed Service for ClickHouse® pricing).
• Public IP addresses if public access is enabled for cluster hosts (see Virtual Private Cloud pricing).
• Each transfer, which includes the use of computing resources and the number of transferred data rows (see Data Transfer pricing).

Getting startedGetting started

Set up your infrastructure:

Prepare the source clusterPrepare the source cluster

1. If you created the infrastructure manually, set up your source cluster.

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

3. Populate the database with test data.

   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 your transferTest your transfer

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

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

   1. Connect to the cluster using 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 the row was updated to this value, in TIMESTAMP format.

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

  The __data_transfer_commit_time column is essential for the ReplicatedReplacedMergeTree engine. If a record is deleted or updated, a new row gets inserted with a value in this column. Querying by the primary key alone returns several records with different __data_transfer_commit_time values.

The source data can be added or deleted while the transfer is in Replicating status. For standard SQL command behavior, where the primary key returns a single record, add filtering by the __data_transfer_delete_time column 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

To reduce the consumption of resources, delete those you do not need:

ClickHouse® is a registered trademark of ClickHouse, Inc.