hive重点内容汇总

前言

  
这篇文章一方面是帮助我记忆常用函数，如果工作中忘了不用百度，直接翻这篇文章就好。另一方面也想分享给各位，大家一起学习。

函数

from_unixtime : 时间戳转日期

  
使用格式：
from_unixtime(10位时间戳,'想转成的日期格式')

举例：

select from_unixtime(1686454221,'yyyy-MM-dd HH:mm:ss');	-- 2023-06-11 11:30:21

日期格式写'年-月-日 时:分:秒'和不写日期格式的效果是一样的

select from_unixtime(1686454221);	-- 2023-06-11 11:30:21

select from_unixtime(1686454221,'yyyy-MM-dd');	-- 2023-06-11

上面都是10位时间戳的情况，
from_unixtime()不能处理13位时间戳，需要转换成10位后计算
转化方式有两种，

方法一：

cast(1660307098908/1000 as bigint)	-- 1660307098

方法二：

substr(1660307098908,1,10)	-- 1660307098

里面时间戳是string和bigint都可以，因为hive会自动类型转换

unix_timestamp : 日期转时间戳

  
使用格式：
unix_timestamp(日期,'待转换日期的格式')

举例：

select unix_timestamp('2023-06-11 11:22:00','yyyy-MM-dd HH:mm:ss');	-- 1686453720

select unix_timestamp('2023-06-11','yyyy-MM-dd');	-- 1686412800

  
年-月-日 时:分:秒可以不写日期格式，但年-月-日一定要写

select unix_timestamp('2023-06-11 11:22:00');    -- 1686453720

select unix_timestamp('2023-06-11');    -- null

current_date : 当前时间 / unix_timestamp : 当前时间戳

  
使用格式：
current_date() 当前时间
unix_timestamp() 当前时间戳

举例：

select current_date ();	-- 2023-06-11

select unix_timestamp();	-- 1686462398

to_date : 返回日期中的年月日

  
使用格式：
to_date('时间')

举例：

select to_date('2023-06-11 11:22:00');	-- 2023-06-11

datediff : 日期之差 / date_add、date_sub : 日期加减

  
使用格式：
datediff(日期1,日期2) 日期1-日期2
date_add(日期,数字) 日期+数字
date_sub(日期,数字) 日期-数字

举例：

select datediff('2023-06-11','2023-06-10');	-- 1

select date_add('2023-06-11',2);    -- 2023-06-13

select date_sub('2023-06-11',2);    -- 2023-06-09

  
这三个函数都是拿日期中的年月日计算的，不涉及时分秒

select datediff('2023-06-11 18:22:00','2023-06-11 10:22:00');	-- 0

select date_add('2023-06-11 18:22:00',2);	-- 2023-06-13

select date_sub('2023-06-11 18:22:00',2);	-- 2023-06-09

加上时分秒后返回的照样只有年月日。有可能是自动进行了to_date，这个回头看源码了解

collect_set、collect_list : 列转行函数

  
使用格式：
配合 group by 使用，把一列数据转换成一个字段 (一行) ，字段类型是数组
collect_set ：数组值 去重
collect_list ：数组值 不去重

举例：

select  name
        ,collect_set(course)	-- 重点！
from    (	-- 这个from不用看，造假数据用
    select  '张三' as name
            ,'语文' as course
    union all 
    select  '张三' as name
            ,'数学' as course
    union all 
    select  '张三' as name
            ,'英语' as course
    union all 
    select  '张三' as name
            ,'数学' as course
    union all 
    select  '李四' as name
            ,'语文' as course
    union all 
    select  '李四' as name
            ,'数学' as course
    union all 
    select  '李四' as name
            ,'英语' as course
    union all 
    select  '李四' as name
            ,'语文' as course
    union all 
    select  '李四' as name
            ,'英语' as course
) t
group by name
;

select  name
        ,collect_list(course)	-- 重点！
from    (	-- 这个from不用看，造假数据用
    select  '张三' as name
            ,'语文' as course
    union all 
    select  '张三' as name
            ,'数学' as course
    union all 
    select  '张三' as name
            ,'英语' as course
    union all 
    select  '张三' as name
            ,'数学' as course
    union all 
    select  '李四' as name
            ,'语文' as course
    union all 
    select  '李四' as name
            ,'数学' as course
    union all 
    select  '李四' as name
            ,'英语' as course
    union all 
    select  '李四' as name
            ,'语文' as course
    union all 
    select  '李四' as name
            ,'英语' as course
) t
group by name
;

concat、concat_ws : 字符串拼接函数

  
使用格式：
concat(str1, str2, str3, …)
concat_ws('分隔符', str1, str2, …)

举例：

select concat('1', '+', '1', '=', '2');	-- 1+1=2

select concat_ws('-', 'yyyy', 'MM', 'dd');  -- yyyy-MM-dd

分隔符不同时用concat，分隔符相同时用concat_ws

  
扩充：
一般collect_set后外面包一层concat_ws：数组转字符串

select  name
        ,concat_ws(',', collect_set(course))	-- 重点！
from    (   -- 这个from不用看，造假数据用
    select  '张三' as name
            ,'语文' as course
    union all 
    select  '张三' as name
            ,'数学' as course
    union all 
    select  '张三' as name
            ,'英语' as course
    union all 
    select  '张三' as name
            ,'数学' as course
    union all 
    select  '李四' as name
            ,'语文' as course
    union all 
    select  '李四' as name
            ,'数学' as course
    union all 
    select  '李四' as name
            ,'英语' as course
    union all 
    select  '李四' as name
            ,'语文' as course
    union all 
    select  '李四' as name
            ,'英语' as course
) t
group by name
;

数组变字符串了

size : 判断array或map数据的大小

  
使用格式：
size(array/map)

举例：

select size(array("英语","数学","语文"));   -- 3
select size(str_to_map('"英语":90, "数学":70, "语文":80'));    -- 3
select size(array(null));   -- 1

当array或者map为null时，size的值为1

cast : 转换数据类型

  
使用格式：
cast(XX as 数据类型)

举例：

select  cast(3.14159265358979 as bigint);   -- 3

一般常用在时间戳转日期中，13位时间戳要先变成10位，才能再用from_unixtime()

select  from_unixtime(cast(1660307098908/1000 as bigint), 'yyyy-MM-dd'); -- 2022-08-12

grouping sets : 替代union all的优化

  
使用格式：
group by… grouping sets((),(),())

举例：

明细

  
想出的指标

  
grouping sets优化写法

select  nvl(`学校`, '全部')
        ,nvl(`班级`, '全部')
        ,nvl(`性别`, '全部')
        ,sum(`过六级人数`)
from    t
group by `学校`, `班级`, `性别` grouping sets((), (`学校`), (`学校`, `班级`))
;

  
与传统写法对比

select  '全部' as `学校`
        ,'全部' as `班级`
        ,'全部' as `性别`
        ,sum(`过六级人数`)
from    t
union all 
select  `学校`
        ,'全部' as `班级`
        ,'全部' as `性别`
        ,sum(`过六级人数`)
from    t
group by `学校`
union all 
select  `学校`
        ,`班级`
        ,'全部' as `性别`
        ,sum(`过六级人数`)
from    t
group by `学校`, `班级`
;

  
如果维度多了，用union all得累死。所以 grouping sets 在大数据中至关重要。

  
PS：with cube是grouping sets的穷举版本，如果想要所有维度组合，用grouping sets太麻烦了，可以用with cube代替。

例：

group by school, class with cube

等于

group by school, class grouping sets((), (school), (class), (school, class))

get_json_object : 解析json字符串

  
使用格式：
get_json_object(json_string, '$.key')

举例：

select  get_json_object('{"hi":"hi", "你好":"你好", "hello":"hello", "hi你好":"hi你好"}', "$.hi");  -- hi
select  get_json_object('{"hi":"hi", "你好":"你好", "hello":"hello", "hi你好":"hi你好"}', "$.你好");  -- 你好

  
如果匹配不上，那就返回null

select  get_json_object('{"hi":"hi", "你好":"你好", "hello":"hello", "hi你好":"hi你好"}', "$.张三");  -- null

  
这个函数只能返回一个值

select  get_json_object('{"hi":"hi", "hi":"hihi", "你好":"你好", "hello":"hello", "hi你好":"hi你好"}', "$.hi");  -- hi

  
这个函数匹配不上中文，key是中文匹不上
可以用regexp_replace转个key名再匹

split : 按指定分隔符分割字符串

  
使用格式：
split(字符串, 分隔符)

举例：

select  split('2023-06-14', '-');   -- ["2023","06","14"]

小技巧

where里面用if : 搭配变量实现动态设置where条件

  
使用格式：
where if(判断, 式1, 式2) 满足判断返回表达式1，否则返回式2
if(判断, 式1, 1=1) 满足条件返回表达式1，否则什么都不生效

t1 join t2 on 1 = 1 : 笛卡尔积

  
因为没有连接条件，所以 t1 的每一列都会跟 t2 的所有列关联上

开窗直接order by : 窗口大小从第一行到最后一行，最大窗口

  
使用格式：
row_number() over(order by ...)

这样会得到一列自增列，可以定位到具体的数据条数

分清

hive执行顺序

  
hive的执行顺序也就是mapreduce的执行顺序

有两个select，作用不同
from... where... join on... select(查全表内容,不是自己sql写的select)... group by... select(执行自己sql写的select)... having... distinct... order by... limit... union...

  
看这个就行了
from... where... join on... group by... select... having... distinct... order by... limit... union...

group by 的字段，必须是表中的字段；having 的字段，必须是 select 的字段

各种 join 叫法区分 （inner join、XX outer join）

  
join = inner join

left join = left outer join

right join = right outer join

full join = full outer join
  

• full outer join ：左表和右表的数据全部返回，不满足条件的用null填充。所以有可能左边一堆null，右边一堆null

substr 和 substring

  
使用格式：
第一个参数含义都一样 —— 截取的开始位置
substr(startIndex,lenth) : 第二个参数是截取字符串的长度（从起始点截取某个长度的字符串）
substring(startIndex, endIndex) : 第二个参数是截取字符串最终的下标 （截取2个位置之间的字符串，含头不含尾）

  
但有的sql，比如prestosql，substring就等于hive的substr。所以函数功能和名字可能都有些差异，这个因使用的工具而定。文章来源地址https://www.toymoban.com/news/detail-497448.html

到了这里，关于hive重点内容汇总的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！