hive 常用的一些内置函数

谁借莪１个温暖的怀抱￠ 2023-06-08 14:56 30阅读 0赞

### hive 常用的一些内置函数 ###

*  nvl （UDF）
 *  substring （UDF）
 *  if （UDF）
 *  array\_contains（常和 if 一起使用） （UDF）
 *  collect\_set /collect\_list （UDAF）
 *  case when 行列转换时使用 （UDF）
 *  explode （UDTF）
 *  round （UDF）

首先可以查看hive的所有内置函数，通过 `show functions;`语句

hive> show functions;
    OK
    tab_name
    !
    !=
    $sum0
    %
    &
    *
    ...

# nvl （UDF） #

先看官方反馈：

hive> desc function nvl;
    OK
    tab_name
    nvl(value,default_value) - Returns default value if value is null else returns value
    
    如果value值为null，则返回default值；不为null，则返回value值。

实际应用中，nvl是典型的空值处理函数

eg：select nvl(null,4);  	//返回值为：4
    eg：select nvl("null",4);  //返回值为：null
    eg：select nvl("jacob",4);  //返回值为：jacob

# substring （UDF） #

hive> desc function substring;
    OK
    tab_name
    substring(str, pos[, len]) - returns the substring of str that starts at pos and is of length len orsubstring(bin, pos[, len]) - returns the slice of byte array that starts at pos and is of length len
    
    substring(str, pos, len)：返回从pos开始，len长度的字符串
    substring(str, pos)：返回从pos开始，到末尾的字符串

注意：hive里面的字符串是从1开始计数的。

eg：hive> select substring("jacob123",2,3);
    OK
    _c0
    aco

只有一个参数时，表示从当前那一位截取到末尾

eg：hive> select substring("jacob123",2);
    OK
    _c0
    acob123

只有2个参数时，且第二个参数为负数时，表示表示从后往前数

eg:hive> select substring("jacob123",-2);
    OK
    _c0
    23

# if （UDF） #

if 相当于java里面的三目表达式。

hive> desc function if;
    OK
    tab_name
    IF(expr1,expr2,expr3) - If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.
    Time taken: 0.152 seconds, Fetched: 1 row(s)
    
    相当于java里面的三目表达式。
    IF(expr1,expr2,expr3) ：表达式1为真，则返回表达式2；为假，则返回表达式3

hive> select if(1<2,"aa","bb");
    OK
    aa
    
    hive> select if(1>2,"aa","bb");
    OK
    bb
    
    hive> select if(1<2,3,5);
    OK
    3

# array\_contains（常和 if 一起使用） （UDF） #

hive> desc function extended array_contains;
    OK
    tab_name
    array_contains(array, value) - Returns TRUE if the array contains value.
    Example:
      > SELECT array_contains(array(1, 2, 3), 2) FROM src LIMIT 1;
      true
    
    第一个参数是数组，第二个参数是value值，如果value值在array数组中，则返回结果是true

course\_stu\_all 表数据如下：

hive> select * from course_stu_all;
    OK
    course_stu_all.id       course_stu_all.stu_course       course_stu_all.all_course
    1       				["a","b","c","e"]       		["a","b","c","e","d","f"]
    2       				["a","c","d","f"]       		["a","b","c","e","d","f"]
    3       				["a","b","c","e"]       		["a","b","c","e","d","f"]

使用 array\_contains 和 if 实现行列变换，达到针对每一个学生，可以通过0、1这样的标识来查看学生是否选课的目的。

hive> select id,
        > if(array_contains(stu_course,all_course[0]),1,0) as a,
        > if(array_contains(stu_course,all_course[1]),1,0) as b,
        > if(array_contains(stu_course,all_course[2]),1,0) as c,
        > if(array_contains(stu_course,all_course[3]),1,0) as e,
        > if(array_contains(stu_course,all_course[4]),1,0) as d,
        > if(array_contains(stu_course,all_course[5]),1,0) as f
        > from course_stu_all;
    OK
    id      a       b       c       e       d       f
    1       1       1       1       1       0       0
    2       1       0       1       0       1       1
    3       1       1       1       1       0       0

# collect\_set /collect\_list （UDAF） #

hive> desc function collect_set;
    OK
    tab_name
    collect_set(x) - Returns a set of objects with duplicate elements eliminated
    返回去除重复元素的一组对象
    
    hive> desc function collect_list;
    OK
    tab_name
    collect_list(x) - Returns a list of objects with duplicates
    返回具有重复项的一组对象

collect\_set 去重————collect\_list 不去重  
他们属于典型的udaf（多 – > 一），属于聚合函数，可以和分组 group by 一起使用。

eg：数据如下：
    id course 
    1,a
    1,b
    1,c
    1,e
    2,a
    2,c
    2,d
    2,f
    3,a
    3,b
    3,c
    3,e
    
    eg1：collect_set()
    hive>select collect_set(course) as all_course from course;
    OK
    all_course
    ["a","b","c","e","d","f"]
    
    eg2:collect_list()
    hive>select collect_list(course) as all_course from course;
    OK
    all_course
    ["a","b","c","e","a","c","d","f","a","b","c","e"]
    
    eg3:和 group by一起使用：
    select
    id,collect_set(course) as choose_course
    from course
    group by id;
    OK
    id      choose_course
    1       ["a","b","c","e"]
    2       ["a","c","d","f"]
    3       ["a","b","c","e"]

# case when 行列转换时使用 （UDF） #

hive> desc function case;
    OK
    tab_name
    CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END - When a = b, returns c; when a = d, return e; else return f

course 表 有如下数据：

mysql> select * from course;
    +----+------+--------+-------+
    | id | sid  | course | score |
    +----+------+--------+-------+
    |  1 |    1 | yuwen  |    43 |
    |  2 |    1 | shuxue |    55 |
    |  3 |    2 | yuwen  |    77 |
    |  4 |    2 | shuxue |    88 |
    |  5 |    3 | yuwen  |    98 |
    |  6 |    3 | shuxue |    65 |
    |  7 |    3 | yingyu |    80 |
    +----+------+--------+-------+

**求所有数学课程成绩 大于 语文课程成绩的学生的学号**

使用case when 进行行列转换。  
case when 使用方式一：

select sid,
    case when course="yuwen" then score else 0 end yuwen,
    case when course="shuxue" then score else 0 end shuxue,
    case when course='yingyu' then score else 0 end yingyu
    from course;

case when 使用方式二：

select sid,
    case course when 'yuwen' then score else 0 end as yuwen,
    case course when 'shuxue' then score else 0 end as shuxue,
    case course when 'yingyu' then score else 0 end as yingyu
    from course;

两种方式结果一致，可以先把结果存入一个表中。  
第一步  
建表，将数据存入新表中：

create table course_step01 as
    select sid,
    case course when 'yuwen' then score else 0 end as yuwen,
    case course when 'shuxue' then score else 0 end as shuxue,
    case course when 'yingyu' then score else 0 end as yingyu
    from course;

行列转换后的新表 course\_step01 如下：

mysql> select * from course_step01;
    +------+-------+--------+--------+
    | sid  | yuwen | shuxue | yingyu |
    +------+-------+--------+--------+
    |    1 |    43 |      0 |      0 |
    |    1 |     0 |     55 |      0 |
    |    2 |    77 |      0 |      0 |
    |    2 |     0 |     88 |      0 |
    |    3 |    98 |      0 |      0 |
    |    3 |     0 |     65 |      0 |
    |    3 |     0 |      0 |     80 |
    +------+-------+--------+--------+

第二步：规整第一步表的样式，并将结果存入新表 course\_step02 中

create table course_step02 as
    select sid,max(yuwen) as yuwen,max(shuxue) as shuxue
    from course_step01
    group by sid;

新表如下：

mysql> select * from course_step02;
    +------+-------+--------+
    | sid  | yuwen | shuxue |
    +------+-------+--------+
    |    1 |    43 |     55 |
    |    2 |    77 |     88 |
    |    3 |    98 |     65 |
    +------+-------+--------+

第三步，按照要求求解

mysql> select sid from course_step02 where yuwen<shuxue;
    +------+
    | sid  |
    +------+
    |    1 |
    |    2 |
    +------+

一个sql语句写下来也可以：

select sid,max(yuwen) as yuwen,max(shuxue) as shuxue
    from 
    (
    select sid,
    case course when 'yuwen' then score else 0 end as yuwen,
    case course when 'shuxue' then score else 0 end as shuxue,
    case course when 'yingyu' then score else 0 end as yingyu
    from course
    ) a
    group by sid
    having yuwen<shuxue;
    +------+-------+--------+
    | sid  | yuwen | shuxue |
    +------+-------+--------+
    |    1 |    43 |     55 |
    |    2 |    77 |     88 |
    +------+-------+--------+

此题的另外一种思路：**使用自连接**

mysql> SELECT a.sid,a.course,a.score,b.course,b.score
        -> FROM course a,course b
        -> WHERE a.sid=b.sid
        -> AND a.course="yuwen"
        -> AND b.course="shuxue"
        -> AND a.score<b.score;
    +------+--------+-------+--------+-------+
    | sid  | course | score | course | score |
    +------+--------+-------+--------+-------+
    |    1 | yuwen  |    43 | shuxue |    55 |
    |    2 | yuwen  |    77 | shuxue |    88 |
    +------+--------+-------+--------+-------+
    
    
    另一种写法（使用 join 关键字）：
    mysql> SELECT a.sid,a.course,a.score,b.course,b.score
        -> FROM course a
        -> JOIN course b
        -> on a.sid=b.sid
        -> AND a.course="yuwen"
        -> AND b.course="shuxue"
        -> AND a.score<b.score;
    +------+--------+-------+--------+-------+
    | sid  | course | score | course | score |
    +------+--------+-------+--------+-------+
    |    1 | yuwen  |    43 | shuxue |    55 |
    |    2 | yuwen  |    77 | shuxue |    88 |
    +------+--------+-------+--------+-------+

# explode （UDTF） #

hive> desc function explode;
    OK
    explode(a) - separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns
    将一个数组a，分成多行；或者将一个map分成 多行或多列。

将数组或map从一个炸裂成多行或多列，典型的“ 一——>多”，UDTF。

hobby表 有如下数据：
    hive> select * from hobby;
    OK
    hobby.id	hobby.name     hobby.age    hobby.favors
    1			huangxiaoming   45			a-c-d-f
    2			huangzitao      36			b-c-d-e
    3			huanglei        41			c-d-e
    4			liushishi       22			a-d-e
    5			liudehua        39			e-f-d
    6			liuyifei        35			a-d-e

favors列，先通过 split() 函数分割成数组：

hive> select split(favors,"-") from hobby;
    OK
    _c0
    ["a","c","d","f"]
    ["b","c","d","e"]
    ["c","d","e"]
    ["a","d","e"]
    ["e","f","d"]
    ["a","d","e"]

再通过explode方法，将favors数组列炸裂成一个一个favor

hive> select id,explode(split(favors,"-")) from hobby where id=1;
    OK
    col
    a
    c
    d
    f

如果要将炸裂的一个一个favor整合到整个表中，还需要使用 lateral view。如下：

hive> select id,name,age,favors_view.favor
        > from hobby lateral view explode(split(favors,"-")) favors_view as favor;
    OK
    id      name    age     favors_view.favor
    1       huangxiaoming   45      a
    1       huangxiaoming   45      c
    1       huangxiaoming   45      d
    1       huangxiaoming   45      f
    2       huangzitao      36      b
    2       huangzitao      36      c
    2       huangzitao      36      d
    2       huangzitao      36      e
    3       huanglei        41      c
    3       huanglei        41      d
    3       huanglei        41      e
    4       liushishi       22      a
    4       liushishi       22      d
    4       liushishi       22      e
    5       liudehua        39      e
    5       liudehua        39      f
    5       liudehua        39      d
    6       liuyifei        35      a
    6       liuyifei        35      d
    6       liuyifei        35      e

注意，favors\_view 是 explode炸裂之后的视图别名，这个必须要。favor是炸裂之后成行成的列，作为新表中的列名。所以 favors\_view.favor 也可以直接写成 favor。

# round （UDF） #

hive> desc function extended round;
    OK
    round(x[, d]) - round x to d decimal places
    Example:
      > SELECT round(12.3456, 1) FROM src LIMIT 1;
      12.3'

保留小数点后几位，如果只有一个参数，则去整数，2个参数的话，第二个参数代表保留的小数点位数。

eg1:
    hive> select round(3.1415926);
    OK
    3
    
    eg2:
    hive> select round(3.1415926,3);
    OK
    3.142
    
    eg3:
    hive> select round(3.14,3);
    OK
    3.14