描述

该语句的功能是重写表或表的某些分区

1INSERT OVERWRITE table table_name
2    [ PARTITION (p1, ... | *) ]
3    [ WITH LABEL label]
4    [ (column [, ...]) ]
5    [ [ hint [, ...] ] ]
6    { VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }

Parameters

table_name: 需要重写的目的表。这个表必须存在。可以是 db_name.table_name 形式

partitions: 需要重写的目标分区，支持两种形式：

1. 分区名。必须是 table_name 中存在的分区，多个分区名称用逗号分隔。
2. 星号 (*)。开启自动检测分区功能。写入操作将会自动检测数据所涉及的分区，并覆写这些分区。该功能自 PALO 2.1.3 版本开始支持。

label: 为 Insert 任务指定一个 label

column_name: 指定的目的列，必须是 table_name 中存在的列

expression: 需要赋值给某个列的对应表达式

DEFAULT: 让对应列使用默认值

query: 一个普通查询，查询的结果会重写到目标中

hint: 用于指示 INSERT 执行行为的一些指示符。目前 hint 有三个可选值/*+ STREAMING */、/*+ SHUFFLE */或/*+ NOSHUFFLE */

1. STREAMING：目前无实际作用，只是为了兼容之前的版本，因此保留。（之前的版本加上这个 hint 会返回 label，现在默认都会返回 label）
2. SHUFFLE：当目标表是分区表，开启这个 hint 会进行 repartiiton。
3. NOSHUFFLE：即使目标表是分区表，也不会进行 repartiiton，但会做一些其他操作以保证数据正确落到各个分区中。

注意：

1. 在当前版本中，会话变量 enable_insert_strict 默认为 true，如果执行 INSERT OVERWRITE 语句时，对于有不符合目标表格式的数据被过滤掉的话会重写目标表失败（比如重写分区时，不满足所有分区条件的数据会被过滤）。
2. INSERT OVERWRITE 语句会首先创建一个新表，将需要重写的数据插入到新表中，最后原子性的用新表替换旧表并修改名称。因此，在重写表的过程中，旧表中的数据在重写完毕之前仍然可以正常访问。

For Auto Partition Table

如果 INSERT OVERWRITE 的目标表是自动分区表，那么行为受到Session Variable enable_auto_create_when_overwrite 的控制，具体行为如下：

1. 若未指定 PARTITION（覆写整表），当 enable_auto_create_when_overwrite 为 true，在覆写整表已有数据的同时，对于没有对应分区的数据，按照该表的自动分区规则创建分区，并容纳这些原本没有对应分区的数据。如果 enable_auto_create_when_overwrite 为 false，未找到分区的数据将累计错误行直到失败。
2. 如果指定了覆写的 PARTITION，那么在此过程中，AUTO PARTITION 表表现得如同普通分区表一样，不满足现有分区条件的数据将被过滤，而非创建新的分区。
3. 若指定 PARTITION 为 partition(*) （自动检测分区并覆写），当 enable_auto_create_when_overwrite 为 true，对于那些在表中有对应分区的数据，覆写它们对应的分区，其他已有分区不变。同时，对于没有对应分区的数据，按照该表的自动分区规则创建分区，并容纳这些原本没有对应分区的数据。如果 enable_auto_create_when_overwrite 为 false，未找到分区的数据将累计错误行直到失败。

enable_auto_create_when_overwrite 自 2.1.8 引入，在没有 enable_auto_create_when_overwrite 的版本，行为如同该变量值为 false。

速查结论如下：

1. 对于开启 enable_auto_create_when_overwrite 的自动分区表：

2. 对于普通表、关闭 enable_auto_create_when_overwrite 的自动分区表：

示例如下：

1mysql> create table auto_list(
2    ->              k0 varchar null
3    ->          )
4    ->          auto partition by list (k0)
5    ->          (
6    ->              PARTITION p1 values in (("Beijing"), ("BEIJING")),
7    ->              PARTITION p2 values in (("Shanghai"), ("SHANGHAI")),
8    ->              PARTITION p3 values in (("xxx"), ("XXX")),
9    ->              PARTITION p4 values in (("list"), ("LIST")),
10    ->              PARTITION p5 values in (("1234567"), ("7654321"))
11    ->          )
12    ->          DISTRIBUTED BY HASH(`k0`) BUCKETS 1
13    ->          properties("replication_num" = "1");
14Query OK, 0 rows affected (0.14 sec)
15
16mysql> insert into auto_list values ("Beijing"),("Shanghai"),("xxx"),("list"),("1234567");
17Query OK, 5 rows affected (0.22 sec)
18
19mysql> insert overwrite table auto_list partition(*) values ("BEIJING"), ("new1");
20Query OK, 2 rows affected (0.28 sec)
21
22mysql> select * from auto_list;
23+----------+ --- p1 被覆写，new1 得到了新分区，其他分区数据未变
24| k0       |
25+----------+
26| 1234567  |
27| BEIJING  |
28| list     |
29| xxx      |
30| new1     |
31| Shanghai |
32+----------+
336 rows in set (0.48 sec)
34
35mysql> insert overwrite table auto_list values ("SHANGHAI"), ("new2");
36Query OK, 2 rows affected (0.17 sec)
37
38mysql> select * from auto_list;
39+----------+ --- 整表原有数据被覆写，同时 new2 得到了新分区
40| k0       |
41+----------+
42| new2     |
43| SHANGHAI |
44+----------+
452 rows in set (0.15 sec)

示例

假设有test 表。该表包含两个列c1, c2，两个分区p1,p2。建表语句如下所示

1CREATE TABLE IF NOT EXISTS test (
2  `c1` int NOT NULL DEFAULT "1",
3  `c2` int NOT NULL DEFAULT "4"
4) ENGINE=OLAP
5UNIQUE KEY(`c1`)
6PARTITION BY LIST (`c1`)
7(
8PARTITION p1 VALUES IN ("1","2","3"),
9PARTITION p2 VALUES IN ("4","5","6")
10)
11DISTRIBUTED BY HASH(`c1`) BUCKETS 3
12PROPERTIES (
13  "replication_allocation" = "tag.location.default: 1",
14  "in_memory" = "false",
15  "storage_format" = "V2"
16);

Overwrite Table

1. VALUES 的形式重写test表

   SQL 复制

   1# 单行重写
   2INSERT OVERWRITE table test VALUES (1, 2);
   3INSERT OVERWRITE table test (c1, c2) VALUES (1, 2);
   4INSERT OVERWRITE table test (c1, c2) VALUES (1, DEFAULT);
   5INSERT OVERWRITE table test (c1) VALUES (1);
   6# 多行重写
   7INSERT OVERWRITE table test VALUES (1, 2), (3, 2 + 2);
   8INSERT OVERWRITE table test (c1, c2) VALUES (1, 2), (3, 2 * 2);
   9INSERT OVERWRITE table test (c1, c2) VALUES (1, DEFAULT), (3, DEFAULT);
   10INSERT OVERWRITE table test (c1) VALUES (1), (3);

• 第一条语句和第二条语句的效果一致，重写时如果不指定目标列，会使用表中的列顺序来作为默认的目标列。重写成功后表test中只有一行数据。
• 第三条语句和第四条语句的效果一致，没有指定的列c2会使用默认值 4 来完成数据重写。重写成功后表test中只有一行数据。
• 第五条语句和第六条语句的效果一致，在语句中可以使用表达式（如2+2，2*2），执行语句的时候会计算出表达式的结果再重写表test。重写成功后表test中有两行数据。
• 第七条语句和第八条语句的效果一致，没有指定的列c2会使用默认值 4 来完成数据重写。重写成功后表test中有两行数据。

2. 查询语句的形式重写test表，表test2和表test的数据格式需要保持一致，如果不一致会触发数据类型的隐式转换

   SQL 复制

   1INSERT OVERWRITE table test SELECT * FROM test2;
   2INSERT OVERWRITE table test (c1, c2) SELECT * from test2;

• 第一条语句和第二条语句的效果一致，该语句的作用是将数据从表test2中取出，使用取出的数据重写表test。重写成功后表test中的数据和表test2中的数据保持一致。

3. 重写 test 表并指定 label

   SQL 复制

   1INSERT OVERWRITE table test WITH LABEL `label1` SELECT * FROM test2;
   2INSERT OVERWRITE table test WITH LABEL `label2` (c1, c2) SELECT * from test2;

• 用户可以通过SHOW LOAD;命令查看此label导入作业的状态。需要注意的是 label 具有唯一性。

Overwrite Table Partition

使用 INSERT OVERWRITE 重写分区时，实际我们是将如下三步操作封装为一个事务并执行，如果中途失败，已进行的操作将会回滚：

1. 假设指定重写分区 p1，首先创建一个与重写的目标分区结构相同的空临时分区 pTMP
2. 向 pTMP 中写入数据
3. 使用 pTMP 原子替换 p1 分区

举例如下：

1. VALUES 的形式重写test表分区P1和p2

   SQL 复制

   1# 单行重写
   2INSERT OVERWRITE table test PARTITION(p1,p2) VALUES (1, 2);
   3INSERT OVERWRITE table test PARTITION(p1,p2) (c1, c2) VALUES (1, 2);
   4INSERT OVERWRITE table test PARTITION(p1,p2) (c1, c2) VALUES (1, DEFAULT);
   5INSERT OVERWRITE table test PARTITION(p1,p2) (c1) VALUES (1);
   6# 多行重写
   7INSERT OVERWRITE table test PARTITION(p1,p2) VALUES (1, 2), (4, 2 + 2);
   8INSERT OVERWRITE table test PARTITION(p1,p2) (c1, c2) VALUES (1, 2), (4, 2 * 2);
   9INSERT OVERWRITE table test PARTITION(p1,p2) (c1, c2) VALUES (1, DEFAULT), (4, DEFAULT);
   10INSERT OVERWRITE table test PARTITION(p1,p2) (c1) VALUES (1), (4);

   以上语句与重写表不同的是，它们都是重写表中的分区。分区可以一次重写一个分区也可以一次重写多个分区。需要注意的是，只有满足对应分区过滤条件的数据才能够重写成功。如果重写的数据中有数据不满足其中任意一个分区，那么本次重写会失败。一个失败的例子如下所示

   SQL 复制

   1INSERT OVERWRITE table test PARTITION(p1,p2) VALUES (7, 2);

   以上语句重写的数据c1=7分区p1和p2的条件都不满足，因此会重写失败。

2. 查询语句的形式重写test表分区P1和p2，表test2和表test的数据格式需要保持一致，如果不一致会触发数据类型的隐式转换

   SQL 复制

   1INSERT OVERWRITE table test PARTITION(p1,p2) SELECT * FROM test2;
   2INSERT OVERWRITE table test PARTITION(p1,p2) (c1, c2) SELECT * from test2;

3. 重写 test 表分区P1和p2并指定 label

   SQL 复制

   1INSERT OVERWRITE table test PARTITION(p1,p2) WITH LABEL `label3` SELECT * FROM test2;
   2INSERT OVERWRITE table test PARTITION(p1,p2) WITH LABEL `label4` (c1, c2) SELECT * from test2;

Overwrite Auto Detect Partition

当 INSERT OVERWRITE 命令指定的 PARTITION 子句为 PARTITION(*) 时，此次覆写将会自动检测分区数据所在的分区。例如：

1mysql> create table test(
2    -> k0 int null
3    -> )
4    -> partition by range (k0)
5    -> (
6    -> PARTITION p10 values less than (10),
7    -> PARTITION p100 values less than (100),
8    -> PARTITION pMAX values less than (maxvalue)
9    -> )
10    -> DISTRIBUTED BY HASH(`k0`) BUCKETS 1
11    -> properties("replication_num" = "1");
12Query OK, 0 rows affected (0.11 sec)
13
14mysql> insert into test values (1), (2), (15), (100), (200);
15Query OK, 5 rows affected (0.29 sec)
16
17mysql> select * from test order by k0;
18+------+
19| k0   |
20+------+
21|    1 |
22|    2 |
23|   15 |
24|  100 |
25|  200 |
26+------+
275 rows in set (0.23 sec)
28
29mysql> insert overwrite table test partition(*) values (3), (1234);
30Query OK, 2 rows affected (0.24 sec)
31
32mysql> select * from test order by k0;
33+------+
34| k0   |
35+------+
36|    3 |
37|   15 |
38| 1234 |
39+------+
403 rows in set (0.20 sec)

可以看到，数据 3、1234 所在的分区 p10 和 pMAX 中的全部数据均被覆写，而 p100 分区未发生变化。该操作可以理解为 INSERT OVERWRITE 操作时通过 PARTITION 子句指定覆写特定分区的语法糖，它的实现原理与指定重写特定分区相同。通过 PARTITION(*) 的语法，在覆写大量分区数据时我们可以免于手动填写全部分区名的繁琐。

关键词

INSERT OVERWRITE, OVERWRITE, AUTO DETECT