This topic describes
INSERT OVERWRITE INTO SELECT, which is a high-performance data writing method used in AnalyticDB for MySQL.
INSERT OVERWRITE INTO SELECTis suitable for the following scenarios:
- Data is written to a table by partition.
- All data of a table is initialized.
- Large amounts of data are written to a table.
How it works
The INSERT OVERWRITE INTO SELECT statement writes data to AnalyticDB MySQL by using external tables. To write data to AnalyticDB MySQL, you must define an external table of a specific data source in AnalyticDB MySQL and execute the
INSERT OVERWRITE INTO SELECT statement.
INSERT OVERWRITE INTO SELECT:
- During a data writing task, large amounts of cluster resources are consumed to ensure high performance. We recommend that you use this statement during off-peak hours.
- The written data is invisible when the data writing task is in progress, and becomes
visible only after the task is completed. If you execute the
INSERT OVERWRITE INTO SELECTstatement on a destination table that has data, no data on the destination table is changed until the statement is completed.
- If a partition is written to AnalyticDB for MySQL by using
INSERT OVERWRITE INTO SELECT, data of the existing partition that has the same name is overwritten.
- When a data writing task is completed, table indexes are created to improve query performance.
Do not execute
INSERT OVERWRITE INTO SELECT and real-time writing statements (INSERT INTO, REPLACE INTO, DELETE, or UPDATE) on the same table at the same time. Otherwise, the data written in real time is
INSERT OVERWRITE INTO table_name [(column_name,...)] select_statement
- table_name: the name of the destination table.
- column_name: the name of the column in the destination table.
- select_statement: the SELECT statement.
The data type of each column in the SELECT statement must match the data type in [(column_name,...)].
If the number of columns in the SELECT statement is larger than the number of columns in [(column_name,...)], the data writing fails. If the number of columns in the SELECT statement is smaller than the number of columns in [(column_name,...)], the additional columns in [(column_name,...)] are automatically filled with the default values. If no default value is specified, NULL is used.
INSERT OVERWRITE INTO Vendors (vend_id) SELECT cust_id FROM Customers;
Submit a task
SUBMIT JOBto submit an asynchronous task. Sample statement:
SUBMIT JOB INSERT OVERWRITE adb_table SELECT * FROM adb_external_table
Optimize writing performance
/* direct_batch_load=true*/) can be added before a data writing statement to accelerate the writing task while saving resources. Sample statement:
/* direct_batch_load=true*/ SUBMIT JOB INSERT OVERWRITE adb_table SELECT * FROM adb_external_table
/* direct_batch_load=true*/hint is supported only for AnalyticDB for MySQL V3.1.5 and later. If performance is not improved, you can Submit a ticket. For information about how to view the minor version of your cluster, see How can I view the version of an AnalyticDB for MySQL cluster?
Query the progress
SUBMIT JOB, the value of job_id is returned. You can use the job_id value to query the status of the writing task. Sample statement:
show job status where job='<job_id>'
If the writing task is completed, FINISH is returned in the status column.