AnalyticDB:Export data to ApsaraDB RDS for MySQL

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.

• 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 accounts and databases and Connect to an ApsaraDB RDS for MySQL instance.

  In this example, a database named test_adb is used as the destination database in the ApsaraDB RDS for MySQL instance. A table named courses is created in the database. The table is used to store data that is exported from the AnalyticDB for MySQL cluster. The following statement is used to create the destination table:

  CREATE TABLE courses (
      id bigint NOT NULL,
      name varchar(32) NOT NULL,
      grade varchar(32) NOT NULL,
      submission_date  timestamp NOT NULL,
      PRIMARY KEY (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 source database. For more information, see Create a database.

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

3. Create a source table and insert data into the source table.

   Execute the following statement to create a source table named courses in the adb_demo source database. Then, export data in the source table to the courses table in the test_adb destination database.

   CREATE TABLE courses (
   id bigint AUTO_INCREMENT,
   name varchar NOT NULL,
   grade varchar DEFAULT '1st Grade',
   submission_date timestamp
   ) DISTRIBUTED BY HASH(id);

   Execute the following statement to insert a row of data into the courses source table:

   INSERT INTO courses (name,submission_date) VALUES("Jams",NOW());

4. Create an external table.

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

    CREATE TABLE IF NOT EXISTS courses_external_table(
    id bigint NOT NULL,
    name varchar(32) NOT NULL,
    grade varchar(32) NOT NULL,
    submission_date  timestamp NOT NULL,
    PRIMARY KEY (id)
    )
   ENGINE='mysql'  
   TABLE_PROPERTIES='{  
   "url":"jdbc:mysql://mysql-vpc-address:3306/test_adb",  
   "tablename":"courses",  
   "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 of the ApsaraDB RDS for MySQL instance and the name of the destination database. In this example, the name of the destination database is test_adb. For more information about how to view the VPC endpoint of the ApsaraDB RDS for MySQL instance, see View and manage instance endpoints and ports. Format: "jdbc:mysql://mysql-vpc-address:3306/ecs-database-name". Example: jdbc:mysql://192.168.128.***:3306/test_adb.
   tablename	The name of the destination table in the destination ApsaraDB RDS for MySQL database. In this example, courses is used.
   username	The name of the account that is used to connect to the destination database in the ApsaraDB RDS for MySQL instance.
   password	The password of the preceding account.
   charset	The character set that is used by the source database. Default value: utf8. Valid values: gbk utf8 utf8mb4

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

   Execute the following statement:

   REPLACE INTO courses_external_table
   SELECT * FROM courses;