To write data from Object Storage Service (OSS) to AnalyticDB for MySQL by executing the INSERT INTO SELECT clause, you must create an AnalyticDB for MySQL schema and its external tables in Data Lake Analytics (DLA) first.
Create a schema
Log on to the DLA console.
In the left-side navigation pane, click Endpoint. In the endpoint list, find the target endpoint and click Log on in DMS. On the DMS for Data Lake Analytics page, execute the following statement to create an AnalyticDB for MySQL schema in DLA.
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' );
CATALOG = 'adb3'
Specifies the AnalyticDB for MySQL 3.0 schema.
The connection information for the AnalyticDB for MySQL database.
The ID of the VPC to which the AnalyticDB for MySQL database belongs.
The ID of the cluster to which the AnalyticDB for MySQL database belongs.
The account created for the AnalyticDB for MySQL database.
USERThe password corresponding to the account name.
Create a table
You can create an AnalyticDB for MySQL table in DLA in either of the following two scenarios:
The table name, field name, or field type is inconsistent with that of the corresponding table in AnalyticDB for MySQL.
The table name, field name, and field type are completely consistent with those of the corresponding table in AnalyticDB for MySQL.
Create a table with table name and field that are consistent with those in AnalyticDB for MySQL (recommended)
The following example illustrates how to create the shipping table 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) )
Create a table with table name or field that is inconsistent with that in AnalyticDB for MySQL
The following example illustrates how to create order_table1 in DLA, which corresponds to order_table. Some field names in order_table1 are different from those in order_table, and the data type of order1_id is different from that of order_id.
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' );
||The mapping between the table in DLA and the source table.|
||The mapping between table names.|
||The mapping between fields (columns).|