2016.01.06

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 の結合に関するアイデア

  1. INNER JOIN でチューニング
    1. 素のサンプルクエリ
    2. GROUP BY に CLUSTER BY mid を追加
    3. GROUP BY に CLUSTER BY mid, aid を追加
    4. 結合の左側と右側を入れ替え(件数が少ないサブクエリを左側に)
    5. 結合の左側と右側を入れ替えて、GROUP BY に CLUSTERED BY mid を追加
  2. LEFT SEMI JOIN に結合方法を変更してチューニング
    1. LEFT SEMI JOIN 結合
    2. LEFT SEMI JOIN 結合で GROUP BY に CLUSTER BY mid を追加
    3. LEFT SEMI JOIN 結合で GROUP BY に CLUSTER BY mid, aid を追加
    4. LEFT SEMI JOIN 結合で左側と右側を入れ替え(件数が少ないサブクエリを左側に)
    5. LEFT SEMI JOIN 結合の左側と右側を入れ替えて、GROUP BY に CLUSTERED BY mid を追加
サブクエリの中で GROUP BY を行うカラムに対して、CLUSTER BY も行ったら reducer を効率良く使ってくれたりしないものかと期待したり、LEFT SEMI JOIN を上手く使えば速くならないかということを期待したのが、上記のアイデアです。

前提条件

HiveQL クエリのチューニング検証時の前提条件を整理しておきます。

Hive on Tez クエリ検証環境

項目補足
HadoopパッケージHDP2.3.2HortonworksのHadoop
HiveクエリエンジンTez
YARNスケジューラーキャパシティスケジューラー
スレーブノード数4台
利用可能なCPU6~13個スレーブノード4台合計
利用可能なメモリ15~30GBスレーブノード4台合計
テーブル件数約2億2千万件z_countテーブル
テーブルサイズ約2GBSnappy圧縮
テーブルフォーマットORC
クエリ結果(件数)約560万件mid, aidごとに最新の
count_dateのレコード数

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 にかかる時間は除く。以下同様。

サンプルクエリは8分強くらいのクエリ実行時間。

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 をテーブル側で定義してデータを作成してなかったことです。
そこで、この二つをやってみるというのが次のアイデアです。

  1. 結合の左側と右側両方に、パーティションキーの条件を指定
    1. INNER JOIN 結合
    2. LEFT SEMI JOIN 結合
  2. テーブル定義に CLUSTERED BY, SORTED BY を追加
    1. SORTED BY (mid, aid) を定義
      1. INNER JOIN 結合
      2. LEFT SEMI JOIN 結合
    2. SORTED BY (mid, aid, count_date DESC) を定義
      1. INNER JOIN 結合で、GROUP BY のみ
      2. INNER JOIN 結合で、GROUP BY に DISTRIBUTE BY media_id SORT BY media_id, article_id, count_date_minute DESC を追加

次に考えた 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) を追加し、バケットを8分割に。
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) を追加し、バケットを8分割に。
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
--------------------------------------------------------------------------------
クエリ実行時間(1回目) ......   204.58 s
クエリ実行時間(2回目) ......   204.93 s
実行計画は HiveQL 3-1 と同じだったものの、実際のクエリ実行時間では1分近く速くなりました。最も速い結果のため、ここは2回行いましたが、やっぱり速いという結果でした。

(追加検証)hiveQL 4-2-1 + INSERT INTO による差分追加

4-2-1 の z_count テーブルに以下の distribute by, sort by をつけた insert into クエリを実行し、小さいバケットファイルを増やしたとき(INTO 8 BUCKETS と定義したので、1回の insert into につき8バケットファイル増加)の性能をついでに測定した。
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
--------------------------------------------------------------------------------
クエリ実行時間(1回目) ......   212.57 s
クエリ実行時間(2回目) ......   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
--------------------------------------------------------------------------------
クエリ実行時間(1回目) ......   204.15 s
クエリ実行時間(2回目) ......   205.23 s
実行計画が今回の検証で唯一、Reducerが増えて変わったが、性能は HiveQL 4-2-1 と変わらなかった。

今回の HiveQL チューニング結果のまとめ


今回の HiveQL の一番のポイントは、次の二つ。
  1. 結合するときは、テーブルのパーティションのキーによる絞り込み条件を、左側と右側の両方でつけることをまず考える
  2. GROUP BY して MAX() するようなときは、対象となるテーブルを作るときに、CLUSTERED BY, SORTED BY の定義をつけて予めソートされた状態を作っておき、Hive の処理量を減らすことを考える

INTO n BUCKETS のバケット分割数をどうするかは、テーブルにどうやってデータを溜めるかによりますが、INSERT INTO で比較的小さなデータを差分で追加する運用の場合は、たくさんファイルが出来てしまうので、バケット分割数を小さくしたほうが良さそうです。

最後に

次世代システム研究室では、アプリケーション開発や設計を行うアーキテクトを募集しています。アプリケーション開発者の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ 募集職種一覧 からご応募をお願いします。

皆さんのご応募をお待ちしています。