目录
引言
Hive是基于hadoop的数据仓库工具，能够将一张结构化的数据文件映射为一张数据库表。以便于在Hive中通过类似于传统sql（Hive中我们叫hql）做数据分析等工作。Hive默认计算模型是MapperReduce，将hql转换成MR任务进行计算；在Hive中还有Hive on Spark的模式，这里仅做了解。本文主要是在工作中使用Hive的时候，对一些用到的函数进行记录，以便后续查阅，也供其他同学们参考及互相交流知识。
函数
时间函数
1、unix_timestamp()、unix_timestamp(STRING date)、unix_timestamp(STRING date,STRING pattern)
返回本地时间当前时间搓，date默认格式 yyyy-MM-dd hh:mm:ss，可通过pattern设置时间格式。
示例1：
select unix_timestamp();
示例2：
select unix_timestamp('2019-06-11 10:10:10');
示例3：
select unix_timestamp('20190611','yyyyMMdd');
2、from_unixtime(BIGINT unixtime[,STRING format]) 将时间搓秒数转换成UTC时间
示例：
select from_unixtime(unix_timestamp('20190611' ,'yyyymmdd'), 'yyyy-mm-dd');
3、to_date(string date) ：返回时间字符串日期部分
示例：
select to_date("1993-01-01 00:12:12");
4、datediff(string enddate,string startdate) 返回endDate和startDate相差的天数
初步验证了下，datediff函数只是简单天相减，与小时无关。
排序函数
1、first_value()：取分组排序后，当前行的第一个值。
常用于：首次消费门店，首次消费类字段
last_value()：取分组排序后，当前行的最后一个值
常用于：最近消费门店，最近消费类字段
- 数据准备
shop_order.txt
001,耐克,2019-06-01
002,安踏,2019-06-01
003,李宁,2019-06-02
001,阿迪达斯,2019-06-03
001,李宁,2019-06-05
002,耐克,2019-06-05
003,耐克,2019-06-05
004,乔丹,2019-06-05- hive建表
create table shop_order(
zvip string,
shopname string,
orderdate string
) row format delimited fields terminated by ',';
我这里在测试的时候，将orderdate的数据类型设置为date，但在做查询的时候报错：Underlying error: Primitve type DATE not supported in Value Boundary expression；暂把orderdate类型设置为string类型，仅做了个简单的建表。- 导入数据
hive -e "load data local inpath 'shop_order.txt' into table shop_order"- hql执行函数示例
执行语句1：
select zvip, --会员编号
orderdate,--订单时间
shopname,--门店名称
row_number() over(partition by zvip order by orderdate asc), --开窗函数对分组进行排序
first_value(shopname) over(partition by zvip order by orderdate),-- 首次消费门店，取第一个值
last_value(shopname) over(partition by zvip order by orderdate) -- 最近消费门店，取最后一个值
from shop_order;
结果：
结果分析：可以看到last_value()中的值都不相同，通过查度娘发现last_value()默认统计范围是 rows between unbounded preceding and current row；对语句进行改造。
执行语句2：
select zvip, --会员编号
orderdate,--订单时间
shopname,--门店名称
row_number() over(partition by zvip order by orderdate asc), --开窗函数对分组进行排序
first_value(shopname) over(partition by zvip order by orderdate),-- 首次消费门店，取第一个值
last_value(shopname) over(partition by zvip order by orderdate rows between unbounded preceding and unbounded following) -- 最近消费门店，取最后一个值
from shop_order;
结果：
去掉时间：
结果分析：可以看到添加rows between unbounded preceding and unbounded following后，返回结果接近需要的结果（会员的首次消费门店、最近消费门店），但是此处存在重复记录；可通过group by 语句去重。改造结果如下：
执行语句3：
select zvip,fv,lv from(
select zvip, --会员编号
orderdate,--订单时间
shopname,--门店名称
row_number() over(partition by zvip order by orderdate asc), --开窗函数对分组进行排序
first_value(shopname) over(partition by zvip order by orderdate) fv,-- 首次消费门店，取第一个值
last_value(shopname) over(partition by zvip order by orderdate rows between unbounded preceding and unbounded following) lv -- 最近消费门店，取最后一个值
from shop_order) x
group by zvip,fv,lv;
结果：
结果分析：通过group by去重得出预期结果，但其中会生成2个job，执行时间也相对增加了。
字符串操作函数
1、concat(str1,str2,str3...)：用于将多个函数连接成一个字符串，如果连接中有NULL值，则返回NULL。
示例1：
select concat(zvip,',',shopname) from shop_order;
示例2：
select concat(zvip,',',shopname,NULL) from shop_order;
2、concat_ws(separator,str1,str2,…) ：concat()的特殊形式，第一个参数是其他参数的分隔符，分隔符的位置放在要连接的两个字符之间。分隔符可以是一个字符串。如果分隔符为NULL，则结果返回NULL。如果分隔字符有NULL值，则返回实际拼接结果。
示例1：
select concat_ws('-',shopname,'offline') from shop_order;
示例2：
select concat_ws('-',shopname,'offline',NULL) from shop_order;
示例3：
select concat_ws(null,shopname,'offline') from shop_order;
3、get_json_object(string jsonstr,String parseAtt) ：用来解析json字符串的一个字段。
示例：
select get_json_object('{"zvip":"001","shopname":"李宁","orderDate":"2019-06-11"}','$.zvip'),
get_json_object('{"zvip":"001","shopname":"李宁","orderDate":"2019-06-11"}','$.shopname'),
get_json_object('{"zvip":"001","shopname":"李宁","orderDate":"2019-06-11"}','$.orderDate');
4、json_tuple(string jsonstr,string str1,string str2,...)：用来解析Json字符串的多个字段。
示例：
select b.zvip,b.shopname,b.orderDate from t1 lateral view json_tuple('{"zvip":"001","shopname":"李宁","orderDate":"2019-06-11"}','zvip','shopname','orderDate') b as zvip,shopname,orderDate;
这里仅做了一个json字符串的示例，json_tuple()和get_json_object()这两个函数常用于解析埋点数据上，一般会将json格式的字符串数据存在表中，通过获取表的字段去解析。
5、正则获取数据regexp_extract
CANKHive 正则匹配函数 regexp_extract - Suckseedeva - 博客园
UDTF函数
1、explode(列表数据)【posexplode(列表数据)】：行转列函数，单独使用的时候仅能返回一列的列表中的数据。
Lateral View 用于和 UDTF 函数（explode、split）结合来使用。
首先通过 UDTF 函数拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
语法：
LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)
用法1：explodeselect id, -- id tab.serviceId -- 服务id from ( SELECT 'a001' as id,'IN02,IN67,IN41' as serviceIds union all SELECT 'a002' as id,'IN67,IN02' as serviceIds ) aa lateral view explode(split(serviceIds,',')) tab as serviceId limit 100
用法2：获取对应列的index位置
select id, -- id tab.servIndex, -- 对应索引 tab.serviceId -- 服务id from ( SELECT 'a001' as id,'IN02,IN67,IN41' as serviceIds union all SELECT 'a002' as id,'IN67,IN02' as serviceIds ) aa lateral view posexplode(split(serviceIds,',')) tab as servIndex,serviceId limit 100
或者两个炸裂函数关联：
UDAF函数
1、collect_set()：列转行函数，将相同key，同一列不同value的值转成一行并去重，返回数组格式数据；
collect_list()：列转行函数，将相同key，同一列不同value的值转成一行不去重，返回数组格式数据；
- 数据准备
shop_order.txt
001,耐克,2019-06-01
002,安踏,2019-06-01
003,李宁,2019-06-02
001,阿迪达斯,2019-06-03
001,李宁,2019-06-05
002,耐克,2019-06-05
002,安踏,2019-06-08 (新增一条记录)
003,耐克,2019-06-05
004,乔丹,2019-06-05
【其他步骤同上】- hql执行函数示例
执行语句1：
select zvip,collect_set(shopname) from shop_order group by zvip;
结果：
结果分析：通过collect_set()是对shopname值进行了去重的。
执行语句2：
select zvip,collect_list(shopname) from shop_order group by zvip;
结果：
结果分析：通过collect_list()可知在002会员中shopname的“安踏”出现了两次。如果有开发经验的人可以知道set和list的区别，就很容易区分这两个函数了。
2、相同类型多个字段求最值
在Hive中，可以使用内置函数GREATEST()来求多个字段的数值最大值。以下是使用示例：
SELECT GREATEST(col1, col2, col3) AS max_value
FROM your_table;
其中，col1、col2和col3是要比较的字段，your_table是包含这些字段的表。函数GREATEST()会返回给定字段中的最大值。
如果要求最小值，可以使用内置函数LEAST()。以下是使用示例：
SELECT LEAST(col1, col2, col3) AS min_value
FROM your_table;
以上语句会返回给定字段中的最小值。
多维分析函数
1、with cube多维数据集
1.1 测试数据使用本地模式会节省很多时间。
SET hive.exec.mode.local.auto=true;
SET hive.exec.mode.local.auto.inputbytes.max=50000000;
SET hive.exec.mode.local.auto.input.files.max=5 ;
1.2 、创建测试表
create table tmp.tmp_test(
f1 string ,
f2 string,
f3 string,
cnt int
);
1.3 造数
'A','A','B',1
'B','B','A',1
'A','A','A',2
'A','B','A',2
INSERT OVERWRITE table tmp.tmp_test
select
"A",
"A",
"B",
1
UNION ALL
select
"B",
"B",
"A",
1
UNION ALL
select
"A",
"A",
"A",
2
UNION ALL
select
"A",
"B",
"A",
2;1.4 测试with_cube
select
f1,
f2,
f3,
sum(cnt),
grouping__id,
rpad(reverse(bin(cast(grouping__id AS bigint))),3,'0')
from tmp.tmp_test
group by f1,
f2,
f3 with cube;结果数据：
1.5 多维数据集
CUBE 运算符可用于生成 n 维的多维数据集，即具有任意数目维度的多维数据集。只有一个维度的多维数据集可用于生成合计，例如：
select
nvl(f1,'all') f1,
sum(cnt)
from tmp.tmp_test
group by f1 with cube;结果数据：
1、 with rollup
select
f1,
f2,
f3,
sum(cnt),
grouping__id,
rpad(reverse(bin(cast(grouping__id AS bigint))),3,'0')
from tmp.tmp_test
group by f1,
f2,
f3 with rollup;数据结果：
2、 grouping sets
select
f1,
f2,
f3,
sum(cnt),
grouping__id,
rpad(reverse(bin(cast(grouping__id AS bigint))),3,'0')
from tmp.tmp_test
group by f1,
f2,
f3 grouping sets((f1),(f1,f2));数据结果：
hivesql和sparksql执行引擎运行结果还会不一样，如下图
HiveSQL：按grouping_id的二进制进行反转。grouping_id算法是有值的为1，空值为0，再反转。
SparkSQL：grouping_id算法是有值的为0，空值为1
总结
1、cube的分组组合最全，是各个维度值得笛卡尔（包含null）组合。
2、rollup的各维度组合应满足：前一维度为null则后一位维度必须为null；前一维度取非null时，下一维度随意。
3、grouping sets则为自定义维度，根据需要分组即可。通过grouping sets的使用可以简化SQL，比group by 单维度进行union性能更好。
3、left semi join与inner join区别
LEFT SEMI JOIN (左半连接) 是IN/EXISTS子查询的一种更高效的实现。
示例：
select A.key,A.value from A where A.key in (select B.key from B)
等同于：
select A.key,A.value FROM A left semi join B on (A.key = B.key)
left semi join 与 inner join 代码关联示例 ：
特点：
1、left semi join 是只传递表的join key给map阶段，所以最终的select结果数据只有左表数据。
2、left semi join是in(keySet)的关系，遇到右表重复记录会跳过，而inner join会发散。所以在右表有重复key的情况下，left semi join只产生一条，而inner join 会产生多条。17、炸裂函数laterial view
逻辑函数
1、CAST(<expr> as <type>) 数据类型转换
cast (null as string)
2、 COALESCE(T v1, T v2, …) ：返回第一个非NULL值，如果全部为NULL，则返回NULL。
窗口函数
1、sum() over(order by row between n preceding and current row)
over(
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)<PARTITION BY clause> ::= PARTITION BY value_expression , ... [ n ] <ORDER BY clause> ::= ORDER BY order_by_expression [ COLLATE collation_name ] [ ASC | DESC ] [ ,...n ] <ROW or RANGE clause> ::= { ROWS | RANGE } <window frame extent> <window frame extent> ::= { <window frame preceding> | <window frame between> } <window frame between> ::= BETWEEN <window frame bound> AND <window frame bound> <window frame bound> ::= { <window frame preceding> | <window frame following> } <window frame preceding> ::= { UNBOUNDED PRECEDING | <unsigned_value_specification> PRECEDING | CURRENT ROW } <window frame following> ::= { UNBOUNDED FOLLOWING | <unsigned_value_specification> FOLLOWING | CURRENT ROW } <unsigned value specification> ::= { <unsigned integer literal> }
参考：
Hive中使用sum over()实现累积求和和滑动求和_xuehuagongzi000的博客-CSDN博客_hive 求和
SQL Server 开窗函数over()选项RANGE/ROWS详解 - 五维思考 - 博客园
1117
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
