Help us understand the problem. What is going on with this article?

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つのテーブルtable1table2を用いて、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を使った場合とのパフォーマンスを、簡易的ではありますが比較しました。

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

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

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした