BigQueryでのデータ復元・テーブル差分取得・データ品質チェックのメモ

BigQueryを使用していると、誤ってデータを削除してしまったり、テーブルの差分を確認したい場面があります。

この記事では、以下の3つのシナリオに対応する方法をまとめています。

1. 過去の特定時点のデータを復元する方法
2. 2つのテーブル間の差分を取得する方法
3. データ品質や異常値を確認する便利なチェック方法

---

1. 過去の特定時点のデータを復元する方法

FOR SYSTEM_TIME AS OFを使用したデータの取得

BigQueryでは、FOR SYSTEM_TIME AS OF句を使用することで、過去の特定時点のデータを取得することができます。

SELECT
  *
FROM
  `project_name.dataset_name.table_name`
FOR SYSTEM_TIME AS OF '2020-02-01 10:00:00 Asia/Tokyo'

この方法は、データの誤削除や誤更新があった場合に、過去の状態を確認するのに有効です。通常、保持期間は7日間です。

データの復元手順

1. 現在のテーブルのデータを削除

DELETE
FROM
  `project_name.dataset_name.table_name`
WHERE
  TRUE;

1. 過去のデータを挿入

INSERT INTO `project_name.dataset_name.table_name`
SELECT
  *
FROM
  `project_name.dataset_name.table_name`
FOR SYSTEM_TIME AS OF '2020-02-01 10:00:00 Asia/Tokyo';

削除されたテーブルの復元

削除から48時間以内であれば、削除されたテーブルを復元できます。

bq cp dataset.deleted@1579878000000 dataset.table

---

2. 2つのテーブル間の差分を取得する方法

EXCEPT DISTINCTを使うと、テーブル間の差分を効率的に抽出できます。

WITH table1 AS (
  SELECT 1 AS a, 2 AS b, 3 AS c UNION ALL
  SELECT 2 AS a, 4 AS b, 6 AS c
),
table2 AS (
  SELECT 1 AS a, 2 AS b, 3 AS c
)

SELECT * FROM (
  SELECT * FROM table1
  EXCEPT DISTINCT
  SELECT * FROM table2
)
UNION ALL
SELECT * FROM (
  SELECT * FROM table2
  EXCEPT DISTINCT
  SELECT * FROM table1
);

FULL OUTER JOINよりも手軽に差分を確認できます。

---

3. データ品質や異常値を確認する便利なチェック方法

データ分析・運用で役立つ「すぐに使える」便利なチェック方法もまとめます。

NULL値の確認

SELECT
  COUNTIF(column_name IS NULL) AS null_count
FROM
  `project.dataset.table`

ユニークキーの一意性チェック

SELECT
  column_name,
  COUNT(*) AS total_count,
  COUNT(DISTINCT column_name) AS distinct_count
FROM
  `project.dataset.table`
GROUP BY
  column_name

頻出値（モード）の確認

SELECT
  column_name,
  COUNT(*) AS cnt
FROM
  `project.dataset.table`
GROUP BY
  column_name
ORDER BY
  cnt DESC
LIMIT 10;

時系列データの連続性確認

SELECT
  event_date,
  COUNT(*) AS cnt
FROM
  `project.dataset.table`
GROUP BY
  event_date
ORDER BY
  event_date;

重複行の抽出

SELECT
  column1, column2, COUNT(*) AS cnt
FROM
  `project.dataset.table`
GROUP BY
  column1, column2
HAVING
  cnt > 1;

これらのチェックを活用すると、異常値の早期発見やデータ整合性の確保に役立ちます。

---

まとめ

• FOR SYSTEM_TIME AS OF で過去のデータを取得・復元
• EXCEPT DISTINCT でテーブル間の差分を簡単取得
• 各種クエリでNULL確認、重複チェック、分布確認などが可能

---

参考記事:

• BigQueryであの頃のデータに戻りたい（ただし、1週間前まで）
• BigQueryで2つのテーブルの差分を求める方法