# HiveQL 语言手册 DML篇

HiveQL DML 所包含的表达式语句如下：

• LOAD (加载)
• INSERT (插入)
  • into Hive tables from queries
  • into directories from queries
  • into Hive tables from SQL
• UPDATE (更新)
• DELETE (删除)
• MERGE (合并)
• EXPORT (导出)
• IMPORT (导入)

# Load

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

# 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)

# Load 概要

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

• 文件路径可以是：
  • 相对路径，例如 ：project/data1
  • 绝对路径，例如 ：/user/hive/project/data1
  • 一个包含scheme和(可选)权限的完整URI，例如 ：hdfs://namenode:9000/user/hive/project/data1
• 加载的目标可以是一个表或一个分区。如果表已分区，则必须通过为分区列指定值，来确认数据加载的目标分区。
• 文件路径可以只指定一个文件（在这种情况下Hive会将文件移动到表中），也可以是一个目录（在这种情况下Hive会将该目录中的所有文件移动到表中）。
• 如果指定了关键字LOCAL，则：
  • load命令将在本地文件系统中查找文件路径。如果指定了相对路径，它将相对于用户的当前工作目录进行解释。用户也可以为本地文件指定一个完整的URI，例如：file:///user/hive/project/data1
  • load命令将尝试将指定的文件路径下的所有文件复制到目标文件系统。目标文件系统是通过查看表的位置属性来推断的。然后复制的数据文件到表中。
  • 注意：如果对HiveServer2实例运行此命令，则本地路径指的是HiveServer2实例上的路径。HiveServer2必须具有访问该文件的适当权限。
• 如果关键字LOCAL没有指定，那么Hive将使用filepath的完整URI，如果指定了一个，或者将应用以下规则：
  • 如果没有指定scheme或权限，Hive将使用hadoop配置的变量fs.default.name中的scheme和权限，该变量指定Namenode的URL
  • 如果路径不是绝对的，那么Hive将相对于/user/<username>来解释它
  • Hive会把filepath寻址的文件移到表(或分区)中
• 如果使用了OVERWRITE关键字，那么原目标表(或分区)中的内容将被删除，并被filepath引用的文件所取代；否则，filepath引用的文件将被添加到表中。

Hive 3.0以后还支持额外的加载操作，因为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;

# Load 其他

• filepath不能包含子目录(Hive 3.0及以上版本除外，如上所述)。
• 如果没有给出关键字LOCAL，则filepath必须指与表(或分区)位置相同的文件系统中的文件。
• Hive在加载前会做一些最小程度的检查，以确保加载的文件与目标表匹配。目前它检查表是否以sequencefile格式存储，正在加载的文件也是sequencefile。
• 在0.13.0版本(HIVE-6048)中修复了文件名包含“+”字符时无法加载的错误。
• 如果数据文件是压缩类型的，请参阅CompressedStorage。

# Insert

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

# Insert 语法

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

# Insert 概要

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

  • 除非为分区提供IF NOT EXISTS(从Hive 0.9.0开始)。
  • 从 Hive 2.3.0版本开始 (HIVE-15880)，如果表有TBLPROPERTIES ("auto.purge"="true")，当对表运行INSERT OVERWRITE查询时，表中的原数据不会移动到Trash目录（此功能仅适用于内表）。

• INSERT INTO 会把数据追加到已有的表或分区中，不影响原来的数据。（注意：INSERT INTO 语法只在 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开始）。

• 可以在同一查询中指定多个插入子句（也称为多表插入）。

• 每个select语句的输出都将写入所选表（或分区）。目前，OVERWRITE关键字是必需的，并意味着所选表或分区的内容将被相应select语句的输出替换。

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

• 从Hive 0.14开始，如果一个表具有实现AcidOutputFormat的OutputFormat，并且系统配置为使用实现ACID的transaction管理器，则将禁用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的说明。

• 从Hive 3.1.0开始，不允许从具有全CRUD ACID表上的UNION ALL的源进行INSERT OVERWRITE。

# Insert 注意

• 多表插入最小化了所需的数据扫描次数。Hive可以通过仅扫描一次输入数据（并应用不同的查询运算符）来将数据插入多个表中。
• 从Hive 0.13.0开始，select语句可以包括一个或多个公共表达式（CTE），如SELECT语法所示。有关示例，请参见Common Table Expression。

# 动态分区插入

在动态分区插入中，用户可以给出部分分区规范，这意味着只在PARTITION子句中指定分区列名的列表。列值是可选的。如果给出分区列值，则称为静态分区，否则为动态分区。每个动态分区列都有一个对应的来自SELECT语句的输入列。这意味着动态分区创建是由输入列的值确定的。动态分区列必须在SELECT语句中的列中最后指定，并且在PARTITION()子句中以相同的顺序出现。从Hive 3.0.0（HIVE-19083）开始，不需要指定动态分区列。如果未指定，则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

这里的country分区将由SELECT子句中的最后一列（即pvs.cnt）动态创建。请注意，名称未使用。在nonstrict模式下，dt分区也可以动态创建。

# Insert Overwrite

查询结果可以通过使用下述语法插入到文件系统目录中：

# Insert Overwrite 语法

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)

# Insert Overwrite 概要

• 如果目录是完整的URI，则可以使用完整的URI。
• 如果未指定方案或权限，则Hive将使用hadoop配置变量fs.default.name中指定的Namenode URI的方案和权限。
• 如果使用LOCAL关键字，则Hive将数据写入本地文件系统上的目录。写入文件系统的数据被序列化为文本，其中列由^A分隔，行由换行符分隔。如果任何列不是原始类型，则这些列将序列化为JSON格式。
• 注意：INSERT OVERWRITE语句可以同时用于目录、本地目录和表（或分区）中。
• 从Hive 0.11.0开始，可以指定用作分隔符的字符；在早期版本中，它总是^A字符（\001）。但是，在Hive版本0.11.0到1.1.0中，仅支持LOCAL写入的自定义分隔符——此错误已在版本1.2.0中修复（请参见HIVE-5672）。
• 在Hive 0.14中，插入到ACID兼容表中的插入将在选择和插入期间停用矢量化。这将自动完成。仍然可以使用矢量化查询插入数据的ACID表。

# Insert Values

The INSERT…VALUES语句可通过SQL直接将数据插入表中。

# Insert Values 语法

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 概要

• 在VALUES子句中列出的每一行都将插入到表tablename中。
• 必须为表中的每一列提供值。尚不支持用户仅向某些列插入值的SQL语法。为实现类似的插入，可以为不希望分配值的列提供null。动态分区与INSERT…SELECT相同。
• 如果要插入的表支持ACID，并且正在使用支持ACID的事务管理器，则此操作将在成功完成后自动提交。
• Hive不支持复杂类型（数组、映射、结构、联合）的文字，因此不能在INSERT INTO…VALUES子句中使用它们。这意味着用户无法使用INSERT INTO…VALUES子句将数据插入到复杂数据类型列中。

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

# Update

# Update 语法

Standard Syntax:
DELETE FROM tablename [WHERE expression]

# Update 概要

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

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

• 只有与WHERE子句匹配的行才会更新。

• 无法更新分区列。

• 无法更新桶列。

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

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

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

# Delete

# Delete 语法

Standard Syntax:
DELETE FROM tablename [WHERE expression]

# Delete 概要

• 只有与WHERE子句匹配的行才会被删除。
• 在Hive 0.14中，此操作成功完成后将自动提交更改。

# Delete 其他

• 对于删除操作，将关闭矢量化。这是自动的，用户不需要采取任何措施。非删除操作不受影响。仍然可以使用矢量化查询已删除数据的表。
• 在版本0.14中，建议在进行删除时将hive.optimize.sort.dynamic.partition=false，因为这会产生更有效的执行计划。

# Merge

# Merge 语法

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>

# Merge 说明

• Merge允许根据与源表的联接结果对目标表执行操作。
• 在Hive 2.2中，此操作成功完成后将自动提交更改。
• SQL标准要求，如果ON子句是这样的：即源中的多行与目标中的一行匹配，则会引发错误。此检查计算成本高昂，可能会显着影响MERGE语句的总运行时间。设置hive.merge.cardinality.check=false可以用于自行承担风险禁用检查。如果禁用了检查，但语句具有这种交叉连接效果，则可能导致数据损坏。
• 可以存在1、2或3个WHEN子句；每种类型最多1个：UPDATE/DELETE/INSERT。
• WHEN NOT MATCHED必须是最后一个WHEN子句。
• 如果UPDATE和DELETE子句都存在，则语句中的第一个子句必须包括AND子句。
• 对于合并操作，将关闭矢量化。这是自动的，用户不需要采取任何措施。非删除操作不受影响。仍然可以使用矢量化查询已删除数据的表。