安徽省交通运输厅网站,ppt模板哪里可以免费下载,asp网站开发设计文档,wordpress相关文章小工具文章目录 一、Hive建表SQL二、Hive函数三、函数1、查看内置函数2、空字段赋值(nvl)3、CASE WHEN THEN ELSE END4、行转列5、列转行6、开窗函数6.1 简介6.2 语法6.3 案例6.4 LAG函数6.5 Ntile函数6.6 Rank 7、自定义函数 四、压缩和存储1、简介2、压缩简介3、Map输出阶段压缩4、… 文章目录 一、Hive建表SQL二、Hive函数三、函数1、查看内置函数2、空字段赋值(nvl)3、CASE WHEN THEN ELSE END4、行转列5、列转行6、开窗函数6.1 简介6.2 语法6.3 案例6.4 LAG函数6.5 Ntile函数6.6 Rank 7、自定义函数 四、压缩和存储1、简介2、压缩简介3、Map输出阶段压缩4、开启Reduce输出阶段压缩5、文件存储格式5.1 列式存储和行式存储5.2 TextFile_行存储5.3 Orc_列存储5.4 Parquet_列存储5.5 数据存储大小对比 6、存储和压缩结合 四、企业级调优1、查看执行计划2、Hive建表优化3、HQL语法优化3.1 列裁剪和分区裁剪3.2 Group By3.3 CBO优化 4、数据倾斜4.1 现象4.2 单表数据倾斜优化4.3 Join数据倾斜优化 5、Hive Job优化 一、Hive建表SQL
Hive建表SQL
二、Hive函数
Hive函数
三、函数
1、查看内置函数
查看系统自带函数
show functions;查看自带函数用法
# 显示简单用法
desc function !;
# 显示详细用法
desc function extended !;2、空字段赋值(nvl)
NVL给值为NULL的数据赋值它的格式是NVL( valuedefault_value)。功能如果value为NULL则NVL函数返回default_value的值否则返回value的值如果两个参数都为NULL 则返回NULL。
# 当common为空时返回age
SELECT nvl(name,age) FROM user;
# 当name为空时候用full_name替代
SELECT nvl(name,full_name) FROM user;3、CASE WHEN THEN ELSE END
根据不同数据返回不同逻辑
SELECTCASE deptnoWHEN 1 THEN EngineeringWHEN 2 THEN FinanceELSE adminEND,CASE zoneWHEN 7 THEN AmericasELSE Asia-PacENDFROM emp_details/** 当ab时返回c当ad时返回d当ae时放回e其他情况返回f。*/
CASE a WHEN b THEN cWHEN d THEN eELSE f
END案例
SELECT name,CASE sex WHEN 男 THEN 1 ELSE 0 END
FROM default.user01 uSELECT name,SUM(CASE sex WHEN 男THEN 1 ELSE 0 END )
FROM default.user01 u
GROUP BY name ;4、行转列
将多行数据进行汇总成1条数据、多行转为单行
CONCAT(string A/col, string B/col…)多个字段拼接CONCAT_WS(separator, str1, str2,...)多字段拼接(指定拼接符号)COLLECT_SET(col)接受分组数据汇总为Array去重COLLECT_LIST(col)接受分组数据汇总为Array不去重
SELECT deptno AS Deptno,CONCAT_WS(, , COLLECT_LIST(Ename)) AS enames
FROM user
GROUP BY deptnoCONCAT
# 字符串拼接
# 返回输入字符串连接后的结果支持任意个输入字符串;
CONCAT(string A/col, string B/col…)案例
SELECT CONCAT(name,age) FROM default.user01 u ;
# 结果
悟空12
大海14**CONCAT_WS(separator, str1, str2,...)**多字符串拼接 它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。 分隔符可以是与剩余参数一样的字符串。 如果分隔符是 NULL返回值也将为 NULL。 这个函数会跳过分隔符参数后的任何 NULL 和空字符串。 分隔符将被加到被连接的字符串之间; 注意: CONCAT_WS must be string or arraystring
# separator连接符
# str1参数1
# str2参数2
# 字符串拼接(CONCAT_WS(,a,b)ab)
CONCAT_WS(separator, str1, str2,...)案例
SELECT concat_ws(., www, facebook, com) FROM default.user01;
www.facebook.comSELECT concat_ws(., www, array(facebook, com)) FROM default.user01;
www.facebook.com
www.facebook.comCOLLECT_SET(col)
函数只接受基本数据类型它的主要作用是将某字段的值进行去重汇总产生array类型字段。去重
# 将分组后数据汇总为Array
collect_list(name)案例
# [宋宋,凤姐,婷婷]
select collect_set(name)from default.user01 group by age;
# 宋宋凤姐婷婷
select concat_ws(,collect_set(name))from default.user01 group by age;COLLECT_LIST(col):
不去重函数指接收基本数据类型它的主要作用是将某字段的值进行不去重汇总产生array类型字段。
# 将分组后数据汇总为Array
collect_list(name)案例
# [宋宋,宋宋,凤姐,婷婷]
select collect_list(name)from default.user01 group by age;
# 宋宋宋宋凤姐婷婷
select concat_ws(,collect_list(name))from default.user01 group by age;5、列转行
EXPLODE(col)将hive表的一列中复杂的array或者map结构拆分成多行。SPLIT(string str, string regex): 按照regex字符串分割str会返回分割后的字符串数组。LATERAL VIEW侧视图 LATERAL VIEW udtf(expression) tableAlias AS columnAlias用于和split, explode等UDTF一起使用它能够将一列数据拆成多行数据在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTFUDTF会报一行拆分成一行或者多行lateral view再把结果组合产生一个支持别名表的虚拟表。 SELECT split(category,,) FROM movie_info ;SELECT explode(split(category,,)) FROM movie_info mi;split将category列中数据拆分为多条以Array类型。explode将单列中Array存储的转为多行数据。lateral VIEW将Array中数据整合为可被查询的列。
SELECT movie,category_name FROM movie_info
lateral VIEW
explode(split(category,,)) movie_info_tmp AS category_name ;6、开窗函数
6.1 简介
窗口函数不同于我们熟悉的常规函数及聚合函数它输入多行数据一个窗口为每行数据进行一次计算返回一个值。灵活运用窗口函数可以解决很多复杂的问题如去重、排名、同比及和环比、连续登录等。 6.2 语法
语法 Functionarg1 …… over[partition by arg1 ……] [order by arg1 ……] [window_expression]rows必须跟在Order by 子句之后对排序的结果进行限制使用固定的行数来限制分区中的数据行数量 如果不指定partition by则不对数据进行分区换句话说所有数据看作同一个分区。 如果不指定order by 则不对各分区进行排序通常用于那些顺序无关的窗口函数如sum。 如果不指定窗口子句 不指定order by默认使用分区内所有行等同于 Function() over(rows between unbounded precedeing and unbounded following)如果指定order by默认使用分区内第起点到当前行等同于 Function() over(rows between unbounded preceding and current row)Over语法
语法解释(添加)解释(不添加)Partition by表示将数据先按字段进行分区不对数据进行分区换句话说所有数据看作同一个分区Order by表示将各个分区内的数据按字段进行排序。不指定默认所有行。则不对各分区进行排序通常用于那些顺序无关的窗口函数。指定后从开头行至当前行。
partition by
不指定则不对数据进行分区换句话说所有数据看作同一个分区。order by
不指定order by默认使用分区内所有行等同于
指定order by 默认使用分区内第起点到当前行等同于window_expression语法
语法解释n preceding往前n行n following往后n行current row当前行unbounded preceding从前面的起点开始unbounded following到后面的终点结束
# 从开头到当前行
ROWS BETWEEN unbounded preceding AND current row# 昨天至今天
rows between 1 preceding and current row# 当天到最后一天
rows between current row and unbounded following6.3 案例
数据表
查询在2017年4月份到店购买的人及到店次数
selectname ,count(name) over()
frombusiness
wheresubstring(orderdate, 1, 7) 2017-04
group by name;查询顾客的购买明细及到当前日期为止的购买总金额
selectname,orderdate,cost,sum(cost)over(partition by name
order byorderdate)
from business;执行结果
jack 2017-01-01 10 10
jack 2017-01-05 46 56
jack 2017-01-08 55 111
jack 2017-02-03 23 134
jack 2017-04-06 42 176
mart 2017-04-08 62 62每一行数据都新增一列即消费总额
selectname,orderdate,cost,sum(cost) over() sample1
from business;执行结果
mart 2017-04-13 94 661
neil 2017-06-12 80 661
mart 2017-04-11 75 661
neil 2017-05-10 12 661
mart 2017-04-09 68 661
mart 2017-04-08 62 661
jack 2017-01-08 55 661计算每个人的销售总额 以name分区计算每行数据新增一列即每人消费总额
selectname,orderdate,cost ,sum(cost)over(partition by name)
from business;执行结果
jack 2017-01-05 46 176
jack 2017-01-08 55 176
jack 2017-01-01 10 176
jack 2017-04-06 42 176
jack 2017-02-03 23 176
mart 2017-04-13 94 299
mart 2017-04-11 75 299
mart 2017-04-09 68 299计算每个人截止到当天的销售总额 以name分区、日期排序计算每行数据增一列即截止到当前的消费总额也就是从起点到当前行做聚合。如果指定order by默认使用分区内第起点到当前行。
selectname,orderdate,cost,sum(cost)over(partition by name order by orderdate)as sum_now
frombusiness;selectname,orderdate,cost,sum(cost)over(partition by name order by orderdate
# 固定写法表明从开头到当前行rows between unbounded preceding and current row )as sum_now
frombusiness;执行结果
jack 2017-01-01 10 10
jack 2017-01-05 46 56
jack 2017-01-08 55 111
jack 2017-02-03 23 134
jack 2017-04-06 42 176
mart 2017-04-08 62 62
mart 2017-04-09 68 130每个人连续两天的消费总额 以name分区、日期排序计算每行数据增一列即连续两天的消费总额也就是前一行和当前行聚合。当天的值为当天值与昨天值相加。
select name,orderdate,cost, sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and current row ) as sample5
from business;执行结果
jack 2017-01-01 10 10
jack 2017-01-05 46 56 (104656)
jack 2017-01-08 55 101 (4655101)
jack 2017-02-03 23 78 (552378)
jack 2017-04-06 42 65
mart 2017-04-08 62 62
mart 2017-04-09 68 130每个人从当天到最后一天的消费总额 以name分区、日期排序计算每行数据增一列即当前天到最后一天的消费总额也就是当前行聚合最后一行。行从当前行至最后一行总和。
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between current row and UNBOUNDED FOLLOWING )
from business;计算结果
jack 2017-01-01 10 176
jack 2017-01-05 46 166
jack 2017-01-08 55 120 (120422355)
jack 2017-02-03 23 65 (654223)
jack 2017-04-06 42 42 (4242)
mart 2017-04-08 62 299
mart 2017-04-09 68 2376.4 LAG函数
Lag函数用于统计窗口内往上第n行值。scalar_pexpression列名。offset 为往上几行。default 是设置的默认值当往上第n行为NULL时取默认值否则就为NULL
LAG (scalar_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause);查看顾客上次的购买时间
selectname,orderdate,cost,lag(orderdate,1,1900-01-01) over(partition by name order by orderdate ) as last_time
from business ;执行结果
jack 2017-01-01 10 1900-01-01
jack 2017-01-05 46 2017-01-01
jack 2017-01-08 55 2017-01-05
jack 2017-02-03 23 2017-01-08
jack 2017-04-06 42 2017-02-03
mart 2017-04-08 62 1900-01-01
mart 2017-04-09 68 2017-04-08
mart 2017-04-11 75 2017-04-096.5 Ntile函数
为已排序的行均分为指定数量的组组号按顺序排列返回组号不支持rows between
selectname,orderdate,cost,ntile(5) over( order by orderdate) sorted
from business;执行结果(分为了5组下面取第1组数据即可)
jack 2017-01-01 10 1
tony 2017-01-02 15 1
tony 2017-01-04 29 1
jack 2017-01-05 46 2
tony 2017-01-07 50 2
jack 2017-01-08 55 2
jack 2017-02-03 23 3
jack 2017-04-06 42 3
mart 2017-04-08 62 3
mart 2017-04-09 68 4
mart 2017-04-11 75 4
mart 2017-04-13 94 4
neil 2017-05-10 12 5
neil 2017-06-12 80 5取第一组数据
selectt1.name,t1.orderdate,t1.cost
from(selectname,orderdate,cost,ntile(5) over( order by orderdate) sortedfrom business ) t1
wheret1.sorted 1;执行结果
OK
jack 2017-01-01 10
tony 2017-01-02 15
tony 2017-01-04 296.6 Rank
Rank()排序值相同时会重复总数不会变(1、1、3、4)。DENSE_RANK()排序值相同时会重复总数会较少(1、1、2、3)。ROW_NUMBER()根据顺序计算。字段相同就按排头字段继续排(1、2、3、4)。
源数据: 7、自定义函数 **内置函数**Hive自带的函数。 **自定义函数**当Hive提供的内置函数无法满足你的业务处理需要时。可以自己定义一些函数。 UDFUser-Defined-Function 一进一出。 UDAFUser-Defined Aggregation Function聚合函数多进一出类似count/max/min UDTFUser-Defined Table-Generating Functions炸裂函数一进多出如explode()
四、压缩和存储
1、简介
Hive不会强制要求将数据转换成特定的格式才能使用。利用Hadoop的InputFormat API可以从不同数据源读取数据使用OutputFormat API可以将数据写成不同的格式输出。对数据进行压缩虽然会增加额外的CPU开销但是会节约客观的磁盘空间并且通过减少载入内存的数据量而提高I/O吞吐量会更加提高网络传输性能。原则上Hadoop的job时I/O密集型的话就可以采用压缩可以提高性能如果job是CPU密集型的话那么使用压缩可能会降低执行性能。
2、压缩简介
常用压缩算法 压缩格式算法文件扩展名是否可切分对应的编码/解码器DeflateDeflate.deflate否org.apache.hadoop.io.compress.DefaultCodecGzipDeflate.gz否org.apache.hadoop.io.compress.GzipCodecBzip2Bzip2.bz2是org.apache.hadoop.io.compress.BZip2CodecLzoLzo.lzo是com.hadoop.compression.lzo.LzopCodecSnappySnappy.snappy否org.apache.hadoop.io.compress.SnappyCodec
压缩效率对比
压缩算法原始文件大小压缩文件大小压缩速度解压速度gzip8.3GB1.8GB17.5MB/s58MB/sbzip28.3GB1.1GB2.4MB/s9.5MB/sLZO8.3GB2.9GB49.3MB/s74.6MB/s
要在Hadoop中启用压缩可以配置如下参数mapred-site.xml文件中
参数默认值阶段建议io.compression.codecs 在core-site.xml中配置org.apache.hadoop.io.compress.DefaultCodec, org.apache.hadoop.io.compress.GzipCodec, org.apache.hadoop.io.compress.BZip2Codec,org.apache.hadoop.io.compress.Lz4Codec输入压缩Hadoop使用文件扩展名判断是否支持某种编解码器mapreduce.map.output.compressfalsemapper输出这个参数设为true启用压缩mapreduce.map.output.compress.codecorg.apache.hadoop.io.compress.DefaultCodecmapper输出使用LZO、LZ4或snappy编解码器在此阶段压缩数据mapreduce.output.fileoutputformat.compressfalsereducer输出这个参数设为true启用压缩mapreduce.output.fileoutputformat.compress.codecorg.apache.hadoop.io.compress. DefaultCodecreducer输出使用标准工具或者编解码器如gzip和bzip2mapreduce.output.fileoutputformat.compress.typeRECORDreducer输出SequenceFile输出使用的压缩类型NONE和BLOCK
3、Map输出阶段压缩
开启map输出阶段压缩可以减少job中map和Reduce task间数据传输量。
1开启hive中间传输数据压缩功能
set hive.exec.compress.intermediate true;2开启mapreduce中map输出压缩功能
set mapreduce.map.output.compresstrue;3设置mapreduce中map输出数据的压缩方式
set mapreduce.map.output.compress.codec org.apache.hadoop.io.compress.SnappyCodec;4执行查询语句
select count(ename) name from emp;查看历史日志中有压缩方式.snappy 4、开启Reduce输出阶段压缩
当Hive将输出写入到表中时可以通过属性hive.exec.compress.output对输出内容进行压缩。
将hive.exec.compress.output false这样输出就是非压缩的纯文本文件了。
将hive.exec.compress.output true来开启输出结果压缩功能。
1开启hive最终输出数据压缩功能
set hive.exec.compress.outputtrue;2开启mapreduce最终输出数据压缩
set mapreduce.output.fileoutputformat.compresstrue;3设置mapreduce最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec org.apache.hadoop.io.compress.SnappyCodec;4设置mapreduce最终数据输出压缩为块压缩
set mapreduce.output.fileoutputformat.compress.typeBLOCK;5测试一下输出结果是否是压缩文件
insert overwrite local directory /opt/module/hive/datas/distribute-result select * from score distribute by name sort by score desc;5、文件存储格式
5.1 列式存储和行式存储
Hive支持的存储数据的格式主要有
TEXTFILE行存储SEQUENCEFILE行存储ORC列存储PARQUET列存储
如图所示左边为逻辑表右边第一个为行式存储第二个为列式存储。 行存储的特点
查询满足条件的一整行数据的时候
列存储则需要去每个聚集的字段找到对应的每个列的值
行存储只需要找到其中一个值其余的值都在相邻地方
所以此时行存储查询的速度更快。
列存储的特点
每个字段的数据聚集存储查询只需要少数几个字段的时候能大大减少读取的数据量
每个字段的数据类型一定是相同的列式存储可以针对性的设计更好的设计压缩算法。
5.2 TextFile_行存储
默认格式数据不做压缩磁盘开销大数据解析开销大。可结合Gzip、Bzip2使用但使用Gzip这种方式hive不会对数据进行切分从而无法对数据进行并行操作。
5.3 Orc_列存储
Orc (Optimized Row Columnar)是Hive 0.11版里引入的新的存储格式。 如下图所示可以看到每个Orc文件由1个或多个stripe组成每个stripe一般为HDFS的块大小每一个stripe包含多条记录这些记录按照列进行独立存储对应到Parquet中的row group的概念。 每个Stripe里有三部分组成分别是Index DataRow DataStripe Footer Index Data一个轻量级的index默认是每隔1W行做一个索引。这里做的索引应该只是记录某行的各字段在Row Data中的offset。 Row Data存的是具体的数据先取部分行然后对这些行按列进行存储。对每个列进行了编码分成多个Stream来存储。 Stripe Footer存的是各个Stream的类型长度等信息。每个文件有一个File Footer这里面存的是每个Stripe的行数每个Column的数据类型信息等每个文件的尾部是一个PostScript这里面记录了整个文件的压缩类型以及FileFooter的长度信息等。在读取文件时会seek到文件尾部读PostScript从里面解析到File Footer长度再读FileFooter从里面解析到各个Stripe信息再读各个Stripe即从后往前读。
ORC中压缩
Zlib压缩比高效率低。压缩ORC的默认压缩格式。Snappy压缩比低效率高。
5.4 Parquet_列存储
Parquet文件是以二进制方式存储的所以是不可以直接读取的文件中包括该文件的数据和元数据因此Parquet格式文件是自解析的。
1行组(Row Group)每一个行组包含一定的行数在一个HDFS文件中至少存储一个行组类似于orc的stripe的概念。2列块(Column Chunk)在一个行组中每一列保存在一个列块中行组中的所有列连续的存储在这个行组文件中。一个列块中的值都是相同类型的不同的列块可能使用不同的算法进行压缩。3页(Page)每一个列块划分为多个页一个页是最小的编码的单位在同一个列块的不同页可能使用不同的编码方式。
通常情况下在存储Parquet数据的时候会按照Block大小设置行组的大小由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block这样可以把每一个行组由一个Mapper任务处理增大任务执行并行度。 上图展示了一个Parquet文件的内容一个文件中可以存储多个行组文件的首位都是该文件的Magic Code用于校验它是否是一个Parquet文件Footer length记录了文件元数据的大小通过该值和文件长度可以计算出元数据的偏移量文件的元数据中包括每一个行组的元数据信息和该文件存储数据的Schema信息。
除了文件中每一个行组的元数据每一页的开始都会存储该页的元数据在Parquet中有三种类型的页数据页、字典页和索引页。数据页用于存储当前行组中该列的值字典页存储该列值的编码字典每一个列块中最多包含一个字典页索引页用来存储当前行组下该列的索引目前Parquet中还不支持索引页。
5.5 数据存储大小对比
TextFile
create table log_text (track_time string,url string,session_id string,referer string,ip string,end_user_id string,city_id string
)
row format delimited fields terminated by \t
stored as textfile;ORC
create table log_orc(track_time string,url string,session_id string,referer string,ip string,end_user_id string,city_id string
)
row format delimited fields terminated by \t
stored as orc
tblproperties(orc.compressNONE); // 由于ORC格式时自带压缩的这设置orc存储不使用压缩Parquet
create table log_parquet(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
)
row format delimited fields terminated by \t
stored as parquet ;同样数据文件存储后大小
TextFile18.1MORC7.7 MParquet13.1M
存储的大小对比ORC Parquet textFile
查询速度速度接近
6、存储和压缩结合
KeyDefaultNotesorc.compressZLIBhigh level compression (one of NONE, ZLIB, SNAPPY)orc.compress.size262,144number of bytes in each compression chunkorc.stripe.size268,435,456number of bytes in each stripeorc.row.index.stride10,000number of rows between index entries (must be 1000)orc.create.indextruewhether to create row indexesorc.bloom.filter.columns“”comma separated list of column names for which bloom filter should be createdorc.bloom.filter.fpp0.05false positive probability for bloom filter (must 0.0 and 1.0)
注意所有关于ORCFile的参数都是在HQL语句的TBLPROPERTIES字段里面出现。
ZLIB压缩的ORC存储方式2.8 M
create table log_orc_zlib(track_time string,url string,session_id string,referer string,ip string,end_user_id string,city_id string
)
row format delimited fields terminated by \t
stored as orc
tblproperties(orc.compressZLIB);SNAPPY压缩的ORC存储方式3.7 M
create table log_orc_snappy(track_time string,url string,session_id string,referer string,ip string,end_user_id string,city_id string
)
row format delimited fields terminated by \t
stored as orc
tblproperties(orc.compressSNAPPY);SNAPPY压缩的parquet存储方式6.4 MB
create table log_parquet_snappy(track_time string,url string,session_id string,referer string,ip string,end_user_id string,city_id string
)
row format delimited fields terminated by \t
stored as parquet
tblproperties(parquet.compressionSNAPPY);hive表的数据存储格式一般选择orc或parquet。压缩方式一般选择snappylzo。ORC默认压缩Zlib。
四、企业级调优
1、查看执行计划
EXPLAIN [EXTENDED | DEPENDENCY | AUTHORIZATION] query没有生成MR任务的
explain select * from emp;生成MR任务的
explain select deptno, avg(sal) avg_sal from emp group by deptno;查看详细执行计划
explain extended select * from emp;2、Hive建表优化
分区表分桶表合适的文件格式
3、HQL语法优化
3.1 列裁剪和分区裁剪
在生产环境中会面临列很多或者数据量很大时如果使用select * 或者不指定分区进行全列或者全表扫描时效率很低。Hive在读取数据时可以只读取查询中所需要的列忽视其他的列这样做可以节省读取开销中间表存储开销和数据整合开销
列裁剪在查询时只读取需要的列。分区裁剪在查询中只读取需要的分区。
3.2 Group By
默认情况下Map阶段同一Key数据分给一个Reduce当一个Key数据过大时就倾斜了。并不是所有的聚合操作都需要再Reduce端完成很多聚合操作都可以先在Map端进行部分聚合最后在Reduce端得出最终结果。 参数配置
开启Map端聚合设置
# 开启在Map端聚合
set hive.map.aggr true
# Map端聚合条目数目
set hive.groupby.mapaggr.checkinterval 100000
# 开启数据倾斜时进行负载均衡
set hive.groupby.skewindata true当开启数据负载均衡时生成的查询计划会有2个MRJob。 第一个MRJob中Map的输出结果会随机分布到Reduce中每个Reduce做部分聚合操作并输出结果这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中从而达到负载均衡的目的 第二个MRJob再根据预处理的数据结果按照Group By Key分布到Reduce中这个过程可以保证相同的Group By Key被分布到同一个Reduce中最后完成最终的聚合操作。
3.3 CBO优化
4、数据倾斜
4.1 现象
数据倾斜绝大多数任务很快完成只有1个或者几个任务执行的很忙甚至最终执行失败。
数据过量所有的任务执行都很慢。这时候只有提高执行资源才能优化HQL的执行效率。
原因
按照Key分组后少量的任务负载着绝大部分的数据的计算也就是说。产生数据倾斜的HQL中一定存在着分组的操作所以从HQL的角度我们可以将数据倾斜分为单表携带了Group By字段的查询和2表(多表)Join的查询。
4.2 单表数据倾斜优化
1使用参数优化
当任务中存在Group By操作同时聚合函数为count或者sum。可以设置参数来处理数据倾斜的问题。
# 是否在Map端进行聚合默认为True
set hive.map.aggr true
# 在Map端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval 100000
# 有数据倾斜的时候进行负载均衡默认是false
set hive.groupby.skewindata true2增加Reduce数量
当数据中多个key同时导致数据倾斜可以通过增加Reduce的数量解决数据倾斜问题。
方式一动态调整
# 每个Reduce处理的数据量默认为 256M参数1
set hive.exec.reducers.bytes.per.reducer256000000
# 每个任务最大的Reduce数默认为1009参数2
set hive.exec.reducers.max1009
# 计算Reducer数的公式
N min(参数2 总数入数据量/参数1)方式二直接指定
# 直接指定Reduce个数
set mapreduce.job.reduces 15;4.3 Join数据倾斜优化
1使用参数
在编写Join查询语句时如果确定是由于join出现的数据倾斜
# join的键对应的记录条数超过这个值则会进行分拆值根据具体数据量设置
set hive.skewjoin.key100000;
# 如果是join过程出现倾斜应该设置为true
set hive.optimize.skewjoinfalse;如果开启了在Join过程中Hive会将计数超过阈值hive.skewjoin.key默认100000的倾斜key对应的行临时写进文件中然后再启动另一个job做map join生成结果。通过 hive.skewjoin.mapjoin.map.tasks参数还可以控制第二个job的mapper数量默认10000。
set hive.skewjoin.mapjoin.map.tasks10000;2大小表join
可以使用MapJoin没有Reduce阶段就不会出现数据倾斜。
3大表大表Join
使用打散加扩容的方式解决数据倾斜问题选择其中较大的表做打算操作
SELECT * ,concat(id,_ , 0 or 1 or 2)FROM A; t1选择其中较小的表做扩容处理
SELECT *,concat(id,-,0) from B
UNION ALL
SELECT *,concat(id,-,1) from B
UNION ALL
SELECT *,concat(id,-,2) from B;t25、Hive Job优化
1 1 1 1 1 1 1 1
