Hive中常用日期函数整理 hive中的日期时间函数包括

转载

mob6454cc6f6c1c 2024-03-10 22:47:07

文章标签 Hive中常用日期函数整理 hive 大数据 hadoop 数据 文章分类 Hive 大数据

引言

函数

时间函数

排序函数

字符串操作函数

UDTF函数

UDAF函数

多维分析函数

逻辑判断函数

引言

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相差的天数

Hive中常用日期函数整理 hive中的日期时间函数包括_数据_06

Hive中常用日期函数整理 hive中的日期时间函数包括_hadoop_07

初步验证了下，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

UDTF函数

1、explode(列表数据)【posexplode(列表数据)】：行转列函数，单独使用的时候仅能返回一列的列表中的数据。

Lateral View 用于和 UDTF 函数（explode、split）结合来使用。
首先通过 UDTF 函数拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
语法：
LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)
用法1：explode

select 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,  -- idtab.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的区别，就很容易区分这两个函数了。

多维分析函数

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 代码关联示例：

Hive中常用日期函数整理 hive中的日期时间函数包括_数据_25

Hive中常用日期函数整理 hive中的日期时间函数包括_数据_26

特点：

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。

Hive中常用日期函数整理 hive中的日期时间函数包括_hadoop_27

窗口函数

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中常用日期函数整理 hive中的日期时间函数包括_hive_28