Hive on Tez でクエリチューニング @HDP2.3.2
こんにちは。次世代システム研究室のデータベースまわり担当のM.K.です。
TAXEL byGMO というサービスのシステム開発で、Hive on Tez を利用しているのですが HiveQL クエリをチューニングしていく際に、あれこれやってみたことをまとめてみました。
Hive on Tez：チューニング対象のサンプルクエリ
INSERT OVERWRITE TABLE ttt
SELECT zc.mid, zc.aid, zc.count_date FROM z_count zc
INNER JOIN (
SELECT mid, aid, MAX(count_date) AS count_date FROM z_count
WHERE count_month IN (201511,201512)
GROUP BY mid, aid
) last_zc
ON (zc.mid = last_zc.mid AND zc.aid = last_zc.aid AND zc.count_date = last_zc.count_date)
mid, aid が同じレコードのうち、2015年11月から12月までで count_date が一番新しいレコードを取り出す
※ count_date の MAX を取るための自己結合クエリ。実用的なクエリではありませんのであしからず
初めに考えた HiveQL の結合に関するアイデア
- INNER JOIN でチューニング
- 素のサンプルクエリ
- GROUP BY に CLUSTER BY mid を追加
- GROUP BY に CLUSTER BY mid, aid を追加
- 結合の左側と右側を入れ替え（件数が少ないサブクエリを左側に）
- 結合の左側と右側を入れ替えて、GROUP BY に CLUSTERED BY mid を追加
- LEFT SEMI JOIN に結合方法を変更してチューニング
- LEFT SEMI JOIN 結合
- LEFT SEMI JOIN 結合で GROUP BY に CLUSTER BY mid を追加
- LEFT SEMI JOIN 結合で GROUP BY に CLUSTER BY mid, aid を追加
- LEFT SEMI JOIN 結合で左側と右側を入れ替え（件数が少ないサブクエリを左側に）
- LEFT SEMI JOIN 結合の左側と右側を入れ替えて、GROUP BY に CLUSTERED BY mid を追加
前提条件
HiveQL クエリのチューニング検証時の前提条件を整理しておきます。| カストディアン | デフォルト | 拡張 |
|---|---|---|
| ERC20Proxy, ERC20Store ＜承認アクション＞ ・インプリ変更 ・カストディアン変更 | 48時間 | 168時間 |
| PrintLimiter ＜承認アクション＞ ・供給量上限引き上げ ・カストディアン変更 | 1時間 | 24時間 |
Hortonworks が出している Hadoop パッケージの HDP2.3.2 を利用、ORC + Snappy 圧縮のテーブルを検証に用いました。2億2千万件のレコードを準備、ORC + Snappy 圧縮済みで 2GB くらいのサイズです。
検証用にテストデータを準備したテーブル
CREATE TABLE z_count(
count_date BIGINT,
count_timestamp BIGINT,
mid INT,
aid STRING,
num_count BIGINT,
log_timestamp BIGINT)
PARTITIONED BY (count_month INT)
STORED AS ORC tblproperties ("orc.compress"="SNAPPY")
;
Hive のパラメータ
HDP2.3.2 のデフォルトに、以下の設定を追加してからテーブル作成。set hive.orc.splits.include.file.footer=true;
set hive.vectorized.execution.enabled=true;
統計情報
今回の検証にあたっては、対象のテーブルにカラム単位で統計情報を取得しました（以下のアナライズ文を使用）。ANALYZE TABLE xxxxx PARTITION(yyyyy) COMPUTE STATISTICS FOR COLUMNS;
初めに考えた HiveQL の検証と結果
最初にお断りしておきますと、ここは細かい記述が多くて長いですので、急ぐ方は「初めに考えた HiveQL 検証まとめ」をお読みください。限られた時間で色々試すため、当たりが付けば良かったので、システムがない状況で基本的に一回だけクエリを実行しました。
HiveQL 1-1 : サンプルクエリ
素のサンプルクエリINSERT OVERWRITE TABLE ttt
SELECT zc.mid, zc.aid, zc.count_date FROM z_count zc
INNER JOIN (
SELECT mid, aid, MAX(count_date) AS count_date FROM z_count
WHERE count_month IN (201511,201512)
GROUP BY mid, aid
) last_zc
ON (zc.mid = last_zc.mid AND zc.aid = last_zc.aid AND zc.count_date = last_zc.count_date)
実行計画
サンプルクエリの実行計画を見ると、GROUP BY のあるサブクエリで先に Map と Reducer の処理（Map1, Reducer2）が走り、それを待って元のテーブルの読み出し（Map4）と結合する処理（Reducer3）を行っています。なお、実行計画はすべて、ユーザーレベルの実行計画を参考にしました。
set hive.explain.user=true; ※HDP2.3.2 はデフォルトで true
Vertex dependency in root stage
Reducer 2 <- Map 1 (SIMPLE_EDGE)
Reducer 3 <- Map 4 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE)
Stage-3
Stats-Aggr Operator
Stage-0
Move Operator
table:{"name:":"ttt","input format:":"org.apache.hadoop.hive.ql.io.orc.OrcInputFormat","output format:":"org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat","serde:":"org.apache.hadoop.hive.ql.io.orc.OrcSerde"}
Stage-2
Dependency Collection{}
Stage-1
Reducer 3
File Output Operator [FS_14]
compressed:false
Statistics:Num rows: 29989739 Data size: 5158235202 Basic stats: COMPLETE Column stats: NONE
table:{"name:":"ttt","input format:":"org.apache.hadoop.hive.ql.io.orc.OrcInputFormat","output format:":"org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat","serde:":"org.apache.hadoop.hive.ql.io.orc.OrcSerde"}
Select Operator [SEL_13]
outputColumnNames:["_col0","_col1","_col2"]
Statistics:Num rows: 29989739 Data size: 5158235202 Basic stats: COMPLETE Column stats: NONE
Merge Join Operator [MERGEJOIN_20]
| condition map:[{"":"Inner Join 0 to 1"}]
| keys:{"0":"mid (type: int), aid (type: string), count_date (type: bigint)","1":"_col0 (type: int), _col1 (type: string), _col2 (type: bigint)"}
| outputColumnNames:["_col0","_col2","_col6"]
| Statistics:Num rows: 29989739 Data size: 5158235202 Basic stats: COMPLETE Column stats: NONE
|<-Map 4 [SIMPLE_EDGE]
| Reduce Output Operator [RS_28]
| key expressions:mid (type: int), aid (type: string), count_date (type: bigint)
| Map-reduce partition columns:mid (type: int), aid (type: string), count_date (type: bigint)
| sort order:+++
| Statistics:Num rows: 27263399 Data size: 4689304628 Basic stats: COMPLETE Column stats: NONE
| Filter Operator [FIL_27]
| predicate:((mid is not null and aid is not null) and count_date is not null) (type: boolean)
| Statistics:Num rows: 27263399 Data size: 4689304628 Basic stats: COMPLETE Column stats: NONE
| TableScan [TS_7]
| alias:zc
| Statistics:Num rows: 218107190 Data size: 37514436680 Basic stats: COMPLETE Column stats: NONE
|<-Reducer 2 [SIMPLE_EDGE]
Reduce Output Operator [RS_26]
key expressions:_col0 (type: int), _col1 (type: string), _col2 (type: bigint)
Map-reduce partition columns:_col0 (type: int), _col1 (type: string), _col2 (type: bigint)
sort order:+++
Statistics:Num rows: 14943611 Data size: 2570301092 Basic stats: COMPLETE Column stats: NONE
Filter Operator [FIL_25]
predicate:_col2 is not null (type: boolean)
Statistics:Num rows: 14943611 Data size: 2570301092 Basic stats: COMPLETE Column stats: NONE
Group By Operator [OP_24]
| aggregations:["max(VALUE._col0)"]
| keys:KEY._col0 (type: int), KEY._col1 (type: string)
| outputColumnNames:["_col0","_col1","_col2"]
| Statistics:Num rows: 14943611 Data size: 2570301092 Basic stats: COMPLETE Column stats: NONE
|<-Map 1 [SIMPLE_EDGE]
Reduce Output Operator [RS_4]
key expressions:_col0 (type: int), _col1 (type: string)
Map-reduce partition columns:_col0 (type: int), _col1 (type: string)
sort order:++
Statistics:Num rows: 29887223 Data size: 5140602356 Basic stats: COMPLETE Column stats: NONE
value expressions:_col2 (type: bigint)
Group By Operator [OP_23]
aggregations:["max(count_date)"]
keys:mid (type: int), aid (type: string)
outputColumnNames:["_col0","_col1","_col2"]
Statistics:Num rows: 29887223 Data size: 5140602356 Basic stats: COMPLETE Column stats: NONE
Select Operator [OP_22]
outputColumnNames:["mid","aid","count_date"]
Statistics:Num rows: 29887223 Data size: 5140602356 Basic stats: COMPLETE Column stats: NONE
Filter Operator [FIL_21]
predicate:(mid is not null and aid is not null) (type: boolean)
Statistics:Num rows: 29887223 Data size: 5140602356 Basic stats: COMPLETE Column stats: NONE
TableScan [TS_0]
alias:z_count
Statistics:Num rows: 119548890 Data size: 20562409080 Basic stats: COMPLETE Column stats: NONE
実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 28 28 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 491.11 s
※INSERT OVEWRITE の最後に行われる Loading data to table にかかる時間は除く。以下同様。サンプルクエリは８分強くらいのクエリ実行時間。
HiveQL 1-2 : GROUP BY + CLUSTER BY mid
INNER JOIN 結合で、GROUP BY に CLUSTER BY mid を追加したクエリINSERT OVERWRITE TABLE ttt
SELECT zc.mid, zc.aid, zc.count_date FROM z_count zc
INNER JOIN (
SELECT mid, aid, MAX(count_date) AS count_date FROM z_count
WHERE count_month IN (201511,201512)
GROUP BY mid, aid
CLUSTER BY mid
) last_zc
ON (zc.mid = last_zc.mid AND zc.aid = last_zc.aid AND zc.count_date = last_zc.count_date)
実行計画
サンプルクエリとほとんど変わらなかったですが、Map1 の Reduce Output Operator の「Map-reduce partition columns:_col0 (type: int)」で差異。CLUSTER BY に指定してない aid カラムの分がなくなっています。実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 28 28 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 480.04 s
クエリ実行時間もサンプルクエリとほぼ変わらない結果となりました。HiveQL 1-3 : GROUP BY + CLUSTER BY mid, aid
INNER JOIN 結合で、GROUP BY に CLUSTER BY mid, aid を追加したクエリINSERT OVERWRITE TABLE ttt
SELECT zc.mid, zc.aid, zc.count_date FROM z_count zc
INNER JOIN (
SELECT mid, aid, MAX(count_date) AS count_date FROM z_count
WHERE count_month IN (201511,201512)
GROUP BY mid, aid
CLUSTER BY mid, aid
) last_zc
ON (zc.mid = last_zc.mid AND zc.aid = last_zc.aid AND zc.count_date = last_zc.count_date)
実行計画
実行計画はサンプルクエリとほぼ一緒でした。実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 28 28 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 468.39 s
サンプルクエリとそれほどクエリ実行時間に差はなく、誤差の範囲と思われます。HiveQL 1-4 : 結合の左側と右側入れ替え
INNER JOIN 結合で、結合の左側と右側を入れ替え（件数が少ないサブクエリを左側にした）クエリINSERT OVERWRITE TABLE ttt
SELECT last_zc.mid, last_zc.aid, last_zc.count_date
FROM (
SELECT mid, aid, MAX(count_date) AS count_date FROM z_count
WHERE count_month IN (201511,201512)
GROUP BY mid, aid
) last_zc
INNER JOIN z_count zc
ON (last_zc.media_id = zc.media_id AND last_zc.article_id = zc.article_id AND last_zc.count_date_minute = zc.count_date_minute)
実行計画
処理の流れはサンプルクエリのときと変わらないですが、Reducer3 において元のテーブルに対する Select Operator がなくなっていました。実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 28 28 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 449.71 s
元のテーブルへの Select Operator がなくなったからなのか、ちょとだけ（？）速くなっている印象。誤差の範囲といえばそれまでな感じです。HiveQL 1-5 : 結合の左側と右側入れ替え & GROUP BY + CLUSTERED BY mid
INNER JOIN 結合で、結合の左側と右側を入れ替え（件数が少ないサブクエリを左側）と、GROUP BY に CLUSTER BY mid を追加したクエリINSERT OVERWRITE TABLE ttt
SELECT last_zc.mid, last_zc.aid, last_zc.count_date
FROM (
SELECT mid, aid, MAX(count_date) AS count_date FROM z_count
WHERE count_month IN (201511,201512)
GROUP BY mid, aid
CLUSTER BY mid
) last_zc
INNER JOIN z_count zc
ON (last_zc.media_id = zc.media_id AND last_zc.article_id = zc.article_id AND last_zc.count_date_minute = zc.count_date_minute)
実行計画
HiveQL 1-4 のように Reducer3 において元のテーブルに対する Select Operator がなくなったのと、HiveQL 1-2 のように Map1 の Reduce Output Operator の「Map-reduce partition columns:_col0 (type: int)」で差異が出ました。実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 28 28 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 454.12 s
HiveQL 1-4 と同じようなクエリ実行時間でした。HiveQL 2-1 : LEFT SEMI JOIN 結合
LEFT SEMI JOIN 結合に変えたクエリINSERT OVERWRITE TABLE ttt
SELECT zc.mid, zc.aid, zc.count_date
FROM z_count zc
LEFT SEMI JOIN (
SELECT mid, aid, MAX(count_date) AS count_date FROM z_count
WHERE count_month in (201511,201512)
GROUP BY mid, aid
) last_zc
ON (zc.mid = last_zc.mid AND zc.aid = last_zc.aid AND zc.count_date = last_zc.count_date)
実行計画
サンプルクエリの実行計画と流れは大体同じです。ただ、Reducer3 の Merge Join Operator が「condition map:[{“”:”Inner Join 0 to 1″}]」から「condition map:[{“”:”Left Semi Join 0 to 1″}]」に変わったのと、Reducer2 に MAX(count_date) を取得するのとは別の Group By Operator が増えました。実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 28 28 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 473.09 s
実行計画に幾らか変化が見られた割には、クエリ実行時間はあまり変わっていません。HiveQL 2-2 : LEFT SEMI JOIN 結合 & GROUP BY + CLUSTER BY mid
LEFT SEMI JOIN 結合で、GROUP BY に CLUSTER BY mid を追加したクエリINSERT OVERWRITE TABLE ttt
SELECT zc.mid, zc.aid, zc.count_date
FROM z_count zc
LEFT SEMI JOIN (
SELECT mid, aid, MAX(count_date) AS count_date FROM z_count
WHERE count_month in (201511,201512)
GROUP BY mid, aid
CLUSTER BY mid
) last_zc
ON (zc.mid = last_zc.mid AND zc.aid = last_zc.aid AND zc.count_date = last_zc.count_date)
実行計画
HiveQL 2-1 の実行計画とほとんど同じで、HiveQL 1-2 のように Map1 の Reduce Output Operator の「Map-reduce partition columns:_col0 (type: int)」だけ差異。実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 28 28 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 473.24 s
INNER JOIN のときとあまりクエリ実行時間は変わっていません。HiveQL 2-3 : LEFT SEMI JOIN 結合 & GROUP BY + CLUSTER BY mid, aid
LEFT SEMI JOIN 結合で、GROUP BY に CLUSTER BY mid, aid を追加したクエリINSERT OVERWRITE TABLE ttt
SELECT zc.mid, zc.aid, zc.count_date
FROM z_count zc
LEFT SEMI JOIN (
SELECT mid, aid, MAX(count_date) AS count_date FROM z_count
WHERE count_month in (201511,201512)
GROUP BY mid, aid
CLUSTER BY mid, aid
) last_zc
ON (zc.mid = last_zc.mid AND zc.aid = last_zc.aid AND zc.count_date = last_zc.count_date)
実行計画
実行計画は HiveQL 2-1 とほぼ一緒でした。実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 28 28 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 463.48 s
これも INNER JOIN のときとあまりクエリ実行時間は変わっていません。HiveQL 2-4 : LEFT SEMI JOIN 結合 & 結合の左側と右側入れ替え
LEFT SEMI JOIN 結合で、結合の左側と右側を入れ替えた（件数が少ないサブクエリを左側にした）クエリINSERT OVERWRITE TABLE ttt
SELECT last_zc.mid, last_zc.aid, last_zc.count_date
FROM (
SELECT mid, aid, MAX(count_date) AS count_date
FROM z_count
WHERE count_month in (201511,201512)
GROUP BY mid, aid
) last_pv
LEFT SEMI JOIN z_count zc
ON (last_zc.mid = zc.mid AND last_zc.aid = zc.aid AND last_zc.count_date = zc.count_date)
実行計画
HiveQL 2-1 の差異に加えて、Reducer3 において元のテーブルに対する Select Operator がなくなったのと、Map 4 に INNER JOIN のときにはなかった Group By Operator が増えています。実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 28 28 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 481.35 s
LEFT SEMI JOIN 結合で、結合の左側を結果件数の少ないサブクエリにした、何か変わってくるかなと期待したのですが、クエリ実行時間はあまり変化がありませんでした。HiveQL 2-5 : LEFT SEMI JOIN 結合 & 結合の左側と右側入れ替え & GROUP BY + CLUSTER BY mid
LEFT SEMI JOIN 結合で、結合の左側と右側を入れ替えて（件数が少ないサブクエリを左側にして）、GROUP BY に CLUSTER BY mid を追加したクエリクエリINSERT OVERWRITE TABLE ttt
SELECT last_zc.mid, last_zc.aid, last_zc.count_date
FROM (
SELECT mid, aid, MAX(count_date) AS count_date
FROM z_count
WHERE count_month in (201511,201512)
GROUP BY mid, aid
CLUSTER BY mid
) last_pv
LEFT SEMI JOIN z_count zc
ON (last_zc.mid = zc.mid AND last_zc.aid = zc.aid AND last_zc.count_date = zc.count_date)
実行計画
HiveQL 2-4 の実行計画とほとんど同じで、HiveQL 1-2 のように Map1 の Reduce Output Operator の「Map-reduce partition columns:_col0 (type: int)」だけ差異。実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 28 28 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 463.48 s
これも、クエリ実行時間が大きく変化することはありませんでした。初めに考えた HiveQL 検証まとめ
GROUP BY に CLUSTER BY を追加したり、結合の左側と右側を入れ替えてみたり、LEFT SEMI JOIN 結合を行ってみたり、これらの組み合わせを試した結果、あまり性能改善に繋がりませんでした。。ここまでやったら引き下がれないので、調べた中で効くかもしれないと考えた幾つかのパラメータを追加でいじってみました。
HDP2.3.2 ではデフォルトで false になっている以下のパラメータを true にして、検証のときに一応少しだけ速かった（誤算の範囲ですが）HiveQL 1-4 を実行してみました。
set hive.convert.join.bucket.mapjoin.tez=true;
set hive.auto.convert.sortmerge.join.to.mapjoin=true;
実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 28 28 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 455.13 s
が、この結果もそれほど大きな変化がありませんでした。他に
tez.runtime.pipelined.sorter.sort.threads 2 -> 4
に変えてみたり、
もしかしたら Hive on Tez にも何かしら影響するかもしれないと思って以下のパラメータを true にして、
set mapreduce.map.speculative=true;
set mapreduce.reduce.speculative=true;
それぞれ HiveQL 1-4 を実行しましたが、結果は特に変化はありませんでした。
次に考えた HiveQL の結合に関するアイデア
なかなか効果が出ないなかで思い悩んでいたところ、ふと思い出したのが、結合の左側と右側の両方の条件にパーティションキーの条件を加えてなかったということと、CLUSTER BY および SORT BY をテーブル側で定義してデータを作成してなかったことです。そこで、この二つをやってみるというのが次のアイデアです。
- 結合の左側と右側両方に、パーティションキーの条件を指定
- INNER JOIN 結合
- LEFT SEMI JOIN 結合
- テーブル定義に CLUSTERED BY, SORTED BY を追加
- SORTED BY (mid, aid) を定義
- INNER JOIN 結合
- LEFT SEMI JOIN 結合
- SORTED BY (mid, aid, count_date DESC) を定義
- INNER JOIN 結合で、GROUP BY のみ
- INNER JOIN 結合で、GROUP BY に DISTRIBUTE BY media_id SORT BY media_id, article_id, count_date_minute DESC を追加
- SORTED BY (mid, aid) を定義
次に考えた HiveQL の検証と結果
hiveQL 3-1 : 結合の両側でパーティションキーの条件指定
INNER JOIN 結合で、左側の元のテーブルにパーティションキーである月の条件を指定したクエリINSERT OVERWRITE TABLE ttt
SELECT zc.mid, zc.aid, zc.count_date FROM z_count zc
INNER JOIN (
SELECT mid, aid, MAX(count_date) AS count_date FROM z_count
WHERE count_month IN (201511,201512)
GROUP BY mid, aid
) last_zc
ON (zc.count_month in (201511,201512) AND
zc.mid = last_zc.mid AND zc.aid = last_zc.aid AND zc.count_date = last_zc.count_date)
実行計画
HiveQL 1-4 の実行計画と同じだが、Statistics の Num rows と Data size が大きく減りました。HiveQL 1-4 の実行計画との diff
16c16 < Statistics:Num rows: 29989739 Data size: 5158235202 Basic stats: COMPLETE Column stats: NONE --- > Statistics:Num rows: 16437973 Data size: 2827331450 Basic stats: COMPLETE Column stats: NONE 22c22 < | Statistics:Num rows: 29989739 Data size: 5158235202 Basic stats: COMPLETE Column stats: NONE --- > | Statistics:Num rows: 16437973 Data size: 2827331450 Basic stats: COMPLETE Column stats: NONE 28c28 < | Statistics:Num rows: 27263399 Data size: 4689304628 Basic stats: COMPLETE Column stats: NONE --- > | Statistics:Num rows: 14943612 Data size: 2570301263 Basic stats: COMPLETE Column stats: NONE 31c31 < | Statistics:Num rows: 27263399 Data size: 4689304628 Basic stats: COMPLETE Column stats: NONE --- > | Statistics:Num rows: 14943612 Data size: 2570301263 Basic stats: COMPLETE Column stats: NONE 34c34 < | Statistics:Num rows: 218107190 Data size: 37514436680 Basic stats: COMPLETE Column stats: NONE --- > | Statistics:Num rows: 119548890 Data size: 20562409080 Basic stats: COMPLETE Column stats: NONE
実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 52 52 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 256.82 s
クエリ実行時間が半分近く減るという結果に。これが一番大きな効果が出ました。hiveQL 3-2 : LEFT SEMI JOIN 結合 & 結合の両側でパーティションキーの条件指定
LEFT SEMI JOIN 結合で、左側の元のテーブルにパーティションキーである月の条件を指定したクエリINSERT OVERWRITE TABLE ttt
SELECT zc.mid, zc.aid, zc.count_date FROM z_count zc
LEFT SEMI JOIN (
SELECT mid, aid, MAX(count_date) AS count_date FROM z_count
WHERE count_month IN (201511,201512)
GROUP BY mid, aid
) last_zc
ON (zc.mid = last_zc.mid AND zc.aid = last_zc.aid AND zc.count_date = last_zc.count_date)
WHERE zc.count_month in (201511,201512)
実行計画
意外にもLEFT SEMI JOIN 結合で左側がサブクエリの HiveQL 2-4 よりも 左側が元テーブルの HiveQL 2-1 と実行計画同じで、Statistics の Num rows と Data size が大きく減りました。HiveQL 2-1 の実行計画との diff
14c14 < File Output Operator [FS_16] --- > File Output Operator [FS_17] 16c16 < Statistics:Num rows: 29989739 Data size: 5158235202 Basic stats: COMPLETE Column stats: NONE --- > Statistics:Num rows: 16437973 Data size: 2827331450 Basic stats: COMPLETE Column stats: NONE 18c18 < Select Operator [SEL_15] --- > Select Operator [SEL_16] 20,21c20,21 < Statistics:Num rows: 29989739 Data size: 5158235202 Basic stats: COMPLETE Column stats: NONE < Merge Join Operator [MERGEJOIN_22] --- > Statistics:Num rows: 16437973 Data size: 2827331450 Basic stats: COMPLETE Column stats: NONE > Merge Join Operator [MERGEJOIN_23] 25c25 < | Statistics:Num rows: 29989739 Data size: 5158235202 Basic stats: COMPLETE Column stats: NONE --- > | Statistics:Num rows: 16437973 Data size: 2827331450 Basic stats: COMPLETE Column stats: NONE 27c27 < | Reduce Output Operator [RS_27] --- > | Reduce Output Operator [RS_28] 31,32c31,32 < | Statistics:Num rows: 27263399 Data size: 4689304628 Basic stats: COMPLETE Column stats: NONE < | Filter Operator [FIL_26] --- > | Statistics:Num rows: 14943612 Data size: 2570301263 Basic stats: COMPLETE Column stats: NONE > | Filter Operator [FIL_27] 34c34 < | Statistics:Num rows: 27263399 Data size: 4689304628 Basic stats: COMPLETE Column stats: NONE --- > | Statistics:Num rows: 14943612 Data size: 2570301263 Basic stats: COMPLETE Column stats: NONE 37c37 < | Statistics:Num rows: 218107190 Data size: 37514436680 Basic stats: COMPLETE Column stats: NONE --- > | Statistics:Num rows: 119548890 Data size: 20562409080 Basic stats: COMPLETE Column stats: NONE 48c48 < Filter Operator [FIL_19] --- > Filter Operator [FIL_20] 63c63 < Group By Operator [OP_25] --- > Group By Operator [OP_26] 68c68 < Select Operator [OP_24] --- > Select Operator [OP_25] 71c71 < Filter Operator [FIL_23] --- > Filter Operator [FIL_24]
実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 52 52 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 279.89 s
HiveQL 3-1 よりクエリ実行時間が変わる可能性も期待しましたが、大体同じくらいのクエリ実行時間という結果に。hiveQL 4-1-1 : テーブル定義に CLUSTERED BY, SORTED BY 追加 & 結合の両側でパーティションキーの条件指定
テーブル定義に、CLUSTERED BY (mid) と SORTED BY (mid, aid) を追加し、バケットを８分割に。CREATE TABLE z_count(
count_date BIGINT,
count_timestamp BIGINT,
mid INT,
aid STRING,
num_count BIGINT,
log_timestamp BIGINT)
PARTITIONED BY (count_month INT)
CLUSTERED BY (mid) SORTED BY (mid, aid) INTO 8 BUCKETS
STORED AS ORC tblproperties ("orc.compress"="SNAPPY")
;
HiveQL 3-1 と同じクエリ実行計画
HiveQL 3-1 の実行計画と全く同じ。実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 39 39 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 226.49 s
実行計画は HiveQL 3-1 と同じだったものの、実際のクエリ実行時間ではさらに少し速くなりました。hiveQL 4-1-2 : テーブル定義に CLUSTERED BY, SORTED BY & LEFT SEMI JOIN 結合 & 結合の両側でパーティションキーの条件指定
テーブルは 4-1-1 と同じものを利用、HiveQL 3-2 と同じクエリ実行計画
HiveQL 3-1 の実行計画と全く同じ。実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 39 39 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間 ...... 267.51 s
今度は、HiveQL 3-2 と同じ実行計画で、ほぼ同じクエリ実行時間でした。hiveQL 4-2-1 : テーブル定義に CLUSTERED BY, SORTED BY（coun_date追加） & 結合の両側でパーティションキーの条件指定
テーブル定義に、CLUSTERED BY (mid) と SORTED BY (mid, aid, count_date DESC) を追加し、バケットを８分割に。CREATE TABLE z_count(
count_date BIGINT,
count_timestamp BIGINT,
mid INT,
aid STRING,
num_count BIGINT,
log_timestamp BIGINT)
PARTITIONED BY (count_month INT)
CLUSTERED BY (mid) SORTED BY (mid, aid, count_date DESC) INTO 8 BUCKETS
STORED AS ORC tblproperties ("orc.compress"="SNAPPY")
;
HiveQL 3-1 と同じクエリ実行計画
HiveQL 3-1 の実行計画と同じ。実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 39 39 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間（１回目） ...... 204.58 s
クエリ実行時間（２回目） ...... 204.93 s
実行計画は HiveQL 3-1 と同じだったものの、実際のクエリ実行時間では１分近く速くなりました。最も速い結果のため、ここは２回行いましたが、やっぱり速いという結果でした。（追加検証）hiveQL 4-2-1 + INSERT INTO による差分追加
4-2-1 の z_count テーブルに以下の distribute by, sort by をつけた insert into クエリを実行し、小さいバケットファイルを増やしたとき（INTO 8 BUCKETS と定義したので、１回の insert into につき８バケットファイル増加）の性能をついでに測定した。insert into table z_count partition(count_date_month)
select
count_date,
count_timestamp,
mid + 100000 as mid,
aid,
num_count,
log_timestamp
from xxx distribute by mid sort by mid, aid, count_date desc limit 10000
;
insert into table z_count partition(count_date_month)
select
count_date,
count_timestamp,
mid + 200000 as mid,
aid,
num_count,
log_timestamp
from xxx distribute by mid sort by mid, aid, count_date desc limit 10000
;
HiveQL 3-1 と同じクエリ実行計画
HiveQL 3-1 の実行計画と同じだが、差分でレコードが追加された分、Statistics の Num rows と Data size が若干変化した。HiveQL 3-1 の実行計画との diff
16c16 < Statistics:Num rows: 16437973 Data size: 2827331450 Basic stats: COMPLETE Column stats: NONE --- > Statistics:Num rows: 16439865 Data size: 2827656875 Basic stats: COMPLETE Column stats: NONE 22c22 < | Statistics:Num rows: 16437973 Data size: 2827331450 Basic stats: COMPLETE Column stats: NONE --- > | Statistics:Num rows: 16439865 Data size: 2827656875 Basic stats: COMPLETE Column stats: NONE 28c28 < | Statistics:Num rows: 14943612 Data size: 2570301263 Basic stats: COMPLETE Column stats: NONE --- > | Statistics:Num rows: 14945332 Data size: 2570597104 Basic stats: COMPLETE Column stats: NONE 31c31 < | Statistics:Num rows: 14943612 Data size: 2570301263 Basic stats: COMPLETE Column stats: NONE --- > | Statistics:Num rows: 14945332 Data size: 2570597104 Basic stats: COMPLETE Column stats: NONE 34c34 < | Statistics:Num rows: 119548890 Data size: 20562409080 Basic stats: COMPLETE Column stats: NONE --- > | Statistics:Num rows: 119562650 Data size: 20564775800 Basic stats: COMPLETE Column stats: NONE 40c40 < Statistics:Num rows: 14943611 Data size: 2570301092 Basic stats: COMPLETE Column stats: NONE --- > Statistics:Num rows: 14945331 Data size: 2570596932 Basic stats: COMPLETE Column stats: NONE 43c43 < Statistics:Num rows: 14943611 Data size: 2570301092 Basic stats: COMPLETE Column stats: NONE --- > Statistics:Num rows: 14945331 Data size: 2570596932 Basic stats: COMPLETE Column stats: NONE 48c48 < | Statistics:Num rows: 14943611 Data size: 2570301092 Basic stats: COMPLETE Column stats: NONE --- > | Statistics:Num rows: 14945331 Data size: 2570596932 Basic stats: COMPLETE Column stats: NONE 54c54 < Statistics:Num rows: 29887223 Data size: 5140602356 Basic stats: COMPLETE Column stats: NONE --- > Statistics:Num rows: 29890663 Data size: 5141194036 Basic stats: COMPLETE Column stats: NONE 60c60 < Statistics:Num rows: 29887223 Data size: 5140602356 Basic stats: COMPLETE Column stats: NONE --- > Statistics:Num rows: 29890663 Data size: 5141194036 Basic stats: COMPLETE Column stats: NONE 63c63 < Statistics:Num rows: 29887223 Data size: 5140602356 Basic stats: COMPLETE Column stats: NONE --- > Statistics:Num rows: 29890663 Data size: 5141194036 Basic stats: COMPLETE Column stats: NONE 66c66 < Statistics:Num rows: 29887223 Data size: 5140602356 Basic stats: COMPLETE Column stats: NONE --- > Statistics:Num rows: 29890663 Data size: 5141194036 Basic stats: COMPLETE Column stats: NONE 68,69c68,69 < alias:z_count < Statistics:Num rows: 119548890 Data size: 20562409080 Basic stats: COMPLETE Column stats: NONE --- > alias:z_count > Statistics:Num rows: 119562650 Data size: 20564775800 Basic stats: COMPLETE Column stats: NONE
実行結果
--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 3 3 0 0 0 0
Map 4 .......... SUCCEEDED 3 3 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 39 39 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間（１回目） ...... 212.57 s
クエリ実行時間（２回目） ...... 215.21 s
小さいバケットファイルでできるような差分追加しても、それほどではないものの、少しだけ遅くなりました。hiveQL 4-2-2 : テーブル定義に CLUSTERED BY, SORTED BY（coun_date追加） & 結合の両側でパーティションキーの条件指定
テーブルは 4-2-1 と同じものを利用し、HiveQL 3-1 のクエリの GROUP BY に DISTRIBUTE BY mid SORT BY mid, aid, count_date DESC をつけたクエリINSERT OVERWRITE TABLE ttt
SELECT zc.mid, zc.aid, zc.count_date FROM z_count zc
INNER JOIN (
SELECT mid, aid, MAX(count_date) AS count_date FROM z_count
WHERE count_month IN (201511,201512)
GROUP BY mid, aid
DISTRIBUTE BY mid SORT BY mid, aid, count_date DESC
) last_zc
ON (zc.count_month in (201511,201512) AND
zc.mid = last_zc.mid AND zc.aid = last_zc.aid AND zc.count_date = last_zc.count_date)
実行計画
Reducer4 が増えました。Vertex dependency in root stage
Reducer 2 <- Map 1 (SIMPLE_EDGE)
Reducer 3 <- Reducer 2 (SIMPLE_EDGE)
Reducer 4 <- Map 5 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE)
Stage-3
Stats-Aggr Operator
Stage-0
Move Operator
table:{"name:":"gmo_rw.ttt","input format:":"org.apache.hadoop.hive.ql.io.orc.OrcInputFormat","output format:":"org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat","serde:":"org.apache.hadoop.hive.ql.io.orc.OrcSerde"}
Stage-2
Dependency Collection{}
Stage-1
Reducer 4
File Output Operator [FS_16]
compressed:false
Statistics:Num rows: 16437973 Data size: 2827331450 Basic stats: COMPLETE Column stats: NONE
table:{"name:":"gmo_rw.ttt","input format:":"org.apache.hadoop.hive.ql.io.orc.OrcInputFormat","output format:":"org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat","serde:":"org.apache.hadoop.hive.ql.io.orc.OrcSerde"}
Merge Join Operator [MERGEJOIN_22]
| condition map:[{"":"Inner Join 0 to 1"}]
| keys:{"0":"_col0 (type: int), _col1 (type: string), _col2 (type: bigint)","1":"media_id (type: int), article_id (type: string), count_date_minute (type: bigint)"}
| outputColumnNames:["_col0","_col1","_col2"]
| Statistics:Num rows: 16437973 Data size: 2827331450 Basic stats: COMPLETE Column stats: NONE
|<-Map 5 [SIMPLE_EDGE]
| Reduce Output Operator [RS_32]
| key expressions:media_id (type: int), article_id (type: string), count_date_minute (type: bigint)
| Map-reduce partition columns:media_id (type: int), article_id (type: string), count_date_minute (type: bigint)
| sort order:+++
| Statistics:Num rows: 14943612 Data size: 2570301263 Basic stats: COMPLETE Column stats: NONE
| Filter Operator [FIL_31]
| predicate:((media_id is not null and article_id is not null) and count_date_minute is not null) (type: boolean)
| Statistics:Num rows: 14943612 Data size: 2570301263 Basic stats: COMPLETE Column stats: NONE
| TableScan [TS_9]
| alias:pvc
| Statistics:Num rows: 119548890 Data size: 20562409080 Basic stats: COMPLETE Column stats: NONE
|<-Reducer 3 [SIMPLE_EDGE]
Reduce Output Operator [RS_30]
key expressions:_col0 (type: int), _col1 (type: string), _col2 (type: bigint)
Map-reduce partition columns:_col0 (type: int), _col1 (type: string), _col2 (type: bigint)
sort order:+++
Statistics:Num rows: 14943611 Data size: 2570301092 Basic stats: COMPLETE Column stats: NONE
Select Operator [OP_29]
| outputColumnNames:["_col0","_col1","_col2"]
| Statistics:Num rows: 14943611 Data size: 2570301092 Basic stats: COMPLETE Column stats: NONE
|<-Reducer 2 [SIMPLE_EDGE]
Reduce Output Operator [RS_28]
key expressions:_col0 (type: int), _col1 (type: string), _col2 (type: bigint)
Map-reduce partition columns:_col0 (type: int)
sort order:++-
Statistics:Num rows: 14943611 Data size: 2570301092 Basic stats: COMPLETE Column stats: NONE
Filter Operator [FIL_27]
predicate:_col2 is not null (type: boolean)
Statistics:Num rows: 14943611 Data size: 2570301092 Basic stats: COMPLETE Column stats: NONE
Group By Operator [OP_26]
| aggregations:["max(VALUE._col0)"]
| keys:KEY._col0 (type: int), KEY._col1 (type: string)
| outputColumnNames:["_col0","_col1","_col2"]
| Statistics:Num rows: 14943611 Data size: 2570301092 Basic stats: COMPLETE Column stats: NONE
|<-Map 1 [SIMPLE_EDGE]
Reduce Output Operator [RS_4]
key expressions:_col0 (type: int), _col1 (type: string)
Map-reduce partition columns:_col0 (type: int), _col1 (type: string)
sort order:++
Statistics:Num rows: 29887223 Data size: 5140602356 Basic stats: COMPLETE Column stats: NONE
value expressions:_col2 (type: bigint)
Group By Operator [OP_25]
aggregations:["max(count_date_minute)"]
keys:media_id (type: int), article_id (type: string)
outputColumnNames:["_col0","_col1","_col2"]
Statistics:Num rows: 29887223 Data size: 5140602356 Basic stats: COMPLETE Column stats: NONE
Select Operator [OP_24]
outputColumnNames:["media_id","article_id","count_date_minute"]
Statistics:Num rows: 29887223 Data size: 5140602356 Basic stats: COMPLETE Column stats: NONE
Filter Operator [FIL_23]
predicate:(media_id is not null and article_id is not null) (type: boolean)
Statistics:Num rows: 29887223 Data size: 5140602356 Basic stats: COMPLETE Column stats: NONE
TableScan [TS_0]
alias:zzzclustered2_article_pv_interval_count
Statistics:Num rows: 119548890 Data size: 20562409080 Basic stats: COMPLETE Column stats: NONE
<pre>
<h4>実行結果</h4>
<pre class="brush: diff; gutter: false">--------------------------------------------------------------------------------
VERTICES STATUS TOTAL COMPLETED RUNNING PENDING FAILED KILLED
--------------------------------------------------------------------------------
Map 1 .......... SUCCEEDED 4 4 0 0 0 0
Map 5 .......... SUCCEEDED 4 4 0 0 0 0
Reducer 2 ...... SUCCEEDED 20 20 0 0 0 0
Reducer 3 ...... SUCCEEDED 10 10 0 0 0 0
Reducer 4 ...... SUCCEEDED 20 20 0 0 0 0
--------------------------------------------------------------------------------
クエリ実行時間（１回目） ...... 204.15 s
クエリ実行時間（２回目） ...... 205.23 s
実行計画が今回の検証で唯一、Reducerが増えて変わったが、性能は HiveQL 4-2-1 と変わらなかった。今回の HiveQL チューニング結果のまとめ
今回の HiveQL の一番のポイントは、次の二つ。- 結合するときは、テーブルのパーティションのキーによる絞り込み条件を、左側と右側の両方でつけることをまず考える
- GROUP BY して MAX() するようなときは、対象となるテーブルを作るときに、CLUSTERED BY, SORTED BY の定義をつけて予めソートされた状態を作っておき、Hive の処理量を減らすことを考える
最後に
次世代システム研究室では、アプリケーション開発や設計を行うアーキテクトを募集しています。アプリケーション開発者の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ 募集職種一覧 からご応募をお願いします。皆さんのご応募をお待ちしています。
