Hive 官方手册翻译 -- Hive DML(数据操纵语言)

由 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字符(\001)。但是，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后的表仍然可以使用矢量化进行查询。

例子参见这里。

posted @ 2018-10-07 12:32 范振勇阅读(2344) 评论(0) 编辑收藏举报

刷新页面返回顶部

范振勇

Hive 官方手册翻译 -- Hive DML(数据操纵语言)

一、从文件加载到表

1.1、 语法

1.2、 概要

1.3、 注释

二、将数据从查询插入Hive表

2.1、 语法

2.2、 概要

2.3、 注释

2.4、 动态分区插入模式

三、将数据从查询写入到文件系统

3.1、 语法

3.2、 概要

3.3、 注释

四、SQL语句将值插入表

4.1、 语法

4.2、 概要

五、更新

5.1、 语法

5.2、 概要

5.3、 注释

六、删除

6.1、 语法

6.2、 概要

6.3、 注释

七、合并

7.1、 语法

7.2、 概要

7.3、 性能注意事项

7.4、 注释

公告

1.1、语法

1.2、概要

1.3、注释

2.1、语法

2.2、概要

2.3、注释

2.4、动态分区插入模式

3.1、语法

3.2、概要

3.3、注释

4.1、语法

4.2、概要

5.1、语法

5.2、概要

5.3、注释

6.1、语法

6.2、概要

6.3、注释

7.1、语法

7.2、概要

7.3、性能注意事项

7.4、注释