AnalyticDB for MySQL Edition allows you to use external tables to import and export data. This topic describes how to use external tables of AnalyticDB for MySQL to import data from an ApsaraDB RDS for MySQL instance to an AnalyticDB for MySQL cluster.

Prerequisites

  • The ApsaraDB RDS for MySQL instance and the AnalyticDB for MySQL cluster are deployed in the same virtual private cloud (VPC). The CIDR block of the VPC is included in an IP address whitelist of the ApsaraDB RDS for MySQL instance. For more information, see Configure an IP address whitelist for 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 a database and 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)
    );
  • Optional. 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. Enable ENI

Procedure

  1. Connect to the AnalyticDB for MySQL cluster. For more information, see Connect to a cluster.
  2. Create a destination database. For more information, see Create a database.

    In this example, the database named adb_demo is used as the destination database in the AnalyticDB for MySQL cluster.

  3. Create an external mapping table.

    Execute the following statement to create an external mapping table named goods_external_table in the adb_demo destination 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"  
      }';
    Parameter Description
    ENGINE='mysql' The storage engine used for the external table. In this example, MySQL is used.
    TABLE_PROPERTIES The connection information used by the AnalyticDB for MySQL cluster to access the data in the ApsaraDB RDS for MySQL instance.
    url The VPC endpoint of the ApsaraDB RDS for MySQL instance and the name of the source database. In this example, the name of the source database is test_adb. For more information about how to view the VPC endpoint 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.

    The endpoint is in the "jdbc:mysql://mysql-vpc-address:3306/rds-database-name" format.

    Example: jdbc:mysql://rm-****************.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 account used to connect to the source database in the ApsaraDB RDS for MySQL instance.
    password The password of the preceding account.
  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.

    Execute the following statement:

    REPLACE INTO mysql_import_test
    SELECT * FROM goods_external_table;

What to do next

After the data is imported, you can log on to the adb_demo destination database in the AnalyticDB for MySQL cluster. Then, execute the following statement to check whether the data is imported from the source table to the mysql_import_test destination table:
SELECT * FROM mysql_import_test LIMIT 100;