hive优化器,hivesql优化的几种方法

Hive优化总结

hive建筑设计1级.使用分区表优化分区表是对一个或多个维中的数据进行分类和存储，其中一个分区表对应于一个目录。 如果筛选条件有点

节字段(即Hive )只需要遍历相应分区目录下的文件，而不需要遍历全局数据来增加处理的数据量

大大减少，提高查询效率。

这意味着，对于大多数Hive表查询，如果根据字段进行筛选，则最好创建以下查询

分区表。 这就是分区字段。

使用水桶表进行优化与分区的概念非常相似。 我们把数据分成多个不同的类别。 区别在于规则不同。

1、分区：按字段值进行。 一个分区只是包含此一个值的所有记录

不是当前分区的数据必须不在当前分区中

当前分区仅包含当前分区值的数据

2、时段：默认规则：散列

一个水桶里有多个不同的值

如果一个时段包含某个值，则该值的所有记录都必须位于此时段中

Hive Bucket，段划分是指将指定数据的列的值作为key进行混列，混列为指定数量的段

目的与分区表类似，过滤时不要全局遍历所有数据，而是遍历所属的水桶即可。 这样也可以支持

维持高效采样。

选择适当的文件存储格式Apache Hive后，Apache Hadoop中使用的熟悉文件格式，如TextFile、SequenceFile、RCFile、Avro、ORC和ParquetFile

存储格式通常需要根据业务进行选择。 在实际操作中，大多数表都使用两种类型的存储： TextFile和Parquet

格式之一。 TextFile是最简单的存储格式，是纯文本记录，也是Hive的默认格式。 虽然有磁盘开销

虽然比较大，查询效率也低，但是经常被用作跳板。 RCFile、ORC、Parquet等格式的表显示在

文件必须直接导入数据，用TextFile中继。 Parquet和ORC都是Apache的开源列存储格式。

与传统的线存储相比，列存储更适合批量处理OLAP查询，并且支持压缩和编码改进。

创建表时，特别是对于宽表，尽量使用ORC、ParquetFile等列型存储格式。 列型存储器的表分别是

列中的数据以物理方式存储在一起，Hive查询只遍历所需列中的数据，大大减少了要处理的数据量。

第一种：TextFile

1、记忆方式：原始存储。 默认格式。 如果在创建表时未指定默认值。2、每一行以记录结束，每一行以换行符'n '结束。 如果不压缩数据，磁盘开销会变大，数据分析开销也会变大。 3、可与Gzip、Bzip2等压缩方式组合使用。 (系统自动检查，查询时自动解压。 )推荐一种可分割的压缩算法。第二种：Sequence File

是Hadoop API提供的二进制文件，具有使用方便、可分割、个压缩的特点。 2、支持无、记录、块三种压缩选项。 RECORD的压缩率低，建议进行BLOCK压缩。第三种：RC File

1、存储方式：数据按行分块，按块按列存储。 a、首先，将数据逐行分块，保证同一个record在一个块中，这样读一个记录就不需要读多个块。 b、第二，块数据串行存储器有利于数据压缩和高速串行存取。 2、相对来说，RCFile在提高任务执行性能方面提高不大，但可以节约存储空间。 可以使用升级版的ORC格式。第四种：ORC File

1、存储方式：数据按行分块，每个块按列存储2、Hive提供的新格式。 是RCFile的升级版，性能大大提高，而且数据可以压缩存储，压缩快，可以高速串行访问。 3、ORC File根据列编制索引，查询时立即。第五种：Parquet File

1、记忆方式：列式记忆。 2、Parquet对大型查询类型高效。 Parquet在扫描特定表中的特定列查询时特别有用。 Parquet通常使用快照、Gzip压缩。 默认Snappy。 3、Parquet支持Impala查询引擎。 4、表格文件保存格式尽量采用Parquet或ORC，不仅降低了保存量，而且优化了查询、压缩、表格关联等性能。 4 .选择适当压缩格式的Hive语句最终转换为MapReduce程序执行，但MapReduce的性能瓶颈是网络I/o和磁盘

解决IO、性能瓶颈，最重要的是减少数据量，压缩数据是一个好方法。 压缩减少了数据

虽然是量，但压缩过程会消耗CPU。 但是，在Hadoop中，性能瓶颈往往不是CPU，CPU压力不大

利用了压缩中比较空闲的CPU。

sql语法和执行参数级别1 .列剪辑是在查询时只读取所需的列，而分区剪辑是只读取所需的分区。 在列多情况或数据量多的情况下，

如果未指定select *或分区，则全列扫描和全表扫描的效率都将下降。

Hive在读取数据时，只能读取查询

所需要用到的列，而忽略其他的列。这样做可以节省读取开
销：中间表存储开销和数据整合开销。

set hive.optimize.cp = true; ## 列裁剪，取数只取查询中需要用到的列，默认是true 2.谓词下推

将 SQL 语句中的 where 谓词逻辑都尽可能提前执行，减少下游处理的数据量。对应逻辑优化器是
PredicatePushDown。

set hive.optimize.ppd=true; 合并小文件

在执行 MapReduce 程序的时候，一般情况是一个文件的一个数据分块需要一个 mapTask 来处理。但
是如果数据源是大量的小文件，这样就会启动大量的 mapTask 任务，这样会浪费大量资源。可以将输
入的小文件进行合并，从而减少 mapTask 任务数量。

合理控制map task和reduce task的数量

这个优化措施，但凡能用就用！ 大表 join 小表 小表满足需求： 小表数据小于控制条件时
MapJoin 是将 join 双方比较小的表直接分发到各个 map 进程的内存中，在 map 进程中进行 join 操
作，这样就不用进行 reduce 步骤，从而提高了速度。只有 join 操作才能启用 MapJoin。

join 数据倾斜优化

在编写 Join 查询语句时，如果确定是由于 join 出现的数据倾斜，那么请做如下设置：

join的键对应的记录条数超过这个值则会进行分拆，值根据具体数据量设置set hive.skewjoin.key=100000;如果是join过程出现倾斜应该设置为trueset hive.optimize.skewjoin=false;

如果开启了，在 Join 过程中 Hive 会将计数超过阈值 hive.skewjoin.key（默认100000）的倾斜 key 对
应的行临时写进文件中，然后再启动另一个 job 做 map join 生成结果。
通过 hive.skewjoin.mapjoin.map.tasks 参数还可以控制第二个 job 的 mapper 数量，默认
10000。

CBO优化

join的时候表的顺序的关系：前面的表都会被加载到内存中。后面的表进行磁盘扫描。

select a.*, b.*, c.* from a join b on a.id = b.id join c on a.id = c.id;

Hive 自 0.14.0 开始，加入了一项 “Cost based Optimizer” 来对 HQL 执行计划进行优化，这个功能通
过 “hive.cbo.enable” 来开启。在 Hive 1.1.0 之后，这个 feature 是默认开启的，它可以 自动优化 HQL
中多个 Join 的顺序，并选择合适的 Join 算法。

CBO，成本优化器，代价最小的执行计划就是最好的执行计划。传统的数据库，成本优化器做出最优化
的执行计划是依据统计信息来计算的。Hive 的成本优化器也一样。
Hive 在提供最终执行前，优化每个查询的执行逻辑和物理执行计划。这些优化工作是交给底层来完成
的。根据查询成本执行进一步的优化，从而产生潜在的不同决策：如何排序连接，执行哪种类型的连
接，并行度等等。
要使用基于成本的优化（也称为CBO），请在查询开始设置以下参数：

set hive.cbo.enable=true;set hive.compute.query.using.stats=true;set hive.stats.fetch.column.stats=true;set hive.stats.fetch.partition.stats=true; hive架构层面 启用本地抓取

Hive 的某些 SQL 语句需要转换成 MapReduce 的操作，某些 SQL 语句就不需要转换成 MapReduce 操
作，但是同学们需要注意，理论上来说，所有的 SQL 语句都需要转换成 MapReduce 操作，只不过
Hive 在转换 SQL 语句的过程中会做部分优化，使某些简单的操作不再需要转换成 MapReduce，例
如：
1、只是 select * 的时候
2、where 条件针对分区字段进行筛选过滤时
3、带有 limit 分支语句时

Hive 从 HDFS 中读取数据，有两种方式：启用MapReduce读取 和 直接抓取。
直接抓取数据比 MapReduce 方式读取数据要快的多，但是只有少数操作可以使用直接抓取方式。
可以通过 hive.fetch.task.conversion 参数来配置在什么情况下采用直接抓取方式：

minimal：只有 select * 、在分区字段上 where 过滤、有 limit 这三种场景下才启用直接抓取方式。more：在 select、where 筛选、limit 时，都启用直接抓取方式。 本地执行优化

Hive 在集群上查询时，默认是在集群上多台机器上运行，需要多个机器进行协调运行，这种方式很好
的解决了大数据量的查询问题。但是在 Hive 查询处理的数据量比较小的时候，其实没有必要启动分布
式模式去执行，因为以分布式方式执行设计到跨网络传输、多节点协调等，并且消耗资源。对于小数据
集，可以通过本地模式，在单台机器上处理所有任务，执行时间明显被缩短。

启动本地模式涉及到三个参数：--打开hive自动判断是否启动本地模式的开关set hive.exec.mode.local.auto=true;-- map任务数最大值，不启用本地模式的task最大个数set hive.exec.mode.local.auto.input.files.max=4;-- map输入文件最大大小，不启动本地模式的最大输入文件大小set hive.exec.mode.local.auto.inputbytes.max=134217728; JVM重用

Hive 语句最终会转换为一系列的 MapReduce 任务，每一个MapReduce 任务是由一系列的 MapTask
和 ReduceTask 组成的，默认情况下，MapReduce 中一个 MapTask 或者 ReduceTask 就会启动一个
JVM 进程，一个 Task 执行完毕后，JVM 进程就会退出。这样如果任务花费时间很短，又要多次启动
JVM 的情况下，JVM 的启动时间会变成一个比较大的消耗，这时，可以通过重用 JVM 来解决。

set mapred.job.reuse.jvm.num.tasks=5;

JVM也是有缺点的，开启JVM重用会一直占用使用到的 task 的插槽，以便进行重用，直到任务完成后才
会释放。如果某个 不平衡的job 中有几个 reduce task 执行的时间要比其他的 reduce task 消耗的时间
要多得多的话，那么保留的插槽就会一直空闲却无法被其他的 job 使用，直到所有的 task 都结束了才
会释放。
根据经验，一般来说可以使用一个 cpu core 启动一个 JVM，假如服务器有 16 个 cpu core ，但是这个
节点，可能会启动 32 个mapTask，完全可以考虑：启动一个JVM，执行两个Task。

并行执行

有的查询语句，Hive 会将其转化为一个或多个阶段，包括：MapReduce 阶段、抽样阶段、合并阶段、
limit 阶段等。默认情况下，一次只执行一个阶段。但是，如果某些阶段不是互相依赖，是可以并行执行
的。多阶段并行是比较耗系统资源的。
一个 Hive SQL 语句可能会转为多个 MapReduce Job，每一个 job 就是一个 stage，这些 Job 顺序执
行，这个在 cli 的运行日志中也可以看到。但是有时候这些任务之间并不是是相互依赖的，如果集群资
源允许的话，可以让多个并不相互依赖 stage 并发执行，这样就节约了时间，提高了执行速度，但是如
果集群资源匮乏时，启用并行化反倒是会导致各个 Job 相互抢占资源而导致整体执行性能的下降。启用
并行化：

-- 可以开启并发执行。set hive.exec.parallel=true;--同一个sql允许最大并行度，默认为8。set hive.exec.parallel.thread.number=16;