如何使用insert overwrite select实现覆盖写入 - 云原生数据仓库AnalyticDB

本文介绍云原生数据仓库 AnalyticDB MySQL 版表数据的高性能写入方式INSERT OVERWRITE SELECT，包括应用场景、功能原理、注意事项、语法和异步写入应用。

功能原理

INSERT OVERWRITE SELECT会先清空分区中的旧数据，再将新数据批量写入到分区。

如果表是分区表，INSERT OVERWRITE SELECT只能覆盖数据涉及到的分区，数据未涉及的分区不会被清空并覆盖写入。
如果表是非分区表，INSERT OVERWRITE SELECT会清空整表的旧数据，并批量写入新数据。

每个表的写入任务串行执行，即单表写入并发数为1，无法调整。为保证单任务写入性能，防止集群负载过高，集群写入任务并发数默认为2，不建议调整。

说明

如有需要调整写入并发数，请提交工单联系技术支持，由技术支持评估调整。

特点与应用场景

INSERT OVERWRITE SELECT的特点如下：

资源消耗大：高性能写入INSERT OVERWRITE SELECT会消耗大量集群资源，建议在业务低峰期使用。
批量可见：写入任务完成前数据不可见，任务完成后该任务写入的数据批量可见。
分区覆盖：通过INSERT OVERWRITE SELECT写入的分区数据会覆盖目标表同一分区的数据。
自动构建索引：写入数据时同步构建索引，写入任务完成，目标表就具备索引，可提升查询性能。

INSERT OVERWRITE SELECT常见的应用场景如下：

分区级数据写入。
数据初始化（全量写入）。
大批量数据写入操作，不建议用于少量数据的写入。

注意事项

请勿同时通过INSERT OVERWRITE SELECT和实时写入方式（INSERT INTO、REPLACE INTO、DELETE、UPDATE）向同一个表中写入数据，否则实时写入的数据会被丢弃。

语法

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

参数说明

table_name：目标表的表名。
column_name：目标表的列名。
select_statement：SELECT语句。
SELECT语句中每一列的数据类型需要与目标表每一列的数据类型相匹配。
如果SELECT语句中的列数比目标表的列数多，会写入失败；如果SELECT语句中的列数比目标表中的列数少，写入数据时，目标表中多出的列会自动填充默认值，无默认值时值为NULL。

示例

示例数据

准备测试表和测试数据，用于测试下文覆盖写入的示例。

创建测试需要的源表test_source和目标表test_target。

CREATE TABLE test_source (a BIGINT, b BIGINT) 
DISTRIBUTED BY HASH(a);

CREATE TABLE test_target (a BIGINT, b BIGINT) 
DISTRIBUTED BY HASH(a) 
PARTITION BY VALUE(b) LIFECYCLE 10;

初始化源表test_source。

INSERT INTO test_source VALUES (1,1);  
INSERT INTO test_source VALUES (1,2); 
INSERT INTO test_source VALUES (1,3);  
INSERT INTO test_source VALUES (2,1); 
INSERT INTO test_source VALUES (2,2); 
INSERT INTO test_source VALUES (2,3);

覆盖写入

初始化目标表test_target。

INSERT OVERWRITE test_target 
SELECT * FROM test_source WHERE a = 1;

查询test_target，得到如下结果。

+-----+------+
|1    |1     |
|1    |2     |
|1    |3     |
+-----+------+

对目标表test_targetb=1的分区进行覆盖写入。

INSERT OVERWRITE test_target (a,b)
SELECT a,b FROM test_source 
WHERE a = 2 AND b = 1;

覆盖写入b=1的分区后，查询test_target，得到如下结果。

+-----+------+
|2    |1     |
|1    |2     |
|1    |3     |
+-----+------+

对目标表test_targetb=2和b=3的分区进行覆盖写入。

INSERT OVERWRITE test_target
SELECT * FROM test_source 
WHERE a = 2 AND b >= 2 AND b <= 3;

覆盖写入b=2和b=3的分区后，查询test_target，得到如下结果。

+-----+------+
|2    |1     |
|2    |2     |
|2    |3     |
+-----+------+

异步写入

提交任务

通常使用SUBMIT JOB提交异步任务，由后台调度执行。示例语句如下。

SUBMIT JOB
INSERT OVERWRITE adb_table
SELECT * FROM adb_external_table;

写入调优

在写入任务前增加Hint（/* direct_batch_load=true*/）可以加速写入任务。该Hint可以在节约大量资源的同时进一步提高写入性能。示例语句如下。

/* direct_batch_load=true*/
SUBMIT JOB
INSERT OVERWRITE adb_table
SELECT * FROM adb_external_table;

说明

仅3.1.5及以上内核版本支持/* direct_batch_load=true*/。若使用后性能无明显优化，可提交工单进行升级与优化。查看内核版本，请参见如何查看实例版本信息。

进度查询

通过SUBMIT JOB提交写入任务后会返回job_id。以该job_id为条件查询写入任务的状态，示例语句如下。

SHOW JOB STATUS WHERE job='<job_id>';

返回结果status列为SUCCEEDED，表示写入任务完成。