Hive之函数

最新推荐文章于 2022-05-01 21:36:05 发布
最新推荐文章于 2022-05-01 21:36:05 发布
阅读量754 收藏 2
点赞数
分类专栏： Hive 文章标签： hive hadoop 数据仓库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lslslslslss/article/details/122106149
版权

目录

一、系统内置函数

二、常用内置函数

1.空字段赋值

2.case语句

3.行转列

4.列转行

5.窗口函数

RANK函数

6.自定义函数以及举例

一、系统内置函数

日期类
unix_timestamp:返回指定时间的时间戳
select unix_timestamp('2021-12-23 12-51-25','yyyy-MM-dd HH-mm-ss');
from_unixtime：将时间戳转化为具体时间 select from_unixtime(1640263885);
current_date：当前日期 select current_date;
date_formate: 转换格式 select date_format('2021-12-27','yyyy-MM')
current_timestamp：当前的日期加时间带毫秒
to_date：抽取日期部分 select to_date('2021-12-23 13-59-56'); 
year：获取年            select year('2021-12-23 13-59-56');                                               
month：获取月                                                         
day：获取日                                                              
hour：获取时                                                            
minute：获取分                                                        
second：获取秒 
weekofyear：当前时间是一年中的第几周 select weekofyear('2021-12-31');
dayofmonth：当前时间是一个月中的第几天 select dayofmonth('2021-12-23');
months_between： 两个日期间的月份 select months_between('2021-12-23','2022-2-5');后面减前面
add_months：日期加减月 select add_months('2021-10-2',-2);
datediff：两个日期相差的天数 select datediff('2021-2-5','2021-12-6'); 前面减后面
date_add：日期加天数 select date_add('2021-8-8',5);
date_sub：日期减天数  select date_add('2021-8-8',-5);
last_day：日期的当月的最后一天 select last_day('2021-12-5');
取整类：
round： 四舍五入 select round(2.3);
ceil：  向上取整
floor： 向下取整 
字符串操作函数
upper： 转大写 select upper('ssss');
lower： 转小写
length： 长度 select length('ssss');
trim：  前后去空格 select trim('   ls   ');
lpad： 向左补齐，到指定长度 select lpad('ls',5,'@');
rpad：  向右补齐，到指定长度
regexp_replace：替换 select regexp_replace('ababab','b','c');
集合操作
size： 集合中元素的个数
map_keys： 返回map中的key
map_values: 返回map中的value 
array_contains: 判断array中是否包含某个元素
sort_array： 将array中的元素排序

二、常用内置函数

1.空字段赋值

    NVL：给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值，否则返回value的值，如果两个参数都为NULL ，则返回NULL。
select id,nvl(id, -1) from stu; //如果id为NULL，则返回-1

2.case语句

具体格式：CASE WHEN THEN ELSE END

需求：

name

dept_id

sex

悟空

A

男

大海

A

男

宋宋

B

男

凤姐

A

女

婷姐

B

女

婷婷

B

女

得到：

+------------+-------+---------+
| t.dept_id  | male  | female  |
+------------+-------+---------+
| A          | 2     | 1       |
| B          | 1     | 2       |
+------------+-------+---------+

步骤：

select
    dept_id,
    case when '男' then 1 else 0 end male,
    case when '女' then 1 else 0 end female,
from emp_sex
+----------+-------+---------+
| dept_id  | male  | female  |
+----------+-------+---------+
| A        | 1     | 0       |
| A        | 1     | 0       |
| B        | 1     | 0       |
| A        | 0     | 1       |
| B        | 0     | 1       |
| B        | 0     | 1       |
+----------+-------+---------+

select 
    t.dept_id,
    sum(t.male) male,
    sum(t.female) female
from (select
    dept_id,
    case sex when '男' then 1 else 0 end,
    case sex when '女' then 1 else 0 end 
from emp_sex) t 
group by t.dept_id;

+------------+-------+---------+
| t.dept_id  | male  | female  |
+------------+-------+---------+
| A          | 2     | 1       |
| B          | 1     | 2       |
+------------+-------+---------+

select
    dept_id,
    sum(case sex when '男' then 1 else 0 end ) male,
    sum(case sex when '女' then 1 else 0 end ) female
from emp_sex
group by dept_id;

用if
select
  dept_id,
  sum(if(sex='男',1,0)) male,
  sum(if(sex='女',1,0)) female
from emp_sex
group by dept_id

3.行转列

函数：CONCAT(string A, string B) 连接字符串

         CONCAT_WS(separator, str1, str2,...) 用separator连接字符串 注：注意：CONCAT_WS must be "string or array<string>"

         COLLECT_SET() 去重，产生array类型字段

         COLLECT_LIST() 不去重，产生array类型字段

需求：

name

constellation

blood_type

孙悟空

白羊座

A

大海

射手座

A

宋宋

白羊座

B

猪八戒

白羊座

A

凤姐

射手座

A

苍老师

白羊座

B

得到：

射手座,A            大海|凤姐
白羊座,A            孙悟空|猪八戒
白羊座,B            宋宋|苍老师

步骤：

select
    concat(constellation,',',blood_type),
    name
from person_info

select
    t.a,
    concat_ws('|',collect_set(t.name))
from (select
concat(constellation,',',blood_type) a,
name
from person_info) t
group by t.a
+--------+----------+
|  t.a   |   _c1    |
+--------+----------+
| 射手座,A  | 大海|凤姐    |
| 白羊座,A  | 孙悟空|猪八戒  |
| 白羊座,B  | 宋宋|苍老师   |
+--------+----------+

select
 concat(constellation,',',blood_type),
 concat_ws('|',collect_list(name))
from person_info
group by concat(constellation,',',blood_type)

4.列转行

函数：

        Split(str, s)：用分隔符s分割字符串str，转换成array。

        EXPLODE()：将hive一列中复杂的array或者map结构拆分成多行。

        LATERAL VIEW

        用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

        解释：lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

        lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

需求：

movie

category

《疑犯追踪》

悬疑,动作,科幻,剧情

《Lie to me》

悬疑,警匪,动作,心理,剧情

《战狼2》

战争,动作,灾难

得到：

《疑犯追踪》      悬疑
《疑犯追踪》      动作
《疑犯追踪》      科幻
《疑犯追踪》      剧情
《Lie to me》   悬疑
《Lie to me》   警匪
《Lie to me》   动作
《Lie to me》   心理
《Lie to me》   剧情
《战狼2》        战争
《战狼2》        动作
《战狼2》        灾难

步骤：

select
    EXPLODE(split(category,','))
from movie_info
+------+
| col  |
+------+
| 悬疑   |
| 动作   |
| 科幻   |
| 剧情   |
| 悬疑   |
| 警匪   |
| 动作   |
| 心理   |
| 剧情   |
| 战争   |
| 动作   |
| 灾难   |
+------+

select 
movie
from movie_info

+--------------+
|    movie     |
+--------------+
| 《疑犯追踪》       |
| 《Lie to me》  |
| 《战狼2》        |
+--------------+

select
    movie,
    category_name
from movie_info
    lateral view explode(split(category,',')) tmp as category_name
where category_name = '悬疑';
    
+--------------+----------------+
|    movie     | category_name  |
+--------------+----------------+
| 《疑犯追踪》       | 悬疑             |
| 《Lie to me》  | 悬疑             |
+--------------+----------------+

select
    movie,
    category_name
from movie_info
    lateral view explode(split(category,',')) tmp as category_name;
    
+--------------+----------------+
|    movie     | category_name  |
+--------------+----------------+
| 《疑犯追踪》       | 悬疑             |
| 《疑犯追踪》       | 动作             |
| 《疑犯追踪》       | 科幻             |
| 《疑犯追踪》       | 剧情             |
| 《Lie to me》  | 悬疑             |
| 《Lie to me》  | 警匪             |
| 《Lie to me》  | 动作             |
| 《Lie to me》  | 心理             |
| 《Lie to me》  | 剧情             |
| 《战狼2》        | 战争             |
| 《战狼2》        | 动作             |
| 《战狼2》        | 灾难             |
+--------------+----------------+

5.窗口函数

        OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

        CURRENT ROW：当前行

        n PRECEDING：往前n行数据

        n FOLLOWING：往后n行数据

        UNBOUNDED：无边界

        UNBOUNDED PRECEDING 前无边界，表示从前面的起点，

        UNBOUNDED FOLLOWING后无边界，表示到后面的终点

        LAG(col,n,default_val)：往前第n行数据

        LEAD(col,n, default_val)：往后第n行数据

        FIRST_VALUE (col,true/false)：当前窗口下的第一个值，第二个参数为true，跳过空值

        LAST_VALUE (col,true/false)：当前窗口下的最后一个值，第二个参数为true，跳过空值

        NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型。

语法

        函数+窗口  

        函数：代表的计算的逻辑

        窗口：表示函数的计算范围  把窗口数据给到函数做计算 
  函数+over([partition by ...] [order by ...] [窗口子句])  over表示开窗
          over 表示开窗 默认有一个窗口大小 就是所有数据
          partition by 表示根据字段再划分一个细窗口 相同字段进入同一个细窗口里面 每个窗口之间相互独立 窗口子句对于每个细窗口独立生效
          order by 表示窗口内按什么排序 如果只有over 表示直接最大窗口排序 如果有partition by 每个细窗口单独排序
  执行顺序
          1.over
          2.partition
          3.order by
          4.窗口子句
          5.函数
窗口子句默认值：当有order by 但是缺少窗口子句时，范围是上无边界到当前行

                            当order by 和 窗口子句都缺少时，范围是上无边界到下无边界

不支持窗口子句的函数

        rank dense_rank ntile row_number lag lead

注意：

一. 建表的时候    
        1.partitioned by 表示你创建的表为分区表
        2.clustered by 表示你创建表为分桶表
二. 查询语句里排序的四个by
        order by 表示全局排序
        distribute by 以什么分区 sort by 区内排序字段
        cluster by 表示分区排序
三.窗口函数的partition
        partition by 表示 更细窗口划分
        order by 窗口以什么排序

        distribute by sort by  相当于 partition by  order by

实例：

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

具体例子

（1）查询在2017年4月份购买过的顾客及总人数
select
    name,
    count(*) over(rows between UNBOUNDED PRECEDING and current row)
from business
where month(orderdate)=4
group by name;
+-------+-----------------+
| name  | count_window_0  |
+-------+-----------------+
| mart  | 1               |
| jack  | 2               |
+-------+-----------------+
（2）查询所有月购买过的顾客及总人数(不用窗口函数)
select
    date_format(orderdate,'yyyy-MM'),
    collect_set(name),
    size(collect_set(name))
from business
group by date_format(orderdate,'yyyy-MM');
+----------+------------------+------+
|   _c0    |       _c1        | _c2  |
+----------+------------------+------+
| 2017-01  | ["jack","tony"]  | 2    |
| 2017-02  | ["jack"]         | 1    |
| 2017-04  | ["jack","mart"]  | 2    |
| 2017-05  | ["neil"]         | 1    |
| 2017-06  | ["neil"]         | 1    |
+----------+------------------+------+
（3）查询顾客的购买明细及月购买总额
select
    name,
    orderdate,
    cost,
    sum(cost)over(partition by month(orderdate))
from business
+-------+-------------+-------+---------------+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+
| jack  | 2017-01-01  | 10    | 205           |
| jack  | 2017-01-08  | 55    | 205           |
| tony  | 2017-01-07  | 50    | 205           |
| jack  | 2017-01-05  | 46    | 205           |
| tony  | 2017-01-04  | 29    | 205           |
| tony  | 2017-01-02  | 15    | 205           |
| jack  | 2017-02-03  | 23    | 23            |
| mart  | 2017-04-13  | 94    | 341           |
| jack  | 2017-04-06  | 42    | 341           |
| mart  | 2017-04-11  | 75    | 341           |
| mart  | 2017-04-09  | 68    | 341           |
| mart  | 2017-04-08  | 62    | 341           |
| neil  | 2017-05-10  | 12    | 12            |
| neil  | 2017-06-12  | 80    | 80            |
+-------+-------------+-------+---------------+
（4）上述的场景, 将每个顾客的cost按照日期进行累加
select
    name,
    orderdate,
    cost,
    sum(cost)over(partition by name order by orderdate)
from business
+-------+-------------+-------+---------------+
| name  |  orderdate  | cost  | sum_window_0  |
+-------+-------------+-------+---------------+
| jack  | 2017-01-01  | 10    | 10            |
| jack  | 2017-01-05  | 46    | 56            |
| jack  | 2017-01-08  | 55    | 111           |
| jack  | 2017-02-03  | 23    | 134           |
| jack  | 2017-04-06  | 42    | 176           |
| mart  | 2017-04-08  | 62    | 62            |
| mart  | 2017-04-09  | 68    | 130           |
| mart  | 2017-04-11  | 75    | 205           |
| mart  | 2017-04-13  | 94    | 299           |
| neil  | 2017-05-10  | 12    | 12            |
| neil  | 2017-06-12  | 80    | 92            |
| tony  | 2017-01-02  | 15    | 15            |
| tony  | 2017-01-04  | 29    | 44            |
| tony  | 2017-01-07  | 50    | 94            |
+-------+-------------+-------+---------------+
（5）查询顾客购买明细以及上次的购买时间和下次购买时间
select
    name,
    orderdate,
    cost,
    lag(orderdate,1,'0000-00-00')over(partition by name order by orderdate),
    lead(orderdate,1,'9999-99-99')over(partition by name order by orderdate)
from business
+-------+-------------+-------+---------------+----------------+
| name  |  orderdate  | cost  | lag_window_0  | lead_window_1  |
+-------+-------------+-------+---------------+----------------+
| jack  | 2017-01-01  | 10    | 0000-00-00    | 2017-01-05     |
| jack  | 2017-01-05  | 46    | 2017-01-01    | 2017-01-08     |
| jack  | 2017-01-08  | 55    | 2017-01-05    | 2017-02-03     |
| jack  | 2017-02-03  | 23    | 2017-01-08    | 2017-04-06     |
| jack  | 2017-04-06  | 42    | 2017-02-03    | 9999-99-99     |
| mart  | 2017-04-08  | 62    | 0000-00-00    | 2017-04-09     |
| mart  | 2017-04-09  | 68    | 2017-04-08    | 2017-04-11     |
| mart  | 2017-04-11  | 75    | 2017-04-09    | 2017-04-13     |
| mart  | 2017-04-13  | 94    | 2017-04-11    | 9999-99-99     |
| neil  | 2017-05-10  | 12    | 0000-00-00    | 2017-06-12     |
| neil  | 2017-06-12  | 80    | 2017-05-10    | 9999-99-99     |
| tony  | 2017-01-02  | 15    | 0000-00-00    | 2017-01-04     |
| tony  | 2017-01-04  | 29    | 2017-01-02    | 2017-01-07     |
| tony  | 2017-01-07  | 50    | 2017-01-04    | 9999-99-99     |
+-------+-------------+-------+---------------+----------------+
（5）查询顾客每个月第一次的购买时间 和 每个月的最后一次购买时间
select
    name,
    orderdate,
    cost,
    first_value(orderdate)over(partition by name order by month(orderdate)),
    last_value(orderdate)over(partition by name order by month(orderdate))
from business
+-------+-------------+-------+-----------------------+----------------------+
| name  |  orderdate  | cost  | first_value_window_0  | last_value_window_1  |
+-------+-------------+-------+-----------------------+----------------------+
| jack  | 2017-01-05  | 46    | 2017-01-05            | 2017-01-01           |
| jack  | 2017-01-08  | 55    | 2017-01-05            | 2017-01-01           |
| jack  | 2017-01-01  | 10    | 2017-01-05            | 2017-01-01           |
| jack  | 2017-02-03  | 23    | 2017-01-05            | 2017-02-03           |
| jack  | 2017-04-06  | 42    | 2017-01-05            | 2017-04-06           |
| mart  | 2017-04-13  | 94    | 2017-04-13            | 2017-04-08           |
| mart  | 2017-04-11  | 75    | 2017-04-13            | 2017-04-08           |
| mart  | 2017-04-09  | 68    | 2017-04-13            | 2017-04-08           |
| mart  | 2017-04-08  | 62    | 2017-04-13            | 2017-04-08           |
| neil  | 2017-05-10  | 12    | 2017-05-10            | 2017-05-10           |
| neil  | 2017-06-12  | 80    | 2017-05-10            | 2017-06-12           |
| tony  | 2017-01-04  | 29    | 2017-01-04            | 2017-01-07           |
| tony  | 2017-01-02  | 15    | 2017-01-04            | 2017-01-07           |
| tony  | 2017-01-07  | 50    | 2017-01-04            | 2017-01-07           |
+-------+-------------+-------+-----------------------+----------------------+
（6）查询前20%时间的订单信息
select
    name,
    orderdate,
    cost,
    ntile(5)over(order by orderdate) 
from business
+-------+-------------+-------+-----------------+
| name  |  orderdate  | cost  | ntile_window_0  |
+-------+-------------+-------+-----------------+
| jack  | 2017-01-01  | 10    | 1               |
| tony  | 2017-01-02  | 15    | 1               |
| tony  | 2017-01-04  | 29    | 1               |
| jack  | 2017-01-05  | 46    | 2               |
| tony  | 2017-01-07  | 50    | 2               |
| jack  | 2017-01-08  | 55    | 2               |
| jack  | 2017-02-03  | 23    | 3               |
| jack  | 2017-04-06  | 42    | 3               |
| mart  | 2017-04-08  | 62    | 3               |
| mart  | 2017-04-09  | 68    | 4               |
| mart  | 2017-04-11  | 75    | 4               |
| mart  | 2017-04-13  | 94    | 4               |
| neil  | 2017-05-10  | 12    | 5               |
| neil  | 2017-06-12  | 80    | 5               |
+-------+-------------+-------+-----------------+

select
    t.name,
    t.orderdate,
    t.cost
from (select
    name,
    orderdate,
    cost,
    ntile(5)over(order by orderdate) n
from business
) t
where t.n =1
+---------+--------------+---------+
| t.name  | t.orderdate  | t.cost  |
+---------+--------------+---------+
| jack    | 2017-01-01   | 10      |
| tony    | 2017-01-02   | 15      |
| tony    | 2017-01-04   | 29      |
+---------+--------------+---------+
（7）求每个顾客的购买明细以及上一次购买和下一次购买花费的和
select
    name,
    orderdate,
    cost,
    sum(cost)over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING)-cost
from business
+-------+-------------+-------+------+
| name  |  orderdate  | cost  | _c3  |
+-------+-------------+-------+------+
| jack  | 2017-01-01  | 10    | 46   |
| jack  | 2017-01-05  | 46    | 65   |
| jack  | 2017-01-08  | 55    | 69   |
| jack  | 2017-02-03  | 23    | 97   |
| jack  | 2017-04-06  | 42    | 23   |
| mart  | 2017-04-08  | 62    | 68   |
| mart  | 2017-04-09  | 68    | 137  |
| mart  | 2017-04-11  | 75    | 162  |
| mart  | 2017-04-13  | 94    | 75   |
| neil  | 2017-05-10  | 12    | 80   |
| neil  | 2017-06-12  | 80    | 12   |
| tony  | 2017-01-02  | 15    | 29   |
| tony  | 2017-01-04  | 29    | 65   |
| tony  | 2017-01-07  | 50    | 29   |
+-------+-------------+-------+------+

RANK函数

        RANK() 排序相同时会重复，总数不会变

        DENSE_RANK() 排序相同时会重复，总数会减少

        ROW_NUMBER() 会根据顺序计算

需求：

name

subject

score

孙悟空

语文

87

孙悟空

数学

95

孙悟空

英语

68

大海

语文

94

大海

数学

56

大海

英语

84

宋宋

语文

64

宋宋

数学

86

宋宋

英语

84

婷婷

语文

65

婷婷

数学

85

婷婷

英语

78

得到：

name    subject score   rp      drp     rmp
孙悟空  数学    95      1       1       1
宋宋    数学    86      2       2       2
婷婷    数学    85      3       3       3
大海    数学    56      4       4       4
宋宋    英语    84      1       1       1
大海    英语    84      1       1       2
婷婷    英语    78      3       2       3
孙悟空  英语    68      4       3       4
大海    语文    94      1       1       1
孙悟空  语文    87      2       2       2
婷婷    语文    65      3       3       3
宋宋    语文    64      4       4       4

步骤：

select
    name,
    subject,
    score,
    rank()over(partition by subject order by score desc) r1,
    dense_rank()over(partition by subject order by score desc) r2,
    row_number()over(partition by subject order by score desc) r3
from score
+-------+----------+--------+-----+-----+-----+
| name  | subject  | score  | r1  | r2  | r3  |
+-------+----------+--------+-----+-----+-----+
| 孙悟空   | 数学       | 95     | 1   | 1   | 1   |
| 宋宋    | 数学       | 86     | 2   | 2   | 2   |
| 婷婷    | 数学       | 85     | 3   | 3   | 3   |
| 大海    | 数学       | 56     | 4   | 4   | 4   |
| 宋宋    | 英语       | 84     | 1   | 1   | 1   |
| 大海    | 英语       | 84     | 1   | 1   | 2   |
| 婷婷    | 英语       | 78     | 3   | 2   | 3   |
| 孙悟空   | 英语       | 68     | 4   | 3   | 4   |
| 大海    | 语文       | 94     | 1   | 1   | 1   |
| 孙悟空   | 语文       | 87     | 2   | 2   | 2   |
| 婷婷    | 语文       | 65     | 3   | 3   | 3   |
| 宋宋    | 语文       | 64     | 4   | 4   | 4   |
+-------+----------+--------+-----+-----+-----+

计算每个人的总分并排序
select
    name,
    sum(score)over(partition by name) s
from score
group by name, s
+-------+---------------+
| name  | sum_window_0  |
+-------+---------------+
| 大海    | 234           |
| 大海    | 234           |
| 大海    | 234           |
| 婷婷    | 228           |
| 婷婷    | 228           |
| 婷婷    | 228           |
| 孙悟空   | 250           |
| 孙悟空   | 250           |
| 孙悟空   | 250           |
| 宋宋    | 234           |
| 宋宋    | 234           |
| 宋宋    | 234           |
+-------+---------------+

//注意：Hive不允许直接访问非group by字段
select
    t.name,
    t.ss
from(select
    name,
    sum(score)over(partition by name) ss
from score
) t
group by t.name,t.ss

+---------+-------+
| t.name  | t.ss  |
+---------+-------+
| 大海      | 234   |
| 婷婷      | 228   |
| 孙悟空     | 250   |
| 宋宋      | 234   |
+---------+-------+

select
    d.t1,
    d.t2,
    rank()over(order by d.t2 desc rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING)
from (select
    t.name t1,
    t.ss t2
from(select
    name,
    sum(score)over(partition by name) ss
from score
) t
group by t.name,t.ss) d

+-------+-------+----------------+
| d.t1  | d.t2  | rank_window_0  |
+-------+-------+----------------+
| 孙悟空   | 250   | 1              |
| 宋宋    | 234   | 2              |
| 大海    | 234   | 2              |
| 婷婷    | 228   | 4              |
+-------+-------+----------------+

6.自定义函数以及举例

（1）UDF（User-Defined-Function）

        一进一出

（2）UDAF（User-Defined Aggregation Function）

        用户自定义聚合函数，多进一出

        类似于：count/max/min

（3）UDTF（User-Defined Table-Generating Functions）

        用户自定义表生成函数，一进多出

        如lateral view explode()

举例：

        自定义一个UDF实现计算给定基本数据类型的长度

    1.导入依赖

<dependencies>
		<dependency>
			<groupId>org.apache.hive</groupId>
			<artifactId>hive-exec</artifactId>
			<version>3.1.2</version>
		</dependency>
</dependencies>

2.编写代码

package com.hpu;


import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

/**
 * @author LS
 * @date 2021/12/24 18:21
 * @description
 */
public class hive_udf extends GenericUDF {
    //判断传进来的参数的类型和长度 约定返回的数据类型
    @Override
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
        if (arguments.length != 1){
            throw new UDFArgumentLengthException("give one please");
        }
        if (!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)){
            throw new UDFArgumentTypeException(1, "i need primitive type arg");
        }
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }
    //解决具体逻辑的
    @Override
    public Object evaluate(DeferredObject[] arguments) throws HiveException {
        Object o = arguments[0].get();
        if(o==null){
            return 0;
        }
        return o.toString().length();
    }
    //用于获取解释的字符串
    @Override
    public String getDisplayString(String[] strings) {
        return "";
    }
}

3.在hive端步骤

1.创建临时函数
（1）打成jar包上传到服务器/opt/module/hive/datas/udf.jar
（2）将jar包添加到hive的classpath，临时生效
    add jar /opt/module/hive/datas/udf.jar;
（3）创建临时函数与开发好的java class关联
    create temporary function my_len as "com.hpu.hive.hive_udf";
（4）即可在hql中使用自定义的临时函数
    select my_len(aaaa);
（5）删除临时函数
    drop  temporary function my_len;
	注意：临时函数只在当前会话内有效
2.创建永久函数
（1）创建永久函数
    create function my_len2 as "com.hpu.hive.hive_udf" using jar "hdfs://hadoop102:8020/udf/udf.jar";
（2）即可在hql中使用自定义的永久函数 
    select my_len(aaaa);
（3）删除永久函数 
    drop function my_len2;
注意：永久函数跟会话没有关系，创建函数的会话断了以后，其他会话也可以使用。
永久函数创建的时候，在函数名之前需要自己加上库名，如果不指定库名的话，会默认把当前库的库名给加上。
永久函数使用的时候，需要在指定的库里面操作,或者在其他库里面使用的话加上 库名.函数名

勤奋的ls丶
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
HiveSql面试题10--sum(if)统计问题
石榴姐yyds
09-01 6145
0 需求分析 t_order表结构 字段名 含义 oid 订单编号 uid 用户id otime 订单时间（yyyy-MM-dd） oamount 订单金额(元) 所有在2018年1月下过单并且在2月没有下过单的用户，在3月份的下单情况： 目标字段名 含义
HiveSQL——sum(if()) 条件累加
最新发布
爱吃辣条的博客
02-10 2462
HiveSQL——sum(if()) 条件累加
hive if函数_结合实例进行讲解HiveSQL基础语法和常用函数
weixin_39796140的博客
11-21 833
本节使用的表有：（1）user_info表字段有用户ID、姓名、性别、年龄、城市、首次激活时间、等级、两个额外字段。其中extra1和extra2的数据形式如下（字典形式）：user_info表内数据(2)user_trade表字段有：用户姓名、购买数量、价格、支付金额、商品分类、支付时间（时间戳格式）、分表时间tips：看到partition 应该注意到，这是个分区表，在查询的时候需要对dt进行...
hive常用函数整理
破晓的成长之路
11-26 317
常用日期函数 1.unix_timestamp:返回当前或指定时间的时间戳 2.from_unixtime：将时间戳转为日期格式 3.current_date：当前日期 4.current_timestamp：当前的日期加时间 5.to_date：抽取日期部分 6.year：获取年 7.month：获取月 8.day：获取日 9.hour：获取时 10.minute：获取分 11.second：获...
hive07: hive中sql常用函数
yaojaijinhou的博客
12-03 677
1.case..when 数据 求出不同部门男女各多少人 王奔 A 男 娜娜 A 男 宋宋 B 男 凤姐 A 女 热巴 B 女 慧慧 B 女 建表并导入数据 create table tb_emp( name string, dname string, gender string ) row format delimited fields terminated by '\t'; load data local inp...
hive窗口函数
01-09
### Hive窗口函数详解 #### 一、概述 在大数据处理领域，Apache Hive 是一个广泛使用的数据仓库工具，它提供了一种SQL-like的语言——HiveQL，使得用户能够更方便地进行数据汇总、特殊查询和分析。其中，窗口函数...
Hive自定义函数
01-20
【Hive自定义函数】是Hive为了满足用户在处理大数据时遇到的特定业务需求而提供的功能。在Hive的内置函数无法满足这些需求时，用户可以通过编写自定义函数（UDF）进行扩展。UDF全称为User Defined Function，允许...
hive常用函数
04-17
Hive作为一款数据仓库工具，主要用于处理...熟练使用Hive函数，不仅可以提高数据处理的效率，还可以提升数据查询和分析的灵活性。对于数据科学家和工程师而言，深入理解Hive的各种函数对于日常工作的开展至关重要。
Hive 窗口函数
01-07
over()：指定分析函数工作的数据窗口的大小，这个数据窗口大小会随着行的变而变化。over跟在聚合函数后面，只对聚合函数生效。 current row：当前行 n preceding：往前n行数据 n following：往后n行数据 unbounded：...
hive窗口函数.docx
07-08
Hive 窗口函数详解 Hive 窗口函数是 Hive 中的一种强大的分析函数，它可以对数据进行分类、排序、聚合和排名等操作。下面将详细介绍 Hive 窗口函数的语法结构、分类、应用场景和实践练习。 窗口函数语法结构 窗口...
大数据之Hive：hive中的条件判断函数
weixin_43597208的博客
06-02 2730
目录1.if函数2.sum(if())函数2.case when3.nvl函数4.coalesce函数 1.if函数 select a,b,c,if(b>1,1,2) as bb from num1 ; 2.sum(if())函数 hive> select sum(if (data_co_1 > 3,1,0)) count1,sum(if (data_co_2 > 3,1,0)) count2 from data 2.case when hive> select scor
hive向上补齐空值
硅谷工具人
03-08 2488
准备的数据，现在要求将b, c两列中空白的值使用前面的值补充。 补充完的结果需要如下： 第一步： 确定数据是否有行号，如果没有，则自己增加一列行号，如准备数据的a列。如果已经有，此步可忽略。 第二步： 增加两列b1,c1, 将空值设置为0，非空值设置为1。 select a,b,c, case when b = '' or b is null then 0 else 1 end b1,...
Hive之常用函数
zhijunming的博客
08-10 629
Hive常用的内置函数 2.1空字段赋值 1)空字段赋值 nvl() -- 给值为NULL的数据赋值，它的格式是NVL( value，default_value)。它的功能是如果value为NULL，则NVL函数返回default_value的值， select nvl(null,'空值'); 2)if(表达式,default,default) -- if(表达式,value1,value2) 如果表达式为true 则取value1,否则取value2 select if('' is null,'空值',
Hive常用函数
youhuaguo001的专栏
03-06 651
1、数学函数 （1）round：四舍五入 select round(数值,小数点位数); （2）ceil：向上取整 select ceil(45.6); （3）floor：向下取整 select floor(45.6); 2、字符函数 （1）lower：转成小写 select lower('Hive'); --hive （2）upper：转成大写 select l...
hive中的NULL分析
热门推荐
henry286700143的专栏
10-17 1万+
Hive中有种假NULL，它看起来和NULL一摸一样，但是实际却不是NULL。空值NULL在底层默认是用'\N'来存储的，Hive中'\'是转义字符，需要对'\'进行一次转义，所以变成'\\N'。 如果实际想存储'\N'，那么实际查询出来的也是NULL而不是'\N'。 修改默认的NULL表示： alter table test SET SERDEPROPERTIES('serializati
Hive空值的处理
rencheng的博客
02-12 3748
1 Hive 空值问题       Hive的使用中不可避免的需要对null、‘’(空字符串)进行判断识别。但是hive有别于传统的数据库。 (1)不同数据类型对空值的存储规则。      int与string 类型数据存储，null 默认存储为\N。string类型的数据如果为""，存储则是""。      另外往int类型的字段插入数据“”时，结果还是\N。 (2)不同数据类型，空值的查询...
如何在 Hive 中使用最近的值填补到缺失的日期中
雾岛与鲸的博客
08-11 2158
我花了几天的时间试图弄清楚如何在 Hive 中使用最近的值添加到缺失的日期中，但没有成功。 原始表目前看起来像下表： account name,available balance,Date of balance Peter,50000,2021-05-24 Peter,50035,2021-05-25 Peter,50035,2021-05-26 Peter,50610,2021-05-28 Peter,51710,2021-06-01 Peter,53028.1,2021-06-02 Peter,53
Hive 对空值的一些处理方法简单记录
weixin_42804692的博客
05-01 2942
1、处理空值 select nvl() from table; hive空字符串数组和空数组 空字符串数组 ：array(’’) 空数组：array() 如果想查找表中的空字符串数组： 方法：可以使用查找数组中的第一个值是否为空字符串 select classify from table where classify[0]=''; 想查找表中空数组： 方法：使用size判断数据大小是否为0 select classify from table where size(classify)=0; ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值