You can connect to Data Lake Analytics (DLA) through the MySQL client or MySQL CLI, and then execute the INSERT SELECT statement to write data from Object Storage Service (OSS) to AnalyticDB for MySQL. Alternatively, you can go to the DMS for Data Lake Analytics page, and then execute the INSERT SELECT statement to write data from OSS to AnalyticDB for MySQL.
Precautions
Generally, INSERT FROM SELECT requires a long execution time. Asynchronous execution is recommended.
When using the MySQL command line, specify the
-c
parameter, which identifies the hint before a MySQL statement.mysql -hxxx -Pxxx -uxxx -pxxx db_name -c
h: specifies the endpoint of DLA, which is obtained in the DLA console.
P: specifies the port for connecting to DLA, which is obtained in the DLA console.
u: specifies the DLA user name. For more information, see Accounts and permissions.
db_name: specifies the name of the AnalyticDB for MySQL schema created in DLA.
If columns are not specified in the INSERT INTO and SELECT clauses, ensure that the columns in the source and destination tables are defined in the same order and the column types match.
If columns are specified in the INSERT INTO clause and SELECT clause, ensure that the column orders in both clauses meet your business requirements and the column types match.
Write data to AnalyticDB for MySQL
Execute the following SQL statement asynchronously to write the data from the dla_table_1 file in the OSS table directory to the shipping table of the AnalyticDB for MySQL database adb_demo.
-- Write full data from OSS to AnalyticDB for MySQL.
/*+run-async=true*/
INSERT INTO ads_database_schema.shipping
SELECT * FROM oss_data_schema.dla_table_1;
Asynchronously execute the following SQL statement to write data from order_amount > 2
in the dla_table_1 file in the OSS table directory to the order_table table in the AnalyticDB for MySQL database adb_demo.
-- Write data to AnalyticDB for MySQL from OSS, 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 the results
Asynchronously executing the INSERT INTO SELECT statement returns a task ID. In AnalyticDB for MySQL, query the task execution status based on the task ID. If the status is SUCCESS, the task is completed.
SHOW query_task WHERE id = 'q201812241524sh385b7d4c0049869';
What to do next
After migrating OSS data to AnalyticDB for MySQL, you can use various functions of AnalyticDB for MySQL to perform data analysis and computing.