パターンとしてありそうなケースの備忘録
たとえば何かしらの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
user | saved | deleted | rn |
---|---|---|---|
fuga | 2022-07-10 | true | 1 |
hoge | 2022-07-12 | false | 1 |
piyo | 2022-07-11 | true | 1 |
このクエリでhogeだけ削除されていないということがわかった
また、ユーザーごとに1レコードあれば良いのでROW_NUMBER
とOVER
句でユーザーごとにデータを区切って最新のレコードを抜き出すようにしている
こうすることで、ユーザーごとにレコードが削除されたかどうかの情報も持たせることができる
まとめ
- ナビゲーション関数
FIRST_VALUE
とOVER
句を用いて、レコードが保存された日と比較することでレコードが最新状態では削除されているか判断できるようにした ROW_NUMBER
とOVER
句で同じユーザーのレコードなら最新のレコードだけを抜き出すようにした
定期的に同期しているデータでレコードが存在しない、削除されたなどのケースを判断したい状況で使えるので覚えておきたい