ApsaraDB for ClickHouse:Import data from an ApsaraDB RDS for MySQL instance

Prerequisites

• A destination ApsaraDB for ClickHouse cluster is created. For more information about how to create a cluster, see Create a cluster.
• A username and a password of a database account are created for the destination ApsaraDB for ClickHouse cluster. For more information about how to create a database account, see Create an account.
• A source ApsaraDB RDS for MySQL instance is created. For more information about how to create an ApsaraDB RDS for MySQL instance, see Create an ApsaraDB RDS for MySQL instance.
• A username and a password of a database account are created for the ApsaraDB RDS for MySQL instance. For more information about how to create a database account, see Create databases and accounts for an ApsaraDB RDS for MySQL instance.
• The IP address of the ApsaraDB for ClickHouse cluster is added to the whitelist of the ApsaraDB RDS for MySQL instance. For more information about how to add an IP address to a whitelist, see Configure an IP address whitelist for an ApsaraDB RDS for MySQL instance.
  Note You can execute the select * from system.clusters; statement to query the IP address of the ApsaraDB for ClickHouse cluster.

Precautions

Make sure that the source ApsaraDB RDS for MySQL instance and the destination ApsaraDB for ClickHouse cluster are deployed in the same region and use the same virtual private cloud (VPC).

Procedure

1. Create a table in the ApsaraDB RDS for MySQL instance and write data to the table.
   1. Log on to the ApsaraDB RDS console.
   2. In the left-side navigation pane, click Instances.
   3. On the Instances page, click the ID of the instance in which you want to create a table.
   4. On the Basic Information page, click Log On to Database in the upper-right corner.
   5. In the Log on to Database Instance dialog box, enter the username and password of your database account and click Login.
   6. Execute a statement to create a table. In the following example, the mysql_test_table table is created in the testdb database.

      CREATE TABLE testdb.mysql_test_table (
        v1 Int NOT NULL,
        v2 Int DEFAULT NULL,
        v3 Float DEFAULT NULL
      ) ENGINE=InnoDB;

   7. Write data to the table in the ApsaraDB RDS for MySQL instance.

      Insert INTO testdb.mysql_test_table values (4,4,4.0),(1,1,2.0),(1,1,0.0),(4,1,2.0),(7,1,3.0);

2. Create a table in ApsaraDB for ClickHouse.
   1. Log on to the ApsaraDB for ClickHouse console.
   2. In the upper-left corner of the page, select the region where the destination ApsaraDB for ClickHouse cluster is deployed.
   3. On the Clusters page, click the Default Instances tab. Find the cluster that you want to manage and click the ID of the cluster.
   4. On the Cluster Details page, click Log On to Database in the upper-right corner.
   5. In the Log on to Database Instance dialog box, enter the username and password of your database account and click Login.
   6. Execute a statement to create a table. In the following example, the mysql_test_table table is created in the cktest database.

      create table cktest.clickhouse_test_table ON CLUSTER default (
      v1 Int32, 
      v2 Nullable(Int32),
      v3 Nullable(Float32)
      ) ENGINE = MergeTree ORDER BY v1;

      Note

      • The data types of a table in ApsaraDB for ClickHouse must be mapped to the data types of a table in ApsaraDB RDS for MySQL. For more information about the mappings, see Data type mappings.
      • If NOT NULL is not specified for a column in the statement that you use to create a table in the ApsaraDB RDS for MySQL instance, the value of the column can be NULL. The corresponding column in the statement that you use to create a table in ApsaraDB for ClickHouse is identified with Nullable.
   7. (Optional) Create a distributed table named clickhouse_test_table_distributed.
      Note If you need to import data only from ApsaraDB RDS for MySQL to the local table, you can skip this step.

      CREATE TABLE clickhouse_test_table_distributed ON CLUSTER default
       AS clickhouse_test_table
      ENGINE = Distributed(default, default, clickhouse_test_table, rand());

3. Import data from an ApsaraDB RDS for MySQL instance to ApsaraDB for ClickHouse.
   Syntax:

   insert into <Name of the table in ApsaraDB for ClickHouse> select * from mysql('<Endpoint of the ApsaraDB RDS for MySQL instance>:<Port>', '<ApsaraDB RDS for MySQL database name>','<Name of the table in the ApsaraDB RDS for MySQL instance>', '<Database account of ApsaraDB RDS for MySQL>', '<Password of the database account of ApsaraDB RDS for MySQL>')

   Note For information about how to obtain the endpoint and port of the ApsaraDB RDS for MySQL instance, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for MySQL instance.

   Example:

   insert into clickhouse_test_table_distributed select * from mysql('rm-bp16t9h3999xb****.mysql.rds.aliyuncs.com:3306','testdb','mysql_test_table','test','123456Aa');

4. Query the data imported to ApsaraDB for ClickHouse.
   Enter the following query statement and click Execute(F8) to check whether the data is imported.

   select * from clickhouse_test_table_distributed;

   Note If you import data to a local table, replace the name of the distributed table clickhouse_test_table_distributed in the query statement with the name of the local table clickhouse_test_table, and execute the query statement.

   The following output is returned:

   ┌─v1─┬─v2─┬─v3──┐
   │  4 │  4 │ 4.0 │  
   │  1 │  1 │ 2.0 │ 
   │  1 │  1 │ 0.0 │
   │  4 │  1 │ 2.0 │
   │  7 │  1 │ 3.0 │
   └────┴────┴─────┘

Data type mappings