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

概要

このチュートリアルでは、既存のAmazon Simple Storage Service（Amazon S3）バケットのファイルからテーブルにデータをロードする方法について説明します。このチュートリアルでは、次の方法を学習します。

  • データファイルを記述する名前付きファイル形式を作成します。

  • 名前付きステージオブジェクトを作成します。

  • S3バケットにあるデータをSnowflakeテーブルにロードします。

  • データファイルのエラーを解決します。

チュートリアルでは、 CSV と JSON の両方のデータのロードについて説明します。

前提条件

このチュートリアルでは、次を前提としています。

  • Amazon Web Services（AWS）を使用するように構成されたSnowflakeアカウントと、データベース、テーブル、および仮想ウェアハウスオブジェクトを作成するために必要な権限を付与するロールを持つユーザーがある。

  • SnowSQL がインストールされている。

これらの要件を満たす手順については、 Snowflakeを20分で紹介 をご参照ください。

Snowflakeは、このチュートリアルで使用するパブリックAmazon S3バケットにサンプルデータファイルを提供します。ただし、開始する前に、このチュートリアル用のデータベース、テーブル、および仮想ウェアハウスを作成する必要があります。これらは、Snowflakeのほとんどのアクティビティに必要となる基本的なSnowflakeオブジェクトです。

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

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

注釈

通常の使用では、 AWS 管理コンソール、 AWS コマンドラインインターフェイス、または同等のクライアントアプリケーションを使用して、独自のデータファイルをステージングします。 Amazon Web Services の手順のドキュメントをご参照ください。

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

  • 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;
Copy

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

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

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

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

ファイル形式オブジェクトを作成する

S3バケットからテーブルにデータファイルをロードする場合は、ファイルの形式を記述し、ファイル内のデータを解釈および処理する方法を指定する必要があります。たとえば、 CSV ファイルからパイプで区切られたデータをロードする場合は、ファイルがパイプ記号を区切り文字として使用する CSV 形式を使用するように指定する必要があります。

COPY INTO <テーブル> コマンドを実行するときに、この形式情報を指定します。この情報をコマンドのオプション（例: TYPE = CSV、 FIELD_DELIMITER = '|'）として指定するか、この形式情報を含むファイル形式オブジェクトを指定できます。 CREATE FILE FORMAT コマンドを使用して、名前付きファイル形式オブジェクトを作成できます。

このステップでは、このチュートリアルで提供されるサンプル CSV および JSON データのデータ形式を記述するファイル形式オブジェクトを作成します。

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

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

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

条件:

  • 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 コマンドに指示します。

ステージオブジェクトを作成する

ステージは、ファイル内のデータをテーブルにロードできるように、データファイルの保存場所（つまり、「ステージングした場所」）を指定します。名前付き 外部ステージ は、Snowflakeによって管理されるクラウドストレージの場所です。外部ステージは、S3バケットに保存されたデータファイルを参照します。この場合、チュートリアルを完了するために必要なサンプルデータファイルを参照するステージを作成しています。

複数のユーザーまたはプロセスでファイルをアップロードする場合は、名前付き外部ステージを作成すると便利です。自分だけがロードするようにデータファイルをステージングする場合や、単一のテーブルにのみロードする場合は、ユーザーステージまたはテーブルステージを使用することをお勧めします。詳細については、 Amazon S3からの一括ロード をご参照ください。

このステップでは、さまざまな種類のサンプルデータファイル用に名前付きステージを作成します。

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

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

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

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

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

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

注釈

通常の使用では、プライベートデータファイルをポイントするステージを作成する場合、アカウント管理者（つまり、ACCOUNTADMIN のロールを持つユーザー）またはグローバル CREATE INTEGRATION 権限を持つロールによって CREATE STORAGE INTEGRATION を使用して作成されたストレージ統合を参照します。

CREATE OR REPLACE STAGE external_stage
  FILE_FORMAT = mycsvformat
  URL = 's3://private-bucket'
  STORAGE_INTEGRATION = myint;
Copy

データをターゲットテーブルにコピーする

COPY INTO <テーブル> を実行して、ステージングされたデータをターゲットテーブルにロードします。

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

クリーンアップする

おめでとうございます。チュートリアルを無事完了しました。

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

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

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

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

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

言語: 日本語