INSERT OVERWRITE SELECT

更新时间:2024-11-04 10:24:02

本文介绍云原生数据仓库 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 INTOREPLACE INTODELETEUPDATE)向同一个表中写入数据,否则实时写入的数据会被丢弃。

语法

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

参数说明

  • table_name:目标表的表名。

  • column_name:目标表的列名。

  • select_statement:SELECT语句。

    SELECT语句中每一列的数据类型需要与目标表每一列的数据类型相匹配。

    如果SELECT语句中的列数比目标表的列数多,会写入失败;如果SELECT语句中的列数比目标表中的列数少,写入数据时,目标表中多出的列会自动填充默认值,无默认值时值为NULL。

示例

示例数据

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

  1. 创建测试需要的源表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;
  2. 初始化源表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);  

覆盖写入

  1. 初始化目标表test_target

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

    查询test_target,得到如下结果。

    +-----+------+
    |1    |1     |
    |1    |2     |
    |1    |3     |
    +-----+------+
  2. 对目标表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     |
    +-----+------+
  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,表示写入任务完成。

  • 本页导读 (1)
  • 功能原理
  • 特点与应用场景
  • 注意事项
  • 语法
  • 参数说明
  • 示例
  • 异步写入
  • 提交任务
  • 写入调优
  • 进度查询