この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは!DA(データアナリティクス)事業本部 インテグレーション部の大高です。
Snowflakeの「内部ステージ」における「名前付きステージ」では、ステージの作成時にファイルフォーマットを指定するのでCOPYコマンド実行時にはファイルフォーマットを指定する必要はありません。
一方で「COPYコマンド実行時に明示的に指定したらどうなるのか?」というのが気になったので試してみました。
なお「名前付きステージ」を利用した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で実行します。
検証準備
ステージの作成
まずは、名前付きステージを作成します。ここでは用意したCSVファイルのCOPY時にあえてエラーになるように、以下のような「ヘッダ行無し」、「タブ区切り」ファイルのフォーマットを指定しておきます。
file_format = (type = csv field_delimiter = '\t' skip_header = 0)
では作ってみます。
$ 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.140s
foo_bar#(no warehouse)@OOTAKA_SANDBOX_DB.PUBLIC>create or replace stage zero_one_stage file_format = (type = csv field_delimiter = '\t' skip_header = 0);
+-------------------------------------------------+
| status |
|-------------------------------------------------|
| Stage area ZERO_ONE_STAGE successfully created. |
+-------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.204s
foo_bar#(no warehouse)@(no database).(no schema)>!q
Goodbye!
COPY時にエラーになるであろうステージが作成できました。
ファイルのステージング
では、準備ができたのでまずはファイルをステージング(アップロード)します。ステージングするファイルを改めて確認します。
$ 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.123s
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.075s, 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.690s
ステージングされました。これで準備完了です。
検証してみる
エラーが起きるかの確認
まずは、単純にCOPY
コマンドを実行して、ファイルフォーマットエラーが起きるか確認しておきます。
ウェアハウス指定が必要なので最初にUSE WAREHOUSE
をします。
foo_bar#(no warehouse)@OOTAKA_SANDBOX_DB.PUBLIC>USE WAREHOUSE X_SMALL_WH;
+----------------------------------+
| status |
|----------------------------------|
| Statement executed successfully. |
+----------------------------------+
1 Row(s) produced. Time Elapsed: 0.563s
では、COPY
してみます。
foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>copy into users from @zero_one_stage;
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.
想定通り、エラーになりましたね。
ファイルフォーマットを上書き指定してCOPYしてみる
それでは、本題です。ファイルフォーマットを上書き指定してCOPYしてみましょう。正しいフォーマットは以下になります。
file_format = (type = csv field_delimiter = ',' skip_header = 1)
これを指定してCOPY
コマンドを実行してみます。
foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>copy into users from @zero_one_stage file_format = (type = csv field_delimiter = ',' skip_header = 1);
+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| 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: 2.796s
成功しました!ちゃんとCOPYコマンド実行時に指定したものが優先されるようですね。一応、テーブルの中身も確認しておきます。
foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>SELECT * FROM USERS;
+----+-------------+-----+
| ID | NAME | AGE |
|----+-------------+-----|
| 1 | Aruto Hiden | 22 |
| 2 | Izu | NULL |
| 3 | Isamu Fuwa | 27 |
| 4 | Yua Yaiba | 24 |
| 5 | Gai Amatsu | 45 |
| 6 | Horobi | NULL |
| 7 | Naki | NULL |
| 8 | Jin | NULL |
| 9 | Ikazuchi | NULL |
+----+-------------+-----+
9 Row(s) produced. Time Elapsed: 1.191s
問題ないですね。
後片付け
最後に、名前付きステージの削除をして、ステージングしたファイルごと削除しておきます。
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.594s
まとめ
以上、COPYコマンド実行時にファイルフォーマットが上書き指定されるか試してみました。このような動作になっていると柔軟に対応できるので、嬉しい挙動ですね。
どなたかのお役に立てば幸いです。それでは!