Hive 简介

Hive：由 Facebook 开源用于解决海量结构化日志的数据统计。

Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类 SQL 查询功能。

1)Hive 处理的数据存储在 HDFS

2)Hive 分析数据底层的实现是 MapReduce

3)执行程序运行在 Yarn 上

Hive 优缺点

• 优点
  • 操作接口采用类 SQL 语法，提供快速开发的能力(简单、容易上手)。
  • 避免了去写 MapReduce，减少开发人员的学习成本。
  • Hive 的执行延迟比较高，因此 Hive 常用于数据分析，对实时性要求不高的场合。
  • Hive 优势在于处理大数据，对于处理小数据没有优势，因为 Hive 的执行延迟比较高。
  • Hive 支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。
• 缺点
  • 1.Hive 的 HQL 表达能力有限
    • 迭代式算法无法表达
    • 数据挖掘方面不擅长，由于 MapReduce 数据处理流程的限制，效率更高的算法却无法实现。
  • 2.Hive 的效率比较低
    • Hive 自动生成的 MapReduce 作业，通常情况下不够智能化
    • Hive 调优比较困难，粒度较粗

Hive 架构原理

1．用户接口：Client

CLI(command-line interface)、JDBC/ODBC(jdbc 访问 hive)、WEBUI(浏览器访问 hive)

2．元数据：Metastore

元数据包括：表名、表所属的数据库(默认是 default)、表的拥有者、列/分区字段、表的类型(是否是外部表)、表的数据所在目录等；

默认存储在自带的 derby 数据库中，推荐使用 MySQL 存储 Metastore

3．Hadoop

使用 HDFS 进行存储，使用 MapReduce 进行计算。

4．驱动器：Driver

(1)解析器(SQL Parser)：将 SQL 字符串转换成抽象语法树 AST，这一步一般都用第三方工具库完成，比如 antlr；对 AST 进行语法分析，比如表是否存在、字段是否存在、SQL 语义是否有误。

(2)编译器(Physical Plan)：将 AST 编译生成逻辑执行计划。

(3)优化器(Query Optimizer)：对逻辑执行计划进行优化。

(4)执行器(Execution)：把逻辑执行计划转换成可以运行的物理计划。对于 Hive 来说，就是 MR/Spark。

Hive 通过给用户提供的一系列交互接口，接收到用户的指令(SQL)，使用自己的 Driver，结合元数据(MetaStore)，将这些指令翻译成 MapReduce，提交到 Hadoop 中执行，最后，将执行返回的结果输出到用户交互接口。

Hive 性能调优

NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。

它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。

nvl 可能存在性能问题，建议使用 if(表达式,a,b)

TIPs: 打开 Hive，输入 Explain ＋ 语句，就能够看到 SQL 语句解析成 MapReduce 的过程。

Join 和 group by 都会存在分发的情况，所以需要对应处理数据倾斜。

对 Hive 来说，数据量再大，都不怕。数据倾斜，是大难题

常用命令

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

-- 1. 查看系统自带的函数
hive> show functions;
-- 2. 显示自带的函数的用法
hive> desc function upper;


-- 设置reduce个数
hive (default)> set mapreduce.job.reduces=3;
-- 查看设置reduce个数
hive (default)> set mapreduce.job.reduces;

-- 限制数据返回行数
hive (default)> select * from emp limit 5;

-- hive查看建表语句
show create table tablename;

-- 查看hive表结构：
describe  tablename;
desc tablename;

-- 删除数据
delete from db_dws_test.dws_ei_basic_tsc_tax_illegal_ds where p_date >='20200923';

-- 删除分区
use db_dwd_test;
alter table db_dws_test.dws_ei_basic_tsc_tax_illegal_ds drop partition (p_date >= '20200923');

-- case when 语句
select
    dept_id,
    case
        when sex = '1' then '男'
        when sex = '0' then '女'
        else '未知'
    end as sex
from emp_sex ;

-- 格式化字符串 #,###,###.##
format_number(number x, int d)

类型转换

Hive-join

Join 的几种方式

• 说明: Hive Join 只能支持等值的 join,不支持非等值 join
• inner join 内连接
• left join 左连接
• rigth join 右连接
• full join 全连接

Join 语句注意事项

1
2
3
4

-- a的所有数据
select a.col,b.col from table a left join b on a.key = b.key;
-- a - (a和b重叠的数据)
select a.col,b.col from table a left join b on a.key = b.key where b.key is null;

Hive Join 的执行说明

• Hive 总是从左到右顺序执行的，所以建议小表在左侧。

• 大数据情况下，Hive 会对每个 join 连接对象启动一个 MapReduce

• 当对 3 个或者更多表进行 join 连接时，如果每个 on 子句都使用相同的连接键的话，那么只会产生一个 MapReduce job。

• Hive Join 语句

Hive-正则表达式

正则表达式函数

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

-- like
-- 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B的正则语法，则为TRUE；否则为FALSE
hive> select like('football', '__otba%'); -- _表示任意单个字符，%表示任意字符
true

-- rlike
-- 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合JAVA正则表达式B的正则语法，则为TRUE；否则为FALSE
hive> select 'football' rlike '^footba';
OK
true

-- REGEXP
-- 功能和 rlike 类似
hive> select regexp('football', '^foot');
OK
true

-- regexp_replace
-- 将字符串 A 中的符合 java 正则表达式 B 的部分替换为 C
hive> select regexp_replace('h234ney', '\\d+', 'o');
OK
honey

-- REGEXP_EXTRACT
-- 将字符串 A 按照 pattern 正则表达式的规则拆分，返回 index 指定的字符，index 从 1 开始计
hive> select regexp_extract('honeymoon', 'hon(.*?)(moon)', 2);
OK
moon

Hive 常用正则表达式语句

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

-- 判断是否纯数字 Hive模式下可用
SELECT  '123456' rlike '^\\d+$'; --true，前面需要是两个\\，后面的$必须要带

-- 判断是否纯数字
select not regexp_like('123456', '[^0-9]+') ;




-- 邮箱提取
select regexp_extract('我的邮箱是1234@qq.com','([a-z0-9\_\.\-]+)@([\da-z\.\-]+)\.([a-z\.]{2,6})',0);

-- 经纬度判断
SELECT regexp('60.054845,30.324694','^([1-9]\\d*\\.\\d*|0\\.\\d*[1-9]\\d*){1}\\,([1-9]\\d*\\.\\d*|0\\.\\d*[1-9]\\d*){1}$')

-- 小数or整数数据提取
select regexp_extract('4个2.0GHz','[0-9]+([.]{1}[0-9]+){0,1}',0);

-- URL去参数
select regexp_extract('http://tool.chinaz.com/regex','(.*)/$',1);
或
select CONCAT(parse_url(wap_url, 'PROTOCOL'),'://',parse_url(wap_url, 'HOST'),parse_url(wap_url, 'PATH'))

-- 提取版本号
select regexp_extract('android 9.1.0','[0-9]+(.[0-9]+)*')

正则表达式语法

参考链接：Python-正则表达说明

TIPS: Python2 中正则表达式对于中文的处理，必须要转换为unicode,否则结果会和预期不一致。

Hive-窗口函数

窗口函数样例

1
2

分析函数（max()/sum()/row_number()）+ 窗口子句（over函数）
例：row_number() over(partition by uid order by create_time asc)

窗口函数应用场景

• Top N
• 分区排序 —— row_number()
• 动态 group by
• 累计计算
• 层次查询

常用的窗口函数

• RANK() ：在分组中排名，相同排名时会留下空位；
• DENSE_RANK() ：在分组中排名，相同排名时不会留下空位；
• FIRST_VALUE() ：分组内排序取第一个值；
• LAST_VALUE() ：分组内排序取最后一个值；
• NTILE(n) ：将分组数据按顺序切分成 n 份，返回当前所在切片；
• ROW_NUMGBER() ：在分组中从 1 开始按序记录序列；
• CUME_DIST() ：小于等于当前值的行数 / 分组总行数（百分比）；
• PERCENT_RANK() ：（分组内的 RANK 值-1） / （分组内总数-1）；
• LAG(col, n, DEFAULT) ：在统计窗口内从下往上取第 n 行的值；
• LEAD(col, n, DEFAULT)：在统计窗口内从上往下取第 n 行的值；

Rank DENSE_RANK ROW_NUMBER 的区别

一般常用 row_number()

• RANK()：排序相同时会重复，总数不会变
• DENSE_RANK()：排序相同时会重复，总数会减少
• ROW_NUMBER()：会根据顺序计算

数据样例

1
2
3
4
5
6
7
8
9

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

name    subject score   rp      drp     rmp
宋宋    英语    84      1       1       1
大海    英语    84      1       1       2
婷婷    英语    78      3       2       3
孙悟空  英语    68      4       3       4

其他函数

Distribute By

Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个 reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by 类似 MR 中 partition(自定义分区)，进行分区，结合 sort by 使用。

对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by 的效果。

1．distribute by 的分区规则是根据分区字段的 hash 码与 reduce 的个数进行模除后，余数相同的分到一个区。(同一个分区的不一定分区值都相同)

2．Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。

Cluster By

当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。

cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。
但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。

1)以下两种写法等价

1
2

hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by deptno;

Having

1．having 与 where 不同点

(1)where 后面不能写分组函数，而 having 后面可以使用分组函数。

(2)having 只用于 group by 分组统计语句。

1
2
3
4
5

-- 求每个部门的平均工资
hive (default)> select deptno, avg(sal) from emp group by deptno;

-- 求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;

分桶抽样

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive 可以通过对表进行抽样来满足这个需求。

查询表 stu_buck 中的数据。

1
2
3
4
5
6

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

-- tablesample是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y)
-- y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。
-- x 必须小于 y的值
-- table总bucket数为4，tablesample(bucket 1 out of 2)，表示总共抽取(4/2=)2个bucket的数据，抽取第1(x)个和第3(x+y)个bucket的数据。

窗口函数

https://www.cnblogs.com/Tunan-Ki/p/11804510.html

OVER()：

指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

CURRENT ROW：

当前行

n PRECEDING：

往前 n 行数据

n FOLLOWING：

往后 n 行数据

UNBOUNDED：

起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING 表示到后面的终点

LAG(col,n,DEFAULT) ：

往前第 n 行数据

第一个参数为列名，第二个参数为往上第 n 行(可选，默认为 1)，第三个参数为默认值(当往上第 n 行为 NULL 时候，取默认值，如不指定，则为 NULL)

LEAD(col,n,DEFAULT)：

往后第 n 行数据

第一个参数为列名，第二个参数为往下第 n 行(可选，默认为 1)，第三个参数为默认值(当往下第 n 行为 NULL 时候，取默认值，如不指定，则为 NULL)

NTILE(n)：

把有序分区中的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

Hive 列转行(集合/数组转多行)

https://www.cnblogs.com/Tunan-Ki/p/11802667.html

1
2
3
4
5
6
7
8
9
10

EXPLODE(col)：

　　explode(col)接受一个数组(或一个map)作为输入，并将数组元素(map)作为单独的行输出。 UDTF可以在SELECT表达式列表中使用，也可以作为LATERAL VIEW的一部分使用。

LATERAL VIEW：

用在FROM语句后：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

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

扩展嵌套的 JSON 数据

1
2
3
4
5
6
7
8
9
10
11
12
13

# prestodb-sql
SELECT json_extract(t.a, '$.a') AS a, 
       json_extract(t.a, '$.b') AS b
FROM (
    SELECT cast(json_extract('{"x":[{"a":1,"b":2},{"a":3,"b":4}]}', '$.x') 
           AS array<JSON>) AS package_array
)
CROSS JOIN UNNEST(package_array) AS t(a);

# spark-sql
get_json_object

Hive 行转列(多行转一个集合/数组)

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

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

-- 去重，返回列表
COLLECT_SET(col)：
　　函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。

空字段赋值

sort by

每个 MapReduce 内部排序(Sort By)

Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。

Sort by 为每个 reducer 产生一个排序文件。每个 Reducer 内部进行排序，对全局结果集来说不是排序。

分桶表存储数据

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

分区针对的是数据的存储路径；分桶针对的是数据文件

group by 聚合函数

GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。

order by

全局字段排序，只会有一个 MapReducer, 会存在一定性能问题，大数据情况下建议使用 sort by 代替

1

select a,save_time from table_name where p_date = '20300101' order by save_time desc ;

参考资源

Hive-SQL 技巧总结

• Hive 博客
• hive sql 技巧总结
• Hive 博客
• Hive 执行原理和 SQL 优化
• Hive/HiveSQL 常用优化方法全面总结

Hive 性能优化

• HIVE-SQL 优化

• Hive 性能优化

• Hive 总结篇及 Hive 的优化

• Hive 之——Hive SQL 优化

• Hive 博客

• Hive str2date

• Hive 常用函数大全

• Hive 官方函数

• Spark 內建函数