Hive内部培训资料及性能优化
Hive优化
Hive优化1 概述1.1 Hive的特征1.可以通过SQL轻松访问数据的工具，从而实现数据仓库的任务，报告和数据分析等。
2.可以使已经存储的数据结构化。
3.可以直接访问存储在HDFS或者其他数据存储系统中的文件。
4.Hive除了支持MapReduce计算引擎之外还支持Spark和Tez这两种分布式计算引擎。
5.提供了类似sql查询语句的HiveSql对数据进行分析。
6.存储格式多样化。
1.2 Hive优势Hive的强大之处不是在与将数据转换成特定格式，而是利用Hadoop本身的InputFormat API来从不同的数据源中读取数据，然后使用OutputFormat API将数据写成不同的格式。
所以对于不同的数据源，或者写出不同的格式就需要不同的对应的InputFormat和OutputFormat类的实现。
Hive拥有统一的元数据管理，所以和spark，impala等SQL引擎通用。
（通用指的是拥有了统一的Metastore之后，在Hive中创建一张表，在spark/impala中能通用，反之在spark中创建一张表，在Hive中也是能用的）只需要共用元数据，就可以切换SQL引擎了。
Hive使用SQL语法，提供快速开发能力，还可以通过用户定义的函数，用户定义的聚合和用户定义的表函数进行扩展，避免了去写MapReduce，减少开发人员学习成本。
Hive中不仅可以使用逗号和制表符分隔文本文件。
还可以使用sequence File、RC、ORC、Parquet。
Hive指在最大限度的提高可伸缩性，性能，可扩展性，容错性以及与其输出格式的松散耦合。
数据离线处理：日志分析，海量数据结构化分析。
2 Hive函数Hive的SQL可以通过用户定义的函数，用户定义的聚合和用户定义的表函数进行扩展当Hive提供的内置函数无法满足你的业务需求时，此时就可以考虑使用用户自定义函数UDF(用户定义函数)，UDAF(用户定义聚合函数)，UDTF(用户定义表函数)的区别：▪udf 一进一出▪udaf 聚集函数，多进一出▪udtf 一进多出3 Hive优化3.1 慎用api大数据场景下不害怕数据量大，但是害怕数据倾斜。
Hive的10种优化总结
Hive的10种优化总结Hive作为⼤数据领域常⽤的数据仓库组件，在平时设计和查询时要特别注意效率。
影响Hive效率的⼏乎从不是数据量过⼤，⽽是数据倾斜、数据冗余、job或I/O过多、MapReduce分配不合理等等。
对Hive的调优既包含对HiveSQL语句本⾝的优化，也包含Hive配置项和MR⽅⾯的调整。
列裁剪和分区裁剪最基本的操作。
所谓列裁剪就是在查询时只读取需要的列，分区裁剪就是只读取需要的分区。
以我们的⽇历记录表为例：select uid,event_type,record_datafrom calendar_record_logwhere pt_date >= 20190201 and pt_date <= 20190224and status = 0;当列很多或者数据量很⼤时，如果select *或者不指定分区，全列扫描和全表扫描效率都很低。
Hive中与列裁剪优化相关的配置项是hive.optimize.cp，与分区裁剪优化相关的则是hive.optimize.pruner，默认都是true。
在HiveSQL解析阶段对应的则是ColumnPruner逻辑优化器。
谓词下推在关系型数据库如MySQL中，也有谓词下推（Predicate Pushdown，PPD）的概念。
它就是将SQL语句中的where谓词逻辑都尽可能提前执⾏，减少下游处理的数据量。
例如以下HiveSQL语句：select a.uid,a.event_type,b.topic_id,b.titlefrom calendar_record_log aleft outer join (select uid,topic_id,title from forum_topicwhere pt_date = 20190224 and length(content) >= 100) b on a.uid = b.uidwhere a.pt_date = 20190224 and status = 0;对forum_topic做过滤的where语句写在⼦查询内部，⽽不是外部。
大数据培训之hive学习
HIVE编程实战一HIVE基础1.1 什么是hive？Hadoop生态系统是为了处理大数据集而产生的一个合乎成本效益的解决方案。
Hadoop实现了一个特别的计算模型，即MapReduce，其可以将计算认为分割成多个处理单元然后分散到Hadoop集群中的家用或服务器级别的硬件机器上，从而降低成本并提供动态扩展的能力。
基于这个计算模型的下面是一个被称之为HDFS（Hadoop分布式文件系统）的分布式文件系统。
不过，仍然存在一个挑战，那就是用户如何从一个现有的数据基础架构转移到Hadoop 上，而这个基础架构是基于传统关系型数据库和结构化SQL语言的。
对于大量的关系型数据库的维护、实施、开发人员，这个问题将如何解决呢？这就是Hive出现的原因。
Hive提供了一个被称之为Hive查询语言，简称HQL的SQL方言（与MYSQL及其类似），用来查询存储在Hadoop集群中的数据。
Hive可以将大多数的查询转换为MapReduce的job任务，从而使得采用简单的SQL编程方式，来替换掉原有的MapReduce中的复杂java编程。
Hive最适合于数据仓库应用程序，使用该应用程序进行相关的静态数据分析，不需要快速响应给出结果，而且数据本身不会频繁变化。
Hive不是一个完整的数据库，其中最大的限制就是hive不支持记录级别的更新、插入或者删除，但是用户可以通过查询生成新表或者将查询结果导入到文件中。
同时，基于Mapreduce的hive查询延时比较严重，没有传统的RDBMS查询速度快，hive不支持事物。
因此，hive是最适合数据仓库应用程序的，其可以维护海量数据，而且可以对数据进行分析，然后形成统计结果以及报表等。
1.2 HIVE组成模块所有的命令和查询都会进入到Driver（驱动模块），通过该模块对输入进行解析编译，对需求的计算进行优化，然后按照指定的步骤执行。
当需要启动MapReduce任务时，Hive 本身是不会生成Java Mapreduce程序。
Hive性能调优指南
Hive性能调优指南目录目录 (2)第一章 Hive调优的总体原则 (4)1.1 计算性能的优化 (4)1.2 I/O的优化 (5)第二章 Hive优化的相关原理 (5)2.1关于Join (6)Ø 2.1.1 Reduce Side Join (6)Ø 2.1.2 Map Side Join (7)Ø 2.1.3 Semi Join (9)Ø 2.1.4 Reduce Side Join + Bloom Filter (10)Ø 2.1.5 Hive提供的Join方式 (10)Shuffle Join (10)Broadcast Join (11)Bucket Map Join (11)Sort-Merge-Bucket Join(SMJ) (12)Skew Join (13)Left Semi Join (14)2.2关于排序 (15)Ø 2.2.1 Order By (15)Ø 2.2.2 Sort By (15)Ø 2.2.3 Distribute By (16)Ø 2.2.4 Cluster By (16)2.3数据倾斜问题 (17)Ø什么是数据倾斜 (17)Ø数据倾斜的常见场景 (17)l空值数据倾斜 (17)l不同数据类型关联产生数据倾斜 (18)l大表Join的数据偏斜 (18)Ø数据倾斜的解决方案 (19)l参数调节 (19)l SQL语句调节 (20)2.4关于Shuffle (20)ØShuffle概述 (20)ØMap端Shuffle过程 (21)ØReduce端Shuffle过程 (22)第三章Hive调优方案及分析 (24)Ø 3.1 Hadoop计算框架的特点 (24)Ø 3.2 Hadoop调优策略 (25)应用程序级别调优 (25)作业级别调优 (26)任务级别调优 (27)管理员级别调优 (30)操作系统级别调优 (30)JVM级别调优 (31)Ø 3.3 Hive调优项目checklist (32)Ø 3.4 Hive优化的常用案例 (32)l优化Join连接 (32)ØJoin的原则 (32)Ø使用Map Join (33)l合理设置map task数 (35)l怎么设置reduce的任务数 (37)l exist in子句 (39)l合并小文件 (39)l开启本地Map任务 (39)l优化limit (40)l JVM重用 (40)l推测式执行（Speculative Execution） (41)l Group By (41)l使用Multi-group by合并MR数 (41)l Bucket and Samping (42)l Partition (42)l Strict模式 (43)l避免笛卡尔积 (44)l慎用count(distinct) (44)l利用Hive中的union all特性减少Map/Reduce个数 (45)l Controlling data locality with Hive (46)l使用ORC File (47)l使用Tez (47)l数据加载场景的优化案例 (48)l多表关联的优化 (49)l使用并发 (51)l Urber模式 (51)l本地map 任务 (52)Ø 3.5 Hive调优参数 (53)第五章 附录-参数 (58)第一章 Hive调优的总体原则 1.1 计算性能的优化原则：合理调整map/reduce的任务数量，充分利用集群中的CPUa)尽量保证每个map任务处理的数据量保持均衡b)Reduce任务数量=总任务槽*0.95：这样为一个推测式执行任务提供空闲机器。
[Hive]-常规优化以及执行计划解析
![[Hive]-常规优化以及执行计划解析](https://img.taocdn.com/s3/m/75a69465ae1ffc4ffe4733687e21af45b307fefc.png)
[Hive]-常规优化以及执⾏计划解析1.HiveSQL优化 1.1 中⼼思想 这⾥以Hive On MapReduce 为例,Hive On Spark等思路也是⼀致的. HiveSQL会最终转化为MapReduce进⾏执⾏,那么优化的前提是⾄少对MapReduce有基本的了解 其次是必须了解HiveSQL会转化成怎么样的MapReduce作业(执⾏计划),这是优化HiveSQL根本依据.切记,HiveSQL的优化本质是对MapReduce作业的优化. ⽐如MapReduce的⼀些特点: 数据读取和写⼊,都是针对HDFS(磁盘)⽽⾔,都是IO操作 不喜欢某⼀个任务过⼤(数据倾斜).⼀个经典的结论:数据量不是问题,数据倾斜才是 不喜欢⼤量过⼩的任务.任务资源申请等本⾝初始化和管理也是需要消耗时间和资源得.⼤量过⼩任务,导致时间和资源都花在任务维护上了 所以在HiveSQL上,也是针对这些特点来进⾏优化 1.2 ⼀些常见的优化思路 1.2.1 IO 只查询需要的列.MapReduce会根据查询谓词裁剪列,简单说就是不查询的列不读,这样可以降低IO 尽可能的使⽤表分区.表分区条件后,MapReduce会直接跳过不需要的分区的全部⽂件,极⼤的降低IO 1.2.2 数据倾斜 1.2.2.1 慎⽤count(distinct) 慎⽤count(distinct)原因是容易造成数据倾斜.因为其执⾏的MapReduce是以GroupBy分组,再对distinct列排序,然后输出交给Reduce. 问题就在这⾥,相⽐其它GroupBy聚合统计,count(distinct)少⼀个关键步骤(Map的预计算,在Map端提前做⼀次聚合再将聚合结果交给Reduce) 当Map直接将全部数据交给Reduce后,如果数据的分组本⾝不平衡(⽐如及格,80%以上及格数据),会造成某⼀些Reduce处理太过多的数据,这就是数据倾斜 count(distinct)可以考虑换GroupBy⼦查询 1.2.2.2 注意null值带来的数据倾斜 所有null会认为是同⼀个值,会⾛同⼀个Map,如果null占的⽐重⼀⼤,⼜是⼀个数据倾斜.这是业务上考虑是否能做过滤 这⾥同样适⽤其它的业务null值(⽐如常见的0,1,-1,-99等业务默认值) 1.2.3 表关联 ⼤表放后 MapReduce从后往前构建数据,先过滤⼤表把数据量降下来,可以在Reduce端的Hash-Join减少数据量,提⽰效率 同列关联如可能,⽤同⼀列关联同列关联,⽆论关联多少表都是⼀个Map搞定,如果不是同列,就会新开⼀个MapReduce 1.2.4 配置优化 这⾥的配置,是指MapReduce或Spark配置2.HiveSQL的MR转换 2.1 不跑MapReduce的情况 HiveSQL不是每种情况都会跑MapReduce的.基本查询,或者是不涉及计算(⽐如查询分区表)的查询,是不会启动MapReduce任务的 explain select * from dept_et limit 1; STAGE DEPENDENCIES:Stage-0 is a root stageSTAGE PLANS:Stage: Stage-0Fetch Operatorlimit: 1Processor Tree:TableScanalias: dept_etStatistics: Num rows: 1 Data size: 322 Basic stats: COMPLETE Column stats: NONESelect Operatorexpressions: id (type: int), name (type: string), city (type: string)outputColumnNames: _col0, _col1, _col2Statistics: Num rows: 1 Data size: 322 Basic stats: COMPLETE Column stats: NONELimitNumber of rows: 1Statistics: Num rows: 1 Data size: 322 Basic stats: COMPLETE Column stats: NONEListSink 2.2 join explain select * from dept_et et join dept_mg mg on et.id= mg.id <!--构筑MR作业流 4=>3=>0(结束) -->STAGE DEPENDENCIES:Stage-4 is a root stageStage-3 depends on stages: Stage-4Stage-0 depends on stages: Stage-3STAGE PLANS:<!--第⼀步MR 表扫描mg(dept_mg mg) ⾃带⼀个基础过滤谓词(id is not null)这⾥可以看出 join的基准表是后表Map Reduce Local 本地化的MapReduce因为测试表的数据量⾮常⼩,所以Hive最终选择将数据拉取到本地直接操作,⽽不是去执⾏⼀个完整的分布式MapReduce-->Stage: Stage-4Map Reduce Local WorkAlias -> Map Local Tables:mgFetch Operatorlimit: -1Alias -> Map Local Operator Tree:mgTableScanalias: mgStatistics: Num rows: 1 Data size: 79 Basic stats: COMPLETE Column stats: NONEFilter Operatorpredicate: id is not null (type: boolean)Statistics: Num rows: 1 Data size: 79 Basic stats: COMPLETE Column stats: NONEHashTable Sink Operatorkeys:0id (type: int)1id (type: int)<!--第⼆步的MapReduce任务表扫描执⾏⼀个 Map Join输出_col0, _col1, _col2, _col6, _col7, _col8(也就是语句中的*,全部共6个字段)输出结果为 File Output 临时⽂件(compressed: false 不压缩)-->Stage: Stage-3Map ReduceMap Operator Tree:TableScanalias: etStatistics: Num rows: 1 Data size: 322 Basic stats: COMPLETE Column stats: NONEFilter Operatorpredicate: id is not null (type: boolean)Statistics: Num rows: 1 Data size: 322 Basic stats: COMPLETE Column stats: NONEMap Join Operatorcondition map:Inner Join 0 to 1keys:0id (type: int)1id (type: int)outputColumnNames: _col0, _col1, _col2, _col6, _col7, _col8Statistics: Num rows: 1 Data size: 354 Basic stats: COMPLETE Column stats: NONESelect Operatorexpressions: _col0 (type: int), _col1 (type: string), _col2 (type: string), _col6 (type: int), _col7 (type: string), _col8 (type: string) outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5Statistics: Num rows: 1 Data size: 354 Basic stats: COMPLETE Column stats: NONEFile Output Operatorcompressed: falseStatistics: Num rows: 1 Data size: 354 Basic stats: COMPLETE Column stats: NONEtable:input format: org.apache.hadoop.mapred.TextInputFormatoutput format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormatserde: zySimpleSerDeLocal Work:Map Reduce Local WorkStage: Stage-0Fetch Operatorlimit: -1Processor Tree:ListSink 2.3 group by explain select city,sum(id) from dept_et group by city; 执⾏计划如下:STAGE DEPENDENCIES:Stage-1 is a root stageStage-0 depends on stages: Stage-1STAGE PLANS:<!--stage定义,⼀个stage对应⼀个MapReduce-->Stage: Stage-1<!--Map过程-->Map ReduceMap Operator Tree:TableScan //表扫描alias: dept_etStatistics: Num rows: 3 Data size: 322 Basic stats: COMPLETE Column stats: NONE //表dept_et的统计数据预估Select Operator //查询列裁剪,表⽰只需要 city (type: string), id (type: int) 两列expressions: city (type: string), id (type: int)outputColumnNames: city, idStatistics: Num rows: 3 Data size: 322 Basic stats: COMPLETE Column stats: NONE<!--map操作定义是以city (type: string)取hash作为key,执⾏函数sum(id),结果为_col0, _col1(hash(city),sum(id))-->Group By Operatoraggregations: sum(id) //分组执⾏函数=>sum(id)keys: city (type: string)mode: hashoutputColumnNames: _col0, _col1Statistics: Num rows: 3 Data size: 322 Basic stats: COMPLETE Column stats: NONE<!--map端的输出-->Reduce Output Operatorkey expressions: _col0 (type: string) //Map端输出的Key是_col0(hash(city))sort order: +Map-reduce partition columns: _col0 (type: string)Statistics: Num rows: 3 Data size: 322 Basic stats: COMPLETE Column stats: NONEvalue expressions: _col1 (type: bigint) //Map端输出的Value是_col1(sum(id))<!--Reduce过程合并多个Map的输出以_col0(也就是map输出的hash(city))为key 执⾏sum(VALUE._col0(也就是map输出的sum(id))),执⾏结果也是_col0, _col1(hash(city),sum(sum(id)))-->Reduce Operator Tree:Group By Operatoraggregations: sum(VALUE._col0keys: KEY._col0 (type: string)mode: mergepartial //partial(多个map的输出)merge(合并)outputColumnNames: _col0, _col1Statistics: Num rows: 1 Data size: 107 Basic stats: COMPLETE Column stats: NONE<!--Reduce端的输出输出为⼀个临时⽂件,不压缩-->File Output Operatorcompressed: falseStatistics: Num rows: 1 Data size: 107 Basic stats: COMPLETE Column stats: NONEtable:input format: org.apache.hadoop.mapred.TextInputFormatoutput format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormatserde: zySimpleSerDeStage: Stage-0Fetch Operatorlimit: -1Processor Tree:ListSink 2.4 distinct 2.4.1 distinct⼀个 select city,count(distinct(name)) from dept_et group by city; 只有⼀个distinct,将group字段和distinct字段⼀起组合为Map的输出Key,然后把group字段作为Reduce的Key,在Reduce阶段保存LastKey STAGE DEPENDENCIES:Stage-1 is a root stageStage-0 depends on stages: Stage-1STAGE PLANS:Stage: Stage-1Map Reduce<!--Map端定义输⼊: 表扫描 dept_et 原值查询city,name执⾏过程: 以group列(city),distinct列(name)做为Key,执⾏表达式count(DISTINCT name)输出:_col0, _col1, _col2 (city,name,count(DISTINCT name))-->Map Operator Tree:TableScanalias: dept_etStatistics: Num rows: 1 Data size: 322 Basic stats: COMPLETE Column stats: NONESelect Operatorexpressions: city (type: string), name (type: string) //没有计算函数,直接是查询原值outputColumnNames: city, nameStatistics: Num rows: 1 Data size: 322 Basic stats: COMPLETE Column stats: NONEGroup By Operatoraggregations: count(DISTINCT name)keys: city (type: string), name (type: string)mode: hashoutputColumnNames: _col0, _col1, _col2Statistics: Num rows: 1 Data size: 322 Basic stats: COMPLETE Column stats: NONEReduce Output Operatorkey expressions: _col0 (type: string), _col1 (type: string)sort order: ++Map-reduce partition columns: _col0 (type: string)Statistics: Num rows: 1 Data size: 322 Basic stats: COMPLETE Column stats: NONE <!--Reduce端定义接收Map端的输出,再以_col0作为Key,再做⼀次聚合(对做⼀次去重计数) 结果输出到临时⽂件--> Reduce Operator Tree:Group By Operatoraggregations: count(DISTINCT KEY._col1:0._col0)keys: KEY._col0 (type: string)mode: mergepartialoutputColumnNames: _col0, _col1Statistics: Num rows: 1 Data size: 322 Basic stats: COMPLETE Column stats: NONEFile Output Operatorcompressed: falseStatistics: Num rows: 1 Data size: 322 Basic stats: COMPLETE Column stats: NONEtable:input format: org.apache.hadoop.mapred.TextInputFormatoutput format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormatserde: zySimpleSerDeStage: Stage-0Fetch Operatorlimit: -1Processor Tree:ListSink 2.4.2 多个distinct字段 select dealid, count(distinct uid), count(distinct date) from order group by dealid;。
Hive（十）Hive性能调优总结
Hive（⼗）Hive性能调优总结⼀、Fetch抓取1、理论分析Fetch抓取是指，Hive中对某些情况的查询可以不必使⽤MapReduce计算。
例如：SELECT * FROM employees;在这种情况下，Hive可以简单地读取employee对应的存储⽬录下的⽂件，然后输出查询结果到控制台。
在hive-default.xml.template⽂件中hive.fetch.task.conversion默认是more，⽼版本hive默认是minimal，该属性修改为more以后，在全局查找、字段查找、limit查找等都不⾛mapreduce。
<property><name>hive.fetch.task.conversion</name><value>more</value><description>Expects one of [none, minimal, more].Some select queries can be converted to single FETCH task minimizing latency.Currently the query should be single sourced not having any subquery and should not haveany aggregations or distincts (which incurs RS), lateral views and joins.0. none : disable hive.fetch.task.conversion1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only2. more : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns)</description></property>2、案例实操（1）把hive.fetch.task.conversion设置成none，然后执⾏查询语句，都会执⾏mapreduce程序。
Hive优化培训ppt课件
set hive.groupby.skewindata = true; -- 有数据倾斜的时候进行负载均衡（默认是false） 当选项设定为 true 时，生成的查询计划有两个 MapReduce 任务。在第一个 MapReduce 任务中，map 的输出结果会随机分布 到 reduce 中，每个 reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的group by key有可能分发到不同的 reduce 中，从而达到负载均衡的目的；第二个 MapReduce 任务再根据预处理的数据结果按照group by key分布到各个 reduce 中，最 后完成最终的聚合操作。
Join优化
1）优先过滤数据，最大限度减少参与join的数据量
2）小表join大表原则 join 操作的reduce阶段，位于join左边的表内容会被加载进内存
3）多表join使用相同多连接键
4）启用mapjoin mapjoin是将join双方比较小的表直接分发到各个map进程的内存中，在map进程中进行join操作，这样就 不用进行reduce步骤，从而提高了速度
启用压缩
1）Map输出压缩
set press=true; set press.codec=press.SnappyCodec;
2）中间数据压缩
set press.intermediate=true; set pression.codec=press.SnappyCodec; set pression.type=BLOCK;
-- 是否根据输入小表的大小，自动将reduce端的common join 转化为map join，将小表刷入内存中。 set hive.auto.convert.join = true; -- 刷入内存表的大小(字节) set hive.mapjoin.smalltable.filesize = 2500000; -- Map Join所处理的最大的行数。超过此行数，Map Join进程会异常退出 set hive.mapjoin.maxsize=1000000;
Hive调优及优化的12种方式
Hive调优及优化的12种方式请记住：在数据处理中，不怕数据量大，就怕数据倾斜！针对于Hive内部调优的一些方式01.请慎重使用COUNT(DISTINCT col);原因：distinct会将b列所有的数据保存到内存中，形成一个类似hash 的结构，速度是十分的块；但是在大数据背景下，因为b列所有的值都会形成以key值，极有可能发生OOM解决方案：所以，可以考虑使用Group By 或者ROW_NUMBER() OVER(PARTITION BY col)方式代替COUNT(DISTINCT col)02.小文件会造成资源的多度占用以及影响查询效率原因：•众所周知，小文件在HDFS中存储本身就会占用过多的内存空间，那么对于MR查询过程中过多的小文件又会造成启动过多的Mapper Task, 每个Mapper都是一个后台线程，会占用JVM的空间•在Hive中，动态分区会造成在插入数据过程中，生成过多零碎的小文件（请回忆昨天讲的动态分区的逻辑）•不合理的Reducer Task数量的设置也会造成小文件的生成，因为最终Reducer是将数据落地到HDFS中的解决方案：在数据源头HDFS中控制小文件产生的个数，比如•采用Sequencefile作为表存储格式，不要用textfile，在一定程度上可以减少小文件（常见于在流计算的时候采用Sequencefile格式进行存储）•减少reduce的数量(可以使用参数进行控制)•慎重使用动态分区，最好在分区中指定分区字段的val值•最好数据的校验工作，比如通过脚本方式检测hive表的文件数量，并进行文件合并•合并多个文件数据到一个文件中，重新构建表03.请慎重使用SELECT *原因：在大数据量多字段的数据表中，如果使用 SELECT * 方式去查询数据，会造成很多无效数据的处理，会占用程序资源，造成资源的浪费解决方案：在查询数据表时，指定所需的待查字段名，而非使用 * 号04.不要在表关联后面加WHERE条件原因：比如以下语句：SELECT * FROM stu as tLEFT JOIN course as t1ON t.id=t2.stu_idWHERE t.age=18;请思考上面语句是否具有优化的空间？如何优化？解决方案：采用谓词下推的技术，提早进行过滤有可能减少必须在数据库分区之间传递的数据量谓词下推的解释：所谓谓词下推就是通过嵌套的方式，将底层查询语句尽量推到数据底层去过滤，这样在上层应用中就可以使用更少的数据量来查询，这种SQL技巧被称为谓词下推(Predicate pushdown)那么上面语句就可以采用这种方式来处理：SELECT * FROM (SELECT * FROM stu WHERE age=18) as tLEFT JOIN course AS t1on t.id=t1.stu_id05.处理掉字段中带有空值的数据原因：一个表内有许多空值时会导致MapReduce过程中,空成为一个key值,对应的会有大量的value值, 而一个key的value会一起到达reduce造成内存不足解决方式：1、在查询的时候，过滤掉所有为NULL的数据，比如：create table res_tbl asselect n.* from(select * from res where id is not null ) nleft join org_tbl o on n.id = o.id;2、查询出空值并给其赋上随机数,避免了key值为空（数据倾斜中常用的一种技巧）create table res_tbl asselect n.* from res nfull join org_tbl o oncase when n.id is null then concat('hive', rand()) else n.id end = o.id;06.设置并行执行任务数通过设置参数 hive.exec.parallel 值为 true，就可以开启并发执行。
Hive优化
Hive常见优化一、数据倾斜1、什么是数据倾斜？Hadoop框架的特性决定最怕数据倾斜•由于数据分布不均匀，造成数据大量的集中到一点，造成数据热点。
节点间数据分布不均衡，会造成map端每个map任务的工作量不同，即map端数据倾斜。
Map-reduce，把相同key提交给同一个reduce，如果key不均衡就会造成不同的reduce的工作量不同。
以京东首页活动为例，曝光率大的是大活动，曝光率小的是小活动：假如reduce1处理的是小活动，reduce2处理大活动，reduce2干的活比其他reduce多很多，会出现其他reduce执行完毕了，reduce2还在缓慢执行。
症状：map阶段快，reduce阶段非常慢；某些map很快，某些map很慢；某些reduce很快，某些reduce奇慢。
如下情况：A、数据在节点上分布不均匀B、join时on关键词中个别值量很大（如null值）C、count(distinct),在数据量大的情况下，容易数据倾斜，因为count(distinct)是按group by字段分组，按distinct字段排序。
其中A无法避免。
B见后边的Join章节。
C语法上有时无法避免如何解决数据倾斜？实际上是没办法避免的，这里的解决只是个别情况起效：有数据倾斜的时候进行负载均衡set hive.groupby.skewindata=false;当选项设定为true，生成的查询计划会有两个MR Job。
第一个MR Job中，Map 的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作。
hive-优化
第一部分：Hadoop 计算框架的特性什么是数据倾斜•由于数据的不均衡原因，导致数据分布不均匀，造成数据大量的集中到一点，造成数据热点Hadoop框架的特性•不怕数据大，怕数据倾斜•jobs数比较多的作业运行效率相对比较低，比如即使有几百行的表，如果多次关联多次汇总，产生十几个jobs，耗时很长。
原因是map reduce作业初始化的时间是比较长的•sum,count,max,min等UDAF，不怕数据倾斜问题,hadoop在map端的汇总合并优化，使数据倾斜不成问题•count(distinct ),在数据量大的情况下，效率较低，因为count(distinct)是按group by 字段分组，按distinct字段排序，一般这种分布方式是很倾斜的第二部分：优化的常用手段优化的常用手段•解决数据倾斜问题•减少job数•设置合理的map reduce的task数，能有效提升性能。
•了解数据分布，自己动手解决数据倾斜问题是个不错的选择•数据量较大的情况下，慎用count(distinct)。
•对小文件进行合并，是行至有效的提高调度效率的方法。
•优化时把握整体，单个作业最优不如整体最优。
第三部分：Hive的数据类型方面的优化优化原则•按照一定规则分区（例如根据日期）。
通过分区，查询的时候指定分区，会大大减少在无用数据上的扫描, 同时也非常方便数据清理。
•合理的设置Buckets。
在一些大数据join的情况下，map join有时候会内存不够。
如果使用Bucket Map Join的话，可以只把其中的一个bucket放到内存中，内存中原来放不下的内存表就变得可以放下。
这需要使用buckets的键进行join的条件连结，并且需要如下设置set hive.optimize.bucketmapjoin = true第四部分：Hive的操作方面的优化•全排序•怎样做笛卡尔积•怎样决定map个数•怎样决定reducer个数•合并MapReduce操作•Bucket 与sampling•Partition•JOIN•Group By•合并小文件全排序•Hive的排序关键字是SORT BY，它有意区别于传统数据库的ORDER BY也是为了强调两者的区别–SORT BY只能在单机范围内排序怎样做笛卡尔积•当Hive设定为严格模式（hive.mapred.mode=strict）时，不允许在HQL语句中出现笛卡尔积•MapJoin是的解决办法•MapJoin，顾名思义，会在Map端完成Join操作。
Hive常用优化技巧
Hive常⽤优化技巧Hive优化最体现程序员的技术能⼒，⾯试官在⾯试时最喜欢问的就是Hive的优化技巧。
技巧1.控制reducer数量下⾯的内容是我们每次在hive命令⾏执⾏SQL时都会打印出来的内容：In order to change the average load for a reducer (in bytes):set hive.exec.reducers.bytes.per.reducer=<number>In order to limit the maximum number of reducers:set hive.exec.reducers.max=<number>In order to set a constant number of reducers:set mapreduce.job.reduces=<number>很多⼈都会有个疑问，上⾯的内容是⼲什么⽤的。
我们⼀⼀来解答，先看set hive.exec.reducers.bytes.per.reducer=<number>，这个⼀条Hive命令，⽤于设置在执⾏SQL的过程中每个reducer处理的最⼤字节数量。
可以在配置⽂件中设置，也可以由我们在命令⾏中直接设置。
如果处理的数据量⼤于number，就会多⽣成⼀个reudcer。
例如，number = 1024K，处理的数据是1M，就会⽣成10个reducer。
我们来验证下上⾯的说法是否正确：1. 执⾏set hive.exec.reducers.bytes.per.reducer=200000;命令，设置每个reducer处理的最⼤字节是200000。
2. 执⾏sql:select user_id,count(1) as cntfrom orders group by user_id limit 20;执⾏上⾯的sql时会在控制台打印出信息：Number of reduce tasks not specified. Estimated from input data size: 159In order to change the average load for a reducer (in bytes):set hive.exec.reducers.bytes.per.reducer=<number>In order to limit the maximum number of reducers:set hive.exec.reducers.max=<number>In order to set a constant number of reducers:set mapreduce.job.reduces=<number>Starting Job = job_1538917788450_0020, Tracking URL = http://hadoop-master:8088/proxy/application_1538917788450_0020/Kill Command = /usr/local/src/hadoop-2.6.1/bin/hadoop job -kill job_1538917788450_0020Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 159控制台打印的信息中第⼀句话：Number of reduce tasks not specified. Estimated from input data size: 159。
Hive原理及查询优化课件
Parser
Semantic Analyzer
Logical Plan Gen.
22
Logical Optimizer
22
Physical Plan Gen.
Physical Optimizer
INSERT OVERWRITE TABLE access_log_temp2 SELECT er, a.prono, p.maker, p.price FROM access_log_hbase a JOIN product_hbase p ON (a.prono = p.prono);
Logical O2p0timizer
Physical Plan Gen.
Physical Optimizer
OP Tree
TableScanOperator TS_1
ReduceSinkOperato r
RS_2
TableScanOperator TS_0
ReduceSinkOperato r
RS_3
Hive QL
Hive QL
AST
INSERT OVERWRITE TABLE access_log_temp2
SELECT er, a.prono, p.maker, p.price
FROM access_log_hbase a JOIN product_hbase p ON (a.prono =
Hadoop
•
•
Metastor e
Client Driver Compiler
Hadoop
• 字符串转换成为Parse Tree的形式
Parser
• 将ParseTree转换灰成为查询块的图，并填 Semantic 充元数据和较验
Hive性能优化
Hive性能优化1.概述 继续《》⼀⽂中的剩余部分，本篇博客赘述了在⼯作中总结Hive的常⽤优化⼿段和在⼯作中使⽤Hive出现的问题。
下⾯开始本篇⽂章的优化介绍。
2.介绍 ⾸先，我们来看看Hadoop的计算框架特性，在此特性下会衍⽣哪些问题？数据量⼤不是问题，数据倾斜是个问题。
jobs数⽐较多的作业运⾏效率相对⽐较低，⽐如即使有⼏百⾏的表，如果多次关联多次汇总，产⽣⼗⼏个jobs，耗时很长。
原因是map reduce作业初始化的时间是⽐较长的。
sum,count,max,min等UDAF，不怕数据倾斜问题,hadoop在map端的汇总合并优化，使数据倾斜不成问题。
count(distinct ),在数据量⼤的情况下，效率较低，如果是多count(distinct )效率更低，因为count(distinct)是按group by 字段分组，按distinct字段排序，⼀般这种分布⽅式是很倾斜的。
举个例⼦：⽐如男uv,⼥uv，像淘宝⼀天30亿的pv，如果按性别分组，分配2个reduce,每个reduce处理15亿数据。
⾯对这些问题，我们能有哪些有效的优化⼿段呢？下⾯列出⼀些在⼯作有效可⾏的优化⼿段：好的模型设计事半功倍。
解决数据倾斜问题。
减少job数。
设置合理的map reduce的task数，能有效提升性能。
(⽐如，10w+级别的计算，⽤160个reduce，那是相当的浪费，1个⾜够)。
了解数据分布，⾃⼰动⼿解决数据倾斜问题是个不错的选择。
set hive.groupby.skewindata=true;这是通⽤的算法优化，但算法优化有时不能适应特定业务背景，开发⼈员了解业务，了解数据，可以通过业务逻辑精确有效的解决数据倾斜问题。
数据量较⼤的情况下，慎⽤count(distinct)，count(distinct)容易产⽣倾斜问题。
对⼩⽂件进⾏合并，是⾏⾄有效的提⾼调度效率的⽅法，假如所有的作业设置合理的⽂件数，对云梯的整体调度效率也会产⽣积极的正向影响。
Hive调优总结
Hive调优总结Hive调优总结（总结的很棒很全⾯）⽬录列裁剪与分区裁剪多对多关联合理使⽤ＭapJoin合理使⽤Union All并⾏执⾏Job使⽤本地MR合理使⽤动态分区避免数据倾斜控制Map数和Reduce数中间结果表压缩Hive分桶表Explain详解1.列裁剪与分区裁剪在select中，只拿需要的列，如果有，尽量使⽤分区过滤，少使⽤select *；另外在分区裁剪中，当使⽤外关联时，副表的过滤条件如果使⽤where就会导致先全表关联在过滤，应该改⽤on，或者之间使⽤⼦查询；2.多对多关联避免笛卡尔集；如果存在多对多关联，起码要保证有⼀个表或者结果集的关联键不重复；如果某⼀个关联键的记录数⾮常多，那么分配到该reduce task的数据量将⾮常⼤，会导致整个job很难完成；３.合理使⽤MapJoin⾸先这⾥先介绍⼀下hive⾥的两种joinHive Map Join：MapJoin通常⽤于⼀个很⼩的表和⼀个⼤表进⾏join的场景．⾸先是⼀个Local task，我们暂称为taskA，它负责扫描⼩表b的数据，将其转化为⼀个hashtable的结构，并写⼊本地⽂件，之后将该⽂件加载到DistributeCache中，该HashTable的数据结构可以抽象为下⾯的表。
接下来是⼀个没有reduce的MR，我们暂称之为taskB,它启动MapTasks扫描⼤表a，在Map阶段根据a的每⼀条记录去和DistributeCache中b表对应的HashTable关联，并直接输出结果。
由于MapJoin没有Reduce，所以Map直接输出结果⽂件，有多少map，就有多少结果⽂件Hive Common Join：如果不主动指定MapJoin或者不符合MapJoin的条件，Hive解析器默认的Join操作就是Common Join，即在Reduce阶段完成Join．Map阶段：以关联键的组合为key，value为join之后关⼼的列，按照key排序，value中会包含表的tag信息，⽤于标明此value属于哪个表Shuffle阶段：根据key的值进⾏hash，并将key/value按照hash值推送⾄不同的reduce中reduce阶段：根据key的值完成join操作，期间通过tag来识别不同表中的数据4.合理使⽤Union All对同⼀张表的union all要⽐多重insert快得多对同⼀张表的union all要⽐多重insert快得多。
Hive参数配置调优
Hive参数配置调优 hive通过将查询划分成⼀个或多个MapReduce任务达到并⾏处理的⽬的。
每个任务都可能具有多个mapper和reducer任务，其中⾄少有⼀些是可以并⾏执⾏的。
确定最佳的mapper个数和reducer个数取决于多个变量，例如输⼊的数据量⼤⼩以及对这些数据执⾏的操作类型等。
保持平衡性是很有必要的，对于Spark/Hadoop这样的⼤数据系统来讲，数据量⼤并不可怕,可怕的是数据倾斜，每个节点处理的运算不均衡。
如果有太多的mapper或reducer任务，就会导致启动阶段、调度和运⾏job过程中产⽣过多的开销；⽽如果设置的数量太少，那就有可能没充分利⽤好集群内在并⾏性。
mapred.reduce.tasks所提交 Job 的 reduer 的个数，使⽤ Hadoop Client 的配置。
1hive.mapred.modeMap/Redure 模式，如果设置为 strict，将禁⽌3中类型的查询：1.分区表的where筛选条件必须含有分区字段；2.对使⽤了order by语句的查询，必须使⽤limit语句(order by语句为执⾏排序会将所有的结果集数据分发到同⼀个reducer中进⾏处理，增加limit语句可以防⽌reducer额外执⾏很长时间)3.限制笛卡⼉积的查询，就是有where语句，⽽没有on语句。
'nonstrict'hive.merge.mapfiles在Map-only的任务结束时合并⼩⽂件是否开启合并 Map 端⼩⽂件，当Hive输⼊由很多个⼩⽂件组成，由于每个⼩⽂件都会启动⼀个map任务，如果⽂件过⼩，会使得map任务启动和初始化的时间⼤于逻辑处理的时间，造成资源浪费，甚⾄OOM。
为此，当我们启动⼀个任务，发现输⼊数据量⼩但任务数量多时，需要注意在Map前端进⾏输⼊合并。
当然，在我们向⼀个表写数据时，也需要注意输出⽂件⼤⼩truehive.merge.mapredfiles是否开启合并 Map/Reduce ⼩⽂件，即是否在Map-Reduce的任务结束时合并⼩⽂件falsehive.exec.parallel是否开启 map/reduce job的并发提交。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
22
Hive调优 – EXPLAIN
• EXPLAIN [EXTENDED | DEPENDENCY]
在使用查询语句前，可以使用EXPLAIN关键字查看HIVE将为某个查询使用多 个MapReduce作业。 EXPLAIN的输出中有很多查询执行计划的详细信息，包 括抽象语法树、Hive执行各阶段之间的依赖图以及每个阶段的信息。如果要 查看更详细的信息，可以在查询时加上EXTENDED。 DEPENDENCY 以json格式输出执行语句会读取的input table和input partition信 息，这样debug语句会读取哪些表就很方便了。
数据库（database）
表（table）
表（table）
分区 桶 桶 分区 倾斜数据 正常数据
桶
桶
18
Hive调优 - 存储格式
• Built-in Formats:
– Parquet – ORCFile – RCFile – Avro – Delimited Text – Regular Expression – S3 Logfile – Typed Bytes
3
Hive的架构简介 – 在Hadoop生态圈的位置
建立在Hive之上的交换层
让传统DBA或者Java工程师 轻松就能完成更多的工作
最终转化成MapReduce Job
4
Hive的架构简介 – 接口
5
Hive的特性 – SQL支持
Sub-queries for IN/NOT IN,Union All
23
Hive调优 – 磁盘问题
• 如果磁盘不是问题，增加副本数，找到平衡 增加副本数，会较大概率增加从本地获取数据。减少网络传输，加快运行。
CREATE TABLE logs (uid INT, ts BIGINT, line STRING) PARTITIONED BY (dt STRING, country STRING) CLUSTERED BY(uid) SORTED BY (uid ASC) INTO 32 BUCKETS; LOAD DATA LOCAL INPATH 'input/hive/partitions/file1' INTO TABLE logs PARTITION (dt='2001-01-01', country='GB'); /user/hive/warehouse/logs/dt=2010-01-01/country=GB/file1 /file2 /country=US/file3 /dt=2010-01-02/country=GB/file4 /country=US/file5 /file6 hive> SHOW PARTITIONS logs; SELECT ts, dt, line dt=2001-01-01/country=GB FROM logs dt=2001-01-01/country=US dt=2001-01-02/country=GB WHERE country='GB'; dt=2001-01-02/country=US Note: • 使用bucket，需要设置 hive.enforce.bucketing属性为true • 如果桶排序，还需要设置 hive.enforce .sorting属性为true
6
第一章
第二章 第三章 第四章
Hive 是什么
Hive 基本操作
怎么用Hive
Hive的调优及发展
7
Hive基本操作 – DDL
• • • • • • • • Create/Drop/Alter Database Create/Drop/Truncate Table Alter Table/Partition/Column Create/Drop/Alter View Create/Drop Index Create/Drop Function Show Describe
20
Hive调优 – 参数
• • • • • • • 以下常用设置 set hive.optimize.mapjoin.mapreduce=true; set hive.optimize.bucketmapjoin=true; set hive.optimize.bucketmapjoin.sortedmerge=true; set hive.auto.convert.join=true; set hive.auto.convert.sortmerge.join=true; set set hive.auto.convert.sortmerge.join.noconditionaltask=true; set hive.fetch.task.conversion=more; //开启Fetch任务，对于上述简单的列查询不在 启用MapReduce job。默认是minimal 当使用bucket数据时 • set hive.enforce.bucketing=true; • set hive.enforce.sorting=true; • set hive.optimize.bucketmapjoin=true; 需要注意 • io.sort.mb Check Your Settings ! • 更多 …
10
Hivቤተ መጻሕፍቲ ባይዱ基本操作 – DML
• Loading files into tables • Inserting data into Hive Tables from queries • Writing data into the filesystem from queries
Note：Multiple Insert
• 数据准备
– 定义数据仓库放置目录： HDFS目录 /user/cloudil/cdr/bssap/20120717/ bssap - 协议类型 20120717 - 年月日 (开始时间) 以bssap为例，自行管理仓库数据，按天查询创建分区； – 建表（支持按时间分区的外部表） – 加载数据 LOAD DATA INPATH '/user/cloudil/bssap-2012-08-05-09/' INTO TABLE bssap PARTITION (date='20120805');
1
第一章
第二章 第三章 第四章
Hive 是什么
Hive 特性
怎么用Hive
Hive的调优及发展
2
Hive 是什么？
•Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文 件映射为一张数据库表，并提供类SQL查询功能。 •本质是将HQL转换为MapReduce程序 Hive关注以下几点： • 在Hadoop中的数据可扩展的SQL处理 • 可扩展到100PB+ • 结构化和非结构化数据
• Map join
该查询Job没有reducer； 使用时充分利用Bucketed Table，需要设置hive.optimize.bucketmapjoin为true;
13
第一章
第二章 第三章 第四章
Hive 是什么
Hive 基本操作
怎么用Hive
Hive的调优及发展
14
Hive 使用 – 以Mc为例
如何选择？
托管表 CREATE/LOAD DROP
把数据已到仓库目录 元数据和数据会被一起删除
外部表
创建表时指明外部数据的位置 只删除元数据
9
Hive基本操作 – Partition And Bucket
• • Hive把表组织成“分区”。这是一种根据“分区列”的值对表进行粗略划分 的机制。使用分区可以加快数据分片的查询速度 表或分区可以进一步分为“桶”，会为数据提供额外的结构获取更高的查询 处理
12
Hive基本操作 – Queries Join
• Inner join
Hive只支持等值连接； JOIN 子句中表的顺序很重要，一般最好将最大的表在最后;
• Outer join
外连接可以让你找到连接表中不能匹配的数据行;
慎重使用！ 弄懂再用
• Semi join
目前并不支持IN子查询，可以使用LEFT SEMI JOIN达到相同效果 （右表最能在ON子句 中出现）;
• 3 rd -Party Addons:
– JSON – XML
19
Hive调优 – 索引
• 索引是标准的数据库技术，hive 0.7版本之后支持索引。Hive 提供有限的索引功能，这不像传统的关系型数据库那样有 “键(key)”的概念，用户可以在某些列上创建索引来加速某些 操作，给一个表创建的索引数据被保存在另外的表中。 Hive 的索引功能现在还相对较晚，提供的选项还较少。但是，索 引被设计为可使用内置的可插拔的java代码来定制，用户可以 扩展这个功能来满足自己的需求。 当然不是说有的查询都会 受惠于Hive索引。用户可以使用EXPLAIN语法来分析HiveQL语 句是否可以使用索引来提升用户查询的性能。像RDBMS中的 索引一样，需要评估索引创建的是否合理，毕竟，索引需要 更多的磁盘空间，并且创建维护索引也会有一定的代价。 用 户必须要权衡从索引得到的好处和代价。
8
Hive基本操作 – 托管表和外部表
Hive 默认创建Managed Table，由Hive来管理数据，意味着Hive会将数据移动到数 据仓库目录。 另外一种选择是创建External Table，这时Hive会到仓库目录以外的位置访问数据。
• 如果所有处理都由Hive完成，应该使用Managed Table。 • 如果要用Hive和其它工具来处理同一个数据集， 应该使用External Tables。
WHERE Clause ALL and DISTINCT Clauses Partition Based Queries HAVING Clause LIMIT Clause REGEX Column Specification