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

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

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

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

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

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

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

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

今回利用するファイルは以下のような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
);

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

「テーブルステージ」のおさらい

テーブルステージは「テーブル」に紐づくステージで、各テーブルにデフォルトで割り当てられています。テーブルに紐づくのでファイルへのアクセスは複数ユーザとなり、ファイルは紐づいているテーブルのみにCOPYすることができます。 また、対象テーブルのOWNERSHIP権限が必要となります。

これを踏まえて、ステージは既にできており、先程作成テーブルも自分自身で作成したのでOWNERSHIP権限があり、問題ないですね。

ファイルのステージング

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

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

put file:///data/data.csv @%mytable;

今回は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）必要があります。その上で、ファイルはテーブルステージのusersテーブル配下にステージングします。

$ 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.600s
foo_bar#(no warehouse)@OOTAKA_SANDBOX_DB.PUBLIC>put file:///tmp/users.csv @%users;
users.csv_c.gz(0.00MB): [##########] 100.00% Done (0.093s, 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.458s

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

foo_bar#(no warehouse)@OOTAKA_SANDBOX_DB.PUBLIC>list @%users;
+--------------+------+----------------------------------+-------------------------------+
| name         | size | md5                              | last_modified                 |
|--------------+------+----------------------------------+-------------------------------|
| users.csv.gz |  160 | 9979ce029e5474189bc761407236631d | Tue, 14 Jul 2020 00:50:11 GMT |
+--------------+------+----------------------------------+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.187s

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

データのコピー

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

コピーは以下のようにCOPYコマンドを使って、テーブルステージ上のファイルを、指定したファイルフォーマットでコピーできるようです。

copy into mytable file_format = (type = csv field_delimiter = '|' skip_header = 1);

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

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

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

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

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

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>copy into users 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 |
|--------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| users.csv.gz | LOADED |           9 |           9 |           1 |           0 | NULL        |             NULL |                  NULL | NULL                    |
+--------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 2.654s

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

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

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

ステージングしたファイルの削除

最後に、ステージングしたファイルをREMOVEコマンドで削除しておきます。

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

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

foo_bar#X_SMALL_WH@OOTAKA_SANDBOX_DB.PUBLIC>list @%users;
+------+------+-----+---------------+
| name | size | md5 | last_modified |
|------+------+-----+---------------|
+------+------+-----+---------------+
0 Row(s) produced. Time Elapsed: 0.191s

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

まとめ

以上、テーブルステージ経由でファイルをロードしてみました。テーブルステージもユーザーステージと同様に、デフォルトでテーブル毎に用意されているのでお手軽に利用できそうですね。

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