「昨日、BigQueryで実行した処理を忘れて困ったな……」そんな時に試したい４ステップ

BigQuery

Last updated at 2023-12-20Posted at 2023-12-20

はじめに

皆さんは、昨日何をしたかを忘れたことはないでしょうか？私はあります。

本記事では「何か、BigQueryで昨日対応したはずだけど忘れたな……」と思ったときを例に挙げ、もし、私なら試すSQLや確認方法の流れを、4つのSTEPに分けてまとめてみました。

STEP1：本当に昨日BigQueryを使用したのか？

　まず、昨日本当にBigQueryを使用したのかを確認します。時には、思い込みで作業を覚えているかもしれません。BigQueryのSQL実行履歴をチェックして、昨日の日付の実行履歴があるかを確認します。ここで実行履歴が見つからなければ、昨日BigQueryを使用していなさそうです。

<パターン1：BigQueryの管理コンソール画面から確認>
　BigQueryの管理コンソール画面で、「個人履歴」タブを開きます。このタブには、自分が実行したSQLの実行履歴が表形式で表示されています。特に「作成時間」の列をチェックすることで、SQLの実行タイミングを把握できます。

<パターン2：SQLで確認>
　もし、すでに今日の実行履歴が多く、管理コンソール画面からの確認が困難な場合は、SQLを使って昨日の実行履歴に絞り込み確認することもできます。以下のSQLを使用すると、昨日のSQLのジョブのみを検索できます。

SELECT
    DATETIME(creation_time,'Asia/Tokyo') AS `作成時間`,
    query AS `概要_実行SQL`
FROM
    `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE
    /*昨日実行の履歴のみに絞り込む*/
    DATE(creation_time,'Asia/Tokyo') = DATE_ADD(CURRENT_DATE('Asia/Tokyo'), INTERVAL -1 DAY)
ORDER BY
    /*作成時間が新しい順にソート*/
    creation_time DESC

STEP2では、実際にどんなSQLを実行したかを見ていこうと思います。

STEP2：どんなSQLを実行したのか？

　STEP1でBigQueryを使用したことを確認したら、次にどんなSQLを実行したのかを調べます。

　まず、STEP1で紹介したBigQueryの管理コンソール画面やSQLを使用して、昨日の日付に実行したSQLの一覧を確認してみます。実行履歴には、SQLの内容だけでなく、実行した時間や、どのデータセットが使われたかといった情報も記録されています。これらの情報を確認することで、どのような分析を行ったかの手がかりになりそうです。

特に、以下の点に注意してSQLを確認してみます。

SQLの内容：実際にどのようなデータを問い合わせたか
 使用したデータセット：どのテーブルに対してSQLを実行したか
       SQLの実行時間：昨日のいつSQLを実行したか

これらの情報は、昨日の作業内容を思い出す上で役立ちそうです。
STEP3では、SQLを実行した結果、どんな変化が起こったかを見ていきましょう。

STEP3:SQLを実行した結果どんな変化が起こったのか？

　STEP2でどのSQLを実行したかを確認した後は、そのSQLの実行結果がデータベースにどのような影響を与えたかを調査します。これは、作業内容の影響範囲を理解し、作業の全体像を把握する上での手がかりになりそうです。

＜データの変化の確認＞
　実行したSQLによってデータに変化があったかどうかを確認してみます。例えば、テーブルに新しいレコードが追加されたか、既存のレコードが変更されたかです。変更内容を確認することで、昨日の作業の内容をより詳細に思い出すことができそうです。

＜FOR SYSTEM TIME AS OF句の活用＞
　データの変化の確認は、INSERT、UPDATEを行った先のテーブルを見ることで思い出せるかもしれません。ただ、「FOR SYSTEM TIME AS OF」句を使用して、昨日時点のデータ状態を確認することもよさそうです。昨日の特定時刻のデータ状態を見ると、変化の前後を比較するのに役立ちそうです。

/* 昨日のテーブルレコードを取得する */
SELECT * FROM `[対象テーブル名]` 
FOR SYSTEM TIME AS OF '[昨日の日付]'
;

実際比較するテーブルを出すことは難しいので、例として、bigquery-public-data.google_trends.international_top_rising_termsテーブルの昨日（2023/12/17）と今日（2023/12/18）のデータを比較してみます。比較した結果は以下の通りです。refresh_dateの日付の違いがあるのが分かるかと思います。

<昨日（2023/12/17当時のデータ）>

<今日（2023/12/18のデータ）>

詳細な使用方法は、以下Googleの公式ドキュメントをご参照ください。

次に、STEP4で使用したデータの詳細を見ていきましょう。

STEP4：当時どのようなデータを使用したのか？

　STEP3でSQLを実行した結果の影響を確認したら、次にどのようなデータを使用したのかを特定してみます。

<テーブルの最終更新日とレコード数の確認>
　使用したデータセットやテーブルの最終更新日やレコード数を確認することで、どの時点のデータに基づいて分析が行われたかがわかります。

　使用したデータセットやテーブルを見て直接確認することも可能ですが、BigQueryのINFORMATION_SCHEMA.PARTITIONSビューを使用するのもよさそうです。このビューを使用し、WHERE句の部分を調整すると、一度に複数のテーブルを対象にテーブルの最終更新日時やレコード数を確認でき便利そうです。以下のSQLは、特定のテーブルに関する最終更新日時やレコード数等の情報を取得する例です。

SELECT
    table_catalog,
    table_schema,
    table_name,
    total_rows,  /* レコード数 */
    DATETIME(last_modified_time, 'Asia/Tokyo') AS last_modified_time_jst, /* 最終更新日 */
FROM
    `[プロジェクト名].[データセット名].INFORMATION_SCHEMA.PARTITIONS`
WHERE
    table_name = ‘[対象テーブル名]’