Hive 浅谈DML语法

前言

在《Hive 浅谈DDL语法》文章中主要介绍了DDL语法，今天就来聊聊 Hive 中的DML语法。

DML语法解析

1. Insert操作

1.1 Load文件到表中

语法：

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)

Hive 3.0 版本之前的 Load 操作是纯粹的复制/移动操作，将数据文件移动到与配置单元表相对应的位置。

• LOCAL：本地文件系统

• load命令将在本地文件系统中查找 filepath 。如果指定了相对路径，即相对于用户的当前工作目录。用户还可以为本地文件指定完整的URI，例如：file:///user/hive/project/data1
• load 命令将尝试将 filepath 寻址的所有文件复制到目标文件系统，通过查看表的 location 属性可以推断出目标文件系统。然后，复制的数据文件将被移动到表中。

注意：如果对 HiveServer2 实例运行此命令，则本地路径将引用 HiveServer2 中的一个路径。HiveServer2 必须具有访问该文件的权限。

• filepath：可以是如下几种方式

• 相对路径，例如：project/data1
• 绝对路径，例如：/user/hive/project/data1
• 带有scheme和authority（可选）的完整URI，例如：hdfs://namenode:9000/user/hive/project/data1

注意：filepath 可以指一个文件（在这种情况下，Hive 会将文件移动到表中），也可以是一个目录（在这种情形下，Hive

会将该目录中的所有文件移动到该表中）。在任何一种情况下，filepath 都会寻址一组文件。

• OVERWRITE：覆盖

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

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

Hive 3.0以后版本支持额外的 Load 操作，因为 Hive 在内部将 Load 重写为INSERT as SELECT。

• 若表有分区，而load命令并没有分区，则加载将转换为INSERT AS SELECT，并假设最后一组列是分区列。如果文件不符合预期的schema，它将抛出一个错误。
• 如果是分桶表，则遵循如下规则：

• 在严格模式下：启动INSERT AS SELECT作业。
• 在非严格模式下：如果文件名符合命名约定（如果文件属于bucket 0，则应命名为000000_0或000000_0_copy_1，或者如果它属于bucket 2，则名称应类似于000002_0或000002_0_copy_3，等等），则它将是纯复制/移动操作，否则它将启动INSERT AS SELECT作业。

• filepath 可以包含子目录，前提是每个文件都符合schema。
• 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;

注意：Hive

会进行一些最小限度的检查，以确保加载的文件与目标表匹配。例如如果表以sequencefile格式存储，则加载的文件也是sequencefiles，反之亦然。

1.2 Insert 查询结果到Hive表中

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

语法：

# 标准语法:
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;

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

• 除非为分区提供了IF NOT EXISTS
• 自Hive 2.3.0 版本起，如果表具有TBLPROPERTIES（“auto.pruge”=“true”），则在对表运行INSERT OVERWRITE查询时，不会将表的先前数据移动到垃圾桶。此功能仅适用于托管表。

• INSERT INTO将追加到表或分区，从而保持现有数据的完整性。

• INSERT INTO语法从 Hive 0.8版本开始可用。
• 从Hive 0.13.0 版本开始，可以通过使用TBLPROPERTIES（“immutable”=“true”）创建表来使其不可变。默认值为"immutable"=“true”。如果已经存在任何数据，则不允许将INSERT INTO行为插入到不可变表中，尽管如果不可变表为空，则INSERT INTO仍然有效。INSERT OVERWRITE的行为不受“不可变”表属性的影响。不可变表可以防止意外更新，因为将数据加载到其中的脚本会错误地多次运行。对不可变表的第一次插入成功，连续插入失败，导致表中只有一组数据，而不是静默地成功插入表中的多个数据副本。

• 可以对表或分区进行插入。如果表是分区的，那么必须通过为所有分区列指定值来指定表的特定分区。如果hive.typecheck.on.insert设置为true，则会验证、转换和规范化这些值，以符合其列类型（Hive 0.12.0 版本开始）。
• 可以在同一查询中指定多个插入子句（也称为多表插入）。

注意：多表插入可最大限度地减少所需的数据扫描次数。Hive可以通过只扫描输入数据一次（并对输入数据应用不同的查询运算符）将数据插入多个表中。

• 每个select语句的输出都被写入所选的表（或分区）。目前，OVERWRITE关键字是强制性的，这意味着所选表或分区的内容将被相应的select语句的输出所替换。
• 输出格式和序列化类由表的元数据决定（通过表上的DDL命令指定）。
• 从Hive 0.14 版本开始，如果表具有实现AcidOutputFormat的OutputFormat，并且系统配置为使用实现ACID的事务管理器，则将禁用该表的INSERT OVERWRITE。这是为了避免用户无意中覆盖事务历史记录。同样的功能可以通过使用TRUNCATE TABLE（对于非分区表）或DROP PARTITION然后再使用INSERT INTO来实现。
• 从 Hive 1.1.0 版本开始，TABLE关键字是可选的。
• 从 Hive 1.2.0 版本开始，每个INSERT INTO T都可以采用类似于INSERT INTO（z，x，c1）的列列表。
• 从 Hive 3.1.0 版本开始，不允许在完整的CRUD ACID表上使用UNION ALL的源进行INSERT OVERWRITE。

动态分区插入：

• 在动态分区插入中，用户可以给出部分分区规范，这意味着只需在partition子句中指定分区列名列表。
• 列值是可选的。如果给定了分区列值，我们将其称为静态分区，否则它就是动态分区。
• 每个动态分区列都有来自select语句的相应输入列。这意味着动态分区的创建是由输入列的值决定的。必须在SELECT语句中的列中最后指定动态分区列，并且指定顺序与它们在partition（）子句中出现的顺序相同。
• 从Hive 3.0.0 版本开始，不需要指定动态分区列。
• 如果没有指定分区规范，Hive 将自动生成分区规范。

在 Hive 0.9.0 版本之前，默认情况下禁用动态分区插入，在 Hive 0.9.0及更高版本中默认情况下启用动态分区插入。

以下是动态分区插入的相关配置：

例子：

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

1.3 将查询结果写入文件系统

查询结果可以通过使用上面语法的细微变化插入到文件系统目录中。

语法：

# 标准语法:
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)

• 目录可以是完整的URI。如果未指定 scheme 或 authority，则Hive将使用hadoop配置变量 fs.default.name 中指定Namenode URI的 scheme 和 authority。
• 如果使用LOCAL关键字，则Hive会将数据写入本地文件系统上的目录。
• 写入文件系统的数据被序列化为文本，列由 ‘\001’ 分隔，行由换行符分隔。如果任何列不是基元类型，那么这些列将被序列化为 JSON 格式。

1.4 INSERT…VALUES

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

语法：

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

• INSERT…VALUES 从 Hive 0.14 版本开始支持。
• VALUES子句中列出的每一行都插入到表tablename中。
• 必须为表中的每一列提供值。目前还不支持允许用户仅在某些列中插入值的标准SQL语法。为了模仿标准SQL，可以为用户不希望为其赋值的列提供null。
• 与 INSERT…SELECT 相同的方式支持动态分区。
• 如果插入的表支持ACID，并且正在使用支持ACID的事务管理器，则此操作将在成功完成后自动提交。
• Hive 不支持复杂类型（数组、映射、结构、联合）的文字，因此不可能在INSERT INTO … VALUES子句中使用它们，这意味着用户不能使用INSERT INTO … VALUES子句将数据插入复杂的数据类型列。

例子：

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

2. Update操作

语法：

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

• 只能对支持ACID的表执行 Update 操作。
• 指定的值必须是 Hive 在select子句中支持的表达式。因此，支持算术运算符、UDF、强制转换、文字等。不支持子查询。
• 只有与WHERE子句匹配的行才会被更新。
• 无法更新分区列。
• 无法更新分桶列。
• 从 Hive 0.14 版本开始支持此操作，成功完成此操作后，更改内容将自动提交。
• 进行更新操作时将自动关闭矢量化，不需要用户采取任何行动。非更新操作不受影响。更新后的表仍然可以使用矢量化进行查询。
• 建议在进行更新时设置hive.optimity.sort.dynamic.partition=false，因为这样可以生成更高效的执行计划。

3. Delete操作

语法：

Standard Syntax:
DELETE FROM tablename [WHERE expression]

• 只能对支持ACID的表执行 Delete 操作。
• 只有与WHERE子句匹配的行才会被删除。
• 从 Hive 0.14 版本开始支持此操作，成功完成此操作后，更改内容将自动提交。
• 进行删除操作时将自动关闭矢量化，不需要用户采取任何行动。非更新操作不受影响。更新后的表仍然可以使用矢量化进行查询。
• 建议在进行删除时设置hive.optimity.sort.dynamic.partition=false，因为这样可以生成更高效的执行计划。

4. Merge操作

语法：

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>

• 允许根据与源表的联接结果对目标表执行 Merge 操作。
• 从 Hive 2.2 版本开始支持此操作，成功完成此操作后，将自动提交更改。
• 最多只能存在一种类型的 WHEN 字句，可用的类型：UPDATE/DELETE/INSERT。
• Merge 操作将自动关闭矢量化，不需要用户采取任何行动。非删除操作不受影响。对于已删除数据的表仍然可以使用矢量化进行查询。