INSERT OVERWRITE SELECT
本文介绍云原生数据仓库 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_target
b=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_target
b=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,表示写入任务完成。
- 本页导读 (1)
- 功能原理
- 特点与应用场景
- 注意事项
- 语法
- 参数说明
- 示例
- 异步写入
- 提交任务
- 写入调优
- 进度查询