HIVE优化场景八-数据裁剪问题

场景八_数据裁剪问题

1) 记录数裁剪

i.通过分区，分桶表的优势去构建

分桶表的构建过程与使用：https://blog.csdn.net/qq_26803795/article/details/105205702

分区表与分桶表本身就是一个一个优化策略。

分区表更适用可以合理方式进行数据组织的列，比如每天的log 日志，由于每天的数据都差不多，可以按分区进行组织！！

分桶表适用于无法形成合理分区的场景，并且可以比较方便的实现抽取，如用户id列。

当然两者可以相互结合，并无冲突

分区表：通过筛选指定的分区，就可以很容易做到数据筛选，因为分区在 hdfs 会以文件夹的形式存在！！（分区列，可以大幅度提升筛选效率）

分桶表：由于分桶列是按照对列值哈希，然后除以桶的个数求余，决定将该条记录存放到哪个桶中。所以在后续的筛选也有很大的优势（不用扫描全表）

例如下面3个语句执行的结果都是一致的。

SELECT

a.user_id

,b.user_id

FROM datacube_salary_org AS a

JOIN datacube_salary_org AS b

ON a.user_id = b.user_id

AND a.salary >= 10000.0

AND b.salary >= 10000.0

ORDER BY a.user_id, b.user_id

;

SELECT

tmp_a.user_id

,tmp_b.user_id

FROM

(

SELECT *

FROM datacube_salary_org AS a

WHERE a.salary >= 10000.0

) tmp_a

JOIN

(

SELECT *

FROM datacube_salary_org AS b

WHERE b.salary >= 10000.0

) tmp_b

ON tmp_a.user_id = tmp_b.user_id

ORDER BY tmp_a.user_id, tmp_b.user_id

;

SELECT

a.user_id

,b.user_id

FROM datacube_salary_org AS a

JOIN datacube_salary_org AS b

ON a.user_id = b.user_id

WHERE

a.salary >= 10000.0

AND b.salary >= 10000.0

ORDER BY a.user_id, b.user_id

;

我们比较下3个语句的执行计划，利用 EXPLAIN

EXPLAIN

SELECT

a.user_id

,b.user_id

FROM datacube_salary_org AS a

JOIN datacube_salary_org AS b

ON a.user_id = b.user_id

AND a.salary >= 10000.0

AND b.salary >= 10000.0

ORDER BY a.user_id, b.user_id

;

STAGE DEPENDENCIES:

Stage-1 is a root stage

Stage-2 depends on stages: Stage-1

Stage-0 depends on stages: Stage-2

STAGE PLANS:

Stage: Stage-1

Map Reduce

Map Operator Tree:

TableScan

alias: a

filterExpr: ((salary >= 10000.0) and user_id is not null) (type: boolean)

Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE

Filter Operator

predicate: ((salary >= 10000.0) and user_id is not null) (type: boolean)

Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE

Reduce Output Operator

key expressions: user_id (type: bigint)

sort order: +

Map-reduce partition columns: user_id (type: bigint)

Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE

TableScan

alias: b

filterExpr: ((salary >= 10000.0) and user_id is not null) (type: boolean)

Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE

Filter Operator

predicate: ((salary >= 10000.0) and user_id is not null) (type: boolean)

Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE

Reduce Output Operator

key expressions: user_id (type: bigint)

sort order: +

Map-reduce partition columns: user_id (type: bigint)

Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE

Reduce Operator Tree:

Join Operator

condition map:

Inner Join 0 to 1

keys:

0 user_id (type: bigint)

1 user_id (type: bigint)

outputColumnNames: _col2, _col13

Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE

Select Operator

expressions: _col2 (type: bigint), _col13 (type: bigint)

outputColumnNames: _col0, _col1

Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE

File Output Operator

compressed: false

table:

input format: org.apache.hadoop.mapred.SequenceFileInputFormat

output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

Stage: Stage-2

Map Reduce

Map Operator Tree:

TableScan

Reduce Output Operator

key expressions: _col0 (type: bigint), _col1 (type: bigint)

sort order: ++

Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE

Reduce Operator Tree:

Select Operator

expressions: KEY.reducesinkkey0 (type: bigint), KEY.reducesinkkey1 (type: bigint)

outputColumnNames: _col0, _col1

Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE

File Output Operator

compressed: false

Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE

table:

input format: org.apache.hadoop.mapred.SequenceFileInputFormat

output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

Stage: Stage-0

Fetch Operator

limit: -1

Processor Tree:

ListSink

第二种方式

EXPLAIN

SELECT

tmp_a.user_id

,tmp_b.user_id

FROM

(

SELECT *

FROM datacube_salary_org AS a

WHERE a.salary >= 10000.0

) tmp_a

JOIN

(

SELECT *

FROM datacube_salary_org AS b

WHERE b.salary >= 10000.0

) tmp_b

ON tmp_a.user_id = tmp_b.user_id

ORDER BY tmp_a.user_id, tmp_b.user_id

;

STAGE DEPENDENCIES:

Stage-1 is a root stage

Stage-2 depends on stages: Stage-1

Stage-0 depends on stages: Stage-2

STAGE PLANS:

Stage: Stage-1

Map Reduce

Map Operator Tree:

TableScan

alias: a

Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE

Filter Operator

predicate: (salary >= 10000.0) (type: boolean)

Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE

Select Operator

expressions: user_id (type: bigint)

outputColumnNames: _col2

Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE

Filter Operator

predicate: _col2 is not null (type: boolean)

Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE

Reduce Output Operator

key expressions: _col2 (type: bigint)

sort order: +

Map-reduce partition columns: _col2 (type: bigint)

Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE

TableScan

alias: b

Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE

Filter Operator

predicate: (salary >= 10000.0) (type: boolean)

Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE

Select Operator

expressions: user_id (type: bigint)

outputColumnNames: _col2

Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE

Filter Operator

predicate: _col2 is not null (type: boolean)

Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE

Reduce Output Operator

key expressions: _col2 (type: bigint)

sort order: +

Map-reduce partition columns: _col2 (type: bigint)

Statistics: Num rows: 2 Data size: 97 Basic stats: COMPLETE Column stats: NONE

Reduce Operator Tree:

Join Operator

condition map:

Inner Join 0 to 1

keys:

0 _col2 (type: bigint)

1 _col2 (type: bigint)

outputColumnNames: _col2, _col10

Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE

Select Operator

expressions: _col2 (type: bigint), _col10 (type: bigint)

outputColumnNames: _col0, _col1

Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE

File Output Operator

compressed: false

table:

input format: org.apache.hadoop.mapred.SequenceFileInputFormat

output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

Stage: Stage-2

Map Reduce

Map Operator Tree:

TableScan

Reduce Output Operator

key expressions: _col0 (type: bigint), _col1 (type: bigint)

sort order: ++

Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE

Reduce Operator Tree:

Select Operator

expressions: KEY.reducesinkkey0 (type: bigint), KEY.reducesinkkey1 (type: bigint)

outputColumnNames: _col0, _col1

Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE

File Output Operator

compressed: false

Statistics: Num rows: 2 Data size: 106 Basic stats: COMPLETE Column stats: NONE

table:

input format: org.apache.hadoop.mapred.SequenceFileInputFormat

output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

Stage: Stage-0

Fetch Operator

limit: -1

Processor Tree:

ListSink

第三种方式

EXPLAIN

SELECT

a.user_id

,b.user_id

FROM datacube_salary_org AS a

JOIN datacube_salary_org AS b

ON a.user_id = b.user_id

WHERE

a.salary >= 10000.0

AND b.salary >= 10000.0

ORDER BY a.user_id, b.user_id

;

STAGE DEPENDENCIES:

Stage-1 is a root stage

Stage-2 depends on stages: Stage-1

Stage-0 depends on stages: Stage-2

STAGE PLANS:

Stage: Stage-1

Map Reduce

Map Operator Tree:

TableScan

alias: a

Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE

Filter Operator

predicate: user_id is not null (type: boolean)

Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE

Reduce Output Operator

key expressions: user_id (type: bigint)

sort order: +

Map-reduce partition columns: user_id (type: bigint)

Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE

value expressions: salary (type: decimal(10,2))

TableScan

alias: b

Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE

Filter Operator

predicate: user_id is not null (type: boolean)

Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE

Reduce Output Operator

key expressions: user_id (type: bigint)

sort order: +

Map-reduce partition columns: user_id (type: bigint)

Statistics: Num rows: 7 Data size: 340 Basic stats: COMPLETE Column stats: NONE

value expressions: salary (type: decimal(10,2))

Reduce Operator Tree:

Join Operator

condition map:

Inner Join 0 to 1

keys:

0 user_id (type: bigint)

1 user_id (type: bigint)

outputColumnNames: _col2, _col4, _col13, _col15

Statistics: Num rows: 7 Data size: 374 Basic stats: COMPLETE Column stats: NONE

Filter Operator

predicate: ((_col4 >= 10000.0) and (_col15 >= 10000.0)) (type: boolean)

Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE

Select Operator

expressions: _col2 (type: bigint), _col13 (type: bigint)

outputColumnNames: _col0, _col1

Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE

File Output Operator

compressed: false

table:

input format: org.apache.hadoop.mapred.SequenceFileInputFormat

output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

Stage: Stage-2

Map Reduce

Map Operator Tree:

TableScan

Reduce Output Operator

key expressions: _col0 (type: bigint), _col1 (type: bigint)

sort order: ++

Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE

Reduce Operator Tree:

Select Operator

expressions: KEY.reducesinkkey0 (type: bigint), KEY.reducesinkkey1 (type: bigint)

outputColumnNames: _col0, _col1

Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE

File Output Operator

compressed: false

Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE

table:

input format: org.apache.hadoop.mapred.SequenceFileInputFormat

output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

Stage: Stage-0

Fetch Operator

limit: -1

Processor Tree:

ListSink

可以看到第一种，第二种方式的执行计划基本一致。第三个执行计划，可以看到把筛选条件放到了 JOIN 之后，在大数据量下，必定慢：

Reduce Operator Tree:

Join Operator

condition map:

Inner Join 0 to 1

keys:

0 user_id (type: bigint)

1 user_id (type: bigint)

outputColumnNames: _col2, _col4, _col13, _col15

Statistics: Num rows: 7 Data size: 374 Basic stats: COMPLETE Column stats: NONE

Filter Operator

predicate: ((_col4 >= 10000.0) and (_col15 >= 10000.0)) (type: boolean)

Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE

Select Operator

expressions: _col2 (type: bigint), _col13 (type: bigint)

outputColumnNames: _col0, _col1

Statistics: Num rows: 1 Data size: 53 Basic stats: COMPLETE Column stats: NONE

File Output Operator

compressed: false

table:

input format: org.apache.hadoop.mapred.SequenceFileInputFormat

output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

serde: org.apache.hadoop.hive.serde2.lazybinary.LazyBinarySerDe

另外我们需要注意第二种方式

SELECT

tmp_a.user_id

,tmp_b.user_id

FROM

(

SELECT *

FROM datacube_salary_org AS a

WHERE a.salary >= 10000.0

) tmp_a

JOIN

(

SELECT *

FROM datacube_salary_org AS b

WHERE b.salary >= 10000.0

) tmp_b

ON tmp_a.user_id = tmp_b.user_id

ORDER BY tmp_a.user_id, tmp_b.user_id

;

需要注意 : 虽然我们子查询里面用的是 * 。但是实际上，执行器会帮我优化，只选取 user_id !!!

2) 列裁剪

i.剔除无效, 非计算范围内的列数据

这个比较好理解，在 SELECT 之中对需要的列做筛选。而不是使用 SELECT *

下面，我们看下这两种方式有什么区别：

EXPLAIN

SELECT

a.user_id

,a.company_name

FROM datacube_salary_org AS a

WHERE

a.pt = '20200406'

;

STAGE DEPENDENCIES:

Stage-1 is a root stage

Stage-0 depends on stages: Stage-1

STAGE PLANS:

Stage: Stage-1

Map Reduce

Map Operator Tree:

TableScan

alias: a

Statistics: Num rows: 7 Data size: 984 Basic stats: COMPLETE Column stats: PARTIAL

Filter Operator

predicate: (pt = '20200406') (type: boolean)

Statistics: Num rows: 7 Data size: 644 Basic stats: COMPLETE Column stats: PARTIAL

Select Operator

expressions: user_id (type: bigint), company_name (type: string)

outputColumnNames: _col0, _col1

Statistics: Num rows: 7 Data size: 0 Basic stats: PARTIAL Column stats: PARTIAL

File Output Operator

compressed: false

Statistics: Num rows: 7 Data size: 0 Basic stats: PARTIAL Column stats: PARTIAL

table:

input format: org.apache.hadoop.mapred.SequenceFileInputFormat

output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

Stage: Stage-0

Fetch Operator

limit: -1

Processor Tree:

ListSink

选取所有列

EXPLAIN

SELECT

a.*

FROM datacube_salary_org AS a

WHERE

a.pt = '20200406'

;

STAGE DEPENDENCIES:

Stage-1 is a root stage

Stage-0 depends on stages: Stage-1

STAGE PLANS:

Stage: Stage-1

Map Reduce

Map Operator Tree:

TableScan

alias: a

Statistics: Num rows: 7 Data size: 984 Basic stats: COMPLETE Column stats: PARTIAL

Filter Operator

predicate: (pt = '20200406') (type: boolean)

Statistics: Num rows: 7 Data size: 644 Basic stats: COMPLETE Column stats: PARTIAL

Select Operator

expressions: company_name (type: string), dep_name (type: string), user_id (type: bigint), user_name (type: string), salary (type: decimal(10,2)), create_time (type: date), update_time (type: date), '20200406' (type: string)

outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5, _col6, _col7

Statistics: Num rows: 7 Data size: 644 Basic stats: COMPLETE Column stats: PARTIAL

File Output Operator

compressed: false

Statistics: Num rows: 7 Data size: 644 Basic stats: COMPLETE Column stats: PARTIAL

table:

input format: org.apache.hadoop.mapred.SequenceFileInputFormat

output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat

serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

Stage: Stage-0

Fetch Operator

limit: -1

Processor Tree:

ListSink

ii.使用列式存储

使用列式存储的作为表的存储方式，也可以帮助我们更迅速的实现对列的快速筛选。

目前 Hive 支持的列式存储方式： PARQUET, RCFILE(不推荐), ORCFILE

————————————————

原文链接：https://blog.csdn.net/u010003835/article/details/105495151

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 206,214评论 6赞 481
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 88,307评论 2赞 382
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 152,543评论 0赞 341
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 55,221评论 1赞 279
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 64,224评论 5赞 371
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 49,007评论 1赞 284
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,313评论 3赞 399
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,956评论 0赞 259
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 43,441评论 1赞 300
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,925评论 2赞 323
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 38,018评论 1赞 333
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,685评论 4赞 322
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 39,234评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 30,240评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,464评论 1赞 261
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,467评论 2赞 352
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,762评论 2赞 345

HIVE优化场景八-数据裁剪问题

推荐阅读更多精彩内容