hive DML[通俗易懂]

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 overwrite将覆盖表或分区中的现有数据。if not exists表示当分区不存在时才插入或者覆盖，否则什么都不做。

从hive2.3.0开始，如果表具有tblproperties(“auto.purge”=”true”)，则在对表运行insert overwrite时，表之前的数据不会移动到回收站，而是直接删除。此功能仅适用于托管表，并且在”auto.purge”属性未设置或者设置为false时关闭。

如果在创建表时指定了tblproperties(“immutable”=”true”)，则表不可变。默认值是false。当不可变表中无数据时，insert into可以生效。当不可变表中有数据时，insert into会报错“Inserting into a non-empty immutable table is not allowed“。

insert overwrite的行为不受”immutable”属性影响。

insert 可以操作表或分区。如果表已分区，则必须通过指定所有分区列的值来指定表的特定分区。

可以在一个语句中指定多个插入子句(目标表和数据都可以不一样)。如

from pokes
insert into invites partition (ds = ‘2018-09-05’) select *
insert into invites partition (ds = ‘2018-09-06’) select *;

假如一个表的OutputFormat实现了AcidOutputFormat，并且系统配置为使用实现ACID的事务管理器，则将禁用该表的insert overwrite。这是为了避免用户无意中覆盖历史数据。可以通过使用truncate table(对于非分区表)或drop partition，然后insert into 来实现相同的功能。

alter table invites drop if exists partition (ds = ‘2018-09-01’) purge;

动态分区插入：

在动态分区插入中，用户只需在partition子句中指定分区列名称列表，而列值是不必需的。如果给出了分区列值，则称为静态分区，否则是动态分区。每个动态分区列都有一个来自select语句的相应输入列，这意味着动态分区创建由输入列的值确定。动态分区列必须在select语句的列中最后指定，并且与它们在partition()子句中出现的顺序相同。从hive3.0.0开始，不需要指定动态分区列。如果未指定，hive将自动生成分区规范。

例如：

创建一个有2个分区列的表，并在此表中插入数据。

create table invites2 (foo int, bar string) partitioned by (ds string, fs int);

insert into invites2 partition (ds=’2018-09-04′, fs) select foo, bar, foo from pokes;

如果foo有很多不同值，则上面插入操作可能会报错Container is running beyond virtual memory limits.可以参考

https://www.jianshu.com/p/62800898dd02

https://blog.csdn.net/T1DMzks/article/details/78818874

解决。

与动态分区插入有关的配置：

hive.exec.dynamic.partition，默认值是true，即允许动态分区插入。改为false，则禁用动态分区插入。

hive.exec.dynamic.partition.mode，默认值是strict，这种情况下，在指定动态分区列时，至少要有一个静态分区列。可改为nonstrict，这样的话，可以所有分区列都是动态分区列。

hive.exec.max.dynamic.partitions.pernode，默认值是100。

hive.exec.max.dynamic.partitions，默认值是1000。

hive.exec.max.created.files，默认值是100000。

hive.error.on.empty.partition，默认值是false。

这些配置项都在hive-default.xml.template文件中有，可以去里面看具体解释。

3、Writing data into the filesystem from queries

将查询结果集写到文件系统中，相当于load的反向操作

标准语法：

insert overwrite [local] directory directory1
[row format row_format] [stored as file_format]
select … from …

hive扩展：

from from_statement
insert overwrite [local] directory directory1 select_statement1
[insert overwrite [local] directory directory2 select_statement2] …

其中row_format支持

row_format
: delimited [fields terminated by char [escaped by char]] [collection items terminated by char]
[lines terminated by char]
[null defined as char]

directory目录可以是完整的URI。如果未指定schema或authority，hive将使用hadoop配置变量fs.default.name中的schema和authority来指定NameNode URI。

如果使用了local关键字，hive会将数据写入本地本件系统。

写入文件系统的数据被序列化为文本，其中列由^A分隔，行由换行符分隔。如果某些列类型不是原始类型，则这些列会被序列化为json格式。

需要注意的是，可以在一条语句同时insert overwrite directory、local directory以及hive表。

4、Inserting values into tables from SQL

用普通sql插入数据，可以一次插入多条记录。

标准语法：

insert into table tablename [partition (partcol1[=val1], partcol2[=val2] …)] values values_row [, values_row …]

这里有个地方需要注意：

假如目标表不是分区表的话，则在插入数据时是可以只指定某些列的，就像普通关系型数据库一样，但由于建hive表时一般不指定列的默认值，故指定列之外的列的值为null。

insert into table pokes values (1, ‘abc1’), (2, ‘abc2’);

insert into table pokes (foo) values (1);

假如目标表是分区表，则不能指定某些列。支持静态分区插入、动态分区插入。

insert into table invites partition (ds = ‘2018-09-07’) values (1, ‘val_1’);

insert into table invites partition (ds) values (1, ‘val_1’, ‘2018-09-07’);   同样的，把动态分区列的值放到最后面。

5、update

更新操作仅能在支持ACID的表上操作成功，否则会报Attempt to do update or delete using transaction manager that does not support these operations 错误。

标准语法：

UPDATE tablename SET column = value [, column = value…] [WHERE expression]

注意，分区列值无法更新，bucket列值也无法更新。

建议将hive.optimize.sort.dynamic.partition 设为fase，因为这会生成更高效的执行计划。

6、delete

删除操作也是仅能在支持ACID的表上操作成功。

标准语法：

delete from tablename [where expression]

同样建议将hive.optimize.sort.dynamic.partition 设为fase

7、merge

从hive2.2之后，才支持merge操作，且也是仅能在支持ACID的表上操作成功。

标准语法：