データのロード

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

このトピックの内容：

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

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は、ステージ定義内のパスセグメントを保存場所からトリムし、残りのパスセグメントとファイル名に正規表現を適用します。ステージ定義を表示するには、ステージに対して DESCRIBE STAGE コマンドを実行します。URL プロパティは、バケット名またはコンテナー名と0個以上のパスセグメントで構成されます。たとえば、 COPY INTO <テーブル> ステートメントの FROM の場所が @s/path1/path2/ で、ステージ @s の URL の値が s3://mybucket/path1/ の場合、Snowpipeは保存場所から /path1/ をトリムします。 FROM 句で、正規表現を path2/ とパス内のファイル名に適用します。

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

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

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

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

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

この例では：

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

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

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

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

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

../_images/data-load-status2.png

この例では：

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

  • 64日が経ちます。 3月7日 に、ステージングされたファイルの 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
言語: 日本語