hive语法之insert overwrite/insert into

LanguageManual DML - Apache Hive - Apache Software Foundation

Standard syntax:

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;

Hive extension (multiple inserts):

FROM from_statement

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1

[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]

[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;

FROM from_statement

INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1

[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2]

[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;

Hive extension (dynamic partition inserts):

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

语法很简单，说明也很简单， insert into 是追加，insert overwrite 是覆盖不过只会覆盖对应分区或者全表数据

关键点在下面

• INSERT OVERWRITE will overwrite any existing data in the table or partition - unless IF NOT EXISTS is provided for a partition (as of Hive 0.9.0).- As of Hive 2.3.0 (HIVE-15880), if the table has TBLPROPERTIES ("auto.purge"="true") the previous data of the table is not moved to Trash when INSERT OVERWRITE query is run against the table. This functionality is applicable only for managed tables (see managed tables) and is turned off when "auto.purge" property is unset or set to false.

这里提到了hive2.3.0之后 如果表属性有("auto.purge"="true")，当insert overwrite的时候，之前的数据是不会移动到垃圾箱的(会移动哪里呢，实际就是会直接删除，垃圾箱回收站都不放？)，也就是说，哎呀 我不小心删除了，想恢复都不可以的。

• INSERT INTO will append to the table or partition, keeping the existing data intact. (Note: INSERT INTO syntax is only available starting in version 0.8.) - As of Hive 0.13.0, a table can be made immutable by creating it with TBLPROPERTIES ("immutable"="true"). The default is "immutable"="false". INSERT INTO behavior into an immutable table is disallowed if any data is already present, although INSERT INTO still works if the immutable table is empty. The behavior of INSERT OVERWRITE is not affected by the "immutable" table property. An immutable table is protected against accidental updates due to a script loading data into it being run multiple times by mistake. The first insert into an immutable table succeeds and successive inserts fail, resulting in only one set of data in the table, instead of silently succeeding with multiple copies of the data in the table.

这里提到了一个比较有意思的参数("immutable"="true") 这个单词是不可变的意思，insert into本身是追加的意思，加了这个参数后， 就代表数据/分区只能被插入一次。（当然overwrite 这个表 也可以）

• Inserts can be done to a table or a partition. If the table is partitioned, then one must specify a specific partition of the table by specifying values for all of the partitioning columns. If hive.typecheck.on.insert is set to true, these values are validated, converted and normalized to conform to their column types (Hive 0.12.0 onward).

类型检查和自动转换

其余的都没啥说的。。

动态分区插入参数

hive.exec.dynamic.partition

true

Needs to be set to

true

to enable dynamic partition inserts

hive.exec.dynamic.partition.mode

strict

In

strict

mode, the user must specify at least one static partition in case the user accidentally overwrites all partitions, in

nonstrict

mode all partitions are allowed to be dynamic

hive.exec.max.dynamic.partitions.pernode

100

Maximum number of dynamic partitions allowed to be created in each mapper/reducer node

hive.exec.max.dynamic.partitions

1000

Maximum number of dynamic partitions allowed to be created in total

hive.exec.max.created.files

100000

Maximum number of HDFS files created by all mappers/reducers in a MapReduce job

hive.error.on.empty.partition

false

Whether to throw an exception if dynamic partition insert generates empty results

---其实这个没啥说的主要是我在hive3.1看到表创建自动带purge=ture ，以为会有详细的解释的。

--后续待补充