超全面试汇总——Hive 超详细!!!带答案!!!持续更新中~
Hive面试总结
-
-
什么是 Hive ？
-
Hive结构描述
-
Hive的优势
-
内部表、外部表、分区表、分桶表
-
hive中 排序的种类和适用场景
-
动态分区和静态分区的区别 + 使用场景
-
hive 语句执行顺序
-
Hive的几种存储方式
-
列式存储的好处
-
HQL转化为MapReduce的过程
-
Hive 和关系型数据库的区别
-
Hive和HBase的对比区别
-
Hive 小文件问题及解决
-
Hive调优及优化
-
- 列裁剪和分区裁剪
- 谓词下推
- 聚合类group by操作，发生数据倾斜
- Join 优化
- 设置合理的map reduce的task数量
- sort by代替order by
- group by代替distinct
- 优化SQL处理join数据倾斜
- 选择使用Tez引擎
- 本地执行 和 并发执行
- 严格模式 (hive.mapred.mode = strict)
-
Hive数据倾斜如何定位 + 怎么解决
-
Hive中MR(map reduce)、Tez和Spark执行引擎对比
-
为什么任务执行的时候只有一个reduce？
-
Hive有索引么
-
Hive为什么有分区
-
如何使用分区
-
分区注意事项
-
为什么要分桶？
-
分桶的意义
-
如何使用分桶
-
分区分桶表举例
-
Hive函数
-
hive中split、coalesce及collect_list函数的用法
-
使用过Hive解析JSON串吗
-
什么是 Hive ？
- Hive 是基于 Hadoop的一个数据仓库工具 ，可以将结构化的数据文件映射为一张数据库表 ，并提供类SQL查询功能（HQL） ，提供快速开发的能力。Hive本质是将SQL转换为 MapReduce的任务进行运算 ，减少开发人员的学习成本，功能扩展很方便。：
- hive存的是和hdfs的映射关 系，hive是逻辑上的数据仓库 ，实际操作的都是hdfs上的文件 ，HQL就是用sql语法来写的mr程序
- 数据仓库是大多数企业“试水”大数据的首选切入点 ，因为数据仓库主要编程语言还是 SQL，而在大数据平台上，不论是 Hive 还是 SparkSQL，都是通过高度标准化的 SQL 来进行开发，这对于很多从传统数据仓库向大数据转型的开发人员和团队来说，是一种较为平滑的过渡。
Hive结构描述
Hive构建在Hadoop的HDFS和MapReduce之 上，用于管理和查询结构化/非结构化数据的数据仓库 。
使用HQL作为查询接口，使用HDFS作为底层存储，使用MapReduce作为执行层
用户接口 ：包括 CLI,JDBC,ODBC和 WUI
* 其中最常用的是 CLI，CLI启动的时候，会同时启动一个 **Hive 副本** * **Client 是 Hive 的客户端** ，用户连接至 Hive Server。在启动 Client 模式的时候，需要指出 Hive Server 所在节点，并且在该节点启动 Hive Server。
* WUI 是通过**浏览器访问 Hive** 。Hive内部执行流程：解释器、编译器、优化器、执行器 * 解析器（解析SQL语句 ）、编译器（把SQL语句编译成MapReduce程序 ）、优化器（优化MapReduce程序 ）、执行器（将MapReduce程序的结果提交到HDFS ）
* 从词法分析、语法分析、编译、优化以及查询计划的生成 。生成的查询计划存储在 HDFS 中，并在随后有 MapReduce 调用执行
元数据存储 。通常是存储在关系数据库如 mysql, derby 中
* Hive 中的元数据包括**表的名字，表的列和分区及其属性** ，表的属性（**是否为外部表** 等），表的数据所在目录等。**Hive 元数据默认存储在 derby 数据库，不支持多客户端访问** ，所以将元数据存储在 MySQL 等数据库，支持多客户端访问。Hadoop：用 HDFS 进行存储，利用 MapReduce 进行计算 * Hive 的数据存储在 HDFS 中，大部分的查询由 MapReduce 完成少数HiveSQL语句不会转化为MapReduce作业，直接从DataNode上获取数据后按照顺序输出。 （包含 * 的查询，比如 select * from tbl 不会生成 MapRedcue 任务 ）
Hive的优势
- Hive拥有统一的元数据管理 ，所以和Spark、Impala等SQL引擎是通用的。通用是指，在拥有了统一的metastore之后，在Hive中创建一张表，在Spark/Impala中是能用的 ，只需要共用元数据，就可以切换SQL引擎，涉及到了Spark sql和Hive On Spark
- 可用SQL轻松访问数据，从而实现数据仓库任务，如提取/转换/加载（ETL） ，报告和数据分析。
- 使存储的数据结构化
- 支持MapReduce计算引擎 、Spark和Tez分布式计算引擎
- 数据的存储格式多样 Hive中不仅可以使用逗号和制表符分隔值（CSV/TSV）文本文件，还可以使用Sequence File、RC、ORC、Parquet
- 数据离线处理 日志分析
内部表、外部表、分区表、分桶表
-
内部表 ：create table
- 创建内部表时，没有特别指定，则默认创建的表都是管理表manage table （也称内部表），会将数据移动到数据仓库指向的路径 ；不共享数据 * hive.metastore.warehouse.dir（默认：/user/hive/warehouse），
- 删除表时：在删除表的时候，内部表的元数据和数据会被一起删除 ，
-
外部表 ：create external table
- 当一份数据需要被共享时 ，可以创建一个外部表指向这份数据。
- 若创建外部表，仅记录数据所在的路径 通常在：/user/username/hive/warehouse/文件夹
- 而外部表只删除元数据，不删除数据 。这样外部表相对来说更加安全些，数据组织也更加灵活，方便共享源数据。
-
分区表 * 分区表使用的是表外字段 ，需要指定字段类型 ，并通过关键字partitioned by(partition_name string)声明，但是分区 划分粒度较粗 。
- 将数据按区域划分开 ，查询时不用扫描无关的数据 ，加快查询速度 。
-
分桶表 * 分桶使用的是表内字段 ，已经知道字段类型 ，不需要再指定。通过关键字 clustered by(column_name) into … buckets 声明。分桶是更细粒度的划分、管理数据 ，可以对表进行先分区再分桶的划分策
- 分桶最大的优势就是：用于数据取样，可以起到优化加速的作用 。
- 对分桶字段求哈希值 ，用哈希值与分桶的数量取余 ，余几，这个数据就放在那个桶内
hive中 排序的种类和适用场景
-
order by 全局排序 * 会对输入做全局排序 ，因此只有一个reducer(多个reducer无法保证全局有序）,所以当输入的数据规模较大时，会导致计算的时间较长
- 与数据库中 order by的区别在于在 hive 的严格模式下(hive.mapred.mode = strict)下，必须指定 limit ，否则执行会报错！
-
sort by 每个MapReduce排序 * 不是全局排序，其在数据进入reducer前完成排序 ，单个有序。
- sort by 的数据只能保证在同一reduce中的数据 可以按指定字段排序
- 不受 hive.mapred.mode 是否为strict ,nostrict 的影响，使用sort by 你可以指定执行的reduce 个数 （set mapred.reduce.tasks= ）
-
distribute by 每个分区排序 ：
- 按照指定的字段对数据进行划分输出到不同的reduce中 。
- distribute by类似 MR 中 partition（自定义分区），进行分区 ，某个特定行应该到哪个 reducer ，通常是为了进行后续的聚集操作
-
distribute by + sort by ：
- 分桶，保证同一字段值只存在一个结果文件当中，结合 sort by 保证 每个 reduceTask 结果有序
- distribute by 和 sort by 的常见使用场景有：
Map输出的文件大小不均
Reduce输出文件不均
小文件过多
文件超大
-
cluster by ：
- 对同一字段分桶并排序 ，不能和 sort by 连用，除了具有 distribute by 的功能外还兼具 sort by 的功能 。
- 但是排序只能是 升序 排序，不能像distribute by 一样去指定排序的规则为 ASC 或者 DESC 。
动态分区和静态分区的区别 + 使用场景
-
静态分区 ：
- 表的分区数量和分区值 是固定的。静态分区需要手动指定，列是在编译时期通过用户传递来决定的。 * 需要提前知道所有分区。适用于分区定义得早且数量少的用例 ，不适用于生产。
-
动态分区 ：
- 是基于查询参数的位置去推断分区的名称 ，只有在 SQL 执行时才能确定 ，会根据数据自动的创建新的分区。
- 应用场景：有很多分区，无法提前预估新分区 ，动态分区是合适的，一般用于生产环境。
hive 语句执行顺序
from … where … select … group by … having … order by … limit …
注意事项 * 使用分区剪裁、列剪裁，分区一定要加
* 少用 COUNT DISTINCT，group by 代替 distinct
* 是否存在多对多的关联
* 连接表时使用相同的关键词，这样只会产生一个 job * 减少每个阶段的数据量，只选出需要的，在 join 表前就进行过滤 * 大表放后面 * 谓词下推：where 谓词逻辑都尽可能提前执行，减少下游处理的数据量 * sort by 代替 order by
mysql执行顺序
* from… where…group by… having… select … order by… limit …Hive的几种存储方式
Text File format : 默认格式，数据不做压缩，磁盘开销大，数据解析开销大 。
Sequence File format * SequenceFile 是 Hadoop API 提供的一种二进制文件支持 ，其具有使用方便、可分割、可压缩 的特点
* SequenceFile 支持三种压缩选择：NONE, RECORD, BLOCK。 Record 压缩率低，一般建议使用 BLOCK 压缩。
面向行：在一起存储的 同一行数据是连续存储
RCfile format : RCFILE 是一种行列存储相结合的存储方式 。首先，其将数据按行分块，保证同一个 record 在一个块上，避免读一个记录需要读取多个 block。其次，块数据列式存储，有利于数据压缩和快速的列存取 。RCFile 目前没有性能优势，只有存储上能省 10% 的空间。
Parquet :
* **列式数据存储** 。 查询比较快
* Parquet**支持嵌套的数据模型** ，每一个数据模型的schema包含多个字段，**每一个字段有三个属性：重复次数、数据类型和字段名** * 二进制方式存储的，是不可以直接读取和修改的AVRO : avro Schema 数据序列化。
ORC : 对RCFile做了一些优化，支持各种复杂的数据类型 性能比较好 * ORC 将行的集合存储在一个文件中，并且集合内的行数据将以列式存储 。采用列式格式，压缩非常容易，从而降低了大量的存储成本。
* 当查询时，会查询特定列而不是查询整行 ，因为记录是以列式存储的。
* ORC 会基于列创建索引，当查询的时候会很快 。
* ORC文件也是以二进制方式存储的，所以是不可以直接读取
列式存储的好处
- 查询的时候不需要扫描全部的数据，而只需要读取每次查询涉及的列，这样可以将I/O消耗降低N倍，另外可以保存每一列的统计信息(min、max、sum等)，实现部分的谓词下推 。
- 由于每一列的成员都是同构的，可以针对不同的数据类型使用更高效的数据压缩算法 ，进一步减小I/O。
- 由于每一列的成员的同构性，可以使用更加适合CPU pipeline的编码方式 ，减小CPU的缓存失效。
HQL转化为MapReduce的过程
-
Antlr定义SQL的语法规则 ，完成SQL词法，语法解析 ，将SQL转化为抽象语法树AST Tree * HiveLexerX，HiveParser 分别是Antlr对语法文件Hive.g编译后自动生成的词法解析和语法解析类
-
遍历AST Tree，抽象出查询的基本组成单元QueryBlock * QueryBlock是一条SQL最基本的组成单元 ，包括三个部分：输入源，计算过程，输出 。简单来讲一个QueryBlock就是一个子查询
-
遍历QueryBlock，翻译为执行操作树OperatorTree * Hive最终生成的MapReduce任务，Map阶段和Reduce阶段均由OperatorTree组成 。逻辑操作符，就是在Map阶段或者Reduce阶段完成单一特定的操作。
-
逻辑层优化器进行OperatorTree变换，减少mapreduce job，减少shuffle数据量 * 谓词下推、合并线性的OperatorTree中partition/sort key相同的reduce （from (select key,value from src group bu key, value）s select s.key group by s.key;
- Map端聚合
-
遍历OperatorTree，翻译为MapReduce任务
-
物理层优化器进行MapReduce任务的变换 ，生成最终的执行计划
Hive 和关系型数据库的区别
- 适用范围不同： Hive时效性、延时性比较高 ，主要进行离线的大数据分析 ；数据库主要用在在线系统
- 规模不同： Hive数据规模大，优势在 于处理大数据集
- 查询语言不同： HQL 和 SQL
- 存储位置不同： HDFS 和 本地
- 执行方式： Hive执行MapReduce ， Mysq执行Executor
- 数据格式：Hive在加载数据的过程中不需要格式的转换，不会对数据本身进行修改 ；数据库中，不同数据库有不同存储引擎，加载的时候较慢
Hive和HBase的对比区别
- Hive 数据仓库 ，Hive的本质其实就相当于将HDFS中已经存储的文件在Mysql中做了一个双射关系 ，以方便使用HQL 去管理查询。
- Hbase 数据库 ，面向列存储的非关系型数据库
- Hive 适用于离线的数据分析和清洗，延迟较高
- Hbase 适用于单表非关系型数据的存储，不适合做关联查询，延迟低适合在线业务
- Hive 存储的数据依旧在DataNode上，编写的HQL语句会转换成MapReduce代码执行
- HBase 数据持久存储放在DataNode上，以region的形式管理
Hive 小文件问题及解决
小文件如何产生的 *
动态分区插入数据，产生大量的小文件 ，从而导致map数量剧增；
倒入数据时产生 ，每执行一次 insert 时hive中至少产生一个文件 ，文件数量=MapTask数量*分区数，insert 导入时至少会有一个MapTask。像有的业务需要每10分钟就要把数据同步到 hive 中，这样产生的文件就会很多。
-- 通过load方式加载数据
load data local inpath '/export/score' overwrite into table A -- 导入文件夹
-- 通过查询方式加载数据
insert overwrite table A select s_id,c_name,s_score from B;
sqlreduce数量越多，小文件也越多（reduce的个数和输出文件是对应的）； *
数据源本身就包含大量的小文件 。
造成的影响
* Hive的角度 小文件会开很多map，**一个map开一个JVM去执行，所以这些任务的初始化，启动，执行会浪费大量的资源，严重影响性能** 。
* HDFS文件**元数据存储在NameNode** 的内存中，在 内存空间有限的情况下，文件过多会影响NameNode 的寿命，**同时影响计算引擎的任务数量** ，比如每个小的文件都会生成一个Map任务。如何解决
使用 hive 自带的 concatenate 命令，自动合并小文件 alter table A concatenate;
* concatenate 命令只支持 RCFILE 和 ORC 文件类型。
* 使用concatenate命令合并小文件时不能指定合并后的文件数量，一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)
set mapred.min.split.size.per.node
-- 设置map输入合并小文件的相关参数：
set mapred.min.split.size.per.node
-- 每个Map最小输入大小(这个值决定了合并后文件的数量)
set mapred.min.split.size=256000000;
-- 设置map端输出进行合并，默认为true
set hive.merge.mapfiles = true;
-- 设置reduce端输出进行合并，默认为false
set hive.merge.mapredfiles = true
-- hive的查询结果输出是否进行压缩
set hive.exec.compress.output=true;
-- MapReduce Job的结果输出是否使用压缩
set mapreduce.output.fileoutputformat.compress=true;
sql
使用Sequencefile作为表存储格式 ，不要用textfile，在一定程度上可以减少小文件；
减少reduce的数量（可以使用参数进行控制） ；set mapreduce.job.reduces=10;
少用动态分区，用时记得按distribute by分区；
Hive调优及优化
-- map端聚合
set hive.map.aggr=true
-- map端自动负载均衡
set hive.groupby.skewindata = true
-- 小文件合并
set mapred.min.split.size= 256000000 -- 256M
-- 设置reduce个数
set mapred.reduce.tasks=10
-- 开启严格模式
set hive.mapred.mode = strict
sql
列裁剪和分区裁剪
- 在SELECT中，只拿需要的列 ，如果有，尽量使用分区过滤，少用SELECT * Hive中与列裁剪优化相关的配置项是
hive.optimize.cp - 分区裁剪就是只读取需要的分区，与分区裁剪优化相关的则是
hive.optimize.pruner
谓词下推
SELECT * FROM stu as t
LEFT JOIN course as t1
ON t.id=t2.stu_id
WHERE t.age=18;
上面语句是否具有优化的空间？如何优化？
SELECT * FROM (SELECT * FROM stu WHERE age=18) as t
LEFT JOIN course AS t1 on t.id=t1.stu_id
sql解决方案： * 采用谓词下推的技术 ，提早进行过滤有可能减少必须在数据库分区之间传递的数据量
* 所谓谓词下推就是通过嵌套的方式，将底层查询语句尽量推到数据底层去过滤，这样在上层应用中就可以使用更少的数据量来查询，这种SQL技巧被称为谓词下推(Predicate pushdown)
聚合类group by操作，发生数据倾斜
map段部分聚合
* 开启**Map端聚合参数设置set hive.map.aggr=true** * 在Map端**进行聚合操作的条目数目set hive.grouby.mapaggr.checkinterval=100000**有数据倾斜的时候进行负载均衡（默认是false） *
set hive.groupby.skewindata = true *
阶段拆分-两阶段聚合 需要聚合的key前加一个随机数的前后缀，这样就均匀了，之后再按照原始的key聚合一次
生成的查询计划有两 个 MapReduce 任务 。在第一个 MapReduce 中，map 的输出结果集合会随机分布 到 reduce 中， 每个 reduce 做部分聚合操作，并输出结果 。相同的 GroupB Key 有可能分发到不同的 reduce 中，从而达到负载均衡的目的 ；第二个 MapReduce 任务再根据预处 理的数据结果按照 Group By Key 分布到 reduce 中 （这个过程可以保证相同的 Group By Key 分布到同一个 reduce 中），最后完成最终的聚合操作。
假设 key = 水果
select count(substr(a.tmp,1,2)) as key
from(
select concat(key,'_',cast(round(10*rand())+1 as string)) tmp
from table
group by tmp
)a
group by key
sqlJoin 优化
-
build table（小表）前置 * Hive在解析带join的SQL语句时，会默认将最后一个表作为probe table，将前面的表作为build table并试图将它们读进内存 。如果表顺序写反，probe table在前面，引发OOM的风险就高了。
- 在维度建模数据仓库中，事实表就是probe table，维度表就是build table 。假设现在要将日历记录事实表和记录项编码维度表来join 维度表在前，事实表在后
-
Reduce join 改为Map join * 在Reduce阶段完成join。容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理 * 适用于小表和大表 join ，将较小RDD中的数据直接通过collect算子拉取到Driver端的内存中来 ，然后对其创建一个Broadcast变量；接着对另外RDD执行map类算子，在算子函数内 ，从Broadcast变量中获取较小RDD 的全量数据，与当前RDD的每一条数据按照连接key进行比对 ，如果连接key相同的话，那么就将两个RDD的数据用你需要的方式连接起来。
- 设置自动选择MapJoin set hive.auto.convert.join = true;默认为true
设置合理的map reduce的task数量
- map阶段优化。使单个map任务处理合适的数据量； *
map的数量不是越多越好，如果一个任务有很多小文件（远远小于块大小128m），则每个小文件也会被当做一个块，用一个map任务来完成 ，而一个map任务启动和初始化的时间远远大于逻辑处理的时间 ，就会造成很大的资源浪费 。而且，同时可执行的map数是受限 的
mapred.min.split.size: 指的是数据的最小分割单元大小 ；min的默认值是1B
mapred.max.split.size: max的默认值是256MB
小文件问题 ：
* 如果**一个任务有很多小文件（远远小于块大小128M），则每个小文件也会当做一个块，用一个map任务来完成** 。
* 而一个**map任务启动和初始化的时间远远大于逻辑处理的时间** ，就会造成很大的资源浪费。而且，比如有一个127M的文件，正常会用一个map去完成，但这个文件只有一个或者两个小字段，**却有几千万的记录，如果map处理的逻辑比较复杂，用一个map任务去做，肯定也比较耗时** 。当input的文件任务逻辑复杂，map执行非常慢的时候 ，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。
- reduce阶段优化 *
启动和初始化reduce也会消耗时间和资源 ；
另外，有多少个reduce，就会有个多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入 ，则也会出现小文件过多的问题；
如果Reduce设置的过小，那么单个Reduce处理的数据将会加大，很可能会引起OOM异常 *
处理大数据量利用合适的Reduce数；使单个Reduce任务处理数据量大小要合适；
set mapred.reduce.tasks=10; 就是10个 如果是-1 就会估算 * hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1000^3=1G）
* hive.exec.reducers.max（每个任务最大的reduce数，默认为999）
* 计算reducer数的公式很简单N=min（参数2，总输入数据量/参数1）
调整hive.exec.reducers.bytes.per.reducer参数的值 每个reduce处理数据量； *
什么情况下只有一个reduce；
* **没有group by的汇总，** * 用了Order by
* 有笛卡尔积。sort by代替order by
- HiveSQL中的order by与其他SQL方言中的功能一样，就是将结果按某字段全局排序，这会导致所有map端数据都进入一个reducer中，在数据量大时可能会长时间计算不完 。
- 如果使用sort by，那么还是会视情况启动多个reducer进行排序，并且保证每个reducer内局部有序。为了控制map端数据分配到reducer的key，往往还要配合distribute by一同使用 。如果不加distribute by的话，map端数据就会随机分配到reducer。
- 举个例子，假如要以UID为key，以上传时间倒序、记录类型倒序输出记录数据：
select uid,upload_time,event_type,record_data
from calendar_record_log
where pt_date >= 20190201 and pt_date <= 20190224
distribute by uid
sort by upload_time desc,event_type desc;
sqlgroup by代替distinct
原因：distinct会将列中所有的数据保存到内存中 ，极有可能发生内存溢出
采用sum() group by的方式来替换count(distinct) 完成计算。
解决方案 ：可以考虑使用Group By 或者 ROW_NUMBER() OVER(PARTITION BY col) 方式代替COUNT(DISTINCT col)
select count(distinct a) from calendar_record_log
where pt_date >= 20190101;
-- 但是这样写会启动两个MR job（单纯distinct只会启动一个），
-- 所以要确保数据量大到启动job的overhead远小于计算耗时，才考虑这种方法。
select count(1) from (
select uid from calendar_record_log
where pt_date >= 20190101
group by uid
) t;
-- 用group by方式同时统计多个列？下面是解决方法：
select t.a,sum(t.b),count(t.c),count(t.d) from (
select a,b,null c,null d from some_table
union all
select a,0 b,c,null d from some_table group by a,c
union all
select a,0 b,null c,d from some_table group by a,d
) t;
sql
优化SQL处理join数据倾斜
处理掉字段中带有空值的数据 *
原因：一个表内有许多空值时会导致 MapReduce过程中,空成为一个key值,对应的会有大量的value值, 而一个key的value会一起到达reduce造成内存不足
1.在查询的时候，过滤掉所有为NULL的数据，比如：
create table res_tbl as
select n.* from
(select * from res where id is not null ) n
left join org_tbl o on n.id = o.id;
2.查询出空值并给其赋上随机数,避免了key值为空（数据倾斜中常用的一种技巧）
create table res_tbl as
select n.* from res n
full join org_tbl o
on case when n.id is null then concat('hive', rand()) else n.id end = o.id;
sql
单独处理倾斜key * 一般来讲倾斜的key都很少，我们可以将它们抽样出来，对应的行单独存入临时表中，然后打上一个较小的随机数前缀（比如0~9），最后再进行聚合。SQL语句与上面的相仿，不再赘述。
不同数据类型 ，这种情况不太常见，主要出现在相同业务含义的列发生过逻辑上的变化时。不转换类型，计算key的hash值时默认是以int型做的，这就导致所有“真正的”string型key都分配到一个reducer上。所以要注意类型转换
select a.uid,a.event_type,b.record_data
from calendar_record_log a
left outer join (
select uid,event_type from calendar_record_log_2
where pt_date = 20190228
) b on a.uid = b.uid and b.event_type = cast(a.event_type as string)
where a.pt_date = 20190228
sql选择使用Tez引擎
- Tez: 是基于Hadoop Yarn之上的DAG（有向无环图，Directed Acyclic Graph）计算框架 。它把Ｍap/Reduce过程拆分成若干个子过程，同时可以把多个Ｍap/Reduce任务组合成一个较大的DAG任务，减少了Ｍap/Reduce之间的文件存储。同时合理组合其子过程，也可以减少任务的时间
- 设置 hive.execution.engine = tez;通过上述设置，执行的每个HIVE查询都将利用Tez, 当然，也可以选择使用spark作为计算引擎
本地执行 和 并发执行
set hive.exec.mode.local.auto=true; //开启本地mr
set hive.exec.parallel=true //可以开启并发执行。
sql严格模式 (hive.mapred.mode = strict)
- 对于分区表，用户不允许扫描所有分区
- 使用了order by语句的查询，要求必须使用limit语句
- 限制笛卡尔积的查询
Hive数据倾斜如何定位 + 怎么解决
Hive 中数据倾斜的基本表现
* 一般都发生在 Sql 中 **group by 和 join on** 上，而且和数据逻辑绑定比较深。
* 任务进度长时间维持在99%（或100%），查看任务监控页面**，发现只有少量（1个或几个）reduce子任务未完成**。因为其处理的数据量和其他reduce差异过大如何产生
* key的分布不均匀或者说**某些key太集中** * 业务数据自身的特性，例如不同数据类型关联产生数据倾斜
* S**QL语句导致的数据倾斜**如何解决
* 开启map端combiner **set hive.map.aggr = true** * 开启数据倾斜时负载均衡 **set hive.groupby.skewindata = true** * **控制空值分布** 将为空的key转变为字符串加随机数或纯随机数，将因空值而造成倾斜的数据分配到多个Reducer
* SQL语句调整
* 选用join key 分布**最均匀的表作为驱动表** 。做好列裁剪和filter操作，以达到两表join的时候，数据量相对变小的效果。
* 大小表Join：使用**map join让小的维度表（1000条以下的记录条数）先进内存** 。在Map端完成Reduce
* 大表Join大表：把空值的Key变成一个字符串加上**一个随机数** ，把倾斜的数据分到不同的reduce上，由于null值关联不上，处理后并不影响最终的结果
* count distinct大量相同特殊值：count distinct 时，**将值为空的情况单独处理** ，如果还有其他计算，**需要进行group by** ，可以先将值为空的记录单独处理，再和其他计算结果进行union。Hive中MR(map reduce)、Tez和Spark执行引擎对比
-
MapReduce是一种编程模型 ，用于大规模数据集，分为映射 和 归约 ，大数据量下优势明显，读写HDFS次数多
-
Tez是Apache开源的支持DAG(有向图)作业 的计算框架，它直接源于MapReduce框架，核心思想是将Map和Reduce两个操作进一步拆分 ，把多个MR任务组合成一个较大的DAG任务，减少文件存储并可以优化子过程
-
Spark基于map reduce算法实现的分布式计算 ，Job中间输出和结果可以保存在内存中，不需要读写HDFS，以DAG方式处理数据 ，数据量比较大的时候比较吃内存
-
Spark和Tez的区别： * Spark与Tez都是以DAG 方式处理数据
- Spark更像是一个通用的计算引擎 ，可以同时作为批式和流式 的处理引擎，提供内存计算，实时流处理，机器学习等多种计算方式，适合迭代计算。tez作为一个框架工具 ，特定为hive 和pig提供批量计算
- Spark属于内存计算，支持多种模式，可以跑在standalone，yarn上 ；而Tez只能跑在yarn上 ；
- Tez能够及时的释放资源 ，重用container，节省调度时间，对内存的资源要求率不高； 而spark如果存在迭代计算时，container一直占用资源 ；
-
使用场景：
- 如果数据需要快速处理而且资源充足，则可以选择Spark；如果资源是瓶颈，则可以使用Tez；
为什么任务执行的时候只有一个reduce？
原因： * 使用了Order by （Order By是会进行全局排序） * 直接COUNT(1),没有加GROUP BY ，比如：有笛卡尔积操作 SELECT COUNT(1) FROM tbl WHERE pt=’201909’
解决方案 ：
* 避免使用全局排序，可以使用sort by进行局部排序
* 使用GROUP BY进行统计，不会进行全局排序，比如：SELECT pt,COUNT(1) FROM tbl WHERE pt=’201909’ group by pt;Hive有索引么
* **Hive支持索引，但不支持主键或者外键** 。Hive索引可以建立在表中的某些列上，以提升一些操作的效率，例如减少MapReduce任务中需要读取的数据块的数量。
* 适用场景：**适用于不更新的静态字段** 。以免总是重建索引数据。每次建立、更新数据后，都要重建索引以构建索引表
* hive在**指定列上建立索引，会产生一张索引表（Hive的一张物理表）** ，里面的字段包括，索引列的值、该值对应的HDFS文件路径、该值在文件中的偏移量
* 很少用索引Hive为什么有分区
- 随着系统时间增长，表的数据量越来越大，使用分区技术可以指定条件，缩小数据扫描的范围，避免hive全表扫描，提升查询效率
- 可以将用户的整个表的数据 划分到多个子目录，
- 根据业务，通常按照年月日、地区等分区
如何使用分区
- PARTITION BY(col_name data_type)
- hive的分区字段使用的是表外字段 。而mysql使用的是表内字段 。
- hive的分区名区分大小写
- hive的分区本质是在表目录下面创建目录，但是该分区字段是一个伪列，不真实存在于数据中
- 一张表可以有一个或者多个分区，分区下面也可以有一个或者多个分区
- 双分区partitioned by (date_time string,type string），在文件系统中的表现为date_time为一个文件夹，type为date_time的子文件夹。
- 动态分区列必须在 SELECT 语句中的最后一个列中指定 ，且顺序与它们在 PARTITION() 子句中出现的顺序相同。
- 动态分区需要开启 set hive.exec.dynamic.partition = true; hive.exec.dynamic.parition.mode=nonstrict;
-- 创建静态分区 数据加载到指定的分区
create table if not exists part1(
uid int,
uname string,
uage int
)PARTITION BY (country string)
row format delimiterd fileds terminated by ',';
(stored as ORC| SequenceFile) ORC、 SequenceFile都是存储方式
(loacation 地址)
-- 导入数据 需要指定分区 数据未知，根据分区值确定创建分区
load data local inpath '/usr/loacl/xxx'
into table part1 partition(country='China');
-- 开启动态分区 默认为false，不开启
set hive.exec.dynamic.partition=true;
hive.exec.dynamic.parition.mode=nonstrict;
-- 创建动态双分区
create table if not exists dt_part1(
uid int,
uname string,
uage int
)
PARTITIONED BY (year string,month string)
row format delimited fields terminated by ',';
-- 在文件系统中的表现为date_time为一个文件夹，type为date_time的子文件夹。
-- 追加写入数据
insert into dy_part1 partition(year,month)
select * from part_tmp;
-- 覆盖写入数据
insert overwrite dy_part1 partition(year,month)
select * from part_tmp;
-- 混合分区
create table if not exists dy_part2(
uid int,
uname string,
uage int
)
PARTITIONED BY (year string,month string)
row format delimited fields terminated by ',';
-- 插入数据
insert into dy_part2 partition(year='2018',month)
select uid,uname,uage,month from part_tmp；
-- 多个范围分区键
create table test_demo (value int)
partitioned by range (id1 INT, id2 INT, id3 INT)
(
-- id1在(--∞,5]之间，id2在(-∞,105]之间，id3在(-∞,205]之间
partition p5_105_205 VALUES LESS THAN (5, 105, 205),
-- id1在(--∞,5]之间，id2在(-∞,115]之间，id3在(-∞,+∞]之间
partition p5_115_max VALUES LESS THAN (5, 115, MAXVALUE)
)
-- 查看分区数据
select * from part1 where country = 'China';
-- 显示分区
show partitions part1;
-- 增加分区
alter table part1 add partition(country = 'india') partition(country = 'America');
-- 增加分区并设置数据
alter table part1 add partition(country = 'xxx')
location 'user/hive/warehouse/xxx'
-- 修改分区的存储路径 hdfs路径必须是全路径
alter table part1 partition(country='Vietnam')
set location 'hdfs://hadoop01:9000/user/hive/warehouse/brz.db/part1/country=Vietnam'
-- 删除分区
alter table part1 drop partition(country = 'india')
-- 手动向hdfs中创建分区目录，添加数据，创建好hive的外表之后，无法加载数据，
-- 元数据中没有相应的记录
msck repair table tablename
sql
分区注意事项
- hive的分区使用的表外字段，分区字段是一个伪列但是可以查询过滤。
- 分区使用的是表外字段，分桶使用的是表内字段
- 分区字段不建议使用中文
- 不太建议使用动态分区。因为动态分区将会使用mapreduce来查询数据 ，如果分区数量过多将导致namenode和yarn的资源瓶颈。所以建议动态分区前也尽可能之前预知分区数量。
- 分区属性的修改均可以使用手动元数据和hdfs的数据内容
- 在hive中的数据是存储在hdfs中的,我们知道hdfs中的数据是不允许修改只能追加的 ，那么在hive中执行数据修改的命令时，就只能先找到对应的文件，读取后执行修改操作，然后重新写一份文件。如果文件比较大，就需要大量的IO读写。在hive中采用了分桶的策略，只需要找到文件存放对应的桶，然后读取再修改写入即可。
为什么要分桶？
- 单个分区或者表中的数据量越来越大，当分区不能更细粒的划分数据时 ，所以会采用分桶技术将数据更细粒度的划分和管理。
分桶的意义
- 分桶是更细粒度的划分、管理数据 ，更多用来做数据抽样、JOIN操作
- 大表在JOIN的时候，效率低下。如果对两个表先分别按id分桶，那么相同id都会归入一个桶 。那么此时再进行JOIN的时候是按照桶来JOIN的，那么大大减少了JOIN的数量 。
- 对数据抽样的时候 ，也不需要扫描整个文件。只需要对每个分区按照相同规则抽取一部分数据即可。
- 原始数据中加入一些额外的结构，这些结构可以用于高效的查询 ，例如，基于ID的分桶可以使得用户的查询非常的块。
如何使用分桶
定义：
* **clustered by (uid) – 指定分桶的字段** * **sorted by (uid desc) – 指定数据的排序规则** ，表示预期的数据就是以这里设置的字段以及排序规则来进行存储
* **into x buckets** 放进几个桶里
* **分区使用的是表外字段，分桶使用的是表内字段** * **分桶数和reduce数对应 一个文件对应一个分桶** * hash 值为 20 的 HDFS 目录为：/ warehouse /app/dt =20100801/ctry=US/part-00020导入数据
导入数据有两种，一种是通过文件导入，但是并不会真正的分桶 ,load data只是把文件上传到 表所在的HDFS目录下。并没有做其他操作 ；一种是通过从其他表插入的方式导入数据，这种方式才能真正的分桶 insert … select； *
cluster by (uid) – 指定getPartition以哪个字段来进行hash散列，并且排序字段也是指定的字段，默认以正序进行排序 *
distribute by(uid) – 指定getPartition以哪个字段来进行hash散列 *
sort by(uid asc) – 指定排序字段，以及排序规则 ，更灵活的方式，这种数据获取方式可以分别指定getPartition的字段和sort的字段
方式1
* 打开enforce bucketing开关,**设置强制分桶属性** **set hive.enforce.bucketing=true** 2.x版本不需要
* **设置reduces数为-1： hive.enforce.bucketing为true时，reduce要设为-1；** * insert overwrite table buc1 select uid,uname,uage from buc_temp;
* 得到的分桶对应的文件，**数据是无序的，也就是 sorted by 或 sort by无效）** * 方式2
* **关闭强制分桶** set hive.enforce.bucketing = false
* **将reducer个数设置为目标表的桶数** ，并在 SELECT 语句中用 DISTRIBUTE BY <bucket_key> – 对查询结果按目标表的分桶键分进reducer中。
* **set mapred.reduce.tasks = num_buckets** * insert into table buc1 select uid,uname,uage from buc_temp **distribute by (uid) sort by (uage desc);** * cluster by (uid)与distribute by(uid) sort by (uid asc)结果是一样的
抽样语句 ：tablesample（bucket x out of y） *
y必须是table总共bucket数的倍数或者因子。 *
例如：table总共分了64份，当y=32时，抽取2(64/32)个bucket的数据，当y=128时，抽取1/2(64/128)个bucket的数据。x表示从哪个bucket开始抽取。 *
例如：table总共bucket数为32，tablesample(bucket 3 out of 16)表示总共抽取2(32/16)个bucket的数据，分别为第3个bucket和第19(3+16)个bucket的数据。 *
select * from table_name tablesample(n percent) 抽出n%的数据 全表扫描 *
如果在 TABLESAMPLE 子句中指定的列与 CLUSTERED BY 子句中的列相匹配，则 TABLESAMPLE 只扫描表中要求的哈希分区【就是具体的桶】
--创建一个分桶表 并且指定排序字段及排序规则
create table if not exists buc1(
uid int,
uname string,
uage int
)
distribute by (uid)
sorted by(uid desc) into 4 buckets
row format delimited fields terminated by ',';
-- cluster by (uid)指定getPartition以哪个字段来进行hash散列，并且排序字段也是指定的字段，默认以正序进行排序
-- distribute by(uid) – 指定getPartition以哪个字段来进行hash散列
-- 加载数据 方式1
-- 打开enforce bucketing开关,设置强制分桶属性
set hive.enforce.bucketing=true
set mapred.reduce.tasks = -1
insert overwrite table buc1
select uid,uname,uage from buc_temp
sort by (uid);
-- 加载数据 方式2
-- 将reducer个数设置为目标表的桶数，并在 SELECT 语句中用 DISTRIBUTE BY <bucket_key>
-- 对查询结果按目标表的分桶键分进reducer中。
set hive.enforce.bucketing = false
set mapred.reduce.tasks = num_buckets
insert into table buc1
select uid,uname,uage from buc_temp
distribute by (uid) sort by (uage desc);
-- 查看表结构
desc formatted tablename;
-- 分桶查询结果
select * from buc1 cluster by (uid);
'''
采样 TABLESAMPLE(BUCKET x OUT OF y)
x：表示从哪个 bucket 开始抽取数据 y：必须为该表总 bucket 数的倍数或因子
'''
-- 查询第几桶 取出 uid % 4 == 0的数据
select * from buc1 tablesample(bucket 1 out of 4 on uid);
-- 查询uid 为奇数
select * from buc1 tablesample(bucket 2 out of 2 on uid)
-- 随机查询三条数据
select * from part_tmp order by rand() limit 3;
select * from part_tmp tablesample(0.1 percent) ;
sql
分区分桶表举例
-- 按照性别进行分区（1男2女），在分区中按照uid的奇偶进行分桶：
-- 分区使用的是表外字段，分桶使用的是表内字段
1 gyy1 1
2 gyy2 2
3 gyy3 2
4 gyy4 1
5 gyy5 2
6 gyy6 1
7 gyy7 1
8 gyy8 2
9 gyy9 1
10 gyy10 1
11 gyy11 2
12 gyy12 1
-- 创建带有分区的分桶表
create table if not exists stus(
uid int,
uname string
)
partitioned by(sex int)
clustered by(uid) into 2 buckets
row format delimited filed terminated by ' ';
-- 创建临时表
create table if not exists stu_temp(
uid int,
uname string,
usex int
)
row format delimited fields terminated by ' ';
-- 临时表中添加数据
load data local inpath '/usr/local/hivedata/stu.dat' into table stu_temp
-- 分桶表中加数据
insert into table stus partition(sex)
select uid,uname,usex from stu_temp
cluster by (uid);
-- 查询性别为女性的、并且学号为奇数的学生：
select * from stus tablesample(bucket 2 out of 2 on uid)
where sex=2;
sql
Hive函数
关系函数： <= 、 >= 、 IS NULL 、IS NOT NULL、LIKE
日期函数： to_date 、 yea r 、month 、second 、weekofyear、 datediff时间比较
条件函数： IF CASE
字符串函数：length、 reverse、 substr 截取字符串 、lower、 trim去空格 、CONCAT 字符串拼接
统计函数：
Hive的SQL还可以通过用户定义的函数（UDF），用户定义的聚合（UDAF）和用户定义的表函数（UDTF）进行扩展。
UDF、UDAF、UDTF 的区别：
* UDF（User-Defined-Function）一进一出
* UDAF（User-Defined Aggregation Funcation）聚集函数，多进一出
* UDTF（User-Defined Table-Generating Functions）一进多出，如lateral view explore()hive中split、coalesce及collect_list函数的用法
split将字符串转化为数组，即：split(‘a,b,c,d’ , ‘,’) ==> [“a”,“b”,“c”,“d”]。
coalesce(T v1, T v2, …) 返回参数中的第一个非空值；如果所有值都为 NULL，那么返回NULL。
collect_list列出该字段所有的值，不去重 select collect_list(id) from table。
使用过Hive解析JSON串吗
- hive 处理json数据总体来说有两个方向的路走
- 将json以字符串的方式整个入Hive表，然后通过使用UDF函数解析已经导入到hive中的数据 ，比如使用LATERAL VIEW json_tuple的方法，获取所需要的列名。
- 在导入之前将json拆成各个字段，导入Hive表的数据是已经解析过得 。这将需要使用第三方的SerDe。