This topic describes how to use AnalyticDB MySQL external tables and the INSERT OVERWRITE INTO or INSERT INTO statement to import data from ApsaraDB RDS for MySQL to AnalyticDB MySQL .

Prerequisites

  • Test data is prepared. For more information, see Create an ApsaraDB RDS for MySQL instance.
    Note The ApsaraDB RDS for MySQL instance and AnalyticDB MySQL cluster must be in the same VPC. You must add the CIDR block of this VPC to the whitelist of the ApsaraDB RDS for MySQL instance. For more information, see Control access to an ApsaraDB RDS for MySQL instance.

    In this example, a table named goods is created in the ApsaraDB RDS for MySQL database named test_adb.

    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)
    )                 
  • An AnalyticDB MySQL cluster is created, a whitelist is configured, and an account and a database are created. For more information, see Create an AnalyticDB MySQL cluster.
Note If AnalyticDB MySQL cluster is in elastic mode, log on to the AnalyticDB MySQL console and view the cluster information. Enable Elastic Network Interface (ENI) in the Network Information section.net

Background information

The MySQL database in this topic can be an ApsaraDB RDS for MySQL database or a self-managed ECS-hosted MySQL database.

Procedure

  1. Connect to an AnalyticDB MySQL cluster and the required database. For more information, see Connect to a cluster.
  2. Create an external mapping table named goods_external_table in the adb_demo database. For more information, see CREATE TABLE.
    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' Specifies the table as an external table whose storage engine is MySQL.
    TABLE_PROPERTIES Specifies the connection information used by the AnalyticDB MySQL database to access the data in the ApsaraDB RDS for MySQL database.
    url The VPC endpoint of the ApsaraDB RDS for MySQL instance.

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

    Note AnalyticDB MySQL can access ApsaraDB RDS for MySQL only through VPC endpoints.

    You can log on to the ApsaraDB RDS for MySQL console and view the internal endpoint of the ApsaraDB RDS for MySQL instance on the Basic Information page.

    tablename The source table in the ApsaraDB RDS for MySQL database, which is the goods table in this example.
    username The account used to connect to the ApsaraDB RDS for MySQL database.
    password The password of the account used to connect to the ApsaraDB RDS for MySQL database.
  3. Create a destination table named mysql_import_test in the adb_demo database to store the data imported from the ApsaraDB RDS for MySQL database. For more information, see CREATE TABLE.
    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)                    
  4. Execute the INSERT statement to import the data from ApsaraDB RDS for MySQL to AnalyticDB MySQL .
    • Execute the INSERT INTO statement to import data.
      insert into mysql_import_test
      select * from goods_external_table;                           
    • Execute the INSERT OVERWRITE INTO statement to import data.
      insert overwrite into mysql_import_test
      select * from goods_external_table