チュートリアル: COPYを使用したAmazon S3からの一括ロード¶

サンプルデータファイルについて¶

Snowflakeは、パブリックS3バケットにステージングされたサンプルデータファイルを提供します。

サンプルデータファイルには、サンプルの連絡先情報が次の形式で含まれています。

• CSV ヘッダー行と5つのレコードを含むファイル。フィールド区切り文字はパイプ（|）文字です。次の例は、ヘッダー行と1つの記録を示しています。

  ID|lastname|firstname|company|email|workphone|cellphone|streetaddress|city|postalcode 6|Reed|Moses|Neque Corporation|eget.lacus@facilisis.com|1-449-871-0780|1-454-964-5318|Ap #225-4351 Dolor Ave|Titagarh|62631 

  Copy

• 1つの配列と3つのオブジェクトを含む JSON 形式の1つファイル。オブジェクトの1つを含む配列の例を次に示します。

  [ { "customer": { "address": "509 Kings Hwy, Comptche, Missouri, 4848", "phone": "+1 (999) 407-2274", "email": "blankenship.patrick@orbin.ca", "company": "ORBIN", "name": { "last": "Patrick", "first": "Blankenship" }, "_id": "5730864df388f1d653e37e6f" } }, ] 

  Copy

データベース、テーブル、ウェアハウスの作成¶

次のステートメントを実行して、データベース、2つのテーブル（csvおよびJSONデータ用）、およびこのチュートリアルに必要な仮想ウェアハウスを作成します。チュートリアルの完了後に、これらのオブジェクトをドロップできます。

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; 

次の点に注意してください。

• CREATE DATABASE ステートメントはデータベースを作成します。データベースには、「public」という名前のスキーマが自動的に含まれます。

• CREATE TABLE ステートメントは、 CSV および JSON データのターゲットテーブルを作成します。テーブルは仮のものです。つまり、テーブルはユーザー セッションの間だけ保持され、他のユーザーには表示されません。

• CREATE WAREHOUSE ステートメントは、中断された初期状態のウェアハウスを作成します。このステートメントは AUTO_RESUME = true も設定します。これにより、コンピューティングリソースを必要とする SQL ステートメントを実行すると、ウェアハウスが自動的に開始されます。

CSV データのファイル形式オブジェクトの作成¶

CREATE FILE FORMAT コマンドを実行して、 mycsvformat ファイル形式を作成します。

CREATE OR REPLACE FILE FORMAT mycsvformat TYPE = 'CSV' FIELD_DELIMITER = '|' SKIP_HEADER = 1; 

条件:

• TYPE = 'CSV' は、ソースファイル形式の種類を示します。CSV は、デフォルトのファイル形式の種類です。

• FIELD_DELIMITER = '|' は、「|」がフィールド区切り文字であることを示します。デフォルト値は「,」です。

• SKIP_HEADER = 1 は、ソースファイルに1つのヘッダー行が含まれていることを示します。COPY コマンドは、データをロードするときにこれらのヘッダー行をスキップします。デフォルト値は0です。

JSON データのファイル形式オブジェクトの作成¶

CREATE FILE FORMAT コマンドを実行して、 myjsonformat ファイル形式を作成します。

CREATE OR REPLACE FILE FORMAT myjsonformat TYPE = 'JSON' STRIP_OUTER_ARRAY = TRUE; 

Copy

条件:

• TYPE = 'JSON' は、ソースファイル形式の種類を示します。

• STRIP_OUTER_ARRAY = TRUE は、データをテーブルにロードするときにルートブラケット（[]）を除外するように COPY コマンドに指示します。

CSV データファイル用ステージの作成¶

CREATE STAGE を実行して my_csv_stage ステージを作成します。

CREATE OR REPLACE STAGE my_csv_stage FILE_FORMAT = mycsvformat URL = 's3://snowflake-docs'; 

JSON データファイル用ステージの作成¶

CREATE STAGE を実行して my_json_stage ステージを作成します。

CREATE OR REPLACE STAGE my_json_stage FILE_FORMAT = myjsonformat URL = 's3://snowflake-docs'; 

CSV¶

サンプル CSV ファイルからデータをロードするには、

1. mycsvtable テーブルの contacts1.csv という名前の /tutorials/dataloading/ プレフィックス（フォルダー）内にあるファイルの1つからデータをロードすることから始めます。次を実行します。

   COPY INTO mycsvtable FROM @my_csv_stage/tutorials/dataloading/contacts1.csv ON_ERROR = 'skip_file'; 

   Copy

   条件:

   • FROM 句は、ステージングされたデータファイルの場所を指定します（ステージ名の後にファイル名が続きます）。

   • ON_ERROR = 'skip_file' 句は、 COPY コマンドでファイルにエラーが発生した場合の処理を指定します。この場合、コマンドは、ファイル内のいずれかの記録でデータエラーが発生すると、ファイルをスキップします。ON_ERROR 句を指定しない場合、デフォルトは abort_statement であり、ファイル内の記録において最初に発生したエラーで COPY コマンドを中止します。

   COPY コマンドは、コピーされたファイルの名前と関連情報を示す結果を返します。

   +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ | 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 | +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ 

   Copy

2. ステージングされたファイルの残りを mycsvtable テーブルにロードします。

   次の例では、パターンマッチングを使用して、正規表現 .*contacts[1-5].csv に一致するファイルからデータを mycsvtable テーブルにロードします。

   COPY INTO mycsvtable FROM @my_csv_stage/tutorials/dataloading/ PATTERN='.*contacts[1-5].csv' ON_ERROR = 'skip_file'; 

   Copy

   ここで、 PATTERN 句は、コマンドがこの正規表現 .*contacts[1-5].csv に一致するファイル名からデータをロードする必要があることを指定します。

   COPY コマンドは、コピーされたファイルの名前と関連情報を示す結果を返します。

   +---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+ | 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/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/contacts4.csv | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | | s3://snowflake-docs/tutorials/dataloading/contacts5.csv | LOADED | 6 | 6 | 1 | 0 | NULL | NULL | NULL | NULL | +---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+ 

   Copy

   結果の次のハイライトに注意してください。

   • contacts1.csv のデータは、既にデータを正常にロードしたため無視されます。

   • これらのファイル contacts2.csv、 contacts5.csv、および contacts4.csv のデータは正常にロードされました、

   • 2つのデータエラーのため、 contacts3.csv のデータはスキップされました。このチュートリアルの次のステップでは、エラーを検証および修正する方法について説明します。

JSON¶

contacts.json ステージングデータファイルを myjsontable テーブルにロードします。

COPY INTO myjsontable FROM @my_json_stage/tutorials/dataloading/contacts.json ON_ERROR = 'skip_file'; 

Copy

COPY は、コピーされたファイルの名前と関連情報を示す結果を返します。

+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ | 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 | +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ 

サンプルデータファイルを検証し、エラーを取得する¶

最初に、以前に実行した COPY INTO コマンドに関連付けられた取得クエリ ID が必要です。次に、クエリ ID を指定して VALIDATE 関数を呼び出します。

1. クエリ ID を取得します。

   1. Snowsight にサインインします。

   2. Snowsight のロールが、このチュートリアルの SQL ステートメントを実行するために SnowSQL で使用しているロールと同じであることを確認してください。

   3. Monitoring » Query History を選択します。

   4. 特定の COPY INTO コマンドの行を選択して、クエリ情報ペインを開きます。

   5. Query ID 値をコピーします。

2. クエリ ID で表される COPY INTO コマンドの実行を検証し、 save_copy_errors という名前の新しいテーブルにエラーを保存します。

   1. SnowSQL で、次のコマンドを実行します。 query_id を Query ID 値に置き換えます。

      CREATE OR REPLACE TABLE save_copy_errors AS SELECT * FROM TABLE(VALIDATE(mycsvtable, JOB_ID=>'<query_id>')); 

      Copy

   2. save_copy_errors テーブルをクエリします。

      SELECT * FROM SAVE_COPY_ERRORS; 

      Copy

      クエリは次の結果を返します。

      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 | mycsvtable/contacts3.csv.gz | 3 | 1 | 234 | parsing | 100080 | 22000 | "MYCSVTABLE"[11] | 1 | 2 | 11|Ishmael|Burnett|Dolor Elit Pellentesque Ltd|vitae.erat@necmollisvitae.ca|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' | mycsvtable/contacts3.csv.gz | 5 | 125 | 625 | parsing | 100016 | 22000 | "MYCSVTABLE"["POSTALCODE":10] | 4 | 5 | 14|Sophia|Christian|Turpis Ltd|lectus.pede@non.ca|1-962-503-3253|1-157-|850-3602|P.O. Box 824, 7971 Sagittis Rd.|Chattanooga|56188 | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ 

      Copy

結果は、 mycsvtable/contacts3.csv.gz に2つのデータエラーがあることを示しています。

• Number of columns in file (11) does not match that of the corresponding table (10)

  行1で、ハイフンが誤ってパイプ（|）文字（データファイルの区切り文字）に置き換えられ、事実上レコードに追加の列が作成されました。

  

• Field delimiter '|' found while expecting record delimiter 'n'

  行5で、ハイフンの後に追加のパイプ（|）文字が導入され、記録が破壊されました。

  

エラーを修正してデータファイルを再ロードする¶

通常の使用では、問題のあるレコードを手動で修正し、新しいデータファイルに書き込みます。次に、固定データファイルをS3バケットにステージングし、ファイルからデータを再ロードしようとします。このチュートリアルでは、Snowflakeが提供するサンプルデータを使用していますが、これは修正しません。

ロードされたデータを確認する¶

SELECT ステートメントを実行して、データが正常にロードされたことを確認します。

チュートリアルのクリーンアップ（オプション）¶

次の DROP <オブジェクト> コマンドを実行して、システムをチュートリアルを開始する前の状態に戻します。

DROP DATABASE IF EXISTS mydatabase; DROP WAREHOUSE IF EXISTS mywarehouse; 

Copy

データベースをドロップすると、テーブルなどのすべての子データベースオブジェクトが自動的に削除されます。

その他のデータロードチュートリアル¶

• Snowflakeを20分で紹介

• チュートリアル: COPY の使用によるローカルファイルシステムからの一括ロード