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不会对结果集去重
