大数据笔记--Hive（第三篇）

目录

一、函数

1、概述

2、案例

3、nvl函数

4、case when 函数

5、explode

6、列转行

7、行转行

二、分类

1、常见函数分类

三、自定义UDF与UDTF

1、Hive3.X新特性

2、自定义UDF

3、自定义UDTF

四、窗口函数

1、over

2、案例

---

一、函数

1、概述

在Hive中，提供了非常丰富的运算符和函数，以方便用户对数据进行处理

如果Hive中原生提供的函数不能适应当前场景，还可以考虑自定函数

在Hive中，所有的函数都不能直接使用，必须结合select形成语句才能使用

查看Hive中的所有函数

show functions;

描述函数的信息：desc function count;

 

2、案例

i、拼接多个字符串，并且在拼接的时候指定字符串之间的间隔符号

原始数据

1234 qq com
2223 qq com
music 163 com
news baidu com
mp csdn net
建表语句

create table webs(app string, company string, kind string) row format delimited fields terminated by ' ';

加载数据

load data local inpath '/home/data/webs.txt' into table webs;

字符串拼接三种方式

select concat(app, '.', company, '.', kind) from webs;

select concat_ws('.', app, company, kind) from webs;

select concat_ws('.', *) from webs;

 

ii、 给定一个字符串表示日期，从日期中将年份提取出来

方式一：切分

select cast(split('2022-3-19','-')[0]as int);

方式二：提取

select year('2022-3-19');

        

iii、year函数在使用的时候，要求年月日之间必须用-来拼接，如果用其他符号拼接，那么就无法提取

方式一：切分

select cast(split('2022/3/19', '/')[0] as int);

方式二：替换之后再进行提取

select year(regexp_replace('2022/3/19', '/', '-'));

iv、提取邮箱后缀

方式一：切分

select split('tom@tedu.cn', '@')[1];

方式二：正则提取

select regexp_extract('tom@tedu.cn', '(.+)@(.+)', 2); 注意捕获组从1开始

3、nvl函数

        nvl(s1, s2)：如果s1的值不为null，那么返回s1的值；如果s1的值为null那么返回s2的值；如果s1和s2的值都为null，那么返回null

案例：

原始数据：

1 jichanghai 1000000
2 yueshan 800
3 cuilaoda 500
4 ninghongye
5 temuer 500
6 jianan 300
7 hutao
8 tianhai 400
9 yaodaoji 60000
10 wuchen 900
11 guqinghan 5000

见表语句：

create table yongjie(id int, name string, reward double) row format delimited fields terminated by ' ';

加载数据：

load data local inpath '/home/data/yongjie.txt' into table yongjie;

计算平均每个人物赚了多少钱，如果碰到null自动忽略：

 select avg(nvl(reward,0)) from yongjie;

 

4、case when 函数

case when函数：类似于Java中的switch-case结构

案例：

原始数据：

1 财务 bill 男
2 技术 charles 男
3 技术 lucy 女
4 技术 lily 女
5 财务 helen 女
6 财务 jack 男
7 财务 john 男
8 技术 alex 男
9 技术 cindy 女
10 技术 david 男

建表语句：

create table employers(id int,department string,name string, gender string) row format delimited fields terminated by ' ';

 加载数据：

load data local inpath '/home/data/employers.txt' into table employers;

统计每一个部门的总人数：

select department, count(*) from employers group by department;

统计每一个部门男女生的总人数：

select department, sum(case gender when '男' then 1 else 0 end) as sum_male, sum(case gender when '女' then 1 else 0 end) as sum_female from employers group by department;

5、explode

        explode(array/map)函数在使用的时候，会传入的数组中的每一个元素给单独提取出来形成单独的一行，或者会将映射中的键值对给拆分开形成多行数据

案例：

单词统计：

在hdfs上建立一个目录words，然后再里面上传一个words.txt文件，内容如下

hello tom hello bob david joy hello
hello rose joy hello rose
jerry hello tom hello joy

建表语句：

create external table words(warr array<string>) row format delimited collection items terminated by ' ' location '/words';

统计语句：

select w, count(w) from (select explode(warr) w from words)ws group by w;

 分析：其中select explode(warr) w from words是一个子查询，结果放到ws这个临时表中，其中又给字段给了一个别名w，外面就对w进行操作。

6、列转行

所谓的列转行，是将一列的数据进行拆分，拆分成多行的数据

i、案例

原始数据：

悬崖之上 剧情/动作/悬疑
秘密访客 悬疑/惊悚
扫黑·决战 剧情/动作/犯罪
真·三国无双 动作/奇幻/古装

建表语句：

create table movies(name string, kinds array<string>) row format delimited fields terminated by ' ' collection items terminated by'/';

加载数据：

load data local inpath '/home/data/movies.txt' into table movies;

列转行 - 炸裂：

select name, kind from movies lateral view explode(kinds) k as kind;

查询所有的悬疑片：

select * from (select name, kind from movies lateral view explode(kinds) k as kind)tmp where kind = '悬疑';

ii、案例 

原始数据：

Kevin 活泼/开朗 打篮球/看电影
Lisa 大方/活泼 看电影/听音乐
Carl 活泼/幽默 听音乐/打篮球
Joy 大方/诙谐 打游戏/看电影

建表语句：

create table friends (name string, characters array<string>, hobbies array<string>) row format delimited fields terminated by ' ' collection items terminated by '/';

加载数据：

load data local inpath '/home/data/friends.txt' into table friends;

炸裂： 

select name, c, h from friends lateral view explode(characters) c_tmp as c lateral view explode(hobbies) h_tmp as h;

获取性格活泼喜欢听音乐的人：

select * from (select name, c, h from friends lateral view explode(characters) c_tmp as c lateral view explode(hobbies) h_tmp as h) tmp where c = '活泼' and h = '听音乐';

7、行转行

行转列指的是将多行的数据拼接之后放到一列上

i、案列collect_set

原始数据：

夺命手术 剧情
夺命手术 悬疑
夺命手术 惊悚
夺命手术 犯罪
白夜行 爱情
白夜行 悬疑
如月疑云 喜剧
如月疑云 悬疑
无罪之最 剧情
无罪之最 悬疑
无罪之最 惊悚
无罪之最 犯罪

建表语句：

create table movies1 (name string, kind string) row format delimited fields terminated by ' ';

加载数据：

load data local inpath '/home/data/movies1.txt' into table movies1;

行转列：

select name, concat_ws('/', collect_set(kind)) from movies1 group by name;

ii、案例

原始数据：

1 1 burt
1 2 james
1 3 fred
1 4 bruce
1 1 carol
1 2 taylor
1 3 evan
1 4 grace
1 1 richard
1 2 adam
1 3 ben
1 4 ross
1 1 charles
1 2 cody
1 3 wendy
1 4 david

建表语句：

create table students(grade int, class int, name string) row format delimited fields terminated by ' ' ;

加载数据：

load data local inpath '/home/data/students.txt' into table students;

行转列：

select grade, class, concat_ws(',', collect_list(name)) from students group by grade, class;

二、分类

1、常见函数分类

①、UDF（User Defined Function）：用户自定义函数。特点是一进一出，指的是输入一行数据会获取到一行结果，例如year，length，concat，concat_ws，split等

②、UDAF(User Defined Aggregation Function)：用户定义聚合函数。特点是多进一出，指的是输入多行数据获取到一行结果，例如count，sum，avg，max，min，collect_set，collect_list等

③、UDTF(User Defined Table-generation Function)：用户定义表生成函数。特点是一进多出，指的是输入一行数据获取到多行结果，例如explode。UDTF在使用的时候必须紧贴select语句，例如select explode(XXX) from xxx;并且注意，UDTF在结合select使用的时候，在表输中只能单独存在，例如select name, explode(xxx) from xxx;或者select explode(xxx), name from xxx是不允许的

注意：在Hive中，相对使用比较多的函数大部分都是UDF函数

三、自定义UDF与UDTF

在Hive中，如果Hive原生提供的函数不能够处理当前的数据，那么Hive允许用户自定义函数

1、Hive3.X新特性

在Hive1.X和Hive2.X，需要定义类继承UDF类，但是在Hive3.X中，UDF类已过时，所以需要定义类继承GenericUDF类

2、自定义UDF

定义完类之后，需要将这个类打成jar包放到Linux上，上传之后需要在Hive中来添加当前的jar包

相关依赖：

<dependencies>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-exec</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>3.1.2</version>
            <exclusions>
                <exclusion>
                    <groupId>org.glassfish</groupId>
                    <artifactId>javax.el</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-metastore</artifactId>
            <version>3.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-common</artifactId>
            <version>3.1.2</version>
        </dependency>
    </dependencies>

示例代码：

package org.example.hive.udf;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
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;

// 需求： 传入两个字符串，获取字符串在原串中第一次出现的下标
public class AuthUDF extends GenericUDF {
    // 初始化 - 返回值类型决定了evaluate方法的返回值类型
    @Override
    public ObjectInspector initialize(ObjectInspector[] objectInspectors) throws UDFArgumentException {
        // 确定参数个数
        if(objectInspectors.length != 2)
            throw new UDFArgumentException("参数个数不为2！！！");
        // 返回结果，这个结果决定了函数的返回值类型
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }

    // Hive调用函数的功能的时候就是调用这个方法
    // 所以函数要执行的逻辑需要覆盖在这个方法中
    @Override
    public Object evaluate(DeferredObject[] deferredObjects) throws HiveException {
        // 获取原串
        String str = deferredObjects[0].get().toString();
        // 获取字串
        String sub = deferredObjects[1].get().toString();
        // 获取下标
        return str.indexOf(sub);
    }

    @Override
    public String getDisplayString(String[] strings) {
        return null;
    }
}

 然后上传到我们的服务器上，在hive中添加jar包：

 add jar /home/data/D_HIVE-1.0-SNAPSHOT.jar;

创建临时函数，给定包名必须是全名：

create temporary function indexof as 'org.example.hive.udf.AuthUDF';

3、自定义UDTF

在Hive中，自定义UDTF过程和自定义UDF过程有些类似，不同的地方在于需要定义一个类继承GenericUDTF

package org.example.hive.udtf;

import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

import java.util.ArrayList;
import java.util.List;

// 需求：将字符串按照指定字符来进行切分，将切分之后的每一个元素单独提取出来成为一行数据
public class AuthUDTF extends GenericUDTF {

    private List<String> list = new ArrayList<>();

    // 初始化方法 - 返回值同样决定了process方法的返回值类型
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) {
        // 定义列的名字
        List<String> fieldsName = new ArrayList<>();
        fieldsName.add("splitStringToLine");
        // 定义列的数据类型，实际上就是定义process方法的打印类型
        List<ObjectInspector> fieldsType = new ArrayList<>();
        fieldsType.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        return ObjectInspectorFactory.getStandardStructObjectInspector(fieldsName, fieldsType);
    }

    // Hive会调用这个函数来处理数据
    @Override
    public void process(Object[] args) throws HiveException {
        // 获取要被切分的字符串
        String str = args[0].toString();
        // 获取切分符号
        String symbol;
        if (args.length >= 2)
            symbol = args[1].toString();
        else
            symbol = ",";
        // 切分
        String[] arr = str.split(symbol);
        // 需要将切出来的数据打印
        for (String s : arr) {
            // 将数据放到集合中
            list.add(s);
            // 将集合中的数据打印
            forward(list);
            // 打印完成之后，需要清空list中的数据避免重复打印
            list.clear();
        }
    }

    @Override
    public void close() {

    }
}

定义完类之后，同样需要将这个类打成jar包放到Linux上，之后同样需要在Hive中添加当前的jar包，同样需要创建临时函数，与上面步骤相似。要注意hive要重新进入一下，临时函数就会失效。

四、窗口函数

1、over

①、窗口函数的作用是用于限制要处理的数据的量

②、语法：

分析函数 over(partition by 列名 order by 列名 rows between 起始位置 and 结束位置)

i、分析函数一般包括聚合函数、排序函数等，例如

count(col)	统计指定列的总的个数
sum(col)	针对指定列进行求和
avg(col)	针对指定列进行求平均
max(col)	获取指定列的最大值
min(col)	获取指定列的最小值
lag(col, n)	获取当前行的第前n行数据。例如lag(col, 1)表示获取上一行数据。再例如，如果当前是第5行数据，那么lag(col, 3)表示获取第2行数据
lead(col, n)	获取当前的第后n行数据。例如lead(col, 1)表示获取下一行数据。再例如，如果当前是第5行数据，那么lead(col, 3)表示获取第8行数据
ntile(n)	要求数据必须排序，数据排序之后，会平均的放到n个桶中。Hive自动的给每一个桶进行编号，编号是从1开始。每一个桶中的数据都会携带当前桶的编号。如果数据不能平均放到每一个桶中，那么会优先将排在前面的数据放到小的桶中去，并且，每一个桶中的数据行数之差不能超过1行
row_number()	在数据排序之后对数据进行自增的编号，且编号不重复
rank()	在数据排序之后会对数据进行自增的编号，如果值相同，那么编号会重复且产生空位
dense_rank()	在数据排序之后会对数据进行自增的编号，如果值相同，那么编号会重复且不产生空位

ii、partition by：对数据进行分区，实际上就是对数据进行分类

iii、order by：对数据进行排序，desc表示降序，asc表示升序

iv、rows between：表示指定数据的处理范围

③、指定范围

        i、preceding：往前

        ii、following：往后

        iii、current row：当前行

        iv、unbounded：无边界

                1、unbounded preceding：从第一行开始

                2、unbounded following：到最后一行结束

2、案例

案例一：

原始数据：

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

建表语句：

create table orders(name string,orderdate string,cost double)row format delimited fields terminated by ',';

加载数据：

load data local inpath '/home/data/orders.txt' into table orders;

 

需求一：查询2021年4月份消费的顾客名单以及2021年4月份产生的总的消费人次

select distinct name,count(*) over() from orders where year(orderdate) = 2021 and month(orderdate) = 4;

需求二：获取每一个顾客的消费明细以及每一位顾客的月度消费总额

select *,sum(cost) over(partition by name,month(orderdate)) from orders;

需求三：获取每一个顾客的消费明细以及到当前日期为止的累计消费

select *,sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) from orders;

需求四：查询顾客的消费明细以及每一次消费之后上一次的消费时间

select *, lag(orderdate, 1) over(partition by name order by orderdate) from orders;

 

需求五：获取最早的20%的顾客的消费名单 - 首先需要先将数据按照消费日期来进行排序，其次需要将按照排序之后的结果将数据放到5个桶中，获取第一个桶的数据

select * from (select *, ntile(5) over(order by orderdate) as bucket_id from orders)tmp where bucket_id = 1;

案例二：

 原始数据：

Charles Chinese 87
Charles Math    95
Charles English 68
Lily    Chinese 94
Lily    Math    56
Lily    English 84
William Chinese 64
William Math    86
William English 84
Vincent Chinese 65
Vincent Math    85
Vincent English 78

建表语句：

create table scores (name string, subject string, score int) row format delimited fields terminated by '\t';

加载数据：

load data local inpath '/home/data/scores.txt' into table scores;

需求一：按学科，对每一个学生的成绩进行降序排序

顺次排序

select *, row_number() over(partition by subject order by score desc) from scores;

并列排序

select *, rank() over(partition by subject order by score desc) from scores;

非空位并列排序

select *, dense_rank() over(partition by subject order by score desc) from scores;

需求二：按学科，获取每一个学科考试成绩前三名的学生

select * from (select *, rank() over(partition by subject order by score desc) as sortid from scores)tmp where sortid <= 3;