BigQueryにAPIのデータを定期的に同期して、削除されたレコードを検出する

パターンとしてありそうなケースの備忘録

たとえば何かしらのAPIのデータを定期的にBigQueryに同期している機能において

API側でレコードが削除された場合、ものによっては削除フラグがあったりするが、ない場合はレスポンスからレコードがなくなる

ある時点から削除されたレコードがあった場合にそのレコードを検知するということがしたい

前提

前提として下記のような状態で話を進める

同期は毎日1度全件同期
レコードが保存された日付情報を持っている
- 例)APIのレスポンスをStorageに直接保存、日付情報をHiveパーティション形式で保存し保存日のデータを持たせる
- 例)AUTOカラムでデータが挿入されたTIMESTAMPを保存している

日付ごとのデータ(oある/xなし)

上記前提のもと、データは次のような状態とする

レコード保存日	hoge	fuga	piyo
2022-07-10	o	o	o
2022-07-11	o	x	o
2022-07-12	o	x	x

理解しやすくするために、上記データをユーザー情報として進める

fugaというユーザーは2022-07-11時点ではレコードが削除されていた
piyoというユーザーは2022-07-12時点ではレコードが削除されていた

サンプルデータの用意

  WITH data AS(
    SELECT
      *
    FROM
      UNNEST(ARRAY<STRUCT<saved DATE, user STRING>> [
         ("2022-07-10", "hoge"),
         ("2022-07-11", "hoge"),
         ("2022-07-12", "hoge"),
         ("2022-07-10", "fuga"),
         ("2022-07-10", "piyo"),
         ("2022-07-11", "piyo")
      ])
  )
  SELECT * FROM data

saved	user
2022-07-10	hoge
2022-07-11	hoge
2022-07-12	hoge
2022-07-10	fuga
2022-07-10	piyo
2022-07-11	piyo

レコードが削除されたかどうかの判定

FIRST_VALUEというナビゲーション関数を用いる

前提の項でも触れたがあらためて

レコードは保存された日付の情報を持っている
対象データは毎度全件同期している

この前提のもとであれば、全レコード中で最新の日付を取得し、その日付がレコードのsavedと差異があった場合、最新のデータではレコードが存在しないということになる

SQLで表すと次のようになる

saved != FIRST_VALUE(saved) OVER(ORDER BY saved DESC) AS deleted,

保存日(saved)とFIRST_VALUEが一致しない場合は最新のデータが存在していない

最新のデータが存在していない＝削除された

という解釈ができる

なのでこの値をdeletedとし削除されたユーザーという判断をする

サンプルデータを使って試してみる

WITH
  data AS(
  SELECT
    *
  FROM
    UNNEST(ARRAY<STRUCT<saved DATE, user STRING>> [
      ("2022-07-10","hoge"),
      ("2022-07-11","hoge"),
      ("2022-07-12","hoge"),
      ("2022-07-10","fuga"),
      ("2022-07-10","piyo"),
      ("2022-07-11","piyo")
    ])
  )
SELECT
  user,
  saved,
  saved != FIRST_VALUE(saved) OVER(ORDER BY saved DESC ) AS deleted,
  ROW_NUMBER() OVER(PARTITION BY user ORDER BY saved DESC ) AS rn
FROM
  data
QUALIFY rn = 1