「Hive 进阶篇」四、HQL 高级巧用

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

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

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

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

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

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 -- 方案三

-- 方案一，利用拼接的方式构造map类型select stat_date    ,event_list['test1'] as test1_cnt    ,event_list['test2'] as test2_cntfrom (    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_cntfrom (    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