HiveQL で Common Table Expressions (CTE a.k.a. WITH 句) を利用する際に注意すべきこと

HiveQL 弱者が Hive における WITH 句の実装を知らずに利用したら痛い目に遭いそうになったので、メモを遺しておきます。

はじめに

SQL でサブクエリが多段になったり FROM 句で UNION ALL する類のちょっと複雑な集計クエリを書こうとしたときに、WITH 句で同じようなサブクエリをひと纏めにし、あたかもテンポラリテーブルのように扱ってクエリの見通しを良くしたり、(RDBMS にもよりますが) あわよくばクエリのパフォーマンス改善も狙う、なんてのはよくあることだと思います。

さて HiveQL では WITH 句をサポートしているのか？ というと、HIVE-1180 を参照するに、Hive 0.13.0 より対応しているようです。利用方法は こちら にあるとおりで、SQL の WITH 句と大差ないことがわかります。

ただ、Hive における CTE の実装を理解しないままに下手に WITH 句を使ってしまうと、クエリパフォーマンスをひどく悪化させる状況を引き起こしかねません。そういうわけで、現時点での Hive の CTE の実装がどのようになっているのかを見ておくことにします。

Hive における CTE の実装

最初に CTE が導入された時点では、HHIVE-1180 の issue の コメント

As a first step have converted references to a CTE in the Query into a SubQuery invocation.

にあるとおり、CTE を参照している箇所をそのサブクエリで置き換える形で実装されたようです。

これはすなわち、「WITH 句に書かれたサブクエリを一度評価したら、その結果一時的に保持しておいて、同じ CTE を参照している別のところで再利用する」のではなく「CTE を参照した箇所にサブクエリを差し込んで、都度評価される」という処理が行われることになります。

そして、この実装は現在も引き継がれていて、 SemanticAnalyzer.java#L1069-L1088 や QB.java#L254-L256 あたりでそれっぽいものが確認できます (Hive のコードは初見なので、ここで本当に正しいのか、いまいち自信は持てませんが…)。

パフォーマンス上の問題が生じそうな WITH 句の使い方

上記のとおり、現時点での Hive における CTE の実装は、WITH 句に書かれたサブクエリをその参照先で置き換え、その評価も都度行われる、というものとなっています。

そのため、以下のような、WITH 句を利用しつつ SELECT … UNION ALL を繰り返した結果をテーブルに突っ込む INSERT ステートメントのように、重いサブクエリとなる CTE を複数の箇所で参照している場合は、都度重い CTE のサブクエリが評価されてしまい、結果として非常に重いクエリの出来上がり… となってしまいます。

WITH heavy_subquery AS (
  -- とっても重いサブクエリ
  SELECT ... FROM ... WHERE ... GROUP BY ...
)
INSERT OVERWRITE TABLE destination
-- heavy_subquery に対して SELECT リストや GROUP BY の指定がちょっとずつ異なるクエリを連ねる
SELECT ... FROM heavy_subquery WHERE ... GROUP BY ...
UNION ALL
SELECT ... FROM heavy_subquery WHERE ... GROUP BY ...
UNION ALL
SELECT ... FROM heavy_subquery WHERE ... GROUP BY ...
UNION ALL
SELECT ... FROM heavy_subquery WHERE ... GROUP BY ...
UNION ALL
SELECT ... FROM heavy_subquery WHERE ... GROUP BY ...

まとめ

• HiveQL でも WITH 句は使えるよ (ver. 0.13.0 以降)
• ただし、現時点での CTE の実装はその参照をサブクエリに置き換えるもののままだよ
• 重いサブクエリを WITH 句に書いて、それを複数箇所 (2箇所以上) で参照するとクエリのパフォーマンスが悪化してしまうよ