Hive uses cost based optimization (CBO) of the query to speed up execution. CBO uses table statistics stored in hive metastore. These statistics are the number of rows, number of files, and raw data size of the objects in the database etc. Statistics are at the table, partition, and column level. Hive collects these statistics automatically or manually by running ANALYZE command.
To use CBO, set a few configurations
hive> SET hive.cbo.enable=true; -- default true
SET hive.compute.query.using.stats=true; -- default false
SET hive.stats.fetch.column.stats=true; -- default false
SET hive.stats.fetch.partition.stats=true; -- default true
Variations of the analyze commands.
hive> ANALYZE TABLE stocks COMPUTE STATISTICS;
ANALYZE TABLE stocks COMPUTE STATISTICS FOR COLUMNS; -- collect statistics for columns
ANALYZE TABLE stocks COMPUTE STATISTICS NOSCAN; -- collects only files count and their size, hence faster
ANALYZE TABLE stocks_bucket PARTITION(year) COMPUTE STATISTICS; -- collect statistics for all partitions
ANALYZE TABLE stocks_bucket COMPUTE STATISTICS FOR COLUMNS symbol, tr_date;
The following statistics currently supported for table and partitions:
Number of rows
Number of files
Size in Bytes
Number of partition if the table is partitioned
Hive calculated the statistics autograph is enabled
hive> set hive.stats.autogather=true;
Compare explain plan for each of the above statement. The query below computes the year-on year avg adjclose price for each symbol.
hive>explain
select
*
from
(select
symbol,
year(tr_date) year,
avg(adjclose) avg_2013
from
stocks
where
year(tr_date) = 2013
group by
symbol,
year(tr_date)) t1
join
(
select
symbol,
year(tr_date) year,
avg(adjclose) avg_2014
from
stocks
where
year(tr_date) = 2014
group by
symbol,
year(tr_date)
) t2
on t1.symbol = t2.symbol
and t1.year = t2.year - 1;
Query Plan
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-6 depends on stages: Stage-1, Stage-3 , consists of Stage-7, Stage-8, Stage-2
Stage-7 has a backup stage: Stage-2
Stage-4 depends on stages: Stage-7
Stage-8 has a backup stage: Stage-2
Stage-5 depends on stages: Stage-8
Stage-2
Stage-3 is a root stage
Stage-0 depends on stages: Stage-4, Stage-5, Stage-2
STAGE PLANS:
Stage: Stage-1
Map Reduce
Map Operator Tree:
TableScan
alias: stocks
Statistics: Num rows: 215855 Data size: 44898024 Basic stats: COMPLETE Column stats: NONE
Filter Operator
predicate: ((year(tr_date) = 2013) and symbol is not null) (type: boolean)
Statistics: Num rows: 53964 Data size: 11224558 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: avg(adjclose)
keys: symbol (type: string), year(tr_date) (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 53964 Data size: 11224558 Basic stats: COMPLETE Column stats: NONE
Filter Operator
predicate: _col1 is not null (type: boolean)
Statistics: Num rows: 26982 Data size: 5612279 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
key expressions: _col0 (type: string), _col1 (type: int)
sort order: ++
Map-reduce partition columns: _col0 (type: string), _col1 (type: int)
Statistics: Num rows: 26982 Data size: 5612279 Basic stats: COMPLETE Column stats: NONE
value expressions: _col2 (type: struct<count:bigint,sum:double,input:double>)
Reduce Operator Tree:
Group By Operator
aggregations: avg(VALUE._col0)
keys: KEY._col0 (type: string), KEY._col1 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 13491 Data size: 2806139 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-6
Conditional Operator
Stage: Stage-7
Map Reduce Local Work
Alias -> Map Local Tables:
$INTNAME1
Fetch Operator
limit: -1
Alias -> Map Local Operator Tree:
$INTNAME1
TableScan
HashTable Sink Operator
keys:
0 _col0 (type: string), _col1 (type: int)
1 _col0 (type: string), (_col1 - 1) (type: int)
Stage: Stage-4
Map Reduce
Map Operator Tree:
TableScan
Map Join Operator
condition map:
Inner Join 0 to 1
keys:
0 _col0 (type: string), _col1 (type: int)
1 _col0 (type: string), (_col1 - 1) (type: int)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
Statistics: Num rows: 14840 Data size: 3086752 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 14840 Data size: 3086752 Basic stats: COMPLETE Column stats: NONE
table:
input format: org.apache.hadoop.mapred.TextInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Local Work:
Map Reduce Local Work
Stage: Stage-8
Map Reduce Local Work
Alias -> Map Local Tables:
$INTNAME
Fetch Operator
limit: -1
Alias -> Map Local Operator Tree:
$INTNAME
TableScan
HashTable Sink Operator
keys:
0 _col0 (type: string), _col1 (type: int)
1 _col0 (type: string), (_col1 - 1) (type: int)
Stage: Stage-5
Map Reduce
Map Operator Tree:
TableScan
Map Join Operator
condition map:
Inner Join 0 to 1
keys:
0 _col0 (type: string), _col1 (type: int)
1 _col0 (type: string), (_col1 - 1) (type: int)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
Statistics: Num rows: 14840 Data size: 3086752 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 14840 Data size: 3086752 Basic stats: COMPLETE Column stats: NONE
table:
input format: org.apache.hadoop.mapred.TextInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Local Work:
Map Reduce Local Work
Stage: Stage-2
Map Reduce
Map Operator Tree:
TableScan
Reduce Output Operator
key expressions: _col0 (type: string), _col1 (type: int)
sort order: ++
Map-reduce partition columns: _col0 (type: string), _col1 (type: int)
Statistics: Num rows: 13491 Data size: 2806139 Basic stats: COMPLETE Column stats: NONE
value expressions: _col2 (type: double)
TableScan
Reduce Output Operator
key expressions: _col0 (type: string), (_col1 - 1) (type: int)
sort order: ++
Map-reduce partition columns: _col0 (type: string), (_col1 - 1) (type: int)
Statistics: Num rows: 13491 Data size: 2806139 Basic stats: COMPLETE Column stats: NONE
value expressions: _col1 (type: int), _col2 (type: double)
Reduce Operator Tree:
Join Operator
condition map:
Inner Join 0 to 1
keys:
0 _col0 (type: string), _col1 (type: int)
1 _col0 (type: string), (_col1 - 1) (type: int)
outputColumnNames: _col0, _col1, _col2, _col3, _col4, _col5
Statistics: Num rows: 14840 Data size: 3086752 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 14840 Data size: 3086752 Basic stats: COMPLETE Column stats: NONE
table:
input format: org.apache.hadoop.mapred.TextInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-3
Map Reduce
Map Operator Tree:
TableScan
alias: stocks
Statistics: Num rows: 215855 Data size: 44898024 Basic stats: COMPLETE Column stats: NONE
Filter Operator
predicate: ((year(tr_date) = 2014) and symbol is not null) (type: boolean)
Statistics: Num rows: 53964 Data size: 11224558 Basic stats: COMPLETE Column stats: NONE
Group By Operator
aggregations: avg(adjclose)
keys: symbol (type: string), year(tr_date) (type: int)
mode: hash
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 53964 Data size: 11224558 Basic stats: COMPLETE Column stats: NONE
Filter Operator
predicate: (_col1 - 1) is not null (type: boolean)
Statistics: Num rows: 26982 Data size: 5612279 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator
key expressions: _col0 (type: string), _col1 (type: int)
sort order: ++
Map-reduce partition columns: _col0 (type: string), _col1 (type: int)
Statistics: Num rows: 26982 Data size: 5612279 Basic stats: COMPLETE Column stats: NONE
value expressions: _col2 (type: struct<count:bigint,sum:double,input:double>)
Reduce Operator Tree:
Group By Operator
aggregations: avg(VALUE._col0)
keys: KEY._col0 (type: string), KEY._col1 (type: int)
mode: mergepartial
outputColumnNames: _col0, _col1, _col2
Statistics: Num rows: 13491 Data size: 2806139 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-0
Fetch Operator
limit: -1
Processor Tree:
ListSink
Also, try hive> explain extended select ... to see additional details.
Output of the above query:
A 2013 32.428159357142846 A 2014 39.7366169325397
AA 2013 8.299071845238094 AA 2014 14.112061321428575
AAL 2013 17.78869600000001 AAL 2014 38.132203436507936
AAP 2013 84.73887314285709 AAP 2014 130.51677267460315
AAPL 2013 63.2123629920635 AAPL 2014 88.46767818253969
ABBV 2013 39.25952140079365 ABBV 2014 51.575054202380976
ABC 2013 54.608562777777784 ABC 2014 72.72405008333328
ABT 2013 33.30880767460318 ABT 2014 39.041839111111116
ACN 2013 70.59546130952383 ACN 2014 77.84725737698413
ADBE 2013 46.803412603174586 ADBE 2014 67.45305560317458
ADI 2013 42.75313803571431 ADI 2014 48.544903773809516
ADM 2013 32.63645731746032 ADM 2014 43.55579893650796
ADP 2013 56.84975079761904 ADP 2014 68.99841694444443
ADS 2013 191.86746031746037 ADS 2014 265.4146823611112
ADSK 2013 39.15007928571429 ADSK 2014 53.830000071428586
AEE 2013 30.59784315873017 AEE 2014 36.93181792063489
AEP 2013 40.76954225396826 AEP 2014 48.832750234127005
AES 2013 11.827752503968249 AES 2014 13.292465710317478
AET 2013 57.41888040476189 AET 2014 76.42373734126986
AFL 2013 53.827321797619014 AFL 2014 58.39401914682539
AGN 2013 122.96440459126977 AGN 2014 220.02797637698416
AIG 2013 42.92983638492063 AIG 2014 51.194616666666654
AIV 2013 26.249909853174604 AIV 2014 30.359743226190485
AIZ 2013 48.386520777777754 AIZ 2014 63.50188482936509
AJG 2013 38.94890921428571 AJG 2014 43.30382427777779
...
Benefits of CBO are realised when you run complex join involving multiple tables of different size and using columns are various degrees of cardinality.
https://hortonworks.com/blog/hive-0-14-cost-based-optimizer-cbo-technical-overview/