第15章:Hive SQL Join连接操作
15-1.Hive Join语法规则
join分类
- 在Hive中，当下版本3.1.2总共支持6种join语法。分别是：
- inner join（内连接）
- left join（左连接）
- right join（右连接）
- full outer join（全外连接）
- left semi join（左半开连接）
- cross join（交叉连接，也叫做笛卡尔乘积）
join语法规则
- table_reference：是join查询中使用的表名，也可以是子查询别名（查询结果当成表参与join）。
- table_factor：与table_reference相同,是联接查询中使用的表名,也可以是子查询别名。
- join_condition：join查询关联的条件，如果在两个以上的表上需要连接，则使用AND关键字。
语法规则：
join_table:
table_reference [INNER] JOIN table_factor [join_condition]
| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition
| table_reference LEFT SEMI JOIN table_reference join_condition
| table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10)
join_condition:
ON expression
join语法丰富化
- Hive中join语法从面世开始其实并不丰富，不像在RDBMS中那么灵活
- 从Hive 0.13.0开始，支持隐式联接表示法。允许FROM子句连接以逗号分隔的表列表，而省略JOIN关键字
- 从Hive 2.2.0开始，支持ON子句中的复杂表达式，支持不相等连接（请参阅HIVE-15211和HIVE-15251）。在此之前，Hive不支持不是相等条件的联接条件
--隐式联接表示法
SELECT *
FROM table1 t1, table2 t2, table3 t3
WHERE t1.id = t2.id AND t2.id = t3.id AND t1.zipcode = '02535';
--支持非等值连接
SELECT a.* FROM a JOIN b ON (a.id = b.id)
SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)
SELECT a.* FROM a LEFT OUTER JOIN b ON (a.id <> b.id)
join查询实验数据环境准备
建表语句
--table1: 员工表
CREATE TABLE employee(
id int,
name string,
deg string,
salary int,
dept string
) row format delimited
fields terminated by ',';
--table2:员工家庭住址信息表
CREATE TABLE employee_address (
id int,
hno string,
street string,
city string
) row format delimited
fields terminated by ',';
--table3:员工联系方式信息表
CREATE TABLE employee_connection (
id int,
phno string,
email string
) row format delimited
fields terminated by ',';
准备数据文件：
wangting@ops01:/home/wangting/20221013/join >pwd
/home/wangting/20221013/join
wangting@ops01:/home/wangting/20221013/join >ls
employee_address.txt employee_connection.txt employee.txt
wangting@ops01:/home/wangting/20221013/join >cat employee.txt
1201,gopal,manager,50000,TP
1202,manisha,cto,50000,TP
1203,khalil,dev,30000,AC
1204,prasanth,dev,30000,AC
1206,kranthi,admin,20000,TP
wangting@ops01:/home/wangting/20221013/join >cat employee_address.txt
1201,288A,vgiri,jublee
1202,108I,aoc,ny
1204,144Z,pgutta,hyd
1206,78B,old city,la
1207,720X,hitec,ny
wangting@ops01:/home/wangting/20221013/join >cat employee_connection.txt
1201,2356742,gopal@tp.com
1203,1661663,manisha@tp.com
1204,8887776,khalil@ac.com
1205,9988774,prasanth@ac.com
1206,1231231,kranthi@tp.com
wangting@ops01:/home/wangting/20221013/join >
上传数据映射文件：
0: jdbc:hive2://ops01:10000> load data local inpath '/home/wangting/20221013/join/employee.txt' into table employee;
No rows affected (0.193 seconds)
0: jdbc:hive2://ops01:10000> load data local inpath '/home/wangting/20221013/join/employee_address.txt' into table employee_address;
No rows affected (0.208 seconds)
0: jdbc:hive2://ops01:10000> load data local inpath '/home/wangting/20221013/join/employee_connection.txt' into table employee_connection;
No rows affected (0.201 seconds)
验证：
0: jdbc:hive2://ops01:10000> select * from employee limit 2;
+--------------+----------------+---------------+------------------+----------------+
| employee.id | employee.name | employee.deg | employee.salary | employee.dept |
+--------------+----------------+---------------+------------------+----------------+
| 1201 | gopal | manager | 50000 | TP |
| 1202 | manisha | cto | 50000 | TP |
+--------------+----------------+---------------+------------------+----------------+
2 rows selected (0.188 seconds)
0: jdbc:hive2://ops01:10000> select * from employee_address limit 2;
+----------------------+-----------------------+--------------------------+------------------------+
| employee_address.id | employee_address.hno | employee_address.street | employee_address.city |
+----------------------+-----------------------+--------------------------+------------------------+
| 1201 | 288A | vgiri | jublee |
| 1202 | 108I | aoc | ny |
+----------------------+-----------------------+--------------------------+------------------------+
2 rows selected (0.166 seconds)
0: jdbc:hive2://ops01:10000> select * from employee_connection limit 2;
+-------------------------+---------------------------+----------------------------+
| employee_connection.id | employee_connection.phno | employee_connection.email |
+-------------------------+---------------------------+----------------------------+
| 1201 | 2356742 | gopal@tp.com |
| 1203 | 1661663 | manisha@tp.com |
+-------------------------+---------------------------+----------------------------+
2 rows selected (0.169 seconds)
为后续join实验做准备
15-2.Hive 6种Join方式详解
inner join 内连接
- 内连接是最常见的一种连接，它也被称为普通连接，其中inner可以省略：inner join == join
- 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来
--1、inner join
0: jdbc:hive2://ops01:10000> select e.id,e.name,e_a.city,e_a.street
. . . . . . . . . . . . . .> from employee e inner join employee_address e_a
. . . . . . . . . . . . . .> on e.id =e_a.id;
+-------+-----------+-----------+-------------+
| e.id | e.name | e_a.city | e_a.street |
+-------+-----------+-----------+-------------+
| 1201 | gopal | jublee | vgiri |
| 1202 | manisha | ny | aoc |
| 1204 | prasanth | hyd | pgutta |
| 1206 | kranthi | la | old city |
+-------+-----------+-----------+-------------+
4 rows selected (23.936 seconds)
-- 等价于 inner join=join
0: jdbc:hive2://ops01:10000> select e.id,e.name,e_a.city,e_a.street
. . . . . . . . . . . . . .> from employee e join employee_address e_a
. . . . . . . . . . . . . .> on e.id =e_a.id;
+-------+-----------+-----------+-------------+
| e.id | e.name | e_a.city | e_a.street |
+-------+-----------+-----------+-------------+
| 1201 | gopal | jublee | vgiri |
| 1202 | manisha | ny | aoc |
| 1204 | prasanth | hyd | pgutta |
| 1206 | kranthi | la | old city |
+-------+-----------+-----------+-------------+
4 rows selected (24.145 seconds)
--等价于 隐式连接表示法
0: jdbc:hive2://ops01:10000> select e.id,e.name,e_a.city,e_a.street
. . . . . . . . . . . . . .> from employee e , employee_address e_a
. . . . . . . . . . . . . .> where e.id =e_a.id;
+-------+-----------+-----------+-------------+
| e.id | e.name | e_a.city | e_a.street |
+-------+-----------+-----------+-------------+
| 1201 | gopal | jublee | vgiri |
| 1202 | manisha | ny | aoc |
| 1204 | prasanth | hyd | pgutta |
| 1206 | kranthi | la | old city |
+-------+-----------+-----------+-------------+
4 rows selected (24.99 seconds)
left join 左连接
- left join中文叫做是左外连接(Left Outer Join)或者左连接，其中outer可以省略，left outer join是早期的写法。
- left join的核心就在于left左。左指的是join关键字左边的表，简称左表。
- 通俗解释：join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回
--2、left join
0: jdbc:hive2://ops01:10000> select e.id,e.name,e_conn.phno,e_conn.email
. . . . . . . . . . . . . .> from employee e left join employee_connection e_conn
. . . . . . . . . . . . . .> on e.id =e_conn.id;
+-------+-----------+--------------+-----------------+
| e.id | e.name | e_conn.phno | e_conn.email |
+-------+-----------+--------------+-----------------+
| 1201 | gopal | 2356742 | gopal@tp.com |
| 1202 | manisha | NULL | NULL |
| 1203 | khalil | 1661663 | manisha@tp.com |
| 1204 | prasanth | 8887776 | khalil@ac.com |
| 1206 | kranthi | 1231231 | kranthi@tp.com |
+-------+-----------+--------------+-----------------+
5 rows selected (24.534 seconds)
--等价于 left outer join
0: jdbc:hive2://ops01:10000> select e.id,e.name,e_conn.phno,e_conn.email
. . . . . . . . . . . . . .> from employee e left outer join employee_connection e_conn
. . . . . . . . . . . . . .> on e.id =e_conn.id;
+-------+-----------+--------------+-----------------+
| e.id | e.name | e_conn.phno | e_conn.email |
+-------+-----------+--------------+-----------------+
| 1201 | gopal | 2356742 | gopal@tp.com |
| 1202 | manisha | NULL | NULL |
| 1203 | khalil | 1661663 | manisha@tp.com |
| 1204 | prasanth | 8887776 | khalil@ac.com |
| 1206 | kranthi | 1231231 | kranthi@tp.com |
+-------+-----------+--------------+-----------------+
5 rows selected (24.553 seconds)
right join 右连接
- right join中文叫做是右外连接(Right Outer Jion)或者右连接，其中outer可以省略。
- right join的核心就在于Right右。右指的是join关键字右边的表，简称右表。
- 通俗解释：join时以右表的全部数据为准，左边与之关联；右表数据全部返回，左表关联上的显示返回，关联不上的显示null返回
- right join和left join之间很相似，重点在于以哪边为准，也就是一个方向的问题。
--3、right join
0: jdbc:hive2://ops01:10000> select e.id,e.name,e_conn.phno,e_conn.email
. . . . . . . . . . . . . .> from employee e right join employee_connection e_conn
. . . . . . . . . . . . . .> on e.id =e_conn.id;
+-------+-----------+--------------+------------------+
| e.id | e.name | e_conn.phno | e_conn.email |
+-------+-----------+--------------+------------------+
| 1201 | gopal | 2356742 | gopal@tp.com |
| 1203 | khalil | 1661663 | manisha@tp.com |
| 1204 | prasanth | 8887776 | khalil@ac.com |
| NULL | NULL | 9988774 | prasanth@ac.com |
| 1206 | kranthi | 1231231 | kranthi@tp.com |
+-------+-----------+--------------+------------------+
5 rows selected (22.544 seconds)
--等价于 right outer join
0: jdbc:hive2://ops01:10000> select e.id,e.name,e_conn.phno,e_conn.email
. . . . . . . . . . . . . .> from employee e right outer join employee_connection e_conn
. . . . . . . . . . . . . .> on e.id =e_conn.id;
+-------+-----------+--------------+------------------+
| e.id | e.name | e_conn.phno | e_conn.email |
+-------+-----------+--------------+------------------+
| 1201 | gopal | 2356742 | gopal@tp.com |
| 1203 | khalil | 1661663 | manisha@tp.com |
| 1204 | prasanth | 8887776 | khalil@ac.com |
| NULL | NULL | 9988774 | prasanth@ac.com |
| 1206 | kranthi | 1231231 | kranthi@tp.com |
+-------+-----------+--------------+------------------+
5 rows selected (24.427 seconds)
full outer join 全外连接
- full outer join 等价 full join ,中文叫做全外连接或者外连接。
- 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行；
- 在功能上：等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的操作将上述两个结果集合并为一个结果集。
--4、full outer join
0: jdbc:hive2://ops01:10000> select e.id,e.name,e_a.city,e_a.street
. . . . . . . . . . . . . .> from employee e full outer join employee_address e_a
. . . . . . . . . . . . . .> on e.id =e_a.id;
+-------+-----------+-----------+-------------+
| e.id | e.name | e_a.city | e_a.street |
+-------+-----------+-----------+-------------+
| 1201 | gopal | jublee | vgiri |
| 1202 | manisha | ny | aoc |
| 1203 | khalil | NULL | NULL |
| 1204 | prasanth | hyd | pgutta |
| 1206 | kranthi | la | old city |
| NULL | NULL | ny | hitec |
+-------+-----------+-----------+-------------+
6 rows selected (27.283 seconds)
--等价于
0: jdbc:hive2://ops01:10000> select e.id,e.name,e_a.city,e_a.street
. . . . . . . . . . . . . .> from employee e full join employee_address e_a
. . . . . . . . . . . . . .> on e.id =e_a.id;
+-------+-----------+-----------+-------------+
| e.id | e.name | e_a.city | e_a.street |
+-------+-----------+-----------+-------------+
| 1201 | gopal | jublee | vgiri |
| 1202 | manisha | ny | aoc |
| 1203 | khalil | NULL | NULL |
| 1204 | prasanth | hyd | pgutta |
| 1206 | kranthi | la | old city |
| NULL | NULL | ny | hitec |
+-------+-----------+-----------+-------------+
6 rows selected (20.993 seconds)
left semi join 左半开连接
- 左半开连接（LEFT SEMI JOIN）会返回左边表的记录，前提是其记录对于右边的表满足ON语句中的判定条件
- 从效果上来看有点像inner join之后只返回左表的结果
--5、left semi join
0: jdbc:hive2://ops01:10000> select *
. . . . . . . . . . . . . .> from employee e left semi join employee_address e_addr
. . . . . . . . . . . . . .> on e.id =e_addr.id;
+-------+-----------+----------+-----------+---------+
| e.id | e.name | e.deg | e.salary | e.dept |
+-------+-----------+----------+-----------+---------+
| 1201 | gopal | manager | 50000 | TP |
| 1202 | manisha | cto | 50000 | TP |
| 1204 | prasanth | dev | 30000 | AC |
| 1206 | kranthi | admin | 20000 | TP |
+-------+-----------+----------+-----------+---------+
4 rows selected (24.137 seconds)
cross join 交叉连接
- 交叉连接cross join，将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积。对于大表来说，cross join慎用。
- 在SQL标准中定义的cross join就是无条件的inner join。返回两个表的笛卡尔积,无需指定关联键。
- 在HiveSQL语法中，cross join 后面可以跟where子句进行过滤，或者on条件过滤
-- 6、cross join
-- 下列A、B、C 执行结果相同，但是效率不一样：
-- A:
select a.*,b.* from employee a,employee_address b where a.id=b.id;
-- B:
select * from employee a cross join employee_address b on a.id=b.id;
-- C:
select * from employee a inner join employee_address b on a.id=b.id;
-- 一般不建议使用方法A和B，因为如果有WHERE子句的话，往往会先生成两个表行数乘积的行的数据表然后才根据WHERE条件从中选择。
-- 因此，如果两个需要求交集的表太大，将会非常非常慢，不建议使用。
15-3.Hive Join使用注意事项
- 允许使用复杂的联接表达式,支持非等值连接
- 同一查询中可以连接2个以上的表
- 如果每个表在联接子句中使用相同的列，则Hive将多个表上的联接转换为单个MR作业
- join时的最后一个表会通过reducer流式传输，并在其中缓冲之前的其他表，因此，将大表放置在最后有助于减少reducer阶段缓存数据所需要的内存
- 在join的时候，可以通过语法STREAMTABLE提示指定要流式传输的表。如果省略STREAMTABLE提示，则Hive将流式传输最右边的表
- join在WHERE条件之前进行
- 如果除一个要连接的表之外的所有表都很小，则可以将其作为仅map作业执行（mapjoin）
第16章:Hive 客户端与属性配置
16-1.CLIs and Commands客户端和命令
Hive CLI
-
$HIVE_HOME/bin/hive是一个shell Util,通常称之为hive的第一代客户端或者旧客户端，主要功能有两个：
-
交互式或批处理模式运行Hive查询
注意，此时作为客户端，需要并且能够访问的是Hive metastore服务，而不是hiveserver2服务
-
hive相关服务的启动，比如metastore服务
-
-
Batch Mode 批处理模式
- 当使用-e或-f选项运行bin/hive时，它将以批处理模式执行SQL命令
- 批处理可以理解为一次性执行，执行完毕退出
-
Interactive Shell 交互式模式
- 交互式模式可以理解为客户端和hive服务一直保持连接，除非手动退出客户端
-
启动Hive服务
- 例如metastore服务和hiveserver2服务的启动
# --hiveconf $HIVE_HOME/bin/hive --hiveconf hive.root.logger=DEBUG,console # --service $HIVE_HOME/bin/hive --service metastore $HIVE_HOME/bin/hive --service hiveserver2 12345
Beeline CLI
- $HIVE_HOME/bin/beeline被称之为第二代客户端或者新客户端，是一个JDBC客户端，是官方强烈推荐使用的Hive命令行工具。和第一代客户端相比，性能加强安全性提高。Beeline在嵌入式模式和远程模式下均可工作
- 在嵌入式模式下，它运行嵌入式Hive(类似于Hive CLI)
- 远程模式下beeline通过Thrift连接到单独的HiveServer2服务上，这也是官方推荐在生产环境中使用的模式
- 在启动hiveserver2服务的前提下使用beeline远程连接HS2服务
16-2.Configuration Properties属性配置
- Hive除了默认的属性配置之外，还支持用户使用时修改配置
- 修改Hive配置之前，用户需要关注：
- 有哪些属性支持用户修改，属性的功能、作用是什么
- 支持哪种方式进行修改，是临时生效还是永久生效的
- Hive配置属性是在HiveConf.Java类中管理
- 从Hive 0.14.0开始，会从HiveConf.java类中直接生成配置模板文件hive-default.xml.template
- 详细的配置参数大全可以参考Hive官网配置参数
- https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
属性参数配置方式一：hive-site.xml
- 在$HIVE_HOME/conf路径下，可以添加一个hive-site.xml文件，把需要定义修改的配置属性添加进去，这个配置文件会影响到基于这个Hive安装包的任何一种服务启动、客户端使用方式
属性参数配置方式二：hiveconf命令行参数
- hiveconf是一个命令行的参数，用于在使用Hive CLI或者Beeline CLI的时候指定配置参数
- 这种方式的配置在整个的会话session中有效，会话结束，失效
属性参数配置方式三：set命令
-
在Hive CLI或Beeline中使用set命令为set命令之后的所有SQL语句设置配置参数，这个也是会话级别的
-
set方式是用户日常开发中使用最多的一种配置参数方式
-
Hive倡导一种：谁需要、谁配置、谁使用的一种思想，避免你的属性修改影响其他用户的修改
例如：
#启用hive动态分区，需要在hive会话中设置两个参数：
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
属性参数配置方式四：服务特定配置文件
hivemetastore-site.xml、hiveserver2-site.xml
- Hive Metastore会加载可用的hive-site.xml以及hivemetastore-site.xml配置文件
- HiveServer2会加载可用的hive-site.xml以及hiveserver2-site.xml
各种配置方式总结
- 配置方式优先级
- set设置 > hiveconf参数 > hive-site.xml配置文件
- set参数声明会覆盖命令行参数hiveconf，命令行参数会覆盖配置文件hive-site.xml设定
- 日常开发使用中，如果不是核心的需要全局修改的参数属性，建议使用set命令进行设置
- Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置
第17章:Hive 内置运算符
Hive支持的运算符可以分为三大类：
- 关系运算
- 算术运算
- 逻辑运算
17-1.关系运算符
- 关系运算符是二元运算符，执行的是两个操作数的比较运算
- 每个关系运算符都返回boolean类型结果（TRUE或FALSE）
例如：
is null 空值判断
is not null 非空值判断
17-2.算术运算符
- 算术运算符操作数必须是数值类型。 分为一元运算符和二元运算符
- 一元运算符,只有一个操作数; 二元运算符有两个操作数,运算符在两个操作数之间
例如：
加减乘除、取整div、取余%、与或等
17-3.逻辑运算符
- [NOT] EXISTS
- 语法：SELECT … FROM table WHERE [NOT] EXISTS (subquery)
- 将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留
例如：
where 3>1 and 2>1
where 3>1 or 2!=2
where not 2>1
where !2=1
where 11 in(11,22,33)
where 11 not in(22,33,44)
select A.* from A where exists (select B.id from B where A.id = B.id);
第18章:Hive 函数入门
18-1.Hive 函数概述及分类标准
函数概述
-
Hive内建了不少函数，用于满足用户不同使用需求，提高SQL编写效率
-
使用show functions查看当下可用的所有函数
-
通过describe function extended funcname来查看函数的使用方式
0: jdbc:hive2://ops01:10000> show functions like '*count*'; +-----------------+ | tab_name | +-----------------+ | count | | regr_count | | sq_count_check | +-----------------+ 3 rows selected (0.026 seconds) 0: jdbc:hive2://ops01:10000> describe function extended count; +----------------------------------------------------+ | tab_name | +----------------------------------------------------+ | count(*) - Returns the total number of retrieved rows, including rows containing NULL values. | | count(expr) - Returns the number of rows for which the supplied expression is non-NULL. | | count(DISTINCT expr[, expr...]) - Returns the number of rows for which the supplied expression(s) are unique and non-NULL. | | Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDAFCount | | Function type:BUILTIN | +----------------------------------------------------+ 5 rows selected (0.028 seconds)
-
分类标准
- Hive的函数分为两大类：内置函数（Built-in Functions）、用户定义函数UDF（User-Defined Functions）
- 内置函数可分为：数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等等
- 用户定义函数根据输入输出的行数可分为3类：UDF、UDAF、UDTF
- UDF（User-Defined-Function）普通函数，一进一出
- UDAF（User-Defined Aggregation Function）聚合函数，多进一出
- UDTF（User-Defined Table-Generating Functions）表生成函数，一进多出
UDF分类标准扩大化：
- UDF分类标准本来针对的是用户自己编写开发实现的函数。UDF分类标准可以扩大到Hive的所有函数中：包括内置函数和用户自定义函数
18-2.Hive 内置函数
- 内置函数（build-in）指的是Hive开发实现好，直接可以使用的函数,也叫做内建函数
- 内置函数根据应用归类整体可以分为8大种类型
- 字符串函数
- 日期函数
- 数学函数
- 集合函数
- 条件函数
- 类型转换函数
- 数据脱敏函数
- 其他杂项函数
内置函数:字符串函数
- 字符串长度函数：length
- 字符串反转函数：reverse
- 字符串连接函数：concat
- 带分隔符字符串连接函数：concat_ws
- 字符串截取函数：substr,substring
- 字符串转大写函数：upper,ucase
- 字符串转小写函数：lower,lcase
- 去空格函数：trim
- 左边去空格函数：ltrim
- 右边去空格函数：rtrim
- 正则表达式替换函数：regexp_replace
- 正则表达式解析函数：regexp_extract
- URL解析函数：parse_url
- json解析函数：get_json_object
- 空格字符串函数：space
- 重复字符串函数：repeat
- 首字符ascii函数：ascii
- 左补足函数：lpad
- 右补足函数：rpad
- 分割字符串函数: split
- 集合查找函数: find_in_set
示例：
0: jdbc:hive2://ops01:10000> select concat("wow","wlk");
+---------+
| _c0 |
+---------+
| wowwlk |
+---------+
0: jdbc:hive2://ops01:10000> select concat_ws('_', 'wow', array('wlk', 'fs'));
+-------------+
| _c0 |
+-------------+
| wow_wlk_fs |
+-------------+
0: jdbc:hive2://ops01:10000> select substr("moshoushijie",3,4);
+-------+
| _c0 |
+-------+
| shou |
+-------+
0: jdbc:hive2://ops01:10000> select regexp_replace('100-200', '(\\d+)-(\\d+)', '666-700');
+----------+
| _c0 |
+----------+
| 666-700 |
+----------+
0: jdbc:hive2://ops01:10000> select parse_url('https://www.douyu.com/directory/myFollow', 'HOST');
+----------------+
| _c0 |
+----------------+
| www.douyu.com |
+----------------+
0: jdbc:hive2://ops01:10000> select parse_url('https://mp.csdn.net/mp_blog/manage/article?spm=1010.2135.3001.5448', 'HOST');
+--------------+
| _c0 |
+--------------+
| mp.csdn.net |
+--------------+
0: jdbc:hive2://ops01:10000> select length("dalao666");
+------+
| _c0 |
+------+
| 8 |
+------+
0: jdbc:hive2://ops01:10000> select reverse("dalao666");
+-----------+
| _c0 |
+-----------+
| 666oalad |
+-----------+
0: jdbc:hive2://ops01:10000> select upper("dalao666");
+-----------+
| _c0 |
+-----------+
| DALAO666 |
+-----------+
0: jdbc:hive2://ops01:10000> select find_in_set('ms','fs,ss,ms,zs,sq');
+------+
| _c0 |
+------+
| 3 |
+------+
内置函数:日期函数
- 获取当前日期: current_date
- 获取当前时间戳: current_timestamp
- UNIX时间戳转日期函数: from_unixtime
- 获取当前UNIX时间戳函数: unix_timestamp
- 日期转UNIX时间戳函数: unix_timestamp
- 指定格式日期转UNIX时间戳函数: unix_timestamp
- 抽取日期函数: to_date
- 日期转年函数: year
- 日期转月函数: month
- 日期转天函数: day
- 日期转小时函数: hour
- 日期转分钟函数: minute
- 日期转秒函数: second
- 日期转周函数: weekofyear
- 日期比较函数: datediff
- 日期增加函数: date_add
- 日期减少函数: date_sub
--获取当前日期: current_date
select current_date();
--获取当前时间戳: current_timestamp
select current_timestamp();
--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();
--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2022-10-18 19:01:03");
--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1618238391);
select from_unixtime(1666119663, 'yyyy-MM-dd HH:mm:ss');
--日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2022-10-15','2022-10-18');
--日期增加函数: date_add
select date_add('2022-10-15',10);
--日期减少函数: date_sub
select date_sub('2022-10-15',10);
--抽取日期函数: to_date
select to_date('2009-07-30 04:17:52');
--日期转年函数: year
select year('2009-07-30 04:17:52');
--日期转月函数: month
select month('2009-07-30 04:17:52');
--日期转天函数: day
select day('2009-07-30 04:17:52');
--日期转小时函数: hour
select hour('2009-07-30 04:17:52');
--日期转分钟函数: minute
select minute('2009-07-30 04:17:52');
--日期转秒函数: second
select second('2009-07-30 04:17:52');
--日期转周函数: weekofyear 返回指定日期所示年份第几周
select weekofyear('2009-07-30 04:17:52');
1234567891011121314151617181920212223242526272829303132333435363738394041
内置函数:数学函数
- 取整函数: round
- 指定精度取整函数: round
- 向下取整函数: floor
- 向上取整函数: ceil
- 取随机数函数: rand
- 二进制函数: bin
- 进制转换函数: conv
- 绝对值函数: abs
--取整函数: round 返回double类型的整数值部分 （遵循四舍五入）
select round(3.1415926);
--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);
--向下取整函数: floor
select floor(3.1415926);
select floor(-3.1415926);
--向上取整函数: ceil
select ceil(3.1415926);
select ceil(-3.1415926);
--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand();
--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);
--二进制函数: bin(BIGINT a)
select bin(18);
--进制转换函数: conv(BIGINT num, int from_base, int to_base)
select conv(17,10,16);
--绝对值函数: abs
select abs(-3.9);
内置函数:集合函数
- 集合元素size函数: size(Map<K.V>) size(Array)
- 取map集合keys函数: map_keys(Map<K.V>)
- 取map集合values函数: map_values(Map<K.V>)
- 判断数组是否包含指定元素: array_contains(Array, value)
- 数组排序函数:sort_array(Array)
--集合元素size函数: size(Map<K.V>) size(Array<T>)
select size(`array`(11,22,33));
select size(`map`("id",10086,"name","zhangsan","age",18));
--取map集合keys函数: map_keys(Map<K.V>)
select map_keys(`map`("id",10086,"name","zhangsan","age",18));
--取map集合values函数: map_values(Map<K.V>)
select map_values(`map`("id",10086,"name","zhangsan","age",18));
--判断数组是否包含指定元素: array_contains(Array<T>, value)
select array_contains(`array`(11,22,33),11);
select array_contains(`array`(11,22,33),66);
--数组排序函数:sort_array(Array<T>)
select sort_array(`array`(12,2,32));
内置函数:条件函数
- if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
- 空判断函数: isnull( a )
- 非空判断函数: isnotnull ( a )
- 空值转换函数: nvl(T value, T default_value)
- 非空查找函数: COALESCE(T v1, T v2, …)
- 条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
- nullif( a, b ): 如果a = b，则返回NULL；否则返回NULL。否则返回一个
- assert_true: 如果’condition’不为真，则引发异常，否则返回null
--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);
select if(sex ='男','M','W') from student limit 3;
--空判断函数: isnull( a )
select isnull("allen");
select isnull(null);
--非空判断函数: isnotnull ( a )
select isnotnull("allen");
select isnotnull(null);
--空值转换函数: nvl(T value, T default_value)
select nvl("someone","default");
select nvl(null,"default");
--非空查找函数: COALESCE(T v1, T v2, ...)
--返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
select COALESCE(null,11,22,33);
select COALESCE(null,null,null,33);
select COALESCE(null,null,null);
--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
SELECT CASE 100
WHEN 50 THEN 'this50'
WHEN 100 THEN 'this100'
ELSE 'nonono'
END;
--nullif( a, b ):
-- 如果a = b，则返回NULL，否则返回一个
select nullif(11,11);
select nullif(11,12);
--assert_true(condition)
--如果'condition'不为真，则引发异常，否则返回null
SELECT assert_true(11 >= 0);
SELECT assert_true(-1 >= 0);
内置函数:类型转换函数
- 主要用于显式的数据类型转换
- 主要完成对数据脱敏转换功能，屏蔽原始数据
--任意数据类型之间转换:cast
select cast(12.14 as bigint);
select cast(12.14 as string);
select cast("hello" as int);
1234
内置函数:数据脱敏函数
- mask
- mask_first_n(string str[, int n]
- mask_last_n(string str[, int n])
- mask_show_first_n(string str[, int n])
- mask_show_last_n(string str[, int n])
- mask_hash(string|char|varchar str)
--将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。
select mask("abc123DEF");
--自定义替换的字母
select mask("abc123DEF",'-','.','^');
--mask_first_n(string str[, int n]
--对前n个进行脱敏替换
select mask_first_n("abc123DEF",4);
--mask_last_n(string str[, int n])
select mask_last_n("abc123DEF",4);
--mask_show_first_n(string str[, int n])
--除了前n个字符，其余进行掩码处理
select mask_show_first_n("abc123DEF",4);
--mask_show_last_n(string str[, int n])
select mask_show_last_n("abc123DEF",4);
--mask_hash(string|char|varchar str)
--返回字符串的hash编码。
select mask_hash("abc123DEF");
内置函数:其他杂项函数
- hive调用java方法: java_method(class, method[, arg1[, arg2…]])
- 反射函数: reflect(class, method[, arg1[, arg2…]])
- 取哈希值函数:hash
- current_user()、logged_in_user()、current_database()、version()
- SHA-1加密: sha1(string/binary)
- SHA-2家族算法加密：sha2(string/binary, int) (SHA-224, SHA-256, SHA-384, SHA-512)
- crc32加密:
- MD5加密: md5(string/binary)
--hive调用java方法: java_method(class, method[, arg1[, arg2..]])
select java_method("java.lang.Math","max",11,22);
--反射函数: reflect(class, method[, arg1[, arg2..]])
select reflect("java.lang.Math","max",11,22);
--取哈希值函数:hash
select hash("allen");
--SHA-1加密: sha1(string/binary)
select sha1("allen");
--SHA-2家族算法加密：sha2(string/binary, int) (SHA-224, SHA-256, SHA-384, SHA-512)
select sha2("allen",224);
select sha2("allen",512);
--crc32加密:
select crc32("allen");
--MD5加密: md5(string/binary)
select md5("allen");
18-3.Hive 用户自定义函数（UDF、UDTF、UDAF）
UDF 普通函数
- 特点是一进一出，也就是输入一行输出一行
- 例如round这样的取整函数，接收一行数据，输出的还是一行数据
UDAF 聚合函数
- UDAF 聚合函数，A所代表的单词就是Aggregation聚合的意思
- 多进一出，也就是输入多行输出一行
- 例如count、sum这样的函数
UDTF 表生成函数
- UDTF 表生成函数，T所代表的单词是Table-Generating表生成的意思
- 特点是一进多出，也就是输入一行输出多行
- 这类型的函数作用返回的结果类似于表，例如比如explode函数
0: jdbc:hive2://ops01:10000> select explode(array(11,22,33,44,55));
±-----+
| col |
±-----+
| 11 |
| 22 |
| 33 |
| 44 |
| 55 |
±-----+
5 rows selected (0.112 seconds)
18-4.案例：UDF实现手机号加密
通过一个详细的案例来演示Hive UDF用户自定义函数
自定义用户开发UDF背景
背景：开发Hive UDF实现手机号****加密
在企业中处理数据的时候，对于敏感数据往往需要进行脱敏处理。比如手机号。我们常见的处理方式是将手机号中间4位进行****处理。
Hive中没有这样的函数可以直接实现功能，虽然可以通过各种函数的嵌套调用最终也能实现，但是效率不高，现要求自定义开发实现Hive函数，满足上述需求。
1、能够对输入数据进行非空判断、手机号位数判断
2、能够实现校验手机号格式，把满足规则的进行****处理
3、对于不符合手机号规则的数据直接返回，不处理
UDF实现步骤
- 写一个java类，继承UDF，并重载evaluate方法，方法中实现函数的业务逻辑；
- 重载意味着可以在一个java类中实现多个函数功能；
- 程序打成jar包，上传HS2服务器本地或者HDFS;
- 客户端命令行中添加jar包到Hive的classpath： hive>add JAR /xxxx/udf.jar;
- 注册成为临时函数（给UDF命名）：create temporary function 函数名 as ‘UDF类全路径’;
- HQL中使用函数。
开发环境准备
IDEA中创建Maven工程，添加下述pom依赖，用于开发Hive UDF，名称可自行定义
- GroupId
- cn.wangting
- ArtifactId
- hive_udf
pom.xml文件：
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>cn.wangting</groupId>
<artifactId>hive-udf</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>3.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>3.1.4</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-shade-plugin</artifactId>
<version>2.2</version>
<executions>
<execution>
<phase>package</phase>
<goals>
<goal>shade</goal>
</goals>
<configuration>
<filters>
<filter>
<artifact>*:*</artifact>
<excludes>
<exclude>META-INF/*.SF</exclude>
<exclude>META-INF/*.DSA</exclude>
<exclude>META-INF/*.RSA</exclude>
</excludes>
</filter>
</filters>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>
</project>
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950
UDF代码开发
创建包：cn.wangting.hive.udf
在创建的包下创建类：EncryptPhoneNumber
EncryptPhoneNumber核心代码如下：
package cn.wangting.hive.udf;
import org.apache.commons.lang.StringUtils;
import org.apache.hadoop.hive.ql.exec.UDF;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class EncryptPhoneNumber extends UDF {
public String evaluate(String phoNum){
String encryptPhoNum = null;
if (StringUtils.isNotEmpty(phoNum) && phoNum.trim().length() == 11 ) {
String regex = "^(1[3-9]\\d{9}$)";
Pattern p = Pattern.compile(regex);
Matcher m = p.matcher(phoNum);
if (m.matches()) {
encryptPhoNum = phoNum.trim().replaceAll("(\\d{3})\\d{4}(\\d{4})","$1****$2");
}else{
encryptPhoNum = phoNum;
}
}else{
encryptPhoNum = phoNum;
}
return encryptPhoNum;
}
}
项目开发完毕，在maven工具栏中点击package打包，打包完毕后的hive-udf-1.0-SNAPSHOT.jar上传至服务器
函数jar包上传引用
把jar包上传到Hiveserver2服务运行所在机器的Linux系统
wangting@ops01:/home/wangting/20221019 >pwd
/home/wangting/20221019
wangting@ops01:/home/wangting/20221019 >ls
hive-udf-1.0-SNAPSHOT.jar
通过hive导入
0: jdbc:hive2://ops01:10000> add jar /home/wangting/20221019/hive-udf-1.0-SNAPSHOT.jar;
No rows affected (0.012 seconds)
0: jdbc:hive2://ops01:10000> create temporary function encrypt_phonum as 'cn.wangting.hive.udf.EncryptPhoneNumber';
No rows affected (0.036 seconds)
使用验证
0: jdbc:hive2://ops01:10000> select encrypt_phonum("18621372666");
+--------------+
| _c0 |
+--------------+
| 186****2666 |
+--------------+
1 row selected (0.129 seconds)
0: jdbc:hive2://ops01:10000> select encrypt_phonum("186213726");
+------------+
| _c0 |
+------------+
| 186213726 |
+------------+
1 row selected (0.126 seconds)
0: jdbc:hive2://ops01:10000> select encrypt_phonum("aaa186213726");
+---------------+
| _c0 |
+---------------+
| aaa186213726 |
+---------------+
1 row selected (0.154 seconds)
0: jdbc:hive2://ops01:10000> select encrypt_phonum("13866668787");
+--------------+
| _c0 |
+--------------+
| 138****8787 |
+--------------+
1 row selected (0.133 seconds)
第19章:Hive 函数高阶
19-1.UDTF之explode函数
- explode接收map、array类型的数据作为输入，然后把输入数据中的每个元素拆开变成一行数据，一个元素一行
- explode执行效果正好满足于输入一行输出多行，所有叫做UDTF函数
- 一般情况下，explode函数可以直接单独使用
explode(array) 将array里的每个元素生成一行
explode(map) 将map里的每一对元素作为一行，其中key为一列，value为一列
0: jdbc:hive2://ops01:10000> select explode(`array`(11,22,33)) as item;
+-------+
| item |
+-------+
| 11 |
| 22 |
| 33 |
+-------+
3 rows selected (0.114 seconds)
0: jdbc:hive2://ops01:10000> select explode(`map`("id",10086,"name","zhangsan","age",18));
+-------+-----------+
| key | value |
+-------+-----------+
| id | 10086 |
| name | zhangsan |
| age | 18 |
+-------+-----------+
3 rows selected (0.127 seconds)
19-2.Lateral View侧视图
- Lateral View是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的一些查询限制的问题
- 一般只要使用UDTF，就会固定搭配lateral view使用
Lateral View原理：
- 将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题
- 使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by 、limit等语句中，不需要再单独嵌套一层子查询
explode函数+Lateral View侧视图案例
背景：NBA总冠军球队名单分析
有一份数据《The_NBA_Championship.txt》，关于部分年份的NBA总冠军球队名单；
第一个字段表示球队名称，第二个字段是获取总冠军的年份；字段之间以，分割；总冠军年份之间以|进行分割
使用Hive建表映射成功数据，对数据拆分
数据文件：
wangting@ops01:/home/wangting/20221019 >pwd /home/wangting/20221019 wangting@ops01:/home/wangting/20221019 >cat the_nba_championship.txt Chicago Bulls,1991|1992|1993|1996|1997|1998 San Antonio Spurs,1999|2003|2005|2007|2014 Golden State Warriors,1947|1956|1975|2015 Boston Celtics,1957|1959|1960|1961|1962|1963|1964|1965|1966|1968|1969|1974|1976|1981|1984|1986|2008 L.A. Lakers,1949|1950|1952|1953|1954|1972|1980|1982|1985|1987|1988|2000|2001|2002|2009|2010 Miami Heat,2006|2012|2013 Philadelphia 76ers,1955|1967|1983 Detroit Pistons,1989|1990|2004 Houston Rockets,1994|1995 New York Knicks,1970|1973
执行建表：
create table the_nba_championship(
team_name string,
champion_year array<string>
) row format delimited
fields terminated by ','
collection items terminated by '|';
数据加载：
0: jdbc:hive2://ops01:10000> load data local inpath '/home/wangting/20221019/the_nba_championship.txt' into table the_nba_championship;
No rows affected (0.157 seconds)
0: jdbc:hive2://ops01:10000> select * from the_nba_championship limit 2;
+---------------------------------+----------------------------------------------+
| the_nba_championship.team_name | the_nba_championship.champion_year |
+---------------------------------+----------------------------------------------+
| Chicago Bulls | ["1991","1992","1993","1996","1997","1998"] |
| San Antonio Spurs | ["1999","2003","2005","2007","2014"] |
+---------------------------------+----------------------------------------------+
2 rows selected (0.145 seconds)
使用lateral view +explode实现需求：
0: jdbc:hive2://ops01:10000> select a.team_name,b.year
. . . . . . . . . . . . . .> from the_nba_championship a lateral view explode(champion_year) b as year
. . . . . . . . . . . . . .> order by b.year desc;
+------------------------+---------+
| a.team_name | b.year |
+------------------------+---------+
| Golden State Warriors | 2015 |
| San Antonio Spurs | 2014 |
| Miami Heat | 2013 |
| Miami Heat | 2012 |
| L.A. Lakers | 2010 |
| L.A. Lakers | 2009 |
| Boston Celtics | 2008 |
| San Antonio Spurs | 2007 |
| Miami Heat | 2006 |
| San Antonio Spurs | 2005 |
| Detroit Pistons | 2004 |
| San Antonio Spurs | 2003 |
| L.A. Lakers | 2002 |
| L.A. Lakers | 2001 |
| L.A. Lakers | 2000 |
| San Antonio Spurs | 1999 |
| Chicago Bulls | 1998 |
| Chicago Bulls | 1997 |
| Chicago Bulls | 1996 |
| Houston Rockets | 1995 |
| Houston Rockets | 1994 |
| Chicago Bulls | 1993 |
| Chicago Bulls | 1992 |
| Chicago Bulls | 1991 |
| Detroit Pistons | 1990 |
| Detroit Pistons | 1989 |
| L.A. Lakers | 1988 |
| L.A. Lakers | 1987 |
| Boston Celtics | 1986 |
| L.A. Lakers | 1985 |
| Boston Celtics | 1984 |
| Philadelphia 76ers | 1983 |
| L.A. Lakers | 1982 |
| Boston Celtics | 1981 |
| L.A. Lakers | 1980 |
| Boston Celtics | 1976 |
| Golden State Warriors | 1975 |
| Boston Celtics | 1974 |
| New York Knicks | 1973 |
| L.A. Lakers | 1972 |
| New York Knicks | 1970 |
| Boston Celtics | 1969 |
| Boston Celtics | 1968 |
| Philadelphia 76ers | 1967 |
| Boston Celtics | 1966 |
| Boston Celtics | 1965 |
| Boston Celtics | 1964 |
| Boston Celtics | 1963 |
| Boston Celtics | 1962 |
| Boston Celtics | 1961 |
| Boston Celtics | 1960 |
| Boston Celtics | 1959 |
| Boston Celtics | 1957 |
| Golden State Warriors | 1956 |
| Philadelphia 76ers | 1955 |
| L.A. Lakers | 1954 |
| L.A. Lakers | 1953 |
| L.A. Lakers | 1952 |
| L.A. Lakers | 1950 |
| L.A. Lakers | 1949 |
| Golden State Warriors | 1947 |
+------------------------+---------+
19-3.Aggregation聚合函数
基础聚合
- 聚合函数的功能是：对一组值执行计算并返回单一的值
- 聚合函数是典型的输入多行输出一行，使用Hive的分类标准，属于UDAF类型函数
- 通常搭配Group By语法一起使用，分组后进行聚合操作
- HQL提供了几种内置的UDAF聚合函数，例如max（…），min（…）和avg（…）。这些我们把它称之为基础的聚合函数
增强聚合
- 增强聚合包括grouping_sets、cube、rollup这几个函数；主要适用于OLAP多维数据分析模式中，多维分析中的维指的分析问题时看待问题的维度、角度
- grouping sets
- 是一种将多个group by逻辑写在一个sql语句中的便利写法。等价于将不同维度的GROUP BY结果集进行UNION ALL。GROUPING__ID表示结果属于哪一个分组集合
- cube
- 表示根据GROUP BY的维度的所有组合进行聚合
- rollup
- rollup是cube的子集，以最左侧的维度为主，从该维度进行层级聚合
- 比如ROLLUP有a,b,c3个维度，则所有组合情况是：(a,b,c),(a,b),(a),()
案例：
cookie_info.txt
2022-03,2022-03-10,cookie1
2022-03,2022-03-10,cookie5
2022-03,2022-03-12,cookie7
2022-04,2022-04-12,cookie3
2022-04,2022-04-13,cookie2
2022-04,2022-04-13,cookie4
2022-04,2022-04-16,cookie4
2022-03,2022-03-10,cookie2
2022-03,2022-03-10,cookie3
2022-04,2022-04-12,cookie5
2022-04,2022-04-13,cookie6
2022-04,2022-04-15,cookie3
2022-04,2022-04-15,cookie2
2022-04,2022-04-16,cookie1
1234567891011121314
-- 建测试表
CREATE TABLE cookie_info(
month STRING,
day STRING,
cookieid STRING
) ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
-- 加载数据
load data local inpath '/home/wangting/20221019/cookie_info.txt' into table cookie_info;
0: jdbc:hive2://ops01:10000> select * from cookie_info;
+--------------------+------------------+-----------------------+
| cookie_info.month | cookie_info.day | cookie_info.cookieid |
+--------------------+------------------+-----------------------+
| 2022-03 | 2022-03-10 | cookie1 |
| 2022-03 | 2022-03-10 | cookie5 |
| 2022-03 | 2022-03-12 | cookie7 |
| 2022-04 | 2022-04-12 | cookie3 |
| 2022-04 | 2022-04-13 | cookie2 |
| 2022-04 | 2022-04-13 | cookie4 |
| 2022-04 | 2022-04-16 | cookie4 |
| 2022-03 | 2022-03-10 | cookie2 |
| 2022-03 | 2022-03-10 | cookie3 |
| 2022-04 | 2022-04-12 | cookie5 |
| 2022-04 | 2022-04-13 | cookie6 |
| 2022-04 | 2022-04-15 | cookie3 |
| 2022-04 | 2022-04-15 | cookie2 |
| 2022-04 | 2022-04-16 | cookie1 |
+--------------------+------------------+-----------------------+
14 rows selected (0.138 seconds)
---group sets---------
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS nums,
GROUPING__ID
FROM cookie_info
GROUP BY month,day
GROUPING SETS (month,day)
ORDER BY GROUPING__ID;
--根据grouping sets中的分组条件month，day，1是代表month，2是代表day
--等价于
SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month
UNION ALL
SELECT NULL as month,day,COUNT(DISTINCT cookieid) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day;
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS nums,
GROUPING__ID
FROM cookie_info
GROUP BY month,day
GROUPING SETS (month,day,(month,day)) --1 month 2 day 3 (month,day)
ORDER BY GROUPING__ID;
--等价于
SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS nums,3 AS GROUPING__ID FROM cookie_info GROUP BY month,day;
------cube---------------
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS nums,
GROUPING__ID
FROM cookie_info
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;
--等价于
SELECT NULL,NULL,COUNT(DISTINCT cookieid) AS nums,0 AS GROUPING__ID FROM cookie_info
UNION ALL
SELECT month,NULL,COUNT(DISTINCT cookieid) AS nums,1 AS GROUPING__ID FROM cookie_info GROUP BY month
UNION ALL
SELECT NULL,day,COUNT(DISTINCT cookieid) AS nums,2 AS GROUPING__ID FROM cookie_info GROUP BY day
UNION ALL
SELECT month,day,COUNT(DISTINCT cookieid) AS nums,3 AS GROUPING__ID FROM cookie_info GROUP BY month,day;
--rollup-------------
--比如，以month维度进行层级聚合：
SELECT
month,
day,
COUNT(DISTINCT cookieid) AS nums,
GROUPING__ID
FROM cookie_info
GROUP BY month,day
WITH ROLLUP
ORDER BY GROUPING__ID;
--把month和day调换顺序，则以day维度进行层级聚合：
SELECT
day,
month,
COUNT(DISTINCT cookieid) AS uv,
GROUPING__ID
FROM cookie_info
GROUP BY day,month
WITH ROLLUP
ORDER BY GROUPING__ID;
19-4.Windows Functions窗口函数
窗口函数概述
- 窗口函数（Window functions）也叫做开窗函数、OLAP函数，其最大特点是：输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的
- 如果函数具有OVER子句，则它是窗口函数
- 窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行，窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中
语法规则：
Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])
1
示例对比：
0: jdbc:hive2://ops01:10000> select * from employee;
+--------------+----------------+---------------+------------------+----------------+
| employee.id | employee.name | employee.deg | employee.salary | employee.dept |
+--------------+----------------+---------------+------------------+----------------+
| 1201 | gopal | manager | 50000 | TP |
| 1202 | manisha | cto | 50000 | TP |
| 1203 | khalil | dev | 30000 | AC |
| 1204 | prasanth | dev | 30000 | AC |
| 1206 | kranthi | admin | 20000 | TP |
+--------------+----------------+---------------+------------------+----------------+
0: jdbc:hive2://ops01:10000> select dept,sum(salary) as total from employee group by dept;
+-------+---------+
| dept | total |
+-------+---------+
| AC | 60000 |
| TP | 120000 |
+-------+---------+
0: jdbc:hive2://ops01:10000> select id,name,deg,salary,dept,sum(salary) over(partition by dept) as total from employee;
+-------+-----------+----------+---------+-------+---------+
| id | name | deg | salary | dept | total |
+-------+-----------+----------+---------+-------+---------+
| 1204 | prasanth | dev | 30000 | AC | 60000 |
| 1203 | khalil | dev | 30000 | AC | 60000 |
| 1206 | kranthi | admin | 20000 | TP | 120000 |
| 1202 | manisha | cto | 50000 | TP | 120000 |
| 1201 | gopal | manager | 50000 | TP | 120000 |
+-------+-----------+----------+---------+-------+---------+
窗口函数案例
背景：网站用户页面浏览次数分析
在网站访问中，经常使用cookie来标识不同的用户身份，通过cookie可以追踪不同用户的页面访问情况
website_pv_info.txt
cookie1,2018-04-10,1
cookie1,2018-04-11,5
cookie1,2018-04-12,7
cookie1,2018-04-13,3
cookie1,2018-04-14,2
cookie1,2018-04-15,4
cookie1,2018-04-16,4
cookie2,2018-04-10,2
cookie2,2018-04-11,3
cookie2,2018-04-12,5
cookie2,2018-04-13,6
cookie2,2018-04-14,3
cookie2,2018-04-15,9
cookie2,2018-04-16,7
1234567891011121314
website_url_info.txt
cookie1,2018-04-10 10:00:02,url2
cookie1,2018-04-10 10:00:00,url1
cookie1,2018-04-10 10:03:04,1url3
cookie1,2018-04-10 10:50:05,url6
cookie1,2018-04-10 11:00:00,url7
cookie1,2018-04-10 10:10:00,url4
cookie1,2018-04-10 10:50:01,url5
cookie2,2018-04-10 10:00:02,url22
cookie2,2018-04-10 10:00:00,url11
cookie2,2018-04-10 10:03:04,1url33
cookie2,2018-04-10 10:50:05,url66
cookie2,2018-04-10 11:00:00,url77
cookie2,2018-04-10 10:10:00,url44
cookie2,2018-04-10 10:50:01,url55
执行建表上传数据文件：
---建表并且加载数据
create table website_pv_info(
cookieid string,
createtime string,
pv int
) row format delimited
fields terminated by ',';
create table website_url_info (
cookieid string,
createtime string,
url string
) row format delimited
fields terminated by ',';
0: jdbc:hive2://ops01:10000> load data local inpath '/home/wangting/20221019/website_pv_info.txt' into table website_pv_info;
No rows affected (0.168 seconds)
0: jdbc:hive2://ops01:10000> load data local inpath '/home/wangting/20221019/website_url_info.txt' into table website_url_info;
No rows affected (0.16 seconds)
-- 验证数据加载情况
0: jdbc:hive2://ops01:10000> select * from website_pv_info limit 2;
+---------------------------+-----------------------------+---------------------+
| website_pv_info.cookieid | website_pv_info.createtime | website_pv_info.pv |
+---------------------------+-----------------------------+---------------------+
| cookie1 | 2018-04-10 | 1 |
| cookie1 | 2018-04-11 | 5 |
+---------------------------+-----------------------------+---------------------+
2 rows selected (0.163 seconds)
0: jdbc:hive2://ops01:10000> select * from website_url_info limit 2;
+----------------------------+------------------------------+-----------------------+
| website_url_info.cookieid | website_url_info.createtime | website_url_info.url |
+----------------------------+------------------------------+-----------------------+
| cookie1 | 2018-04-10 10:00:02 | url2 |
| cookie1 | 2018-04-10 10:00:00 | url1 |
+----------------------------+------------------------------+-----------------------+
2 rows selected (0.138 seconds)
窗口聚合函数
- 所谓窗口聚合函数指的是sum、max、min、avg这样的聚合函数在窗口中的使用
- 这里以sum()函数为例，其他聚合函数使用类似
--1、求出每个用户总pv数 sum+group by普通常规聚合操作
select cookieid,sum(pv) as total_pv from website_pv_info group by cookieid;
+-----------+-----------+
| cookieid | total_pv |
+-----------+-----------+
| cookie1 | 26 |
| cookie2 | 35 |
+-----------+-----------+
--2、sum+窗口函数 总共有四种用法 注意是整体聚合 还是累积聚合
--sum(...) over( )对表所有行求和
--sum(...) over( order by ... ) 连续累积求和
--sum(...) over( partition by... ) 同组内所行求和
--sum(...) over( partition by... order by ... ) 在每个分组内，连续累积求和
--需求：求出网站总的pv数 所有用户所有访问加起来
--sum(...) over( )对表所有行求和
select cookieid,createtime,pv,
sum(pv) over() as total_pv
from website_pv_info;
+-----------+-------------+-----+-----------+
| cookieid | createtime | pv | total_pv |
+-----------+-------------+-----+-----------+
| cookie2 | 2018-04-16 | 7 | 61 |
| cookie2 | 2018-04-15 | 9 | 61 |
| cookie2 | 2018-04-14 | 3 | 61 |
| cookie2 | 2018-04-13 | 6 | 61 |
| cookie2 | 2018-04-12 | 5 | 61 |
| cookie2 | 2018-04-11 | 3 | 61 |
| cookie2 | 2018-04-10 | 2 | 61 |
| cookie1 | 2018-04-16 | 4 | 61 |
| cookie1 | 2018-04-15 | 4 | 61 |
| cookie1 | 2018-04-14 | 2 | 61 |
| cookie1 | 2018-04-13 | 3 | 61 |
| cookie1 | 2018-04-12 | 7 | 61 |
| cookie1 | 2018-04-11 | 5 | 61 |
| cookie1 | 2018-04-10 | 1 | 61 |
+-----------+-------------+-----+-----------+
--需求：求出每个用户总pv数
--sum(...) over( partition by... )，同组内所行求和
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid) as total_pv
from website_pv_info;
+-----------+-------------+-----+-----------+
| cookieid | createtime | pv | total_pv |
+-----------+-------------+-----+-----------+
| cookie1 | 2018-04-10 | 1 | 26 |
| cookie1 | 2018-04-16 | 4 | 26 |
| cookie1 | 2018-04-15 | 4 | 26 |
| cookie1 | 2018-04-14 | 2 | 26 |
| cookie1 | 2018-04-13 | 3 | 26 |
| cookie1 | 2018-04-12 | 7 | 26 |
| cookie1 | 2018-04-11 | 5 | 26 |
| cookie2 | 2018-04-16 | 7 | 35 |
| cookie2 | 2018-04-15 | 9 | 35 |
| cookie2 | 2018-04-14 | 3 | 35 |
| cookie2 | 2018-04-13 | 6 | 35 |
| cookie2 | 2018-04-12 | 5 | 35 |
| cookie2 | 2018-04-11 | 3 | 35 |
| cookie2 | 2018-04-10 | 2 | 35 |
+-----------+-------------+-----+-----------+
--需求：求出每个用户截止到当天，累积的总pv数
--sum(...) over( partition by... order by ... )，在每个分组内，连续累积求和
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as current_total_pv
from website_pv_info;
+-----------+-------------+-----+-------------------+
| cookieid | createtime | pv | current_total_pv |
+-----------+-------------+-----+-------------------+
| cookie1 | 2018-04-10 | 1 | 1 |
| cookie1 | 2018-04-11 | 5 | 6 |
| cookie1 | 2018-04-12 | 7 | 13 |
| cookie1 | 2018-04-13 | 3 | 16 |
| cookie1 | 2018-04-14 | 2 | 18 |
| cookie1 | 2018-04-15 | 4 | 22 |
| cookie1 | 2018-04-16 | 4 | 26 |
| cookie2 | 2018-04-10 | 2 | 2 |
| cookie2 | 2018-04-11 | 3 | 5 |
| cookie2 | 2018-04-12 | 5 | 10 |
| cookie2 | 2018-04-13 | 6 | 16 |
| cookie2 | 2018-04-14 | 3 | 19 |
| cookie2 | 2018-04-15 | 9 | 28 |
| cookie2 | 2018-04-16 | 7 | 35 |
+-----------+-------------+-----+-------------------+
窗口表达式
- 在sum(…) over( partition by… order by … )语法完整的情况下，进行累积聚合操作，默认累积聚合行为是：从第一行聚合到当前行
- Window expression窗口表达式给我们提供了一种控制行范围的能力，比如向前2行，向后3行
- 常用语法
- preceding：往前
- following：往后
- current row：当前行
- unbounded：边界
- unbounded preceding 表示从前面的起点
- unbounded following：表示到后面的终点
-- 窗口表达式
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime) as pv1
from website_pv_info;
+-----------+-------------+-----+------+
| cookieid | createtime | pv | pv1 |
+-----------+-------------+-----+------+
| cookie1 | 2018-04-10 | 1 | 1 |
| cookie1 | 2018-04-11 | 5 | 6 |
| cookie1 | 2018-04-12 | 7 | 13 |
| cookie1 | 2018-04-13 | 3 | 16 |
| cookie1 | 2018-04-14 | 2 | 18 |
| cookie1 | 2018-04-15 | 4 | 22 |
| cookie1 | 2018-04-16 | 4 | 26 |
| cookie2 | 2018-04-10 | 2 | 2 |
| cookie2 | 2018-04-11 | 3 | 5 |
| cookie2 | 2018-04-12 | 5 | 10 |
| cookie2 | 2018-04-13 | 6 | 16 |
| cookie2 | 2018-04-14 | 3 | 19 |
| cookie2 | 2018-04-15 | 9 | 28 |
| cookie2 | 2018-04-16 | 7 | 35 |
+-----------+-------------+-----+------+
--第一行到当前行
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from website_pv_info;
--向前3行至当前行
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from website_pv_info;
--向前3行 向后1行
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from website_pv_info;
--当前行至最后一行
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from website_pv_info;
--第一行到最后一行 也就是分组内的所有行
select cookieid,createtime,pv,
sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and unbounded following) as pv6
from website_pv_info;
窗口排序函数
row_number
- 用于给每个分组内的数据打上排序的标号，注意窗口排序函数不支持窗口表达式
- row_number：在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复
- rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置
- dense_rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置
ntile
- 将每个分组内的数据分为指定的若干个桶里（分为若干个部分），并且为每一个桶分配一个桶编号
- 如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1
- 有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足
-- 窗口排序函数
SELECT
cookieid,
createtime,
pv,
RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3
FROM website_pv_info
WHERE cookieid = 'cookie1';
+-----------+-------------+-----+------+------+------+
| cookieid | createtime | pv | rn1 | rn2 | rn3 |
+-----------+-------------+-----+------+------+------+
| cookie1 | 2018-04-12 | 7 | 1 | 1 | 1 |
| cookie1 | 2018-04-11 | 5 | 2 | 2 | 2 |
| cookie1 | 2018-04-16 | 4 | 3 | 3 | 3 |
| cookie1 | 2018-04-15 | 4 | 3 | 3 | 4 |
| cookie1 | 2018-04-13 | 3 | 5 | 4 | 5 |
| cookie1 | 2018-04-14 | 2 | 6 | 5 | 6 |
| cookie1 | 2018-04-10 | 1 | 7 | 6 | 7 |
+-----------+-------------+-----+------+------+------+
--需求：找出每个用户访问pv最多的Top3 重复并列的不考虑
SELECT * from
(SELECT
cookieid,
createtime,
pv,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS seq
FROM website_pv_info) tmp where tmp.seq <4;
--把每个分组内的数据分为3桶
SELECT
cookieid,
createtime,
pv,
NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2
FROM website_pv_info
ORDER BY cookieid,createtime;
--需求：统计每个用户pv数最多的前3分之1天。
--理解：将数据根据cookieid分 根据pv倒序排序 排序之后分为3个部分 取第一部分
SELECT * from
(SELECT
cookieid,
createtime,
pv,
NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn
FROM website_pv_info) tmp where rn =1;
窗口分析函数
- LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
- 第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
- LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
- 第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
- FIRST_VALUE 取分组内排序后，截止到当前行，第一个值
- LAST_VALUE 取分组内排序后，截止到当前行，最后一个值
-----------窗口分析函数----------
--LAG
SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time
FROM website_url_info;
--LEAD
SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time
FROM website_url_info;
--FIRST_VALUE
SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1
FROM website_url_info;
--LAST_VALUE
SELECT cookieid,
createtime,
url,
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1
FROM website_url_info;
19-5.Sampling抽样函数
- 当数据量过大时，我们可能需要查找数据子集以加快数据处理速度分析
- 这就是抽样、采样，一种用于识别和分析数据中的子集的技术，以发现整个数据集中的模式和趋势
- 在HQL中，可以通过三种方式采样数据：
- 随机采样
- 存储桶表采样
- 块采样
Random 随机抽样
- 随机抽样使用rand（）函数来确保随机获取数据，LIMIT来限制抽取的数据个数
- 优点是随机，缺点是速度不快，尤其表数据多的时候
- 推荐DISTRIBUTE+SORT，可以确保数据也随机分布在mapper和reducer之间，使得底层执行有效率
- ORDER BY语句也可以达到相同的目的，但是表现不好，因为ORDER BY是全局排序，只会启动运行一个reducer
0: jdbc:hive2://ops01:10000> select * from student;
+--------------+---------------+--------------+--------------+---------------+
| student.num | student.name | student.sex | student.age | student.dept |
+--------------+---------------+--------------+--------------+---------------+
| 95001 | 李勇 | 男 | 20 | CS |
| 95002 | 刘晨 | 女 | 19 | IS |
| 95003 | 王敏 | 女 | 22 | MA |
| 95004 | 张立 | 男 | 19 | IS |
| 95005 | 刘刚 | 男 | 18 | MA |
| 95006 | 孙庆 | 男 | 23 | CS |
| 95007 | 易思玲 | 女 | 19 | MA |
| 95008 | 李娜 | 女 | 18 | CS |
| 95009 | 梦圆圆 | 女 | 18 | MA |
| 95010 | 孔小涛 | 男 | 19 | CS |
| 95011 | 包小柏 | 男 | 18 | MA |
| 95012 | 孙花 | 女 | 20 | CS |
| 95013 | 冯伟 | 男 | 21 | CS |
| 95014 | 王小丽 | 女 | 19 | CS |
| 95015 | 王君 | 男 | 18 | MA |
| 95016 | 钱国 | 男 | 21 | MA |
| 95017 | 王风娟 | 女 | 18 | IS |
| 95018 | 王一 | 女 | 19 | IS |
| 95019 | 邢小丽 | 女 | 19 | IS |
| 95020 | 赵钱 | 男 | 21 | IS |
| 95021 | 周二 | 男 | 17 | MA |
| 95022 | 郑明 | 男 | 20 | MA |
+--------------+---------------+--------------+--------------+---------------+
0: jdbc:hive2://ops01:10000> SELECT * FROM student DISTRIBUTE BY rand() SORT BY rand() LIMIT 2;
+--------------+---------------+--------------+--------------+---------------+
| student.num | student.name | student.sex | student.age | student.dept |
+--------------+---------------+--------------+--------------+---------------+
| 95013 | 冯伟 | 男 | 21 | CS |
| 95007 | 易思玲 | 女 | 19 | MA |
+--------------+---------------+--------------+--------------+---------------+
Block 基于数据块抽样
- Block块采样允许随机获取n行数据、百分比数据或指定大小的数据
- 采样粒度是HDFS块大小
- 优点是速度快，缺点是不随机
-- block抽样
--根据行数抽样
SELECT * FROM student TABLESAMPLE(1 ROWS);
--根据数据大小百分比抽样 25%
SELECT * FROM student TABLESAMPLE(25 PERCENT);
--根据数据大小抽样
--支持数据单位 b/B, k/K, m/M, g/G
SELECT * FROM student TABLESAMPLE(1k);
Bucket table 基于分桶表抽样
- 这是一种特殊的采样方法，针对分桶表进行了优化。优点是既随机速度也很快
-- 根据整行数据进行抽样
SELECT * FROM table_name TABLESAMPLE(BUCKET 1 OUT OF 5 ON rand());
-- 根据分桶字段进行抽样 效率更高
SELECT * FROM table_name TABLESAMPLE(BUCKET 1 OUT OF 5 ON state);
第20章:Hive中多字节分隔符处理
20-1.Hive中的分隔符
Hive默认序列化类是LazySimpleSerDe，其只支持使用单字节分隔符（char）来加载文本数据，例如逗号、制表符、空格等等，默认的分隔符为”\001”。根据不同文件的不同分隔符，我们可以通过在创建表时使用 row format delimited 来指定文件中的分割符，确保正确将表中的每一列与文件中的每一列实现一一对应的关系
20-2.分隔符问题与需求
实际使用场景中，会遇到获取的数据文件为多字节分隔符
特殊数据-情况1：每一行数据的分隔符是多字节分隔符，例如：”||”、“–”
singer.txt
01||周杰伦||中国||台湾||男||七里香
02||刘德华||中国||香港||男||笨小孩
03||汪 峰||中国||北京||男||光明
04||朴 树||中国||北京||男||那些花儿
05||许 巍||中国||陕西||男||故乡
06||张靓颖||中国||四川||女||画心
07||黄家驹||中国||香港||男||光辉岁月
08||周传雄||中国||台湾||男||青花
09||刘若英||中国||台湾||女||很爱很爱你
10||张 杰||中国||四川||男||天下
如果直接使用LazysimpleSerDe处理：
-- 建表
create table singer(
id string,
name string,
country string,
province string,
gender string,
works string)
row format delimited fields terminated by '||';
--加载数据
load data local inpath '/home/wangting/20221019/singer.txt' into table singer;
-- 查数验证
select * from singer;
+------------+--------------+-----------------+------------------+----------------+---------------+
| singer.id | singer.name | singer.country | singer.province | singer.gender | singer.works |
+------------+--------------+-----------------+------------------+----------------+---------------+
| 01 | | 周杰伦 | | 中国 | |
| 02 | | 刘德华 | | 中国 | |
| 03 | | 汪 峰 | | 中国 | |
| 04 | | 朴 树 | | 中国 | |
| 05 | | 许 巍 | | 中国 | |
| 06 | | 张靓颖 | | 中国 | |
| 07 | | 黄家驹 | | 中国 | |
| 08 | | 周传雄 | | 中国 | |
| 09 | | 刘若英 | | 中国 | |
| 10 | | 张 杰 | | 中国 | |
+------------+--------------+-----------------+------------------+----------------+---------------+
结论为如果直接使用LazysimpleSerDe处理指定”||“分割，会错列和数据缺失
特殊数据-情况2：数据的字段中恰巧也包含了分隔符
apachelog.log
192.168.88.134 [08/Nov/2020:10:44:32 +0800] "GET / HTTP/1.1" 404 951
192.168.88.100 [08/Nov/2020:10:44:33 +0800] "GET /hpsk_sdk/index.html HTTP/1.1" 200 328
192.168.88.134 [08/Nov/2020:20:19:06 +0800] "GET / HTTP/1.1" 404 951
192.168.88.100 [08/Nov/2020:20:19:13 +0800] "GET /hpsk_sdk/demo4.html HTTP/1.1" 200 982
192.168.88.100 [08/Nov/2020:20:19:13 +0800] "GET /hpsk_sdk/js/analytics.js HTTP/1.1" 200 11095
192.168.88.100 [08/Nov/2020:20:19:23 +0800] "GET /hpsk_sdk/demo3.html HTTP/1.1" 200 1024
192.168.88.100 [08/Nov/2020:20:19:26 +0800] "GET /hpsk_sdk/demo2.html HTTP/1.1" 200 854
192.168.88.100 [08/Nov/2020:20:19:27 +0800] "GET /hpsk_sdk/demo.html HTTP/1.1" 200 485
192.168.88.134 [08/Nov/2020:20:26:51 +0800] "GET / HTTP/1.1" 404 951
192.168.88.134 [08/Nov/2020:20:29:08 +0800] "GET / HTTP/1.1" 404 951
192.168.88.100 [08/Nov/2020:20:31:27 +0800] "GET /hpsk_sdk/demo5.html HTTP/1.1" 200 5333
192.168.88.100 [08/Nov/2020:20:32:59 +0800] "GET /hpsk_sdk/demo5.html HTTP/1.1" 200 5333
192.168.88.100 [08/Nov/2020:20:32:59 +0800] "GET /hpsk_sdk/js/analytics.js HTTP/1.1" 200 11082
192.168.88.100 [08/Nov/2020:20:32:59 +0800] "GET /favicon.ico HTTP/1.1" 404 973
192.168.88.100 [08/Nov/2020:20:33:01 +0800] "GET /hpsk_sdk/demo3.html HTTP/1.1" 200 1024
192.168.88.100 [08/Nov/2020:20:34:25 +0800] "GET /hpsk_sdk/demo2.html HTTP/1.1" 200 854
192.168.88.100 [08/Nov/2020:20:34:25 +0800] "GET /hpsk_sdk/js/analytics.js HTTP/1.1" 304 -
192.168.88.100 [08/Nov/2020:20:34:28 +0800] "GET /hpsk_sdk/demo4.html HTTP/1.1" 200 982
192.168.88.100 [08/Nov/2020:20:35:05 +0800] "GET /hpsk_sdk/demo.html HTTP/1.1" 200 485
192.168.88.100 [08/Nov/2020:20:35:05 +0800] "GET /hpsk_sdk/js/analytics.js HTTP/1.1" 304 -
[08/Nov/2020:20:19:13 +0800] 为时间信息，但是其中也包含了空格
0: jdbc:hive2://ops01:10000> create table apachelog( ip string,stime string,mothed string,url string,policy string,stat string,body string)
. . . . . . . . . . . . . .> row format delimited fields terminated by ' ';
No rows affected (0.075 seconds)
0: jdbc:hive2://ops01:10000> load data local inpath '/home/wangting/20221019/apachelog.log' into table apachelog;
No rows affected (0.163 seconds)
0: jdbc:hive2://ops01:10000> select t.* from apachelog t limit 2;
+-----------------+------------------------+-----------+--------+-----------------------+------------+---------+
| t.ip | t.stime | t.mothed | t.url | t.policy | t.stat | t.body |
+-----------------+------------------------+-----------+--------+-----------------------+------------+---------+
| 192.168.88.134 | [08/Nov/2020:10:44:32 | +0800] | "GET | / | HTTP/1.1" | 404 |
| 192.168.88.100 | [08/Nov/2020:10:44:33 | +0800] | "GET | /hpsk_sdk/index.html | HTTP/1.1" | 200 |
+-----------------+------------------------+-----------+--------+-----------------------+------------+---------+
结论为如果直接使用LazysimpleSerDe处理指定”||“分割，同样也会错列和数据缺失
20-3.分隔符问题解决方案一：替换分隔符
wangting@ops01:/home/wangting/20221019 >cat singer.txt
01||周杰伦||中国||台湾||男||七里香
02||刘德华||中国||香港||男||笨小孩
03||汪 峰||中国||北京||男||光明
04||朴 树||中国||北京||男||那些花儿
05||许 巍||中国||陕西||男||故乡
06||张靓颖||中国||四川||女||画心
07||黄家驹||中国||香港||男||光辉岁月
08||周传雄||中国||台湾||男||青花
09||刘若英||中国||台湾||女||很爱很爱你
10||张 杰||中国||四川||男||天下
wangting@ops01:/home/wangting/20221019 >sed -i 's/||/|/g' singer.txt
wangting@ops01:/home/wangting/20221019 >cat singer.txt
01|周杰伦|中国|台湾|男|七里香
02|刘德华|中国|香港|男|笨小孩
03|汪 峰|中国|北京|男|光明
04|朴 树|中国|北京|男|那些花儿
05|许 巍|中国|陕西|男|故乡
06|张靓颖|中国|四川|女|画心
07|黄家驹|中国|香港|男|光辉岁月
08|周传雄|中国|台湾|男|青花
09|刘若英|中国|台湾|女|很爱很爱你
10|张 杰|中国|四川|男|天下
1234567891011121314151617181920212223
create table singer_new(
id string,
name string,
country string,
province string,
gender string,
works string)
row format delimited fields terminated by '|';
load data local inpath '/home/wangting/20221019/singer.txt' into table singer_new;
select t.* from singer_new t limit 2;
+-------+---------+------------+-------------+-----------+----------+
| t.id | t.name | t.country | t.province | t.gender | t.works |
+-------+---------+------------+-------------+-----------+----------+
| 01 | 周杰伦 | 中国 | 台湾 | 男 | 七里香 |
| 02 | 刘德华 | 中国 | 香港 | 男 | 笨小孩 |
+-------+---------+------------+-------------+-----------+----------+
20-4.分隔符问题解决方案二：RegexSerDe正则加载
Hive内置的SerDe
除了使用最多的LazySimpleSerDe，Hive该内置了很多SerDe类
多种SerDe用于解析和加载不同类型的数据文件，常用的有ORCSerDe 、RegexSerDe、JsonSerDe等
RegexSerDe用来加载特殊数据的问题，使用正则匹配来加载数据
--创建表
create table singer_reg(id string,
name string,
country string,
province string,
gender string,
works string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES ("input.regex" = "([0-9]*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)\\|\\|(.*)");
-- 注意将文件分隔符恢复至||用来测试
wangting@ops01:/home/wangting/20221019 >cat singer.txt
01||周杰伦||中国||台湾||男||七里香
02||刘德华||中国||香港||男||笨小孩
03||汪 峰||中国||北京||男||光明
04||朴 树||中国||北京||男||那些花儿
05||许 巍||中国||陕西||男||故乡
06||张靓颖||中国||四川||女||画心
07||黄家驹||中国||香港||男||光辉岁月
08||周传雄||中国||台湾||男||青花
09||刘若英||中国||台湾||女||很爱很爱你
10||张 杰||中国||四川||男||天下
--加载数据
load data local inpath '/home/wangting/20221019/singer.txt' into table singer_reg;
-- 查询验证
select t.* from singer_reg t limit 2;
+-------+---------+------------+-------------+-----------+----------+
| t.id | t.name | t.country | t.province | t.gender | t.works |
+-------+---------+------------+-------------+-----------+----------+
| 01 | 周杰伦 | 中国 | 台湾 | 男 | 七里香 |
| 02 | 刘德华 | 中国 | 香港 | 男 | 笨小孩 |
+-------+---------+------------+-------------+-----------+----------+
20-5.分隔符问题解决方案三：自定义InputFormat
- Hive中也允许使用自定义InputFormat来解决以上问题，通过在自定义InputFormat，来自定义解析逻辑实现读取每一行的数据
- 与MapReudce中自定义InputFormat一致，继承TextInputFormat
写个方法打包上传，add jar添加到hive的环境变量中
20-6.分隔符处理总结
当数据文件中出现多字节分隔符或者数据中包含了分隔符时，会导致数据加载与实际表的字段不匹配的问题
三种方案：
- 替换分隔符
- 正则匹配加载RegexSerde
- 自定义InputFormat
其中替换分隔符无法解决数据字段中依然存在分隔符的问题，自定义InputFormat的开发成本较高，所以整体推荐使用正则加载的方式来实现对于特殊数据的处理
第21章:URL解析函数
21-1.Hive中的URL解析函数
- Hive中为了实现对URL的解析，专门提供了解析URL的函数parse_url和parse_url_tuple
- parse_url
- parse_url函数是Hive中提供的最基本的url解析函数，可以根据指定的参数，从URL解析出对应的参数值进行返回，函数为普通的一对一函数类型
- 语法：parse_url(url, partToExtract[, key]) - extracts a part from a URL
- parse_url_tuple
- parse_url
0: jdbc:hive2://ops01:10000> show functions like '*parse*';
+------------------+
| tab_name |
+------------------+
| parse_url |
| parse_url_tuple |
+------------------+
SELECT parse_url('http://facebook.com/path/p1.php?id=10086', 'HOST');
+---------------+
| _c0 |
+---------------+
| facebook.com |
+---------------+
SELECT parse_url('http://facebook.com/path/p1.php?id=10086&name=allen', 'QUERY') ;
+----------------------+
| _c0 |
+----------------------+
| id=10086&name=allen |
+----------------------+
SELECT parse_url('http://facebook.com/path/p1.php?id=10086&name=allen', 'QUERY', 'name') ;
+--------+
| _c0 |
+--------+
| allen |
+--------+
parse_url的弊端：
如果想一次解析多个参数，需要使用多次函数
因此需要有parse_url_tuple函数
parse_url_tuple函数是Hive中提供的基于parse_url的url解析函数，可以通过一次指定多个参数，从URL解析出多个参数的值进行返回多列，函数为特殊的一对多函数类型，即通常所说的UDTF函数类型
语法：parse_url_tuple(url, partname1, partname2, …, partnameN) - extracts N (N>=1) parts from a URL
示例：
背景：从URL中获取每个ID对应HOST、PATH以及QUERY
url.txt
1 http://facebook.com/path/p1.php?query=1
2 http://tongji.baidu.com/news/index.jsp?uuid=allen&age=18
3 http://www.jdwz.com/index?source=baidu
4 http://www.itcast.cn/index?source=alibaba
1234
--建表
create table tb_url(
id int,
url string
)row format delimited
fields terminated by '\t';
--加载数据
load data local inpath '/home/wangting/20221019/url.txt' into table tb_url;
select * from tb_url;
+------------+----------------------------------------------------+
| tb_url.id | tb_url.url |
+------------+----------------------------------------------------+
| 1 | http://facebook.com/path/p1.php?query=1 |
| 2 | http://tongji.baidu.com/news/index.jsp?uuid=allen&age=18 |
| 3 | http://www.jdwz.com/index?source=baidu |
| 4 | http://www.itcast.cn/index?source=alibaba |
+------------+----------------------------------------------------+
select parse_url_tuple(url,"HOST","PATH") as (host,path) from tb_url;
+-------------------+------------------+
| host | path |
+-------------------+------------------+
| facebook.com | /path/p1.php |
| tongji.baidu.com | /news/index.jsp |
| www.jdwz.com | /index |
| www.itcast.cn | /index |
+-------------------+------------------+
select parse_url_tuple(url,"PROTOCOL","HOST","PATH") as (protocol,host,path) from tb_url;
+-----------+-------------------+------------------+
| protocol | host | path |
+-----------+-------------------+------------------+
| http | facebook.com | /path/p1.php |
| http | tongji.baidu.com | /news/index.jsp |
| http | www.jdwz.com | /index |
| http | www.itcast.cn | /index |
+-----------+-------------------+------------------+
select parse_url_tuple(url,"HOST","PATH","QUERY") as (host,path,query) from tb_url;
+-------------------+------------------+--------------------+
| host | path | query |
+-------------------+------------------+--------------------+
| facebook.com | /path/p1.php | query=1 |
| tongji.baidu.com | /news/index.jsp | uuid=allen&age=18 |
| www.jdwz.com | /index | source=baidu |
| www.itcast.cn | /index | source=alibaba |
+-------------------+------------------+--------------------+
21-2.Lateral View侧视图
Hive中的一对多的UDTF函数可以实现高效的数据转换，但是也存在着一些使用中的问题，UDTF函数对于很多场景下有使用限制，例如：select时不能包含其他字段、不能嵌套调用、不能与group by等放在一起调用等等
UDTF函数的调用方式，主要有以下两种方式
- 方式一：直接在select后单独使用
- 方式二：与Lateral View放在一起使用
-- parse_url_tuple
-- select id,parse_url_tuple(url,"HOST","PATH","QUERY") as (host,path,query) from tb_url;
-- 如果需要实现带id查询需要引用lateral view一同使用
select
a.id as id,
b.host as host,
b.path as path,
b.query as query
from tb_url a lateral view parse_url_tuple(url,"HOST","PATH","QUERY") b as host,path,query;
+-----+-------------------+------------------+--------------------+
| id | host | path | query |
+-----+-------------------+------------------+--------------------+
| 1 | facebook.com | /path/p1.php | query=1 |
| 2 | tongji.baidu.com | /news/index.jsp | uuid=allen&age=18 |
| 3 | www.jdwz.com | /index | source=baidu |
| 4 | www.itcast.cn | /index | source=alibaba |
+-----+-------------------+------------------+--------------------+
--多个lateral view
select
a.id as id,
b.host as host,
b.path as path,
c.protocol as protocol,
c.query as query
from tb_url a
lateral view parse_url_tuple(url,"HOST","PATH") b as host,path
lateral view parse_url_tuple(url,"PROTOCOL","QUERY") c as protocol,query;
+-----+-------------------+------------------+-----------+--------------------+
| id | host | path | protocol | query |
+-----+-------------------+------------------+-----------+--------------------+
| 1 | facebook.com | /path/p1.php | http | query=1 |
| 2 | tongji.baidu.com | /news/index.jsp | http | uuid=allen&age=18 |
| 3 | www.jdwz.com | /index | http | source=baidu |
| 4 | www.itcast.cn | /index | http | source=alibaba |
+-----+-------------------+------------------+-----------+--------------------+
第22章:行列转换应用与实现
22-1.行列转化工作应用场景
实际应用场景中会遇到表中的数据存储格式不利于直接查询展示，需要进行调整
例如访问数据，每小时的各数据情况；而用户希望看到的为右边的指标，每个监控项随着时间推移的每小时数据情况。
22-2.行转列：多行转多列
背景：实现多行转多列
wangting@ops01:/home/wangting/20221020 >pwd
/home/wangting/20221020
wangting@ops01:/home/wangting/20221020 >cat r2c1.txt
a c 1
a d 2
a e 3
b c 4
b d 5
b e 6
123456789
--建表
create table row2col1(
col1 string,
col2 string,
col3 int
) row format delimited fields terminated by '\t';
--加载数据到表中
load data local inpath '/home/wangting/20221020/r2c1.txt' into table row2col1;
select * from row2col1;
+----------------+----------------+----------------+
| row2col1.col1 | row2col1.col2 | row2col1.col3 |
+----------------+----------------+----------------+
| a | c | 1 |
| a | d | 2 |
| a | e | 3 |
| b | c | 4 |
| b | d | 5 |
| b | e | 6 |
+----------------+----------------+----------------+
--sql最终实现
select
col1 as col1,
max(case col2 when 'c' then col3 else 0 end) as c,
max(case col2 when 'd' then col3 else 0 end) as d,
max(case col2 when 'e' then col3 else 0 end) as e
from
row2col1
group by
col1;
+-------+----+----+----+
| col1 | c | d | e |
+-------+----+----+----+
| a | 1 | 2 | 3 |
| b | 4 | 5 | 6 |
+-------+----+----+----+
22-3.行转列：多行转单列
r2c2.txt
a b 1
a b 2
a b 3
c d 4
c d 5
c d 6
123456
select * from row2col1;
+----------------+----------------+----------------+
| row2col1.col1 | row2col1.col2 | row2col1.col3 |
+----------------+----------------+----------------+
| a | c | 1 |
| a | d | 2 |
| a | e | 3 |
| b | c | 4 |
| b | d | 5 |
| b | e | 6 |
+----------------+----------------+----------------+
-- collect_list效果展示
select collect_list(col1) from row2col1;
+----------------------------+
| _c0 |
+----------------------------+
| ["a","a","a","b","b","b"] |
+----------------------------+
-- collect_set效果展示
select collect_set(col1) from row2col1;
+------------+
| _c0 |
+------------+
| ["a","b"] |
+------------+
--建row2col2表
create table row2col2(
col1 string,
col2 string,
col3 int
)row format delimited fields terminated by '\t';
--加载数据到表中
load data local inpath '/home/wangting/20221020/r2c2.txt' into table row2col2;
select * from row2col2;
+----------------+----------------+----------------+
| row2col2.col1 | row2col2.col2 | row2col2.col3 |
+----------------+----------------+----------------+
| a | b | 1 |
| a | b | 2 |
| a | b | 3 |
| c | d | 4 |
| c | d | 5 |
| c | d | 6 |
+----------------+----------------+----------------+
--最终SQL实现
select
col1,
col2,
concat_ws(',', collect_list(cast(col3 as string))) as col3
from
row2col2
group by
col1, col2;
+-------+-------+--------+
| col1 | col2 | col3 |
+-------+-------+--------+
| a | b | 1,2,3 |
| c | d | 4,5,6 |
+-------+-------+--------+
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263
22-4.列转行：多列转多行
c2r1.txt
a 1 2 3
b 4 5 6
12
--创建表
create table col2row1
(
col1 string,
col2 int,
col3 int,
col4 int
) row format delimited fields terminated by '\t';
--加载数据
load data local inpath '/home/wangting/20221020/c2r1.txt' into table col2row1;
select * from col2row1;
+----------------+----------------+----------------+----------------+
| col2row1.col1 | col2row1.col2 | col2row1.col3 | col2row1.col4 |
+----------------+----------------+----------------+----------------+
| a | 1 | 2 | 3 |
| b | 4 | 5 | 6 |
+----------------+----------------+----------------+----------------+
--最终实现
select col1, 'c' as col2, col2 as col3 from col2row1
UNION ALL
select col1, 'd' as col2, col3 as col3 from col2row1
UNION ALL
select col1, 'e' as col2, col4 as col3 from col2row1;
+-----------+-----------+-----------+
| _u1.col1 | _u1.col2 | _u1.col3 |
+-----------+-----------+-----------+
| a | c | 1 |
| a | d | 2 |
| a | e | 3 |
| b | c | 4 |
| b | d | 5 |
| b | e | 6 |
+-----------+-----------+-----------+
22-5.列转行：单列转多行
c2r2.txt
a b 1,2,3
c d 4,5,6
12
-- 创建col2row2表
create table col2row2(
col1 string,
col2 string,
col3 string
)row format delimited fields terminated by '\t';
-- 加载数据
load data local inpath '/home/wangting/20221020/c2r2.txt' into table col2row2;
select * from col2row2;
+----------------+----------------+----------------+
| col2row2.col1 | col2row2.col2 | col2row2.col3 |
+----------------+----------------+----------------+
| a | b | 1,2,3 |
| c | d | 4,5,6 |
+----------------+----------------+----------------+
select explode(split(col3,',')) from col2row2;
+------+
| col |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+------+
-- SQL最终实现
select
col1,
col2,
lv.col3 as col3
from
col2row2
lateral view
explode(split(col3, ',')) lv as col3;
+-------+-------+-------+
| col1 | col2 | col3 |
+-------+-------+-------+
| a | b | 1 |
| a | b | 2 |
| a | b | 3 |
| c | d | 4 |
| c | d | 5 |
| c | d | 6 |
+-------+-------+-------+
第23章:JSON数据处理
23-1.json数据处理使用场景
JSON数据格式是数据存储及数据处理中最常见的结构化数据格式之一，很多场景下公司都会将数据以JSON格式存储在HDFS中，当构建数据仓库时，需要对JSON格式的数据进行处理和分析，那么就需要在Hive中对JSON格式的数据进行解析读取
对以下JSON数据实现处理，解析每个字段到表中
23-2.json数据处理方式
Hive中为了实现JSON格式的数据解析，提供了两种解析JSON数据的方式，在实际工作场景下，可以根据不同数据，不同的需求来选择合适的方式对JSON格式数据进行处理
- 使用JSON函数处理
- get_json_object、json_tuple
- 这两个函数都可以实现将JSON数据中的每个字段独立解析出来，构建成表
- get_json_object、json_tuple
- JSON Serde加载数据
- JSON Serde
- 建表时指定Serde，加载JSON文件到表中，会自动解析为对应的表格式
- JSON Serde
23-3.JSON函数：get_json_object
get_json_object
- 功能
- 用于解析JSON字符串，可以从JSON字符串中返回指定的某个对象列的值
- 语法
- get_json_object(json_txt, path) - Extract a json object from path
- 参数
- 第一个参数：指定要解析的JSON字符串
- 第二个参数：指定要返回的字段，通过$.columnName的方式来指定path
- 特点
- 每次只能返回JSON对象中一列的值
案例测试：
数据文件device.json
{"device":"device_30","deviceType":"kafka","signal":98.0,"time":1616817201390}
{"device":"device_40","deviceType":"route","signal":99.0,"time":1616817201887}
{"device":"device_21","deviceType":"bigdata","signal":77.0,"time":1616817202142}
{"device":"device_31","deviceType":"kafka","signal":98.0,"time":1616817202405}
{"device":"device_20","deviceType":"bigdata","signal":12.0,"time":1616817202513}
{"device":"device_54","deviceType":"bigdata","signal":14.0,"time":1616817202913}
{"device":"device_10","deviceType":"db","signal":39.0,"time":1616817203356}
{"device":"device_94","deviceType":"bigdata","signal":59.0,"time":1616817203771}
{"device":"device_32","deviceType":"kafka","signal":52.0,"time":1616817204010}
{"device":"device_21","deviceType":"bigdata","signal":85.0,"time":1616817204229}
12345678910
--get_json_object
--创建表
create table tb_json_test1 (
json string
);
--加载数据
load data local inpath '/home/wangting/20221020/device.json' into table tb_json_test1;
select * from tb_json_test1;
| {"device":"device_30","deviceType":"kafka","signal":98.0,"time":1616817201390} |
| {"device":"device_40","deviceType":"route","signal":99.0,"time":1616817201887} |
| {"device":"device_21","deviceType":"bigdata","signal":77.0,"time":1616817202142} |
| {"device":"device_31","deviceType":"kafka","signal":98.0,"time":1616817202405} |
| {"device":"device_20","deviceType":"bigdata","signal":12.0,"time":1616817202513} |
| {"device":"device_54","deviceType":"bigdata","signal":14.0,"time":1616817202913} |
| {"device":"device_10","deviceType":"db","signal":39.0,"time":1616817203356} |
| {"device":"device_94","deviceType":"bigdata","signal":59.0,"time":1616817203771} |
| {"device":"device_32","deviceType":"kafka","signal":52.0,"time":1616817204010} |
| {"device":"device_21","deviceType":"bigdata","signal":85.0,"time":1616817204229} |
select
get_json_object(json,"$.device") as device,
get_json_object(json,"$.deviceType") as deviceType,
get_json_object(json,"$.signal") as signal,
get_json_object(json,"$.time") as stime
from tb_json_test1;
+------------+-------------+---------+----------------+
| device | devicetype | signal | stime |
+------------+-------------+---------+----------------+
| device_30 | kafka | 98.0 | 1616817201390 |
| device_40 | route | 99.0 | 1616817201887 |
| device_21 | bigdata | 77.0 | 1616817202142 |
| device_31 | kafka | 98.0 | 1616817202405 |
| device_20 | bigdata | 12.0 | 1616817202513 |
| device_54 | bigdata | 14.0 | 1616817202913 |
| device_10 | db | 39.0 | 1616817203356 |
| device_94 | bigdata | 59.0 | 1616817203771 |
| device_32 | kafka | 52.0 | 1616817204010 |
| device_21 | bigdata | 85.0 | 1616817204229 |
+------------+-------------+---------+----------------+
23-4.JSON函数：json_tuple
json_tuple
- 功能
- 用于实现JSON字符串的解析，可以通过指定多个参数来解析JSON返回多列的值
- 语法
- json_tuple(jsonStr, p1, p2, …, pn) like get_json_object, but it takes multiple names and return a tuple
- 参数
- 第一个参数：指定要解析的JSON字符串
- 第二个参数：指定要返回的第1个字段
- …
- 第N+1个参数：指定要返回的第N个字段
- 特点
- 功能类似于get_json_object，但是可以调用一次返回多列的值，属于UDTF类型函数，一般搭配lateral view使用
- 返回的每一列都是字符串类型
案例测试：
--单独使用
select
json_tuple(json,"device","deviceType","signal","time") as (device,deviceType,signal,stime)
from tb_json_test1;
--搭配侧视图使用
select
json,device,deviceType,signal,stime
from tb_json_test1
lateral view json_tuple(json,"device","deviceType","signal","time") b
as device,deviceType,signal,stime;
搭配侧视图使用
23-5.JSONSerde
功能
上述解析JSON的过程中是将数据作为一个JSON字符串加载到表中，再通过JSON解析函数对JSON字符串进行解析，灵活性比较高，但是对于如果整个文件就是一个JSON文件，在使用起来就相对比较麻烦。
Hive中为了简化对于JSON文件的处理，内置了一种专门用于解析JSON文件的Serde解析器，在创建表时，只要指定使用JSONSerde解析表的文件，就会自动将JSON文件中的每一列进行解析。
--JsonSerDe
--创建表
create table tb_json_test2 (
device string,
deviceType string,
signal double,
`time` string
)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;
load data local inpath '/home/wangting/20221020/device.json' into table tb_json_test2;
select * from tb_json_test2;
+-----------------------+---------------------------+-----------------------+---------------------+
| tb_json_test2.device | tb_json_test2.devicetype | tb_json_test2.signal | tb_json_test2.time |
+-----------------------+---------------------------+-----------------------+---------------------+
| device_30 | kafka | 98.0 | 1616817201390 |
| device_40 | route | 99.0 | 1616817201887 |
| device_21 | bigdata | 77.0 | 1616817202142 |
| device_31 | kafka | 98.0 | 1616817202405 |
| device_20 | bigdata | 12.0 | 1616817202513 |
| device_54 | bigdata | 14.0 | 1616817202913 |
| device_10 | db | 39.0 | 1616817203356 |
| device_94 | bigdata | 59.0 | 1616817203771 |
| device_32 | kafka | 52.0 | 1616817204010 |
| device_21 | bigdata | 85.0 | 1616817204229 |
+-----------------------+---------------------------+-----------------------+---------------------+
23-6.json函数使用总结
不论是Hive中的JSON函数还是自带的JSONSerde，都可以实现对于JSON数据的解析，工作中一般根据数据格式以及对应的需求来实现解析。如果数据中每一行只有个别字段是JSON格式字符串，就可以使用JSON函数来实现处理，但是如果数据加载的文件整体就是JSON文件，每一行数据就是一个JSON数据，那么建议直接使用JSONSerde来实现处理最为方便
第24章:拉链表的设计与实现
24-1.数据同步问题
- Hive在实际工作中主要用于构建离线数据仓库，定期的从各种数据源中同步采集数据到Hive中，经过分层转换提供数据应用
- 例如每天需要从MySQL中同步最新的订单信息、用户信息、店铺信息等到数据仓库中，进行订单分析、用户分析
背景：
- MySQL中有一张用户表：tb_user，每个用户注册完成以后，就会在用户表中新增该用户的信息，记录该用户的id、手机号码、用户名、性别、地址等信息
- 每天都会有用户注册，产生新的用户信息
- 每天都需要将MySQL中的用户数据同步到Hive数据仓库中
- 需要对用户的信息做统计分析，例如统计新增用户的个数、用户性别分布、地区分布、运营商分布等指标
新增的数据会直接加载到Hive表中，但是更新的数据如何存储在Hive表
方案1：在Hive中用新的表覆盖的老的表，直接全量更新
- 优点：实现最简单，使用起来最方便
- 缺点：没有历史状态
方案二：每次数据改变，根据日期构建一份新全量的快照表，每天一张表
- 优点：记录了所有数据在不同时间的状态
- 缺点：冗余存储了很多没有发生变化的数据，导致存储的数据量过大
方案三：构建拉链表，通过时间标记发生变化的数据的每种状态的时间周期
24-2.拉链表的设计
- 拉链表专门用于解决在数据仓库中数据发生变化如何实现数据存储的问题
- 拉链表的设计是将更新的数据进行状态记录，没有发生更新的数据不进行状态存储，用于存储所有数据在不同时间上的所有状态，通过时间进行标记每个状态的生命周期，查询时，根据需求可以获取指定时间范围状态的数据，默认用9999-12-31等最大值来表示最新状态
拉链表的实现过程：
Step1：增量采集变化数据，放入增量表中
Step2：将Hive中的拉链表与临时表的数据进行合并，合并结果写入临时表
Step3：将临时表的数据覆盖写入拉链表中
24-3.拉链表的实现
案例测试：
zipper.txt
001 186xxxx1234 laoda 0 sh 2021-01-01 9999-12-31
002 186xxxx1235 laoer 1 bj 2021-01-01 9999-12-31
003 186xxxx1236 laosan 0 sz 2021-01-01 9999-12-31
004 186xxxx1237 laosi 1 gz 2021-01-01 9999-12-31
005 186xxxx1238 laowu 0 sh 2021-01-01 9999-12-31
006 186xxxx1239 laoliu 1 bj 2021-01-01 9999-12-31
007 186xxxx1240 laoqi 0 sz 2021-01-01 9999-12-31
008 186xxxx1241 laoba 1 gz 2021-01-01 9999-12-31
009 186xxxx1242 laojiu 0 sh 2021-01-01 9999-12-31
010 186xxxx1243 laoshi 1 bj 2021-01-01 9999-12-31
12345678910
update.txt
008 186xxxx1241 laoba 1 sh 2021-01-02 9999-12-31
011 186xxxx1244 laoshi 1 jx 2021-01-02 9999-12-31
012 186xxxx1245 laoshi 0 zj 2021-01-02 9999-12-31
123
--1、建表加载数据
--创建拉链表
create table dw_zipper(
userid string,
phone string,
nick string,
gender int,
addr string,
starttime string,
endtime string
) row format delimited fields terminated by '\t';
-- 数据加载
load data local inpath '/home/wangting/20221020/zipper.txt' into table dw_zipper;
-- 查询
select userid,nick,addr,starttime,endtime from dw_zipper;
+---------+---------+-------+-------------+-------------+
| userid | nick | addr | starttime | endtime |
+---------+---------+-------+-------------+-------------+
| 001 | laoda | sh | 2021-01-01 | 9999-12-31 |
| 002 | laoer | bj | 2021-01-01 | 9999-12-31 |
| 003 | laosan | sz | 2021-01-01 | 9999-12-31 |
| 004 | laosi | gz | 2021-01-01 | 9999-12-31 |
| 005 | laowu | sh | 2021-01-01 | 9999-12-31 |
| 006 | laoliu | bj | 2021-01-01 | 9999-12-31 |
| 007 | laoqi | sz | 2021-01-01 | 9999-12-31 |
| 008 | laoba | gz | 2021-01-01 | 9999-12-31 |
| 009 | laojiu | sh | 2021-01-01 | 9999-12-31 |
| 010 | laoshi | bj | 2021-01-01 | 9999-12-31 |
+---------+---------+-------+-------------+-------------+
-- 创建ods层增量表 加载数据
create table ods_zipper_update(
userid string,
phone string,
nick string,
gender int,
addr string,
starttime string,
endtime string
) row format delimited fields terminated by '\t';
load data local inpath '/home/wangting/20221020/update.txt' into table ods_zipper_update;
select t.* from ods_zipper_update t;
+-----------+--------------+---------+-----------+---------+--------------+-------------+
| t.userid | t.phone | t.nick | t.gender | t.addr | t.starttime | t.endtime |
+-----------+--------------+---------+-----------+---------+--------------+-------------+
| 008 | 186xxxx1241 | laoba | 1 | sh | 2021-01-02 | 9999-12-31 |
| 011 | 186xxxx1244 | laoshi | 1 | jx | 2021-01-02 | 9999-12-31 |
| 012 | 186xxxx1245 | laoshi | 0 | zj | 2021-01-02 | 9999-12-31 |
+-----------+--------------+---------+-----------+---------+--------------+-------------+
--创建临时表
create table tmp_zipper(
userid string,
phone string,
nick string,
gender int,
addr string,
starttime string,
endtime string
) row format delimited fields terminated by '\t';
-- 合并拉链表与增量表
insert overwrite table tmp_zipper
select
userid,
phone,
nick,
gender,
addr,
starttime,
endtime
from ods_zipper_update
union all
select
a.userid,
a.phone,
a.nick,
a.gender,
a.addr,
a.starttime,
if(b.userid is null or a.endtime < '9999-12-31', a.endtime , date_sub(b.starttime,1)) as endtime
from dw_zipper a left join ods_zipper_update b
on a.userid = b.userid ;
-- 覆盖拉链表
insert overwrite table dw_zipper select * from tmp_zipper;
select userid,nick,addr,starttime,endtime from dw_zipper;
+---------+---------+-------+-------------+-------------+
| userid | nick | addr | starttime | endtime |
+---------+---------+-------+-------------+-------------+
| 001 | laoda | sh | 2021-01-01 | 9999-12-31 |
| 002 | laoer | bj | 2021-01-01 | 9999-12-31 |
| 003 | laosan | sz | 2021-01-01 | 9999-12-31 |
| 004 | laosi | gz | 2021-01-01 | 9999-12-31 |
| 005 | laowu | sh | 2021-01-01 | 9999-12-31 |
| 006 | laoliu | bj | 2021-01-01 | 9999-12-31 |
| 007 | laoqi | sz | 2021-01-01 | 9999-12-31 |
| 008 | laoba | gz | 2021-01-01 | 2021-01-01 |
| 009 | laojiu | sh | 2021-01-01 | 9999-12-31 |
| 010 | laoshi | bj | 2021-01-01 | 9999-12-31 |
| 008 | laoba | sh | 2021-01-02 | 9999-12-31 |
| 011 | laoshi | jx | 2021-01-02 | 9999-12-31 |
| 012 | laoshi | zj | 2021-01-02 | 9999-12-31 |
+---------+---------+-------+-------------+-------------+
第25章:Hive表设计优化
25-1.分区表结构设计
Hive查询基本原理
- Hive的设计思想是通过元数据解析描述将HDFS上的文件映射成表
- 基本的查询原理是当用户通过HQL语句对Hive中的表进行复杂数据处理和计算时，默认将其转换为分布式计算MapReduce程序对HDFS中的数据进行读取处理的过程
- 当执行查询计划时，Hive会使用表的最后一级目录作为底层处理数据的输入
Step1：先根据表名在元数据中进行查询表对应的HDFS目录
Step2：然后将整个HDFS中表的目录作为底层查询的输入，可以通过explain命令查看执行计划依赖的数据
普通表结构问题
- 假设每天有1G的数据增量，一年就是365GB的数据，按照业务需求，每次只需要对其中一天的数据进行处理，也就是处理1GB的数据
- 程序会先加载365GB的数据，然后将364GB的数据过滤掉，只保留一天的数据再进行计算，导致了大量的磁盘和网络的IO的损耗
分区表结构-分区设计思想
- Hive提供了一种特殊的表结构来解决——分区表结构。分区表结构的设计思想是：根据查询的需求，将数据按照查询的条件【一般以时间】进行划分分区存储，将不同分区的数据单独使用一个HDFS目录来进行存储，当底层实现计算时，根据查询的条件，只读取对应分区的数据作为输入，减少不必要的数据加载，提高程序的性能
25-2.分桶表结构设计
Hive中Join的问题
- 默认情况下，Hive底层是通过MapReduce来实现的
- MapReduce在处理数据之间join的时候有两种方式：MapJoin、ReduceJoin，其中MapJoin效率较高
- 如果有两张非常大的表要进行Join，底层无法使用MapJoin提高Join的性能，只能走默认的ReduceJoin
- 而ReduceJoin必须经过Shuffle过程，相对性能比较差，而且容易产生数据倾斜
分桶表设计思想
- 分区表是将数据划分不同的目录进行存储，而分桶表是将数据划分不同的文件进行存储
- 分桶表的设计是按照一定的规则，底层通过MapReduce中的多个Reduce来实现，将数据划分到不同的文件中进行存储，构建分桶表
- 如果有两张表按照相同的划分规则，比如按照Join的关联字段，将各自的数据进行划分
- 在Join时，就可以实现Bucket与Bucket的Join，避免不必要的比较，减少笛卡尔积数量
第26章:Hive表数据优化
26-1.文件格式
文件格式-概述
- Hive数据存储的本质还是HDFS，所有的数据读写都基于HDFS的文件来实现
- 为了提高对HDFS文件读写的性能，Hive提供了多种文件存储格式：
- TextFile
- SequenceFile
- ORC
- Parquet
- 不同的文件存储格式具有不同的存储特点，有的可以降低存储空间，有的可以提高查询性能
- Hive的文件格式在建表时STORED AS file_format指定，默认是TextFile
文件格式-TextFile
- TextFile是Hive中默认的文件格式，存储形式为按行存储
- 工作中最常见的数据文件格式就是TextFile文件，几乎所有的原始数据生成都是TextFile格式，所以Hive设计时考虑到为了避免各种编码及数据错乱的问题，选用了TextFile作为默认的格式
- 建表时不指定存储格式即为TextFile，导入数据时把数据文件拷贝至HDFS不进行处理
| 优点 | 缺点 | 应用场景 |
|---|---|---|
| 最简单的数据格式，可以直接查看可以使用任意的分隔符进行分割便于和其他工具共享数据可以搭配压缩一起使用 | 耗费存储空间，I/O性能较低结合压缩时Hive不进行数据切分合并，不能进行并行操作，查询效率低按行存储，读取列的性能差 | 适合于小量数据的存储查询一般用于做第一层数据加载和测试使用 |
文件格式-SequenceFile
- SequenceFile是Hadoop里用来存储序列化的键值对即二进制的一种文件格式
- SequenceFile文件也可以作为MapReduce作业的输入和输出，hive也支持这种格式
| 优点 | 缺点 | 应用场景 |
|---|---|---|
| 以二进制的KV形式存储数据与底层交互更加友好，性能更快可压缩、可分割，优化磁盘利用率和I/O可并行操作数据，查询效率高也可以用于存储多个小文件 | 存储空间消耗最大与非Hadoop生态系统之外的工具不兼容构建SequenceFile需要通过TextFile文件转化加载 | 适合于小量数据，但是查询列比较多的场景 |
文件格式-Parquet
- Parquet是一种支持嵌套结构的列式存储文件格式，有着高效的数据编码和压缩
- 是一种支持嵌套数据模型对的列式存储系统，作为大数据系统中OLAP查询的优化方案，它已经被多种查询引擎原生支持，并且部分高性能引擎将其作为默认的文件存储格式
- 通过数据编码和压缩，以及映射下推和谓词下推功能，Parquet的性能也较之其它文件格式有所提升
| 优点 | 缺点 | 应用场景 |
|---|---|---|
| 更高效的压缩和编码可压缩、可分割，优化磁盘利用率和I/O可用于多种数据处理框架 | 不支持update, insert, delete, ACID | 适用于字段数非常多，无更新，只取部分列的查询 |
文件格式-ORC
- ORC（OptimizedRC File）文件格式也是一种Hadoop生态圈中的列式存储格式
- 用于降低Hadoop数据存储空间和加速Hive查询速度
- ORC不是一个单纯的列式存储格式，仍然是首先根据行组分割整个表，在每一个行组内进行按列存储
- ORC文件是自描述的，它的元数据使用Protocol Buffers序列化，并且文件中的数据尽可能的压缩以降低存储空间的消耗，目前也被Hive、Spark SQL、Presto等查询引擎支持
| 优点 | 缺点 | 应用场景 |
|---|---|---|
| 列式存储，存储效率非常高可压缩，高效的列存取查询效率较高，支持索引支持矢量化查询 | 加载时性能消耗较大需要通过text文件转化生成读取全量数据时性能较差 | 适用于Hive中大型的存储、查询 |
26-2.数据压缩
数据压缩-概述
- Hive底层运行MapReduce程序时，磁盘I/O操作、网络数据传输、shuffle和merge要花大量的时间，尤其是数据规模很大和工作负载密集的情况下
- 鉴于磁盘I/O和网络带宽是Hadoop的宝贵资源，数据压缩对于节省资源、最小化磁盘I/O和网络传输非常有帮助
- Hive压缩实际上说的就是MapReduce的压缩
数据压缩-优点
- 减小文件存储所占空间
- 加快文件传输效率，从而提高系统的处理速度
- 降低IO读写的次数
数据压缩-缺点
使用数据时需要先对文件解压，加重CPU负荷，压缩算法越复杂，解压时间越长
数据压缩-Hive中压缩
Hive中的压缩就是使用了Hadoop中的压缩实现的，所以Hadoop中支持的压缩在Hive中都可以直接使用
Hadoop中支持的压缩算法：
要想在Hive中使用压缩，需要对MapReduce和Hive进行相应的配置
数据压缩-Hive中常用压缩配置
--开启hive中间传输数据压缩功能
--1）开启hive中间传输数据压缩功能
set hive.exec.compress.intermediate=true;
--2）开启mapreduce中map输出压缩功能
set mapreduce.map.output.compress=true;
--3）设置mapreduce中map输出数据的压缩方式
set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec;
--开启Reduce输出阶段压缩
--1）开启hive最终输出数据压缩功能
set hive.exec.compress.output=true;
--2）开启mapreduce最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;
--3）设置mapreduce最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
--4）设置mapreduce最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
26-3.存储优化
存储优化-避免小文件生成
- Hive的存储本质还是HDFS，HDFS是不利于小文件存储的，因为每个小文件会产生一条元数据信息，并且不利用MapReduce的处理，MapReduce中每个小文件会启动一个MapTask计算处理，导致资源的浪费，所以在使用Hive进行处理分析时，要尽量避免小文件的生成
- Hive中提供了一个特殊的机制，可以自动的判断是否是小文件，如果是小文件可以自动将小文件进行合并
-- 如果hive的程序，只有maptask，将MapTask产生的所有小文件进行合并
set hive.merge.mapfiles=true;
-- 如果hive的程序，有Map和ReduceTask,将ReduceTask产生的所有小文件进行合并
set hive.merge.mapredfiles=true;
-- 每一个合并的文件的大小（244M）
set hive.merge.size.per.task=256000000;
-- 平均每个文件的大小，如果小于这个值就会进行合并(15M)
set hive.merge.smallfiles.avgsize=16000000;
存储优化-合并小文件
- Hive中也提供一种输入类CombineHiveInputFormat，用于将小文件合并以后，再进行处理
-- 设置Hive中底层MapReduce读取数据的输入类：将所有文件合并为一个大文件作为输入
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
12
存储优化-ORC文件索引
- 在使用ORC文件时，为了加快读取ORC文件中的数据内容，ORC提供了两种索引机制：Row Group Index 和 Bloom Filter Index可以帮助提高查询ORC文件的性能
- 当用户写入数据时，可以指定构建索引，当用户查询数据时，可以根据索引提前对数据进行过滤，避免不必要的数据扫描
- Row Group Index
- 一个ORC文件包含一个或多个stripes(groups of row data)，每个stripe中包含了每个column的min/max值的索引数据
- 当查询中有大于等于小于的操作时，会根据min/max值，跳过扫描不包含的stripes
- 而其中为每个stripe建立的包含min/max值的索引，就称为Row Group Index行组索引，也叫min-max Index大小对比索引，或者Storage Index
- 建立ORC格式表时，指定表参数’orc.create.index’=’true’之后，便会建立Row Group Index
- 为了使Row Group Index有效利用，向表中加载数据时，必须对需要使用索引的字段进行排序
-- 开启索引配置
set hive.optimize.index.filter=true;
- Bloom Filter Index
- 建表时候通过表参数”orc.bloom.filter.columns”=”columnName……”来指定为哪些字段建立BloomFilter索引，在生成数据的时候，会在每个stripe中，为该字段建立BloomFilter的数据结构
- 当查询条件中包含对该字段的等值过滤时候，先从BloomFilter中获取以下是否包含该值，如果不包含，则跳过该stripe
存储优化-ORC矢量化查询
- Hive的默认查询执行引擎一次处理一行，而矢量化查询执行是一种Hive针对ORC文件操作的特性，目的是按照每批1024行读取数据，并且一次性对整个记录整合（而不是对单条记录）应用操作，提升了像过滤, 联合, 聚合等等操作的性能
注意：要使用矢量化查询执行，就必须以ORC格式存储数据
-- 开启矢量化查询
set hive.vectorized.execution.enabled = true;
set hive.vectorized.execution.reduce.enabled = true;
第27章:Job作业执行优化
27-1.Explain查询计划
Explain查询计划
- HiveQL是一种类SQL的语言，从编程语言规范来说是一种声明式语言，用户会根据查询需求提交声明式的HQL查询，而Hive会根据底层计算引擎将其转化成Mapreduce/Tez/Spark的job
- explain命令可以帮助用户了解一条HQL语句在底层的实现过程。通俗来说就是Hive打算如何去做这件事
- explain会解析HQL语句，将整个HQL语句的实现步骤、依赖关系、实现过程都会进行解析返回，可以了解一条HQL语句在底层是如何实现数据的查询及处理的过程，辅助用户对Hive进行优化
- 常用语法命令如下：
- EXPLAIN [FORMATTED|EXTENDED|DEPENDENCY|AUTHORIZATION|] query
- FORMATTED：对执行计划进行格式化，返回JSON格式的执行计划
- EXTENDED：提供一些额外的信息，比如文件的路径信息
- DEPENDENCY：以JSON格式返回查询所依赖的表和分区的列表
- AUTHORIZATION：列出需要被授权的条目，包括输入与输出
0: jdbc:hive2://ops01:10000> select * from student limit 2;
+--------------+---------------+--------------+--------------+---------------+
| student.num | student.name | student.sex | student.age | student.dept |
+--------------+---------------+--------------+--------------+---------------+
| 95001 | 李勇 | 男 | 20 | CS |
| 95002 | 刘晨 | 女 | 19 | IS |
+--------------+---------------+--------------+--------------+---------------+
2 rows selected (0.144 seconds)
0: jdbc:hive2://ops01:10000> explain extended select * from student limit 2;
+----------------------------------------------------+
| Explain |
+----------------------------------------------------+
| STAGE DEPENDENCIES: |
| Stage-0 is a root stage |
| |
| STAGE PLANS: |
| Stage: Stage-0 |
| Fetch Operator |
| limit: 2 |
| Processor Tree: |
| TableScan |
| alias: student |
| Statistics: Num rows: 1 Data size: 5270 Basic stats: COMPLETE Column stats: NONE |
| GatherStats: false |
| Select Operator |
| expressions: num (type: int), name (type: string), sex (type: string), age (type: int), dept (type: string) |
| outputColumnNames: _col0, _col1, _col2, _col3, _col4 |
| Statistics: Num rows: 1 Data size: 5270 Basic stats: COMPLETE Column stats: NONE |
| Limit |
| Number of rows: 2 |
| Statistics: Num rows: 1 Data size: 5270 Basic stats: COMPLETE Column stats: NONE |
| ListSink |
+----------------------------------------------------+
27-2.MapReduce属性优化
本地模式
- 使用Hive的过程中，有一些数据量不大的表也会转换为MapReduce处理，提交到集群时，需要申请资源，等待资源分配，启动JVM进程，再运行Task，一系列的过程比较繁琐，本身数据量并不大，提交到YARN运行返回会导致性能较差的问题
- Hive为了解决这个问题，延用了MapReduce中的设计，提供本地计算模式，允许程序不提交给YARN，直接在本地运行，以便于提高小数据量程序的性能
- 配置
- set hive.exec.mode.local.auto = true;
JVM重用
- Hadoop默认会为每个Task启动一个JVM来运行，而在JVM启动时内存开销大；
- Job数据量大的情况，如果单个Task数据量比较小，也会申请JVM，这就导致了资源紧张及浪费的情况；
- JVM重用可以使得JVM实例在同一个job中重新使用N次，当一个Task运行结束以后，JVM不会进行释放，而是继续供下一个Task运行，直到运行了N个Task以后，就会释放；
- N的值可以在Hadoop的mapred-site.xml文件中进行配置，通常在10-20之间。
并行执行
- Hive在实现HQL计算运行时，会解析为多个Stage，有时候Stage彼此之间有依赖关系，只能挨个执行，但是在一些别的场景下，很多的Stage之间是没有依赖关系的
- 例如Union语句，Join语句等等，这些Stage没有依赖关系，但是Hive依旧默认挨个执行每个Stage，这样会导致性能非常差，我们可以通过修改参数，开启并行执行，当多个Stage之间没有依赖关系时，允许多个Stage并行执行，提高性能
-- 开启Stage并行化，默认为false
SET hive.exec.parallel=true;
-- 指定并行化线程数，默认为8
SET hive.exec.parallel.thread.number=16;
27-3.Join优化
- Join是数据分析处理过程中必不可少的操作，Hive同样支持Join的语法
- Hive Join的底层是通过MapReduce来实现的，Hive实现Join时，为了提高MapReduce的性能，提供了多种Join方案来实现
- 适合小表Join大表的Map Join
- 大表Join大表的Reduce Join
- 大表Join的优化方案Bucket Join
Map Join
- 应用场景
- 适合于小表join大表或者小表Join小表
- 原理
- 将小的那份数据给每个MapTask的内存都放一份完整的数据，大的数据每个部分都可以与小数据的完整数据进行join
- 底层不需要经过shuffle，需要占用内存空间存放小的数据文件
- 使用
- 尽量使用Map Join来实现Join过程，Hive中默认自动开启了Map Join：hive.auto.convert.join=true
- 相关参数
- hive.auto.convert.join.noconditionaltask.size=512000000
Reduce Join
- 应用场景
- 适合于大表Join大表
- 原理
- 将两张表的数据在shuffle阶段利用shuffle的分组来将数据按照关联字段进行合并
- 必须经过shuffle，利用Shuffle过程中的分组来实现关联
- 使用
- Hive会自动判断是否满足Map Join，如果不满足Map Join，则自动执行Reduce Join
Bucket Join
- 应用场景
- 适合于大表Join大表
- 原理
- 将两张表按照相同的规则将数据划分，根据对应的规则的数据进行join
- 减少了比较次数，提高了性能
- 使用
- 语法：clustered by colName
- 参数：set hive.optimize.bucketmapjoin = true;
- 要求：分桶字段 = Join字段 ，桶的个数相等或者成倍数
27-4.优化器
优化器-关联优化
- 当一个程序中如果有一些操作彼此之间有关联性，是可以在一个MapReduce中实现的，但是Hive会不智能的选择，Hive会使用两个MapReduce来完成这两个操作
- 例如：当我们执行 select …… from table group by id order by id desc。该SQL语句转换为MapReduce时，我们可以有两种方案来实现
- 方案一：第一个MapReduce做group by，经过shuffle阶段对id做分组，第二个MapReduce对第一个MapReduce的结果做order by，经过shuffle阶段对id进行排序
- 方案二：因为都是对id处理，可以使用一个MapReduce的shuffle既可以做分组也可以排序
- 在这种场景下，Hive会默认选择用第一种方案来实现，这样会导致性能相对较差
- 可以在Hive中开启关联优化，对有关联关系的操作进行解析时，可以尽量放在同一个MapReduce中实现
- 相关配置：
- set hive.optimize.correlation=true;
优化器引擎
- Hive默认的优化器在解析一些聚合统计类的处理时，底层解析的方案有时候不是最佳的方案
优化器引擎-CBO优化器
- RBO
- rule basic optimise：基于规则的优化器，根据设定好的规则来对程序进行优化
- CBO
- cost basic optimise：基于代价的优化器，根据不同场景所需要付出的代价来合适选择优化的方案
- Hive中支持RBO与CBO这两种引擎，默认使用的是RBO优化器引擎
- 很明显CBO引擎更加智能，所以在使用Hive时，我们可以配置底层的优化器引擎为CBO引擎
- 相关配置项
- set hive.cbo.enable=true;
- set hive.compute.query.using.stats=true;
- set hive.stats.fetch.column.stats=true;
优化器-Analyze分析器
- 功能
- 用于提前运行一个MapReduce程序将表或者分区的信息构建一些元数据【表的信息、分区信息、列的信息】，搭配CBO引擎一起使用
- 语法
- ANALYZE TABLE tablename
27-5.谓词下推（PPD）
- 谓词：用来描述或判定客体性质、特征或者客体之间关系的词项。比如"3 大于 2"中"大于"是一个谓词
- 谓词下推Predicate Pushdown（PPD）基本思想：将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。简单点说就是在不影响最终结果的情况下，尽量将过滤条件提前执行
- Hive中谓词下推后，过滤条件会下推到map端，提前执行过滤，减少map到reduce的传输数据，提升整体性能
- 开启参数( 默认开启 )
- hive.optimize.ppd=true;
第28章:Hive3新特性
28-1.Hive on Tez
Hive 执行引擎
- Hive底层的计算由分布式计算框架实现，目前支持三种计算引擎，分别是MapReduce、Tez、Spark
- Hive中默认的计算引擎是MapReduce，由hive.execution.engine参数属性控制
- Tez是Apache社区中的一种支持DAG作业的开源计算框架
- 可以将多个有依赖的作业转换为一个作业从而大幅提升DAG作业的性能，最终Tez也会将程序提交给YARN来实现运行
- Tez并不直接面向最终用户，事实上它允许开发者为最终用户构建性能更快、扩展性更好的应用程序
28-2.Hive LLAP更新
- LLAP是hive 2.0版本就引入的特性，在Hive 3中与Tez集成的应用更加成熟
- Hive官方称之为实时长期处理(Live long and process)，实现将数据预取、缓存到基于yarn运行的守护进程中，降低和减少系统IO和与HDFS DataNode的交互，以提高程序的性能，LLAP 目前只支持tez引擎
- LLAP提供了一种混合执行模型。它由一个长期存在的守护进程(该守护进程替换了与 HDFS DataNode 的直接交互)以及一个紧密集成的基于 DAG 的框架组成。诸如缓存，预取，某些查询处理和访问控制之类的功能已移至守护程序中
- 此守护程序直接直接处理小/短查询，而任何繁重的工作都将在标准 YARN 容器中执行
- 与DataNode 相似，LLAP 守护程序也可以由其他应用程序使用
- Tez AM 统筹整体执行，查询的初始阶段被推送到 LLAP 中，在还原阶段，将在单独的容器中执行大型Shuffle，多个查询和应用程序可以同时访问 LLAP
- LLAP 在现有的Hive基于流程的执行中工作，以保持Hive的可伸缩性和多功能性。它不会替代现有的执行模型，而是对其进行了增强。它有如下的几个特点:
- LLAP守护程序是可选的
- Hive可以在没有LLAP的情况下工作，并且即使已经部署并启动运行LLAP也可以绕过不执行
- LLAP不是执行引擎
- 不同于MapReduce 或 Tez，整个执行由现有的 Hive 执行引擎(例如 Tez)在 LLAP 节点以及常规容器上透明地调度和监控。显然，LLAP 的支持级别取决于单独的执行引擎。不计划支持 MapReduce，但以后可能会添加其他引擎，例如Pig等其他框架也可以选择使用 LLAP 守护程序
- 部分执行
- LLAP守护程序执行的工作结果可以构成 Hive 查询结果的一部分
- 资源Management
- YARN仍然负责资源的管理和分配
- LLAP守护程序是可选的
28-3.Metastore独立模式
- Hive中的所有对象如数据库、表、函数等，他们的定义都叫做metadata元数据。
- metastore是元数据存储服务，用于操作访问元数据。
- Hive或者其他执行引擎在运行时会使用这些元数据来决定如何解析、授权和有效地执行用户查询。
- metadata元数据可以存储配置为嵌入式的Apache Derby RDBMS或连接到外部RDBMS。
- Metastore本身可以完全嵌入到用户进程中，也可以作为服务运行，供其他进程连接
- 从Hive 3.0开始，Metastore可以在不安装Hive其他部分的情况下单独运行，作为一个单独的发行版提供，用于实现允许其他非Hive的系统，例如Spark、Impala等轻松地与Metastore集成。
- 目前来说为了方便起见，依旧建议将Metastore放在Hive中，一起安装部署
- 从Hive 3.0开始，Metastore作为一个单独的包发布，可以在没有Hive其他部分的情况下运行。这称为
