网站页面的滑动怎么做的,初爱视频教程完整版免费观看,dede网站模版,北京建筑大学研究生招生网电气总结了一下在以往工作中#xff0c;对于Hive SQL调优的一些实际应用#xff0c;是日常积累的一些优化技巧#xff0c;如有出入#xff0c;欢迎在评论区留言探讨~
EXPLAIN 查看执行计划
建表优化
分区
分区表基本操作#xff0c;partitioned二级分区动态分区
分桶
分…总结了一下在以往工作中对于Hive SQL调优的一些实际应用是日常积累的一些优化技巧如有出入欢迎在评论区留言探讨~
EXPLAIN 查看执行计划
建表优化
分区
分区表基本操作partitioned二级分区动态分区
分桶
分桶表基本操作clustered分桶表主要是抽样查询找出具有代表性的结果
选择合适的文件格式和压缩格式
LZO拉兹罗Snappy压缩速度快压缩比高
HiveSQL语法优化
单表查询优化 列裁剪和分区裁剪全表和全列扫描效率都很差生产环境绝对不要使用SELECT *所谓列裁剪就是在查询时只读取需要的列分区裁剪就是只读取需要的分区 与列裁剪优化相关的配置项是hive.optimize.cp默认是true与分区裁剪优化相关的则是hive.optimize.pruner默认是true在HiveSQL解析阶段对应的则是ColumnPruner逻辑优化器 Group By 配置调整map阶段会把同一个key发给一个reduce当一个key过大时就倾斜了可以开启map端预聚合可以有效减少shuffle数据量并 # 是否在map端聚合默认为true
set hive.map.aggr true;# 在map端聚合的条数
set hive.groupby.mapaggr.checkintervel 100000;# 在数据倾斜的时候进行均衡负载默认是false开启后会有 两个mr任务。
# 当选项设定为true时第一个 mr任务 会将map输出的结果随机分配到reduce
# 每个reduce会随机分布到reduce上这样的处理结果是会使相同的group by key分到不同的reduce上。
# 第二个 mr任务 再根据预处理的结果按group by key分到reduce上
# 保证相同group by key的数据分到同一个reduce上。# *切记*
# 这样能解决数据倾斜但是不能让运行速度更快
# 在数据量小的时候开始数据倾斜负载均衡可能反而会导致时间变长
# 配置项毕竟是死的单纯靠它有时不能根本上解决问题
# 因此还是建议自行了解数据倾斜的细节并优化查询语句
set hive.groupby.skewindata true;Vectorization矢量计算技术通过设置批处理的增量大小为1024行单次来达到比单行单次更好的效率 # 开启矢量计算
set hive.vectorized.execution.enabled true;# 在reduce阶段开始矢量计算
set hive.vectorized.execution.reduce.enabled true;多重模式一次读取多次插入同一张表的插入操作优化成先from table再insert in/exists或者join用left semi join代替为什么替代扩展一下~
多表查询优化 CBO优化成本优化器代价最小的执行计划就是最好的执行计划 join的时候表的顺序关系前面的表都会被加载到内存中后面的表进行磁盘扫描通过hive.cbo.enable自动优化hivesql中多个join的执行顺序可以通过查询一下参数这些一般都是true无需修改 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;谓词下推非常关键的一个优化将sql语句中的where谓词逻辑都尽可能提前执行减少下游处理的数据量 在关系型数据库如MySQL中也有谓词下推Predicate PushdownPPD的概念 它就是将sql语句中的where谓词逻辑都尽可能提前执行减少下游处理的数据量 # 这个设置是默认开启的
# 如果关闭了但是cbo开启那么关闭依然不会生效
# 因为cbo会自动使用更为高级的优化计划
# 与它对应的逻辑优化器是PredicatePushDown
# 该优化器就是将OperatorTree中的FilterOperator向上提
set hive.optimize.pdd true;# 举个例子
# 对forum_topic做过滤的where语句写在子查询内部而不是外部
select a.uid,a.event_type,b.topic_id,b.title
from calendar_record_log a
left outer join (select uid,topic_id,title from forum_topicwhere pt_date 20220108 and length(content) 100
) b on a.uid b.uid
where a.pt_date 20220108 and status 0;Map Joinmap join是指将join操作两方中比较小的表直接分发到各个map进程的内存中在map中进行join的操作。 map join特别适合大小表join的情况Hive会将build table和probe table在map端直接完成join过程消灭了reduce减少shuffle所以会减少开销 set hive.auto.convert.join true配置开启默认是true注意 如果执行小表join大表小表作为主连接的主表所有数据都要写出去此时会走reduce阶段mapjoin会失效大表join小表不受影响上一条的原因主要是因为小表join大表的时候map阶段不知道reduce的结果其他reduce是否有所以必须在最后reduce聚合的时候再处理就产生了reduce的开销 # 举个例子
# 在最常见的hash join方法中一般总有一张相对小的表和一张相对大的表
# 小表叫build table大表叫probe table
# Hive在解析带join的SQL语句时会默认将最后一个表作为probe table
# 将前面的表作为build table并试图将它们读进内存
# 如果表顺序写反probe table在前面引发OOM内存不足的风险就高了
# 在维度建模数据仓库中事实表就是probe table维度表就是build table
# 假设现在要将日历记录事实表和记录项编码维度表来join
select a.event_type,a.event_code,a.event_desc,b.upload_time
from calendar_event_code a
inner join (select event_type,upload_time from calendar_record_logwhere pt_date 20220108
) b on a.event_type b.event_type;Map Join大表和大表的MapReduce任务可以使用SMB Join 直接join耗时会很长但是根据某字段分桶后两个大表每一个桶就是一个小文件两个表的每个小文件的分桶字段都应该能够一一对应hash值取模的结果总结就是分而治之注意两个大表的分桶字段和数量都应该保持一致 set hive.optimize.bucketmapjoin true;
set hive.optimeize.bucketmapjoin.sortedmerge true;
hive.input.format org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;多表join时key相同这种情况会将多个join合并为一个mr 任务来处理 # 举个例子
# 如果下面两个join的条件不相同
# 比如改成a.event_code c.event_code
# 就会拆成两个MR job计算
select a.event_type,a.event_code,a.event_desc,b.upload_time
from calendar_event_code a
inner join (select event_type,upload_time from calendar_record_logwhere pt_date 20220108
) b on a.event_type b.event_type
inner join (select event_type,upload_time from calendar_record_log_2where pt_date 20220108
) c on a.event_type c.event_type;笛卡尔积在生产环境中严禁使用
其他查询优化 Sort By 代替 Order ByHiveQL中的order by与其他sql方言中的功能一样就是将结果按某字段全局排序这会导致所有map端数据都进入一个reducer中 在数据量大时可能会长时间计算不完。如果使用sort by那么还是会视情况启动多个reducer进行排序并且保证每个reducer内局部有序。 为了控制map端数据分配到reducer的key往往还要配合distribute by一同使用如果不加distribute by的话map端数据就会随机分配到reducer # 举个例子
select uid,upload_time,event_type,record_data
from calendar_record_log
where pt_date 20220108 and pt_date 20220131
distribute by uid
sort by upload_time desc,event_type desc;Group By代替Distinct当要统计某一列的去重数时如果数据量很大count(distinct)就会非常慢原因与order by类似 count(distinct)逻辑只会有很少的reducer来处理。但是这样写会启动两个mr任务单纯distinct只会启动一个 所以要确保数据量大到启动mr任务的overhead远小于计算耗时才考虑这种方法当数据集很小或者key的倾斜比较明显时group by还可能会比distinct慢
数据倾斜
注意要和数据过量的情况区分开数据倾斜是大部分任务都已经执行完毕但是某一个任务或者少数几个任务一直未能完成甚至执行失败 而数据过量是大部分任务都执行的很慢这种情况需要通过扩充执行资源的方式来加快速度大数据编程不怕数据量大就怕数据倾斜一旦数据倾斜严重影响效率
单表携带了 Group By 字段的查询
任务中存在group by操作同时聚合函数为count或sum单个key导致的数据倾斜可以这样通过设置开启map端预聚合参数的方式来处理# 是否在map端聚合默认为true
set hive.map.aggr true;# 在map端聚合的条数
set hive.groupby.mapaggr.checkintervel 100000;# 有数据倾斜的时候开启负载均衡这样会生成两个mr任务
set hive.groupby.skewindata true;任务中存在group by操作同时聚合函数为count或sum多个key导致的数据倾斜可以通过增加reduce的数量来处理 增加分区可以减少不同分区之间的数据量差距而且增加的分区时候不能是之前分区数量的倍数不然会导致取模结果相同继续分在相同分区第一种修改方式 # 每个reduce处理的数量
set hive.exec.reduce.bytes.per.reducer 256000000;# 每个任务最大的reduce数量
set hive.exec.reducers.max 1009;# 计算reducer数的公式根据任务的需要调整每个任务最大的reduce数量
N min设置的最大数总数量数/每个reduce处理的数量第二种修改方式 # 在hadoop的mapred-default.xml文件中修改
set mapreduce.job.reduces 15;两表或多表的 join 关联时其中一个表较小但是 key 集中
设置参数增加map数量# join的key对应记录条数超过该数量会进行分拆
set hive.skewjoin.key 1000;# 并设置该参数为true默认是false
set hive.optimize.skewjoin true;# 上面的参数如果开启了会将计算数量超过阈值的key写进临时文件再启动另外一个任务做map join
# 可以通过设置这个参数控制第二个任务的mapper数量默认10000
set hive.skewjoin.mapjoin.map.tasks 10000;使用mapjoin减少reduce从根本上解决数据倾斜参考HiveSQL语法优化 - 多表查询优化 - Map Join大表和大表的MapReduce任务SMB Join
两表或多表的 join 关联时有 Null值 或 无意义值
这种情况很常见比如当事实表是日志类数据时往往会有一些项没有记录到我们视情况会将它置为null或者空字符串、-1等 如果缺失的项很多在做join时这些空值就会非常集中拖累进度因此若不需要空值数据就提前写where语句过滤掉 需要保留的话将空值key用随机方式打散例如将用户ID为null的记录随机改为负值
select a.uid,a.event_type,b.nickname,b.age
from (select (case when uid is null then cast(rand()*-10240 as int) else uid end) as uid,event_type from calendar_record_logwhere pt_date 20220108
) a left outer join (select uid,nickname,age from user_info where status 4
) b on a.uid b.uid;两表或多表的 join 关联时数据类型不统一
比如int类型和string类型进行关联关联时候以小类型作为分区这里int、string会到一个reduceTask中如果数据量多会造成数据倾斜
# 可以通过转换为同一的类型来处理
cast(user.id as string)单独处理倾斜key
这其实是上面处理空值方法的拓展不过倾斜的key变成了有意义的一般来讲倾斜的key都很少我们可以将它们抽样出来 对应的行单独存入临时表中然后打上一个较小的随机数前缀比如0~9最后再进行聚合
Hive Job 优化
Hive Map 优化
Map数量多少的影响
Map数过大 map阶段输出文件太小产生大量小文件初始化和创建map的开销很大 Map数太小 文件处理或查询并发度小Job执行时间过长大量作业时容易堵塞集群
控制Map数的原则
根据实际情况控制map数量需要遵循两个原则
第一是使大数据量利用合适的map数第二是使单个map任务处理合适的数据量
复杂文件适当增加Map数
当input的文件都很大任务逻辑复杂map执行非常慢的时候可以考虑增加map数来使得每个map处理的数据量减少从而提高任务的执行效率那么如何增加map的数量呢在map阶段文件先被切分成split块而后每一个split切片对应一个Mapper任务 FileInputFormat这个类先对输入文件进行逻辑上的划分以128m为单位将原始数据从逻辑上分割成若干个split每个split切片对应一个mapper任务 所以说减少切片的大小就可增加map数量可以依据公式计算computeSliteSize(Math.max(minSize, Math.min(maxSize, blockSize))) blockSize 128m执行语句set mapreduce.input.fileinputformat.split.maxsize 100;
小文件进行合并减少Map数
为什么要进行小文件合并因为如果一个任务有很多小文件远远小于块大小128m则每个小文件也会被当做一个块用一个map任务来完成 而一个map任务启动和初始化的时间远远大于逻辑处理的时间就会造成很大的资源浪费同时可执行的map数是受限的 两种方式合并小文件
在Map执行前合并小文件减少map数量// 每个Map最大输入大小(这个值决定了合并后文件的数量)
set mapred.max.split.size 256000000;// 一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)
set mapred.min.split.size.per.node 100000000;// 一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)
set mapred.min.split.size.per.rack 100000000;// 执行Map前进行小文件合并
set hive.input.format org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;在Map-Reduce任务执行结束时合并小文件减少小文件输出// 设置map端输出进行合并默认为true
set hive.merge.mapfiles true;// 设置reduce端输出进行合并默认为false
set hive.merge.mapredfiles true;// 设置合并文件的大小默认是256
set hive.merge.size.per.task 256 * 1000 * 1000;// 当输出文件的平均大小小于该值时启动一个独立的MapReduce任务进行文件merge。
set hive.merge.smallfiles.avgsize 16000000;Map端预聚合减少Map数量
相当于在map端执行combiner执行命令set hive.map.aggr true;combiners是对map端的数据进行适当的聚合其好处是减少了从map端到reduce端的数据传输量其作用的本质是将map计算的结果进行二次聚合使Key-ValueList中List的数据量变小从而达到减少数据量的目的
推测执行
在分布式集群环境下因为程序Bug包括Hadoop本身的bug负载不均衡或者资源分布不均等原因会造成同一个作业的多个任务之间运行速度不一致 有些任务的运行速度可能明显慢于其他任务比如一个作业的某个任务进度只有50%而其他所有任务已经运行完毕则这些任务会拖慢作业的整体执行进度Hadoop采用了推测执行Speculative Execution机制它根据一定的法则推测出拖后腿的任务并为这样的任务启动一个备份任务 让该任务与原始任务同时处理同一份数据并最终选用最先成功运行完成任务的计算结果作为最终结果执行命令set mapred.reduce.tasks.speculative.execution true; # 默认是true当然如果用户对于运行时的偏差非常敏感的话那么可以将这些功能关闭掉如果用户因为输入数据量很大而需要执行长时间的map task或者reduce task的话 那么启动推测执行造成的浪费是非常巨大的
合理控制Map数量的实际案例
假设一个SQL任务
SELECT COUNT(1)
FROM fx67ll_alarm_count_copy
WHERE alarm_date 2021-01-08;该任务的输入目录inputdir是/group/fx67ll_data/fx67ll_data_etl/date/fx67ll_alarm_count_copy/alarm_date2021-01-08共有194个文件 其中很多是远远小于128m的小文件总大小约9G正常执行会用194个Map任务map总共消耗的计算资源SLOTS_MILLIS_MAPS 610,023 通过在Map执行前合并小文件减少Map数
# 前面三个参数确定合并文件块的大小
# 大于文件块大小128m的按照128m来分隔
# 小于128m,大于100m的按照100m来分隔
# 把那些小于100m的包括小文件和分隔大文件剩下的进行合并最终生成了74个块
set mapred.max.split.size100000000;
set mapred.min.split.size.per.node100000000;
set mapred.min.split.size.per.rack100000000;
set hive.input.formatorg.apache.hadoop.hive.ql.io.CombineHiveInputFormat;合并后用了74个map任务map消耗的计算资源SLOTS_MILLIS_MAPS 323,098对于这个简单SQL任务执行时间上可能差不多但节省了一半的计算资源
再假设这样一个SQL任务
SELECT data_fx67ll,
COUNT(1),
COUNT(DISTINCT id),
SUM(CASE WHEN …),
SUM(CASE WHEN …),
SUM(…)
FROM fx67ll_device_info_zs
GROUP data_fx67ll如果表fx67ll_device_info_zs只有一个文件大小为120m但包含几千万的记录如果用1个map去完成这个任务肯定是比较耗时的 这种情况下我们要考虑将这一个文件合理的拆分成多个 增加Reduce数量来增加Map数量
set mapred.reduce.tasks10;
CREATE TABLE fx67ll_device_info_zs_temp
AS
SELECT *
FROM fx67ll_device_info_zs
DISTRIBUTE BY RAND(123);这样会将fx67ll_device_info_zs表的记录随机的分散到包含10个文件的fx67ll_device_info_zs_temp表中 再用fx67ll_device_info_zs_temp代替上面sql中的fx67ll_device_info_zs表 则会用10个map任务去完成每个map任务处理大于12m几百万记录的数据效率肯定会好很多
Hive Reduce 优化
Reduce数量多少的影响
同map一样启动和初始化reduce也会消耗时间和资源另外有多少个reduce就会有多少个输出文件如果生成了很多个小文件那么如果这些小文件作为下一个任务的输入则也会出现小文件过多的问题
控制Reduce数的原则
和map一样控制reduce数量需要遵循两个原则
第一是使大数据量利用合适的reduce数第二是使单个reduce任务处理合适的数据量
Hive自己如何确定Reduce数
reduce个数的设定极大影响任务执行效率不指定reduce个数的情况下Hive会猜测确定一个reduce个数基于以下两个设定
# 每个reduce任务处理的数据量默认为 1000^31G
hive.exec.reducers.bytes.per.reducer# 每个任务最大的reduce数默认为999
hive.exec.reducers.max计算reducer数的公式很简单N min(参数2总输入数据量 / 参数1) 即如果reduce的输入map的输出总大小不超过1G那么只会有一个reduce任务
举个例子
SELECT alarm_date,COUNT(1)
FROM fx67ll_alarm_count_copy
WHERE alarm_date 2021-01-08
GROUP BY alarm_date;该任务的输入目录inputdir是/group/fx67ll_data/fx67ll_data_etl/date/fx67ll_alarm_count_copy/alarm_date2021-01-08 总大小为9G多因此这句有10个reduce
如何调整Reduce数量
注意实际开发中reduce的个数一般通过程序自动推定而不人为干涉因为人为控制的话如果使用不当很容易造成结果不准确且降低执行效率
通过调整每个reduce任务处理的数据量来调整reduce个数处理的数据量少了任务数就多了# 设置每个reduce任务处理的数据量500M默认是1G
set hive.exec.reducers.bytes.per.reducer 500000000;SELECT alarm_date,COUNT(1)
FROM fx67ll_alarm_count_copy
WHERE alarm_date 2021-01-08
GROUP BY alarm_date;这次有20个reduce直接调整每个Job中的最大reduce数量过于简单粗暴慎用尽量不要虽然设置了reduce的个数看起来好像执行速度变快了但是实际并不是这样的# 设置每个任务最大的reduce数为15个默认为999
set mapred.reduce.tasks 15;SELECT alarm_date,COUNT(1)
FROM fx67ll_alarm_count_copy
WHERE alarm_date 2021-01-08
GROUP BY alarm_date;这次有15个reduce推测执行
参考map优化的最后一项
什么情况下只有一个Reduce
很多时候你会发现任务中不管数据量多大不管你有没有设置调整reduce个数的参数任务中一直都只有一个reduce任务 其实只有一个reduce任务的情况除了数据量小于hive.exec.reducers.bytes.per.reducer参数值的情况外还有以下原因
没有Group By的汇总例如SELECT alarm_date,COUNT(1)
FROM fx67ll_alarm_count_copy
WHERE alarm_date 2021-01-08
GROUP BY alarm_date;写成SELECT COUNT(1)
FROM fx67ll_alarm_count_copy
WHERE alarm_date 2021-01-08;注意避免这样情况的发生用了Order by排序因为它会对数据进行全局排序所以数据量特别大的时候效率非常低尽量避免有笛卡尔积生产环境必须严格避免
Hive 任务整体优化
Fetch抓取
Fetch抓取是指Hive在某些情况的查询可以不必使用mr 任务例如在执行一个简单的select * from XX时我们只需要简单的进行抓取对应目录下的数据即可。 在hive-default.xml.template中hive.fetch.task.conversion默认是morn老版本中默认是minimal。 该属性为morn时在全局查找字段查找limit查找等都不走mr 任务
本地模式
Hive也可以不将任务提交到集群进行运算而是直接在一台节点上处理因为消除了提交到集群的overhead所以比较适合数据量很小且逻辑不复杂的任务。 设置hive.exec.mode.local.auto为true可以开启本地模式但任务的输入数据总量必须小于hive.exec.mode.local.auto.inputbytes.max默认值128MB 且mapper数必须小于hive.exec.mode.local.auto.tasks.max默认值4reducer数必须为0或1才会真正用本地模式执行
并行执行
Hive中互相没有依赖关系的job间是可以并行执行的最典型的就是多个子查询union all 在集群资源相对充足的情况下可以开启并行执行即将参数hive.exec.parallel设为true 另外hive.exec.parallel.thread.number可以设定并行执行的线程数默认为8一般都够用。 注意没资源无法并行且数据量小时开启可能还没不开启快所以建议数据量大时开启
严格模式
要开启严格模式需要将参数hive.mapred.mode设为strict。 所谓严格模式就是强制不允许用户执行3种有风险的sql语句一旦执行会直接失败这3种语句是
查询分区表时不限定分区列的语句两表join产生了笛卡尔积的语句用order by来排序但没有指定limit的语句
JVM重用
主要用于处理小文件过多的时候在mr 任务中默认是每执行一个task就启动一个JVM如果task非常小而碎那么JVM启动和关闭的耗时就会很长可以通过调节参数mapred.job.reuse.jvm.num.tasks来重用例如将这个参数设成5那么就代表同一个mr 任务中顺序执行的5个task可以重复使用一个JVM减少启动和关闭的开销但它对不同mr 任务中的task无效
启用压缩
压缩job的中间结果数据和输出数据可以用少量CPU时间节省很多空间压缩方式一般选择Snappy效率最高。 要启用中间压缩需要设定hive.exec.compress.intermediate为true 同时指定压缩方式hive.intermediate.compression.codec为org.apache.hadoop.io.compress.SnappyCodec。 另外参数hive.intermediate.compression.type可以选择对块BLOCK还是记录RECORD压缩BLOCK的压缩率比较高。 输出压缩的配置基本相同打开hive.exec.compress.output即可
采用合适的存储格式
在Hive SQL的create table语句中可以使用stored as ...指定表的存储格式。 Hive表支持的存储格式有TextFile、SequenceFile、RCFile、Avro、ORC、Parquet等。 存储格式一般需要根据业务进行选择在我们的实操中绝大多数表都采用TextFile与Parquet两种存储格式之一。TextFile是最简单的存储格式它是纯文本记录也是Hive的默认格式虽然它的磁盘开销比较大查询效率也低但它更多地是作为跳板来使用。RCFile、ORC、Parquet等格式的表都不能由文件直接导入数据必须由TextFile来做中转。Parquet和ORC都是Apache旗下的开源列式存储格式。列式存储比起传统的行式存储更适合批量OLAP查询并且也支持更好的压缩和编码。我们选择Parquet的原因主要是它支持Impala查询引擎并且我们对update、delete和事务性操作需求很低。
Hive的小文件
什么情况下会产生小文件?
动态分区插入数据产生大量的小文件从而导致map数量剧增reduce数量越多小文件也越多有多少个reduce就会有多少个输出文件如果生成了很多小文件那这些小文件作为下一次任务的输入数据源本身就包含大量的小文件
小文件有什么样的危害
从Hive的角度看小文件会开很多map一个map开一个java虚拟机jvm去执行所以这些任务的初始化启动执行会浪费大量的资源严重影响性能在hdfs中每个小文件对象约占150byte如果小文件过多会占用大量内存这样NameNode内存容量严重制约了集群的扩展 每个hdfs上的文件会消耗128字节记录其meta信息所以大量小文件会占用大量内存
如何避免小文件带来的危害
从小文件产生的途经就可以从源头上控制小文件数量
使用Sequencefile作为表存储格式不要用textfile在一定程度上可以减少小文件减少reduce的数量(可以使用参数进行控制)少用动态分区用时记得按distribute by分区
对于已有的小文件
使用hadoop archive命令把小文件进行归档采用archive命令不会减少文件存储大小只会压缩NameNode的空间使用重建表建表时减少reduce数量
我是 fx67ll.com如果您发现本文有什么错误欢迎在评论区讨论指正感谢您的阅读 如果您喜欢这篇文章欢迎访问我的 本文github仓库地址为我点一颗StarThanks~ 转发请注明参考文章地址非常感谢
