[Hive_12] Hive 的自定义函数

 


 

0. 说明 

  UDF   //user define function
      //输入单行,输出单行,类似于 format_number(age,'000')

  UDTF   //user define table-gen function
         //输入单行,输出多行,类似于 explode(array);

  UDAF   //user define aggr function
         //输入多行,输出单行,类似于 sum(xxx)

 

  Hive 通过 UDF 实现对 temptags 的解析

 


1. UDF

  1.1 代码示例

  Code

 

  1.2 用户自定义函数的使用

  1. 将 Hive 自定义函数打包并发送到 /soft/hive/lib 下
  2. 重启 Hive
  3. 注册函数

# 永久函数
  create function myudf as 'com.share.udf.MyUDF';

# 临时函数
  create temporary function myudf as 'com.share.udf.MyUDF';

 

  1.3 Demo

  Hive 通过 UDF 实现对 temptags 的解析

  0. 准备数据

  1. 建表

    create table temptags(id int,json string) row format delimited fields terminated by '\t';

  2. 加载数据

    load data local inpath '/home/centos/files/temptags.txt' into table temptags;

  3. 代码编写

  Code

  4. 打包

  5. 添加 fastjson-1.2.47.jar & myhive-1.0-SNAPSHOT.jar 到 /soft/hive/lib 中

  6. 重启 Hive

  7. 注册临时函数

    create temporary function parsejson as 'com.share.udf.ParseJson';

  8. 测试

select id ,parsejson(json) as tags from temptags;

 

# 将 id 和 tag 炸开
select id,  tag from temptags lateral view explode(parsejson(json)) xx as tag;

# 开始统计每个商家每个标签个数
select id, tag, count(*) as count
from (select id, tag from temptags lateral view explode(parsejson(json)) xx as tag) a
group by id, tag; # 进行商家内标签数的排序 select id, tag , count, row_number()over(partition by id order by count desc) as rank
from (select id, tag, count(*) as count from (select id, tag from temptags lateral view explode(parsejson(json)) xx as tag) a
group by id,tag) b ; # 将标签和个数进行拼串,取得前 10 标签数 select id, concat(tag,'_',count)
from (select id, tag , count, row_number()over(partition by id order by count desc) as rank
from (select id, tag, count(*) as count from (select id, tag from temptags lateral view explode(parsejson(json)) xx as tag) a
group by id,tag) b )c
where rank<=10; #聚合拼串 //concat_ws(',', List<>) //collect_set(name) 将所有字段变为数组,去重 //collect_list(name) 将所有字段变为数组,不去重 select id, concat_ws(',',collect_set(concat(tag,'_',count))) as tags
from (select id, tag , count, row_number()over(partition by id order by count desc) as rank
from (select id, tag, count(*) as count from (select id, tag from temptags lateral view explode(parsejson(json)) xx as tag) a
group by id,tag) b )c where rank<=10 group by id;

 

   1.4 虚列:lateral view

  123456 味道好_10,环境卫生_9

  id   tags
  1   [味道好,环境卫生]   =>   1 味道好
                      1 环境卫生

 

select name, workplace from employee lateral view explode(work_place) xx as workplace;

 

 

 

 

 


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 


 

上一篇:Fortinet检测命令控制——就是通过心跳,最短60s,最长1天的周期,检测偏离度0.2


下一篇:攻防世界misc 坚持60s