Zenn
🍣

SnowsqlのPUTを使ってみた

2023/02/11に公開
Snowflake
tech

PUTコマンドを使ってファイルアップロードしてみる

前提条件

  • Snowsqlが使えること
  • Snowflakeトライアルアカウントを持っていること
  • 今回は名前付きステージを使う

名前付きステージを作成する

*****#COMPUTE_WH@DEMO_DB.PUBLIC>create STAGE my_stage file_format = (type = csv FIELD_delimiter = ',' skip_header = 1);
+--------------------------------------------------+
| status                                           |
|--------------------------------------------------|
| ステージエリア MY_STAGE が正常に作成されました。 |
+--------------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.557s

アップロード用CSVファイルを作成する

アップロード用CSVファイル
name,age,sex
user1,10,F
user2,11,M
user3,12,F
user4,13,M
user5,14,F
user6,15,M
user7,16,F
user8,17,M
user9,18,F
user10,19,M
user11,20,F

テーブルを作成する

*****#COMPUTE_WH@DEMO_DB.PUBLIC>CREATE OR REPLACE TABLE TEST_TABLE (
                                 name VARCHAR(10),
                                 age STRING,
                                 sex VARCHAR(1));
+----------------------------------------------+
| status                                       |
|----------------------------------------------|
| テーブル TEST_TABLE が正常に作成されました。 |
+----------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.305s

PUTコマンドで外部ステージにアップロードする

*****#COMPUTE_WH@DEMO_DB.PUBLIC>put file://./test.csv @my_stage;
+----------+-------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source   | target      | source_size | target_size | source_compression | target_compression | status   | message |
|----------+-------------+-------------+-------------+--------------------+--------------------+----------+---------|
| test.csv | test.csv.gz |         159 |         128 | NONE               | GZIP               | UPLOADED |         |
+----------+-------------+-------------+-------------+--------------------+--------------------+----------+---------+
1 Row(s) produced. Time Elapsed: 1.225s

外部ステージの確認

*****#COMPUTE_WH@DEMO_DB.PUBLIC>list @my_stage;
+----------------------+------+----------------------------------+-------------------------------+
| name                 | size | md5                              | last_modified                 |
|----------------------+------+----------------------------------+-------------------------------|
| my_stage/test.csv.gz |  128 | 0652d8cc068906d257aea188418d0c67 | Mon, 30 Jan 2023 05:19:37 GMT |
+----------------------+------+----------------------------------+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.131s

COPYコマンドでTEST_TABLEに取り込む

*****#COMPUTE_WH@DEMO_DB.PUBLIC>copy into TEST_TABLE from @my_stage;
+----------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                 | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|----------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| my_stage/test.csv.gz | LOADED |          12 |          12 |           1 |           0 | NULL        |             NULL |                  NULL | NULL                    |
+----------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 2.811s

SELECT句で確認する

*****#COMPUTE_WH@DEMO_DB.PUBLIC>SELECT * FROM TEST_TABLE;
+--------+-----+-----+
| NAME   | AGE | SEX |
|--------+-----+-----|
| user1  | 10  | F   |
| user2  | 11  | M   |
| user3  | 12  | F   |
| user4  | 13  | M   |
| user5  | 14  | F   |
| user6  | 15  | M   |
| user7  | 16  | F   |
| user8  | 17  | M   |
| user9  | 18  | F   |
| user10 | 19  | M   |
| user11 | 20  | F   |
| user12 | 21  | M   |
+--------+-----+-----+
12 Row(s) produced. Time Elapsed: 1.134s

テーブル内部ステージへPUTでファイルアップロード

前提条件

  • すでに内部ステージを作成済みであること

putコマンドで内部ステージへアップロード

*****#COMPUTE_WH@DEMO_DB.PUBLIC>put file://./test.csv @%TEST_TABLE;
+----------+-------------+-------------+-------------+--------------------+--------------------+---------+---------+
| source   | target      | source_size | target_size | source_compression | target_compression | status  | message |
|----------+-------------+-------------+-------------+--------------------+--------------------+---------+---------|
| test.csv | test.csv.gz |         159 |           0 | NONE               | GZIP               | SKIPPED |         |
+----------+-------------+-------------+-------------+--------------------+--------------------+---------+---------+
1 Row(s) produced. Time Elapsed: 1.785s

内部ステージへアップロードされたことを確認

*****#COMPUTE_WH@DEMO_DB.PUBLIC>list @%TEST_TABLE;
+-------------+------+----------------------------------+-------------------------------+
| name        | size | md5                              | last_modified                 |
|-------------+------+----------------------------------+-------------------------------|
| test.csv.gz |  128 | 0ed34ac24b9823870d0964e1303fce39 | Mon, 30 Jan 2023 04:51:32 GMT |
+-------------+------+----------------------------------+-------------------------------+
1 Row(s) produced. Time Elapsed: 0.302s
*****#COMPUTE_WH@DEMO_DB.PUBLIC>SELECT * from @%TEST_TABLE;
+--------+-----+-----+
| NAME   | AGE | SEX |
|--------+-----+-----|
| name   | age | sex |
| user1  | 10  | F   |
| user2  | 11  | M   |
| user3  | 12  | F   |
| user4  | 13  | M   |
| user5  | 14  | F   |
| user6  | 15  | M   |
| user7  | 16  | F   |
| user8  | 17  | M   |
| user9  | 18  | F   |
| user10 | 19  | M   |
| user11 | 20  | F   |
| user12 | 21  | M   |
+--------+-----+-----+

COPY INTOでテーブルにコピーする

*****#COMPUTE_WH@DEMO_DB.PUBLIC>copy into TEST_TABLE from @%TEST_TABLE 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 |
|-------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| test.csv.gz | LOADED |          12 |          12 |           1 |           0 | NULL        |             NULL |                  NULL | NULL                    |
+-------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
1 Row(s) produced. Time Elapsed: 1.693s

テーブルの中身を確認してみる

*****#COMPUTE_WH@DEMO_DB.PUBLIC>SELECT * from TEST_TABLE;
+--------+-----+-----+
| NAME   | AGE | SEX |
|--------+-----+-----|
| user1  | 10  | F   |
| user2  | 11  | M   |
| user3  | 12  | F   |
| user4  | 13  | M   |
| user5  | 14  | F   |
| user6  | 15  | M   |
| user7  | 16  | F   |
| user8  | 17  | M   |
| user9  | 18  | F   |
| user10 | 19  | M   |
| user11 | 20  | F   |
| user12 | 21  | M   |
+--------+-----+-----+
12 Row(s) produced. Time Elapsed: 0.246s
*****#COMPUTE_WH@DEMO_DB.PUBLIC>

Discussion