SnowflakeのCOPYコマンド実行時にPURGEオプションを利用してみた

こんにちは！DA(データアナリティクス)事業本部 インテグレーション部の大高です。

SnowflakeのCOPYコマンド実行時のオプションとして、PURGEというオプションがあります。このオプションを指定するとCOPY成功時にロード済みのファイルを自動で削除してくれるという便利なオプションです。

今回は、このオプションを実際に試してみたいと思います。

前提条件

以下のような「ヘッダ行付き」、「カンマ区切り」のCSVファイルをローカルPCに準備しておきます。

users.csv

id,name,age
1,Aruto Hiden,22
2,Izu,
3,Isamu Fuwa,27
4,Yua Yaiba,24
5,Gai Amatsu,45
6,Horobi,
7,Naki,
8,Jin,
9,Ikazuchi,

また、Snowflakeには事前にデータベースとテーブルを用意しておきます。

USE ROLE OOTAKA_SANDBOX_ROLE; -- ROLEは事前作成済みのものを利用します
CREATE DATABASE OOTAKA_SANDBOX_DB;

USE DATABASE OOTAKA_SANDBOX_DB;
CREATE TABLE public.users(
  id INTEGER,
  name STRING,
  age INTEGER
);

なお、Snowflakeの操作については、すべてSnowSQLで実行します。

PURGEオプションの利点について

まずはじめに、このPURGEオプションを指定すると「何がうれしいのか？」という点について説明したいと思います。PURGEオプションには、ドキュメントに記載のとおり以下の２点の利点があります。

不注意による再ロードの防止

ロード済みのファイルは、明示的にREMOVEコマンドで削除しないとステージ内に残り続けます。一方で、Snowflakeではロードメタデータと呼ばれるメタデータを持っており、ファイルのロード情報を保持しています。これにより、特にオプション指定をしない通常のCOPYコマンドではロード済みのファイルが再ロードされることはありません。

ロード済みのファイルを再ロードする場合、COPYコマンドにオプションとしてFORCE = TRUEを追加することで、ロード済みの（最初にロードされたファイルと同じチェックサムを持つ）ファイルもロードされます。この場合にはステージングされていて削除されていないファイルが対象となるので、不要なファイルを削除しておくことで誤ってロードすることが防げます。

COPYコマンドによるスキャンファイル数の削減

COPYコマンド実行時には、Snowflakeによりステージングされている各ファイルがロード済みかがチェックされます。ステージングされているロード済みのファイルを削除することで、このスキャンファイル数を削減し、パフォーマンスを向上することができます。

検証準備

では、上記の利点を持ったPURGEオプションを実際に利用してみたいと思います。まずは検証の準備を進めます。

ステージの作成

今回は名前付きステージを作成して検証します。ファイルフォーマットも、用意したCSVファイルにあわせて「ヘッダ行付き」、「カンマ区切り」のCSVファイルフォーマットを指定しておきます。

$ snowsql
* SnowSQL * v1.2.7
Type SQL statements or !help
foo_bar#(no warehouse)@(no database).(no schema)>USE DATABASE OOTAKA_SANDBOX_DB;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.134s
foo_bar#(no warehouse)@OOTAKA_SANDBOX_DB.PUBLIC>create or replace stage zero_one_stage file_format = (type = csv field_delimiter = ',' skip_header = 1);
+-------------------------------------------------+
| status                                          |
|-------------------------------------------------|
| Stage area ZERO_ONE_STAGE successfully created. |
+-------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.156s
foo_bar#(no warehouse)@(no database).(no schema)>!q
Goodbye!

ステージが作成できました。

ファイルのステージング

では、ステージの準備ができたのでまずはファイルをステージング（アップロード）します。ステージングするファイルを改めて確認します。

$ cat /tmp/users.csv
id,name,age
1,Aruto Hiden,22
2,Izu,
3,Isamu Fuwa,27
4,Yua Yaiba,24
5,Gai Amatsu,45
6,Horobi,
7,Naki,
8,Jin,
9,Ikazuchi,

「ヘッダ行付き」、「カンマ区切り」のCSVです。このファイルをステージングします。

$ snowsql
* SnowSQL * v1.2.7
Type SQL statements or !help
foo_bar#(no warehouse)@(no database).(no schema)>USE DATABASE OOTAKA_SANDBOX_DB;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.693s
foo_bar#(no warehouse)@OOTAKA_SANDBOX_DB.PUBLIC>put file:///tmp/users.csv @zero_one_stage;
users.csv_c.gz(0.00MB): [##########] 100.00% Done (0.067s, 0.00MB/s).
+-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source    | target       | source_size | target_size | source_compression | target_compression | status   | message |
|-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------|
| users.csv | users.csv.gz |         130 |         147 | NONE               | GZIP               | UPLOADED |         |
+-----------+--------------+-------------+-------------+--------------------+--------------------+----------+---------+
1 Row(s) produced. Time Elapsed: 2.876s

ステージングされました。これで準備完了です。

検証してみる

では、検証してみます。PURGEオプション指定時は成功時のみステージングされているファイルが削除されるとのことなので、まずは失敗パターンを試してから成功パターンを試したいと思います。

COPY失敗時のパターン

まずは失敗時のパターンです。明示的に誤ったファイルフォーマット（「ヘッダ行なし」、「タブ区切り」のCSV）を指定してCOPYしてみようと思います。

copy into users from @zero_one_stage purge = true file_format = (type = csv field_delimiter = '\t' skip_header = 0);

SnowSQLで実行してみます。まずは、WAREHOUSEを指定しておきます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>USE WAREHOUSE X_SMALL_WH;
+----------------------------------+
| status                           |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.567s

では、COPYしてみます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>copy into users from @zero_one_stage purge = true file_format = (type = csv field_delimiter = '\t' skip_header = 0);
100080 (22000): Number of columns in file (1) does not match that of the corresponding table (3), use file format option error_on_column_count_mismatch=false to ignore this error
  File 'users.csv.gz', line 2, character 1
  Row 1 starts at line 1, column "USERS"["ID":1]
  If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

エラーになりましたね。ファイルが残っているか確認してみます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>LIST @zero_one_stage;
+-----------------------------+------+----------------------------------+-------------------------------+
| name                        | size | md5                              | last_modified                 |
|-----------------------------+------+----------------------------------+-------------------------------|
| zero_one_stage/users.csv.gz |  160 | 866515a99ab3d1a3220068ba7698e420 | Tue, 28 Jul 2020 01:19:53 GMT |
+-----------------------------+------+----------------------------------+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.179s

ちゃんと残っていますね！

COPY成功時のパターン

次に、成功時のパターンを試してみます。こちらは正しいフォーマットでロードするだけなので、PURGEオプションを付けるだけです。

copy into users from @zero_one_stage purge = true;

では、COPYしてみます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>copy into users from @zero_one_stage purge = true;
+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                        | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| zero_one_stage/users.csv.gz | LOADED |           9 |           9 |           1 |           0 | NULL        |             NULL |                  NULL | NULL                    |
+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 1.992s

成功しました。ファイルが消えたか確認してみます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>LIST @zero_one_stage;
+------+------+-----+---------------+
| name | size | md5 | last_modified |
|------+------+-----+---------------|
+------+------+-----+---------------+
0 Row(s) produced. Time Elapsed: 0.660s

ちゃんとファイルが自動で削除されていますね。便利！

後片付け

最後に、名前付きステージの削除をしておきます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>drop stage zero_one_stage;
+--------------------------------------+
| status                               |
|--------------------------------------|
| ZERO_ONE_STAGE successfully dropped. |
+--------------------------------------+
1 Row(s) produced. Time Elapsed: 0.843s

まとめ

以上、COPYコマンドのPURGEオプションを試してみました。個人的には、おそらくステージ上のファイルは、残しておくべきファイルは残しておき、削除するべきファイルは削除する、という方向になりそうなので、この場合にはPURGEオプションは削除の有無を意識してうまく利用する感じになるかなと思いました。

どなたかのお役に立てば幸いです。それでは！