はじめに
便利なクエリをケース別に紹介していきます。筆者が運用保守でよく使用しているクエリたちです。
特にタイムトラベル機能は非常に便利なので、ぜひ使っていただきたいです。
使用ケース
日次データに欠落がないか調べたい
きちんと毎日のレコードが入っているか確認します。テーブルに日時カラムがあることが条件です。
日別のレコード件数を出すことによってデータが少ない等の違和感に気づくこともできます。
SELECT
purchase_date,
COUNT(*) AS cnt
FROM
`dataset.table_name`
GROUP BY 1
ORDER BY 1 DESC
↓ こんな感じで欠落した日があることに気づくことができる
DATEではなくTIMESTAMPならこちら。タイムゾーンを合わせることをお忘れなく。
SELECT
DATE(purchase_timestamp, "Asia/Tokyo"),
COUNT(*) AS cnt
FROM
`dataset.table_name`
GROUP BY 1
ORDER BY 1 DESC
過去の状態のテーブルが見たい（7日前まで）
タイムトラベル機能の使い方
過去のテーブルに入っていたデータをFOR SYSTEM_TIME AS OFで見ることができます。
見たい日時を指定してください。
これを利用すれば、誤って更新したテーブルを元の状態に戻すこともできるので、とても安心感があります。
SELECT
*
FROM
`dataset.table_name`
-- 絶対時間
FOR SYSTEM_TIME AS OF TIMESTAMP('2023-12-01 14:00:00', 'Asia/Tokyo');
-- 相対時間
-- FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 HOUR);
遡れるのは最大で7日間です。データセットにタイムトラベルできる期間が設定されています（デフォルトは7日）。
タイムトラベルが使えないテーブル
Viewテーブルは実態を持たないため、タイムトラベル機能を使うことはできません（エラーになります）。
過去の状態が見たければ、そのViewに設定されたクエリをFOR SYSTEM_TIME AS OF付きで実行しましょう。
また、外部テーブルで利用する際にも注意しましょう。
FOR SYSTEM_TIME AS OFのクエリは成功しますが、過去に遡るのはテーブルの設定（スキーマやソースURIなど）だけです。つまり外部（GCSなど）に保存されたデータは今の状態しか参照できません。
2つのテーブルの差分を見たい
このような2つのテーブルがあるとします。
| id | name |
|---|---|
| 1 | 田中 |
| 2 | 佐藤 |
| 3 | 高橋 |
| 4 | 伊藤 |
| 5 | 武田 |
| id | name |
|---|---|
| 1 | 田中 |
| 2 | 佐藤 |
| 3 | 高橋 |
| 4 | 鈴木 |
| 5 | 中村 |
AにはあるがBには無いレコードを探す
Aのテーブルには"伊藤"と"武田"がありますが、Bにはありません。
こういった片方で比較して差分を抽出するにはこうします。
SELECT
*
FROM
`dataset.users_A` EXCEPT DISTINCT
SELECT
*
FROM
`dataset.users_B`
AとBのどちらか一方にしかないレコードを探す
先ほどとは違い、AとBの双方向で比較して差分を抽出するには、それぞれの差分をUNIONします。
table_nameカラムをつけると、どちらに存在するレコードか分かるためおすすめです。
SELECT
"A" AS table_name
, *
FROM
(SELECT * FROM dataset.users_A EXCEPT DISTINCT SELECT * FROM dataset.users_B) -- Aにだけ存在するレコードを抽出する
UNION ALL
SELECT
"B" AS table_name
, *
FROM
(SELECT * FROM dataset.users_B EXCEPT DISTINCT SELECT * FROM dataset.users_A) -- Bにだけ存在するレコードを抽出する
ただしテーブルの容量が大きいほど、処理にかかるコスト・時間も大きくなるため状況に応じてWHERE句を使いましょう。
例）2023年12月1日のレコードだけを比較する
SELECT
"A" AS table_name
, *
FROM
(SELECT * FROM dataset.table_A WHERE date = "2023-12-01" EXCEPT DISTINCT SELECT * FROM dataset.table_B WHERE date = "2023-12-01") -- Aにだけ存在するレコードを抽出する
UNION ALL
SELECT
"B" AS table_name
, *
FROM
(SELECT * FROM dataset.table_B WHERE date = "2023-12-01" EXCEPT DISTINCT SELECT * FROM dataset.talbe_A WHERE date = "2023-12-01") -- Bにだけ存在するレコードを抽出する
(応用編) テーブルの更新前後の差分を見たい
このようなテーブルの中身を更新したとします
| id | name |
|---|---|
| 1 | 田中 |
| 2 | 佐藤 |
| 3 | 高橋 |
| 4 | 伊藤 |
| 5 | 武田 |
| id | name |
|---|---|
| 1 | 田中太郎 |
| 2 | 佐藤次郎 |
| 3 | 高橋三郎 |
| 4 | 鈴木四郎 |
| 5 | 中村五郎 |
差分抽出のクエリ
差分を抽出するには一時テーブルとタイムトラベル機能、テーブルの比較クエリを組み合わせます。
-- 一時テーブルの作成クエリ
CREATE OR REPLACE TEMP TABLE users_a_old AS
SELECT
*
FROM
`dataset.users_a` FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
;
-- 差分の抽出クエリ
SELECT
"after" AS table_name
, *
FROM
(SELECT * FROM `dataset.users_a` EXCEPT DISTINCT SELECT * FROM users_a_old) -- 変更後にだけ存在するレコードを抽出する
UNION ALL
SELECT
"before" AS table_name
, *
FROM
(SELECT * FROM users_a_old EXCEPT DISTINCT SELECT * FROM `dataset.users_a`) -- 変更前にだけ存在するレコードを抽出する
一時テーブルを使用する理由
上のクエリでなぜ一時テーブルを使用しているのかと疑問に思う方もいらっしゃると思います。
その理由は、1度のクエリで過去と現在の両方は参照できないからです。
例えば、下記のクエリはエラーになります。
SELECT
*
FROM
`dataset.users_a` EXCEPT DISTINCT
SELECT
*
FROM
`dataset.users_a` FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR)
エラーメッセージ
Table 'dataset.users_a' is referenced with and without 'FOR SYSTEM_TIME AS OF' expression. If a 'FOR SYSTEM_TIME AS OF' expression is used, all references of a table should use the same TIMESTAMP value.
訳）テーブル'dataset.users_a'が'FOR SYSTEM_TIME AS OF'式の有無にかかわらず参照されている。FOR SYSTEM_TIME AS OF'式を使用した場合、テーブルの全ての参照は同じTIMESTAMP値を使用しなければなりません。
そのため、過去のテーブルから抽出するクエリと現在のテーブルから抽出するクエリは分けなければなりません。
一時テーブルに過去データを格納することで、差分比較ができるようになります。
ちなみに一時テーブルは24時間で自動削除されるため、テーブルの後片付けは必要ありません。