This topic describes INSERT OVERWRITE INTO SELECT, which is a high-performance data writing method used in AnalyticDB for MySQL.

Scenarios

INSERT OVERWRITE INTO SELECT is 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.

Data writing tasks are executed in series for each table. Only a single writing task can be executed at a time for each table. This setting cannot be modified. To ensure the data writing performance of each task and prevent high cluster loads, two tasks can be executed concurrently for each cluster by default. We recommend that you do not modify this default setting.
Note If you need to modify the number of concurrent writing tasks, Submit a ticket.
The following section describes basic characteristics of 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 SELECT statement 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.

Precautions

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 discarded.

Syntax

INSERT OVERWRITE INTO table_name [(column_name,...)]
select_statement 

Parameters

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

Example

Write data of the cust_id column in the Customers table to the vend_id column of the Vendors table. Sample statement:
INSERT OVERWRITE INTO Vendors (vend_id)
SELECT cust_id FROM Customers;

Asynchronous writing

Submit a task

Use SUBMIT JOB to submit an asynchronous task. Sample statement:
SUBMIT JOB
INSERT OVERWRITE adb_table
SELECT * FROM adb_external_table

Optimize writing performance

A hint (/* 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
Note The /* 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

After you submit a data writing task by using 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.