带你了解主流计算引擎Hive-中篇

带你了解主流计算引擎Hive-中篇 主流计算引擎Hive 一、Hive DQL - 数据查询语言 1. 关系运算符

= ，！=，<，>，<=，>=，【Not】between and；
【not】Null，in(数值1，数值2) 枚举查询；
【not】like，Rlike（正则表达式）过滤；

2. 算数运算符

+，-，/，%取余；

3. 逻辑运算符

A AND B 、 A&&B 、A OR B 、 A||B 、NOT A 、!A

4. 复杂的运算符

数组A[n] ：返回数组A的第n个元素、第一个元素的索引为0；
键值对M[key]：返回对应于映射关系中关键字的值；
结构体S.x ；返回S的x字段；

5. Hive内置函数

（1）数学函数

• round(double a) - 返回double类型的整数值部分（遵循四舍五入）
• round(double a ,int d) - 返回指定精度d的double类型；
• floor(double a) - 返回等于或者小于 该double变量的最大的整数；
• ceil(double a) - 返回等于或者小于 该double变量的最小的整数；
• rand() - 返回一个0到1范围内的随机数
• rand(int seed) - 返回指定种子seed，则会等到一个稳定的随机数序列；
• pow(double a ,double b) - 返回a的p次幂；
• sqrp(double a) - 返回a的平方根
• abs(int a) - 返回a的绝对值
• …

（2）日期函数

（3）条件判断函数

• if(条件判断,条件为真返回的值1,条件为假返回的值 2)
• coalesce(T1,T2,T3,T4,…) -返回参数中的第一个非空值，如果所有值都为null，那么返回null；
• case when then
  - case a when b then c [when d then e] [eles f ] END - 如果a等于b，那么返回C；如果a等于d则返回e，否则返回f；
  - case when a then b [ when c then d][eles f] END - 如果a为true，那么返回b；如果c为true，返回d，否则返回f；

（4）字符串函数

• length (string A) - 返回字符串A的长度
• reverse (string A) - 返回字符串A 的反转结果
• concat (string A,string B…) - 返回字符串A和B的拼接结果，支持任意个输入字符；
• concat_ws (string SEP , string A ,string B…) - 返回字符串连接够的结果，SEP表示各个字符串间的分隔符；
• substr (string A ,int start) - 返回字符串A 从start位置到结尾的字符串
• substring (string A ,int start) - 同上
• substr (string A,int start,int len) - 返回字符串A 从start位置开始，长度为len的字符串
• substring (string A ,int start,int len) - 同上
• upper (string A ) / ucase (string A) - 返回字符串A的大写格式
• lower (string A ) / lcase (string A ) - 返回字符串A的小写格式
• trim (string A ) - 去掉字符串两边的空格
• ltrim (string A ) - 去掉字符串左边的空格
• rtrim (string A ) - 去掉字符串右边的空格
• regexp_replace(string A,string B,string C) - 将字符串A中的符合java正则表达式的B的部分替换为C；
• regexp_extract(string subject,string pattern,int index) - 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符；
• parse_url (,) - 返回URL
• get_json_object (string json_string ,string path) - 解析json的字符串json_string,返回path路径；
• space(int n) -返回长度为n的空格字符串
• repeat (string str,int n) -返回重复n次的str字符串
• split (string str,string pat) - 按照pat字符串分割str，返回分割后的字符串数组；

（5）统计函数

• count (*) - 行数
• sum () - 相加的结果
• avg () - 平均值
• min () - 最小值
• max() - 最大值

（6）复合类型构建的访问函数

• map - 根据输入的key和value构建map类型
• struct - 根据输入的参数构建结构体struct类型
• array - 根据输入的参数构建数据ARRAY类型

二、Hive视图

在 Hive 中，视图是逻辑数据结构，可以通过隐藏复杂数据 *** 作（Joins, 子查询, 过滤,数据扁平化）来于简化查询 *** 作；
与关系数据库不同的是，Hive 视图并不存储数据或者实例化。一旦创建 HIve 视图，它的 schema 也会立刻确定下来。对底层表后续的更改(如 增加新列)并不会影响视图的 schema。如果底层表被删除或者改变，之后对视图的查询将会failed。基于以上 Hive view 的特性，我们在 ETL 和数据仓库中对于经常变化的表应慎重使用视图。

CREATE VIEW employee_skills
AS
SELECT name, skills_score['DB'] AS DB,
skills_score['Perl'] AS Perl,
skills_score['Python'] AS Python,
skills_score['Sales'] as Sales,
skills_score['HR'] as HR
FROM employee;

创建视图的时候是不会触发 MapReduce 的 Job，因为只存在元数据的改变。但是，当对视图进行查询的时候依然会触发一个 MapReduce Job 进程：SHOW CREATE TABLE 或者 DESC FORMATTED TABLE 语句来显示通过 CREATE VIEW 语句创建的视图。

以下是对 Hive视图的DDL *** 作：

• 更改视图的属性

alter view employee_skills
set TBLPROPERTIES ('comment' = 'This is a view');

• 重新定义视图

ALTER VIEW employee_skills 
AS
SELECt * from employee;

三、Hive 数据抽样

当数据规模不断膨胀时，我们需要找到一个数据的子集来加快数据分析效率。因此我们就需要通过筛选和分析数据集为了进行模式 & 趋势识别。目前来说有三种方式来进行抽样：随机抽样，桶表抽样，和块抽样；

1. 随机抽样
关键词： rand ( ) 函数

使用 rand()函数进行随机抽样，limit 关键字限制抽样返回的数据，其中 rand函数前的 distribute 和 sort 关键字可以保证数据在 mapper 和 reducer 阶段是随机分布的；

select * from table_name 
where col = 105
distribute by rand() sort by rand()
limit num;

使用 order关键词

select * from table_name 
where col = 102
oprder by rand()
limit num;

经测试对比，千万级数据中进行随机抽样 order by 方式耗时更长，大约多 30秒左右;

2. 块抽样

关键词： tablesample () 函数

i. tablesample(n percent) 根据 hive 表数据的大小按比例抽取数据，并保
存到新的 hive 表中。如：抽取原 hive表中 10%的数据

select * from xxx tablesample(10 percent) ;
-- 数字与 percent 之间要有空格

ii. tablesample(nM) 指定抽样数据的大小，单位为 M

select * from xxx tablesample(20M) ;
-- 数字与 M 之间不要有空格

iii. tablesample(n rows) 指定抽样数据的行数，其中 n 代表每个 map 任务均取 n 行数据，map 数量可通过 hive 表的简单查询语句确认（关键词：number of mappers: x

select * from xxx tablesample(100 rows) ;
-- 数字与 rows 之间要有空

3. 桶表抽样
关键词：tablesample (bucket x out of y [on colname])。

其中 x 是要抽样的桶编号，桶编号从 1 开始，colname 表示抽样的列，y 表示桶的数量。hive 中分桶其实就是根据某一个字段 Hash 取模，放入指定数据的桶中，比如将表 table_1 按照 ID 分成 100 个桶，其算法是 hash(id) % 100，这样，hash(id) %100 = 0 的数据被放到第一个桶中，hash(id) % 100 = 1 的记录被放到第二个桶中。创建分桶表的关键语句为：CLUSTER BY 语句。

例如：将表随机分成 10 组，抽取其中的第一个桶的数据：

select * from table_01
tablesample(bucket 1 out`of 10 on rand());

四、Hive存储与压缩

1. Hive的存储格式
Hive 支持的存储数的格式主要有：

• TEXTFILE（行式存储）
• SEQUENCEFILE(行式存储)
• ORC（列式存储
• ARQUET（列式存储）

1.1 行式存储和列式存储

左边为逻辑表，右边第一个为行式存储，第二个为列式存储；

• 行存储的特点： 查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。

select * from table_01;

• 列存储的特点： 因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。
  - select 某些字段效率更高。

• 存储格式

  • TEXTFILE

    • 默认格式，数据不做压缩，磁盘开销大，数据解析开销大。
    • 可结合 Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)
    • 但使用这种方式，hive 不会对数据进行切分，从而无法对数据进行并行 *** 作

  • ORC 格

    • Orc (Optimized Row Columnar)是 hive 0.11 版里引入的新的存储格式
    • 可以看到每个 Orc 文件由 1 个或多个 stripe 组成，每个 stripe250MB 大小，这个 Stripe 实际相当于 RowGroup 概念，不过大小由 4MB->250MB，这样能提升顺序读的吞吐率。
    • 每个 Stripe 里有三部分组成，分别是 Index Data,Row Data,Stripe Footer：

  • PARQUET 格式

    • Parquet 是面向分析型业务的列式存储格式；
    • Parquet 文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此 Parquet 格式文件是自解析的。
      -通常情况下，在存储 Parquet 数据的时候会按照 Block 大小设置行组的大小，由于一般情况下每一个 Mapper 任务处理数据的最小单位是一个 Block，这样可以把每一个行组由一个 Mapper 任务处理，增大任务执行并行度 ；
    • 在 Parquet 中，有三种类型的页：数据页、字典页和索引页。数据页用于存储当前行组中该列的值，字典页存储该列值的编码字典，每一个列块中最多包含一个字典页，索引页用来存储当前行组下该列的索引，目前 Parquet 中还不支持索引页。

2.Hive 压缩格式
在实际工作当中，hive 当中处理的数据，一般都需要经过压缩，可
以使用压缩来节省我们的 MR 处理的网络带宽；


3. 主流存储文件性能对比

从存储文件的压缩比和查询速度两个角度对比：

• 压缩比比较
  • TextFile

--1）创建表，存储数据格式为 TEXTFILE

create table log_text (
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' STORED AS TEXTFILE ;

-- （2）向表中加载数据
load data local inpath '/export/servers/hivedatas/log.data' into table log_text ;

--（3）查看表中数据大小，大小为 18.1M
dfs -du -h /user/hive/warehouse/myhive.db/log_text;

--结果显示:
--18.1 M /user/hive/warehouse/log_text/log.data

-  ORC

--1）创建表，存储数据格式为 ORC
create table log_orc(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' STORED AS orc ;

--2）向表中加载数据
insert into table log_orc select * from log_text ;
--3）查看表中数据大小
dfs -du -h /user/hive/warehouse/myhive.db/log_orc;

--结果显示:
--2.8 M /user/hive/warehouse/log_orc/123456_0

-  Parguet

--1）创建表，存储数据格式为 parquet
create table log_parquet(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' STORED AS PARQUET ;

--2）向表中加载数据
insert into table log_parquet select * from log_text ;

--3）查看表中数据大小
dfs -du -h /user/hive/warehouse/myhive.db/log_parquet;

--结果显示:
--13.1 M /user/hive/warehouse/log_parquet/123456_

数据压缩比结论:
ORC > Parquet > textFil

存储文件的查询效率比较:
ORC > TextFile > Parque

4. Hive的执行计划
Hive SQL 的执行计划描述 SQL 实际执行的整体轮廓，通过执行计划能了解 SQL
程序在转换成相应计算引擎的执行逻辑，掌握了执行逻辑也就能更好地把握程序
出现的瓶颈点，从而能够实现更有针对性的优化。此外还能帮助开发者识别看似
等价的 SQL 其实是不等价的，看似不等价的 SQL 其实是等价的 SQL。可以说执行
计划是打开 SQL 优化大门的一把钥匙。

要想学 SQL 执行计划，就需要学习查看执行计划的命令：explain，在查询语句
的 SQL 前面加上关键字 explain 是查看执行计划的基本方法。

学会 explain，能够给我们工作中使用 hive 带来极大的便利！

查看 SQL 的执行计划
Hive 提供的执行计划目前可以查看的信息有以下几种：
 explain：查看执行计划的基本信息；
 explain dependency：dependency 在 explain 语句中使用会产生有关计划
中输入的额外信息。它显示了输入的各种属性；
 explain authorization：查看 SQL *** 作相关权限的信息；
 explain vectorization：查看 SQL 的向量化描述信息，显示为什么未对
Map 和 Reduce 进行矢量化。从 Hive 2.3.0 开始支持；
 explain analyze：用实际的行数注释计划。从 Hive 2.2.0 开始支持；
 explain cbo：输出由 Calcite 优化器生成的计划。CBO 从 Hive 4.0.0 版
本开始支持；
 explain locks：这对于了解系统将获得哪些锁以运行指定的查询很有用。
LOCKS 从 Hive 3.2.0 开始支持；
 explain ast：输出查询的抽象语法树。AST 在 Hive 2.1.0 版本删除了，
存在 bug，转储 AST 可能会导致 OOM 错误，将在 4.0.0 版本修复；
 explain extended：加上 extended 可以输出有关计划的额外信息。这通
常是物理信息，例如文件名，这些额外信息对我们用处不大；

1. explain 的用法
   Hive 提供了 explain 命令来展示一个查询的执行计划，这个执行计划对于我们了
   解底层原理，Hive 调优，排查数据倾斜等很有帮助。
   使用语法如下：

explain query;
在 hive cli 中输入以下命令(hive 2.3.7)：
explain select sum(id) from test1;
得到结果：
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE PLANS:
Stage: Stage-1
Map Reduce
Map Operator Tree:
TableScan
alias: test1
Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stat
s: NONE
Select Operator
expressions: id (type: int)
outputColumnNames: id
Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column st
ats: NONE
Group By Operator
aggregations: sum(id)
mode: hash
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column s
tats: NONE
Reduce Output Operator
sort order:
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column
stats: NONE
value expressions: _col0 (type: bigint)
Reduce Operator Tree:
Group By Operator
aggregations: sum(VALUE._col0)
mode: mergepartial
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats:
NONE
File Output Operator
compressed: false
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats:
NONE

table:
input format: org.apache.hadoop.mapred.SequenceFileInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputF
ormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
ListSink

看完以上内容有什么感受，是不是感觉都看不懂，不要着急，下面将会详细讲解

每个参数，相信你学完下面的内容之后再看 explain 的查询结果将游刃有余。

一个 HIVE 查询被转换为一个由一个或多个 stage 组成的序列（有向无环图 DAG。

这些 stage 可以是 MapReduce stage，也可以是负责元数据存储的 stage，也可以
是负责文件系统的 *** 作（比如移动和重命名）的 stage。 我们将上述结果拆分看，先从最外层开始，包含两个大的部分：

1. stage dependencies： 各个 stage 之间的依赖性

2. stage plan： 各个 stage 的执行计划
   先看第一部分 stage dependencies ，包含两个 stage，Stage-1 是根 stage，
   说明这是开始的 stage，Stage-0 依赖 Stage-1，Stage-1 执行完成后执行
   Stage-0。
   再看第二部分 stage plan，里面有一个 Map Reduce，一个 MR 的执行计划分为
   两个部分：

3. Map Operator Tree： MAP 端的执行计划树

4. Reduce Operator Tree： Reduce 端的执行计划树
   这两个执行计划树里面包含这条 sql 语句的 operator：

5. TableScan：表扫描 *** 作，map 端第一个 *** 作肯定是加载表，所以就是表扫
   描 *** 作，常见的属性：
    alias： 表名称
    Statistics： 表统计信息，包含表中数据条数，数据大小等

6. Select Operator： 选取 *** 作，常见的属性 ：
    expressions：需要的字段名称及字段类型
    outputColumnNames：输出的列名称
    Statistics：表统计信息，包含表中数据条数，数据大小等

7. Group By Operator：分组聚合 *** 作，常见的属性：
    aggregations：显示聚合函数信息
    mode：聚合模式，值有 hash：随机聚合，就是 hash partition；
   partial：局部聚合；final：最终聚合
    keys：分组的字段，如果没有分组，则没有此字段
    outputColumnNames：聚合之后输出列名
    Statistics： 表统计信息，包含分组聚合之后的数据条数，数据
   大小等

8. Reduce Output Operator：输出到 reduce *** 作，常见属性：
    sort order：值为空 不排序；值为 + 正序排序，值为 - 倒序排
   序；值为 ± 排序的列为两列，第一列为正序，第二列为倒序

9. Filter Operator：过滤 *** 作，常见的属性：
    predicate：过滤条件，如 sql 语句中的 where id>=1，则此处显
   示(id >= 1)

10. Map Join Operator：join *** 作，常见的属性：
     condition map：join 方式 ，如 Inner Join 0 to 1 Left Outer Join0
    to 2
     keys: join 的条件字段
     outputColumnNames： join 完成之后输出的字段
     Statistics： join 完成之后生成的数据条数，大小等

11. File Output Operator：文件输出 *** 作，常见的属性
     compressed：是否压缩
     table：表的信息，包含输入输出文件格式化方式，序列化方式等

12. Fetch Operator 客户端获取数据 *** 作，常见的属性：
     limit，值为 -1 表示不限制条数，其他值为限制的条数

13. explain 的使用场景
    本节介绍 explain 能够为我们在生产实践中带来哪些便利及解决我们哪些迷惑