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

サンプルデータファイルをダウンロードする¶

このチュートリアルには、Snowflakeが提供するサンプルデータファイルをダウンロードする必要があります。

サンプルデータファイルをダウンロードして解凍するには、

1. アーカイブファイル名 data-load-internal.zip を右クリックして、リンク/ファイルをローカルファイルシステムに保存します。

2. サンプルファイルを解凍します。このチュートリアルでは、次のディレクトリにファイルを解凍したことを前提としています。

• Linux/macOS: /tmp/load

• Windows： C:\tempload

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

• 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 a database. A database automatically includes a schema named 'public'.

CREATE OR REPLACE DATABASE mydatabase;

/* Create target tables for CSV and JSON data. The tables are temporary, meaning they persist only for the duration of the user session and are not visible to other users. */

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 a warehouse

CREATE OR REPLACE WAREHOUSE mywarehouse WITH
  WAREHOUSE_SIZE='X-SMALL'
  AUTO_SUSPEND = 120
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED=TRUE;

Copy

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;

条件:

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

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

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

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

CREATE OR REPLACE STAGE my_csv_stage
  FILE_FORMAT = mycsvformat;

ステージの作成時に FILE_FORMAT オプションを指定する場合は、ステージからデータをロードするために使用される COPY コマンドで、同じ FILE_FORMAT オプションを指定する必要はないことに注意してください。

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

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

CREATE OR REPLACE STAGE my_json_stage
  FILE_FORMAT = myjsonformat;

CSV サンプルデータファイルのステージング¶

PUT コマンドを実行して、ローカルファイルシステムから CSV ファイルをアップロードします。

• Linuxまたは macOS

  PUT file:///tmp/load/contacts*.csv @my_csv_stage AUTO_COMPRESS=TRUE;
  

  Copy

• Windows

  PUT file://C:\temp\load\contacts*.csv @my_csv_stage AUTO_COMPRESS=TRUE;
  

  Copy

コマンドを詳しく見てみましょう。

• file://<ファイルパス >[/]contacts*.csv は、ステージするローカルマシン上のファイルの完全なディレクトリパスとファイル名を指定します。ファイルシステムのワイルドカードが許可されています

• @my_csv_stage は、データをステージするステージ名です。

• auto_compress=true; は、ステージング時にデータを圧縮するようコマンドに指示します。これもデフォルトです。

このコマンドは、ステージングされたファイルを示す次の結果を返します。

+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source        | target           | source_size | target_size | source_compression | target_compression | status   | message |
|---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| contacts1.csv | contacts1.csv.gz |         694 |         506 | NONE               | GZIP               | UPLOADED |         |
| contacts2.csv | contacts2.csv.gz |         763 |         565 | NONE               | GZIP               | UPLOADED |         |
| contacts3.csv | contacts3.csv.gz |         771 |         567 | NONE               | GZIP               | UPLOADED |         |
| contacts4.csv | contacts4.csv.gz |         750 |         561 | NONE               | GZIP               | UPLOADED |         |
| contacts5.csv | contacts5.csv.gz |         887 |         621 | NONE               | GZIP               | UPLOADED |         |
+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+

JSON サンプルデータファイルのステージング¶

PUT コマンドを実行して、 JSON ファイルをローカルファイルシステムから名前付きステージにアップロードします。

• Linuxまたは macOS

  PUT file:///tmp/load/contacts.json @my_json_stage AUTO_COMPRESS=TRUE;
  

  Copy

• Windows

  PUT file://C:\temp\load\contacts.json @my_json_stage AUTO_COMPRESS=TRUE;
  

  Copy

このコマンドは、ステージングされたファイルを示す次の結果を返します。

+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source        | target           | source_size | target_size | source_compression | target_compression | status   | message |
|---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| contacts.json | contacts.json.gz |         965 |         446 | NONE               | GZIP               | UPLOADED |         |
+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+

ステージングされたファイルをリストする（オプション）¶

LIST コマンドを使用すると、ステージングされたファイルをリストできます。

CSV¶

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

1. ファイルの1つ（contacts1.csv.gz）からデータをロードすることから始めます。次を実行します。

   COPY INTO mycsvtable
     FROM @my_csv_stage/contacts1.csv.gz
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     ON_ERROR = 'skip_file';
   

   Copy

   条件:

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

   • ON_ERROR 句は、 COPY コマンドでファイルにエラーが発生した場合の処理を指定します。デフォルトでは、コマンドは最初のエラーが発生するとデータのロードを停止します。ただし、エラーを含むファイルはスキップし、次のファイルのロードに進むように指示しています。これは単に説明を目的としたものです。このチュートリアルのファイルにはエラーが含まれていません。

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

   +-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   | file                        | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
   |-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
   | mycsvtable/contacts1.csv.gz | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
   +-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   

   Copy

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

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

   COPY INTO mycsvtable
     FROM @my_csv_stage
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     PATTERN='.*contacts[1-5].csv.gz'
     ON_ERROR = 'skip_file';
   

   Copy

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

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

   +-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   | file                        | status      | rows_parsed | rows_loaded | error_limit | errors_seen | first_error                                                                                                                                                          | first_error_line | first_error_character | first_error_column_name |
   |-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------|
   | mycsvtable/contacts2.csv.gz | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | mycsvtable/contacts3.csv.gz | 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]        |
   | mycsvtable/contacts4.csv.gz | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | mycsvtable/contacts5.csv.gz | LOADED      |           6 |           6 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   +-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   

   Copy

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

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

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

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

JSON¶

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

COPY INTO myjsontable
  FROM @my_json_stage/contacts.json.gz
  FILE_FORMAT = (FORMAT_NAME = myjsonformat)
  ON_ERROR = 'skip_file';

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

+------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                         | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| myjsontable/contacts.json.gz | 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で、ハイフンの後に追加のパイプ（|）文字が導入され、記録が破壊されました。

  

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

1. ローカル環境で、 contacts3.csv ファイルにある記録のエラーを手動で修正します。

2. PUT コマンドを使用して、変更したデータファイルをステージにアップロードします。変更されたファイルは、既存のステージングされたファイルを上書きします。

   • Linuxまたは macOS：

     PUT file:///tmp/load/contacts3.csv @my_csv_stage AUTO_COMPRESS=TRUE OVERWRITE=TRUE;
     

     Copy

   • Windows：

     PUT file://C:\temp\load\contacts3.csv @my_csv_stage AUTO_COMPRESS=TRUE OVERWRITE=TRUE;
     

     Copy

3. ステージングされたファイルからテーブルにデータをコピーします。

   COPY INTO mycsvtable
     FROM @my_csv_stage/contacts3.csv.gz
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     ON_ERROR = 'skip_file';
   

   Copy

Snowflakeは次の結果を返し、 contacts3.csv.gz のデータが正常にロードされたことを示します。

+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                        | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| mycsvtable/contacts3.csv.gz | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

Copy

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

SELECT クエリを実行して、データが正常にロードされたことを確認します。

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

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

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

Copy

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

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

• Snowflakeを20分で紹介

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