Snowflakeのチュートリアル「Bulk Loading from Amazon S3 Using COPY」をやってみた #SnowflakeDB
これも20分くらいでできます
こんちわ。大阪オフィスの玉井です。
SnowflakeはS3にcsv等を置いておけば、そこからテーブルにデータをロード（コピー）することができます。今回はそれらに関する一連のチュートリアルをやってみました。
今回の作業環境
- Windows 10 Pro
- Google Chrome 77.0.3865.90
- Snowflake 2019/10/16時点の最新
ステップ0：準備
Snowflake側の準備
Snowflakeで、下記のクエリを実行しておきます（チュートリアルで使用するテーブル等の作成）。
create or replace database mydatabase; create or replace temporary table mycsvtable ( id integer, last_name string, first_name string, company string, email string, workphone string, cellphone string, streetaddress string, city string, postalcode string); create or replace temporary table myjsontable ( json_data variant); create or replace warehouse mywarehouse with warehouse_size='X-SMALL' auto_suspend = 120 auto_resume = true initially_suspended=true;
S3側の準備
このチュートリアル用にSnowflakeが用意しているS3バケットがあるので、特に準備は不要です。このバケットにはcsvファイルとjsonファイルが格納されています。
csvの一部
id|lastname|firstname|company|email|workphone|cellphone|streetaddress|city|postalcode 6|reed|moses|neque corporation|[email protected]|1-449-871-0780|1-454-964-5318|ap #225-4351 dolor ave|titagarh|62631
jsonの一部
[
{
"customer": {
"address": "509 Kings Hwy, Comptche, Missouri, 4848",
"phone": "+1 (999) 407-2274",
"email": "[email protected]",
"company": "ORBIN",
"name": {
"last": "Patrick",
"first": "Blankenship"
},
"_id": "5730864df388f1d653e37e6f"
}
},
]
ステップ1：ファイルフォーマットオブジェクトを作成する
ファイルフォーマットオブジェクトは、データをテーブルにコピーする際、元データ（ファイル）の形式を予め定義しておくものです。必須の作業ではないのですが、これをやっておくと、後でいちいち形式を指定しなくていいので、作業が楽になります。
cmtamai#[email protected]>use database MYDATABASE; +----------------------------------+ | status | |----------------------------------| | Statement executed successfully. | +----------------------------------+ 1 Row(s) produced. Time Elapsed: 0.156s cmtamai#[email protected]>create or replace file format mycsvformat_tutorial type = 'CSV' field_delimiter = '|' skip_header = 1; +--------------------------------------------------------+ | status | |--------------------------------------------------------| | File format MYCSVFORMAT_TUTORIAL successfully created. | +--------------------------------------------------------+ 1 Row(s) produced. Time Elapsed: 0.208s cmtamai#[email protected]>create or replace file format myjsonformat type = 'JSON' strip_outer_array = true; +------------------------------------------------+ | status | |------------------------------------------------| | File format MYJSONFORMAT successfully created. | +------------------------------------------------+
指定するパラメータは読めばわかるもので、わかりやすいです（ファイル形式とか区切り文字とか）。
また、最初にどのDBで作業するかを指定するために、USE DATABASEを使用しています。
ステップ2：名前付きステージオブジェクトを作成する
簡単にいうと「外部ストレージをSnowflakeに定義する」という感じの作業です。外部ストレージ（S3）の場所をSnowflakeに教えてあげるという感じでしょうか。ちなみに、ここで先程作成したファイルフォーマットオブジェクトを指定します。「このS3に入っているファイルはこの形式です」という形ですかね。
今回はSnowflakeが公開しているS3バケットを使用します（パブリックじゃないバケットの場合は別途アクセスキーが必要です）。
cmtamai#[email protected]>create or replace stage my_csv_stage file_format = mycsvformat_tutorial url = 's3://snowflake-docs'; +-----------------------------------------------+ | status | |-----------------------------------------------| | Stage area MY_CSV_STAGE successfully created. | +-----------------------------------------------+ 1 Row(s) produced. Time Elapsed: 2.191s cmtamai#[email protected]>create or replace stage my_json_stage file_format = myjsonformat url = 's3://snowflake-docs'; +------------------------------------------------+ | status | |------------------------------------------------| | Stage area MY_JSON_STAGE successfully created. | +------------------------------------------------+ 1 Row(s) produced. Time Elapsed: 2.362s
ステップ3：テーブルにデータをコピー
ファイル形式とS3を定義したところで、テーブルにS3（に配置している）のデータをコピーします。
csv
単一のファイルを指定
cmtamai#[email protected]>copy into mycsvtable from @my_csv_stage/tutorials/dataloading/contacts1.csv on_error = 'skip_file'; +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ | file | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name | |---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------| | s3://snowflake-docs/tutorials/dataloading/contacts1.csv | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ 1 Row(s) produced. Time Elapsed: 3.575s
ワイルドカードで複数のファイルを指定
cmtamai#[email protected]>copy into mycsvtable from @my_csv_stage/tutorials/dataloading/ pattern='.*contacts[1-5].csv' on_error = 'skip_file'; +---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+ | file | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name | |---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------| | s3://snowflake-docs/tutorials/dataloading/contacts2.csv | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | | s3://snowflake-docs/tutorials/dataloading/contacts4.csv | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | | s3://snowflake-docs/tutorials/dataloading/contacts3.csv | LOAD_FAILED | 5 | 0 | 1 | 2 | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error | 3 | 1 | "MYCSVTABLE"[11] | | s3://snowflake-docs/tutorials/dataloading/contacts5.csv | LOADED | 6 | 6 | 1 | 0 | NULL | NULL | NULL | NULL | +---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+ 4 Row(s) produced. Time Elapsed: 2.898s
上記のワイルドカード版を実行した時、処理結果としては下記のようになりました。
- contacts1.csvはさっきテーブルに入れたので処理はスルーされた
- contacts2.csv、contacts4.csv、contacts5.csvは正常に処理が完了した
- contacts3.csvは2件のエラーがあったため処理はスルーされた
ちなみに、今回のクエリではon_error = 'skip_file'としているため、エラーが発生しても、そこだけスキップして、後続の処理は実行するようになっています。デフォルトではエラーが発生した時点で処理はストップします。
json
cmtamai#[email protected]>copy into myjsontable from @my_json_stage/tutorials/dataloading/contacts.json on_error = 'skip_file'; +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ | file | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name | |---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------| | s3://snowflake-docs/tutorials/dataloading/contacts.json | LOADED | 3 | 3 | 1 | 0 | NULL | NULL | NULL | NULL | +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ 1 Row(s) produced. Time Elapsed: 2.731s
JSONファイルも正常にコピーできました。
ステップ4：エラーの原因を探る
ステップ3では、CSVファイルのコピー時にエラーが発生しました。このステップでエラーの原因をチェックします。
エラー情報をテーブルに格納する
まず、エラー自体の情報を確認する必要があります。Snowflakeの管理画面にログインし、画面上部メニューのHistoryを選びます。そして、先程エラーがあったクエリを見つけます。
該当クエリのQuery IDを選択してDetailを確認します。
Detail画面でQuery IDをコピっておき、下記のクエリを実行します。
create or replace table save_copy_errors as select * from table(validate(mycsvtable, job_id=>'<query_id>'));
cmtamai#[email protected]>create or replace table save_copy_errors as select * from table(validate(mycsvtable, job_id=>'018f9909-006e-4746-0000-12950003147e')); +----------------------------------------------+ | status | |----------------------------------------------| | Table SAVE_COPY_ERRORS successfully created. | +----------------------------------------------+ 1 Row(s) produced. Time Elapsed: 3.111s
格納したエラー情報を確認します。
cmtamai#[email protected]>select * from save_copy_errors; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | ERROR | FILE | LINE | CHARACTER | BYTE_OFFSET | CATEGORY | CODE | SQL_STATE | COLUMN_NAME | ROW_NUMBER | ROW_START_LINE | REJECTED_RECORD | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------| | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error | tutorials/dataloading/contacts3.csv | 3 | 1 | 234 | parsing | 100080 | 22000 | "MYCSVTABLE"[11] | 1 | 2 | 11|Ishmael|Burnett|Dolor Elit Pellentesque Ltd|[email protected]|1-872|600-7301|1-513-592-6779|P.O. Box 975, 553 Odio, Road|Hulste|63345 | | | | | | | | | | | | | | | Field delimiter '|' found while expecting record delimiter '\n' | tutorials/dataloading/contacts3.csv | 5 | 125 | 625 | parsing | 100016 | 22000 | "MYCSVTABLE"["POSTALCODE":10] | 4 | 5 | 14|Sophia|Christian|Turpis Ltd|[email protected]|1-962-503-3253|1-157-|850-3602|P.O. Box 824, 7971 Sagittis Rd.|Chattanooga|56188 | | | | | | | | | | | | | | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ 2 Row(s) produced. Time Elapsed: 0.595s
エラーの詳細を確認
上記でわかったのは、contacts3.csvで2件のエラーがあったということです。
- 1行目で、ハイフンが区切り文字であるパイプに変換されてしまい、列が1つ増えてしまってエラー
- 5行目で、ハイフンの後にパイプが追加されているため、レコードが破損扱いになってエラー
修正について
今回はデータの中身自体に問題があるため、手動なりなんなりでデータ自体を修正するしかありません。
ステップ5：データの確認
今回入れたデータは正しく入っているのでしょうか。それを確認します。普通のSELECT文が使えます。
cmtamai#[email protected]>select * from mycsvtable; +----+-----------+------------+----------------------------------+----------------------------------------+----------------+----------------+--------------------------------+------------------+------------+ | ID | LAST_NAME | FIRST_NAME | COMPANY | EMAIL | WORKPHONE | CELLPHONE | STREETADDRESS | CITY | POSTALCODE | |----+-----------+------------+----------------------------------+----------------------------------------+----------------+----------------+--------------------------------+------------------+------------| | 6 | Reed | Moses | Neque Corporation | [email protected] | 1-449-871-0780 | 1-454-964-5318 | Ap #225-4351 Dolor Ave | Titagarh | 62631 | | 7 | Audrey | Franks | Arcu Eu Limited | [email protected] | 1-527-945-8935 | 1-263-127-1173 | Ap #786-9241 Mauris Road | Bergen | 81958 | | 8 | Jakeem | Erickson | A Ltd | [email protected] | 1-381-591-9386 | 1-379-391-9490 | 319-1703 Dis Rd. | Pangnirtung | 62399 | | 9 | Xaviera | Brennan | Bibendum Ullamcorper Limited | [email protected] | 1-260-757-1919 | 1-211-651-0925 | P.O. Box 146, 8385 Vel Road | Béziers | 13082 | | 10 | Francis | Ortega | Vitae Velit Egestas Associates | [email protected] | 1-257-584-6487 | 1-211-870-2111 | 733-7191 Neque Rd. | Chatillon | 33081 | | 1 | Imani | Davidson | At Ltd | [email protected] | 1-243-889-8106 | 1-730-771-0412 | 369-6531 Molestie St. | Russell | 74398 | | 2 | Kelsie | Abbott | Neque Sed Institute | [email protected] | 1-467-506-9933 | 1-441-508-7753 | P.O. Box 548, 1930 Pede. Road | Campbellton | 27022 | | 3 | Hilel | Durham | Pede Incorporated | [email protected] | 1-752-108-4210 | 1-391-449-8733 | Ap #180-2360 Nisl. Street | Etalle | 84025 | | 4 | Graiden | Molina | Sapien Institute | [email protected] | 1-130-156-6666 | 1-269-605-7776 | 8890 A, Rd. | Dundee | 70504 | | 5 | Karyn | Howard | Pede Ac Industries | [email protected] | 1-109-166-5492 | 1-506-782-5089 | P.O. Box 902, 5398 Et, St. | Saint-Hilarion | 26232 | | 16 | Aretha | Sykes | Lobortis Tellus Justo Foundation | [email protected] | 1-670-849-1866 | 1-283-783-3710 | Ap #979-2481 Dui. Av. | Thurso | 66851 | | 17 | Akeem | Casey | Pharetra Quisque Ac Institute | [email protected] | 1-277-657-0361 | 1-623-630-8848 | Ap #363-6074 Ullamcorper, Rd. | Idar-Oberstei | 30848 | | 18 | Keelie | Mendez | Purus In Foundation | [email protected] | 1-330-370-8231 | 1-301-568-0413 | 3511 Tincidunt Street | Lanklaar | 73942 | | 19 | Lane | Bishop | Libero At PC | [email protected] | 1-340-862-4623 | 1-513-820-9039 | 7459 Pede. Street | Linkebeek | 89252 | | 20 | Michelle | Dickson | Ut Limited | [email protected] | 1-202-490-0151 | 1-129-553-7398 | 6752 Eros. St. | Stornaway | 61290 | | 20 | Michelle | Dickson | Ut Limited | [email protected] | 1-202-490-0151 | 1-129-553-7398 | 6752 Eros. St. | Stornaway | 61290 | | 21 | Lance | Harper | Rutrum Lorem Limited | [email protected] | 1-685-778-6726 | 1-494-188-6168 | 663-7682 Et St. | Gisborne | 73449 | | 22 | Keely | Pace | Eleifend Limited | [email protected] | 1-312-381-5244 | 1-432-225-9226 | P.O. Box 506, 5233 Aliquam Av. | Woodlands County | 61213 | | 23 | Sage | Leblanc | Egestas A Consulting | [email protected] | 1-630-981-0327 | 1-301-287-0495 | 4463 Lorem Road | Woodlands County | 33951 | | 24 | Marny | Holt | Urna Nec Luctus Associates | [email protected] | 1-522-364-3947 | 1-460-971-8360 | P.O. Box 311, 4839 Nulla Av. | Port Coquitlam | 36733 | | 25 | Holly | Park | Mauris PC | [email protected] | 1-370-197-9316 | 1-411-413-4602 | P.O. Box 732, 8967 Eu Avenue | Provost | 45507 | +----+-----------+------------+----------------------------------+----------------------------------------+----------------+----------------+--------------------------------+------------------+------------+ 21 Row(s) produced. Time Elapsed: 1.061s
cmtamai#[email protected]>select * from myjsontable; +-----------------------------------------------------------------+ | JSON_DATA | |-----------------------------------------------------------------| | { | | "customer": { | | "_id": "5730864df388f1d653e37e6f", | | "address": "509 Kings Hwy, Comptche, Missouri, 4848", | | "company": "ORBIN", | | "email": "[email protected]", | | "name": { | | "first": "Blankenship", | | "last": "Patrick" | | }, | | "phone": "+1 (999) 407-2274" | | } | | } | | { | | "customer": { | | "_id": "5730864d4d8523c8baa8baf6", | | "address": "290 Lefferts Avenue, Malott, Delaware, 1575", | | "company": "SNIPS", | | "email": "[email protected]", | | "name": { | | "first": "Anna", | | "last": "Glass" | | }, | | "phone": "+1 (958) 411-2876" | | } | | } | | { | | "customer": { | | "_id": "5730864e375e08523150fc04", | | "address": "756 Randolph Street, Omar, Rhode Island, 3310", | | "company": "ESCHOIR", | | "email": "[email protected]", | | "name": { | | "first": "Sparks", | | "last": "Ramos" | | }, | | "phone": "+1 (962) 436-2519" | | } | | } | +-----------------------------------------------------------------+ 3 Row(s) produced. Time Elapsed: 1.281s
JSONはそのまま格納されています。
ステップ6：後片付け
今回作成したテーブル等が不要な場合は、DROP文で削除することができます。
drop database if exists mydatabase; drop warehouse if exists mywarehouse;
おわりに
入れるファイルの形式を事前にしっかり把握しておくことが大事ですね。
![[アップデート] QuickSight で IAM 権限を持たないユーザーでもカスタムアクセス許可（制限）が設定できるようになっていました](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-19e77b9a51519e08b94d0f688c125729/4f439c75e1ee56c70e315fa46fa45f81/amazon-quicksight)
