5、hive的load、insert、事务表使用详解及示例_hive load

Apache Hive 系列文章

1、apache-hive-3.1.2简介及部署（三种部署方式-内嵌模式、本地模式和远程模式）及验证详解
2、hive相关概念详解–架构、读写文件机制、数据存储
3、hive的使用示例详解-建表、数据类型详解、内部外部表、分区表、分桶表
4、hive的使用示例详解-事务表、视图、物化视图、DDL(数据库、表以及分区)管理详细操作
5、hive的load、insert、事务表使用详解及示例
6、hive的select（GROUP BY、ORDER BY、CLUSTER BY、SORT BY、LIMIT、union、CTE）、join使用详解及示例
7、hive shell客户端与属性配置、内置运算符、函数（内置运算符与自定义UDF运算符）
8、hive的关系运算、逻辑预算、数学运算、数值运算、日期函数、条件函数和字符串函数的语法与使用示例详解
9、hive的explode、Lateral View侧视图、聚合函数、窗口函数、抽样函数使用详解
10、hive综合示例：数据多分隔符（正则RegexSerDe）、url解析、行列转换常用函数（case when、union、concat和explode）详细使用示例
11、hive综合应用示例：json解析、窗口函数应用（连续登录、级联累加、topN）、拉链表应用
12、Hive优化-文件存储格式和压缩格式优化与job执行优化（执行计划、MR属性、join、优化器、谓词下推和数据倾斜优化）详细介绍及示例
13、java api访问hive操作示例

---

---

本文介绍了hive的load、insert、事务表的内容和详细使用示例。
本文依赖hive环境好用。
本文分为四个部分，即load、insert、事务表以及update和delete。

一、DML-Load 加载数据

在Hive中建表成功之后，就会在HDFS上创建一个与之对应的文件夹，且文件夹名字就是表名；
文件夹父路径是由参数hive.metastore.warehouse.dir控制，默认值是/user/hive/warehouse；
也可以在建表的时候使用location语句指定任意路径。
Hive官方推荐使用Load命令将数据加载到表中。

1、语法

所谓加载是指将数据文件移动到与Hive表对应的位置，移动时是纯复制、移动操作。
纯复制、移动指在数据load加载到表中时，Hive不会对表中的数据内容进行任何转换，任何操作。

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)

--1、filepath
    --filepath表示待移动数据的路径。可以指向文件（在这种情况下，Hive将文件移动到表中），也可以指向目录（在这种情况下，Hive将把该目录中的所有文件移动到表中）。
    --filepath文件路径支持下面三种形式，要结合LOCAL关键字一起考虑：
    --1.相对路径，例如：project/data1
    --2.绝对路径，例如：/user/hive/project/data1
   -- 3.具有schema的完整URI，例如：hdfs://namenode:9000/user/hive/project/data1

--2、LOCAL
    --1）、指定LOCAL，将在本地文件系统中查找文件路径。
        --本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统，不是Hive客户端所在的本地文件系统。
        --若指定相对路径，将相对于用户的当前工作目录进行解释；
        --用户也可以为本地文件指定完整的URI-例如：file:///user/hive/project/data1。
    --2）、没有指定LOCAL关键字。
        --如果filepath指向的是一个完整的URI，会直接使用这个URI；
        --如果没有指定schema，Hive会使用在hadoop配置文件中参数fs.default.name指定的（不出意外，都是HDFS）

--3、OVERWRITE
    --如果使用了OVERWRITE关键字，则目标表（或者分区）中的已经存在的数据会被删除，然后再将filepath指向的文件/目录中的内容添加到表/分区中
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

2、示例

--step1:建表
--建表student_local 用于演示从本地加载数据
create table student_local(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
--建表student_HDFS  用于演示从HDFS加载数据
create external table student_HDFS(num int,name string,sex string,age int,dept string) row format delimited fields terminated by ',';
--建表student_HDFS_p 用于演示从HDFS加载数据到分区表
create table student_HDFS_p(num int,name string,sex string,age int,dept string) partitioned by(country string) row format delimited fields terminated by ',';

--建议使用beeline客户端 可以显示出加载过程日志信息

--step2:加载数据
-- 从本地加载数据  数据位于HS2（server4）本地文件系统  本质是hadoop fs -put上传操作
LOAD DATA LOCAL INPATH '/usr/local/bigdata/students.txt' INTO TABLE student_local;

--从HDFS加载数据  数据位于HDFS文件系统根目录下  本质是hadoop fs -mv 移动操作
--先把数据上传到HDFS上  hadoop fs -put /hivetest/student/students.txt 
LOAD DATA INPATH '/hivetest/student/students.txt' INTO TABLE student_HDFS;

----从HDFS加载数据到分区表中并制定分区  数据位于HDFS文件系统根目录下
--先把数据上传到HDFS上 hadoop fs -put /hivetest/partition/students.txt
LOAD DATA INPATH '/hivetest/partition/students.txt' INTO TABLE student_HDFS_p partition(country ="China");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3、hive 3.0 load命令新特性

Hive3.0+，load加载数据时除了移动、复制操作之外，在某些场合下还会将加载重写为INSERT AS SELECT。
Hive3.0+，还支持使用inputformat、SerDe指定输入格式，例如Text，ORC等。比如，如果表具有分区，则load命令没有指定分区，则将load转换为INSERT AS SELECT，并假定最后一组列为分区列，如果文件不符合预期，则报错。

-------hive 3.0 load命令新特性------------------
CREATE TABLE if not exists tab1 (col1 int, col2 int)
PARTITIONED BY (col3 int)
row format delimited fields terminated by ',';

--正常情况下  数据格式如下
11,22
33,44
LOAD DATA LOCAL INPATH '/root/hivedata/xxx.txt' INTO TABLE tab1 partition(col3="1");

--在hive3.0之后 新特性可以帮助我们把load改写为insert as select
--tab1.txt内容如下
11,22,1
33,44,2

LOAD DATA INPATH '/hivetest/tab1/tab1.txt' INTO TABLE tab1;
--通过MR的运行结果看是成功的，但hive报错了，数据也是插入成功了
LOAD DATA LOCAL  INPATH '/usr/local/bigdata/tab1.txt' INTO TABLE tab1;

--通过MR执行的
0: jdbc:hive2://server4:10000> LOAD DATA INPATH '/hivetest/tab1/tab1.txt' INTO TABLE tab1;
WARN  : Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
No rows affected (52.469 seconds)
0: jdbc:hive2://server4:10000> select * from tab1;
+------------+------------+------------+
| tab1.col1  | tab1.col2  | tab1.col3  |
+------------+------------+------------+
| 11         | 22         | 1          |
| 33         | 44         | 2          |
+------------+------------+------------+
2 rows selected (0.125 seconds)

0: jdbc:hive2://server4:10000> LOAD DATA LOCAL  INPATH '/usr/local/bigdata/tab1.txt' INTO TABLE tab1;
WARN  : Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
ERROR : FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.StatsTask (state=08S01,code=1)
0: jdbc:hive2://server4:10000> select * from tab1;
+------------+------------+------------+
| tab1.col1  | tab1.col2  | tab1.col3  |
+------------+------------+------------+
| 11         | 22         | 1          |
| 11         | 22         | 1          |
| 33         | 44         | 2          |
| 33         | 44         | 2          |
+------------+------------+------------+
4 rows selected (0.137 seconds)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

二、Hive insert使用方式

把Hive当成RDBMS，用insert+values的方式插入数据，执行过程非常非常慢，原因在于底层是使用MapReduce把数据写入Hive表中
Hive官方推荐加载数据的方式：清洗数据成为结构化文件，再使用Load语法加载数据到表中。这样的效率更高。

1、insert+select

insert+select表示将后面查询返回的结果作为内容插入到指定表中，注意OVERWRITE将覆盖已有数据。

• 需要保证查询结果列的数目和需要插入数据表格的列数目一致。
• 如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。

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;
1
2
3
4

3、示例1：insert+select

---------hive中insert+values---执行慢-------------
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );

create table t_test_insert(id int,name string,age int);
insert into table t_test_insert values(1,"allen",18);
select * from t_test_insert;

----------hive中insert+select----如果一定要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;

--step1:创建一张源表student
drop table if exists student;
create table student(num int,name string,sex string,age int,dept string)
row format delimited
fields terminated by ',';
--加载数据
load data local inpath '/usr/local/bigdata/students.txt' into table student;

--step2：创建一张目标表  只有两个字段
create table student_from_insert(sno int,sname string);
--使用insert+select插入数据到新表中
insert into table student_from_insert
select num,name from student;

select * from student_from_insert;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

4、示例2：multiple inserts多重插入

翻译为多次插入，多重插入，其核心功能是：一次扫描，多次插入。
语法目的就是减少扫描的次数，在一次扫描中。完成多次insert操作。

------------multiple inserts----------------------
--当前库下已有一张表student
select * from student;
字段
student.num  | student.name  | student.sex  | student.age  | student.dept

--创建两张新表
create table student_info(num int,name string, sex string, age int);
create table student_dept(num int,dept string);

--一般的做法
insert into student_info select num,name,sex,age from student;
insert into student_dept select num,dept  from student;

--多重插入  一次扫描 多次插入
from student
insert overwrite table student_info
select num,name,sex,age
insert overwrite table student_dept
select num,dept;

--查看结果
select * from student_info;
select * from student_dept;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

5、示例3：dynamic partition insert动态分区插入

• 对于分区表的数据导入加载，最基础的是通过load命令加载数据。在load过程中，分区值是手动指定写死的，叫做静态分区。
• 动态分区插入是分区的值是由后续的select查询语句的结果来动态确定的。根据查询结果自动分区。

1）、配置参数

2）、语法与示例

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.country
--country分区将由SELECT子句（即pvs.country）的最后一列动态创建。
--而dt分区是手动指定写死的。
--如果是nonstrict模式下，dt分区也可以动态创建。
1
2
3
4
5
6

---------------动态分区插入--------------------
--背景：静态分区
drop table if exists student_HDFS_p;
create table student_HDFS_p(Sno int,Sname string,Sex string,Sage int,Sdept string) 
partitioned by(country string) 
row format delimited fields terminated by ',';
--注意 分区字段country的值是在导入数据的时候手动指定的 China
LOAD DATA INPATH '/hivetest/student/students.txt' INTO TABLE student_HDFS_p partition(country ="China");

-----------案例：动态分区插入-----------
--1、首先设置动态分区模式为非严格模式 默认已经开启了动态分区功能
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;

--2、当前库下已有一张表student
select * from student;

--3、创建分区表 以sdept作为分区字段
create table student_partition(Sno int,Sname string,Sex string,Sage int) 
partitioned by(Sdept string);

--4、执行动态分区插入操作
insert into table student_partition partition(Sdept)
select num,name,sex,age,dept from student;
--其中，num,name,sex,age作为表的字段内容插入表中
--dept作为分区字段值

select * from student_partition;
show partitions student_partition;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

6、insert Directory导出数据

lHive支持将select查询的结果导出成文件存放在文件系统中。
导出操作是一个OVERWRITE覆盖操作，导出都是通过MR运行的。

1)、语法

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

--1、目录可以是完整的URI。如果未指定scheme，则Hive将使用hadoop配置变量fs.default.name来决定导出位置；
--2、如果使用LOCAL关键字，则Hive会将数据写入本地文件系统上的目录；
--3、写入文件系统的数据被序列化为文本，列之间用\001隔开，行之间用换行符隔开。如果列都不是原始数据类型，那么这些列将序列化为JSON格式。也可以在导出的时候指定分隔符换行符和文件格式。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2）、示例

--导出操作演示
--当前库下已有一张表student
select * from student;

--1、导出查询结果到HDFS指定目录下
insert overwrite directory '/hivetest/student/e1' 
select num,name,age from student limit 2;

--2、导出时指定分隔符和文件存储格式
insert overwrite directory '/hivetest/student/e2' 
row format delimited fields terminated by ','
stored as orc
select * from student;

--4、多重导出数据到HDFS指定目录下
from student
insert overwrite directory '/hivetest/student/student_info'
select num,name,sex,age
insert overwrite directory '/hivetest/student/student_dept'
select num,dept;

--4、导出数据到本地文件系统指定目录下
insert overwrite local directory '/usr/local/bigdata/e1' select * from student;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

三、Hive Transaction事务表

Hive设计之初时，是不支持事务的，原因：

• Hive的核心目标是将已经存在的结构化数据文件映射成为表，然后提供基于表的SQL分析处理，是一款面向历史、面向分析的工具；
• Hive作为数据仓库，是分析数据规律的，而不是创造数据规律的；
• Hive中表的数据存储于HDFS上，而HDFS是不支持随机修改文件数据的，其常见的模型是一次写入，多次读取。

1、实现原理

Hive的文件是存储在HDFS上的，而HDFS上又不支持对文件的任意修改，只能是采取另外的手段来完成。

• 用HDFS文件作为原始数据（基础数据），用delta保存事务操作的记录增量数据；
• 正在执行中的事务，是以一个staging开头的文件夹维护的，执行结束就是delta文件夹。每次执行一次事务操作都会有这样的一个delta增量文件夹;
• 当访问Hive数据时，根据HDFS原始文件和delta增量文件做合并，查询最新的数据。
  INSERT语句会直接创建delta目录；
  DELETE目录的前缀是delete_delta；
  UPDATE语句采用了split-update特性，即先删除、后插入；

2、通过实例验证实现过程

以下是操作过程，HDFS系统文件变化过程

1）、执行insert into语句中

2）、执行insert into语句结束

delta_minWID_maxWID_stmtID，即delta前缀、写事务的ID范围、以及语句ID；删除时前缀是delete_delta，里面包含了要删除的文件；
Hive会为写事务（INSERT、DELETE等）创建一个写事务ID（Write ID），该ID在表范围内唯一；
语句ID（Statement ID）则是当一个事务中有多条写入语句时使用的，用作唯一标识。


每个事务的delta文件夹下，都有两个文件：

• _orc_acid_version的内容是2,即当前ACID版本号是2。和版本1的主要区别是UPDATE语句采用了split-update特性，即先删除、后插入。这个文件不是ORC文件，可以下载下来直接查看。
• bucket_00000文件则是写入的数据内容。如果事务表没有分区和分桶，就只有一个这样的文件。文件都以ORC格式存储，底层二进制，需要使用ORC TOOLS查看（使用orc-tools-1.6.7-uber.jar，将bucket_00000下载到和该工具相同的目录或指定目录，
  执行命令

java -jar /usr/local/bigdata/apache-hive-3.1.2-bin/orctools/orc-tools-1.6.7-uber.jar data bucket_00000）
1

operation：0 表示插入，1 表示更新，2 表示删除。由于使用了split-update，UPDATE是不会出现的，所以delta文件中的operation是0 ， delete_delta 文件中的operation是2。
originalTransaction、currentTransaction：该条记录的原始写事务ID，当前的写事务ID。
rowId：一个自增的唯一ID，在写事务和分桶的组合中唯一。
row：具体数据。对于DELETE语句，则为null，对于INSERT就是插入的数据，对于UPDATE就是更新后的数据。

3）、第二次insert 执行中

4）、第二次insert 执行结束

5）、delete操作执行中

6）、delete操作执行结束

7）、执行更新操作中

8）、执行更新操作结束

/user/hive/warehouse/testhive.db/emp/delta_0000004_0000004_0000/bucket_00000文件内容

/user/hive/warehouse/testhive.db/emp/delete_delta_0000004_0000004_0000/bucket_00000文件内容

3、合并器(Compactor)

随着表的修改操作，创建了越来越多的delta增量文件，就需要合并以保持足够的性能。
合并器Compactor是一套在Hive Metastore内运行，支持ACID系统的后台进程。所有合并都是在后台完成的，不会阻止数据的并发读、写。合并后，系统将等待所有旧文件的读操作完成后，删除旧文件。

合并操作分为两种，minor compaction（小合并）、major compaction（大合并）：

• 小合并会将一组delta增量文件重写为单个增量文件，默认触发条件为10个delta文件；
• 大合并将一个或多个增量文件和基础文件重写为新的基础文件，默认触发条件为delta文件相应于基础文件占比，10%
  

4、Hive事务表使用设置与局限性

虽然Hive支持了具有ACID语义的事务，但只能在限制条件下使用，具体如下：

• 不支持BEGIN，COMMIT和ROLLBACK，所有语言操作都是自动提交的；
• 表文件存储格式仅支持ORC（STORED AS ORC）；
• 需要配置参数开启事务使用；
• 外部表无法创建为事务表，因为Hive只能控制元数据，无法管理数据；
• 表属性参数transactional必须设置为true；
• 必须将Hive事务管理器设置为org.apache.hadoop.hive.ql.lockmgr.DbTxnManager才能使用ACID表；
• 事务表不支持LOAD DATA …语句。

5、设置参数

事务表在操作过程中，需要进行的参数配置如下：
Client端：
可以使用set设置当前session生效 也可以配置在hive-site.xml中）
set hive.support.concurrency = true; --Hive是否支持并发
set hive.enforce.bucketing = true; --从Hive2.0开始不再需要 是否开启分桶功能
set hive.exec.dynamic.partition.mode = nonstrict; --动态分区模式 非严格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; –
服务端：
set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动压缩合并
set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个合并程序工作线程。

6、示例

--Hive中事务表的创建使用
--1、开启事务配置（可以使用set设置当前session生效 也可以配置在hive-site.xml中）
set hive.support.concurrency = true; --Hive是否支持并发
set hive.enforce.bucketing = true; --从Hive2.0开始不再需要  是否开启分桶功能
set hive.exec.dynamic.partition.mode = nonstrict; --动态分区模式  非严格
set hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; --
set hive.compactor.initiator.on = true; --是否在Metastore实例上运行启动压缩合并
set hive.compactor.worker.threads = 1; --在此metastore实例上运行多少个压缩程序工作线程。

--事务表的创建
CREATE TABLE emp (id int, name string, salary int)
STORED AS ORC TBLPROPERTIES ('transactional' = 'true');

--事务表 insert  -->delta文件
INSERT INTO emp VALUES
(1, 'Jerry', 5000),
(2, 'Tom',   8000),
(3, 'Kate',  6000);

select * from emp;

--再次insert  --->delta文件
INSERT INTO emp VALUES(4, 'Allen', 8000);

--执行delete --> delete-delta文件
delete from emp where id =2;

--显示有关当前运行的压缩和最近的压缩历史
Show Compactions;

--2、创建Hive事务表
create table trans_student(
                              id int,
                              name String,
                              age int
)stored as orc TBLPROPERTIES('transactional'='true');

describe formatted trans_student;

--3、针对事务表进行insert update delete操作
insert into trans_student (id, name, age) values (1,"allen",18);
select * from trans_student;
describe formatted trans_student;

update trans_student
set age = 20
where id = 1;

delete from trans_student where id =1;
select * from trans_student;

show tables;
select * from student_local;

update student_local
set  age= 35
where num =95001;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

四、Hive SQL-DML-Update、Delete

Hive是基于Hadoop的数据仓库，是面向分析支持分析工具。将已有的结构化数据文件映射成为表，然后提供SQL分析数据的能力。
因此在Hive中常见的操作就是分析查询select操作。
Hive早期是不支持update和delete语法的，因为Hive所处理的数据都是已经存在的的数据、历史数据。后续Hive支持了相关的update和delete操作，不过有很多约束。详见Hive事务的支持段落。

以上，介绍了hive的load、insert、事务表的内容和详细使用示例。