You must create an AnalyticDB for MySQL schema and an external AnalyticDB for MySQL table in Data Lake Analytics (DLA) before you can write data from Object Storage Service (OSS) to AnalyticDB for MySQL by using the INSERT INTO SELECT statement.

Create a schema

  1. Log on to the DLA console.

  2. In the left-side navigation pane, choose Serverless Presto > SQL access point. In the VPC Network section, find your Virtual Private Cloud (VPC) and click Log on in DMS in the Actions column. In the Data Management (DMS) console, create an AnalyticDB for MySQL schema.

    CREATE SCHEMA ads_database_schema WITH DBPROPERTIES 
    ( 
    CATALOG = 'adb3', 
    LOCATION = 'jdbc:mysql://am-bp1m76i0ivxpc60v313****.ads.aliyuncs.com:3306/adb_demo',
    VPC_ID='your-adb-vpc_id',
    INSTANCE_ID='your-adb-instance_id',
    USER='account1',
    PASSWORD='Account1'
    );
    Parameter Description
    CATALOG = 'adb3' The AnalyticDB for MySQL schema that you want to create
    LOCATION The connection information of the AnalyticDB for MySQL cluster
    VPC_ID The VPC ID of the AnalyticDB for MySQL cluster
    INSTANCE_ID The ID of the AnalyticDB for MySQL cluster
    USER The username used to access databases in the AnalyticDB for MySQL cluster
    PASSWORD The password of the username

Create a table

You can create an external AnalyticDB for MySQL table in DLA based on the following scenarios:

  • Scenario 1: The name, field names, or field types of the table created in DLA are different from those of the table created in AnalyticDB for MySQL.

  • Scenario 2: The name, field names, and field types of the table created in DLA are the same as those of the table created in AnalyticDB for MySQL.

Scenario 2 (Recommended)

The following example demonstrates how to create a table named shipping in DLA.

CREATE EXTERNAL TABLE 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)
)

Scenario 1

The following example demonstrates how to create a table named order_table1 that corresponds to order_table in AnalyticDB for MySQL. Some field names in order_table1 are different from the field names in order_table. The data type of the order1_id column in order_table1 is different from the data type of the order_id column in order_table.

CREATE EXTERNAL TABLE order_table1 (
  customer1_id bigint NOT NULL COMMENT '',
  order1_id bigint NOT NULL COMMENT '',
  order1_time date NOT NULL COMMENT '',
  order1_amount double NOT NULL COMMENT '',
  order1_type varchar NOT NULL COMMENT '',
  address1 varchar NOT NULL COMMENT '',
  city1 varchar NOT NULL COMMENT '',
  order1_season bigint COMMENT '',
  PRIMARY KEY (customer1_id)
)
tblproperties (
   table_mapping = 'adb_demo.order_table', 
   column_mapping = 'customer1_id,customer_id; order1_id,order_id; order1_time:order_time, 
   order1_amount:order_amount, order1_type:order_type, address1:address, 
   city1:city,order1_season:order_season'
);
Parameter Description
tblproperties The mapping between the table in DLA and the source table in AnalyticDB for MySQL
table_mapping The mapping between table names
column_mapping The mapping between columns