HiveQL DML—数据加载和插入

使用LOAD DATA加载数据

Hive使用LOAD DATA加载数据的时候，不会进行任何的数据转换操作。完全就是简单的将数据文件复制/移动到表对应的路径下

语法

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之前，加载操作就是简单的将数据文件复制/移动到表对应的路径下

• 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命令复制由filepath指定的所有文件到目标文件系统。通过查看表的Location属性推断目标文件系统。然后将复制的数据文件移到表中。
  • 注意：如果是在一个HiveServer2实例上运行Load命令，则其本地路径指的是在HiveServer2实例上的路径。同时，HiveServer2必须具有访问该文件的适当权限。
• 如果没指定关键字LOCAL，HIVE要么使用完整URI的文件路径（如果指定），要么应用以下规则：
  • 如果未指定scheme或授权信息，Hive将使用来自Hadoop配置变量fs.default.name指定的Namenode URI的scheme和授权信息。
  • 如果不是绝对路径，那么HIVE会相对于 /user/<username>来解释路径。
  • HIVE将移动filepath所指定的文件到表（或分区）的文件路径。
• 如果使用了OVERWRITE关键字，则目标表（或分区）的内容将被filepath指定的文件内容覆盖。

从Hive 3.0开始，支持另一种方式的Load操作，它会在Hive内部重写为一个INSERT AS SELECT操作。

• 如果表有分区，但Load命令没有指定分区，则Load将被转换成INSERT AS SELECT，并且假设最后一组字段是分区字段。如果文件不符合预期的Schema，则它会抛出一个错误。
• 如果是分桶表，则遵循以下规则：
  • strict 模式：启动一个INSERT AS SELECT作业。
  • non-strict模式：如果文件名符合命名惯例（如果该文件属于桶0，它应该被命名为000000_0或000000_0_copy_1，或者如果它属于桶2名应该像000002_0或000002_0_copy_3 等。 ），那么这将是一个纯粹的复制/移动操作，反之，它将启动一个INSERT AS SELECT工作。
• filepath可以包含子目录，提供的每个文件都符合该模式。
• inputformat可以是Hive的任何输入格式，如text，ORC等
• serde可以关联Hive SERDE
• inputformat和serde都是大小写敏感的。

注意

• Hive3.0之前，filepath不能包含子目录
• 如果不指定关键字LOCAL，则filepath引用的文件必须同Hive表（或分区）的位置处于同一文件系统中
• Hive会做一些最基础的检查，以确保这些加载文件匹配目标表。目前，如果该目标表的存储格式为sequencefile，它会检查加载的文件是否也为sequencefiles，以此类推。
• Gzip、Bzip2压缩格式的文本文件可以直接加载到存储格式为TextFile的表中

示例

示例：加载本地数据

--加载到表
> LOAD DATA LOCAL INPATH
 '/home/dayongd/Downloads/employee_hr.txt'
 OVERWRITE INTO TABLE employee_hr;

--加载到分区
> LOAD DATA LOCAL INPATH
'/home/dayongd/Downloads/employee.txt'
OVERWRITE INTO TABLE employee_partitioned
PARTITION (year=2018, month=12);

示例：加载HDFS数据

> LOAD DATA INPATH
'/tmp/hivedemo/data/employee.txt'
INTO TABLE employee;

--使用完整的URI
> LOAD DATA INPATH
> 'hdfs://ns001/tmp/employee.txt'
> OVERWRITE INTO TABLE employee;

将查询结果插入到表中

使用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扩展(多个 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扩展 (动态分区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 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将追加数据到表或分区，原有数据保留不变。

  • 从Hive 0.13.0开始，可以使用TBLPROPERTIES(“Immutable”=“true”)创建表使表不可变。默认是"Immutable"=“false”，此时如果表中已经存在数据，则不允许INSERT INTO插入数据到表中，但如果表中数据为空，则INSERT INTO操作仍然有效。INSERT OVERWRITE行为不受"immutable"表属性的影响。不可变表可以保护多次运行数据加载脚本导致的错误，防止意外更新。对不可变表的第一个插入成功，之后的插入则失败，这样，在表中的只有一组数据，而不是白白保留多个数据副本。

• 插入的目标可以是表或者分区，如果是分区表，则必须指定所有分区字段的值来确定要insert的分区。如果 hive.typecheck.on.insert 的值设置为true，这些字段值会进行验证，转换并正常化，以符合他们的字段类型（Hive 0.12.0以后）。

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

• 每个select语句的的输出被写入到选中的表（或分区）。目前，OVERWRITE关键字是强制性的，意味着所选择的表或分区的内容将被对应的select语句的输出覆盖。

• 输出格式和序列化类是由表的元数据来确定

• 从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的例子。

注意

• 多表插入可使数据扫描所需的次数最小化。通过对输入数据只扫描一次(并应用不同的查询操作符)，Hive可以将数据插入多个表中。
• 从HIVE 0.13.0开始，Select子句可以包含一个或多个公共表表达式（CTE)。

示例

示例：标准语法

> CREATE TABLE employee_orc(
name string,
work_place array<string>,
gender_age struct<gender:string,age:int>,
skills_score map<string,int>,
depart_title map<string,array<string>>
)
COMMENT 'ORC Format'
STORED AS ORC;
> insert into employee_orc select * from employee_internal;
> select * from employee_orc;
+--------------------+--------------------------+-------------------------------+----------------------------+----------------------------------------+
| employee_orc.name  | employee_orc.work_place  |    employee_orc.gender_age    | employee_orc.skills_score  |       employee_orc.depart_title        |
+--------------------+--------------------------+-------------------------------+----------------------------+----------------------------------------+
| Michael            | ["Montreal","Toronto"]   | {"gender":"Male","age":30}    | {"DB":80}                  | {"Product":["Developer^DLead"]}        |
| Will               | ["Montreal"]             | {"gender":"Male","age":35}    | {"Perl":85}                | {"Product":["Lead"],"Test":["Lead"]}   |
| Shelley            | ["New York"]             | {"gender":"Female","age":27}  | {"Python":80}              | {"Test":["Lead"],"COE":["Architect"]}  |
| Lucy               | ["Vancouver"]            | {"gender":"Female","age":57}  | {"Sales":89,"HR":94}       | {"Sales":["Lead"]}                     |
+--------------------+--------------------------+-------------------------------+----------------------------+----------------------------------------+

示例：插入部分字段

> CREATE TABLE employee_simple( 
name string,
work_place string
);
> INSERT INTO TABLE employee_simple (name)
SELECT name FROM employee_internal WHERE name = 'Will';
> select * from employee_simple;
+-----------------------+-----------------------------+
| employee_simple.name  | employee_simple.work_place  |
+-----------------------+-----------------------------+
| Will                  | NULL                        |
+-----------------------+-----------------------------+

示例：从CET语句插入数据

> CREATE TABLE ctas_employee as SELECT * FROM employee_internal;
> WITH a as (
SELECT * FROM ctas_employee
)
FROM a
INSERT OVERWRITE TABLE employee_orc
SELECT *;
> select * from employee_orc;
+--------------------+--------------------------+-------------------------------+----------------------------+----------------------------------------+
| employee_orc.name  | employee_orc.work_place  |    employee_orc.gender_age    | employee_orc.skills_score  |       employee_orc.depart_title        |
+--------------------+--------------------------+-------------------------------+----------------------------+----------------------------------------+
| Michael            | ["Montreal","Toronto"]   | {"gender":"Male","age":30}    | {"DB":80}                  | {"Product":["Developer^DLead"]}        |
| Will               | ["Montreal"]             | {"gender":"Male","age":35}    | {"Perl":85}                | {"Product":["Lead"],"Test":["Lead"]}   |
| Shelley            | ["New York"]             | {"gender":"Female","age":27}  | {"Python":80}              | {"Test":["Lead"],"COE":["Architect"]}  |
| Lucy               | ["Vancouver"]            | {"gender":"Female","age":57}  | {"Sales":89,"HR":94}       | {"Sales":["Lead"]}                     |
+--------------------+--------------------------+-------------------------------+----------------------------+----------------------------------------+

示例：多表插入

> FROM ctas_employee
INSERT OVERWRITE TABLE employee_orc
SELECT *
INSERT OVERWRITE TABLE employee_internal
SELECT *
INSERT OVERWRITE TABLE employee_partitioned
PARTITION (year=2018, month=9)
SELECT *
;

示例：动态分区插入

--创建表employee_hr并加载数据
> CREATE TABLE IF NOT EXISTS employee_hr (
name string,
employee_id int,
sin_number string,
start_date date
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|';

> LOAD DATA PATH "/tmp/employee_hr.txt" INTO TABLE employee_hr;

--设置动态分区使用nonstrict模式
> SET hive.exec.dynamic.partition=true;
> SET hive.exec.dynamic.partition.mode=nonstrict;

> show partitions employee_partitioned;
+--------------------+
|     partition      |
+--------------------+
| year=2018/month=9  |
+--------------------+

--示例中，复杂类型构造函数用于创建复杂数据类型的常量值
> INSERT INTO TABLE employee_partitioned
PARTITION(year, month)
SELECT name, 
array('Toronto') as work_place,
named_struct("gender","Male","age",30) as gender_age,
map("Python",90) as skills_score,
map("R&D",array('Developer')) as depart_title,
year(start_date) as year, 
month(start_date) as month
FROM employee_hr eh
WHERE eh.employee_id = 102;
> show partitions employee_partitioned;
+---------------------+
|      partition      |
+---------------------+
| year=2012/month=11  |
| year=2018/month=9   |
+---------------------+

将查询数据写入到文件系统中

INSERT语句除了可以将查询数据插入到表中，还可以将查询数据写入到文件系统中，此时INSERT语句的作用和LOAD正好相反，通常用于将SELECT的结果写入到本地或者HDFS文件系统中。

语法

--标准语法
INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format]
  SELECT ... FROM ...
 
--Hive 扩展 (多个insert):
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

说明

• 目录可以是一个完整的URI。如果未指定scheme或授权信息，Hive将使用来自Hadoop配置变量fs.default.name指定的HDFS URI的scheme或授权。
• 如果使用LOCAL关键词，Hive将数据写入到本地文件系统的目录上。
• 写入文件系统的数据被默认序列化为由^A做字段分割符，换行符做行分隔符的文本。如果表的任何字段都不是原始类型（而是MAP、ARRAY、STRUCT、UNION），则这些列被序列化为JSON格式。

注意

• INSERT OVERWRITE语句可以在同一个查询中将数据写入到HDFS目录、本地目录以及表（或分区）中。
• INSERT OVERWRITE语句是Hive提取大量数据到HDFS文件目录的最佳方式。Hive可以从一个map-reduce作业中将数据并行写入HDFS目录。
• 该语句只支持OVERWRITE，也就是目录是被覆盖的；换句话说，如果指定的路径存在，则该目录将被关闭并被替换为新的输出。
• 从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表仍然可以使用矢量化来查询。

示例

示例：将查询数据写入到本地目录

INSERT OVERWRITE LOCAL DIRECTORY '/tmp/output1'
SELECT * FROM employee_internal;

示例：将查询数据写入到本地目录同时指定字段分隔符

> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/output2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
SELECT * FROM employee_internal;
> !sh cat /tmp/output2/000000_0
Michael,MontrealToronto,Male30,DB80,ProductDeveloper^DLead
Will,Montreal,Male35,Perl85,ProductLeadTestLead
Shelley,New York,Female27,Python80,TestLeadCOEArchitect
Lucy,Vancouver,Female57,Sales89HR94,SalesLead

示例：多INSERT语句

> FROM employee_internal
INSERT OVERWRITE DIRECTORY '/user/output3'
SELECT *
INSERT OVERWRITE DIRECTORY '/user/output4'
SELECT name ;

> !sh hadoop fs -cat /user/output3/000000_0
MichaelMontrealTorontoMale30DB80ProductDeveloper^DLead
WillMontrealMale35Perl85ProductLeadTestLead
ShelleyNew YorkFemale27Python80TestLeadCOEArchitect
LucyVancouverFemale57Sales89HR94SalesLead
> !sh hadoop fs -cat /user/output4/000000_0
Michael
Will
Shelley
Lucy

使用技巧：前面已经提到，使用INSERT语句将查询数据写入到文件系统只支持OVERWRITE，但这并不意味着就没有办法进行追加写了。我们可以使用 hive/beeline -e 或 hive/beeline -f 命令并结合linux的管道功能将数据追加到文件中：

追加到本地文件: $hive -e 'select * from employee' >> test
覆盖本地文件: $hive -e 'select * from employee' > test
追加到HDFS文件: $hive -e 'select * from employee'|hdfs dfs -appendToFile - /tmp/test1
覆盖HDFS文件: $hive -e 'select * from employee'|hdfs dfs -put -f - /tmp/test2

示例：将输出追加到HDFS文件中

#执行下面命令2次
$ beeline -u  "jdbc:hive2://localhost:10000/"  -e "select * from test2.employee_internal" | hadoop fs -appendToFile - /tmp/test1
$ beeline -u  "jdbc:hive2://localhost:10000/"  -e "select * from test2.employee_internal" | hadoop fs -appendToFile - /tmp/test1
$ hadoop fs -cat /tmp/test1
+-------------------------+-------------------------------+-------------------------------+---------------------------------+----------------------------------------+
| employee_internal.name  | employee_internal.work_place  | employee_internal.gender_age  | employee_internal.skills_score  |     employee_internal.depart_title     |
+-------------------------+-------------------------------+-------------------------------+---------------------------------+----------------------------------------+
| Michael                 | ["Montreal","Toronto"]        | {"gender":"Male","age":30}    | {"DB":80}                       | {"Product":["Developer^DLead"]}        |
| Will                    | ["Montreal"]                  | {"gender":"Male","age":35}    | {"Perl":85}                     | {"Product":["Lead"],"Test":["Lead"]}   |
| Shelley                 | ["New York"]                  | {"gender":"Female","age":27}  | {"Python":80}                   | {"Test":["Lead"],"COE":["Architect"]}  |
| Lucy                    | ["Vancouver"]                 | {"gender":"Female","age":57}  | {"Sales":89,"HR":94}            | {"Sales":["Lead"]}                     |
+-------------------------+-------------------------------+-------------------------------+---------------------------------+----------------------------------------+
+-------------------------+-------------------------------+-------------------------------+---------------------------------+----------------------------------------+
| employee_internal.name  | employee_internal.work_place  | employee_internal.gender_age  | employee_internal.skills_score  |     employee_internal.depart_title     |
+-------------------------+-------------------------------+-------------------------------+---------------------------------+----------------------------------------+
| Michael                 | ["Montreal","Toronto"]        | {"gender":"Male","age":30}    | {"DB":80}                       | {"Product":["Developer^DLead"]}        |
| Will                    | ["Montreal"]                  | {"gender":"Male","age":35}    | {"Perl":85}                     | {"Product":["Lead"],"Test":["Lead"]}   |
| Shelley                 | ["New York"]                  | {"gender":"Female","age":27}  | {"Python":80}                   | {"Test":["Lead"],"COE":["Architect"]}  |
| Lucy                    | ["Vancouver"]                 | {"gender":"Female","age":57}  | {"Sales":89,"HR":94}            | {"Sales":["Lead"]}                     |
+-------------------------+-------------------------------+-------------------------------+---------------------------------+----------------------------------------+

使用 INSERT…VALUES 语句插入数据到表中

语法

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

说明

• 在VALUES子句中列出的每一行插入到表tablename表中。
• VALUES子句必须为表中的每一字段提供值。还不支持将值只插入到某些字段的。若要模拟标准SQL，可以向字段提供的null值。
• 可以与INSERT … SELECT同样的方式支持动态分区。
• 如果要插入的表支持ACID并且Hive正在使用一个支持ACID的事务管理器，该操作成功后将自动提交完成。
• 不支持复杂数据类型 (array, map, struct, union)的文字。这意味着用户不能使用 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);
 
> SELECT * FROM students;
+------------------+---------------+---------------+
|  students.name   | students.age  | students.gpa  |
+------------------+---------------+---------------+
| barney rubble    | 32            | 2.32          |
| fred flintstone  | 35            | 1.28          |
+------------------+---------------+---------------+

示例：插入部分字段值 (虽然社区文档中说不支持将数据插入到某些字段，但是实际测试发现可以这样操作，缺失的字段会使用NULL补充)

> INSERT INTO TABLE students(name,age)
  VALUES ('haha', 35), ('hehe', 18);
> SELECT * FROM students;
+------------------+---------------+---------------+
|  students.name   | students.age  | students.gpa  |
+------------------+---------------+---------------+
| barney rubble    | 32            | 2.32          |
| fred flintstone  | 35            | 1.28          |
| hehe             | 18            | NULL          |
| haha             | 35            | NULL          |
+------------------+---------------+---------------+

示例：插入数据到分区表

> 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);
  
> SELECT * FROM pageviews;
+-------------------+-----------------+----------------------+----------------------+
| pageviews.userid  | pageviews.link  | pageviews.came_from  | pageviews.datestamp  |
+-------------------+-----------------+----------------------+----------------------+
| jdoe              | mail.com        | NULL                 | 2014-09-23           |
| jsmith            | mail.com        | sports.com           | 2014-09-23           |
+-------------------+-----------------+----------------------+----------------------+

示例：动态分区

> INSERT INTO TABLE pageviews PARTITION (datestamp)
  VALUES ('tjohnson', 'sports.com', 'finance.com', '2014-09-23'), ('tlee', 'finance.com', null, '2014-09-21');
  
> SHOW PARTITIONS pageviews;
+-----------------------+
|       partition       |
+-----------------------+
| datestamp=2014-09-21  |
| datestamp=2014-09-23  |
+-----------------------+

> INSERT INTO TABLE pageviews
  VALUES ('user1', 'hello.com', 'finance.com', '2014-09-23'), ('user2', 'world.com', null, '2014-09-25');
> SHOW PARTITIONS pageviews;
+-----------------------+
|       partition       |
+-----------------------+
| datestamp=2014-09-21  |
| datestamp=2014-09-23  |
| datestamp=2014-09-25  |
+-----------------------+ 

参考

• https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML
• https://www.cnblogs.com/fanzhenyong/p/9749140.html
• 书籍 Apache Hive Essentials Second Edition (by Dayong Du) Chapter 5