Hive超常用的十个转换小技巧

  • 1、数据脱敏函数
  • 2、hive 获取当天时间
  • 3、hive 格式化时间数据
  • 4、hive 获取本月第一天，本年第一天，上个月第一天，本月最后一天，下个月第一天等指标
  • 5、datediff日期比较函数第一个参数是结束日期，第二个是开始日期，返回结束日期减开始日期
  • 6、hive对yyyy/MM/dd格式的日期和yyyy-MM-dd格式的日期相互转换方案
  • 7、hive的多行转多列
  • 8、hive查找数组内是否包含某个元素
  • 9、hive字符串数组类型的数据转为字符串数据
  • 10、hive的空处理函数，coalesce(数据字段,'自定义值')


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

2、hive 获取当天时间

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

3、hive 格式化时间数据

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

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

select trunc(from_unixtime(unix_timestamp() + 8*3600),'MM') -- 2022-10-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-09-01
select last_day(from_unixtime(unix_timestamp() + 8*3600)) -- 2022-10-30
select trunc(add_months(from_unixtime(unix_timestamp() + 8*3600),1),'MM') -- 2022-11-01

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

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

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

7、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

8、hive查找数组内是否包含某个元素

注意：array_contains()函数支持int数组或者string数组，不支持bigint数据类型的数组。

select array_contains(array<int>,某元素);

9、hive字符串数组类型的数据转为字符串数据

select concat_ws(',',array<string>);`

10、hive的空处理函数，coalesce(数据字段,‘自定义值’)

select coalesce(aaa,'空值清洗')