外部テーブルの概要¶

外部テーブルとは、Snowflakeの機能で、外部ステージに格納されているデータを、あたかもSnowflakeのテーブル内にあるかのようにクエリすることができます。外部ステージはSnowflakeの一部ではないため、Snowflakeがステージを格納したり、管理したりすることはありません。

外部テーブルを使用すると、ファイル名、バージョン識別子、および関連プロパティを含む特定のファイルレベルのメタデータを（Snowflake内に）格納できます。外部テーブルは、 COPY INTO <テーブル> コマンドがサポートする任意の形式で格納されたデータにアクセスできます。

外部テーブルは読み取り専用です。データ操作言語（DML）の操作は実行できません。ただし、クエリや結合操作に外部テーブルを使用することはできます。また、外部テーブルに対するビューも作成できます。

外部テーブルのデータをクエリすると、Snowflake内のテーブルにネイティブで格納したデータをクエリするよりも遅くなる場合があります。クエリのパフォーマンスを向上させるために、外部テーブルを基にしたマテリアライズドビューを使用することができます。

注釈

クエリ操作中にクラウドストレージ内のファイルをスキャンしているときにSnowflakeでエラーが発生した場合、ファイルはスキップされ、スキャンは次のファイルで続行されます。クエリはファイルを部分的にスキャンし、エラーが発生する前にスキャンされた行を返すことができます。

このトピックの内容:

外部テーブルのスキーマの計画
外部テーブルのマテリアライズドビュー
外部テーブルのメタデータの自動更新
外部テーブルの請求
ワークフロー
外部テーブルのクエリ
- Parquetファイルの記録のフィルタリング
- 永続的なクエリ結果
外部テーブルメタデータからの古いステージングされたファイルの削除
Apache Hiveメタストアの統合
外部テーブル DDL
必要なアクセス権限
Information Schema
- ビュー
- テーブル関数

外部テーブルのスキーマの計画¶

このセクションでは、外部テーブルの設計に使用できるオプションについて説明します。

読み取り時のスキーマ¶

すべての外部テーブルには次の列が含まれます。

VALUE: 外部ファイルの単一の行を表す VARIANT 型の列。
METADATA$FILENAME: 外部テーブルに含まれるステージングされた各データファイルの名前を識別する疑似列。ステージ内のパスも含まれます。
METADATA$FILE_ROW_NUMBER: ステージングされたデータファイルの各記録の行番号を表示する疑似列。

外部テーブルを作成するには、ソースデータファイルのファイル形式とレコード形式に関する知識が必要です。データファイルのスキーマを知る必要はありません。

SELECT * は常にVALUE列を返すことに注意してください。この列では、すべての通常または半構造化データがバリアント行にキャストされます。

仮想列¶

ソースデータファイルのスキーマに精通している場合は、 VALUE 列および/または METADATA$FILENAME または METADATA$FILE_ROW_NUMBER 疑似列を使用して、式として追加の仮想列を作成できます。外部データをスキャンする場合、データファイル内の指定されたフィールドまたは半構造化データ要素のデータ型は、外部テーブル内のこれらの追加列のデータ型と一致する必要があります。これにより、外部データに対する強力な型チェックとスキーマ検証が可能になります。

一般的なファイルサイズの推奨事項¶

外部テーブルをクエリするときの並列スキャン操作の数を最適化するには、フォーマットごとに次のファイルまたは行グループのサイズをお勧めします。

形式	推奨サイズの範囲	メモ
Parquetファイル	256 - 512 MB
Parquet列グループ	16 - 256 MB	Parquetファイルに複数の行グループが含まれている場合、Snowflakeは異なるサーバーの各行グループで動作できます。クエリのパフォーマンスを向上させるために、Parquetファイルのサイズを推奨範囲内にすることをお勧めします。または、大きなファイルサイズが必要な場合には、各ファイルに複数の行グループを含めます。
サポートされている他のすべてのファイル形式	16 - 256 MB

大きなデータファイルをクエリするときに最適なパフォーマンスを得るには、外部テーブルに対してマテリアライズドビューを作成してクエリします。

パーティション化された外部テーブル¶

外部テーブルをパーティション化することを強くお勧めします。これには、パスに日付、時刻、国、または同様のディメンションを含む論理パスを使用して基礎データを編成する必要があります。パーティション化は、パーティション列を使用して外部テーブルデータを複数の部分に分割します。

外部テーブルの定義には、外部データに多次元構造を課す複数のパーティション列を含めることができます。パーティションは外部テーブルのメタデータに保存されます。

パーティション化の利点には、クエリパフォーマンスの向上が含まれます。外部データは個別のスライス/パーツに分割されるため、データセット全体をスキャンする代わりにデータのごく一部を処理する場合、クエリの応答時間が速くなります。

個々のユースケースに基づいて、次のいずれかを実行できます。

各パーティション列の式を定義する外部テーブルを更新することにより、新しいパーティションを自動的に追加します。
新しいパーティションを手動で追加します。

パーティション列は、 CREATE EXTERNAL TABLE ... PARTITION BY 構文を使用して、外部テーブルが作成されるときに定義されます。外部テーブルの作成後は、パーティションの追加メソッドを変更することはできません。

次のセクションでは、パーティションを追加するためのさまざまなオプションについて詳しく説明します。例については、 CREATE EXTERNAL TABLE をご参照ください。

自動的に追加されたパーティション¶

外部テーブル作成者は、新しい外部テーブルのパーティション列を、 METADATA$FILENAME 疑似列に保存されているパスやファイル名情報を解析する式として定義します。パーティションは、パーティション列の式のパスやファイル名に一致するすべてのデータファイルで構成されます。

式に基づいてパーティションを自動的に追加するための CREATE EXTERNAL TABLE 構文は次のとおりです。

CREATE EXTERNAL TABLE
  <table_name>
     ( <part_col_name> <col_type> AS <part_expr> )
     [ , ... ]
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  ..

Copy

Snowflakeは、外部テーブルのメタデータが更新されるときに、定義されたパーティション列の式に基づいてパーティションを計算して追加します。デフォルトでは、メタデータはオブジェクトの作成時に自動的に更新されます。さらに、オブジェクトの所有者は、新しいデータファイルまたは更新されたデータファイルが外部ステージで利用可能になったときに、自動的に更新されるようにメタデータを構成できます。または、所有者は ALTER EXTERNAL TABLE ... REFRESH コマンドを実行して、メタデータを手動で更新できます。

手動で追加されたパーティション¶

外部テーブル作成者は、新しい外部テーブルのパーティション型を ユーザー定義 として決定し、パーティション列のデータ型のみを指定します。式に一致する外部ストレージの場所にあるすべての新しいファイルのパーティションを自動的に追加するのではなく、パーティションを選択的に追加および削除する場合は、このオプションを使用します。

このオプションは通常、外部テーブルを他のメタストア（例: AWS GlueまたはApache Hive）と同期するために選択されます。

手動で追加されたパーティションの CREATE EXTERNAL TABLE 構文は次のとおりです。

CREATE EXTERNAL TABLE
  <table_name>
     ( <part_col_name> <col_type> AS <part_expr> )
     [ , ... ]
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  PARTITION_TYPE = USER_SPECIFIED
  ..

Copy

必要な PARTITION_TYPE = USER_SPECIFIED パラメーターを含めます。

パーティション列の定義は、内部（非表示） METADATA$EXTERNAL_TABLE_PARTITION 列の列メタデータを解析する式です。

オブジェクトの所有者は、 ALTER EXTERNAL TABLE ... ADD PARTITION コマンドを実行して、外部テーブルのメタデータにパーティションを手動で追加します。

ALTER EXTERNAL TABLE <name> ADD PARTITION ( <part_col_name> = '<string>' [ , <part_col_name> = '<string>' ] ) LOCATION '<path>'

Copy

ユーザー定義のパーティションを使用して外部テーブルを自動的に更新することはサポートされていません。この型の外部テーブルを手動で更新しようとすると、ユーザーエラーが発生します。

Delta Lakeサポート¶

Delta Lake は、データレイク上のテーブル形式であり、他の機能の中でも ACID （アトミック性、一貫性、分離性、耐久性）トランザクションをサポートします。Delta Lakeのすべてのデータは、Apache Parquet形式で保存されます。Delta Lakeで拡張されたクラウドストレージの場所を参照する外部テーブルを作成します。

Delta Lakeを参照する外部テーブルを作成するには、 CREATE EXTERNAL TABLE ステートメントで TABLE_FORMAT = DELTA パラメーターを設定します。

このパラメーターが設定されている場合、外部テーブルは、 [ WITH ] LOCATION の場所にあるDelta Lakeトランザクションログファイルをスキャンします。Deltaログファイルには、 _delta_log/00000000000000000000.json、 _delta_log/00000000000000000010.checkpoint.parquet などの名前が付いています。外部テーブルのメタデータが更新されると、SnowflakeはDelta Lakeトランザクションログを解析し、現在のParquetファイルを判別します。バックグラウンドで、更新はファイルの追加と削除の操作を実行して、外部テーブルのメタデータの同期を維持します。

クラウドストレージでの DDL 操作によってトリガーされるイベント通知の順序は保証されないことに注意してください。したがって、Delta Lakeファイルを参照する外部テーブルでは、自動的にメタデータを更新する機能は使用できません。代わりに、 ALTER EXTERNAL TABLE ... REFRESH ステートメントを定期的に実行して、追加または削除されたファイルを登録します。

例などの詳細については、 CREATE EXTERNAL TABLE をご参照ください。

列の追加またはドロップ¶

次の ALTER TABLE 構文を使用して、既存の外部テーブルを変更して列を追加または削除します。

列の追加： ALTER TABLE ... ADD COLUMN
列の削除： ALTER TABLE ... DROP COLUMN

注釈

デフォルトの VALUE 列と METADATA$FILENAME および METADATA$FILE_ROW_NUMBER 疑似列は削除できません。

ALTER TABLE の例をご参照ください。

外部テーブルの保護¶

マスキングポリシーと行アクセスポリシーを使用して外部テーブルを保護できます。詳細については、以下をご参照ください。

外部テーブルのマテリアライズドビュー¶

多くの場合、外部テーブルに対するマテリアライズドビューは、基礎となる外部テーブルに対する同等のクエリよりも高速なパフォーマンスを提供できます。このパフォーマンスの違いは、クエリが頻繁に実行される場合、または非常に複雑な場合に顕著になります。

マテリアライズドビューがクエリされたクラウドストレージの場所にある現在のファイルのセットを反映するように、クエリされた外部テーブルのファイルレベルのメタデータを更新します。

外部テーブルのメタデータは、クラウドストレージサービスのイベント通知サービスを使用して自動で、または ALTER EXTERNAL TABLE ... REFRESH を使用して手動で更新できます。

外部テーブルのメタデータの自動更新¶

外部テーブルのメタデータは、クラウドストレージサービスのイベント通知サービスを使用して自動的に更新できます。

更新操作は、メタデータを外部ステージと外部パスの関連ファイルの最新セットと同期します。つまり、

パス内の新しいファイルがテーブルメタデータに追加されます。

パス内のファイルへの変更は、テーブルメタデータで更新されます。

パス内になくなったファイルは、テーブルのメタデータから削除されます。

詳細については、外部テーブルの自動更新をご参照ください。

外部テーブルの請求¶

外部テーブルメタデータの自動更新のイベント通知を管理するためのオーバーヘッドは料金に含まれています。このオーバーヘッドは、外部テーブルに指定された外部ステージとパスのクラウドストレージに追加されるファイルの数に関連して増加します。Snowpipeは外部テーブルの自動更新のイベント通知に使用されるため、このオーバーヘッド料金は請求明細書にSnowpipe料金として表示されます。この料金は、 PIPE_USAGE_HISTORY 関数をクエリするか、Account Usage PIPE_USAGE_HISTORY ビューを調べると見積もることができます。

さらに、外部テーブルのメタデータを手動で更新する場合（ ALTER EXTERNAL TABLE ... REFRESH を使用）は、メンテナンスオーバーヘッドを若干請求します。このオーバーヘッドは、Snowflakeにおける他の類似のアクティビティすべてと同様に、標準のクラウドサービス課金モデルに従って課金されます。標準の外部テーブルの手動更新は、クラウドサービスの操作のみです。ただし、Delta Lakeで拡張された外部テーブルの手動更新は、ユーザー管理のコンピューティングリソース（つまり、仮想ウェアハウス）に依存しています。

ACCOUNTADMIN ロールを持つユーザー、または MONITOR USAGE グローバル権限を持つロールは、 AUTO_REFRESH_REGISTRATION_HISTORY テーブル関数にクエリを実行して、指定されたオブジェクトのメタデータに登録されているデータファイルの履歴と、これらの操作に対して請求されるクレジットを取得できます。

ワークフロー¶

Amazon S3¶

このセクションでは、Amazon S3ステージを参照する外部テーブルのセットアップおよびロードワークフローの概要を説明します。完全な手順については、 Amazon S3に対する外部テーブルの自動更新をご参照ください。

データファイルがステージングされる外部の場所（つまり、S3バケット）を参照する、名前付きステージオブジェクトを（ CREATE STAGE を使用して）作成します。
名前付きステージを参照する外部テーブルを作成します（ CREATE EXTERNAL TABLE を使用）。
ALTER EXTERNAL TABLE ... REFRESH を使用して、外部テーブルのメタデータを手動で更新し、メタデータをステージパス内のファイルの現在のリストと同期します。このステップでは、外部テーブル定義の設定も確認します。
S3バケットのイベント通知を構成します。Snowflakeは、イベント通知に依存して外部テーブルメタデータを継続的に更新し、ステージングされたファイルとの一貫性を維持します。
ALTER EXTERNAL TABLE ... REFRESH を使用して、外部テーブルメタデータを手動でもう一度更新し、ステップ3以降に発生した変更とメタデータを同期します。その後、S3イベント通知はメタデータの更新を自動的にトリガーします。
追加のロールにSnowflakeアクセス制御権限を設定して、外部テーブルへのクエリアクセスを付与します。

Google Cloud Storage¶

このセクションでは、Google Cloud Storage（GCS）ステージを参照する外部テーブルのセットアップおよびロードワークフローの概要を説明します。

GCS イベントのGooglePub/Subサブスクリプションを構成します。
Snowflakeで通知統合を作成します。通知統合は、SnowflakeとPub/Subなどサードパーティのクラウドメッセージキューサービス間におけるインターフェイスを提供する、Snowflakeオブジェクトです。
データファイルがステージングされる外部の場所（つまり、 GCS バケット）を参照する、名前付きステージオブジェクトを（ CREATE STAGE を使用して）作成します。
名前付きステージと統合を参照する外部テーブルを作成します（ CREATE EXTERNAL TABLE を使用）。
ALTER EXTERNAL TABLE ... REFRESH を使用して、外部テーブルメタデータを手動で一度更新し、ステップ4以降に発生した変更とメタデータを同期します。その後、Pub/Sub通知により、メタデータの更新が自動的にトリガーされます。
追加のロールにSnowflakeアクセス制御権限を設定して、外部テーブルへのクエリアクセスを付与します。

Microsoft Azure¶

このセクションでは、Azureステージを参照する外部テーブルのセットアップおよびロードワークフローの概要を説明します。完全な手順については、 Azure Blobストレージに対する外部テーブルの自動更新をご参照ください。

Azure StorageイベントのEvent Gridサブスクリプションを構成します。
Snowflakeで通知統合を作成します。通知統合は、Snowflakeと、Microsoft Event Gridといった、サードパーティのクラウドメッセージキューサービス間のインターフェイスを提供するSnowflakeオブジェクトです。
データファイルがステージングされる外部の場所（つまり、Azureコンテナー）を参照する、名前付きステージオブジェクトを（ CREATE STAGE を使用して）作成します。
名前付きステージと統合を参照する外部テーブルを作成します（ CREATE EXTERNAL TABLE を使用）。
ALTER EXTERNAL TABLE ... REFRESH を使用して、外部テーブルメタデータを手動で一度更新し、ステップ4以降に発生した変更とメタデータを同期します。その後、Event Grid通知はメタデータの更新を自動的にトリガーします。
追加のロールにSnowflakeアクセス制御権限を設定して、外部テーブルへのクエリアクセスを付与します。

外部テーブルのクエリ¶

標準テーブルと同じように、外部テーブルをクエリします。

Parquetファイルの記録のフィルタリング¶

行グループの統計を利用してParquetファイルのデータを整理するために、 WHERE 句にパーティション列または通常の列、あるいはその両方を含めることができます。次の制限が適用されます。

句に VARIANT 列を含めることはできません。
句には、次の比較演算子の1つ以上のみを含めることができます。
- =
- >
- <
この句には、1つ以上の論理/ブール演算子、また STARTSWITH SQL 関数のみを含めることができます。

さらに、 "value:<パス>::<データ型>" （または同等の GET / GET_PATH、 : 関数）形式のクエリは、ベクトル化されたスキャナーを利用します。 "value" または単に "value:<パス>" 形式のクエリは、ベクトル化されていないスキャナーを使用して処理されます。

クエリフィルターに含まれるキーでファイルを並べ替える場合、およびファイルに複数の行グループがある場合は、より適切なプルーニング結果が得られます。

次のテーブルは、このセクションの動作を示す同様のクエリ構造を示しています。ここで、 et は外部テーブルであり、 c1、 c2、および c3 は仮想列です。

最適化されている	最適化されていない
`SELECT c1, c2, c3 FROM et;`	`SELECT value:c1, c2, c3 FROM et;`
`SELECT c1, c2, c3 FROM et WHERE c1 = 'foo';` `SELECT c1, c2, c3 FROM et WHERE value:c1::string = 'foo';`	`SELECT c1, c2, c3 FROM et WHERE value:c1 = 'foo';`

最適化されている

最適化されていない

SELECT c1, c2, c3 FROM et;

SELECT value:c1, c2, c3 FROM et;

SELECT c1, c2, c3 FROM et WHERE c1 = 'foo';

SELECT c1, c2, c3 FROM et WHERE value:c1::string = 'foo';

SELECT c1, c2, c3 FROM et WHERE value:c1 = 'foo';

永続的なクエリ結果¶

テーブルと同様に、外部テーブルのクエリ結果は、24時間保存されます。この24時間の期間中、次の操作は外部テーブルのクエリ結果キャッシュを無効にしてパージします。

外部テーブル定義を変更する DDL 操作。これには、外部テーブル定義の明示的な変更（ALTER EXTERNAL TABLE を使用）または外部テーブルの再作成（CREATE OR REPLACE EXTERNAL TABLE を使用）が含まれます。
外部テーブルのメタデータに登録されている、クラウドストレージ内のファイルセットの変更。保存場所のイベント通知サービスを使用した自動更新操作、または手動更新操作（ALTER EXTERNAL TABLE ... REFRESH を使用）のいずれかにより、結果キャッシュが無効になります。

クラウドストレージ内の参照ファイルを変更しても、次の状況ではクエリ結果キャッシュが無効にならず、クエリ結果が古くなることに注意してください。

自動更新操作が無効になっている（つまり、 AUTO_REFRESH = FALSE）か、正しく構成されていません。
外部テーブルのメタデータは、手動では更新されません。

外部テーブルメタデータからの古いステージングされたファイルの削除¶

ストアドプロシージャは、 ALTER EXTERNAL TABLE ... REMOVE FILES ステートメントを使用して、外部テーブルのメタデータから古いステージングされたファイルを削除できます。ストアドプロシージャは、ステージで最後に変更された日付に基づいてメタデータからファイルを削除します。

例:

CREATE PROCEDURE ステートメントを使用してストアドプロシージャを作成します。

CREATE or replace PROCEDURE remove_old_files(external_table_name varchar, num_days float)
  RETURNS varchar
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
  // 1. Get the relative path of the external table
  // 2. Find all files registered before the specified time period
  // 3. Remove the files


  var resultSet1 = snowflake.execute({ sqlText:
    `call exttable_bucket_relative_path('` + EXTERNAL_TABLE_NAME + `');`
  });
  resultSet1.next();
  var relPath = resultSet1.getColumnValue(1);


  var resultSet2 = snowflake.execute({ sqlText:
    `select file_name
     from table(information_schema.EXTERNAL_TABLE_FILES (
         TABLE_NAME => '` + EXTERNAL_TABLE_NAME +`'))
     where last_modified < dateadd(day, -` + NUM_DAYS + `, current_timestamp());`
  });

  var fileNames = [];
  while (resultSet2.next())
  {
    fileNames.push(resultSet2.getColumnValue(1).substring(relPath.length));
  }

  if (fileNames.length == 0)
  {
    return 'nothing to do';
  }


  var alterCommand = `ALTER EXTERNAL TABLE ` + EXTERNAL_TABLE_NAME + ` REMOVE FILES ('` + fileNames.join(`', '`) + `');`;

  var resultSet3 = snowflake.execute({ sqlText: alterCommand });

  var results = [];
  while (resultSet3.next())
  {
    results.push(resultSet3.getColumnValue(1) + ' -> ' + resultSet3.getColumnValue(2));
  }

  return results.length + ' files: \n' + results.join('\n');

  $$;

  CREATE or replace PROCEDURE exttable_bucket_relative_path(external_table_name varchar)
  RETURNS varchar
  LANGUAGE javascript
  EXECUTE AS CALLER
  AS
  $$
  var resultSet = snowflake.execute({ sqlText:
    `show external tables like '` + EXTERNAL_TABLE_NAME + `';`
  });

  resultSet.next();
  var location = resultSet.getColumnValue(10);

  var relPath = location.split('/').slice(3).join('/');
  return relPath.endsWith("/") ? relPath : relPath + "/";

  $$;

Copy

ストアドプロシージャを呼び出します。
```
-- Remove all files from the exttable external table metadata:
call remove_old_files('exttable', 0);

-- Remove files staged longer than 90 days ago from the exttable external table metadata:
call remove_old_files('exttable', 90);
```
Copy
または、 CREATE TASK を使用して、ストアドプロシージャを定期的に呼び出して、外部テーブルのメタデータから古いファイルを削除するタスクを作成します。

Apache Hiveメタストアの統合¶

Snowflakeは、外部テーブルを使用した Apache Hive メタストアとSnowflakeの統合をサポートしています。Hiveコネクタは、メタストアイベントを検出し、それらをSnowflakeに送信して、外部テーブルとHiveメタストアの同期を維持します。これにより、ユーザーはHiveでデータを管理しながら、Snowflakeからクエリを実行できます。

手順については、 Apache HiveメタストアとSnowflakeの統合をご参照ください。

外部テーブル DDL¶

外部テーブルの作成と管理をサポートするために、Snowflakeは次の一連の特別な DDL コマンドを提供します：

必要なアクセス権限¶

外部テーブルを作成および管理するには、少なくとも次のロール権限を持つロールが必要です。

オブジェクト	権限
データベース	USAGE
スキーマ	USAGE、 CREATE STAGE （新しいステージを作成する場合）、 CREATE EXTERNAL TABLE
ステージ（既存のステージを使用する場合）	USAGE

Information Schema¶

Snowflake Snowflake Information Schema には、外部テーブルとそのステージングされたデータファイルに関する情報を取得するためにクエリできるビューとテーブル関数が含まれています。

ビュー¶

EXTERNAL_TABLES ビュー: 指定された（または現在の）データベース内の外部テーブルの情報を表示します。

テーブル関数¶

AUTO_REFRESH_REGISTRATION_HISTORY: 指定されたオブジェクトのメタデータに登録されているデータファイルの履歴と、これらの操作に対して請求されるクレジットを取得します。
EXTERNAL_TABLE_FILES: 指定された外部テーブルのメタデータに含まれる、ステージングされたデータファイルに関する情報を取得します。
EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY: メタデータの更新時に見つかったエラーを含む、外部テーブルのメタデータ履歴に関する情報を取得します。