BigQueryのUDFの落とし穴 #SQL

はじめに

自分はBigQueryのSQLを書いてる時はあまりパフォーマンスを気にしていない。
というのも大抵が一瞬で結果を返してくれるし、頑張って工夫しても結果的に返ってくる速度に大差がないからである。(厳密にはあるのかもしれないが誤差の範囲でしか無い)
SQLを書く際に気にしているのはいつも 参照するデータ量(=課金額) である。

ただ、ついこ先日ユーザ定義関数(UDF)を利用したSQLで明確なパフォーマンス差が出てしまったのでメモ。

問題のSQL

CREATE TEMPORARY FUNCTION  parseCode(x STRING)
RETURNS INT64
LANGUAGE js AS """
  var result = 0
  // 適当な処理
  return result;
""";
CREATE TEMPORARY FUNCTION codeLists() AS (
  ['code-111333', 'code-222444', 'code-333555']
);

WITH codes AS (
  SELECT
    parseCode(hoge) AS code
  FROM
    UNNEST (codeLists()) AS hoge
)

SELECT
  *
FROM
  `dataset.tableB` 
WHERE
  code IN (SELECT * FROM codes)

※ わかりやすいように実際のSQLを短くしている

やってることは至極単純で、ある一定の法則で生成されている code を適当なパース処理を加えてINT64型に直した後、検索したいテーブルに対して WHERE句 に SELECT IN で指定しているだけ。
この際、tableBのデータ量はたかだか120万件程度だったにも関わらず3分待っても結果は返ってこなかった。

解決方法

1. そもそもUDF使わない

UDFを書いている理由が特に無いならわざわざ挟む必要はない。
今回で言うならはじめから codeLists をINT64型の配列データにしておいてそれを直接渡してやればいいだけだった。
こうするだけでものの10秒で結果を返してくれるようになった。

2. INNER JOINを使う

SELECT IN がパフォーマンス的に問題あるのは普通のRDBSに対してSQLを書いてる人なら聞いたことはあるはず。
ついつい楽な書き方なので SELECT IN を書いてしまいがちだが、普通に INNER JOIN を使えばUDFを挟んでいてもパフォーマンスの劣化は起きない。

SELECT
  *
FROM
  `dataset.tableB`
INNER JOIN codes USING (code)

感想

BigQueryを利用し始めて結構経ったが明確にここまでパフォーマンス差が出たのは初めてだったので驚いてしまった。
とはいえ、BigQueryに慣れてしまいパフォーマンスに対する意識が薄れてしまったというのが今回の最大の問題なのかもしれない。