Hive SQL 高级函数使用

1. 数据脱敏函数

   hive有专门的脱敏函数供我们使用，就是mask()函数，返回值是string类型，默认需要脱敏的数据中大写字母就自动转换为X，小写字母就自动转换为x，数字就自动转换为n，也可通过mask()函数的参数来自定义转换格式。注意：入参也必须是string类型才不会有隐藏bug

select mask(要加密字段) from 表名                     -- 输出默认脱敏后的结果
select mask(要加密字段,'X','x','#') from 表名         -- 输出自定义脱敏后的结果
select mask_first_n(要加密的字段,n) from 表名         -- 对前n个字符进行脱敏
select mask_last_n(要加密的字段,n) from 表名          -- 对后n个字符进行脱敏
select mask_show_first_n(要加密的字段,n) from 表名    -- 对除了前n个字符之外的字符进行脱敏
select mask_show_last_n(要加密的字段,n) from 表名     -- 对除了后n个字符之外的字符进行脱敏
select mask_hash(字段) from 表名                     -- 对字段进行hash操作，若是非string类型的字段此函数就返回null

1. hive 获取当天时间

-- PS：hive3版本对时间函数`unix_timestamp()`和`from_unixtime()`做了重写，需要加8小时或者减8小时，结果才正确
select current_date -- 2022-06-19
select from_unixtime(unix_timestamp() + 8*3600) --  2022-06-19 15:30:54

1. hive 格式化时间数据

select from_unixtime(unix_timestamp() + 8*3600,'yyyy-MM') -- 2022-06
select date_format(from_unixtime(unix_timestamp()),'yyyy-MM') -- 2022-06

1. hive 获取本月第一天，本年第一天，上个月第一天，本月最后一天，下个月第一天等指标

select trunc(from_unixtime(unix_timestamp() + 8*3600),'MM') -- 2022-06-01
select trunc(from_unixtime(unix_timestamp() + 8*3600),'YEAR'); -- 2022-01-01
select trunc(add_months(from_unixtime(unix_timestamp() + 8*3600),-1),'MM') -- 2022-05-01
select last_day(from_unixtime(unix_timestamp() + 8*3600)) -- 2022-06-30
select trunc(add_months(from_unixtime(unix_timestamp() + 8*3600),1),'MM') -- 2022-07-01

1. datediff日期比较函数第一个参数是结束日期，第二个是开始日期，返回结束日期减开始日期

select datediff('2020-07-05','2020-06-15'); -- 返回20，注意日期格式认准- ，如果是/则无效，得使用格式转换

1. hive对yyyy/MM/dd格式的日期和yyyy-MM-dd格式的日期相互转换方案

• 第一种是通过from_unixtime()+unix_timestamp()转换时间戳方式转换

• 第二种是通过concat()+substr()拼接截取方式转换，

• 第三种是通过regexp_replace()正则匹配方式去掉横杠。

select 
     '2022/08/09' as source_text
    ,from_unixtime(unix_timestamp('2022/08/09','yyyy/MM/dd'),'yyyy-MM-dd') as func_text_1 -- 方案一
    ,concat(substr('2022/08/09',1,4),'-',substr('2022/08/09',6,2),'-',substr('2022/08/09',9,2)) as func_text_2 -- 方案二
    ,regexp_replace('2022/08/09','/','-') as func_text_3 -- 方案三

1. hive的多行转多列

• 方案一：利用拼接的方式构造map类型

• 方案二：利用if判断表达式+聚合收敛

-- 方案一，利用拼接的方式构造map类型
select stat_date
    ,event_list['test1'] as test1_cnt
    ,event_list['test2'] as test2_cnt
from 
(
    select 
         stat_date
        ,str_to_map(concat_ws(',',collect_list(concat_ws(':',event_name,cast(event_cnt as string))))) as event_list
    from
    (
        select 
             stat_date
            ,event_name
            ,count(1) as event_cnt
        from 表名
        where stat_date between 20220801 and 20220810
        and event_name in('test1','test2')
        group by stat_date 
                ,event_name
    ) s 
    group by stat_date
) w 
    
-- 方案二，利用if判断表达式
select 
     stat_date
    ,sum(if(event_name='test1',event_cnt,0)) as test1_cnt
    ,sum(if(event_name='test2',event_cnt,0)) as test2_cnt
from 
(
    select 
         stat_date
        ,event_name
        ,count(1) as event_cnt
    from 表名
    where stat_date between 20220801 and 20220810
    and event_name in('test1','test2')
    group by stat_date 
            ,event_name
) s 
group by stat_date

1. hive查找数组内是否包含某个元素select array_contains(array,某元素); 注意：array_contains()函数支持int数组或者string数组，不支持bigint数据类型的数组。

2. hive字符串数组类型的数据转为字符串数据select concat_ws(',',array);

3. hive的空处理函数，coalesce(数据字段,'自定义值')select coalesce(aaa,'空值清洗')