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指向的文件/目录中的内容添加到表/分区中
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");
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)
二、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;
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;
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;
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分区也可以动态创建。
---------------动态分区插入--------------------
--背景：静态分区
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;
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格式。也可以在导出的时候指定分隔符换行符和文件格式。
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;
三、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）
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;
四、Hive SQL-DML-Update、Delete
Hive是基于Hadoop的数据仓库，是面向分析支持分析工具。将已有的结构化数据文件映射成为表，然后提供SQL分析数据的能力。
因此在Hive中常见的操作就是分析查询select操作。
Hive早期是不支持update和delete语法的，因为Hive所处理的数据都是已经存在的的数据、历史数据。后续Hive支持了相关的update和delete操作，不过有很多约束。详见Hive事务的支持段落。
以上，介绍了hive的load、insert、事务表的内容和详细使用示例。
1861
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
