BigQueryでのSTRUCT型を伴うSELECT文での重複行の削除についてのメモ

まず、前提として、BigQUeryにはunique constraint的な機能が存在しない。なので、あれこれと頑張ってユニーク性を保つ作業をしたくなることがある。テキトーに検索してみてもそういう話の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()を使う形でも悪くないかなと思うようになりました。

参考


  1. 例えばembulkなどでBigQueryに転送していることを思い浮かべてもらえると

  2. 実際のところ、この記事を書くために昨日の記事を書いた

  3. group by部分はformatを使って手抜きをする方法はあるかもしれない。雑でよければ。https://sucrose.hatenablog.com/entry/2018/10/09/232753