HiveでNOT EXISTSの代わりにLEFT OUTER JOINを使ってみる

この記事はMicroAd Advent Calendar 2019の10日目の記事です。

はじめに

MySQLなどのクエリで集合差を表現する方法にNOT EXISTSがあります。NOT INを使うよりはマシですが、この処理のパフォーマンスが気になる場面があり、以下の記事にあるようなLEFT OUTER JOINを使う方法を知りました。

参考：MySQLで集合差を出す

例えば、NOT EXISTSを含む次のような「ブラックリストに入っていないメンバーの情報を取得する」クエリ

SELECT
  id
  ,name
FROM
  member
WHERE NOT EXISTS (
  SELECT
    id
  FROM
    black_list
  WHERE
    member.id = black_list.id
  LIMIT 1
)

を

SELECT
  id
  ,name
FROM
  member
LEFT OUTER JOIN black_list
ON member.id = black_list.id
WHERE
  black_list.id IS NULL

のように表現することができ、MySQLではこちらの方が高速であるケースが多いようです。

業務でHiveを使うことがあり、Hiveの場合はパフォーマンスにどの程度差が出るのかを簡単に調べてみました。

検証

Hive上の2つのテーブルtable1とtable2を用いて、2つの記法の所要時間を比較しました。レコード数は1億5000万件ほど用意し、集合差の大きさによる所要時間の違いも検証しました。

• 集合差0: table1, table2が完全一致で、集合差が0
• 集合差10%: table2にはtable1のレコードの10%だけが入っている
• 集合差50%: table2にはtable1のレコードの半分が入っている
• 集合差90%: table2にはtable1のレコードの90%が入っている
• 集合差100%: table2が空で、集合差がtable1と完全一致

なお、CDHのバージョンは5.14で、テーブルの圧縮形式はParquetです。

NOT EXISTSを使う場合

SELECT
  id
FROM table1
WHERE NOT EXISTS (
  SELECT
    id
  FROM table2
  WHERE table1.id = table2.id
)

• 集合差0: 97 (sec)
• 集合差10%: 97 (sec)
• 集合差50%: 99 (sec)
• 集合差90%: 100 (sec)
• 集合差100%: 97 (sec)

LEFT OUTER JOINを使う場合

SELECT
  id
FROM table1
LEFT OUTER JOIN table2
ON table1.id = table2.id
WHERE table2.id IS NULL

• 集合差0: 99 (sec)
• 集合差10%: 96 (sec)
• 集合差50%: 101 (sec)
• 集合差90%: 100 (sec)
• 集合差100%: 98 (sec)

結果

今回の検証では、この2つの記法と集合差の大きさによるパフォーマンスに明確な差は見られませんでした。

NOT EXISTSはあまり速くないというイメージがありましたが、意外とそんなことはないのかもしれません。

おわりに

HiveでのNOT EXISTSと、その代わりにLEFT OUTER JOINを使った場合とのパフォーマンスを、簡易的ではありますが比較しました。

何らかの参考になれば幸いです。

より良い記法や検証方法などがありましたら、ご指摘いただけますと有難いです。