Hive 数据处理语言

在 Hive 中有多种修改数据的方法：

• LOAD

• INSERT

• 从查询进入 Hive 表

  • 从查询进入目录

  • 从 SQL 到 Hive 表

• UPDATE

• DELETE

• MERGE

进 Export命令也可用(自Hive 0.8起)。

将文件加载到表中

Hive 在将数据加载到表中时不会进行任何转换。加载操作当前是纯复制/移动操作，可将数据文件移动到与 Hive 表相对应的位置。

Syntax

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)

Synopsis

Hive 3.0 之前的加载操作是纯复制/移动操作，可将数据文件移动到与 Hive 表相对应的位置。

• 文件路径可以是：

• 相对路径，例如project/data1

  • 绝对路径，例如/user/hive/project/data1

  • 具有方案和(可选)权限的完整 URI，例如hdfs://namenode:9000/user/hive/project/data1

• 加载到的目标可以是表或分区。如果表已分区，则必须通过为所有分区列指定值来指定表的特定分区。

• • filepath 可以引用文件(在这种情况下，Hive 会将文件移至表中)，也可以是目录(在这种情况下，Hive 会将目录中的所有文件移至表中)。无论哪种情况， filepath *都会寻址一组文件。

• 如果指定了关键字 LOCAL，则：

• load 命令将在本地文件系统中查找* filepath *。如果指定了相对路径，它将相对于用户的当前工作目录进行解释。用户也可以为本地文件指定完整的 URI，例如：file:///user/hive/project/data1

  • load 命令将尝试将* filepath *寻址的所有文件复制到目标文件系统。通过查看表的 location 属性可以推断目标文件系统。然后，复制的数据文件将被移到表中。

  • 注意：如果对 HiveServer2 实例运行此命令，则本地路径是指 HiveServer2 实例上的路径。 HiveServer2 必须具有适当的权限才能访问该文件。

• 如果未指定关键字 LOCAL，则 Hive 将使用* filepath *的完整 URI(如果已指定)，或将应用以下规则：

• 如果未指定方案或权限，则 Hive 将使用 hadoop 配置变量fs.default.name中的方案和权限，该变量指定名称节点 URI。

  • 如果该路径不是绝对路径，则 Hive 将相对于/user/<username>对其进行解释

  • Hive 会将* filepath 寻址的文件移动到表(或分区)中

• 如果使用 OVERWRITE 关键字，则目标表(或分区)的内容将被删除，并由* filepath 引用的文件替换；否则，由 filepath *引用的文件将被添加到表中。

Hive 3.0 及更高版本支持其他加载操作，因为 Hive 在内部将加载重写为 INSERT AS SELECT。

• 但是，如果表具有分区，则 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 *可以是任何 HiveImporting 格式，例如文本，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 *的文件符合表模式，使得每一行以分区列结尾，则负载将被重写为 INSERT AS SELECT 作业。

未压缩的数据应如下所示：

(1,2,3)，(2,3,4)，(4,5,3)等。

Notes

• • filepath *不能包含子目录(如上所述，Hive 3.0 或更高版本除外)。

• 如果未给出关键字 LOCAL，则* filepath *必须引用与表(或分区)位置相同的文件系统中的文件。

• Hive 进行了一些最少的检查，以确保要加载的文件与目标表匹配。当前，它会检查表是否以 sequencefile 格式存储，正在加载的文件也是 sequencefile，反之亦然。

• 在版本 0.13.0(HIVE-6048)中修复了一个阻止在文件名中包含“”字符时加载文件的错误。

• 如果您的数据文件已压缩，请阅读CompressedStorage。

通过查询将数据插入到 Hive 表中

可以使用 insert 子句将查询结果插入表中。

Syntax

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;

Synopsis

• INSERT OVERWRITE 将覆盖表或分区中的任何现有数据

• 除非为分区提供IF NOT EXISTS(从 Hive 0.9.0起)。

  • 从 Hive 2.3.0(HIVE-15880)开始，如果表具有TBLPROPERTIES(“ auto.purge” =“ true”)，则对该表运行 INSERT OVERWRITE 查询时，该表的先前数据不会移至“已删除邮件”。此功能仅适用于托管表(请参见managed tables)，并且在未设置“ auto.purge”属性或将其设置为 false 时将关闭此功能。

• INSERT INTO 将追加到表或分区，使现有数据保持不变。 (注意：INSERT INTO 语法仅从版本 0.8 开始可用.)

• 从 Hive 0.13.0开始，可以通过使用TBLPROPERTIES ("immutable"="true")创建表使其成为不可变***。默认值为“ immutable” =“ false”。
  如果已经存在任何数据，则不允许在不可变表中执行 INSERT INTO 行为，尽管如果不可变表为空，则 INSERT INTO 仍然有效。 INSERT OVERWRITE 的行为不受“不可变”表属性的影响。
  不可变的表可以防止意外更新，因为脚本将数据加载到该表中会导致错误的多次运行。对不可变表的第一次插入成功，而随后的插入失败，导致表中只有一组数据，而不是在表中多次复制数据而无提示地成功。

• 可以对表或分区进行插入。如果表已分区，则必须通过为所有分区列指定值来指定表的特定分区。如果hive.typecheck.on.insert设置为 true，则将验证，转换和标准化这些值以使其符合其列类型(从 Hive 0.12.0开始)。

• 可以在同一查询中指定多个 insert 子句(也称为* Multi Table Insert *)。

• 每个 select 语句的输出都将写入所选的表(或分区)。当前，OVERWRITE 关键字是强制性的，它表示将所选表或分区的内容替换为对应的 select 语句的输出。

• 输出格式和序列化类由表的元数据确定(通过表上的 DDL 命令指定)。

• 从Hive 0.14开始，如果表具有实现 AcidOutputFormat 的 OutputFormat，并且系统配置为使用实现 ACID 的transactionManagement 器，则将对该表禁用 INSERT OVERWRITE。这是为了避免用户无意间覆盖 Transaction 历史记录。可以通过使用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的说明。

Notes

• 多表插入可最大程度地减少所需的数据扫描次数。 Hive 可以通过只扫描一次 Importing 数据(并应用不同的查询运算符)到 Importing 数据来将数据插入到多个表中。

• 从Hive 0.13.0开始，select 语句可以包含一个或多个通用表表达式(CTE)，如SELECT syntax所示。有关示例，请参见公用表表达式。

动态分区插入

在动态分区插入中，用户可以提供部分分区规范，这意味着只需在 PARTITION 子句中指定分区列名称的列表。列值是可选的。如果给出了分区列的值，我们称其为静态分区，否则为动态分区。每个动态分区列在 select 语句中都有一个对应的 Importing 列。这意味着动态分区的创建取决于 Importing 列的值。动态分区列必须在 SELECT 语句的列中“最后指定”，并且必须与它们在 PARTITION()子句中出现的 Sequences 相同。从 Hive 3.0.0(HIVE-19083)开始，无需指定动态分区列。如果未指定，Hive 将自动生成分区规范。

默认情况下，在 Hive 0.9.0 之前禁用动态分区插入，并在 Hive 0.9.0及更高版本中默认启用。这些是动态分区插入的相关配置属性：

Configuration property	Default	Note
hive.exec.dynamic.partition	true	需要设置为true以启用动态分区插入
hive.exec.dynamic.partition.mode	strict	在strict模式下，用户必须至少指定一个静态分区，以防万一用户意外覆盖了所有分区；在nonstrict模式下，允许所有分区都是动态的
hive.exec.max.dynamic.partitions.pernode	100	每个 Map 器/还原器节点中允许创建的最大动态分区数
hive.exec.max.dynamic.partitions	1000	总共允许创建的最大动态分区数
hive.exec.max.created.files	100000	MapReduce 作业中所有 Map 器/还原器创建的 HDFS 文件的最大数量
hive.error.on.empty.partition	false	如果动态分区插入生成空结果，是否引发异常

Example

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分区。

Additional Documentation

• Design Document

• 原始设计文件

  • HIVE-936

• 教程：动态分区插入

• HCatalog 动态分区

• 与猪一起使用

  • MapReduce 的用法

通过查询将数据写入文件系统

可以使用上面语法的一些细微变化将查询结果插入文件系统目录：

Syntax

Standard syntax:
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 extension (multiple inserts):
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)

Synopsis

• 目录可以是完整的 URI。如果未指定方案或权限，则 Hive 将使用 hadoop 配置变量fs.default.name中的方案和权限，该变量指定名称节点 URI。

• 如果使用 LOCAL 关键字，则 Hive 会将数据写入本地文件系统上的目录。

• 写入文件系统的数据被序列化为文本，列之间用^ A 隔开，行之间用换行符隔开。如果任何列都不是原始类型，那么这些列将序列化为 JSON 格式。

Notes

• 对目录，本地目录和表(或分区)的 INSERT OVERWRITE 语句可以在同一查询中一起使用。

• 对 HDFS 文件系统目录执行 INSERT OVERWRITE 语句是从 Hive 提取大量数据的最佳方法。 Hive 可以从 map-reduce 作业中并行写入 HDFS 目录。

• 如您所料，该目录已被覆盖；换句话说，如果指定的路径存在，它将被破坏并被输出替换。

• 从 Hive 0.11.0起，可以指定使用的分隔符；在早期版本中，它始终是^ A 字符( 001)。但是，自定义分隔符仅在 Hive 版本 0.11.0 至 1.1.0 中支持 LOCAL 写入-此错误已在版本 1.2.0 中得到修复(请参见HIVE-5672)。

• 在Hive 0.14中，插入到ACID兼容表中将在选择和插入期间停用向量化。这将自动完成。仍然可以使用矢量化查询已插入数据的 ACID 表。

从 SQL 向表中插入值

INSERT ... VALUES 语句可用于直接从 SQL 将数据插入表中。

Syntax

Standard Syntax:
INSERT INTO TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

Where values_row is:
( value [, value ...] )
where a value is either null or any valid SQL literal

Synopsis

• VALUES 子句中列出的每一行都插入表* tablename *中。

• 必须为表中的每一列提供值。尚不支持允许用户仅将值插入某些列的标准 SQL 语法。为了模仿标准 SQL，可以为用户不希望为其分配值的列提供空值。

• 支持动态分区的方式与INSERT...SELECT相同。

• 如果要插入支持ACID的表，并且正在使用支持 ACID 的事务 Management 器，则成功完成后将自动提交此操作。

• Hive 不支持复杂类型(数组，Map，结构，联合)的 Literals，因此无法在 INSERT INTO ... VALUES 子句中使用它们。这意味着用户无法使用 INSERT INTO ... VALUES 子句将数据插入到复杂数据类型列中。

Examples

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');

Update

Syntax

Standard Syntax:
UPDATE tablename SET column = value [, column = value ...] [WHERE expression]

Synopsis

• 引用的列必须是要更新的表的列。

• 分配的值必须是 Hive 在 select 子句中支持的表达式。因此，支持算术运算符，UDF，强制转换，Literals 等。不支持子查询。

• 仅匹配 WHERE 子句的行。

• 分区列无法更新。

• 存储桶列无法更新。

• 在 Hive 0.14 中，成功完成此操作后，更改将自动提交。

Notes

• 矢量化将被关闭以进行更新操作。这是自动的，不需要用户采取任何措施。非更新操作不受影响。仍然可以使用矢量化查询更新的表。

• 在版本 0.14 中，建议您在进行更新时设置hive.optimize.sort.dynamic.partition = false，因为这会产生更有效的执行计划。

Delete

Syntax

Standard Syntax:
DELETE FROM tablename [WHERE expression]

Synopsis

• 仅匹配 WHERE 子句的行将被删除。

• 在 Hive 0.14 中，成功完成此操作后，更改将自动提交。

Notes

• 矢量化将被关闭以进行删除操作。这是自动的，不需要用户采取任何措施。非删除操作不受影响。带有删除数据的表仍可以使用矢量化查询。

• 在版本 0.14 中，建议您在执行删除操作时设置hive.optimize.sort.dynamic.partition = false，因为这会产生更有效的执行计划。

Merge

Syntax

Standard Syntax:
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>

Synopsis

• Merge允许基于与源表的联接结果在目标表上执行操作。

• 在 Hive 2.2 中，成功完成此操作后，更改将自动提交。

Performance Note

如果 ON 子句使得源中的多行与目标中的一行匹配，则 SQL Standard 要求引发错误。该检查的计算量很大，并且可能会严重影响 MERGE 语句的整体运行时间。 hive.merge.cardinality.check = false 可能会被用来禁用该检查，后果自负。如果禁用了检查，但是该语句具有这种交叉联接的效果，则可能导致数据损坏。

Notes

• 可能存在 1，2 或 3 个 WHEN 子句；每种类型最多 1 个：UPDATE/DELETE/INSERT。

• 未匹配时必须是最后的 WHEN 子句。

• 如果同时存在 UPDATE 和 DELETE 子句，则语句中的第一个必须包含[AND<boolean expression>]。

• 矢量化将关闭以进行合并操作。这是自动的，不需要用户采取任何措施。非删除操作不受影响。带有删除数据的表仍可以使用矢量化查询。

Examples

• See here.