BigQueryでのSTRUCT型を伴うSELECT文での重複行の削除についてのメモ
まず、前提として、BigQUeryにはunique constraint的な機能が存在しない。なので、あれこれと頑張ってユニーク性を保つ作業をしたくなることがある。テキトーに検索してみてもそういう話のstack overflowの質問やそれへの回答のようなものは見つかる。
- Google BigQuery There are no primary key or unique constraints, how do you prevent duplicated records being inserted? - Stack Overflow
- distinct - Delete duplicate rows from a BigQuery table - Stack Overflow
unique constraintが存在しないだとか、重複除去したテーブル(的なもの)を如何にして作るかなどは今回言及したいことではないので諸々省略することにする。
ユニーク性を保証したくなる時
BigQueryは追記ベースで考えたほうが良い。そういうアーキテクチャであることは分かっている。まぁそれは置いておいて、ユニーク性を保証したくなる事がある。例えば、アプリのデーターベースのバックアップを取っているときに、同一の行をBigQueryに転送してしまうことがある1。
利便性やバックアップのために、複数の世代のデータをBigQueryに保持しておくということはよくあることだと思う。ここで、最新の断面だけが欲しい場合に、以下の様な形でテーブルfooに対するfoo_latest的なviewなどを設定しておくことで、分析などで利用するときに最新断面とのjoinをやりやすくというような設定はされているとする。
-- こんなに陽に書くことはなく、viewか何かになっていることが多い WITH indexTable AS ( SELECT DISTINCT id, MAX(_updatedAt) as _updatedAt FROM <database name> GROUP BY id ) SELECT DISTINCT o.* FROM <database name> as o INNER JOIN indexTable as oo ON o.id == oo.id AND o._updatedAt = oo._updatedAt
一方で、重複すること自体は防げず同じようなデータが登録されている状態になりうる。
viewを作ることに限らず、ユニーク性を担保する方法は色々あるが、基本的には重複行を排除した結果を別のテーブルなどに保存する(materialized viewも含む)という形なのではないかと思う。そのテーブルを作るためのSELECT文さえ作ってしまえばあとは何とでもなる。
このときの重複行を排除したSELECT文をどうやって作るか?ということに関するモヤモヤが今回の主題。
前提確認
まずは、実際のデータを使って状況を整理してみることにする。ここで、昨日の記事が役に立つ2。一切実データを使うことなくqueryのみで状況を確認していきたい。
とりあえず、usersテーブル的なものが存在すると仮定する。これを対象に色々やっていきたい。
アプリ上での最新断面の状態
とりあえずfooさんbarさん2人のユーザーが居るくらいの雑なデータセット。
CREATE TEMP FUNCTION Now0() RETURNS TIMESTAMP AS (TIMESTAMP_SECONDS(1628038568)); WITH users AS ( SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL SELECT 2, "bar", Now0() ) SELECT * FROM users
この時のデータの出力は以下のようなもの。
[ { "id": "1", "name": "foo", "_updatedAt": "2021-08-04 00:56:08 UTC" }, { "id": "2", "name": "bar", "_updatedAt": "2021-08-04 00:56:08 UTC" } ]
これが、アプリ側から見える世界。
複数世代に跨ったデータをBigQuery側は保持
さて、先程話したとおり、BigQueryには複数世代のデータが入っているとする。 そんなわけで過去のデータも持っていることにする。_updatedAtが異なる同一idのデータも保持することにする。
CREATE TEMP FUNCTION Now0() RETURNS TIMESTAMP AS (TIMESTAMP_SECONDS(1628038568)); CREATE TEMP FUNCTION Now1() RETURNS TIMESTAMP AS (TIMESTAMP_SUB(Now0(), INTERVAL 1 DAY)); WITH users AS ( SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL SELECT 2, "bar", Now0() UNION ALL -- これが過去のもの SELECT 1, "fo", Now1() ) SELECT * FROM users
id=1の行が2つある
[ { "id": "1", "name": "foo", "_updatedAt": "2021-08-04 00:56:08 UTC" }, { "id": "2", "name": "bar", "_updatedAt": "2021-08-04 00:56:08 UTC" }, { "id": "1", "name": "fo", "_updatedAt": "2021-08-03 00:56:08 UTC" } ]
これをviewなどを作ることで最新断面だけを見るということはできる様になっている。これも模倣してみる。
CREATE TEMP FUNCTION Now0() RETURNS TIMESTAMP AS (TIMESTAMP_SECONDS(1628038568)); CREATE TEMP FUNCTION Now1() RETURNS TIMESTAMP AS (TIMESTAMP_SUB(Now0(), INTERVAL 1 DAY)); WITH users AS ( SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL SELECT 2, "bar", Now0() UNION ALL SELECT 1, "fo", Now1() ), users_latest AS ( -- 実際はviewなど SELECT x.* FROM users as x INNER JOIN (SELECT DISTINCT o.id, MAX(o._updatedAt) as _updatedAt FROM users as o GROUP BY id) as y ON x.id = y.id AND x._updatedAt = y._updatedAt ) SELECT * FROM users_latest
一見したところ良い感じ。
[ { "id": "1", "name": "foo", "_updatedAt": "2021-08-04 00:56:08 UTC" }, { "id": "2", "name": "bar", "_updatedAt": "2021-08-04 00:56:08 UTC" } ]
最新断面だけが参照可能な世界。
本当に同じデータが重複行として含まれることがある
ところで、先ほどのview(的なもの)は、_updatedAtのMAXで見ていたので、本当に同じデータが含まれていた場合には二重に出力されることになる。
CREATE TEMP FUNCTION Now0() RETURNS TIMESTAMP AS (TIMESTAMP_SECONDS(1628038568)); CREATE TEMP FUNCTION Now1() RETURNS TIMESTAMP AS (TIMESTAMP_SUB(Now0(), INTERVAL 1 DAY)); WITH users AS ( SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL -- 完全に同じ SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL SELECT 2, "bar", Now0() UNION ALL SELECT 1, "fo", Now1() ), users_latest AS ( SELECT x.* FROM users as x INNER JOIN (SELECT DISTINCT o.id, MAX(o._updatedAt) as _updatedAt FROM users as o GROUP BY id) as y ON x.id = y.id AND x._updatedAt = y._updatedAt ) SELECT * FROM users_latest
MAXで絞り込んたものとのjoinなので当然。
[ { "id": "1", "name": "foo", "_updatedAt": "2021-08-04 00:56:08 UTC" }, { "id": "1", "name": "foo", "_updatedAt": "2021-08-04 00:56:08 UTC" }, { "id": "2", "name": "bar", "_updatedAt": "2021-08-04 00:56:08 UTC" } ]
対応方法はSELECT DISTINCTで絞ったりなどで。
CREATE TEMP FUNCTION Now0() RETURNS TIMESTAMP AS (TIMESTAMP_SECONDS(1628038568)); CREATE TEMP FUNCTION Now1() RETURNS TIMESTAMP AS (TIMESTAMP_SUB(Now0(), INTERVAL 1 DAY)); WITH users AS ( SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL SELECT 1 as id, "foo" as name, Now0() as _updatedAt UNION ALL SELECT 2, "bar", Now0() UNION ALL SELECT 1, "fo", Now1() ), users_latest AS ( SELECT DISTINCT -- distinct x.* FROM users as x INNER JOIN (SELECT DISTINCT o.id, MAX(o._updatedAt) as _updatedAt FROM users as o GROUP BY id) as y ON x.id = y.id AND x._updatedAt = y._updatedAt ) SELECT * FROM users_latest
はい。
[ { "id": "1", "name": "foo", "_updatedAt": "2021-08-04 00:56:08 UTC" }, { "id": "2", "name": "bar", "_updatedAt": "2021-08-04 00:56:08 UTC" } ]
これでめでたしめでたしと思いきや、ようやく本題に入れる。
本題
ようやく、本題。
STRUCT型はdistinctができない
重複除去のためのdistinctがSTRUCT型を使った場合には効かない。というかエラーになってしまう。これがだるい。
意気揚々と以下のようなqueryを実行するとエラーになってしまう。悲しい。テーブルpointsのpがSTRUCT型。
WITH points AS ( SELECT 1 as id, STRUCT<x int64, y int64>(10, 10) as p UNION ALL SELECT 2, STRUCT(0 as x, 10 as y) UNION ALL SELECT 2, STRUCT(0 as x, 10 as y) ) SELECT DISTINCT * FROM points
こういうエラー。
Column p of type STRUCT cannot be used in SELECT DISTINCT at [6:18]
なるほど。仕方がないのでGROUP BYで逃げる。これもだめ。結局フィールドごとに明示的に指定しなければいけないらしい。
WITH points AS ( SELECT 1 as id, STRUCT<x int64, y int64>(10, 10) as p UNION ALL SELECT 2, STRUCT(0 as x, 10 as y) UNION ALL SELECT 2, STRUCT(0 as x, 10 as y) ) SELECT * FROM points as t GROUP BY t.id, t.p -- Grouping by expressions of type STRUCT is not allowed at [8:9]
明示的に指定する必要があるのは良いのだけれど、指定した瞬間にflattenされてしまってだるい。
WITH points AS ( SELECT 1 as id, STRUCT<x int64, y int64>(10, 10) as p UNION ALL SELECT 2, STRUCT(0 as x, 10 as y) UNION ALL SELECT 2, STRUCT(0 as x, 10 as y) ) SELECT t.id, -- t.p, -- SELECT list expression references t.p which is neither grouped nor aggregated at [8:3] t.p.x, t.p.y FROM points as t GROUP BY t.id, t.p.x, t.p.y
そんなわけで同じ形状を保とうと思ったらもう一度作り直す必要がある3。
WITH points AS ( SELECT 1 as id, STRUCT<x int64, y int64>(10, 10) as p UNION ALL SELECT 2, STRUCT(0 as x, 10 as y) UNION ALL SELECT 2, STRUCT(0 as x, 10 as y) ) SELECT t.id, STRUCT(t.p.x as x, t.p.y as y) as p FROM points as t GROUP BY t.id, t.p.x, t.p.y
大変だった。structも自由に利用できる任意のものについての最新断面の提供ということを考えると、SQL文のコピペでは対応できずに頑張って生成するしかないのかもしれない。一方でなんだかそれが必要になるというのもオーバーワーク気味なんじゃ?という気がしている。そういうモヤモヤ。
[ { "id": "1", "p": { "x": "10", "y": "10" } }, { "id": "2", "p": { "x": "0", "y": "10" } } ]
ただ、考えてみると、元となるソースがdocument指向のDBではなくRDBMSなどであった場合はそもそも初めからflattenなのであまり問題にならないのかもしれない?
まとめ
- BigQueryで最新断面とのjoinがしたくなる。
- STRUCT型ではDISTINCTが効かない。だるい。
gist
追記:
後日、TobakuCptlsmさんにtwitterでROW_NUMBER()と組み合わせる方法を教えてもらいました。実はROW_NUMBER()の存在自体は知っていたのですが、行番号に依存したコードはあまりSQL的には嬉しくないんじゃないか?と思ったりなどして記事に含めるのを辞めていたのでした
その後、色々bigqueryのドキュメントを読んでいくうちに、CDC(Change Data Capture)を利用したデータの同期のドキュメントでも同様の説明があったことから利用しても良いかもしれないと思うようになりました。
すこし形は違いますが以下の様な例の部分です。
bq mk --view \ "SELECT * EXCEPT(change_type, row_num) FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY change_id DESC) AS row_num FROM ( SELECT * EXCEPT(change_type), change_type FROM \`$(gcloud config get-value project).cdc_tutorial.session_delta\` UNION ALL SELECT *, 'I' FROM \`$(gcloud config get-value project).cdc_tutorial.session_main\`)) WHERE row_num = 1 AND change_type <> 'D'" \ cdc_tutorial.session_latest_v
あとは、viewではなくmaterialized viewないしは別テーブルへのqueryになっていればROW_NUMBER()を使う形でも悪くないかなと思うようになりました。
参考
- Google BigQuery There are no primary key or unique constraints, how do you prevent duplicated records being inserted? - Stack Overflow
- 標準 SQL のタイムスタンプ関数 | BigQuery | Google Cloud
- 標準 SQL ユーザー定義関数 | BigQuery | Google Cloud
- 標準 SQL のデータ型 | BigQuery | Google Cloud
- 標準 SQL の番号付け関数 | BigQuery | Google Cloud
- 変更データ キャプチャを使用した BigQuery へのデータベース レプリケーション | Cloud アーキテクチャ センター
-
例えばembulkなどでBigQueryに転送していることを思い浮かべてもらえると↩
-
実際のところ、この記事を書くために昨日の記事を書いた↩
-
group by部分はformatを使って手抜きをする方法はあるかもしれない。雑でよければ。https://sucrose.hatenablog.com/entry/2018/10/09/232753↩