hive总结(收藏版)

最近在写hive踩了坑，把hive函数汇总在此，做个工具笔记。
一、时间差计算
1、计算两个日期的天数差
select datediff(from_unixtime(unix_timestamp(‘2020-10-31 00:00:00’),‘YYYY-MM-dd’),concat(substr(“20201001”,1,4),‘-’,substr(“20201001”,5,2),‘-’,substr(“20201001”,7,2)))

2、计算两个时间相差的秒数
select unix_timestamp(end_time,‘yyyy-MM-dd HH:mm:ss’)-unix_timestamp(create_time,‘yyyy-MM-dd HH:mm:ss’)
3、计算两个时间的分钟差
select (unix_timestamp(end_time,‘yyyy-MM-dd HH:mm:ss’)-unix_timestamp(create_time,‘yyyy-MM-dd HH:mm:ss’))/60
二、分层汇总

1、关于分层汇总的函数有三个，with rollup,with cube, grouping sets。

有时候我们做一些数据经营分析需要用到分层汇总时，涉及到维度的交叉汇总，全量的交叉一般是with cube,但是有些维度之间是映射（或者包含）关系，这些维度之间用rollup那种交叉即可。

那么，下面主要讲讲 ，如何通过grouping
sets实现，非包含关系的维度之间交叉汇总，包含关系的维度映射汇总。

下面贴两张图来说明上面的意思。

像这种就是非包含关系的维度之间的交叉汇总。

像这种就是包含关系的维度，IC姓名（个人）是归属于某个IC团队，效果是选了IC团队的全部，ic姓名就不会出现明细，也就是说要选某个具体的IC姓名，要先选对应的IC团队。

Select 
	nvl(collect_type_id,-1) as collect_type_id,
	Nvl(ic_team_id,-1) as ic_team_id,
	Nvl(ic_id,-1) as ic_id,
	Count(distinct uid) as user_cnt  
from table 
group by collect_type_id,ic_team_id,ic_id 
grouping sets ( 
(), 
(collect_type_id),
(collect_type_id,ic_team_id),
(collect_type_id,ic_team_id,ic_id),

(ic_team_id),
(ic_team_id,ic_id)
)

总结：先理清楚非包含关系的维度有几维，先把包含关系的交叉写好，往上编排，比如上面先写好grouping sets 最后两行，代表ic这个维度的，然后跟募集类型进行分别交叉。
记住，募集类型跟ic_id的交叉必须带上ic_team_id。如果再加上是否清仓维度，代码就如下：

Select 
	nvl(is_liquid,-1) as is_liquid,
	 nvl(collect_type_id,-1) as collect_type_id,
	 Nvl(ic_team_id,-1) as ic_team_id,
	 Nvl(ic_id,-1) as ic_id,
	 Count(distinct uid) as user_cnt  
from table 
group by
is_liquid,collect_type_id,ic_team_id,ic_id

grouping sets (
(),
(is_liquid),
(is_liquid,collect_type_id),
(is_liquid,collect_type_id,ic_team_id),
(is_liquid,collect_type_id,ic_team_id,ic_id),

(collect_type_id),
(collect_type_id,ic_team_id),
(collect_type_id,ic_team_id,ic_id),

(ic_team_id),
(ic_team_id,ic_id)
)

三、关于hdfs文件
hive表有几种存储格式，详细见链接
https://blog.csdn.net/weixin_52851967/article/details/127348999
其中，我们建立数仓表一般是存储成orc格式，因为它的hdfs文件进行了压缩，大大地减少了存储空间的消耗，但也正是这点，ord格式的hdfs文件的内容是加密的，但是如果存储成textfile的hdfs文件就是明文的,所以orc格式不适合本地文件上传，textfile文件则适合。