5.Hive参数配置与函数、运算符使用_hive -f

1.Hive客户端与属性配置

1.1 CLIs and Commands

1.1.1 Hive CLI

$HIVE_HOME/bin/hive是第一个shell Util，其主要功能有两个
1.交互式或批处理模式运行Hive查询
2.hive相关服务的启动
可以运行“hive -H”或者“hive --help”来查看命令行选项

-e <quoted-query-string> 执行命令行-e参数后指定的sql语句 运行完退出
-f <filename> 执行命令行-f参数后指定的sql文件 运行完退出
-H --help 打印帮助信息
--hiveconf <property=value> 设置参数
-S,--silent 静默设置
-v,--verbose 详情模式，将指向sql回显到console
--service Service_name 启动hive的i相关服务
1
2
3
4
5
6
7

功能一：Batch Mode批处理模式
当使用-e或-f选项运行bin/hive时，它将以批处理模式执行SQL命令。
所谓的批处理可以理解为一次性执行，指向完毕退出

# -e
$HIVE_HOME/bin/hive -e 'show databases'

# -f
cd ~
# 编辑一个sql文件 里面协商合法正确的sql语句
vim hive.sql
show databases;
# 执行从客户端所在及其的本地磁盘加载文件
$HIVE_HOME/bin/hive -f /root/hive.sql
# 也可以从其他文件系统加载sql文件执行
$HIVE_HOME/bin/hive -f hdfs://<namenode>:<port>/hive-script.sql
$HIVE_HOME/bin/hive -f s3://mys3bucket/s2-script.sql

# -i 进入交互模式之前运行初始化脚本
$HIVE_HOME/bin/hive -i /home/my/hive-init.sql

# 使用静默模式 将数据从查询中转储到文件中
$HIVE_HOME/bin/hive -S -e 'select * from student' > a.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

功能二：Interactive Shell交互式模式
所谓交互式模式可以理解为客户端和hive服务一直保持连接，除非手动退出客户端

/export/server/hive/bin/hive

hive> show databases;
1
2
3

功能三：启动Hive服务
比如metastore服务和hiveserver2服务的启动

# --hiveconf
$HIVE_HOME/bin/hiva --hiveconf hive.root.logger=DEBUG,console

# --service
$HIVE_HOME/bin/hiva --service metastore
$HIVE_HOME/bin/hiva --service hiveserver2
1
2
3
4
5
6

1.1.2 Beeline CLI

$HIVE_HOME/bin/beeline被称为第二代客户端，是一个JDBC客户端。相比于第一代客户端，性能加强安全性提高
在嵌入式模式下，它运行嵌入式Hive
在远程模式下beeline运行Thrift连接到单独的HiveServer2服务上。
Beeline支持的参数非常多，可以通过官方文档进行查询

常见的使用该方式如下：在启动hiveserver2服务的前提下使用beeline远程连接HS2服务

启动beeline
/export/server/hive/bin/beeline

beeline> ! connect jdbc:hive2://node1:10000
1
2
3
4

1.2 Configuration Properties属性配置

概述

• Hive除了默认的属性配置之外，还支持用户使用时修改配置
• 修改Hive配置之前，作为用户需要掌握两件事
  • 有哪些属性支持用户修改，属性的功能、作用是什么
  • 支持哪些方式进行修改，是临时生效还是永久生效
• Hive配置属性实在HiveConf.java类中管理的，可以参考文件以获取当前使用版本可用的配置属性列表
• 从Hive0.14.0开始，会从HiveConf.java中直接生成配置模板文件hive-default.xml.template
• 详细的配置参数大全可以参考Hive官网配置参数

配置方式
方式一：hive-site.xml
在$HIVE_HOME/conf路径下，可以添加一个hive-site.xml文件，把需要定义修改的配置属性添加进去，这个配置文件会影响到基于这个Hive安装包的任何一种服务启动、客户端使用方式

方式二：–hiveconf命令行参数

• hiveconf是一个命令行的参数，用于在使用Hive CLI或者Beeline CLI的时候指定配置参数
• 这种方式的配置在整个的会话session中生效，会话结束，失效
• 比如在启动hive服务的时候，为了更好的查看启动详情，可以通过hiveconf参数修改日志级别

方式三：set命令

• 在Hive CLI或Beeline中使用set命令为set命令之后的所有SQL语句设置配置参数，这个也是会话级别的
• 这种方式也是用户日常开发中使用最多的一种配置参数方式

set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrit;
1
2

方式四：服务特定配置文件
hivemetastore-site.xml、hiveserver2-site.xml

• Hive Metastore会加载可用的hive-site.xml以及hivemetastore-site配置文件
• HiveServer2会加载可用的hive-site.xml以及hiveserver2-site.xml
  如果HiveServer2以嵌入式模式使用元存储，则还将将在hivemetastore-site.xml

总结

• 配置方式优先级：set设置 > hiveconf参数 > hive-site.xml配置我呢见
• set参数声明会覆盖命令行参数hiveconf，命令行参数会覆盖配置文件hive-site.xml设定
• 日常开发使用中，如果不是核心的需要全局修改的参数属性，建议使用set命令进行设置
• Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置

2.Hive内置运算符

概述
整体上Hive支持的运算符分为三大类：关系运算、算术运算、逻辑运算
可以参考官方文档
也可以使用下述方式查看运算符的使用

-- 显示所有的函数和运算符
show functions;
-- 查看运算符或者函数的使用说明
describe function count;
-- 使用extended可以查看更加详细的使用说明
describe function extended count;
1
2
3
4
5
6

测试环境准备
在Hive中创建一个空表dual，用于测试各种运算符的功能

-- 1.创建dual
create table dual(id string);

-- 2.加载一个文件dual.txt到dual表中
-- dual.txt只有一行内容，内容为一个空格

-- 3.在select查询语句中使用dual表完成运算符、函数功能测试
select 1+1 from dual;
1
2
3
4
5
6
7
8

2.1 关系运算符

• 关系运算符是二元运算符，执行的是两个操作数的比较运算
• 每个关系运算符都返回boolean类型结果（TRUE或FALSE）

等值比较：=、==
不等值比较：<>、!=
小于比较：<
小于等于比较：<=
大于比较：>
大于等于比较：>=
空值判断：IS NULL
非空判断：IS NOT NULL
LIKE比较：LIKE
JAVA的LIKE操作：RLIKE
REGEXP操作：REGEXP
1
2
3
4
5
6
7
8
9
10
11

示例

-- 1.Hive中关系运算符
-- is null控制判断
select 1 from dual where 'test' is null;

-- is not null非空值判断
select 1 from dual where 'test' is not null;

-- like比较: _表示任意单个字符，%表示任意数量字符
-- 否定判断：NOT A like B
select 1 from dual where 'test' like 'te__';
select 1 from dual where 'test' like 'te%';
select 1 from dual where 'test' like '_e%';

-- rlike：确定字符串是否匹配正则表达式，是REGEXP_LIKE()的同义词
select 1 from dual where 'test' rlike '^t.*t$';
select 1 from dual where '123456' rlike '^\\d+$'; -- 判断是否全为数字
select 1 from dual where '123456aa' rlike '^\\d+$';

-- regexp：功能与rlike相同，用于判断字符串是否匹配正则表达式
select 1 from dual where 'test' rlike '^t.*t$';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

2.2 算术运算符

算术运算符操作数必须是数值类型。分为一元运算符和二元元素安抚
一元运算符，只有一个操作数；二元运算符有两个操作数，运算符在两个操作数之间

加法操作：+
减法操作：-
乘法操作：*
除法操作：/
取整操作：div
取余操作：%
位与操作：&
位或操作：|
位异或操作：^
位取反操作：~
1
2
3
4
5
6
7
8
9
10

示例

-- 取整操作：div
select 17 div 3;

-- 取余操作：%
select 17 % 3;

-- 位与操作：& A和B按位进行与操作
select 4 & 8 from dual;

-- 位或操作：|
select 4 | 8 from dual;

-- 位异或操作：^
select 4 ^ 8 from dual;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.3 逻辑运算符

与操作：A AND B
或操作：A OR B
非操作：NOT A、!A
在：A IN (val1,val2,...)
不在：A NOT IN (val1,val2,...)
逻辑是否存在：[NOT] EXISTS (subquery)
1
2
3
4
5
6

示例

-- 3.Hive逻辑运算符
-- 与操作：A AND B
select 1 from dual where 3 > 1 and 2 > 1;
-- 或操作：A OR B
select 1 from dual where 3 > 1 or 2 != 2;
-- 非操作：NOT A、!A
select 1 from dual where not 2 > 1;
select 1 from dual where !2 = 1;
-- 在：A IN (val1,val2,...)
select 1 from dual where 11 in (11,22,33);
-- 不在：A NOT IN (val1,val2,...)
select 1 from dual where 11 not in (22,33,44);
-- 逻辑是否存在：[NOT] EXISTS (subquery)
select A.* from A
where exists (select B.id from B where A.id = B.id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

其他运算符还有如字符串拼接（||），构造运算符等

3.Hive函数入门

3.1 Hive函数概述及分类标准

概述
Hive内建了不少函数，用于满足用户不同使用需求，提高SQL编写效率
1.使用show functions查看当下可用的所有函数
2.通过describe function extended funcname来查看函数的使用方式

分类标准
Hive的函数分为两大类：内置函数、用户自定义函数UDF
1.内置函数可分为：数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等
2.用户定义函数根据输入输出的行数可分为3类：UDF、UDAF、UDTF

用户自定义函数UDF分类标准
UDF普通函数，一进一出
UDAF聚合函数，多进一出
UDTF表生成函数，一进多出

UDF分类标准扩大化
UDF分类标准本来针对的是用户自己编写开发实现的函数。UDF分类标准可以扩大到Hive的所有函数中：包括内置函数和用户自定义函数。比如Hive官方文档中，针对聚合函数的标准就是内置的UDAF类型

3.2 Hive内置函数

概述
内置函数指的是Hive开发实现好，之恶杰可以使用的函数，也叫做内建函数。具体可以查看官方文档
内置函数根据应用归类整体可以分为8大种类型：日期函数，字符串函数，数学函数，条件函数，类型转换函数，数据脱敏函数，集合函数，其他杂项函数

3.2.1 字符串函数

• 字符串长度函数：length
• 字符串反转函数：reverse
• 字符串连接函数：concat
• 带分隔符字符串连接函数：concat_ws
• 字符串截取函数：substr,substring
• 字符串转大写函数：uppe,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

-- 字符串拼接：concat
select concat("angela","baby");
-- 带分隔符字符串拼接：concat_ws(separator, [string |array(string)]+)
select concat_ws('.','www',array('baidu','com'));
-- 字符串截取函数：substr(str,pos[, len]) 或者 substring(str,pos[, len])
select substr("angelababy",-2); -- pos是从1开始的索引，如果是复数则倒着数
select substr("angelababy",2,2);
-- 正则表达式替换函数：regexp_replace(str, regexp, rep)
select regexp_replace('100-200','(\\d+)', 'num');
-- 正则表达式解析函数：regexp_extract(str, regexp[, idx]) 提取正则陪陪到的指定组内容
select regexp_extract('100-200','(\\d+)-(\\d+)', 2);
-- URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数
select parse_url('http://www.baidu.com/123/fasfawq','HOST');
-- 分割字符串函数：split(str, regex)
select split('apache hive','\\s+'); -- \\s+ 自动匹配空的符号，比如空格，制表符等
-- json解析函数：get_json_object(json_txt,path)
-- $表示json对象
select get_json_object('[{"website":"www.baidu.com","name":"test"}],[{"website":"www.google.com","name":"test22"}]','$.[1].website');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

3.2.2 日期函数

• 获取当前日期：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
-- 同一查询中对current_timestamp的所有调用均返回相同的值
select current_timestamp();
-- 获取当前UNIX时间戳函数：unix_timestamp
select unix_timestamp();
-- 日期转UNIX时间戳函数：unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");
-- 指定格式日期转UNIX时间戳函数：unix_timestamp
select unix_timestamp("20111207 13:01:03",'yyyMMdd HH:mm:ss');
-- UNIX时间戳转日期函数：from_unixtime
select from_unixtime(1618238391);
select from_unixtime(0,'YYY=MM-dd HH:mm:ss');
-- 日期比较函数：datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2012-02-28','2012-05-28');
-- 日期增加函数：date_add
select date_add('2012-03-28',10);
-- 日期减少函数：date_sub
select date_sub('2012-01-01',10);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3.2.3 数学函数

• 取整函数：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, 5);
-- 向下取整函数：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(2);
-- 二进制函数：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);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3.2.4 集合函数

• 集合元素size函数：size(Map<K,V>) size(Array)
• 取map集合key函数：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`(111,222,333),111);

-- 数组排序函数：sort_array(Array<T>)
select sort_array(`array`(12,2,32));
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.3.5 条件函数

主要用于条件判断、逻辑判断转换这样的场合

• 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,否则返回a
• assert_true：如果条件部位真，则引发异常，否则返回null

-- 使用之前课程创建好的student表数据
-- if条件判断
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("allen",'helen');
select nvl(null,'test');

-- 非空查找函数 COALESCE(T v1, T v2,...)
-- 返回参数中的第一个非空值，如果所有值都为NULL，那么返回NULL
select COALESCE(null,11,22,33);
select COALESCE(null,null);

-- 条件转换函数：
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
select case sex when '男' then 'male' else 'female' end from student limit 3;

-- nullif(a,b)
-- 如果 a = b,则返回null，否则返回a
select nullif(11,11);
select nullif(11,12); 

-- assert_true(condition)
-- 如果'condition' 不为真，则引发异常，否则返回null
select assert_true(11 >= 0);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

2.3.6 类型转换函数

主要用于显式的数据类型转换

describe function extended cast;

-- 在任意数据类型之间转换：cast
select cast(12.14 as bigint);
select cast(12.14 as string);
1
2
3
4
5

2.3.7 数据脱敏函数

主要完成对数据脱敏转换功能，屏蔽原始数据

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)
1
2
3
4
5
6

-- mask
-- 将查询返回的数据，大写字母转换位X，小写字母转换为x，数字转换为n
select mask("abc123DFG");
select mask("abc123DFG",'-','.','^'); -- 自定义替换字符

-- mask_first_n(string str[, int n])
-- 对前n个进行脱敏替换
select mask_first_n("abc123DFG",4);

-- mask_last_n(string str[, int n])
-- 对后n个进行脱敏替换
select mask_last_n("abc123DFG",4);

-- mask_show_first_n(string str[, int n])
-- 除了前n个字符，其余进行掩码
select mask_show_first_n("abc123DFG",4);

-- mask_show_last_n(string str[, int n])
-- 除了后n个字符，其余进行掩码
select mask_show_last_n("abc123DFG",4);

-- mask_hash(string|char|varchar str)
-- 返回字符串的hash编码
select mask_hash("abc123DFG");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

2.3.8 其他杂项函数

• 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)

-- 如果调用的java方法所在的jar包不是hive自带的 可以使用add jar添加进来
-- Hive调用java方法
select java_method("java.lang.Math","max",11,22);

-- 反射函数
select reflect("java.lang.Math","max,11,22");

-- 取哈希值函数
select hash("allen");

-- current_user()、logged_in_user()、current_database()、version()

-- SHA-1加密：sha1(string/binary)
select sha1("allen");

-- SHA-2
select sha2("allen",224);
select sha2("allen",512);

-- crc32加密
select crc32("allen");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3.3 用户自定义函数

UDF普通函数
特点：一进一出，输入一行输出一行

UDAF聚合函数
UDAF的A表示Aggregation聚合的意思
多进一出，也就是输入多行输出一行
比如count、sum这样的函数

• count：统计检索到的总行数
• sum：求和
• avg：求平均
• min：最小值
• max：最大值
• 数据收集函数（去重）：collect_set(col)
• 数据收集函数（不去重）：collect_list(col)

UDTF表生成函数
UDTF表生成函数，T代表Table-Generating表生成的意思
特点是一进多出，也就是输入一行输出多行
这个类型的函数作用返回结果类似于表，比如explode函数

3.3.1 开发Hive UDF实现手机号加密

需求
1.能够对输入数据进行非空判断、手机号位数判断
2.能够实现校验手机号格式，把满足规则的进行加密处理
3.对不符合手机号规则的数据直接返回，不处理

UDF实现步骤
1.写一个java类，继承UDF，并重载evaluate方法，方法中实现函数的业务逻辑
2.重载意味着可以在一个java类中实现多个函数功能
3.程序打成jar包，上传HS2服务器本地或HDFS
4.客户端命令行中添加jar包到Hive的classpath：hive > add JAR /xxx/udf.jar
5.注册成为临时函数(给UDF命名)：create temporary function 函数名 as ‘UDF类全路径’
6.HQL中使用函数

开发环境准备
IDEA中创建Maven工程，添加下述pom依赖，用于开发Hive UDF

<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>
1
2
3
4
5
6
7
8
9
10
11
12

步骤1：编写业务代码

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 encryPhoNum = null;
//        手机号不为空，并且位11位
        if (StringUtils.isNotEmpty(phoNum) && phoNum.trim().length() == 11){
//            判断数据是否满足中国大陆手机号码规范
            String regex = "^(1[3-9)\\d{9}$";
            Pattern p = Pattern.complie(regex);
            Matcher m = p.matcher(phoNum);
//            判断是否符合手机号规则
            if(m.matches()){
//                使用正则替换 返回加密后数据
                encryPhoNum = phoNum.trim().replaceAll("(\\d{3})\\d{4}(\\d{4})","$1****$2");
            }else {
//                不符合手机号规则的返回源号码
                encryPhoNum = phoNum;
            }
        }else {
//            不符合11位的返回原号码
            encryPhoNum = phoNum;
        }
        return encryPhoNum;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

步骤2：打jar包上传
IDEA中使用继承的Maven插件进行打包，这里会把依赖一起打入jar包

步骤3：jar包上传HS2服务器本地
把jar包上传到HS2服务运行所在及其的Linux系统，上传HDFS文件系统也可以，后续路径指定清楚即可

步骤4：添加jar至Hive Classpath
在客户端使用命令把jar包添加至classpath

add jar <jar包路径>
1

步骤5：注册临时函数
就是给用户编写的函数起个名字

create temporary function 函数名 as 'UDF类全路径';
1

4.Hive函数高阶

4.1 Hive UDTF之explode函数

功能介绍

• explode接受map、array类型的数据作为输入，然后把输入数据中的每个元素拆开变成一行数据，一个元素一行（array一列，map两列）
• explode执行效果正好满足于输入一行输出多行，所以叫做UDTF函数
• 一般情况下，explode函数可以直接单独使用
• 也可以根据业务需要结合lateral view侧视图一起使用

练习：NBA总冠军球队名单分析
1.练习explode函数的使用
2.感悟什么叫做UDTF表生成函数
3.发现UDTF函数使用限制

业务需求：
有一份“The_NBA_Championship.txt”，关于部分年份的NBA总冠军球队名单
第一个字段表示球队名称，第二个字段是获取总冠军的年份

数据示例：
Chicago Bulls,1991|1992|1993|1996|1997|1998
1
2

需求：使用Hive建表映射成功数据，对数据拆分

建表加载数据

-- step1:建表
create table the_nba_championship(
    team_name string,
    champion_year array<string>
) row format delimited
fields terminated by ','
collection items terminated by '|';

-- step2:加载数据文件到表中
load data local inpath '/root/hivedata/The_NBA_Championship.txt' into table the_nab_championsship;

-- step3：验证
select * from the_nba_championship;
1
2
3
4
5
6
7
8
9
10
11
12
13

UDTF语法限制
explode函数属于UDTF表生成函数，explode执行结果可以理解位一张虚拟的表，其数据来源于源表
在select中只查询源表数据没问题，只查询explode生成的虚表数据也没问题，但是不能在只查询源表的时候，既想返回源表数据又想返回explode生成的虚拟表字段

UDTF语法限制解决
从SQL层面上来说问题解决方案：join关联查询
Hive专门提供了语法lateral View侧视图，专门用于搭配explode这样的UDTF函数

-- step4:使用explode函数对champion_year 进行拆分
select explode(champion_year) from the_nba_championship;

-- step5:lateral view + explode
select a.team_name,b.year
from the_nba_championship a lateral view explode(champion_year) b as year
order by b.year desc;
1
2
3
4
5
6
7

4.2 Hive Lateral View侧视图

概念
Lateral View 是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的一些查询限制的问题
一般使用UDTF就会固定搭配lateral view使用

原理
将UDTF的结果构建乘一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题
使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by、limit等语法中，不需要再单独嵌套一层子查询

-- lateral view 侧视图基本语法
select ... from tableA lateral view UDTF(xxx) 别名 as col1,col2,...;
1
2

4.3 Hive Aggregation聚合函数

概述

• 聚合函数的功能是：对一组值执行计算并返回单一的值
• 聚合函数是典型的输入多行输出一行，使用Hive的分类标准，允许UDAF类型函数
• 通常搭配Group By语法一起使用，分组后进行聚合操作

基础聚合
HQL提供了几种内置的UDAF聚合函数，例如max(…),min(…)和avg(…)

增强聚合

• 增强聚合包括grouping_sets、cube、rollup这几个函数;主要适用于OLAP多维数据分析模式中，多维分析中的维指的分析问题时看待问题的维度、角度
• 下面通过案例来理解函数的功能含义。数据中字段含义：月、天、用户表示cookieid

数据示例：
2018-03,2018-03-10,cookie1
1
2

-- 表创建
create table cookie_info(
    month string,
    day string,
    cookieid string
) row format delimited
fields terminated by ',';
-- 加载数据
1
2
3
4
5
6
7
8

grouping sets
grouping sets是一种将多个group by逻辑写再一个sql语句中的便利写法。等价于将不用维度的group by 结果集进行union all。grouping_id表示结果属于哪一个分组集合

select month,day,count(distinct cookieid) as numa,grouping__id
from cookie_info
group by month,day
grouping sets (month,day)
order by grouping__id;

-- 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

cube

• cube表示根据group by的维度的所有组合进行聚合
• 对于cube来说，如果有n个维度，则素有组合的总个数是：2^n

-- cube
select month,day,count(distinct cookieid) as numa,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
union all
select null,day,count(distinct cookieid) as nums,2 as grouping__id
from cookie_info
union all
select month,day,count(distinct cookieid) as nums,3 as grouping__id
from cookie_info;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

rollup

• cube的语法功能指的是：根据group by的维度的所有组合进行聚合
• rollup是cube的子集，以最左侧的维度为主，从该维度进行层级聚合
  比如rollup有a，b，c三个维度，则组合情况是：(a,b,c),(a,b),(a),()

-- rollup
-- 以month维度进行层级聚合
select month,day,count(distinct cookieid) as numa,grouping__id
from cookie_info
group by month,day
with rollup
order by grouping__id;
1
2
3
4
5
6
7

4.4 Hive Windows Functions窗口函数

概述

• 窗口函数也叫开窗函数、OLAP函数，最大的特点是输入值是从select语句的结果集中的一行或多行的“窗口”中获取
• 如果函数具有OVER子句，则它是窗口函数
• 窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过group by子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行，窗口函数聚合后还可以访问当中的各个行，并且将这些行中的某些属性添加到结果集中

-- 建表和加载数据
create table employee(
    id int,
    name string,
    deg string,
    salary int,
    dept string
) row format delimited
fields terminated by ',';
-- load加载数据

-- sum+group by
select dept,sum(salary) as total from employee group by dept;
-- sum+窗口函数聚合操作
select id,name,deg,salary,dept,sum(salary) over(partition by dept) as total from employee;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

语法规则

Function(arg1,...,argn) OVER ([PARTITION BY <...>] [ORDER BY <...>] [<window_expression>])
-- 其中Function(arg1,...,argn) 可以是下面分类中的任意一个
    -- 聚合函数：比如sum max avg等
    -- 排序函数：比如rank row_number等
    -- 分析函数：比如lead lag first_value等

-- OVER [PARTITION BY <...>] 类似于group by用于指定分组 每个分组可以叫做窗口
-- 如果没有PARTITION BY 那么整张表所有行就是一组

-- [ORDER BY <...>] 用于指定每个分组内的数据排序规则 支持ASC、DESC

-- [<window_expression>] 用于指定每个窗口中操作的数据范围 默认是窗口中所有行
1
2
3
4
5
6
7
8
9
10
11
12

4.4.1 练习：网站用户页面浏览次数分析

再网站访问中，经常使用cookie来标识不用的用户身份，通过cookie可以追踪不同用户的页面访问情况
通过用户再网站的访问数据学习Hive中窗口函数的相关语法知识

语法练习环境

两份数据
1.字段含义：cookieid、访问时间、pv数（页面浏览数）
cookie1,2018-04-10,5
2.字段含义：cookieid、访问时间、访问页面url
cookie1,2018-04-10 10:00:02,url2
1
2
3
4
5

建表加载

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 int
) row format delimited
fields terminated by ',';
1
2
3
4
5
6
7
8
9
10
11
12
13

(1)窗口聚合函数
所谓窗口聚合函数，指的是sum、max、min、avg这样的聚合函数在窗口中的使用
这里以sum()函数为例，其他聚合函数使用类似

-- 1. 求出每个用户总pv数 sum + group by普通常规聚合操作
select cookieid,sum(pv) as total_pv from website_pv_info group by cookieid;

-- 2.sum+窗口函数 总共有四种用法 注意是整体聚合 还是累积聚合
-- sum(...) over() 对表所有行求和
-- sum(...) over(order by ...) 连续累积求和
-- sum(...) over(partition by ...) 同组内所有行求和
-- sum(...) over(partition by ... order by ...) 在每个分组内，连续累积求和
1
2
3
4
5
6
7
8

(2)窗口表达式

• 在sum(…) over(partition by… order by…)语法完整的情况下，进行累积聚合操作，默认累积聚合行为是从第一行聚合到当前行
• window expression窗口表达式给我们提供了一种控制行范围的能力，比如向前2行，向后3行
• 语法如下

关键字是rows between，包括下面这几个选项
preceding：向前
following：向后
current row：当前行
unbounded：边界
unbounded preceding：表示从前面的起点
unbounded following：表示到后面的终点
1
2
3
4
5
6
7

-- 窗口表达式
select cookieid,createtime,pv,
    sum(pv) over(partition by cookieid order by createtime) as pv1 -- 默认从第一行到当前行
from werbsite_pv_info;
    
-- 第一行到当前行
select cookieid,createtime,pv,
    sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from werbsite_pv_info;

-- 向前3行至当前行
select cookieid,createtime,pv,
    sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv3
from werbsite_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 pv4
from werbsite_pv_info;

-- 当前行至最后一行
select cookieid,createtime,pv,
    sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv5
from werbsite_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 werbsite_pv_info;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

(3)窗口排序函数 row_number家族
row_number：每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复
rank：在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置
dense_rank：在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置

-- 窗口排序函数
select cookieid,createtime,pv,
    RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS m1,
    DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS m2,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS m3
from website_pv_info
where cookieid = 'cookie1';
1
2
3
4
5
6
7

(3)窗口排序函数 ntile

• 将每个分组内的数据分为指定的若干个桶里，并且为每一个桶分配一个桶编号
• 如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1

-- 把每个分组内的数据分为3桶
select cookieid,createtime,pv,
    NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS m2
from website_pv_info
ORDER BY cookieid,createtime;
1
2
3
4
5

(4)窗口分析函数

• LAG(col,n,DEFAULT) 用于统计窗口内网上第n行值，第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当网上第n行为null时，取默认值，如不指定，则为null）
• LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
• 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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14

4.5 抽样函数

概述

• 当数据量过大时，需要找到数据子集以加快数据处理速度分析
• 这就时抽样、采样，一种用于识别和分析数据中的子集的技术，以发现整个数据集中的模式和趋势
• 在HQL中，可以通过三种方式采样数据：随机采样，存储桶表采样和块采样

(1)Random随机抽样
随机抽样使用rand()函数来确保随机获取数据，LIMIT来限制抽取的数据个数
优点时随机，缺点是速度不快，尤其表数据多的时候
1.推荐DISTRIBUTE+SORT，可以确保数据也随机分部在mapper和reducer之间，使得底层执行有效率
2.ORDER BY语句也可以达到相同的目的，但是表现不好，因为ORDER BY时全局排序，指挥启动运行一个reducer

-- 需求：随机抽取2个学生的情况进行查看
select * from student
distribute by rand() sort by rand() limit 2;

-- 使用order by + rand也可以实现相同的效果 但是效率不高
select * from student
order by rand() limit 2;
1
2
3
4
5
6
7

(2)Block基于数据块抽样

• Block块采样允许随机获取n行数据、百分比数据或指定大小的数据
• 采样粒度时HDFS块大小
• 优点是速度块，缺点是不随机

-- block抽样
-- 根据行数抽样
select * from student TABLESAMPLE(1 ROWS);

-- 根据数据大小百分比抽样
select * from student TABLESAMPLE(50 PERCENT);

-- 根据数据大小抽样
-- 支持数据单位 b/B k/K m/M g/G
select * from student TABLESAMPLE(1k);
1
2
3
4
5
6
7
8
9
10

(3)Bucket table基于分桶表的抽样
这是一种特殊的抽样方法，针对分桶表进行了优化，优点是既随机也很快
语法如下

TABLESAMPLE(BUCKET x OUT OF y [ON colname])

-- 1.y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例
    -- 例如，table总共分了4分（4个bucket），当y=2时，抽样（4/2）=2个bucket的数据，当y=8时，抽取（4/2）=1/2个bucket的数据
-- 2.x表示从哪个bucket开始抽取
    -- 例如，table总bucket数为4，tablesample(bucket 4 out of 4),表示总共抽取（4/4=）1个bucket的数据，抽取第4个bucket的数据
    -- 注意：x的值必须小于等于y的值，否则报错
-- 3.ON colname表示基于什么抽
    -- ON rand() 表示随机抽
    -- ON 分桶字段 表示基于分桶字段抽样 效率更高 推荐
1
2
3
4
5
6
7
8
9
10

-- bucket table抽样
-- 根据整行数据进行抽样
select * from t_usa_covid19_bucket TABLESAMPLE(BUCKET 1 OUT OF 2 ON rand());

-- 根据分桶字段进行抽样
describe formatted t_usa_covid19_bucket;
select * from t_usa_covid19_bucket TABLESAMPLE(BUCKET 1 OUT OF 2 ON state);
1
2
3
4
5
6
7