BigQueryで更新と削除の多いデータを扱う

PLAID Advent Calendar 2018 の12日目。今回はBigQueryについてです。

BigQueryでの更新と削除

BigQueryではデータ操作言語（DML）を使用してデータを更新や削除ができますが、制限があります。

UPDATE、DELETE、および MERGE の各ステートメントを組み合わせた実行数の 1 日あたりテーブルあたり最大値 - 200
UPDATE、DELETE、および MERGE の各ステートメントを組み合わせた実行数の 1 日あたりプロジェクトあたり最大値 - 10,000

更新頻度によっては厳しい制限となるので、ここではDMLを使わずデータの構造とクエリで、更新・削除が多いデータの扱い方を考えてみます。
そんなデータはない、更新の多いデータはそもそも別のサービスで扱うべき、という話もありそうですがここではとりあえず気にしないことにします。

方針

方針A

• 削除・更新の処理を同じテーブルに追記していく
• 読み込み時は最新のデータだけ読む
• 削除はフラグで対応

方針B

• 削除・更新の処理をスキーマが同じ別のテーブルに書き込んでいく
• 読み込み時に元のテーブルとJOINして反映させる

※ここまで読んで理解した方は以下を読む必要はありません

方針Aの実装例

準備：スキーマを変更する

たとえばこういうschemaのテーブルを扱いたいとしたら

[
  {
    "name": "id",
    "type": "STRING"
  },
  {
    "name": "fileld1",
    "type": "STRING"
  },
  ...
]

deleted、updated_atというカラムを追加しておきます。

[
  {
    "name": "id",
    "type": "STRING"
  },
  {
    "name": "fileld1",
    "type": "STRING"
  },
  ...
  {
    "name": "deleted",  // 論理削除用のフラグ
    "type": "BOOLEAN"
  },
  {
    "name": "updated_at", // 更新した時間
    "type": "TIMESTAMP"
  },
]

行の削除・更新をしたいとき

以下のようなデータがあるとします。

id	field1	...	deleted	updated_at
1	hoge	...	false	00:00:00
2	fuga	...	false	00:00:00

削除

id:1のデータを削除するときはdeleted:true、update_at:更新時刻というデータをつけて挿入します。

id	field1	...	deleted	updated_at
1	hoge	...	false	00:00:00
2	fuga	...	false	00:00:00
1		...	true	00:00:10

更新

id:2のデータを更新するときはupdate_atに更新時刻をいれて変更後のデータを挿入します。

id	field1	...	deleted	updated_at
1	hoge	...	false	00:00:00
2	fuga	...	false	00:00:00
1		...	true	00:00:10
2	piyo	...	false	00:00:10

読み込み時のSQL

このようなデータに対し、読み込み時には削除されているデータを除外し、最新のデータだけを読むようにします。

SELECT
  * EXCEPT(row_number)
FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY updated_at DESC) row_number,
    *
FROM
  tablename
)
WHERE
  row_number = 1 -- 最新の値だけ読む
  AND deleted IS FALSE -- 論理削除されていないものだけ読む

実行結果

id	field1	...	deleted	updated_at
2	piyo	...	false	00:00:10

毎回このクエリを書くのが面倒な場合はViewとして定義しておけばOKです。
※上記のクエリをそのままViewにした場合はカラムも日付も関係なく毎回全データを読み込んでしまうことになるのでご注意ください。

方針Bの実装例

ほぼほぼAと同じなので簡単な説明だけ書いておきます。

• スキーマに更新日次と削除フラグを追加した別の更新用テーブルを準備します。
• 削除・更新時には方針Aと同様のデータを更新用のテーブルの方に書き込みます。
• 読み込み時には元のテーブルと更新用テーブルを両方見て、LEFT JOIN、COALESCEなどを使って最新のデータを読むようにします。

やっぱりきれいなテーブルにしたい

以上で更新・削除に対応できましたが、読み込みの処理は増えることになります。
それを避けたい場合はクエリ結果からテーブルを作成することができるので、上に記載したSQLの結果を別のテーブルにコピーする、またはそのテーブル自身をコピー先に指定すれば削除・更新が反映されたテーブルを作成することができます。

// コピー時のオプション
// 参考) https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs#configuration.query
{
  writeDisposition : 'WRITE_TRUNCATE', // データがあったら上書き
  destinationTable: {
    tableId: 'srctable', // コピー元のテーブル自身をコピー先に指定する
    ...
  }
}

分割テーブルの場合にも同じことができるので、日次バッチで日毎にきれいにしていくということも可能です。

{
  writeDisposition : 'WRITE_TRUNCATE',
  destinationTable: {
    tableId: 'srctable$20181212', // 指定した日付のテーブルだけ上書き
    ...
  }
}

ただし、日付を指定せずストリーミングでデータを挿入している場合はデータが一時的に NULL パーティションに関連付けられるため、日付を指定した操作をする場合はご注意ください。

おわりに

データの構造と読み込み方法を変えることでBigQueryでもDMLの制限を気にせず更新・削除に対応できるようになりました。需要があるかどうかはわかりませんが、こうすればできる、程度の話なので、本番環境などで使用する際にはご注意ください。