This topic describes how to create an OSS schema and table. In this example, data in dla_table_1 is consistent with data in the shipping table of an AnalyticDB for MySQL database, and data in dla_table_2 is consistent with data in the order_table table of the AnalyticDB for MySQL database.

  1. Log on to the Data Lake Analytics console.

  2. In the left-side navigation pane, click SQL access point, find the required VPC, and then click Log on in DMS in the Actions column.

    ​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 of the OSS bucket where the data file is stored. The directory must end with a forward slash (/).

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

Create an OSS table

​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/';​

What to do next

For more information, see Create an AnalyticDB for MySQL 2.0 schema and table.