HIVE优化
1.分区分桶
分区:
如果查询表为分区表，那么根据业务逻辑，需要选择其中的分区字段进行做where条件过滤
分桶：
如果查询表为分桶表，根据分桶字段做过滤
原因： 由于分区表中的数据相对来说较大，在查询时，为了避免全表扫描，对其中的部分数据进行加载，那么可以通过加载指定分区或分桶中的数据，进行查询
分区字段选择：
-
如果数据中有时间字段，那么通常按照数据量将数据按天或按月进行分区保存
-
根据业务逻辑，取经常过滤的字段作为分区字段
-
要求过滤字段能够将数据均匀划分
2.使用外部表
作用：
外部表在删除时，只删除其元数据信息，而不会删除HDFS中的数据，可以保证数据的安全性
3.选择适当的文件压缩格式
两种比较常用的文件格式：
① TextFile并不是压缩格式，通常被应用在hive中保存从源数据采集过来的数据
②ORCFile 压缩率较高，并且其读写速度较快，拥有良好的性能
4.命名要规范
HIVE中的建库建表规则
①库：通常公司中的库都是限定好，不需要自己构建，通过名称有 ods dwd dws dim dwa app
②建表：库名_
2.1 表明当前表所在库
2.2 表明当前表应用在哪些应用系统中（项目）
2.3 表明当前的计算逻辑，如：计算周期 dd mm yy
5.数据分层，表分离，但是也不要分的太散
数据分层:
数据要根据来源、性质、使用方式，存放在不同的数据库中保存
表分离：
将数据中冗余字段进行分割，保存至多个表中，后续使用时，可以通过表关联方式使用
但是也不要分的太散：
如果表中的数据过于分散，那么后续在做数据处理时，就会产生大量的网络IO,降低数据处理性能
6.分区裁剪 where过滤，先过滤，后join
分区裁剪
针对分区表中的数据，需要对其添加where过滤，避免全表扫描
where过滤，先过滤，后join
行过滤：针对多个表中数据做关联，需要先对部分表进行做where条件过滤
列过滤：查询过程中避免使用*取全表字段
7.mapJoin
mapJoin作用：当大表和小表进行做关联时，将小表加载至内存中保存，再和大表进行关联
优化方式：
1）设置自动选择Mapjoin set hive.auto.convert.join = true; 默认为true
2）大表小表的阈值设置（默认25M以下认为是小表）： set hive.mapjoin.smalltable.filesize = 25000000;
实验：
create table total_score(
student_id string,
score int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
load data local inpath '/usr/local/soft/data/totalscore.txt' into table total_score;
create table student_score AS
SELECT
T1.*
,T2.score
FROM learn1.student1 T1 JOIN learn5.total_score T2
-- 在hive默认开启mapjoin情况下，大表和小表关联时，不会产生reduce过程
-- 关闭mapjoin
set hive.auto.convert.join=false;
create table student_score AS
SELECT
T1.*
,T2.score
FROM learn1.student1 T1 JOIN learn5.total_score T2
-- 关闭mapjoin之后，会产生reduce过程使用场景：
当有大小表关联时，其中小表数据量比25M相差不大，可以通过调整大表小表的阈值来启动mapJoin，但是前提要求集群中内存资源充足
扩充知识点：
当SQL执行前，想查询其执行计划可以使用explain命令查看
explain SELECT T1.* ,T2.score FROM learn1.student1 T1 JOIN learn5.total_score T2
explain extended SELECT T1.* ,T2.score FROM learn1.student1 T1 JOIN learn5.total_score T2
8.合并小文件
1）在map执行前合并小文件，减少map数：CombineHiveInputFormat具有对小文件进行合并的功能（系统默认的格式）。HiveInputFormat没有对小文件合并功能。 --将多个小文件打包作为一个整体的inputsplit，减少map任务数 set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; set mapreduce.input.fileinputformat.split.maxsize=128000000; --切片大小最大值，不设置,则所有输入只启动一个map任务 set mapreduce.input.fileinputformat.split.minsize.per.node=16000000; --同一节点的数据块形成切片时，切片大小的最小值
2）在Map-Reduce的任务结束时合并小文件的设置： 在map-only任务结束时合并小文件，默认true SET hive.merge.mapfiles = true; 在map-reduce任务结束时合并小文件，默认false SET hive.merge.mapredfiles = true; 合并文件的大小，默认256M SET hive.merge.size.per.task = 268435456; 当输出文件的平均大小小于该值时，启动一个独立的map-reduce任务进行文件merge SET hive.merge.smallfiles.avgsize = 128000000;
通用做法：如果表中存在小文件，那么可以通过简单查询，将原先表中的数据进行覆盖，达到合并小文件的效果
9.排序优化
1.ORDER BY
当数据做排序时，为全局排序，使用的reduce数量只能为1个，在数据量较大情况下，会导致执行效率低
2.distribute by + sort by
当有多列数据进行做排序时，可以使用，会将数据先进行做分区，然后再对分区中的数据做排序，reduce数量可以不为1，提高排序效率
10.数据倾斜优化
什么是数据倾斜？
在MR执行过程中，由于相同Key的数据会被一个Reduce方法处理，当数据量较大时，reduce方法处理时间较长，或者因为计算资源不足导致结果出错
数据倾斜表现
--实验
CREATE EXTERNAL TABLE learn3.student_null(
id string,
name string,
age int,
gender string,
clazz string
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
load data local inpath "/usr/local/soft/data/student_null.txt" into table learn3.student_null;
CREATE EXTERNAL TABLE learn3.score_null(
id string,
obj string,
score int
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
load data local inpath "/usr/local/soft/data/score_null.txt" into table learn3.score_null;
-- 关联查询
CREATE TABLE learn3.score_student_null AS
SELECT
T1.*
,T2.score
FROM learn3.student_null T1 join learn3.score_null T2 ON T1.id = T2.id
-- 关闭mapjoin
set hive.auto.convert.join=false;
-- 设置reduce数量
set mapreduce.job.reduces=5;表现分为：
① 在执行日志上可以看出reduce一直处于90%左右
② 通过在Yarn平台查看Job执行，可以发现Reduce中，有部分reduce执行时间较长，说明其中处理数据量较大
数据倾斜原因：
① 数据中存在一些NULL值，并且根据这些NULL值做数据关联，产生笛卡尔积
② 数据在处理过程中，由于不恰当的处理方式，导致数据中的Key相同
数据倾斜解决方式：
① 根据业务逻辑，可以在Map端去除掉NULL值或者一些和计算结果不相关的Key
② 根据Key可以将Key进行添加随机数，之后再对数据做初步处理，之后再将处理结果再做一次聚合，得到最终结果
③通过参数进行调解
-- 方式一：
CREATE TABLE learn3.score_student_null_2 AS
SELECT
T1.*
,T2.score
FROM (SELECT * FROM learn3.student_null WHERE id != 'null_student' ) T1
join (SELECT * FROM learn3.score_null WHERE id != 'null_student') T2 ON T1.id = T2.id
-- 方式二：二次聚合
-- 由于关联字段为id，并且产生数据倾斜的Key为ID中的null_student
SELECT id,count(*) FROM learn3.student_null GROUP BY id
with gorup_tbl as(
SELECT
T1.rand_id
,count(*) as count_num
FROM (
SELECT concat(id,'_',floor(rand()*10)%5) as rand_id,name,age FROM learn3.student_null
) T1
GROUP BY rand_id
)
SELECT
substr(T2.rand_id,1,length(T2.rand_id)-2) as id
,sum(count_num) as total_num
FROM gorup_tbl T2
GROUP BY substr(T2.rand_id,1,length(T2.rand_id)-2)
-- select substr('null_student_3',1,length('null_student_3')-2)
-- 方式三
-- 开启map端的预聚合操作
set hive.map.aggr = true
set hive.groupby.mapaggr.checkinterval = 100000 --检查点条数
set hive.map.aggr.hash.min.reduction=0.5
-- 开启负载均衡
set hive.groupby.skewindata = true *
11.合理设置Reduce个数
在何时需要调整Reduce数量？
当数据处理过程中，Reduce过程相对比较长，那么可以考虑增加Reduce数量
合理设置Reduce数 1）调整reduce个数方法一，通过调整Reduce处理数据量实现 （1）每个Reduce处理的数据量默认是256MB hive.exec.reducers.bytes.per.reducer=256000000 （2）每个任务最大的reduce数，默认为1009 hive.exec.reducers.max=1009 （3）计算reducer数的公式 N=min(参数2，总输入数据量/参数1) 2）调整reduce个数方法二，手动给定固定reduce数量 在hadoop的mapred-default.xml文件中修改 设置每个job的Reduce个数 set mapreduce.job.reduces = 15; 3）reduce个数并不是越多越好 （1）过多的启动和初始化reduce也会消耗时间和资源； （2）另外，有多少个reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题；
12.设置严格模式
Hive可以通过设置防止一些危险操作： 1）分区表不使用分区过滤 将hive.strict.checks.no.partition.filter设置为true时，对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。 2）使用 order by 没有limit过滤 将hive.strict.checks.orderby.no.limit设置为true时，对于使用了order by语句的查询，要求必须使用limit语句。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。 3）笛卡尔积 将hive.strict.checks.cartesian.product设置为true时，会限制笛卡尔积的查询。对关系型数据库非常了解的用户可能期望在 执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。不幸的是，Hive并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况。
5298
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
