由 Confluence Administrator创建, 最终由 Lars Francke修改于 八月 15, 2018
原文链接
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML
翻译：Google Google翻译，金山软件 金山词霸
校对：南大通用 范振勇 (2018.10.6)
在Hive中，有多种方式修改数据：
LOAD
INSERT
从查询到目标表
从查询到目录
成从SQL蜂巢表
UPDATE
DELETE
MERGE
从Hive0.8起可以使用EXPORT和IMPORT命令。
一、从文件加载到表
在将数据加载到表中时，Hive不执行任何转换。当前，Load操作是纯复制/移动操作，仅将数据文件移动到与Hive表对应的位置。
1.1、 语法
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] [INPUTFORMAT 'inputformat' SERDE 'serde'] (3.0 or later)
1.2、 概要
Hive3.0之前的加载操作是将数据文件移动（纯复制/移动操作）到与Hive表对应的位置。
- filepath可以是：
- 相对路径，如 project/data1
- 绝对路径，如 /user/hive/project/data1
- 一个完整的带scheme和（可选）授权信息的URI，如 hdfs://namenode:9000/user/hive/project/data1
- 加载到目标可以是一个表或一个分区。如果分区表，则必须制定所有分区列的值来确定加载特定分区。
- filepath可以是指文件（在这种情况下Hive将文件移动到表），也可以是目录（在这种情况下Hive将移动该目录中的所有文件到表）。在这两种情况下，filepath都会处理一组文件。
- 如果指定了关键字LOCAL，则：
- LOAD命令将在本地文件系统查找filepath。如果指定了相对路径，将相对于用户当前的工作目录来解释。用户可以为本地文件指定一个完整的URI，例如：file:///user/hive/project/data1
- LOAD命令根据目标表的Location属性推断其文件系统位置，将复制filepath指定的所有文件到目标表文件系统，复制的数据文件将被移到表中。
- 注意：如果你用Beeline访问一个HiveServer2实例，运行Load命令，则其本地路径是指在HiveServer2实例的路径。同时，HiveServer2必须具有访问该文件的适当权限。
- 如果没有指定关键字LOCAL，HIVE要么使用完整的URI的文件路径（如果指定），要么应用以下规则：
- 如果未指定scheme或授权信息，Hive将使用来自Hadoop配置变量fs.default.name指定的Namenode URI的scheme和授权信息。
- 如果不是绝对路径，那么HIVE会相对于 /user/<username>解释路径。
- HIVE将移动filepath所指定文件的到表（或分区）的文件路径。
- 如果使用了overwrite关键字，则目标表（或分区）的内容将被删除，然后替换为filepath所引用的文件路径 ; 否则filepath指定的文件路径内容将会被添加到表中。
从Hive 3.0开始，支持附加的Load操作，它在Hive内部重写为一个INSERT AS SELECT。
- 如果表有分区，但是，Load命令没有指定分区，Load将被转换成INSERT AS SELECT，并且假设最后一组列是分区列。如果文件不符合预期的模式，则它会抛出一个错误。
- 如果是分桶表，则遵循以下规则：
- 在严格模式：启动一个INSERT AS SELECT工作。
- 在非严格模式：如果文件名符合命名惯例（如果该文件属于桶0，它应该被命名为000000_0或000000_0_copy_1，或者如果它属于桶2名应该像000002_0或000002_0_copy_3等。 ），那么这将是一个纯粹的复制/移动操作，反之，它将启动一个INSERT AS SELECT工作。
- filepath可以包含子目录，提供的每个文件都符合该模式。
- inputformat可以是Hive的任何输入格式，诸如文本，ORC等
- serde可以关联到Hive SERDE。
- inputformat和serde都是大小写敏感的。
这样的架构的实施例：
CREATE TABLE tab1 (col1 int, col2 int) PARTITIONED BY (col3 int) STORED AS ORC; LOAD DATA LOCAL INPATH 'filepath' INTO TABLE tab1;
这里，分区信息是缺失的，本应该给出一个错误，但是，如果位于filepath下的（一个或多个）文件路径符合分区表模式，使得每行具有分配列（一个或多个）结束，则Load将改写成一个INSERT AS SELECT工作。
未压缩的数据应该是这样的：
（1,2,3），（2,3,4），（4,5,3）等等。
1.3、 注释
- 文件路径不能包含子目录（如上面所述，除了Hive3.0或更高版本）。
- 如果不给出关键字LOCAL，filepath引用文件必须同Hive表（或分区的）位置处于同一文件系统中。
- Hive仅做一些最起码的检查，以确保这些加载文件匹配目标表。目前，如果该目标表存储在sequencefile格式，它会检查加载的文件也是否为sequencefiles，以此类推。
- Hive0.13.0修正了当名称包括“+”字符导致加载失败的bug（HIVE-6048）。
- 如果你的数据文件是压缩的，请阅读CompressedStorage。
二、将数据从查询插入Hive表
查询结果可以通过使用插入件子句插入到Hive表中。
2.1、 语法
标准语法: 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 扩展(多表插入模式): 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 扩展 (动态分区插入模式): 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;
2.2、 概要
- INSERT OVERWRITE将覆盖在表或分区的任何现有数据
- 除非用于分区时提供了IF NOT EXISTS（Hive 0.9.0）。
- 自Hive 2.3.0（HIVE-15880），如果表中有TBLPROPERTIES（“auto.purge” =“true”），在表上执行INSERT OVERWRITE查询时，该表以前的数据不被移动到回收站。此功能仅适用于托管表（见托管表），并且要求 “auto.purge”属性未设置或设置为false。
- INSERT INTO将追加到表或分区，保留原有数据不变。（注：INSERT INTO语法自Hive 0.8版本开始）。
- 从Hive 0.13.0开始，可以通过使用TBLPROPERTIES创建表(“Immutable”=“true”)使表不可变。默认情况是“Immutable”=“false”。如果已经存在任何数据，则不允许INSERT INTO行为插入到不可变表中，但如果不可变数据为空，则INSERT INTO操作仍然有效。INSERT OVERWRITE行为不受“Immutable”表属性的影响。
- 不可变表可以保护多次运行加载数据脚本的错误，以防意外更新。对不可变表的第一个插入成功，之后的插入则失败，这样，在表中的只有一组数据，而不是白白保留多个数据副本。
- 插入目标可以是一个表或分区。如果是分区表，则必须由设定所有分区列的值来指定表的特定分区。如果hive.typecheck.on.insert被设置为true时，这些值进行验证，转换并归一化，以符合他们的列类型（Hive 0.12.0以后）。
- 可以在同一个查询中指定多个INSERT子句(也称为多表插入)。
- 每个Select语句的的输出被写入到所对应表（或分区）。目前，OVERWRITE关键字是强制性的，意味着所选择的表或分区的内容将与对应的Select语句的输出代替。
- 输出格式和序列化类是由表元数据来确定（通过表的DDL命令指定）。
- 自Hive 0.14，如果一个表具有一个实现AcidOutputFormat的OUTPUTFORMAT，并且Hive系统配置用于一个实现的事务ACID管理器，则为了避免用户无意间改写事务记录，禁止INSERT OVERWRITE该表。如果想实现同样的功能，可以通过调用TRUNCATE TABLE（对于非分区表）或DROP PARTITION，然后再INSERT INTO。
- 自Hive 1.1.0，TABLE关键字是可选的。
- 自Hive 1.2.0，每个INSERT INTO T能够提供列的列表，类似INSERT INTO T（Z，X，C1）。详见HIVE-9481的例子。
2.3、 注释
- 多表插入可使数据扫描所需的次数最小化。通过对输入数据只扫描一次(并应用不同的查询操作符)，Hive可以将数据插入多个表中。
- 自HIVE 0.13.0开始，Select子句可以包含一个或多个公共表表达式（CTE），如SELECT语法所示。示例参见公用表表达式。
2.4、 动态分区插入模式
在动态分区插入时，用户可以提供局部分区规范，这意味着只需在分区子句中指定分区列名列表，而列值是可选的。如果给出分区列值，我们将其称为静态分区，否则就是动态分区。每个动态分区列都有来自SELECT语句的相应的投影列。这意味着动态分区创建由输入列的值决定。动态分区列必须在SELECT语句中的投影列中最后指定，并按照它们在PARTITION（）子句中出现的顺序。
在Hive3.0.0(hive-19083)中，不需要为动态分区指定分区列。如果未指定分区规范，Hive将自动生成该分区规范。
在Hive 0.9.0之前默认禁用动态分区插入，在Hive 0.9.0及更高版本中默认启用动态分区插入。下面是支持动态分区插入的相关配置属性：
|
配置属性 |
缺省值 |
注释 |
|
hive.exec.dynamic.partition |
true |
需要设置为true来启用动态分区插入 |
|
hive.exec.dynamic.partition.mode |
strict |
在strict模式下，用户必须指定至少一个静态分区的情况下，防止不小心将覆盖所有分区，在nonstrict模式下，允许所有分区是动态的。 |
|
hive.exec.max.dynamic.partitions.pernode |
100 |
允许在每个MAPPER/REDUCER节点创建动态分区的最大数目 |
|
hive.exec.max.dynamic.partitions |
1000 |
允许创建动态分区的最大数目 |
|
hive.exec.max.created.files |
100000 |
在MapReduce作业中所有MAPPER/REDUCER创建HDFS文件的最大数量 |
|
hive.error.on.empty.partition |
false |
当动态分区插入产生空结果时，是否抛出一个异常 |
例：
FROM page_view_stg pvs INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country) SELECT pvs.viewTime, pvs.userid, pvs.page_url, pvs.referrer_url, null, null, pvs.ip, pvs.cnt
这里的country分区列的值将由SELECT子句的最后一列(即pvs.cnt)动态创建。请注意，该名称不使用。在nonstrict模式下，还可以动态创建DT分区。
其他文档
三、将数据从查询写入到文件系统
将上述语法作细微变化，就可以将查询结果插入到文件系统目录中。
3.1、 语法
标准语法: INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0) SELECT ... FROM ... Hive 扩展 (多表插入): FROM from_statement INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1 [INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ... row_format : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] [NULL DEFINED AS char] (Note: Only available starting with Hive 0.13)
3.2、 概要
- 目录可以是一个完整的URI。如果未指定scheme或授权，Hive将使用来自Hadoop配置变量fs.default.name指定Namenode URI的scheme或授权。
- 如果使用LOCAL关键词，Hive将数据写入到本地文件系统的目录上。
- 写入文件系统的数据被序列化为由^ A做列分割符，换行做行分隔符的文本。如果任何列都不是原始类型（而是MAP、ARRAY、STRUCT、UNION），则这些列被序列化为JSON格式。
3.3、 注释
- 可以在同一查询中，INSERT OVERWRITE到目录，到本地目录和到表（或分区）。
- INSERT OVERWRITE语句是Hive提取大量数据到HDFS文件目录的最佳方式。Hive可以从map-reduce作业中的并行写入HDFS目录。
- 正如您预期的那样，该目录是被覆盖的；换句话说，如果指定的路径存在，则该目录将被关闭并替换为输出。
- 从Hive 0.11.0开始，可以使用指定的分隔符；在早期版本中，它始终是^A字符( 01)。但是，Hive版本0.11.0到1.1.0中，自定义分隔符只支持本地写入，这个bug在Hive 1.2.0中得到了修复(参见hive-5672)。
- 在Hive 0.14中，插入符合ACID的表将在SELECT和INSERT期间禁用矢量化，这将自动完成。插入数据后的ACID表仍然可以使用矢量化来查询。
四、SQL语句将值插入表
在INSERT ... VALUES语句可以用来从SQL中将数据直接插入到表。
版本信息
自Hive0.14开始支持INSERT ... VALUES。
4.1、 语法
标准语法: INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...] 此处的values_row is: ( value [, value ...] ) 此处的value或者是NULL或者是任何有效的sql表达式。
4.2、 概要
- 在VALUES子句中列出的每一行插入到表tablename中。
- VALUES子句必须为表中的每一列提供值。还不支持允许用户只将值插入某些列的标准SQL语法。若要模拟标准SQL，可以为用户向其不希望分配值的列提供空。
- 以与INSERT ... SELECT同样的方式，来支持动态分区。
- 如果要插入的表格支持ACID并且Hive正在使用一个支持ACID的事务管理器，该操作成功后将自动提交完成。
- Hive不支持复杂类型（数组、映射、结构、联合）的文字，所以它不可能在INSERT INTO ...VALUES子句中使用它们。这意味着用户不能使用INSERT INTO VALUES子句将数据插入复杂的数据类型列中。
译者注：我在Hive 2.3.3中验证上面第2条规则是不对的
drop table if exists test ; create table test(a int,b varchar(128)); insert into test (a,b) values (100,'tianjin'); insert into test (a) values (200),(300); insert into test values (400,'beijing'); select * from test ; 三条Insert into语句都是正确的，最后查询结果也是正确的 0: jdbc:hive2://hadoop15.gbase.cn:2181,hadoop> select * from test ; +---------+----------+ | test.a | test.b | +---------+----------+ | 100 | tianjin | | 200 | NULL | | 300 | NULL | | 400 | beijing | +---------+----------+
所以，在Hive 2.3.3中 Insert into 支持标准的SQL语句，可以直接插入部分列，其他列自动设置为NUL
例子
CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2)) CLUSTERED BY (age) INTO 2 BUCKETS STORED AS ORC; INSERT INTO TABLE students VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32); CREATE TABLE pageviews (userid VARCHAR(64), link STRING, came_from STRING) PARTITIONED BY (datestamp STRING) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC; INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23') VALUES ('jsmith', 'mail.com', 'sports.com'), ('jdoe', 'mail.com', null); INSERT INTO TABLE pageviews PARTITION (datestamp) VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21'); INSERT INTO TABLE pageviews VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');
五、更新
版本信息
自Hive0.14开始，可以使用UPDATE。
UPDATE只能在支持ACID表上执行。详见Hive事务。
5.1、 语法
标准语法:
UPDATE tablename SET column = value [, column = value ...] [WHERE expression]
5.2、 概要
- 被引用的列必须是被更新表中的列。
- 设置的值必须是Hive Select子句中支持的表达式。因此，算术运算符，UDF，转换，文字等，是支持的，子查询是不支持的。
- 只有符合WHERE子句的行会被更新。
- 分区列不能被更新。
- 分桶列不能被更新。
- 自Hive 0.14，在此UPDATE操作成功完成后，会自动提交。
5.3、 注释
- UPDATE操作中会关闭矢量化。这是自动的，无需用户任何操作。而非UPDATE操作不受影响。UPDATE后的表仍然可以使用矢量化进行查询。
- 自0.14版开始，建议您在UPDATE时设置 hive.optimize.sort.dynamic.partition =false，这样会产生更有效的执行计划。
六、删除
版本信息
自Hive0.14开始，可以使用DELETE。
DELETE只能在支持ACID表上执行。详见Hive事务。
6.1、 语法
标准语法:
DELETE FROM tablename [WHERE expression]
6.2、 概要
- 只有符合WHERE子句的行会被删除。
- 自Hive 0.14，在此DELETE操作成功完成后，会自动提交。
6.3、 注释
- DELETE操作中会关闭矢量化。这是自动的，无需用户任何操作。而非DELETE操作不受影响。DELETE后的表仍然可以使用矢量化进行查询。
- 自0.14版开始，建议您在DELETE时设置 hive.optimize.sort.dynamic.partition =false，这样会产生更有效的执行计划。
七、合并
版本信息
自Hive 2.2开始，可以使用MEGER。
MERGE只能在支持ACID表上执行。详见Hive事务。
7.1、 语法
标准语法: MERGE INTO <target table> AS T USING <source expression/table> AS S ON <boolean expression1> WHEN MATCHED [AND <boolean expression2>] THEN UPDATE SET <set clause list> WHEN MATCHED [AND <boolean expression3>] THEN DELETE WHEN NOT MATCHED [AND <boolean expression4>] THEN INSERT VALUES<value list>
7.2、 概要
- Merge允许根据与源表Join的结果对目标表执行操作。
- 在Hive 2.2中，在此操作成功完成后，更改将自动提交.
7.3、 性能注意事项
按SQL标准要求，如果ON子句是使得源中超过1行与目标中的1行匹配，就应该引发错误。此检查在计算上开销很大，可能会对合并语句的整个运行时产生显著影响。hive.merge.cardinality.check =false，可以禁用检查，这需要您自己承担风险。如果禁用检查是，但语句具有交叉连接效果，则可能导致数据损坏。
7.4、 注释
- 1，2，或3 WHEN子句都可以存在; 但每种类型的至多1次：UPDATE /DELETE/INSERT。
- WHEN NOT MATCHED必须是最后一个WHEN子句。
- 如果UPDATE和DELETE子句都存在，则在第一个子句中的必须包括[AND <布尔表达式>]。
- MERGE操作中会关闭矢量化。这是自动的，无需用户任何操作。而非MERGE操作不受影响。MERGE后的表仍然可以使用矢量化进行查询。
例子参见这里。