DLA serves as a hub for data processing and can read data stored in Object Storage Service (OSS). After OSS data is cleansed, DLA writes the data to AnalyticDB for MySQL 2.0. AnalyticDB for MySQL 2.0 then uses its powerful compute engine to compute and analyze the data in milliseconds.

Note DLA supports the following OSS data formats: LOG, CSV, and JSON.

After you create the same virtual private cloud (VPC) for AnalyticDB for MySQL 2.0 and DLA based on the region where DLA and AnalyticDB for MySQL 2.0 are deployed, DLA uses the VPC endpoint of AnalyticDB for MySQL 2.0 to connect to AnalyticDB for MySQL 2.0.

China (Hangzhou) cn-hangzhou-g vpc-bp1g66t4f0onrvbht2et5 vsw-bp1nh5ri8di2q7tkof474
China (Shanghai) cn-shanghai-d vpc-uf6wxkgst74es59wqareb vsw-uf6m7k4fcq3pgd0yjfdnm
China (Beijing) cn-beijing-g vpc-2zeawsrpzbelyjko7i0ir vsw-2zea8ct4hy4hwsrcpd52d
China (Zhangjiakou) cn-zhangjiakou-a vpc-8vbpi1t7c0devxwfe19sn vsw-8vbjl32xkft0ewggef6g9
China (Shenzhen) cn-shenzhen-a vpc-wz9622zx341dy24ozifn3 vsw-wz91ov6gj2i4u2kenpe42
Singapore (Singapore) ap-southeast-a vpc-t4n3sczhu5efvwo1gsupf vsw-t4npcrmzzk64r13e3nhhm
UK (London) eu-west-1a vpc-d7ovzdful8490upm8b413 vsw-d7opmgixr2h34r1975s8a
Japan (Tokyo) ap-northeast-1b vpc-6weiev04rkbhguxqnnh2c vsw-6wei487pduzx7753blrfr
US (Virginia) us-east-1a vpc-0xiarsc9sd2sen1kmii71 vsw-0xi31nno1mx2rb1sk1u3k
US (Silicon Valley) us-west-1a vpc-rj98sujw7ms8kkpdv9hak vsw-rj9o2vqz0xjbj9cmjqeiv
China (Hong Kong) cn-hongkong-b vpc-j6cotfjiboiskto4htqga vsw-j6cv4uyb6qybxnpjzph2p
Germany (Frankfurt) eu-central-1b vpc-gw8mxe263blkfaurn81ej vsw-gw8j7409kagfk3rdjwdak
India (Mumbai) ap-south-1a vpc-a2d9k64h6zy80akzvnhyf vsw-a2d6wrycvxb71rszsnpc8
Australia (Sydney) ap-southeast-2b vpc-p0wsvf29xy47hbsw9440g vsw-p0w5t4q4pmu8pjj3z5s81

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 or client t1916490.html#task792 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****';