大数据Hive学习之旅第五篇 一、函数 二、友情链接

发表于

「这是我参与11月更文挑战的第26天，活动详情查看：2021最后一次更文挑战」。

一、函数

1、系统内置函数

1.1、查看系统自带的函数

1
hive复制代码hive (default)> show functions;

1.2、显示自带的函数的用法

1
hive复制代码hive (default)> desc function upper;

1.3、详细显示自带的函数的用法

1
hive复制代码hive (default)> desc function extended upper;

2、常用内置函数

2.1、空字段赋值

  1. 函数说明

NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。

  1. 数据准备：采用员工表
  2. 查询：如果员工的 comm 为 NULL，则用-1 代替
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
hive复制代码hive (default)> select comm,nvl(comm,-1) from emp;
OK
comm	_c1
NULL	-1.0
300.0	300.0
500.0	500.0
NULL	-1.0
1400.0	1400.0
NULL	-1.0
NULL	-1.0
NULL	-1.0
NULL	-1.0
0.0	0.0
NULL	-1.0
NULL	-1.0
NULL	-1.0
NULL	-1.0
  1. 查询：如果员工的 comm 为 NULL，则用领导 id 代替
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
hive复制代码hive (default)> select comm,nvl(comm,mgr) from emp;
OK
comm	_c1
NULL	7902.0
300.0	300.0
500.0	500.0
NULL	7839.0
1400.0	1400.0
NULL	7839.0
NULL	7839.0
NULL	7566.0
NULL	NULL
0.0	0.0
NULL	7788.0
NULL	7698.0
NULL	7566.0
NULL	7782.0

2.2、CASE WHEN THEN ELSE END

  1. 数据准备

image.png
2. 需求

求出不同部门男女各多少人。结果如下：

image.png
3. 创建本地 emp_sex.txt，导入数据

1
2
3
4
5
6
7
shell复制代码[moe@hadoop102 datas]$ vi emp_sex.txt
悟空	A	男
大海	A	男
宋宋	B	男
凤姐	A	女
婷姐	B	女
婷婷	B	女
  1. 创建 hive 表并导入数据
1
2
3
4
5
hive复制代码hive (default)> create table emp_sex
              > (name string, dept_id string, sex string)
              > row format delimited fields terminated by '\t';
              
hive (default)> load data local inpath '/opt/module/hive-3.1.2/datas/emp_sex.txt' into table emp_sex;
  1. 按需求查询数据
1
2
3
4
5
6
hive复制代码hive (default)> select
              >  dept_id,
              >  sum(case sex when '男' then 1 else 0 end) male_count,
              >  sum(case sex when '女' then 1 else 0 end) female_count
              > from emp_sex
              > group by dept_id;

2.3、行转列

  1. 相关函数说明

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,…)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

注意: CONCAT_WS must be "string or array<string>

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 Array 类型字段。
2. 数据准备

image.png
3. 需求

把星座和血型一样的人归类到一起。结果如下：

image.png
4. 创建本地 constellation.txt，导入数据

创建本地 constellation.txt，导入数据

1
2
3
4
5
6
7
shell复制代码[moe@hadoop102 datas]$ vim person_info.txt
孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A
苍老师	白羊座	B
  1. 创建 hive 表并导入数据
1
2
3
4
5
hive复制代码hive (default)> create table person_info
              > (name string, constellation string, blood_type string)
              > row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/opt/module/hive-3.1.2/datas/person_info.txt' into table person_info;
  1. 按需求查询数据
1
2
3
4
5
6
7
8
9
10
hive复制代码hive (default)> SELECT
              > t1.c_b,
              > CONCAT_WS("|",collect_set(t1.name))
              > FROM (
              > SELECT
              > NAME,
              > CONCAT_WS(',',constellation,blood_type) c_b
              > FROM person_info
              > )t1
              > GROUP BY t1.c_b;

image.png

2.4、列转行

  1. 函数说明

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

LATERAL VIEW

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

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

image.png
3. 需求

将电影分类中的数组数据展开。结果如下：

image.png
4. 创建本地 movie.txt，导入数据

1
2
3
4
shell复制代码[moe@hadoop102 datas]$ vi movie_info.txt
《疑犯追踪》	悬疑,动作,科幻,剧情
《Lie to me》	悬疑,警匪,心理,剧情
《战狼2》	战争,动作,灾难
  1. 创建 hive 表并导入数据
1
2
3
4
5
hive复制代码hive (default)> create table movie_info
              > (movie string, category string)
              > row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/opt/module/hive-3.1.2/datas/movie_info.txt' into table movie_info;
  1. 按需求查询数据
1
2
3
4
5
6
7
hive复制代码hive (default)> SELECT
              > movie,
              > category_name
              > FROM
              > movie_info
              > lateral VIEW
              > explode(split(category,",")) movie_info_tmp AS category_name;

image.png

2.5、窗口函数（开窗函数）

  1. 相关函数说明

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 行数据

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
txt复制代码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 月份购买过的顾客及总人数
* 查询顾客的购买明细及月购买总额
* 上述的场景, 将每个顾客的 cost 按照日期进行累加
* 查询每个顾客上次的购买时间
* 查询前 20%时间的订单信息
  1. 创建本地 business.txt，导入数据
1
shell复制代码[moe@hadoop102 datas]$ vi business.txt
  1. 创建 hive 表并导入数据
1
2
3
4
5
6
7
hive复制代码hive (default)> create table business(
              > name string,
              > orderdate string,
              > cost int)
              > row format delimited fields terminated by ',';

hive (default)> load data local inpath '/opt/module/hive-3.1.2/datas/business.txt' into table business;
  1. 按需求查询数据
* 查询在 2017 年 4 月份购买过的顾客及总人数


未使用over()函数




1
2
3
4
5
hive复制代码hive (default)> select
              > name,count(*)
              > from business
              > where substring(orderdate,1,7) = '2017-04'
              > group by name;



![image.png](https://gitee.com/songjianzaina/juejin_p8/raw/master/img/88f17e33d94cf803de0aece411aaecac801cab1c445d3799cb45743a592bc923)


使用over()函数




1
2
3
4
5
hive复制代码hive (default)> select
              > name,count(*) over()
              > from business
              > where substring(orderdate,1,7) = '2017-04'
              > group by name;



![image.png](https://gitee.com/songjianzaina/juejin_p8/raw/master/img/7c04e0cbb6a6cba7afad9eaee7d5ca649f0251d151d2e8489146bfdb07f7c06e)
* 查询顾客的购买明细及月购买总额




1
2
3
hive复制代码hive (default)> select
              > name,orderdate,cost,sum(cost) over(partition by month(orderdate))
              > from business;



![image.png](https://gitee.com/songjianzaina/juejin_p8/raw/master/img/c7871d7ab4bce96a03a8c0e72bbfe56114075d012ca101e8390063944b537c54)
* 将每个顾客的 cost 按照日期进行累加




1
2
3
4
5
6
7
8
9
hive复制代码select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按 name 分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按 name分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一样,由起点到当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from business;



![image.png](https://gitee.com/songjianzaina/juejin_p8/raw/master/img/8e88a26f953aeea8b6a20199ca4092512a94ce27776dc5ccef15e31c38835a28)


rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量


![154E646C.gif](https://gitee.com/songjianzaina/juejin_p8/raw/master/img/72c633265ad4dcfbb62b91d224e47e2d177b7431ae01ec2b8a57f53b78d7bda9)
* 查看顾客上次的购买时间




1
2
3
4
5
hive复制代码select 
name,orderdate,cost,
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1,
lag(orderdate,2) over (partition by name order by orderdate) as time2
from business;



![image.png](https://gitee.com/songjianzaina/juejin_p8/raw/master/img/201541085e19bc6909b8efb1b2ba7939ad3cc9e28fbf2cc403a56aeaedc97f3d)
* 查询前 20%时间的订单信息




1
2
3
4
5
hive复制代码select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
from business
) t
where sorted = 1;



![image.png](https://gitee.com/songjianzaina/juejin_p8/raw/master/img/4eb3dc069cb621448033f0d19c9f812a7c10731db5ec56eb57a4c84d3772ef48)

2.6、Rank

  1. 函数说明

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

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

ROW_NUMBER() 会根据顺序计算
2. 数据准备

image.png
3. 需求

计算每门学科成绩排名。
4. 创建本地 score.txt，导入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
shell复制代码[moe@hadoop102 datas]$ vi score.txt

孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78
  1. 创建 hive 表并导入数据
1
2
3
4
5
6
7
hive复制代码hive (default)> create table score(
              > name string,
              > subject string,
              > score int)
              > row format delimited fields terminated by '\t';

hive (default)> load data local inpath '/opt/module/hive-3.1.2/datas/score.txt' into table score;
  1. 按需求查询数据
1
2
3
4
5
6
7
hive复制代码select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;

image.png

3、自定义函数

  1. Hive 自带了一些函数，比如：max/min 等，但是数量有限，自己可以通过自定义 UDF 来方便的扩展。
  2. 当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
  3. 根据用户自定义函数类别分为以下三种
* UDF（User-Defined-Function）


一进一出
* UDAF（User-Defined Aggregation Function）聚集函数


多进一出


类似于：count/max/min
* UDTF（User-Defined Table-Generating Functions）


一进多出


如 lateral view explode()
  1. 官方文档地址

cwiki.apache.org/confluence/…
5. 编程步骤

* 继承 Hive 提供的类


org.apache.hadoop.hive.ql.udf.generic.GenericUDF


org.apache.hadoop.hive.ql.udf.generic.GenericUDTF
* 实现类中的抽象方法
* 在 hive 的命令行窗口创建函数


添加 jar




1
shell复制代码add jar linux_jar_path



创建 function




1
hive复制代码create [temporary] function [dbname.]function_name AS class_name;


* 在 hive 的命令行窗口删除函数




1
hive复制代码drop [temporary] function [if exists] [dbname.]function_name;

4、自定义 UDF 函数

  1. 需求

自定义一个 UDF 实现计算给定字符串的长度（当然系统默认有这个函数，仅仅是案例模拟！），例如：

1
2
hive复制代码hive(default)> select my_len("abcd");
4
  1. 创建一个 Maven 工程 Hive
  2. 导入依赖
1
2
3
4
5
6
7
pom复制代码<dependencies>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>3.1.2</version>
    </dependency>
</dependencies>
  1. 创建一个类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
java复制代码public class MyUDF extends GenericUDF {

    //校验数据参数个数
    @Override
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {

        if (arguments.length != 1) {
            throw new UDFArgumentException("参数个数不为1");
        }

        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }

    //处理数据
    @Override
    public Object evaluate(DeferredObject[] arguments) throws HiveException {

        //1.取出输入数据
        String input = arguments[0].get().toString();

        //2.判断输入数据是否为null
        if (input == null) {
            return 0;
        }

        //3.返回输入数据的长度
        return input.length();
    }

    @Override
    public String getDisplayString(String[] children) {
        return "";
    }
}
  1. 打成 jar 包上传到服务器/opt/module/hive-3.1.2/datas/myudf.jar
  2. 将 jar 包添加到 hive 的 classpath
1
hive复制代码hive (default)> add jar /opt/module/hive-3.1.2/datas/myudf.jar;
  1. 创建临时函数与开发好的 java class 关联
1
hive复制代码hive (default)> create temporary function my_len as "com.moe.hive.udf.MyUDF";
  1. 即可在 hql 中使用自定义的函数
1
2
3
4
5
6
7
8
9
hive复制代码hive (default)> select my_len('moe');
OK
_c0
3

hive (default)> select length('moe');
OK
_c0
3

5、自定义 UDTF 函数

  1. 需求

自定义一个 UDTF 实现将一个任意分割符的字符串切割成独立的单词，例如：

1
2
3
4
5
hive复制代码hive(default)> select myudtf("hello,world,hadoop,hive", ",");
hello
world
hadoop
hive
  1. 代码实现
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
java复制代码public class MyUDTF extends GenericUDTF {

    //输出数据的集合
    private ArrayList<String> outPutList = new ArrayList<>();

    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {

        //输出数据的默认列名,可以用别名覆盖
        List<String> fieldNames = new ArrayList<>();
        fieldNames.add("word");

        //输出数据的类型
        List<ObjectInspector> fieldOIs = new ArrayList<>();
        fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);

        //最终返回值
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs);
    }

    //处理输入数据：hello,moe,hive
    @Override
    public void process(Object[] args) throws HiveException {

        //1.取出输入数据
        String input = args[0].toString();

        //2.按照"，"分割字符串
        String[] words = input.split(",");

        //3.遍历数据写出
        for (String word : words) {

            //清空集合
            outPutList.clear();

            //将数据放入集合
            outPutList.add(word);

            //输出数据
            forward(outPutList);

        }

    }

    //收尾方法
    @Override
    public void close() throws HiveException {

    }
}
  1. 打成 jar 包上传到服务器/opt/module/hive-3.1.2/datas/myudtf.jar
  2. 将 jar 包添加到 hive 的 classpath 下
1
hive复制代码hive (default)> add jar /opt/module/hive-3.1.2/datas/myudtf.jar;
  1. 创建临时函数与开发好的 java class 关联
1
hive复制代码hive (default)> create temporary function myudtf as "com.moe.hive.udtf.MyUDTF";
  1. 使用自定义的函数
1
2
3
4
5
6
hive复制代码hive (default)> select myudtf('hello,moe,hive',',');
OK
word
hello
moe
hive

二、友情链接

大数据Hive学习之旅第四篇

大数据Hive学习之旅第三篇

大数据Hive学习之旅第二篇

大数据Hive学习之旅第一篇

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%