この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは！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
);これらを利用して、進めていきます。
「テーブルステージ」のおさらい
テーブルステージは「テーブル」に紐づくステージで、各テーブルにデフォルトで割り当てられています。テーブルに紐づくのでファイルへのアクセスは複数ユーザとなり、ファイルは紐づいているテーブルのみに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問題なく消えていますね！
まとめ
以上、テーブルステージ経由でファイルをロードしてみました。テーブルステージもユーザーステージと同様に、デフォルトでテーブル毎に用意されているのでお手軽に利用できそうですね。
どなたかのお役に立てば幸いです。それでは！
24
