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

# 目次
- はじめに
- STEP1：本当に昨日BigQueryを使用したのか？
- STEP2：どんなSQLを実行したのか？
- STEP3：SQLを実行した結果、どんな変化が起こったのか？
- STEP4：当時どのようなデータを使用したのか？
- 終わりに

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

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

<img width=20% src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3655998/827a835f-cd81-c813-022c-843eda0c739a.png">

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

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

<img width=90% src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3655998/92bb283d-06c9-7214-d227-7f1a404480cf.png">

**<パターン2：SQLで確認>**
　もし、すでに今日の実行履歴が多く、管理コンソール画面からの確認が困難な場合は、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
```
<img width=90% src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3655998/908ea893-06d2-084d-5552-b8da9759c76f.png">

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」句を使用して、昨日時点のデータ状態を確認することもよさそうです。昨日の特定時刻のデータ状態を見ると、変化の前後を比較するのに役立ちそうです。

```SQL
/* 昨日のテーブルレコードを取得する */
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当時のデータ）>**
<img width=90% src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3655998/303b4d3e-7b69-4bdb-92d0-3baecc6e1fb1.png">

**<今日（2023/12/18のデータ）>**
<img width=90% src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3655998/fb3647ec-2f6e-dd62-cdd1-70e484061755.png">

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

https://cloud.google.com/bigquery/docs/access-historical-data?hl=ja

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

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

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

```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 = ‘[対象テーブル名]’  
```
例えば、使用しているテーブルが、`bigquery-public-data.samples.wikipedia`の場合、以下SQLで確認可能です。念のため、実際のテーブル詳細を見てみると、レコード数と最終更新日が一致していることが確認できるかと思います。
<img width=90% src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3655998/8d716f6d-afac-15cb-1ad5-57281d56abcc.png">

<img width=70% src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3655998/a535ca34-d5fa-619d-acd1-06dfa9e954e4.png">

この情報により、どのテーブルが分析に使用されたか、またそれが最後に更新された時刻はいつだったかを特定できます。これで、昨日の分析の全体像がよりはっきりしそうです。

参考にした記事：

https://www.yasuhisay.info/entry/2021/12/23/083000/

## 終わりに
　昨日、何をしたか忘れたとき、BigQueryだとこのようなことができるということを紹介しました。 少しでもお役に立てば幸いです。おそらく、明日の私はこの記事を使用する気がします。