大数据小江

V1

2022/08/30阅读:14主题:默认主题

「Hive进阶篇」HQL 高级巧用

进入主页,点击右上角“设为星标” 比别人更快接收好文章

我是Akin,给大家整理了一些hive函数的高级用法,作为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(1as 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(1as 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<int>,某元素); 注意:array_contains()函数支持int数组或者string数组,不支持bigint数据类型的数组。
  2. hive字符串数组类型的数据转为字符串数据 select concat_ws(',',array<string>);
  3. hive的空处理函数,coalesce(数据字段,'自定义值') select coalesce(aaa,'空值清洗')

分类:

后端

标签:

大数据

作者介绍

大数据小江
V1