Hive-HiveDML+数据导入导出

1. Hive DML

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

• LOAD
• INSERT
  • into Hive tables from queries
  • into directories from queries
  • into Hive tables from SQL
• UPDATE
• DELETE
• MERGE
  另外，EXPORT 和 IMPORT 命令也可以使用(截至Hive0.8)

1.1 Load方式导入数据

使用Load的方式，数据不会有转换动作。Load操作只是单纯的将数据从原目录移动到目标Hive表的对应数据目录。
语法：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

说明：Hive3.0之前的Hive Load操作是纯移动操作，用于将数据文件移动到与Hive表相对应的数据目录。

• filepath 可以是：
  • 相对路径：比如 project/data1
  • 绝对路径：比如 /user/hive/warehouse/test_table
  • 完整URI, 比如 hdfs://namenode:9000/user/hive/project/data1
• 数据加载的目标端可以是一个表或者是一个分区。如果表是分区表，那么必须通过为所有分区列指定值来指定表的特定分区。
• 文件路径可以引用一个文件（在这种情况下，Hive将文件移动到表中），也可以是一个目录（在这种情况下，Hive将该目录中的所有文件移动到表中）。在这两种情况下，filepath都表示一组文件。
• 如果含关键词LOCAL，那么：
  • 加载命令会在本地文件系统查找filepath。如果指定的是相对路径，那么他会理解为用户当前工作路径。用户也可以指定绝对路径或者完整的URI，比如：/user/hive/project/data1和file:///user/hive/project/data1
  • Load命令会尝试拷贝filepath指定的所有文件到目标文件系统。通过查看表的location属性推断目标文件系统。然后将复制的数据文件移动到表中。
  • 注意：如果对HiveServer2实例运行此命令，则本地路径将引用HiveServer2实例上的路径。HiveServer2必须具有访问该文件的适当权限。
• 如果不包含关键词LOCAL,那么Hive要么使用filepath的完整URI，要么遵循以下规则：
  • 如果系统前缀没有加，那么Hive会从Hadoop配置文件中读取fs.default.name作为系统前缀来指定完整URI.
  • 如果filepath不是绝对路径，Hive会参考拼接上/user/<username>
• 如果包含关键词OVERWRITE，数据加载方式为覆盖，否则为追加。
• 但是，如果表有分区，而Load命令中没有指定分区，则加载时将转换为INSERT AS SELECT，并假定最后一组列是分区列。如果文件不符合预期的架构，它将报错。

1.2 将查询结果插入表

语法：

# 标准语法:
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 延伸 (多个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 延伸 (动态分区入库):
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 覆盖表或分区
• INSERT INTO 追加
• 在动态分区入库中，用户可以给出部分分区规范，这意味着只在分区子句中指定分区列名称的列表。列值是可选的。如果给定了分区列值，我们将其称为静态分区，否则它是动态分区。每个动态分区列都有一个来自select语句的相应输入列。这意味着动态分区的创建由输入列的值决定。动态分区列必须在SELECT语句中的列中最后指定，并以它们出现在PARTITION()子句中的相同顺序指定。
  动态分区入库在Hive0.9.0之前默认禁用，在Hive0.9.0和更高版本中默认启用。以下是动态分区插入的相关配置属性：

动态分区配置属性	默认值	备注
hive.error.on.empty.partition	false	动态分区入库生成空结果时，是否抛异常
hive.exec.dynamic.partition	true	true 表示开启动态分区入库
hive.exec.dynamic.partition.mode	strict	strict模式下，用户必须制定至少一个静态分区，防止用户以外覆盖了所有分区。nonstrict模式下所有分区都允许被动态更新
hive.exec.max.created.files	100000	MapReduce作业中所有mappers/reducers创建的HDFS文件的最大数目
hive.exec.max.dynamic.partitions	1000	允许被创建的动态分区数目的最大值
hive.exec.max.dynamic.partitions.pernode	100	在每个mappers/reducers中允许被创建的动态分区数目的最大值

Example：

INSERT OVERWRITE TABLE page_view PARTITION(dt='2008-06-08', country)
SELECT t.viewTime, t.userid, t.page_url, t.referrer_url, null, null, t.ip, t.cnt
FROM page_view_stg t

1.3 数据导出到本地

INSERT OVERWRITE DIRECTORY ‘/hivetmp’ ROW FORMAT DELIMITED FIELDS TERMINATED BY ‘\t’ SELECT empno,ename FROM emp;
语法：

# 标准语法:
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 延伸 (多个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)

Example：

INSERT OVERWRITE LOCAL DIRECTORY '/hivetmp'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT empno,ename FROM emp;