You can connect to Data Lake Analytics (DLA) by using a MySQL client, MySQL CLI tool, or DMS for DLA and then execute INSERT INTO SELECT to write data from OSS to AnalyticDB for MySQL 2.0.
The execution of INSERT INTO SELECT is a long-running task. We recommend that you run this task in asynchronous mode.
When you write data by using the MySQL CLI, you must specify the
-cparameter in the CLI to identify the hint before the MySQL statement.
mysql -hxxx -Pxxx -uxxx -pxxx db_name -c
h: the information of the endpoint for DLA. You can obtain the information in the DLA console. If you install your MySQL client or MySQL CLI tool on an ECS instance that is in the same region as DLA, and the ECS instance is in the same VPC as DLA, use the VPC endpoint of DLA. If the ECS instance is in a VPC different from DLA, use the classic network endpoint of DLA.
P: the port that is used to connect to DLA. You can obtain the port number from the DLA console.
u: the username that is used to log on to DLA. For more information, see Accounts and permissions.
db_name: the name of the schema that is used in DLA.
If you do not specify columns in the INSERT INTO or SELECT clauses, make sure that the column sequence and data types of columns between the source and destination tables are consistent.
If you specify columns in the INSERT INTO and SELECT clauses, make sure that the column sequence meets your business requirements and the data types of columns between the source and destination tables are consistent.
Asynchronously execute the following SQL statements to insert data from the dla_table_1 file in the OSS table folder into the shipping table in the ads_database database on the AnalyticDB for MySQL 2.0 instance.
-- Insert full data from OSS to the AnalyticDB for MySQL 2.0 instance. /*+run-async=true*/ INSERT INTO ads_database_schema.shipping SELECT * FROM oss_data_schema.dla_table_1;
Asynchronously execute the following SQL statements to insert data with
order_amount greater than 2 into the order_table table of the ads_database database on the AnalyticDB for MySQL 2.0 instance.
-- Insert data from OSS to the AnalyticDB for MySQL 2.0 instance, including the logic for filtering OSS data. /*+run-async=true*/ INSERT INTO ads_database_schema.order_table1 (customer1_id, order1_id, order1_time, order1_amount,order1_type,address1,city1,order1_season) SELECT customer_id, order_id, order_time, order_amount,order_type,address,city,order_season FROM oss_data_schema.dla_table_2 WHERE order_amount > 2 LIMIT 10000;
A task ID is returned after the asynchronous execution of INSERT INTO SELECT. You can use the task ID to poll the task execution status in AnalyticDB for MySQL 2.0. If the status is SUCCESS, the data has been inserted.
SHOW query_task WHERE id = 'q201812241524sh385b7d4c0049869';