Background information and preparations

Preparations

1. Activate DLA, AnalyticDB for MySQL 2.0, and OSS, and ensure that they are deployed in the same region.

2. Create a VPC endpoint in DLA. For more information, see Create an endpoint.

3. Create a VPC in AnalyticDB for MySQL 2.0. DLA can then use the VPC endpoint of AnalyticDB for MySQL 2.0 to connect to AnalyticDB for MySQL 2.0.

4. If your AnalyticDB for MySQL 2.0 database is not in the same VPC as DLA, you can use a MySQL CLI tool to connect to your AnalyticDB for MySQL 2.0 database and execute the following SQL statement to modify the VPC of your AnalyticDB for MySQL 2.0 database. After that, DLA can connect to your AnalyticDB for MySQL 2.0 database. Skip this step if your AnalyticDB for MySQL 2.0 database is in the same VPC as DLA.

   ALTER DATABASE ads_database set zone_id='cn-shanghai-d' vpc_id='vpc-uf6wxkgst74es59wqareb' vswitch_id='vsw-uf6m7k4fcq3pgd0yjfdnm';

5. Create streaming tables in your AnalyticDB for MySQL 2.0 database. These tables are used to store OSS data.

   In this step, the shipping and order_table tables are created. The following code snippets show the table schemas.

   ​CREATE TABLE ads_database.shipping (
   id bigint NOT NULL COMMENT '',
   origin_state varchar NOT NULL COMMENT '',
   origin_zip varchar NOT NULL COMMENT '',
   destination_state varchar NOT NULL COMMENT '',
   destination_zip varchar NOT NULL COMMENT '',
   package_weight int NOT NULL COMMENT '',
   PRIMARY KEY (id)
   )
   PARTITION BY HASH KEY (id) PARTITION NUM 23
   TABLEGROUP ads
   OPTIONS (UPDATETYPE='realtime')
   COMMENT ''​

   CREATE TABLE ads_database.order_table (
      customer_id bigint NOT NULL COMMENT '',
      order_id varchar NOT NULL COMMENT '',
      order_time date NOT NULL COMMENT '',
      order_amount double NOT NULL COMMENT '',
      order_type varchar NOT NULL COMMENT '',
      address varchar NOT NULL COMMENT '',
      city varchar NOT NULL COMMENT '',
      order_season bigint COMMENT '',
      PRIMARY KEY (customer_id)
    )
    PARTITION BY HASH KEY (customer_id) PARTITION NUM 32
    TABLEGROUP ads
    OPTIONS (UPDATETYPE='realtime')
    COMMENT '' 

Precautions

• AnalyticDB for MySQL 2.0 uses primary key logic. If INSERT FROM SELECT is executed and the extract, transformation, load (ETL) task fails, you must perform the ETL task again.

• When you use DLA to query data in your AnalyticDB for MySQL 2.0 database, latency may occur. The latency varies based on the elastic compute unit (ECU) specifications of your AnalyticDB for MySQL 2.0 database.

• We recommend that you split data into multiple files to perform ETL tasks. For example, the size of data stored in OSS is 200 GB, and data is split into 100 folders, each of which stores 2 GB of data. 100 tables are created in DLA and ETL tasks are performed for each table. If an ETL task fails, you need only to retry this task.

• After OSS data is written into your AnalyticDB for MySQL 2.0 database, you need only to modify the VPC endpoint of the database. Otherwise, only DLA can use the current VPC endpoint to access your AnalyticDB for MySQL 2.0 database.

  ALTER DATABASE ads_database set zone_id='cn-shanghai-d' vpc_id='vpc-uf6wxkgst*****' vswitch_id='vsw-uf6m7k4fcq3pgd0y****';