自分がなんとなくBigQueryのクエリを書いていてハマった罠について列挙しておきます。ドキュメントをちゃんと読めば書いてあったりするのですが、普段はそこまで細かく見てなかったりするんですよね……。

BigQueryのカレンダー | Advent Calendar 2023 - Qiita の16日目の記事です。

CAST(value AS INT64) は切り捨てではない

他のプログラミング言語などをやっているとなんとなく整数型にキャストすると切り捨てのような気がしてしまいますがBigQueryは違います。四捨五入的な挙動になります。

SELECT CAST(1.5 AS INT64)
-- => 2

Returns the closest integer value. Halfway cases such as 1.5 or -0.5 round away from zero. https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions#cast_as_integer

DATE_DIFF(date1, date2, YEAR) の値が増えるのは1年後ではない

https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date_diff

1年後ではないというと語弊がありますが、DATE_DIFFでは差を計算したい部分の差を出してるだけっぽいので大晦日と元旦を比べると1日しか経ってないのにDATE_DIFF(date1, date2, YEAR) の値は1になります。

SELECT DATE_DIFF('2024-01-01', '2023-12-31', YEAR);
-- => 1

ちゃんと365日ぐらい経過したかどうかを判定したければ日数を見たほうがよさそうです。

同じWITH句を一つのクエリ内で複数回参照してもそれぞれ違う結果になる

BigQueryではWITH句を使ってサブクエリに名前を付けて扱うことができます。同じ名前のWITH句を参照した場合同じ結果になるのを期待したくなりますが、それぞれ個別に実行されるので乱数やランダムな順序でSELECTしてLIMITした結果などはそれぞれ別々になります。

WITH x AS (
  SELECT RAND() AS r
)

SELECT *
FROM x
UNION ALL
SELECT *
FROM x
-- => 同じWITH句のxを参照しているのに1つ目のxと2つ目のxで結果が異なる

対策としてはWITH句ではなくCREATE TEMP TABLEで一旦テーブルを作ってしまってそれを参照したり、少しトリッキーな方法だとWITH RECURSIVEを使うものがあります。 WITH RECURSIVE は再帰的なWITH句を書くための記法ですが、これを使うとWITH句の結果がマテリアライズされるので一旦テーブルを作るのと同様の効果があります。

WITH RECURSIVE x AS (
  SELECT RAND() AS r
  UNION ALL
  SELECT * FROM x WHERE FALSE
)

SELECT *
FROM x
UNION ALL
SELECT *
FROM x
-- => WITH RECURSIVEを使うとWITH句の結果がマテリアライズされるので等しくなる

NULL との比較はNULL(falsy)になる。特にNOT IN の挙動がわかりづらい

BigQueryに限った話ではないですが値がNULLの時に!=などでそれと比較をすると結果もNULLになってしまいWHEREの部分で意図せずfalsyになって困ることがあります。特にvalue NOT IN (values) の挙動はわかりづらくvaluesにNULLが含まれている場合、valueがvaluesに含まれていればFALSE、含まれていなければNULLを返し、つまり常にTRUEではないfalsyな結果になってWHEREなどに書いた場合条件を満たすことはありません。

SELECT
  1 NOT IN (1, NULL, 2), -- => FALSE
  2 NOT IN (1, NULL, 2), -- => FALSE
  3 NOT IN (1, NULL, 2), -- => NULL

上の例ぐらいなら考えれば気付けると思うのですが、valuesの部分がサブクエリになっていたりするとNULLが含まれていることまで頭が回らずうっかりしがちです。

, UNNEST([]) するとその行が消える

BigQueryでは配列を展開するためのUNNEST(ARRAY)という記法があり、, UNNEST(ARRAY) とすると配列のそれぞれの要素をCROSS JOINできます。

WITH x AS (
  SELECT 1 AS n
)

SELECT
  *
FROM x, UNNEST([1, 2, 3]) AS i
-- 以下の結果が得られる
-- 1, 1
-- 1, 2
-- 1, 3

この時ARRAYが空配列だと空のデータとJOINしようとしたことになり対応するxのデータごと消えてしまいます。うっかりこの挙動を失念しているとあるはずのデータがない状態になって困ります。

WITH x AS (
  SELECT 1 AS n
)

SELECT
  *
FROM x, UNNEST([]) AS i
-- => 空配列をUNNESTしたものをCROSS JOINするとxの方ごと消える

この場合LEFT OUTER JOINをすればxの方は消えずにJOINした方にはNULLが入って大丈夫になります。

WITH x AS (
  SELECT 1 AS n
)

SELECT
  *
FROM x
LEFT OUTER JOIN UNNEST([]) AS i
-- 以下の結果が得られる
-- 1, NULL

IN UNNEST(ARRAY) ができる

これはあんまり罠というわけではないですが、サブクエリをかかずとも IN に UNNEST(ARRAY) を指定すれば配列に含まれるかどうかが判定できます。最初はサブクエリを書く必要があるのかと思っていました。

[NULL] が作れる

BigQueryではクエリの最終結果にNULLを含む配列があるとエラーになります。しかし上のNOT IN の例にも出てきましたがクエリの途中結果では配列にNULLが含まれていても特にエラーにはなりません。

SELECT [NULL] AS x
-- Array cannot have a null element; error in writing field x というエラーが出る

テーブル名に.が連続してても動く

これは完全にトリビアな話ですが、BigQueryのテーブル名の指定ではプロジェクト名やデータセット名のあとに . を付けますが、この . はなぜか複数ついていてもエラーなどにはならず普通に動くようです。文字列的にテーブル名を検索したらなぜかtypoで.が多くて見つからないということがありえます。

SELECT * FROM `bigquery-public-data...........github_repos...................languages` LIMIT 1000

唯物是真 @Scaled_Wurm

プログラミング(主にPython2.7)とか機械学習とか

BigQueryでクエリを書いたときにハマった罠集