Snowflakeの名前付きステージ経由でファイルをロードしてみた

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

Snowflakeには「ステージ」というSnowflakeのテーブルにファイルからデータをロードする際に、そのファイルを置く場所があります。

「ステージ」の種類としては大きく２つあり、Snowflake内部にある「内部ステージ」と、各種クラウド上（Amazon S3, Google Cloud Storage, Microsoft Azure）に存在するファイルとの架け橋になる「外部ステージ」があります。

今回はこのうちの「内部ステージ」における「名前付きステージ」を実際に利用してファイルのデータをテーブルへロードしてみました。

内部ステージについての概要は以下の記事でもまとめています。

「やりたいこと」と「事前準備」

ローカルPCにあるCSVファイルを、「テーブルステージ」を経由してSnowflake上のテーブルにロードしてみたいと思います。

今回利用するファイルは以下のようなCSVファイルとします。

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
);

これらを利用して、進めていきます。

「名前付きステージ」のおさらい

「名前付きステージ」は、「ユーザー」に紐づく「ユーザーステージ」や、「テーブル」に紐づく「テーブルステージ」とは異なり、デフォルトでは作成されないステージとなります。「データベース内のオブジェクト」扱いのステージとなり、各ユーザや各テーブルは紐づかないので、柔軟に制御が可能です。

ユーザーステージやテーブルステージはデフォルトで用意されているので作成は不要ですが、名前付きステージは自分自身で作成する必要があるので、まずはステージを作成する必要があります。なお、テーブルについては先程自分自身で作成しOWNERSHIP権限があるため、こちらは特に問題ありません。

ステージの作成

では、下記を参考にまずはステージを作成します。

名前付きステージは以下のようにCREATE STAGEコマンドで作成できるようです。

create or replace stage my_stage file_format = my_csv_format;

今回はSnowSQLを使ってzero_one_stageというステージを作成してみます。file_formatですが事前に別途オブジェクトを用意するのではなく直接フォーマットも以下のように指定してみます。

file_format = (type = csv field_delimiter = ',' skip_header = 1)

また、オブジェクトであるステージはデータベースに所属するので、まずはデータベースを選択する（USE DATABASE）必要があります。では、これらを踏まえて実際にステージを作成します。

$ 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.715s
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.163s
foo_bar#(no warehouse)@(no database).(no schema)>!q
Goodbye!

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

ファイルのステージング

では、準備ができたのでまずはファイルをステージング（アップロード）します。ファイルのステージングは下記を参考に進めます。

ステージングは以下のようにPUTコマンドを使って、ローカルファイルを指定の名前付きステージにステージングできるようです。

put file:///data/data.csv @my_stage;

今回はSnowSQLを使ってPUTしてみます。まずはステージングするファイルの確認から。

$ 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,

問題ないですね。再びSnowSQLを起動してこれをステージングしてみます。

名前付きステージの場合、ステージはデータベースに所属するので、まずはデータベースを選択する（USE DATABASE）必要があります。その上で、ファイルは先程作成した名前付きステージのzero_one_stageステージにステージングします。

$ 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.695s
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.076s, 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: 3.394s

ステージングされました！デフォルトでGZIP圧縮も行ってくれているのが分かります。念のため、LISTコマンドで名前付きステージを確認してみます。

foo_bar#(no warehouse)@OOTAKA_SANDBOX_DB.PUBLIC>list @zero_one_stage;
+-----------------------------+------+----------------------------------+-------------------------------+
| name                        | size | md5                              | last_modified                 |
|-----------------------------+------+----------------------------------+-------------------------------|
| zero_one_stage/users.csv.gz |  160 | 62e5f9db73ac6d67067083244bf37cb7 | Thu, 16 Jul 2020 00:48:44 GMT |
+-----------------------------+------+----------------------------------+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.197s

ちゃんとusers.csv.gzファイルがありますね。

データのコピー

ステージングが出来たので、ファイルからテーブルへデータをコピーしてみます。コピーについてはこちらを参考に実施します。

コピーは以下のようにCOPYコマンドを使って、名前付きステージ上のファイルをコピーできるようです。また、ユーザーステージやテーブルステージと違い、ファイルのフォーマットについては既に名前付きステージとして定義しているので、COPYコマンドで指定する必要はありません。

copy into mytable from @my_stage;

ではやってみます。まずは、ウェアハウスを指定しておきます。

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

テーブルの中身が空なのも確認しておきましょう。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>SELECT * FROM USERS;
+----+------+-----+
| ID | NAME | AGE |
|----+------+-----|
+----+------+-----+
0 Row(s) produced. Time Elapsed: 0.182s

空ですね。ではCOPYコマンドを使ってコピーしてみます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>copy into users from @zero_one_stage;
+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| 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.414s

成功しました！テーブルを確認してみます。

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: 0.277s

想定通りコピーされました！

ステージングしたファイルの削除と、名前付きステージの削除

最後に、ステージングしたファイルの削除と、名前付きステージの削除をしておきます。まずはファイルをREMOVEコマンドで削除してみます。

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>remove @zero_one_stage;
+-----------------------------+---------+
| name                        | result  |
|-----------------------------+---------|
| zero_one_stage/users.csv.gz | removed |
+-----------------------------+---------+
1 Row(s) produced. Time Elapsed: 0.636s

削除できました。一応確認してみます。

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.551s

問題なく消えていますね！

続けて、ステージ自体をDROP STAGEコマンドで削除します。今回は先にステージングしたファイルを削除しましたが、内部ステージではステージ自体を削除すればファイルも併せて削除されます。また、削除時のステージの指定（ここではzero_one_stage）には、作成時と同様に@は不要になります。

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.178s

こちらも問題なく削除されました！

まとめ

以上、名前付きステージ経由でファイルをロードしてみました。名前付きステージは、ユーザーステージやテーブルステージとは異なり自分自身でオブジェクトとして作成する必要がありますが、一方でメリットとして柔軟に制御することが出来るのでうまく使い分けたいですね。

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