1.6 Hive

1.6 Hive

1.6.1 Hive的架构

1.6.2 HQL转换为MR流程

（1）解析器（SQLParser）：将SQL字符串转换成抽象语法树（AST）

（2）语义分析器（Semantic Analyzer）：将AST进一步抽象为QueryBlock（可以理解为一个子查询划分成一个QueryBlock）

（2）逻辑计划生成器（Logical Plan Gen）：由QueryBlock生成逻辑计划

（3）逻辑优化器（Logical Optimizer）：对逻辑计划进行优化

（4）物理计划生成器（Physical Plan Gen）：根据优化后的逻辑计划生成物理计划

（5）物理优化器（Physical Optimizer）：对物理计划进行优化

（6）执行器（Execution）：执行该计划，得到查询结果并返回给客户端

1.6.3 Hive和数据库比较

Hive 和数据库除了拥有类似的查询语言，再无类似之处。

1）数据存储位置

Hive 存储在 HDFS 。数据库将数据保存在块设备或者本地文件系统中。

2）数据更新

Hive中不建议对数据的改写。而数据库中的数据通常是需要经常进行修改的。

3）执行延迟

Hive 执行延迟较高。数据库的执行延迟较低。当然，这个是有条件的，即数据规模较小，当数据规模大到超过数据库的处理能力的时候，Hive的并行计算显然能体现出优势。

4）数据规模

Hive支持很大规模的数据计算；数据库可以支持的数据规模较小。

1.6.4 内部表和外部表

元数据、原始数据

1）删除数据时：

内部表：元数据、原始数据，全删除

外部表：元数据 只删除

2）在公司生产环境下，什么时候创建内部表，什么时候创建外部表？

在公司中绝大多数场景都是外部表。

自己使用的临时表，才会创建内部表；

1.6.5 系统函数

1）数值函数

（1）round：四舍五入；（2）ceil：向上取整；（3）floor：向下取整

2）字符串函数

（1）substring：截取字符串；（2）replace：替换；（3）regexp_replace：正则替换

（4）regexp：正则匹配；（5）repeat：重复字符串；（6）split：字符串切割

（7）nvl：替换null值；（8）concat：拼接字符串；

（9）concat_ws：以指定分隔符拼接字符串或者字符串数组；

（10）get_json_object：解析JSON字符串

3）日期函数

（1）unix_timestamp：返回当前或指定时间的时间戳

（2）from_unixtime：转化UNIX时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式

（3）current_date：当前日期

（4）current_timestamp：当前的日期加时间，并且精确的毫秒

（5）month：获取日期中的月；（6）day：获取日期中的日

（7）datediff：两个日期相差的天数（结束日期减去开始日期的天数）

（8）date_add：日期加天数；（9）date_sub：日期减天数

（10）date_format：将标准日期解析成指定格式字符串

4）流程控制函数

（1）case when：条件判断函数

（2）if：条件判断，类似于Java中三元运算符

5）集合函数

（1）array：声明array集合

（2）map：创建map集合

（3）named_struct：声明struct的属性和值

（4）size：集合中元素的个数

（5）map_keys：返回map中的key

（6）map_values：返回map中的value

（7）array_contains：判断array中是否包含某个元素

（8）sort_array：将array中的元素排序

6）聚合函数

（1）collect_list：收集并形成list集合，结果不去重

（2）collect_set：收集并形成set集合，结果去重

1.6.6 自定义UDF、UDTF函数

1）在项目中是否自定义过UDF、UDTF函数，以及用他们处理了什么问题，及自定义步骤？

（1）目前项目中逻辑不是特别复杂就没有用自定义UDF和UDTF

（2）自定义UDF：继承G..UDF，重写核心方法evaluate

（3）自定义UDTF：继承自GenericUDTF，重写3个方法：initialize（自定义输出的列名和类型），process（将结果返回forward（result）），close

2）企业中一般什么场景下使用UDF/UDTF？

（1）因为自定义函数，可以将自定函数内部任意计算过程打印输出，方便调试。

（2）引入第三方jar包时，也需要。

1.6.7 窗口函数

一般在场景题中出现手写：分组TopN、行转列、列转行。

按照功能，常用窗口可划分为如下几类：聚合函数、跨行取值函数、排名函数。

1）聚合函数

max：最大值。

min：最小值。

sum：求和。

avg：平均值。

count：计数。

2）跨行取值函数

（1）lead和lag

注：lag和lead函数不支持自定义窗口。

（2）first_value和last_value

3）排名函数

注：rank 、dense_rank、row_number不支持自定义窗口。

1.6.8 Hive优化

1.6.8.1 分组聚合

一个分组聚合的查询语句，默认是通过一个MapReduce Job完成的。Map端负责读取数据，并按照分组字段分区，通过Shuffle，将数据发往Reduce端，各组数据在Reduce端完成最终的聚合运算。

分组聚合的优化主要围绕着减少Shuffle数据量进行，具体做法是map-side聚合。所谓map-side聚合，就是在map端维护一个Hash Table，利用其完成部分的聚合，然后将部分聚合的结果，按照分组字段分区，发送至Reduce端，完成最终的聚合。

相关参数如下：

--启用map-side聚合，默认是true

set hive.map.aggr=true;

--用于检测源表数据是否适合进行map-side聚合。检测的方法是：先对若干条数据进行map-side聚合，若聚合后的条数和聚合前的条数比值小于该值，则认为该表适合进行map-side聚合；否则，认为该表数据不适合进行map-side聚合，后续数据便不再进行map-side聚合。

set hive.map.aggr.hash.min.reduction=0.5;

--用于检测源表是否适合map-side聚合的条数。

set hive.groupby.mapaggr.checkinterval=100000;

--map-side聚合所用的hash table，占用map task堆内存的最大比例，若超出该值，则会对hash table进行一次flush。

set hive.map.aggr.hash.force.flush.memory.threshold=0.9;

1.6.8.2 Map Join

Hive中默认最稳定的Join算法是Common Join。其通过一个MapReduce Job完成一个Join操作。Map端负责读取Join操作所需表的数据，并按照关联字段进行分区，通过Shuffle，将其发送到Reduce端，相同key的数据在Reduce端完成最终的Join操作。

优化Join的最为常用的手段就是Map Join，其可通过两个只有Map阶段的Job完成一个join操作。第一个Job会读取小表数据，将其制作为Hash Table，并上传至Hadoop分布式缓存（本质上是上传至HDFS）。第二个Job会先从分布式缓存中读取小表数据，并缓存在Map Task的内存中，然后扫描大表数据，这样在map端即可完成关联操作。

注：由于Map Join需要缓存整个小标的数据，故只适用于大表Join小表的场景。

相关参数如下：

--启动Map Join自动转换

set hive.auto.convert.join=true;

--开启无条件转Map Join

set hive.auto.convert.join.noconditionaltask=true;

--无条件转Map Join小表阈值，默认值10M，推荐设置为Map Task总内存的三分之一到二分之一

set hive.auto.convert.join.noconditionaltask.size=10000000;

1.6.8.3 SMB Map Join

上节提到，Map Join只适用于大表Join小表的场景。若想提高大表Join大表的计算效率，可使用Sort Merge Bucket Map Join。

需要注意的是SMB Map Join有如下要求：

（1）参与Join的表均为分桶表，且分桶字段为Join的关联字段。

（2）两表分桶数呈倍数关系。

（3）数据在分桶内是按关联字段有序的。

SMB Join的核心原理如下：只要保证了上述三点要求的前两点，就能保证参与Join的两张表的分桶之间具有明确的关联关系，因此就可以在两表的分桶间进行Join操作了。

若能保证第三点，也就是参与Join的数据是有序的，这样就能使用数据库中常用的Join算法之一——Sort Merge Join了，Merge Join原理如下：

在满足了上述三点要求之后，就能使用SMB Map Join了。

由于SMB Map Join无需构建Hash Table也无需缓存小表数据，故其对内存要求很低。适用于大表Join大表的场景。

1.6.8.4 Reduce并行度

Reduce端的并行度，也就是Reduce个数，可由用户自己指定，也可由Hive自行根据该MR Job输入的文件大小进行估算。

Reduce端的并行度的相关参数如下：

--指定Reduce端并行度，默认值为-1，表示用户未指定

set mapreduce.job.reduces;

--Reduce端并行度最大值

set hive.exec.reducers.max;

--单个Reduce Task计算的数据量，用于估算Reduce并行度

set hive.exec.reducers.bytes.per.reducer;

Reduce端并行度的确定逻辑如下：

若指定参数mapreduce.job.reduces的值为一个非负整数，则Reduce并行度为指定值。否则，Hive自行估算Reduce并行度，估算逻辑如下：

假设Job输入的文件大小为totalInputBytes

参数hive.exec.reducers.bytes.per.reducer的值为bytesPerReducer。

参数hive.exec.reducers.max的值为maxReducers。

则Reduce端的并行度为：

根据上述描述，可以看出，Hive自行估算Reduce并行度时，是以整个MR Job输入的文件大小作为依据的。因此，在某些情况下其估计的并行度很可能并不准确，此时就需要用户根据实际情况来指定Reduce并行度了。

需要说明的是：若使用Tez或者是Spark引擎，Hive可根据计算统计信息（Statistics）估算Reduce并行度，其估算的结果相对更加准确。

1.6.8.5 小文件合并

若Hive的Reduce并行度设置不合理，或者估算不合理，就可能导致计算结果出现大量的小文件。该问题可由小文件合并任务解决。其原理是根据计算任务输出文件的平均大小进行判断，若符合条件，则单独启动一个额外的任务进行合并。

相关参数为：

--开启合并map only任务输出的小文件

set hive.merge.mapfiles=true;

--开启合并map reduce任务输出的小文件

set hive.merge.mapredfiles=true;

--合并后的文件大小

set hive.merge.size.per.task=256000000;

--触发小文件合并任务的阈值，若某计算任务输出的文件平均大小低于该值，则触发合并

set hive.merge.smallfiles.avgsize=16000000;

1.6.8.6 谓词下推

谓词下推（predicate pushdown）是指，尽量将过滤操作前移，以减少后续计算步骤的数据量。开启谓词下推优化后，无需调整SQL语句，Hive就会自动将过滤操作尽可能的前移动。

相关参数为：

--是否启动谓词下推（predicate pushdown）优化

set hive.optimize.ppd = true;

1.6.8.7 并行执行

Hive会将一个SQL语句转化成一个或者多个Stage，每个Stage对应一个MR Job。默认情况下，Hive同时只会执行一个Stage。但是某SQL语句可能会包含多个Stage，但这多个Stage可能并非完全互相依赖，也就是说有些Stage是可以并行执行的。此处提到的并行执行就是指这些Stage的并行执行。相关参数如下：

--启用并行执行优化，默认是关闭的

set hive.exec.parallel=true;       

    

--同一个sql允许最大并行度，默认为8

set hive.exec.parallel.thread.number=8;

1.6.8.8 CBO优化

CBO是指Cost based Optimizer，即基于计算成本的优化。

在Hive中，计算成本模型考虑到了：数据的行数、CPU、本地IO、HDFS IO、网络IO等方面。Hive会计算同一SQL语句的不同执行计划的计算成本，并选出成本最低的执行计划。目前CBO在Hive的MR引擎下主要用于Join的优化，例如多表Join的Join顺序。

相关参数为：

--是否启用cbo优化 

set hive.cbo.enable=true;

1.6.8.9 列式存储

采用ORC列式存储加快查询速度。

            id   name   age

            1    zs     18

            2    lishi  19

        行：1    zs     18   2    lishi  19

        列：1 2  zs   lishi   18 19

        select name from user

1.6.8.10 压缩

压缩减少磁盘IO：因为Hive底层计算引擎默认是MR，可以在Map输出端采用Snappy压缩。

Map（Snappy ） Reduce

1.6.8.11 分区和分桶

（1）创建分区表  防止后续全表扫描

（2）创建分桶表  对未知的复杂的数据进行提前采样

1.6.8.12 更换引擎

MR/Tez/Spark区别：

MR引擎：多Job串联，基于磁盘，落盘的地方比较多。虽然慢，但一定能跑出结果。一般处理，周、月、年指标。

Spark引擎：虽然在Shuffle过程中也落盘，但是并不是所有算子都需要Shuffle，尤其是多算子过程，中间过程不落盘  DAG有向无环图。 兼顾了可靠性和效率。一般处理天指标。

Tez引擎的优点

（1）使用DAG描述任务，可以减少MR中不必要的中间节点，从而减少磁盘IO和网络IO。

（2）可更好的利用集群资源，例如Container重用、根据集群资源计算初始任务的并行度等。

（3）可在任务运行时，根据具体数据量，动态的调整后续任务的并行度。

1.6.9 Hive解决数据倾斜方法

数据倾斜问题，通常是指参与计算的数据分布不均，即某个key或者某些key的数据量远超其他key，导致在shuffle阶段，大量相同key的数据被发往同一个Reduce，进而导致该Reduce所需的时间远超其他Reduce，成为整个任务的瓶颈。以下为生产环境中数据倾斜的现象：

Hive中的数据倾斜常出现在分组聚合和join操作的场景中，下面分别介绍在上述两种场景下的优化思路。

1）分组聚合导致的数据倾斜

前文提到过，Hive中的分组聚合是由一个MapReduce Job完成的。Map端负责读取数据，并按照分组字段分区，通过Shuffle，将数据发往Reduce端，各组数据在Reduce端完成最终的聚合运算。若group by分组字段的值分布不均，就可能导致大量相同的key进入同一Reduce，从而导致数据倾斜。

由分组聚合导致的数据倾斜问题，有如下解决思路：

（1）判断倾斜的值是否为null

若倾斜的值为null，可考虑最终结果是否需要这部分数据，若不需要，只要提前将null过滤掉，就能解决问题。若需要保留这部分数据，考虑以下思路。

（2）Map-Side聚合

开启Map-Side聚合后，数据会现在Map端完成部分聚合工作。这样一来即便原始数据是倾斜的，经过Map端的初步聚合后，发往Reduce的数据也就不再倾斜了。最佳状态下，Map端聚合能完全屏蔽数据倾斜问题。

相关参数如下：

set hive.map.aggr=true;

set hive.map.aggr.hash.min.reduction=0.5;

set hive.groupby.mapaggr.checkinterval=100000;

set hive.map.aggr.hash.force.flush.memory.threshold=0.9;

（3）Skew-GroupBy优化

Skew-GroupBy是Hive提供的一个专门用来解决分组聚合导致的数据倾斜问题的方案。其原理是启动两个MR任务，第一个MR按照随机数分区，将数据分散发送到Reduce，并完成部分聚合，第二个MR按照分组字段分区，完成最终聚合。

相关参数如下：

--启用分组聚合数据倾斜优化

set hive.groupby.skewindata=true;

2）Join导致的数据倾斜

若Join操作使用的是Common Join算法，就会通过一个MapReduce Job完成计算。Map端负责读取Join操作所需表的数据，并按照关联字段进行分区，通过Shuffle，将其发送到Reduce端，相同key的数据在Reduce端完成最终的Join操作。

如果关联字段的值分布不均，就可能导致大量相同的key进入同一Reduce，从而导致数据倾斜问题。

由Join导致的数据倾斜问题，有如下解决思路：

（1）Map Join

使用Map Join算法，Join操作仅在Map端就能完成，没有Shuffle操作，没有Reduce阶段，自然不会产生Reduce端的数据倾斜。该方案适用于大表Join小表时发生数据倾斜的场景。

相关参数如下：

set hive.auto.convert.join=true;

set hive.auto.convert.join.noconditionaltask=true;

set hive.auto.convert.join.noconditionaltask.size=10000000;

（2）Skew Join

若参与Join的两表均为大表，Map Join就难以应对了。此时可考虑Skew Join，其核心原理是Skew Join的原理是，为倾斜的大key单独启动一个Map Join任务进行计算，其余key进行正常的Common Join。原理图如下：

相关参数如下：

--启用skew join优化

set hive.optimize.skewjoin=true;

--触发skew join的阈值，若某个key的行数超过该参数值，则触发

set hive.skewjoin.key=100000;

3）调整SQL语句

若参与Join的两表均为大表，其中一张表的数据是倾斜的，此时也可通过以下方式对SQL语句进行相应的调整。

假设原始SQL语句如下：A，B两表均为大表，且其中一张表的数据是倾斜的。

hive (default)>

select

    *

from A

join B

on A.id=B.id;

：

调整SQL语句如下：

hive (default)>

select

    *

from(

    select --打散操作

        concat(id,'_',cast(rand()*2 as int)) id,

        value

    from A

)ta

join(

    select --扩容操作

        concat(id,'_',1) id,

        value

    from B

    union all

    select

        concat(id,'_',2) id,

        value

    from B

)tb

on ta.id=tb.id;

1.6.10 Hive的数据中含有字段的分隔符怎么处理？

Hive 默认的字段分隔符为Ascii码的控制符\001（^A），建表的时候用fields terminated by '\001'。注意：如果采用\t或者\001等为分隔符，需要要求前端埋点和JavaEE后台传递过来的数据必须不能出现该分隔符，通过代码规范约束。

一旦传输过来的数据含有分隔符，需要在前一级数据中转义或者替换（ETL）。通常采用Sqoop和DataX在同步数据时预处理。

id  name  age

1  zs    18

2  li分隔符si   19

1.6.11 MySQL元数据备份

元数据备份（重点，如数据损坏，可能整个集群无法运行，至少要保证每日零点之后备份到其它服务器两个复本）。

（1）MySQL备份数据脚本（建议每天定时执行一次备份元数据）

#/bin/bash

#常量设置

MYSQL_HOST='hadoop102'

MYSQL_USER='root'

MYSQL_PASSWORD='000000'

# 备份目录，需提前创建

BACKUP_DIR='/root/mysql-backup'

# 备份天数，超过这个值，最旧的备份会被删除

FILE_ROLL_COUNT='7'

# 备份MySQL数据库

[ -d "${BACKUP_DIR}" ] || exit 1

mysqldump \

--all-databases \

--opt \

--single-transaction \

--source-data=2 \

--default-character-set=utf8 \

-h"${MYSQL_HOST}" \

-u"${MYSQL_USER}" \

-p"${MYSQL_PASSWORD}" | gzip > "${BACKUP_DIR}/$(date +%F).gz"

if [ "$(ls "${BACKUP_DIR}" | wc -l )" -gt "${FILE_ROLL_COUNT}" ]

then

  ls "${BACKUP_DIR}" | sort |sed -n 1p | xargs -I {} -n1 rm -rf "${BACKUP_DIR}"/{}

fi

（2）MySQL恢复数据脚本

#/bin/bash

#常量设置

MYSQL_HOST='hadoop102'

MYSQL_USER='root'

MYSQL_PASSWORD='000000'

BACKUP_DIR='/root/mysql-backup'

# 恢复指定日期，不指定就恢复最新数据

RESTORE_DATE=''

[ "${RESTORE_DATE}" ] && BACKUP_FILE="${RESTORE_DATE}.gz" || BACKUP_FILE="$(ls ${BACKUP_DIR} | sort -r | sed -n 1p)"

gunzip "${BACKUP_DIR}/${BACKUP_FILE}" --stdout | mysql \

-h"${MYSQL_HOST}" \

-u"${MYSQL_USER}" \

-p"${MYSQL_PASSWORD}"

1.6.12 如何创建二级分区表？

create table dept_partition2(

    deptno int,    -- 部门编号

    dname string, -- 部门名称

)

partitioned by (day string, hour string)

row format delimited fields terminated by '\t';

1.6.13 Union与Union all区别

（1）union会将联合的结果集去重

（2）union all不会对结果集去重