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

Prerequisites

  • Test data is prepared. For more information, see Quick start of ApsaraDB RDS for MySQL.
    Note The ApsaraDB RDS for MySQL instance and AnalyticDB for 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 Configure a whitelist for an ApsaraDB RDS 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 for MySQL cluster is created, a whitelist is configured, and an account and a database are created. For more information, see Quick start of AnalyticDB for MySQL.

Background information

Note The MySQL database in this topic can be an ApsaraDB RDS for MySQL database or a user-created ECS-hosted MySQL database.

Procedure

  1. Connect to an AnalyticDB for MySQL cluster and the target 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 data engine is MySQL.
    TABLE_PROPERTIES Specifies the connection information used by the AnalyticDB for 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 for 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 in ApsaraDB RDS for MySQL to AnalyticDB for 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