Snowflakeでどのファイルをロードしたっけ？ LOAD_HISTORYビューで履歴を確認してみた

こんにちは！エノカワです。

Snowflakeには、Amazon S3 からデータをロードする仕組みがありますが、
ロードするタイミングやファイル数が多いと、どのファイルをロードしたのか確認したい場面が出てきます。

過去にロードしたファイルの履歴はどうやって確認すれば良いのでしょう？

調べてみたところ、テーブルにロードされたファイルの履歴を取得できるビューがありました。
ということで、今回はLOAD_HISTORYビューで履歴の確認を行ってみましょう。

データロード

まずは履歴の確認対象となるデータロードから行います。
履歴のバリエーションを持たせるために、２種類の方法でロードします。

バルクロード

S3に配置されるデータをCOPYコマンドでロードします。
以下の記事を参考に行います。

Snowpipe

S3に配置されるデータをSnowpipeでロードします。
以下の記事を参考に行います。

ロード後の状態

ロード方法に応じて、ロード先のテーブルを分けました。

LOAD_HISTORYビューで履歴を確認する

早速、先ほどのロード履歴を確認してみましょう。
確認方法は簡単です。
ワークシートの画面左部から対象のデータベースを選択して広げます。
INFORMATION_SCHEMAのビューからLOAD_HISTORYを選択し、メニューから「データをプレビュー」を実行します。
そうすると、画面下部のデータプレビューに履歴データが表示されます。

バルクロードでロードした結果が確認できますね。

SQLによる確認

SQLでも同じように確認できます。
ビューからLOAD_HISTORYを選択し、メニューから「SQLの場所名」を実行します。
そうすると、場所名"ENOKAWA_SANDBOX_DB"."INFORMATION_SCHEMA"."LOAD_HISTORY"が
ワークシートに出力されるので、
場所名に対してSELECTすることで、画面下部に履歴データが表示されます。

SELECT * FROM "ENOKAWA_SANDBOX_DB"."INFORMATION_SCHEMA"."LOAD_HISTORY";

件数が多い場合は、WHEREによる絞り込み、ORDER BYによる並び替えも可能です。

列項目

ソースファイル、ターゲットテーブル、ロード日時、ステータスなどが確認できます。
画面下部の右側「列▼」から表示する列項目を選択できます。

各列の説明は下記ドキュメントを参照ください。

• LOAD_HISTORY ビュー — Snowflake Documentation

…あれ？ Snowpipeでロードした履歴が見当たりませんね。

改めてドキュメントを確認すると以下の注釈があります。

このビューは、Snowpipeを使用してロードされたデータの履歴を返しません。この履歴情報については、代わりに COPY_HISTORY テーブル関数をクエリします。

LOAD_HISTORYビューは、Snowpipeでロードした履歴は返さないみたいですね。
では、COPY_HISTORYテーブル関数でクエリしてみましょう。

COPY_HISTORYテーブル関数で履歴を確認する

COPY_HISTORYテーブル関数でSnowpipeのロード履歴を確認してみましょう。
構文は下記の通りです。

COPY_HISTORY(
      TABLE_NAME => '<string>'              # テーブル名を指定
       , START_TIME => <constant_expr>      # ロードイベントを取得するための時間範囲の開始をマーク
      [, END_TIME => <constant_expr> ] )    # ロードイベントを取得するための時間範囲の終了をマーク

SQLで直近１ヶ月間のロード履歴を見てみましょう。
INFORMATION_SCHEMAのCOPY_HISTORYテーブル関数をSELECTします。
Snowpipeのロード先テーブルであるLOGを指定します。

select *
from table(information_schema.copy_history(
                                table_name=>'PUBLIC.LOG'
                              , start_time=> dateadd(month, -1, current_timestamp())));

Snowpipeのロード履歴が確認できました。

TABLE_NAMEを変更することで、
バルクロードでロードしたテーブルUSERSのロード履歴も確認できます。

select *
from table(information_schema.copy_history(
                                table_name=>'PUBLIC.USERS'
                              , start_time=> dateadd(month, -1, current_timestamp())));

表示が見切れていますが、COPY_HISTORYテーブル関数では、
パイプの名前などSnowpipeに関する情報も確認できます。

各列の説明は下記ドキュメントを参照ください。

• COPY_HISTORY — Snowflake Documentation

補足

LAST_LOAD_TIME

LOAD_HISTORYビューとCOPY_HISTORYテーブル関数はどちらも
ロードレコードのタイムスタンプであるLAST_LOAD_TIMEを返します。

LAST_LOAD_TIMEのデータ型はTIMESTAMP_LTZになっており、
デフォルトのタイムゾーンはAmerica/Los_Angeles(太平洋標準時)です。

今回はデフォルトのままですので、タイムゾーンは太平洋標準時の夏時間となり、
LAST_LOAD_TIMEにはUTCからのオフセットである-0700が表示されています。

権限

今回はINFORMATION_SCHEMAのLOAD_HISTORYビューとCOPY_HISTORYテーブル関数を参照しました。

INFORMATION_SCHEMAのビューまたはテーブル関数をクエリすると、
現在のロールにアクセス権限が付与されているオブジェクトのみが返されます。
ユーザーの現在のロールに付与されている権限に依存するので注意が必要です。

Snowpipeのデータロードの場合、COPY_HISTORYテーブル関数は
パイプ所有者または最小権限を持つロールの結果のみを返します。

詳細については、参考欄のドキュメントを参照ください。

まとめ

LOAD_HISTORYビューでロード履歴を確認することができました。
また、ロード方法によって履歴の確認の仕方が異なることも分かりました。

• データベース全体に対するバルクロードの履歴を横断的に確認したい場合はLOAD_HISTORYビュー
• 特定テーブルに対するSnowpipeのロード履歴を確認したい場合はCOPY_HISTORYテーブル関数

といったような使い分けが考えられそうですね。

参考

• LOAD_HISTORY ビュー — Snowflake Documentation
• COPY_HISTORY — Snowflake Documentation
• 日付と時刻のデータ型 — Snowflake Documentation