データのロード

このトピックでは、ベストプラクティス、一般的なガイドライン、およびステージ済みデータをロードするための重要な考慮事項について説明します。

このトピックの内容：

ステージングされたデータファイルを選択するためのオプション

COPY コマンドは、パス（内部ステージ）/プレフィックス（Amazon S3バケット）によるステージからデータファイルをロードするための、

  • いくつかのオプションをサポートしています。詳細については、 パスによるデータの整理 をご参照ください。

  • ロードする特定のファイルのリストを指定します。

  • パターンマッチングを使用して、パターンごとに特定のファイルを識別します。

これらのオプションを使用すると、1つのコマンドでステージングされたデータの一部をSnowflakeにコピーできます。これにより、並列操作を利用して、ファイルのサブセットに一致する COPY ステートメントを同時に実行できます。

ファイルのリスト

COPY INTO <テーブル> コマンドには、特定の名前でファイルをロードするための FILES パラメーターが含まれています。

Tip

ステージからロードするデータファイルを特定/指定するための3つのオプションのうち、ファイルの個別のリストを提供するのが一般的には最速です。ただし、 FILES パラメーターは最大1、000ファイルをサポートします。つまり、 COPY コマンドを FILES パラメーターで実行した場合、最大1、000ファイルしかロードできません。

例:

COPY INTO load1 FROM @%load1/data1/ FILES=('test1.csv', 'test2.csv', 'test3.csv')
Copy

ファイルリストをパスと組み合わせて、データのロードをさらに制御できます。

パターンマッチング

COPY INTO <テーブル> コマンドには、正規表現を使用してファイルをロードするための PATTERN パラメーターが含まれています。

例:

COPY INTO people_data FROM @%people_data/data1/
   PATTERN='.*person_data[^0-9{1,3}$$].csv';
Copy

正規表現を使用したパターンマッチングは、ステージからロードするデータファイルを識別/指定するための3つのオプションの中で通常最も低速です。ただし、外部アプリケーションから名前の付いた順序でファイルをエクスポートし、同じ順序でファイルをバッチロードする場合、このオプションは良好に機能します。

パターンマッチングをパスと組み合わせて、データのロードをさらに制御できます。

注釈

正規表現は、異なる方法で一括データロードとSnowpipeデータロードに適用されます。

  • Snowpipe trims any path segments in the stage definition from the storage location and applies the regular expression to any remaining path segments and filenames. To view the stage definition, execute the DESCRIBE STAGE command for the stage. The URL property consists of the bucket or container name and zero or more path segments. For example, if the FROM location in a COPY INTO <table> statement is @s/path1/path2/ and the URL value for stage @s is s3://mybucket/path1/, then Snowpipe trims s3://mybucket/path1/path2/ from the storage location in the FROM clause and applies the regular expression to the remaining filenames in the path.

  • 一括データロード操作は、 FROM 句の保存場所全体に正規表現を適用します。

Snowflakeは、Snowpipe用のクラウドイベントフィルタリングを有効にして、コスト、イベントノイズ、および遅延を削減するようにお勧めします。PATTERN オプションは、クラウドプロバイダーのイベントフィルタリング機能では不十分な場合にのみ使用してください。各クラウドプロバイダーのイベントフィルタリングの構成に関する詳細については、次のページをご参照ください。

同じデータファイルを参照する並列 COPY ステートメントの実行

COPY ステートメントが実行されると、Snowflakeは、ステートメントで参照されるデータファイルのテーブルメタデータにロードステータスを設定します。これにより、パラレル COPY ステートメントが同じファイルをテーブルにロードすることを防ぎ、データの重複を防ぎます。

COPY ステートメントの処理が完了すると、Snowflakeはデータファイルのロードステータスを適切に調整します。1つ以上のデータファイルのロードに失敗すると、Snowflakeはそれらのファイルのロードステータスをロード失敗として設定します。これらのファイルは、後続の COPY ステートメントでロードできます。

ワークロードが、同じテーブルにデータをロードする高度な並行処理 COPY ステートメントで構成されている場合は、Snowpipeを使用してください。このサービスは、並行処理 COPY ステートメントを処理するように設計されており、テーブルのメタデータ管理を含む並行処理をより有効に活用することができるからです。時間の経過とともに、既存の COPY ステートメントワークロードのSnowpipeへの移行を検討する必要があるかもしれません。これは、データ量や実行されるロードの頻度が変化するためかもしれません。その間に、 COPY ステートメントの間隔を空けることで、並行処理を減らし、パフォーマンスを向上させることができます。

古いファイルをロードする

このセクションでは、ファイルのロードステータスが既知であるか不明であるかに基づいて、 COPY INTO <テーブル> コマンドがデータの重複をどのように防止するかについて説明します。日付ごとの論理的で詳細なパス（ パスによるデータの整理 で推奨のとおり）を使用して段階的にデータを分割し、ステージング後に短時間でデータをロードする場合、このセクションはほとんど該当しません。ただし、 COPY コマンドがデータロードで古いファイル（つまり、履歴データファイル）をスキップする場合、このセクションではデフォルトの動作をバイパスする方法について説明します。

メタデータのロード

Snowflakeは、データがロードされる各テーブルの詳細なメタデータを保持します。

  • データのロード元の各ファイルの名前

  • ファイルサイズ

  • ファイル用 ETag

  • ファイルで解析された行の数

  • ファイルの最後のロードタイムスタンプ

  • ロード中にファイルで発生したエラーに関する情報

このロードメタデータは64日後に期限切れになります。ステージングされたデータファイルの LAST_MODIFIED の日付が64日以下の場合、 COPY コマンドは特定のテーブルのロードステータスを判断し、再ロード（およびデータの重複）を防ぐことができます。LAST_MODIFIED の日付は、ファイルが最初にステージングされたときまたは最後に変更されたときのどちらか遅い方のタイムスタンプです。

LAST_MODIFIED の日付が64日より古い時は、次のイベントの いずれか が現在の日付から64日以内に発生した場合、ロードステータスはまだ認識されます。

  • ファイルは正常にロードされました。

  • テーブルの最初のデータセット（テーブルが作成された後の最初のバッチ）のロード。

ただし、 COPY コマンドは、LAST_MODIFIED の日付が64日より古く、 かつ データの初期セットが64日より前にテーブルにロードされた場合（および ファイルがテーブルにロードされた場合は、それも64日より前に発生）、ファイルがすでにロードされたかどうかを断定的に判断することはできません。この場合、偶発的な再ロードを防ぐために、コマンドはデフォルトでファイルをスキップします。

回避策

メタデータの有効期限が切れたファイルをロードするには、 LOAD_UNCERTAIN_FILES コピーオプションをtrueに設定します。コピーオプションは、データの重複を避けるために、可能な場合はロードメタデータを参照しますが、期限切れのロードメタデータを含むファイルのロードも試みます。

または、 FORCE オプションを設定してすべてのファイルをロードし、ロードメタデータが存在する場合は無視します。このオプションはファイルを再ロードし、テーブル内のデータを複製する可能性があることに注意します。

例

../_images/data-load-status1.svg

この例では:

  • 3月1日 にテーブルが作成され、最初のテーブルのロードは同じ日に行われます。

  • 64日が経ちます。 5月4日 に、ロードメタデータの有効期限が切れます。

  • 7月1日 と 2日 に、ファイルがそれぞれステージングされ、テーブルにロードされます。ファイルはロードされる1日前にステージングされたため、 LAST_MODIFIED の日付は64日以内でした。ロードステータスは既知でした。ファイルにはデータやフォーマットの問題はなく、 COPY コマンドはファイルを正常にロードします。

  • 64日が経ちます。 9月3日 に、ステージングされたファイルの LAST_MODIFIED の日付が64日を超えます。 9月4日 に、ファイルのロードが成功した場合のロードメタデータの有効期限が切れます。

  • 11月1日 に、同じテーブルにファイルを再ロードしようとします。COPY コマンドはファイルが既にロードされているかどうかを判断できないため、ファイルはスキップされます。ファイルをロードするには、 LOAD_UNCERTAIN_FILES コピーオプション（または FORCE コピーオプション）が必要です。

../_images/data-load-status2.svg

この例では:

  • ファイルは 3月1日 にステージングされます。

  • 64日が経ちます。 5月4日 に、ステージングされたファイルの LAST_MODIFIED の日付が64日を超えます。

  • 9月29日 に、新しいテーブルが作成され、ステージングされたファイルがテーブルにロードされます。最初のテーブルのロードは64日以内に発生したため、 COPY コマンドによりファイルがまだロードされていないと判断できます。ファイルにはデータやフォーマットの問題はなく、 COPY コマンドはファイルを正常にロードします。

JSON データ: 「null」値の削除

VARIANT 列では、 NULL 値は SQL NULL 値ではなく、「null」という単語を含む文字列として保存されます。JSON ドキュメントの「null」値が欠損値を示し、他に特別な意味がない場合、ファイル形式オプションの STRIP_NULL_VALUES を TRUE （COPY INTO <テーブル> コマンド向け、 JSON ファイルをロードする際）に設定することをお勧めします。「null」値を保持すると、多くの場合、ストレージが無駄になり、クエリ処理が遅くなります。

CSV データ: 先行スペースのトリミング

外部ソフトウェアがフィールドを引用符で囲むものの、各フィールドの開始引用符文字の前に先行スペースを挿入する場合、Snowflakeはフィールドの先頭として開始引用文字ではなく先行スペースを読み取ります。引用文字は文字列データとして解釈されます。

TRIM_SPACE ファイル形式 オプションを使用して、データのロード中に不要なスペースを削除します。

たとえば、サンプルの CSV ファイルの次の各フィールドには、先行スペースが含まれています。

"value1", "value2", "value3"
Copy

次の COPY コマンドは、先頭のスペースをトリミングし、各フィールドを囲む引用符を削除します。

COPY INTO mytable
FROM @%mytable
FILE_FORMAT = (TYPE = CSV TRIM_SPACE=true FIELD_OPTIONALLY_ENCLOSED_BY = '0x22');

SELECT * FROM mytable;

+--------+--------+--------+
| col1   | col2   | col3   |
+--------+--------+--------+
| value1 | value2 | value3 |
+--------+--------+--------+
Copy
言語: 日本語