All Products
Search
Document Center

Execute INSERT FROM SELECT to migrate data

Last Updated: Nov 25, 2020

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.

Usage notes

  • 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 -c parameter 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.

Insert data

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;

Query data

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