1. 序言
大数据处理过程,由于量大耗时,数据效率是首要考虑的。如何避免数据倾斜和加快数据处理。主要分两部分建表过程 ,处理过程。
2. 建表
通常情况数据源或二次处理结果都不能直接应用或者最终结果,需要进一步处理。建表模型需要考虑效率处理。大数据语境就是合理、有序、一致的建立分区。
比如上百亿的汇总数据,除了安装时间细粒度建立分区外,另外根据后续处理join的链接键值建立分区。比如用户ID后三位。
业务分析之后合理设计分区之外
sort by
对每一个Reduce内部数据进行排序,全局结果集来说不是排序:
----例子不明显需要更直观的例子
set mapreduce.job.reduces='3'
select * from tablename sort by colname asc;
distribute by
类似于mapreduce中分区partition,对数据进行区分,结合sort by进行使用;
insert overwrite local directory '<localPath>' select * from tablename distribute by colname1 sort by colname2 asc;
注意:distribute by必须要在sort by前;
cluster by
当distribute by和sort by字段相同时,可以使用;
3 数据处理
join的key值因为有很多null值发生倾斜:对异常值赋予随机值来分散key,通过rand函数将为null的值分散到不同的值上。但最好是在where过滤条件就过滤掉。
select userid,name
from user_info as a
join(
select case when userid is null then cast(rand(47)*100000 as int)
else userid
from user_read_log) b
on a.userid = b.userid
hive.groupby.mapaggr.checkinterval = 100000 (默认)
hive.map.aggr.hash.min.reduction=0.5(默认)
--两个参数的意思是:预先取100000条数据聚合,如果聚合后的条数/100000>0.5,则不再聚合。
set hive.auto.convert.join = true;
--把小表刷进内存, hive可以和内存中的小表逐一匹配,从而省略掉常规链接操作所需要的reduce过程。
hive.mapjoin.smalltable.filesize = 25000000
--也可以配置能够使用这个优化的小表的大小;默认是25MB。
select /*+MAPJOIN(table_name)*/
--如果有些表不适合载入内存,可以用map join,能减少reduce的阶段。
select /*+STREAMTABLE(table_name)*/
--大表驱动,相当于指出最大的表放在join的最右边。
set hive.optimize.bucketmapJOIN = true
--如果表中的数据按照ON语句中的键进行分桶,并且其中一张表的分桶的个数是另一张表的分桶个数若干倍,那么hive可以在map阶段按照分桶数据进行连接。
set hive.input.format=apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true
--如果涉及的分桶表都有相同的分桶数,而且数据是按照连接键或桶的键进行排序,那么hive可以执行一个更快的sort-merge join。
#where条件写在join中。
#对于count(distinct)操作,在map端以group by的字段和count的字段联合作为key。
#小表放在左边,因为:对于普通的join操作,会在map端根据key的hash值,shuffle到某一个reduce上去,在reduce端做join连接操作,内存中缓存join左边的表,遍历右边的表,一次做join操作。
--note:有的hive操作,不存在数据倾斜的问题,比如数据聚合类的操作,像sum、count,因为已经在map端做了聚合操作了,到reduce端的数据相对少一些。
高级聚合ROLLUP和CUBE
这两个关键字都是GROUP BY的高级实现。
对比于规定了n层聚合的GROUPING SETS,ROLLUP会创建n+1层聚合,在此n表示分组列的个数。
GROUP BY a, b, c WITH ROLLUP 等价于 GROUP BY a,b,c GROUPING SETS ((a,b,c),(a,b),(a),())
CUBE将会对分组列进行所有可能的组合聚合。如果为CUBE指定了n列,则将返回2^n个聚合组合。
GROUP BY a, b, c WITH ROLLUP 等价于 GROUP BY a,b,c GROUPING SETS ((a,b,c),(a,b),(b,c),(a,c),(a),(b),©,())
抽样
采样
当数据集非常大的时候,我们需要找一个子集来加快数据分析。此时我们需要数据采集工具以获得需要的子集。在此可以使用三种方式获得采样数据:random sampling, bucket sampling, block sampling.
- Random sampling
使用RAND()函数和LIMIT关键字来获取样例数据。使用DISTRIBUTE和SORT关键字来保证数据是随机分散到mapper和reducer的。ORDER BY RAND()语句可以获得同样的效果,但是性能没这么高。
SELECT * FROM <Table_Name> DISTRIBUTE BY RAND() SORT BY RAND() LIMIT <N rows to sample>;
Bucket table sampling
该方式是最佳化采样bucket表。RAND()函数也可以用来采样整行。如果采样列同时使用了CLUSTERED BY,使用TABLESAMPLE语句会更有效率。
SELECT * FROM <Table_Name> TABLESAMPLE(BUCKET <specified bucket number to sample> OUT OF <total number of buckets> ON [colname|RAND()]) table_alias;
hive>
CREATE TABLE employee_id_buckets (
name string,
employee_id int,
work_place ARRAY<string>,
sex_age STRUCT<sex:string,age:int>,
skills_score MAP<string,int>,
depart_title MAP<string,ARRAY<string >>
)
CLUSTERED BY (employee_id) INTO 2 BUCKETS
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';
INSERT OVERWRITE TABLE employee_id_buckets SELECT * FROM employee_id;
SELECT name FROM employee_id_buckets TABLESAMPLE(BUCKET 1 OUT OF 2 ON rand()) a;
Block sampling
该方式允许Hive随机抽取N行数据,数据总量的百分比(n百分比)或N字节的数据。
–Syntax:
SELECT * FROM <Table_Name> TABLESAMPLE(N PERCENT|ByteLengthLiteral|N ROWS) s;
–ByteLengggthLiteral:
–(Digit)+ (‘b’ | ‘B’ | ‘k’ | ‘K’ | ‘m’ | ‘M’ | ‘g’ | ‘G’)
例:按行抽样
hive>
SELECT name FROM employee_id_buckets TABLESAMPLE(4 ROWS) a;
例:按数据量百分比抽样
hive>
SELECT name FROM employee_id_buckets TABLESAMPLE(10 PERCENT) a;
注:此方法有待考证,在Hive0.11.0中将所有25条数据全取出来了,在Hive0.13.0中取出了其中的12条,但是都不符合要求!!
例:按数据大小采样
hive>
SELECT name FROM employee_id_buckets TABLESAMPLE(1M) a;