In this topic, data in dla_table_1 is the same as that in the shipping table of your AnalyticDB for MySQL 2.0 database, and data in dla_table_2 is the same as that in the order_table table of your AnalyticDB for MySQL 2.0 database.

  1. Log on to the DLA console.

  2. In the left-side navigation pane, click SQL access point, find the required VPC in the VPC Network section, and then click Log on in DMS in the Actions column. In the DMS console, create an OSS schema.

    CREATE SCHEMA oss_data_schema with DBPROPERTIES(
       catalog='oss',
      location = 'oss://oss_bucket_name/table/'
      );​
    • catalog='oss': indicates the OSS schema you created.

    • location: indicates the directory in the OSS bucket. This directory is used to save data files and must end with a forward slash (/).

      The locations that store the data files of subsequent tables must be under this directory. In this example, the table you created is the object that stores files in the OSS bucket.

Create OSS tables

​CREATE EXTERNAL TABLE IF NOT EXISTS dla_table_1 (
  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 ''
) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
STORED AS TEXTFILE 
LOCATION 'oss://oss_bucket_name/table/';​
​CREATE EXTERNAL TABLE IF NOT EXISTS dla_table_2 (
   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)
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' 
STORED AS TEXTFILE 
LOCATION 'oss://oss_bucket_name/table/';​