AnalyticDB:Use external tables to import data to Data Warehouse Edition

Prerequisites

• An ApsaraDB RDS for MySQL instance and an AnalyticDB for MySQL cluster are deployed in the same virtual private cloud (VPC), which means that the VPC ID of the instance is the same as that of the cluster.

  Note

  • To obtain the VPC ID of the instance, log on to the ApsaraDB RDS console and click the instance ID. On the Database Connection page, you can view the VPC ID in the Network Type field.

  • To obtain the VPC ID of the cluster, log on to the AnalyticDB for MySQL console, go to the Data Warehouse Edition (V3.0) tab, and then click the cluster ID. In the Network Information section of the Cluster Information page, you can view the VPC ID of the AnalyticDB for MySQL cluster.

• The VPC CIDR block of the AnalyticDB for MySQL cluster is added to an IP address whitelist of the ApsaraDB RDS for MySQL instance. For more information, see Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

• A database is created on the ApsaraDB RDS for MySQL instance, and you have logged on to the database. Test data is prepared. For more information, see Create databases and accounts for an ApsaraDB RDS for MySQL instance and Use a database client or the CLI to connect to an ApsaraDB RDS for MySQL instance.

  In this example, a database named test_adb is used as the source database in the ApsaraDB RDS for MySQL instance. A source table named goods is created in the database. The following statement is used to create the source table:

  CREATE TABLE goods (
  goods_id bigint(20) NOT NULL,
  price double NOT NULL,
  class bigint(20) NOT NULL,
  name varchar(32) NOT NULL,
  update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (goods_id)
  );

• If the AnalyticDB for MySQL cluster is in elastic mode, you must turn on ENI in the Network Information section of the Cluster Information page.

Procedure

1. Connect to the AnalyticDB for MySQL cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.

2. Create a database. For more information, see Create a database.

   In this example, a database named adb_demo is created in the AnalyticDB for MySQL cluster.

3. Create an external table.

   Execute the following statement to create an external table named goods_external_table in the adb_demo database:

   CREATE TABLE IF NOT EXISTS goods_external_table (
           goods_id bigint(20) NOT NULL,
           price double NOT NULL,
           class bigint(20) NOT NULL,
           name varchar(32) NOT NULL,
           update_time timestamp,
           PRIMARY KEY (goods_id)
    )
           ENGINE='mysql'  
           TABLE_PROPERTIES='{  
           "url":"jdbc:mysql://mysql-vpc-address:3306/test_adb",  
           "tablename":"goods",  
           "username":"mysql-user-name",  
           "password":"mysql-user-password",
           "charset":"utf8"
     }';

   Parameter	Description
   ENGINE='mysql'	The storage engine that is used for the external table. In this example, MySQL is used.
   TABLE_PROPERTIES	The connection information that is used by the AnalyticDB for MySQL cluster to access the data in the ApsaraDB RDS for MySQL instance.
   url	The VPC endpoint that is used to connect to the source database in the ApsaraDB RDS for MySQL instance. In this example, the test_adb database is used. For information about how to view the endpoints of an ApsaraDB RDS for MySQL instance, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for MySQL instance. Format: "jdbc:mysql://mysql-vpc-address:3306/rds-database-name". Example: jdbc:mysql://rm-bp1hem632****.mysql.rds.aliyuncs.com:3306/test_adb.
   tablename	The name of the source table in the ApsaraDB RDS for MySQL instance. In this example, goods is used.
   username	The name of the account that is used to connect to the source database in the ApsaraDB RDS for MySQL instance.
   password	The password of the database account.
   charset	The character set that is used by the source database in the ApsaraDB RDS for MySQL instance. Valid values: gbk utf8 (default) utf8mb4

4. Create a destination table.

   Execute the following statement to create a destination table named mysql_import_test in the adb_demo database. The destination table is used to store the data that is imported from the ApsaraDB RDS for MySQL instance.

   CREATE TABLE IF NOT EXISTS  mysql_import_test (
           goods_id bigint(20) NOT NULL,
           price double NOT NULL,
           class bigint(20) NOT NULL,
           name varchar(32) NOT NULL,
           update_time timestamp,
           PRIMARY KEY (goods_id)
    )
   DISTRIBUTED BY HASH(goods_id);

5. Import data from the ApsaraDB RDS for MySQL instance to the AnalyticDB for MySQL cluster.

   REPLACE INTO mysql_import_test
   SELECT * FROM goods_external_table;