项目实训第八天

本文最后更新于：2021年7月23日 晚上

Hive

函数

基本案例

  1. 拼接
  2. 原始数据
1
2
3
4
baike baidu com
mail 163 com
music qq com
news baidu com
  1. 在Hive中建表来管理这些数据
1
create table webs (app string, company string, kind string) row format delimited fields terminated by ' ';
  1. 加载数据
1
load data local inpath '/opt/hivedemo/webs' into table webs;
  1. 拼接数据
1
2
3
4
5
6
# 方式一
select concat(app, '.', company, '.', kind) from webs;
# 方式二
select concat_ws('.', app, company, kind) from webs;
# 方式三
select concat_ws('.', *) from webs;
  1. 提取年份 ：’2021-07-19’，需要将日期中的年份提取出来
  2. 方式一：剪切
1
select cast(split('2021-07-19', '-')[0] as int);
  1. 方式二：提取
1
select year('2021-07-19');
  1. 提取年份：’2021/07/19’，需要将日期中的年份提取出来
  2. 方式一：剪切
1
select cast(split('2021/07/19', '/')[0] as int);
  1. 方式二：替换
1
select year(regexp_replace('2021/07/19', '/', '-'));
  1. 提取邮箱后缀：‘tom@163.com‘需要邮箱的后缀提取出来
  2. 方式一：切分
1
select split('tom@163.com', '@')[1];
  1. 方式二：提取
1
select regexp_extract('tom@163.com', '(.*)@(.*)', 2);

NVL

  1. nvl(s1, s2)：如果s1的值不为null，则返回s1；如果s1的值为null，那么会返回s2的值
  2. 案例：计算平均奖金
  3. 原始数据
1
2
3
4
5
6
7
8
9
10
1 Alex 3000
2 Bill 2600
3 Cathy 1500
4 Danny
5 Evan 2000
6 Feddy 2400
7 Grace 1000
8 Hack 1500
9 Iran
10 John 2800
  1. 在Hive中建表来管理原始数据
1
create table rewards(id int, name string, reward double) row format delimited fields terminated by ' ';
  1. 加载数据
1
load data local inpath '/opt/hivedemo/rewards' into table rewards;
  1. 获取这个月平均每一个人发了多少奖金
1
2
select avg(reward) from rewards; # avg在求平均值的时候，会自动的跳过null值
select avg(nvl(reward, 0)) from rewards;

case when

  1. case when类似于Java中的switch-case结构，对数据进行分支选择
  2. 案例：统计每一个部门的男女生人数
  3. 原始数据
1
2
3
4
5
6
7
8
9
10
11
12
1 技术 Nancy 女
2 技术 Mark 男
3 财务 Mike 男
4 技术 Nack 男
5 财务 Lucy 女
6 财务 Lily 女
7 技术 Lisa 女
8 技术 Kite 男
9 财务 Sam 男
10 财务 Simon 男
11 技术 Tony 男
12 财务 Thomas 男
  1. 建表语句
1
create table employers(id int, department string, name string, gender string) row format delimited fields terminated by ' ';
  1. 加载数据
1
load data local inpath '/opt/hivedemo/employers' into table employers;
  1. 分别统计每一个部门内男女生的人数
1
select department, sum(case gender when '男' then 1 else 0 end) as totalmale, sum(case gender when '女' then 1 else 0 end) as totalfemale from employers group by department;

explode

  1. explode函数在使用的时候，需要传入一个数组或者映射。如果传入的是一个数组，那么会将数组中的每一个元素提取出来成为单独的一行；如果传入的是一个映射，那么会将映射的键值对拆分成两列
  2. 案例：单词统计
  3. 原始数据
1
2
3
4
5
6
7
hadoop flume hadoop hdfs
flume source hadoop source map reduce
sink flume hadoop map hdfs channel
hadoop hdfs map reduce
shuffle hadoop flume source map
hdfs sink channel reduce hadoop flume
hadoop test flume sink
  1. 建表语句
1
create table words(warr array<string>) row format delimited collection items terminated by ' ';
  1. 加载数据
1
load data local inpath '/opt/hivedemo/words' into table words;
  1. 统计每一个单词出现的次数
1
select w, count(w) from (select explode(warr) as w from words)ws group by w;

列转行

  1. 所谓列转行，指的是将某一列的数据拆分成多行的数据
  2. 案例一
  3. 原始数据
1
2
3
4
5
6
济公之降龙降世    动画/奇幻/冒险
黑寡妇    动作/科幻/冒险
明日之战    动作/科幻
疾速猪杀    剧情/惊悚
钛    剧情/惊悚
人类清除计划5    惊悚/科幻/犯罪
  1. 建表
1
create table movies (name string, kinds array<string>) row format delimited fields terminated by '\t' collection items terminated by '/';
  1. 加载数据
1
load data local inpath '/opt/hivedemo/movies' into table movies;
  1. 列转行 - 炸裂
1
select name, k from movies lateral view explode(kinds) k_tmp as k;
  1. 查询惊悚片
1
select name, k from movies lateral view explode(kinds) k_tmp as k where k = '惊悚';
  1. 案例二
  2. 原始数据
1
2
3
4
Darl 活泼/大方 打篮球/打游戏
Danny 活泼/幽默 打篮球/听音乐
Fred 大方/开朗 打游戏/看电影
Frank 幽默/开朗 打游戏/听音乐
  1. 建表
1
create table person(name string, characters array<string>, hobbies array<string>) row format delimited fields terminated by ' ' collection items terminated by '/';
  1. 加载数据
1
load data local inpath '/opt/hivedemo/person' into table person;
  1. 炸裂多列
1
select name, c, h from person lateral view explode(characters) c_tmp as c lateral view explode(hobbies) h_tmp as h;

行转列

  1. 所谓行转列，将多行的数据合并到一列上
  2. 案例一
  3. 原始数据
1
2
3
4
5
6
7
8
9
10
智齿 悬疑
智齿 犯罪
狼行者 动画
狼行者 奇幻
狼行者 冒险
杀手妻子的保镖 犯罪
杀手妻子的保镖 动作
杀手妻子的保镖 喜剧
我要我们在一起 剧情
我要我们在一起 爱情
  1. 建表
1
create table movies (name string, kind string) row format delimited fields terminated by ' ';
  1. 加载数据
1
load data local inpath '/opt/hivedemo/movies' into table movies;
  1. 行转列
1
select name, concat_ws('/', collect_set(kind)) from movies group by name;
  1. 案例二
  2. 原始数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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
  1. 建表
1
create table students(grade int, class int, name string) row format delimited fields terminated by ' ';
  1. 加载数据
1
load data local inpath '/opt/hivedemo/students' into table students;
  1. 合并 - 行转列
1
select grade, class, concat_ws(',', collect_list(name)) from students group by grade, class;

分类

  1. 在Hive中，将窗口函数以外的函数分为了3类
  2. UDF(User Defined Function)：用户定义函数。这类函数的特点是一进一出，即输入一行数据获取到一行结果。例如year，length，sin，floor等
  3. UDAF(User Defined Aggregation Function)：用户定义聚合函数。这类函数的特点是多进一出，即输入多行数据会获取到一行结果。例如count，sum，min，max，avg，collect_set，collect_list等
  4. UDTF(User Defined Table-generated Function)：用户定义表生成函数。这类函数的特点一进多出，即输入一行数据会获取到多行结果，例如explode
  5. 在Hive中，大约90%的函数都是UDF函数，UDF函数也是最常用的函数

UDF

  1. 构建Maven工程，导入对应的POM依赖
  2. 在Hive1.x和Hive2.x中，需要定义一个类继承UDF类；在Hive3.x中，UDF类已经过时，所以此时需要定义一个类继承GenericUDF类
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
// 在Hive3.x中需要继承GenericUDF
public class AuthUDF extends GenericUDF {

  // 初始化方法
  // 这个方法的返回值类型决定了evaluate方法的返回值类型
  @Override
  public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
      // 判断参数个数
      if (arguments.length < 2)
          throw new UDFArgumentException("参数个数缺少，至少需要2个参数！！！");
      // 返回值类型决定了evaluate方法的返回值类型
      return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
  }

  // Hive在执行的时候，实际上的执行逻辑需要覆盖在这个方法中
  @Override
  public Object evaluate(DeferredObject[] arguments) {
      // 获取到字符串
      String str = arguments[0].toString();
      // 获取子串
      String sub = arguments[1].toString();
      // 获取子串在字符串中第一次出现的下标
      return str.indexOf(sub);
  }

  @Override
  public String getDisplayString(String[] children) {
      return null;
  }
}
  1. 定义完类之后，需要将这个类打成jar包，然后将这个jar上传到Linux系统中
  2. 需要在Hive中添加jar包
1
add jar /opt/hivedemo/cqhive-1.0-SNAPSHOT.jar;
  1. 需要在Hive中构建临时函数来使用
1
create temporary function indexof as 'cn.tedu.udf.AuthUDF';

UDTF

  1. 定义一个类继承GenericUDTF，覆盖其中的方法
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
public class AuthUDTF extends GenericUDTF {

  // 初始化方法
  // 返回值决定了process方法的返回值类型
  @Override
  public StructObjectInspector initialize(StructObjectInspector argOIs) {
      // 指定拆分出来的列名
      List<String> fieldsName = new ArrayList<>();
      fieldsName.add("word");
      // 指定拆分出来的数据类型
      List<ObjectInspector> fieldsType = new ArrayList<>();
      fieldsType.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
      return ObjectInspectorFactory.getStandardStructObjectInspector(fieldsName, fieldsType);
  }

  // 处理数据的方法
  @Override
  public void process(Object[] args) throws HiveException {
      // 判断参数个数
      if (args.length <= 0) throw new HiveException();
      // 获取第一个参数
      String str = args[0].toString();
      // 获取一个切分符号
      String symbol = args.length >= 2 ? args[1].toString() : "-";
      // 切分字符串
      String[] arr = str.split(symbol);
      List<String> list = new ArrayList<>();
      // 遍历数组依次写出
      for (String s : arr) {
          // 将数据放到集合中
          list.add(s);
          // 将数据写出
          forward(list);
          // 写完之后需要清空集合
          list.clear();
      }
  }

  @Override
  public void close() {

  }
}
  1. 指定jar包
1
add jar /opt/hivedemo/cqhive-1.0-SNAPSHOT.jar;
  1. 构建临时函数
1
create temporary function splitarr as 'cn.tedu.udtf.AuthUDTF';

窗口函数

概述

  1. 窗口函数又称之为开窗函数，用于决定要处理的数据量的多少
  2. 基本语法
1
分析函数 over(partition by 字段 order by 字段 起始范围 and 结束范围)
  1. partition by ：对数据进行分类
  2. order by：对数据进行排序
  3. 处理范围
  4. 关键词
| 关键词 | 解释  |
| --- | --- |
| preceding | 往前  |
| following | 往后  |
| current row | 当前行 |
| unbounded | 无边界 |
  1. 案例
    1. unbounded preceding：从第一行开始 2. unbounded following：到最后一行结束 3. 2 preceding and current row：最近三行数据 4. current row and 3 following：从当前行往后再数三行
  2. 分析函数
  3. count(col)：统计指定列的总的个数
  4. sum(col)：针对指定列进行求和
  5. avg(col)：针对指定列进行求平均
  6. max(col)：获取指定列的最大值
  7. min(col)：获取指定列的最小值
  8. lag(col, n)：表示获取往前数第n行的数据。假设当前行是第6行，那么lag(col, 3)表示要处理第3行的数据
  9. lead(col, n)：表示获取往后第n行的数据。
  10. ntile(n)：要求数据必须先排序，排序之后将数据平均分到n个桶中，桶的编号从1开始依次递增。如果数据不能平分到每一个桶中，那么此时每一个桶之间的元素个数最多相差1个
  11. row_number()：不考虑数据是否重复，会顺次进行递增编号
  12. rank()：在数据排序之后会对数据进行自增的编号，如果值相同，那么编号会重复且产生空位
  13. dense_rank()：在数据排序之后会对数据进行自增的编号，如果值相同，那么编号会重复且不产生空位
Program 实训
Hadoop Hive

本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！