Hive（三）函数及应用_hive replaceall

一.运算符

1.关系运算符

关系运算符是二元运算符，执行的是两个操作数的比较运算。每个关系运算符都返回boolean类型结果（TRUE或FALSE）

• 等值比较: = 、==
• 不等值比较: <> 、!=
• 小于比较: <
• 小于等于比较: <=
• 大于比较: >
• 大于等于比较: >=
• 空值判断: IS NULL
• 非空判断: IS NOT NULL
• LIKE比较: LIKE
• JAVA的LIKE操作: RLIKE
• REGEXP操作: REGEXP

--is null空值判断
select 1  where 'gzhu' is null;

--is not null 非空值判断
select 1  where 'gzhu' is not null;

--like比较： _表示任意单个字符 %表示任意数量字符
--否定比较： NOT A like B
select 1  where 'gzhu' like 'gz_';
select 1  where 'gzhu' like 'sz%';
select 1  where  'gzhu' not like 'sz_';

--rlike：确定字符串是否匹配正则表达式，是REGEXP_LIKE()的同义词。
select 1  where 'gzhu' rlike '^g.*u$';
select 1  where '123456' rlike '^\\d+$';  --判断是否全为数字
select 1  where '123456aa' rlike '^\\d+$';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

2.算术运算符

• 加法操作: +
• 减法操作: -
• 乘法操作: *
• 除法操作: /
• 取整操作: div
• 取余操作: %
• 位与操作: &
• 位或操作: |
• 位异或操作: ^
• 位取反操作: ~

3.逻辑运算符

• 与操作: A AND B
• 或操作: A OR B
• 非操作: NOT A 、!A
• 在:A IN (val1, val2, …)
• 不在:A NOT IN (val1, val2, …)
• 逻辑是否存在: [NOT] EXISTS (subquery)

二.Hive函数

Hive的函数很多，除了自己内置所支持的函数之外，还支持用户自己定义开发函数

describe function f; 查看f函数的使用和作用

针对内置的函数，可以根据函数的应用类型进行归纳分类，比如：数值类型函数、日期类型函数、字符串类型函数、集合函数、条件函数等

针对用户自定义函数，可以根据函数的输入输出行数进行分类，比如：UDF、UDAF、UDTF

1.内置函数

①.字符串内置函数

• 字符串长度函数：length
• 字符串反转函数：reverse
• 字符串连接函数：concat
• 带分隔符字符串连接函数：concat_ws
• 字符串截取函数：substr,substring
• 字符串转大写函数：upper,ucase
• 字符串转小写函数：lower,lcase
• 去空格函数：trim
• 左边去空格函数：ltrim
• 右边去空格函数：rtrim
• 正则表达式替换函数：regexp_replace
• 正则表达式解析函数：regexp_extract
• URL解析函数：parse_url
• json解析函数：get_json_object
• 空格字符串函数：space
• 重复字符串函数：repeat
• 首字符ascii函数：ascii
• 左补足函数：lpad
• 右补足函数：rpad
• 分割字符串函数: split
• 集合查找函数: find_in_set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

------------String Functions 字符串函数------------
describe function extended find_in_set;

--字符串长度函数：length(str | binary)
select length("angelababy");

--字符串反转函数：reverse
select reverse("angelababy");

--字符串连接函数：concat(str1, str2, ... strN)
select concat("angela","baby");

--带分隔符字符串连接函数：concat_ws(separator, [string | array(string)]+)
--第一个是分隔符，后面是待拼接的字符串，可以放到几页数组里
select concat_ws('.', 'www', array('gzhu', 'cn')); --输出www.gzhu.cn

--字符串截取函数：substr(str, pos[, len]) 或者  substring(str, pos[, len])
select substr("angelababy",-2); --pos是从1开始的索引，如果为负数则倒着数  输出by
select substr("angelababy",2,2); --输出ng

--字符串转大写函数：upper,ucase
select upper("angelababy");
select ucase("angelababy");

--字符串转小写函数：lower,lcase
select lower("ANGELABABY");
select lcase("ANGELABABY");

--去空格函数：trim 去除左右两边的空格
select trim(" angelababy ");

--左边去空格函数：ltrim
select ltrim(" angelababy ");

--右边去空格函数：rtrim
select rtrim(" angelababy ");

--正则表达式替换函数：regexp_replace(str, regexp, rep)
select regexp_replace('18- yes .200', '(\\d+)', 'num'); --输出num- yes .num

--正则表达式解析函数：regexp_extract(str, regexp , idx]) 提取正则匹配到的指定组内容
select regexp_extract('18-yes200', '(\\d+)-yes(\\d+)', 1);  --输出18

--URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数
select parse_url('http://www.baidu.cn/path/p1.php?query=1', 'HOST');

--json解析函数：get_json_object
--空格字符串函数：space(n) 返回指定个数空格
select space(4);

--重复字符串函数：repeat(str, n) 重复str字符串n次
select repeat("angela",2);--angelaangela

--首字符ascii函数：ascii
select ascii("angela");  --a对应ASCII 97

--左补足函数：lpad
--位数不足补  只会显示第二个参数个数的位数
select lpad('hi', 5, '??');  --输出???hi
select lpad('hi', 1, '??');  --输出h

--右补足函数：rpad
select rpad('hi', 5, '??');

--分割字符串函数: split(str, regex)
select split('apache hive', '\\s+'); --输出["apache","hive"]

--集合查找函数: find_in_set(str,str_array)
--返回查到的第一个的下标  从1开始
select find_in_set('a','abc,b,a,c,def'); --输出3
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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70

②.日期内置函数

主要针对时间、日期数据类型进行操作，比如下面这些：

• 获取当前日期: current_date
• 获取当前时间戳: current_timestamp
• UNIX时间戳转日期函数: from_unixtime
• 获取当前UNIX时间戳函数: unix_timestamp
• 日期转UNIX时间戳函数: unix_timestamp
• 指定格式日期转UNIX时间戳函数: unix_timestamp
• 抽取日期函数: to_date
• 日期转年函数: year
• 日期转月函数: month
• 日期转天函数: day
• 日期转小时函数: hour
• 日期转分钟函数: minute
• 日期转秒函数: second
• 日期转周函数: weekofyear
• 日期比较函数: datediff
• 日期增加函数: date_add
• 日期减少函数: date_sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

--获取当前时间戳: current_timestamp
--同一查询中对current_timestamp的所有调用均返回相同的值。
select current_timestamp(); 

--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();  // 时间戳直接相减就是秒数

--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1618238391); --输出2021-04-12 14:39:51

--日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03");

--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');

--抽取日期函数: to_date
select to_date('2022-05-05 04:17:52'); --输出2022-05-05

--日期转年函数: year
select year('2022-05-05 04:17:52'); --输出2022

--日期转月函数: month
select month('2022-05-05 04:17:52');

--日期转天函数: day
select day('2022-05-05 04:17:52');

--日期转小时函数: hour
select hour('2022-05-05 04:17:52');

--日期转分钟函数: minute
select minute('2022-05-05 04:17:52');

--日期转秒函数: second
select second('2022-05-05 04:17:52');

--日期转周函数: weekofyear 返回指定日期所示年份第几周
select weekofyear('2022-05-05 04:17:52'); --输出18

--日期比较函数: datediff  日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2022-05-05 04:17:52','2022-05-03 04:18:52'); --输出2 表示差2天

--日期增加函数: date_add
select date_add('2022-02-28',10); --加10天的日期

--日期减少函数: date_sub
select date_sub('2022-01-1',10); --减10天的日期
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

③.数学函数
主要针对数值类型的数据进行数学计算，比如下面这些：

• 取整函数: round
• 指定精度取整函数: round
• 向下取整函数: floor
• 向上取整函数: ceil
• 取随机数函数: rand
• 二进制函数: bin
• 进制转换函数: conv
• 绝对值函数: abs
1
2
3
4
5
6
7
8

--取整函数: round  返回double类型的整数值部分 （遵循四舍五入）
select round(3.1415926); --输出3

--指定精度取整函数: round(double a, int d) 返回指定精度d的double类型
select round(3.1415926,4);--输出3.1416

--向下取整函数: floor
select floor(3.1415926);
select floor(-3.1415926);

--向上取整函数: ceil
select ceil(3.1415926);
select ceil(-3.1415926);

--取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数
select rand(); --可能输出0.841555154323

--指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列
select rand(3);

--二进制函数:  bin(BIGINT a)
select bin(18);--输出10010

--进制转换函数: conv(BIGINT num, int from_base, int to_base)
select conv(17,10,16);--将17的10十进制转化为16进制

--绝对值函数: abs
select abs(-3.9);
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

④.集合函数
主要针对集合这样的复杂数据类型进行操作，比如下面这些：

• 集合元素size函数: size(Map<K.V>) size(Array<T>)
• 取map集合keys函数: map_keys(Map<K.V>)
• 取map集合values函数: map_values(Map<K.V>)
• 判断数组是否包含指定元素: array_contains(Array<T>, value)
• 数组排序函数:sort_array(Array<T>)
1
2
3
4
5

--集合元素size函数: size(Map<K.V>) size(Array<T>)
select size(`array`(11,22,33));
select size(`map`("id",10086,"name","zhangsan","age",18)); --输出3 因为有3个KV

--取map集合keys函数: map_keys(Map<K.V>)
select map_keys(`map`("id",10086,"name","zhangsan","age",18));--输出["id","name","age"]

--取map集合values函数: map_values(Map<K.V>)
select map_values(`map`("id",10086,"name","zhangsan","age",18));

--判断数组是否包含指定元素: array_contains(Array<T>, value)
select array_contains(`array`(11,22,33),11);--true
select array_contains(`array`(11,22,33),66);

--数组排序函数:sort_array(Array<T>)
select sort_array(`array`(12,2,32));--输出[2,12,32]
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

⑤.条件函数

• if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
• 空判断函数: isnull( a )
• 非空判断函数: isnotnull ( a )
• 空值转换函数: nvl(T value, T default_value)
• 非空查找函数: COALESCE(T v1, T v2, ...)
• 条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
• nullif( a, b ): 如果a = b，则返回NULL；否则返回NULL。否则返回一个
• assert_true: 如果'condition'不为真，则引发异常，否则返回null
1
2
3
4
5
6
7
8

--if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200);--1=2 false 所以输出200
select if(age = 16,18,28) from t_person where name = "kun";--判断name为kun的人年龄是否是16 是输出18

--空判断函数: isnull( a )
select isnull("allen");
select isnull(null);

--非空判断函数: isnotnull ( a )
select isnotnull("allen");
select isnotnull(null);

--空值转换函数: nvl(null, T default_value)
select nvl(null,"gzhu");--输出gzhu

--非空查找函数: COALESCE(T v1, T v2, ...)
--返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL
select COALESCE(null,11,22,33); --输出11
select COALESCE(null,null,null);--输出null

--条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

select case age
    when 16 then "年龄16"
    when 18 then "年龄18"
    else "年龄其他"
    end
from t_person limit 3;

--nullif( a, b ):
-- 如果a = b，则返回NULL；否则返回NULL。否则返回一个
select nullif(11,11);
select nullif(11,12);

--assert_true(condition)
--如果'condition'不为真，则引发异常，否则返回null
SELECT assert_true(11 >= 0);
SELECT assert_true(-1 >= 0);
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

⑥.类型转换函数

主要用于显式的数据类型转换，有下面两种函数：

select cast(12.14 as bigint);
select cast(12.14 as string);
1
2

⑦.脱敏函数

• mask
• mask_first_n(string str[, int n]
• mask_last_n(string str[, int n])
• mask_show_first_n(string str[, int n])
• mask_show_last_n(string str[, int n])
• mask_hash(string|char|varchar str)
1
2
3
4
5
6

--mask
--将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n
select mask("abc123DEF"); --输出xxxnnnXXX
select mask("abc123DEF",'-','.','^'); --自定义替换的字母 ...^^^---

--mask_first_n(string str[, int n]
--对前n个进行脱敏替换
select mask_first_n("abc123DEF",4);--xxxn23DEF

--mask_last_n(string str[, int n])
select mask_last_n("abc123DEF",4);--abc12nXXX

--mask_show_first_n(string str[, int n])
--除了前n个字符，其余进行掩码处理
select mask_show_first_n("abc123DEF",4);--abc1nnXXX

--mask_show_last_n(string str[, int n])
select mask_show_last_n("abc123DEF",4);

--mask_hash(string|char|varchar str)
--返回字符串的hash编码
select mask_hash("abc123DEF");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

⑧.其他

• hive调用java方法: java_method(class, method[, arg1[, arg2..]])
• 反射函数: reflect(class, method[, arg1[, arg2..]])
• 取哈希值函数:hash
• current_user()、logged_in_user()、current_database()、version()
• SHA-1加密: sha1(string/binary)
• SHA-2家族算法加密：sha2(string/binary, int)  (SHA-224, SHA-256, SHA-384, SHA-512)
• crc32加密:
• MD5加密: md5(string/binary)
1
2
3
4
5
6
7
8

--hive调用java方法: java_method(class, method[, arg1[, arg2..]])
select java_method("java.lang.Math","max",11,22); --22

--反射函数: reflect(class, method[, arg1[, arg2..]])
select reflect("java.lang.Math","max",11,22);--22

--取哈希值函数:hash
select hash("allen");

--current_user()、logged_in_user()、current_database()、version()

--SHA-1加密: sha1(string/binary)
select sha1("allen");

--SHA-2家族算法加密：sha2(string/binary, int)  (SHA-224, SHA-256, SHA-384, SHA-512)
select sha2("allen",224);
select sha2("allen",512);

--crc32加密:
select crc32("allen");

--MD5加密: md5(string/binary)
select md5("allen");
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

2.自定义UDF示例

虽然说Hive内置了很多函数，但是不见得一定可以满足于用户各种各样的分析需求场景。为了解决这个问题，Hive推出来用户自定义函数功能，让用户实现自己希望实现的功能函数
用户自定义函数简称UDF，源自于英文user-defined function。自定义函数总共有3类，是根据函数输入输出的行数来区分的，分别是：

UDF（User-Defined-Function）普通函数，一进一出
UDAF（User-Defined Aggregation Function）聚合函数，多进一出
UDTF（User-Defined Table-Generating Functions）表生成函数，一进多出

UDF分类标准扩大化

虽然说UDF叫做用户自定义函数，其分类标准主要针对的是用户编写开发的函数

但是这套UDF分类标准可以扩大到Hive的所有函数中：包括内置函数和自定义函数。因为不管是什么类型的行数，一定满足于输入输出的要求，那么从输入几行和输出几行上来划分没有任何毛病。千万不要被UD（User-Defined）这两个字母所迷惑，照成视野的狭隘
比如Hive官方文档中，针对聚合函数的标准就是内置的UDAF类型

案例

在企业中处理数据的时候，对于敏感数据往往需要进行脱敏处理。比如手机号。我们常见的处理方式是将手机号中间4位进行****处理

Hive中没有这样的函数可以直接实现功能，虽然可以通过各种函数的嵌套调用最终也能实现，但是效率不高，现要求自定义开发实现Hive函数，满足上述需求

1、能够对输入数据进行非空判断、位数判断处理
2、能够实现校验手机号格式，把满足规则的进行****处理
3、对于不符合手机号规则的数据原封不动不处理

导入依赖

<dependencies>
     <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>
             <exclusion>
                 <groupId>org.eclipse.jetty</groupId>
                 <artifactId>jetty-runner</artifactId>
             </exclusion>
         </exclusions>
     </dependency>
     <dependency>
         <groupId>org.apache.hadoop</groupId>
         <artifactId>hadoop-common</artifactId>
         <version>3.1.2</version>
     </dependency>
     <dependency>
         <groupId>org.apache.hive</groupId>
         <artifactId>hive-exec</artifactId>
         <version>3.1.2</version>
     </dependency>
 </dependencies>
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

继承UDF，编写逻辑代码，这就是我们自定义的方法

public class EncryptPhoneNumber extends UDF {
    /**
     * 重载evaluate方法 实现函数的业务逻辑
     * @param phoNum  入参：未加密手机号
     * @return 返回：加密后的手机号字符串
     */
    public String evaluate(String phoNum){
        String encryptPhoNum = null;
        //手机号不为空 并且为11位
        if (StringUtils.isNotEmpty(phoNum) && phoNum.trim().length() == 11 ) {
            //判断数据是否满足中国大陆手机号码规范
            String regex = "^(1[3-9]\\d{9}$)";
            Pattern p = Pattern.compile(regex);
            Matcher m = p.matcher(phoNum);
            if (m.matches()) {//进入这里都是符合手机号规则的
                //使用正则替换 返回加密后数据
                encryptPhoNum = phoNum.trim().replaceAll("(\\d{3})\\d{4}(\\d{4})","$1****$2");
            }else{
                //不符合手机号规则 数据直接原封不动返回
                encryptPhoNum = phoNum;
            }
        }else{
            //不符合11位 数据直接原封不动返回
            encryptPhoNum = phoNum;
        }
        return encryptPhoNum;
    }
}
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

打包插件

<build>
     <plugins>
         <plugin>
             <artifactId>maven-compiler-plugin</artifactId>
             <version>3.6.1</version>
             <configuration>
                 <source>1.8</source>
                 <target>1.8</target>
             </configuration>
         </plugin>
         <plugin>
             <artifactId>maven-assembly-plugin</artifactId>
             <configuration>
                 <descriptorRefs>
                     <descriptorRef>jar-with-dependencies</descriptorRef>
                 </descriptorRefs>
             </configuration>
             <executions>
                 <execution>
                     <id>make-assembly</id>
                     <phase>package</phase>
                     <goals>
                         <goal>single</goal>
                     </goals>
                 </execution>
             </executions>
         </plugin>
     </plugins>
 </build>
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

3.UDTF - explode

对于UDTF表生成函数，很多人难以理解什么叫做输入一行，输出多行
为什么叫做表生成？能够产生表吗？下面我们就来学习Hive当做内置的一个非常著名的UDTF函数，名字叫做explode函数，中文戏称之为"爆炸函数"，可以炸开数据

explode函数接收map或者array类型的数据作为参数，然后把参数中的每个元素炸开变成一行数据。一个元素一行。这样的效果正好满足于输入一行输出多行

explode函数在关系型数据库中本身是不该出现的

因为他的出现本身就是在操作不满足第一范式的数据（每个属性都不可再分）。本身已经违背了数据库的设计原理，但是在面向分析的数据库或者数据仓库中，这些规范可以发生改变

explode(array)将array列表里的每个元素生成一行
explode(map)将map里的每一对元素作为一行，其中key为一列，value为一列

很明显，explode函数是生成的是一张表，不过是一张虚表！！！

在select条件中，如果只有explode函数表达式，程序执行是没有任何问题的
但是如果在select条件中，包含explode和其他字段，就会报错。错误信息为：
org.apache.hadoop.hive.ql.parse.SemanticException:UDTF’s are not supported outside the SELECT clause, nor nested in expressions
那么如何理解这个错误呢？为什么在select的时候，explode的旁边不支持其他字段的同时出现？

explode函数属于UDTF函数，即表生成函数，explode函数执行返回的结果可以理解为一张虚拟的表，其数据来源于源表；在select中只查询源表数据没有问题，只查询explode生成的虚拟表数据也没问题，但是不能在只查询源表的时候，既想返回源表字段又想返回explode生成的虚拟表字段

从SQL层面上来说应该对两张表进行关联查询，Hive专门提供了语法lateral View侧视图，专门用于搭配explode这样的UDTF函数，以满足上述需要

Lateral View一般与用户自定义表生成函数（如explode()）结合使用。 如内置表生成函数中所述，UDTF为每个输入行生成零个或多个输出行。 Lateral View 首先将UDTF应用于基表的每一行，然后将结果输出行连接到输入行，以形成具有提供的表别名的虚拟表

这个关键字我们可以看做join

例如有下表the_nba_championship，第一列是NBA球队名字，第二列是夺冠年份

炸开年份

假如我们想把球队和夺冠年份一对一怎么办？

select a.team_name,b.year
from the_nba_championship a lateral view explode(a.champion_year) b as year;
1
2

注意，from后面其实是一张联合表

the_nba_championship a lateral view explode(a.champion_year) b as year;
1

the_nba_championship起别名为a，lateral view充当join的作用

explode(a.champion_year) b as year; 炸开年份，只有一列，as后面是列名

这样得到了一张表，结构是这样的

可以看到，已经自动帮我们对应好了！！

UDTF函数的问题

Hive中的一对多的UDTF函数可以实现高效的数据转换，但是也存在着一些使用中的问题，UDTF函数对于很多场景下有使用限制，例如：select时不能包含其他字段、不能嵌套调用、不能与group by等放在一起调用等等

UDTF函数的调用方式，主要有以下两种方式：
方式一：直接在select后单独使用
方式二：与Lateral View放在一起使用

4.UDAF

HQL提供了几种内置的UDAF聚合函数，例如max（…），min（…）和avg（…）。这些我们把它称之为基础的聚合函数

通常情况下，聚合函数会与GROUP BY子句一起使用。 如果未指定GROUP BY子句，默认情况下，它会汇总所有行数据

22行学生信息

--场景1：没有group by子句的聚合操作
select count(*) as cnt1,count(1) as cnt2 from gzhu.student; --两个一样

--场景2：带有group by子句的聚合操作 注意group by语法限制
select sex,count(*) as cnt from student group by sex;

--场景3：select时多个聚合函数一起使用
select count(*) as cnt1,avg(age) as cnt2 from student;

--场景4：聚合函数和case when条件转换函数、coalesce函数、if函数使用
select
    sum(CASE WHEN sex = '男'THEN 1 ELSE 0 END)
from student;

select
    sum(if(sex = '男',1,0))
from student;

--场景5：聚合参数不支持嵌套聚合函数
select avg(count(*))  from student;

--聚合参数针对null的处理方式
--null null 0
select max(null), min(null), count(null);
--下面这两个不支持null
select sum(null), avg(null);

--场景5：聚合操作时针对null的处理
CREATE TABLE tmp_1 (val1 int, val2 int);
INSERT INTO TABLE tmp_1 VALUES (1, 2),(null,2),(2,3);
select * from tmp_1;
--第二行数据(NULL, 2) 在进行sum(val1 + val2)的时候会被忽略
select sum(val1), sum(val1 + val2) from tmp_1;
--可以使用coalesce函数解决
select
    sum(coalesce(val1,0)),
    sum(coalesce(val1,0) + val2)
from tmp_1;

--场景6：配合distinct关键字去重聚合
--此场景下，会编译期间会自动设置只启动一个reduce task处理数据  性能可能会不会 造成数据拥堵
select count(distinct sex) as cnt1 from student;
--可以先去重 在聚合 通过子查询完成
--因为先执行distinct的时候 可以使用多个reducetask来跑数据
select count(*) as gender_uni_cnt
from (select distinct sex from student) a;

--案例需求：找出student中男女学生年龄最大的及其名字
--这里使用了struct来构造数据 然后针对struct应用max找出最大元素 然后取值
select sex,
       max(struct(age, name)).col1 as age,
       max(struct(age, name)).col2 as name
from student
group by sex;

select struct(age, name) from student;
select struct(age, name).col1 from student;
select max(struct(age, name)) from student;
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
53
54
55
56
57
58

增强聚合函数

增强聚合的grouping_sets、cube、rollup这几个函数主要适用于OLAP多维数据分析模式中，多维分析中的维指的分析问题时看待问题的维度、角度

下面我们来准备一下数据，通过案例更好的理解函数的功能含义

一.grouping sets是一种将多个group by逻辑写在一个sql语句中的便利写法，另外的置空

等价于将不同维度的GROUP BY结果集进行UNION ALL

GROUPING__ID表示结果属于哪一个分组集合

二.cube的语法功能指的是：根据GROUP BY的维度的所有组合进行聚合

对于cube,如果有n个维度,则所有组合的总个数是：2^n

比如Cube有a,b,c3个维度，则所有组合情况是：
((a,b,c),(a,b),(b,c),(a,c),(a),(b),©,())

SELECT
    month,
    day,
    COUNT(DISTINCT cookieid) AS nums,
    GROUPING__ID
FROM cookie_info
GROUP BY month,day
WITH CUBE
ORDER BY GROUPING__ID;
1
2
3
4
5
6
7
8
9

相当于分析了2 * 2 = 4个维度 (month,day) (month,null) (null,day) (null,null)

三.cube的语法功能指的是：根据GROUP BY的维度的所有组合进行聚合

rollup是Cube的子集，以最左侧的维度为主，从该维度进行层级聚合

比如ROLLUP有a,b,c3个维度，则所有组合情况是：
((a,b,c),(a,b),(a),())

SELECT
    day,
    month,
    COUNT(DISTINCT cookieid) AS uv,
    GROUPING__ID
FROM cookie_info
GROUP BY day,month
WITH ROLLUP
ORDER BY GROUPING__ID;
1
2
3
4
5
6
7
8
9

相当于分析了3 个维度 (day,month) (day,null) (null,null)

(null,month)不是左边原则

5.窗口聚合函数

窗口函数（Window functions）是一种SQL函数，非常适合于数据分析，因此也叫做OLAP函数，其最大特点是：输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。你也可以理解为窗口有大有小（行有多有少）

通过OVER子句，窗口函数与其他SQL函数有所区别。如果函数具有OVER子句，则它是窗口函数。如果它缺少OVER子句，则它是一个普通的聚合函数

窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行，窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中

窗口函数语法

Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])
1

–其中Function(arg1,…, argn) 可以是下面分类中的任意一个
–聚合函数：比如sum max avg等
–排序函数：比如rank row_number等
–分析函数：比如lead lag first_value等

–OVER [PARTITION BY <…>] 类似于group by 用于指定分组 每个分组你可以把它叫做窗口（如果没有PARTITION BY 那么整张表的所有行就是一组）

–[ORDER BY <…>] 用于指定每个分组内的数据排序规则 支持ASC、DESC

–[<window_expression>] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行

窗口聚合函数的使用

顾名思义，是窗口函数 + 普通聚合函数的使用

聚合函数：比如sum max avg等

website_pv_info表如下

第一列是cookieId，第二列是某天，第三列是某天某用户访问了某页面几次

普通聚合

只加over()：行不变，每一行都多一个聚合字段

over指定分区字段，行不变，每一行多一个聚合值（分区为单位）

over指定分区并且指定分区内的排序字段 这里警惕，指定了order by是连续累计求和

案例：统计一年内某用户的级联累计消费

select
    userid,
    mth,
    m_money,
    sum(m_money) over (partition by userid order by mth) as t_money
from tb_money_mtn;
1
2
3
4
5
6

[<window_expression>]

Window expression窗口表达式给我们提供了一种控制行范围的能力，比如向前2行，向后3行

语法如下：

关键字是rows between，包括下面这几个选项

• preceding：往前
• following：往后
• current row：当前行
• unbounded：边界
• unbounded preceding 表示起点
• unbounded following：表示终点

下图表示从当前行往前3行（包括本行）

下图表示从当前行到最后一行（包括本行）

6.窗口排序函数 分组topN

窗口排序函数用于给每个分组内的数据打上排序的标号。注意窗口排序函数不支持窗口表达式。总共有4个函数需要掌握：

①.row_number：在每个分组中，为每行分配一个从1开始的唯一序列号，递增，不考虑重复
②.rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，挤占后续位置
③.dense_rank: 在每个分组中，为每行分配一个从1开始的序列号，考虑重复，不挤占后续位置

还有一个函数，叫做ntile函数，其功能为：将每个分组内的数据分为指定的若干个桶里（分为若干个部分），并且为每一个桶分配一个桶编号
如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足

7.窗口分析函数

• lag(col,n,DEFAULT) 用于统计窗口内往上第n行值
  第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

• lead(col,n,DEFAULT) 用于统计窗口内往下第n行值
  第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

• FIRST_VALUE 取分组内排序后，截止到当前行，第一个值

• LAST_VALUE 取分组内排序后，截止到当前行，最后一个值

案例：哪些用户连续登录2天

with t1 as (
  select
    userid,
    logintime,
    --本次登陆日期的第二天
      date_add(logintime,1) as nextday,
    --按照用户id分区，按照登陆日期排序，取下一次登陆时间，取不到就为0
     lead(logintime,1,0) over (partition by userid order by logintime) as nextlogin
from tb_login )
select distinct userid from t1 where nextday = nextlogin;
1
2
3
4
5
6
7
8
9
10

8.抽样函数

当数据量过大时，我们可能需要查找数据子集以加快数据处理速度分析。 这就是抽样、采样，一种用于识别和分析数据中的子集的技术，以发现整个数据集中的模式和趋势

随机抽样使用rand（）函数和LIMIT关键字来获取数据。 使用了DISTRIBUTE和SORT关键字，可以确保数据也随机分布在mapper和reducer之间，使得底层执行有效率。
ORDER BY 和rand（）语句也可以达到相同的目的，但是表现不好。因为ORDER BY是全局排序，只会启动运行一个Reducer

Block块采样允许select随机获取n行数据，即数据大小或n个字节的数据

采样粒度是HDFS块大小

---block抽样
--根据行数抽样
SELECT * FROM student TABLESAMPLE(1 ROWS);

--根据数据大小百分比抽样 随机抽50%
SELECT * FROM student TABLESAMPLE(50 PERCENT);

--根据数据大小抽样
--支持数据单位 b/B, k/K, m/M, g/G
SELECT * FROM student TABLESAMPLE(1k);
1
2
3
4
5
6
7
8
9
10

Bucket table分桶表抽样 这是一种特殊的采样方法，针对分桶表进行了优化

---bucket table抽样
--根据整行数据进行抽样
SELECT * FROM t_usa_covid19_bucket TABLESAMPLE(BUCKET 2 OUT OF 2 ON rand());

--根据分桶字段进行抽样 效率更高
describe formatted t_usa_covid19_bucket;
SELECT * FROM t_usa_covid19_bucket TABLESAMPLE(BUCKET 3 OUT OF 8 ON state);
1
2
3
4
5
6
7

TABLESAMPLE(BUCKET x OUT OF y ON rand())

TABLESAMPLE是关键字， x代表从那个桶开始抽，y必须是桶数量的倍数或者因子，如果抽取多个桶，那么以后的桶号为当前桶号+y，x <= y

上述中，假如数据有4个桶

y = 2，表示抽4/2=2个桶的数据，x = 2，表示从第二个桶开始抽，由于抽2个桶，第二个桶为2 + 2 = 4号桶

y=8，表示抽取4/8 = 1/2个桶的数据，x = 3，从第三个桶开始，抽1/2个桶的数据，也就是抽第三个桶的1/2

on rand()表示根据某个字段随机分桶，on col表示基于col字段分桶抽

三.Hive函数应用

1.复杂分隔符解决方案

Hive中默认使用单字节分隔符来加载文本数据，例如逗号、制表符、空格等等，默认的分隔符为\001。根据不同文件的不同分隔符，我们可以通过在创建表时使用 row format delimited fields terminated by ‘单字节分隔符’ 来指定文件中的分割符，确保正确将表中的每一列与文件中的每一列实现一一对应的关系

情况一 但时我们看下表的数据，每列数据时以空格分割的，第一列是IP地址，第二列是时间，我们可以看到，时间这一列还包含一个空格，这样我们再以空格的分的话就会把原本是一列的时间分成两列，导致后面的列也错位了

情况二 下面每列分隔符不是单字符了，而是一个双字符||，那ROW FORMAT DELIMITED此时就不支持了
三种解决方案

一.RegexSerde正则匹配（推荐）

RegexSerde是Hive中专门为了满足复杂数据场景所提供的正则加载和解析数据的接口，使用RegexSerde可以指定正则表达式加载数据，根据正则表达式匹配每一列数据

一个正则分组()对应一列

drop table if exists apachelog;
create table apachelog(
      ip string,      --IP地址
      stime string,    --时间
      mothed string,  --请求方式
      url string,     --请求地址
      policy string,  --请求协议
      stat string,    --请求状态
      body string     --字节大小
)
--指定使用RegexSerde加载数据
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
--指定正则表达式
WITH SERDEPROPERTIES (
"input.regex" = "([0-9.]*)\\s([\\w\\:\\+\\/\\s\\[\\]]*)\\s\"([A-Z]{3,4})\\s([\\/\\w\\.]*)\\s(HTTP\\/[\\d\\.]*)\"\\s([0-9]{3})\\s([0-9\\-]{1,})"
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

上传文件

二.清洗数据

例如将上述的数据||全部更换为|，变成单字符

三.自定义InutFormat
复杂，成本高，不推荐

2.URL解析函数

一个标准的URL一般包括

• PROTOCOL：协议类型
  通信协议类型，一般也叫作Schema，常见的有http、https等；
• HOST：域名
  一般为服务器的域名主机名或ip地址
• PATH：访问路径
  访问路径目录，由“/”隔开的字符串，表示的是主机上的目录或文件地址
• QUERY：参数数据
  查询参数，此项为可选项，可以给动态网页传递参数，用“&”隔开，每个参数的名和值用“=”隔开

有如下表tb_url

parse_url

parse_url函数是Hive中提供的最基本的url解析函数，可以根据指定的参数，从URL解析出对应的参数值进行返回，函数为普通的一对一函数类型

parse_url(url, partToExtract[, key]) - extracts a part from a URL

Parts: HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO

parse_url在使用时需要指定两个参数
第一个参数：url：指定要解析的URL
第二个参数：key：指定要解析的内容

缺点就是第二个参数只能指定一个，想要查询多个参数，只能多次调用parse_url

parse_url_tuple

parse_url_tuple函数是Hive中提供的基于parse_url的url解析函数，可以通过一次指定多个参数，从URL解析出多个参数的值进行返回多列，函数为特殊的一对多函数类型，即通常所说的UDTF函数类型

因为使用parse_url_tuple生成的是虚表，所以想要联合其他字段查询，可以使用侧视图查询

3.行列转换

多行转多列

主要是用case-when语句

CASE 列
WHEN V1 THEN VALUE1
WHEN V2 THEN VALUE2
……
WHEN VN THEN VALUEN
ELSE 默认值
END
1
2
3
4
5
6
7

多行转单列

concat

功能：用于实现字符串拼接，不可指定分隔符

语法：concat(element1,element2,element3……)

如果任意一个元素为null，结果就为null

concat_ws

功能：用于实现字符串拼接，可以指定分隔符

语法：concat_ws(SplitChar，element1，element2……)

特点：任意一个元素不为null，结果就不为null

collect_list

功能：用于将一列中的多行合并为一行，不进行去重

语法：collect_list（colName）

concat_set

功能：用于将一列中的多行合并为一行，并进行去重

语法：collect_set（colName）

多行转单列就是用concat_ws + collect_list

多列转多行

union

功能：将多个select语句结果合并为一个，且结果去重且排序

语法
select_statement
UNION [DISTINCT]
select_statement
UNION [DISTINCT]
select_statement …

union all

功能：将多个select语句结果合并为一个，且结果不去重不排序

语法
select_statement UNION ALL select_statement UNION ALL select_statement …

多列转多行主要用union all

select col1, 'c' as col2, col2 as col3 from col2row1
UNION ALL
select col1, 'd' as col2, col3 as col3 from col2row1
UNION ALL
select col1, 'e' as col2, col4 as col3 from col2row1;
1
2
3
4
5

多列转单行

explode关键字

select
  col1,
  col2,
  lv.col3 as col3
from
  col2row2
    lateral view
  explode(split(col3, ',')) lv as col3;
1
2
3
4
5
6
7
8

4.JSON数据处理

Hive中为了实现JSON格式的数据解析，提供了两种解析JSON数据的方式，在实际工作场景下，可以根据不同数据，不同的需求来选择合适的方式对JSON格式数据进行处理

方式一：使用JSON函数进行处理

Hive中提供了两个专门用于解析JSON字符串的函数：get_json_object、json_tuple，这两个函数都可以实现将JSON数据中的每个字段独立解析出来，构建成表

实际上，我们是将一行JSON作为字符串

select get_json_object(json,"$.time") as stime from tb_json_test1;
1

get_json_object(json,“$.xxx”)一次只能查询一个字段

json_tuple(json,"col1","col2"..)可以查询多个字段
1

json_tuple是一个UDTF函数，要结合侧视图使用

方式二：使用Hive内置的JSON Serde加载数据

Hive中除了提供JSON的解析函数以外，还提供了一种专门用于加载JSON文件的Serde来实现对JSON文件中数据的解析，在创建表时指定Serde，加载文件到表中，会自动解析为对应的表格式

--创建表
create table tb_json_test2 (
   device string,
   deviceType string,
   signal double,
   `time` string
 )
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS TEXTFILE;
1
2
3
4
5
6
7
8
9

5.拉链表

拉链表专门用于解决在数据仓库中数据发生变化如何实现数据存储的问题，如果直接覆盖历史状态，会导致无法查询历史状态，如果将所有数据单独切片存储，会导致存储大量非更新数据的问题。拉链表的设计是将更新的数据进行状态记录，没有发生更新的数据不进行状态存储，用于存储所有数据在不同时间上的所有状态，通过时间进行标记每个状态的生命周期，查询时，根据需求可以获取指定时间范围状态的数据，默认用9999-12-31等最大值来表示最新状态

案例

一共需要三张表，①原数据表 ②增量变化表 ③临时表

①原数据表

create table dw_zipper(
      userid string,
      phone string,
      nick string,
      gender int,
      addr string,
      starttime string,
      endtime string
) row format delimited fields terminated by '\t';
1
2
3
4
5
6
7
8
9

②增量变化表

create table ods_zipper_update(
          userid string,
          phone string,
          nick string,
          gender int,
          addr string,
          starttime string,
          endtime string
) row format delimited fields terminated by '\t';
1
2
3
4
5
6
7
8
9

③临时表

create table tmp_zipper(
  userid string,
  phone string,
  nick string,
  gender int,
  addr string,
  starttime string,
  endtime string
) row format delimited fields terminated by '\t';
1
2
3
4
5
6
7
8
9

合并 将原数据表和增量变化表进行union all合并插入临时表，首先，增量变化表直接插入临时表，因为这都是最新的数据，然后原数据表哪些插入临时表呢？首先，endtime小于9999年的肯定插入，这些都是历史数据，其次，通过原数据表和增量表进行左连接，可以定位出哪些是update的数据，然后将原数据表的endtime更改为增量表strattime加1就可以了

insert overwrite table tmp_zipper
select
    userid,
    phone,
    nick,
    gender,
    addr,
    starttime,
    endtime
from ods_zipper_update
union all
--查询原来拉链表的所有数据，并将这次需要更新的数据的endTime更改为更新值的startTime
select
    a.userid,
    a.phone,
    a.nick,
    a.gender,
    a.addr,
    a.starttime,
    --如果这条数据没有更新或者这条数据不是要更改的数据，就保留原来的值，否则就改为新数据的开始时间-1
    if(b.userid is null or a.endtime < '9999-12-31', a.endtime , date_sub(b.starttime,1)) as endtime
from dw_zipper a  left join ods_zipper_update b
    on a.userid = b.userid ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

最后把数据更新到原数据表即可

insert overwrite table dw_zipper
select * from tmp_zipper;
1
2

四.Hive的一些简单优化

1.explain

explain [formatted|extended|dependency|authorization] query

formatted ：对执行计划进行格式化，返回JSON格式的执行计划
extended：提供一些额外的信息，比如文件的路径信息
dependency：以JSON格式返回查询所依赖的表和分区的列表
authorization：列出需要被授权的条目，包括输入与输出

每个查询计划由以下几个部分组成

• The Abstract Syntax Tree for the query
  抽象语法树（AST）：Hive使用Antlr解析生成器，可以自动地将HQL生成为抽象语法树
• The dependencies between the different stages of the plan
  Stage依赖关系：会列出运行查询划分的stage阶段以及之间的依赖关系
• The description of each of the stages
  Stage内容：包含了每个stage非常重要的信息，比如运行时的operator和sort orders等具体的信息

2.MapReduce属性优化

本地模式

使用Hive的过程中，有一些数据量不大的表也会转换为MapReduce处理，提交到集群时，需要申请资源，等待资源分配，启动JVM进程，再运行Task，一系列的过程比较繁琐，本身数据量并不大，提交到YARN运行返回会导致性能较差的问题
Hive为了解决这个问题，延用了MapReduce中的设计，提供本地计算模式，允许程序不提交给YARN，直接在本地运行，以便于提高小数据量程序的性能

-- 开启本地模式
set hive.exec.mode.local.auto = true;
1
2

限制条件

Hive为了避免大数据量的计算也使用本地模式导致性能差的问题，所以对本地模式做了以下限制，如果以下任意一个条件不满足，那么即使开启了本地模式，将依旧会提交给YARN集群运行

• 处理的数据量不超过128M
• MapTask的个数不超过4个
• ReduceTask的个数不超过1个

JVM重用

JVM正常指代一个Java进程，Hadoop默认使用派生的JVM来执行map-reducer，如果一个MapReduce程序中有100个Map，10个Reduce，Hadoop默认会为每个Task启动一个JVM来运行，那么就会启动100个JVM来运行MapTask，在JVM启动时内存开销大，尤其是Job大数据量情况，如果单个Task数据量比较小，也会申请JVM资源，这就导致了资源紧张及浪费的情况
为了解决上述问题，MapReduce中提供了JVM重用机制来解决，JVM重用可以使得JVM实例在同一个job中重新使用N次，当一个Task运行结束以后，JVM不会进行释放，而是继续供下一个Task运行，直到运行了N个Task以后，就会释放，N的值可以在Hadoop的mapred-site.xml文件中进行配置，通常在10-20之间

配置

-- Hadoop3之前的配置，在mapred-site.xml中添加以下参数
-- Hadoop3中已不再支持该选项
mapreduce.job.jvm.numtasks=10 
1
2
3

并行执行

Hive在实现HQL计算运行时，会解析为多个Stage，有时候Stage彼此之间有依赖关系，只能挨个执行，但是在一些别的场景下，很多的Stage之间是没有依赖关系的，例如Union语句，Join语句等等，这些Stage没有依赖关系，但是Hive依旧默认挨个执行每个Stage，这样会导致性能非常差，我们可以通过修改参数，开启并行执行，当多个Stage之间没有依赖关系时，允许多个Stage并行执行，提高性能

配置

-- 开启Stage并行化，默认为false
SET hive.exec.parallel=true;
-- 指定并行化线程数，默认为8
SET hive.exec.parallel.thread.number=16; 
1
2
3
4

注意：线程数越多，程序运行速度越快，但同样更消耗CPU资源

3.JOIN优化

表的Join是数据分析处理过程中必不可少的操作，Hive同样支持Join的语法，Hive Join的底层还是通过MapReduce来实现的，Hive实现Join时，为了提高MapReduce的性能，提供了多种Join方案来实现，例如适合小表Join大表的Map Join，大表Join大表的Reduce Join，以及大表Join的优化方案Bucket Join等

Map Join

应用场景

适合于小表join大表或者小表Join小表

原理

将小的那份数据给每个MapTask的内存都放一份完整的数据，大的数据每个部分都可以与小数据的完整数据进行join
底层不需要经过shuffle，需要占用内存空间存放小的数据文件

尽量使用Map Join来实现Join过程，Hive中默认自动开启了Map Join：

hive.auto.convert.join=true
1

Hive中小表的大小限制

-- 2.0版本之前的控制属性
hive.mapjoin.smalltable.filesize=25M
-- 2.0版本开始由以下参数控制
hive.auto.convert.join.noconditionaltask.size=512000000
1
2
3
4

Reduce Join

应用场景

适合于大表Join大表

原理

将两张表的数据在shuffle阶段利用shuffle的分组来将数据按照关联字段进行合并
必须经过shuffle，利用Shuffle过程中的分组来实现关联

使用
Hive会自动判断是否满足Map Join，如果不满足Map Join，则自动执行Reduce Join

Bucket Join

应用场景

适合于大表Join大表

原理

将两张表按照相同的规则将数据划分
根据对应的规则的数据进行join
减少了比较次数，提高了性能

4.关联优化

当一个程序中如果有一些操作彼此之间有关联性，是可以在一个MapReduce中实现的，但是Hive会不智能的选择，Hive会使用两个MapReduce来完成这两个操作

例如：当我们执行 select …… from table group by id order by id desc。该SQL语句转换为MapReduce时，我们可以有两种方案来实现：

方案一
第一个MapReduce做group by，经过shuffle阶段对id做分组
第二个MapReduce对第一个MapReduce的结果做order by，经过shuffle阶段对id进行排序

方案二
因为都是对id处理，可以使用一个MapReduce的shuffle既可以做分组也可以排序

在这种场景下，Hive会默认选择用第一种方案来实现，这样会导致性能相对较差；
可以在Hive中开启关联优化，对有关联关系的操作进行解析时，可以尽量放在同一个MapReduce中实现

--配置
set hive.optimize.correlation=true;
1
2

5.优化器选择

Hive默认的优化器在解析一些聚合统计类的处理时，底层解析的方案有时候不是最佳的方案

例如当前有一张表共1000条数据，id构建了索引，id =100值有900条
需求：查询所有id = 100的数据，SQL语句为：select * from table where id = 100;

方案一
由于id这一列构建了索引，索引默认的优化器引擎RBO，会选择先从索引中查询id = 100的值所在的位置，再根据索引记录位置去读取对应的数据，但是这并不是最佳的执行方案

方案二
有id=100的值有900条，占了总数据的90%，这时候是没有必要检索索引以后再检索数据的，可以直接检索数据返回，这样的效率会更高，更节省资源，这种方式就是CBO优化器引擎会选择的方案

RBO
rule basic optimise：基于规则的优化器，根据设定好的规则来对程序进行优化

CBO
cost basic optimise：基于代价的优化器，根据不同场景所需要付出的代价来合适选择优化的方案

对数据的分布的信息数值出现的次数，条数，分布来综合判断用哪种处理的方案是最佳方案，这是由Analyze分析器来完成的

Hive中支持RBO与CBO这两种引擎，默认使用的是RBO优化器引擎
很明显CBO引擎更加智能，所以在使用Hive时，我们可以配置底层的优化器引擎为CBO引擎

set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
1
2
3

6.谓词下推

谓词：用来描述或判定客体性质、特征或者客体之间关系的词项。比如"3 大于 2"中"大于"是一个谓词

谓词下推Predicate Pushdown（PPD）基本思想：将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。简单点说就是在不影响最终结果的情况下，尽量将过滤条件提前执行

Hive中谓词下推后，过滤条件会下推到map端，提前执行过滤，减少map到reduce的传输数据，提升整体性能

开启参数

hive.optimize.ppd=true;
1

7.数据倾斜

分布式计算中最常见的，最容易遇到的问题就是数据倾斜

数据倾斜的现象是，当提交运行一个程序时，这个程序的大多数的Task都已经运行结束了，只有某一个Task一直在运行，迟迟不能结束，导致整体的进度卡在99%或者100%，这时候就可以判定程序出现了数据倾斜的问题

数据倾斜的原因：数据分配

当程序中出现group by或者count（distinct）等分组聚合的场景时，如果数据本身是倾斜的，根据MapReduce的Hash分区规则，肯定会出现数据倾斜的现象
根本原因是因为分区规则导致的，所以可以通过以下几种方案来解决group by导致的数据倾斜的问题

方案一：开启Map端聚合

hive.map.aggr=true;
1

通过减少shuffle数据量和Reducer阶段的执行时间，避免每个Task数据差异过大导致数据倾斜

方案二：实现随机分区

select * from table distribute by rand();
1

distribute by用于指定底层按照哪个字段作为Key实现分区、分组等
通过rank函数随机值实现随机分区，避免数据倾斜

方案三：数据倾斜时自动负载均衡

hive.groupby.skewindata=true;
1

开启该参数以后，当前程序会自动通过两个MapReduce来运行
第一个MapReduce自动进行随机分布到Reducer中，每个Reducer做部分聚合操作，输出结果
第二个MapReduce将上一步聚合的结果再按照业务（group by key）进行处理，保证相同的分布到一起，最终聚合得到结果

Join操作时，如果两张表比较大，无法实现Map Join，只能走Reduce Join，那么当关联字段中某一种值过多的时候依旧会导致数据倾斜的问题

面对Join产生的数据倾斜，核心的思想是尽量避免Reduce Join的产生，优先使用Map Join来实现

但往往很多的Join场景不满足Map Join的需求，那么可以以下几种方案来解决Join产生的数据倾斜问题：

方案一：提前过滤，将大数据变成小数据，实现Map Join

方案二：使用Bucket Join
如果使用方案一，过滤后的数据依旧是一张大表，那么最后的Join依旧是一个Reduce Join
这种场景下，可以将两张表的数据构建为桶表，实现Bucket Map Join，避免数据倾斜

方案三：使用Skew Join
Skew Join是Hive中一种专门为了避免数据倾斜而设计的特殊的Join过程
这种Join的原理是将Map Join和Reduce Join进行合并，如果某个值出现了数据倾斜，就会将产生数据倾斜的数据单独使用Map Join来实现
其他没有产生数据倾斜的数据由Reduce Join来实现，这样就避免了Reduce Join中产生数据倾斜的问题
最终将Map Join的结果和Reduce Join的结果进行Union合并

-- 开启运行过程中skewjoin
set hive.optimize.skewjoin=true;
-- 如果这个key的出现的次数超过这个范围
set hive.skewjoin.key=100000;
-- 在编译时判断是否会产生数据倾斜
set hive.optimize.skewjoin.compiletime=true;
-- 不合并，提升性能
set hive.optimize.union.remove=true;
-- 如果Hive的底层走的是MapReduce，必须开启这个属性，才能实现不合并
set mapreduce.input.fileinputformat.input.dir.recursive=true;
1
2
3
4
5
6
7
8
9
10