外部テーブルの概要

典型的なテーブルでは、データはデータベースに保存されます。ただし、外部テーブルでは、データが外部ステージのファイルに保存されます。外部テーブルには、ファイル名、バージョン識別子、関連プロパティなど、データファイルに関するファイルレベルのメタデータが格納されます。これにより、外部ステージのファイルに保存されているデータを、データベース内にあるかのようにクエリできます。外部テーブルは、 COPY INTO <テーブル> ステートメントでサポートされる任意の形式で保存されたデータにアクセスできます。

外部テーブルは読み取り専用であるため、それらに対して DML 操作を実行できません。ただし、外部テーブルはクエリおよび結合操作に使用できます。ビューは外部テーブルに対して作成できます。

データベースの外部に保存されているデータのクエリは、ネイティブデータベーステーブルのクエリよりも遅い可能性があります。ただし、外部テーブルに基づいたマテリアライズドビューを使用すると、クエリのパフォーマンスを改善できます。

このトピックの内容:

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

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

読み取り時のスキーマ

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

VALUE

外部ファイルの単一の行を表す VARIANT タイプの列。

METADATA$FILENAME

外部テーブルに含まれる各ステージングデータファイルの名前を識別する疑似列。ステージ内のパスも含まれます。

METADATA$FILE_ROW_NUMBER

ステージングされたデータファイルの各記録の行番号を表示する疑似列。

外部テーブルを作成するには、ソースデータファイルのファイル形式とレコード形式に関する知識が必要です。データファイルのスキーマを知る必要はありません。クエリを実行すると、外部テーブルはすべての通常または半構造化データを 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> ... ] ) ]
  ..

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
  ..

必要な 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>'

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

Delta Lakeサポート

Delta Lake は、データレイク上のテーブル形式であり、サポートする機能には ACID (アトミック性、一貫性、分離、耐久性)トランザクションが含まれます。Delta Lakeのすべてのデータは、Apache Parquet形式で保存されます。Delta Lakeで拡張されたクラウドストレージの場所を参照する外部テーブルを作成します。これらの外部テーブルを作成するときは、必ず TABLE_FORMAT = DELTA パラメーターを設定してください。

列の追加またはドロップ

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

  • 列の追加: ALTER TABLE ... ADD COLUMN

  • 列の削除: ALTER TABLE ... DROP COLUMN

注釈

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

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

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

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

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

外部テーブルのメタデータは、保存場所ごとに次のイベント通知サービスを使用して自動的に更新できます:

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

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

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

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

注釈

現在、メタデータを自動的に更新する機能は、Google Cloud Storageステージを参照する外部テーブルでは使用できません。

回避策として、 データファイルをステージングするためのベストプラクティス 従うとともに、欠落したファイルを登録するために、 ALTER EXTERNAL TABLE ... REFRESH ステートメントを定期的に実行するようにお勧めします。満足のいくパフォーマンスを得るために、 ALTER EXTERNAL TABLE で選択パスプレフィックスを使用して、リストと既存登録の有無を確認する必要のあるファイル数を減らすようにお勧めします(例:ボリュームに応じて、 バケット名/YYYY/MM/DD/ または バケット名/YYYY/MM/DD/HH/)。

外部テーブルのメタデータを更新するための請求

(クラウドストレージに追加されたファイルの)イベント通知を管理するためのオーバーヘッドは料金に含まれています。このオーバーヘッドは、外部テーブルに指定された外部ステージとパスのクラウドストレージに追加されるファイルの数に関連して増加します。Snowflakeは、受信した1000イベント通知ごとに0.06クレジットを請求します。

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

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

ワークフロー

Amazon S3

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

  1. データファイルがステージングされる外部の場所(つまり、S3バケット)を参照する、名前付きステージオブジェクトを( CREATE STAGE を使用して)作成します。

  2. 名前付きステージを参照する外部テーブルを作成します( CREATE EXTERNAL TABLE を使用)。

  3. ALTER EXTERNAL TABLE ... REFRESH を使用して、外部テーブルのメタデータを手動で更新し、メタデータをステージパス内のファイルの現在のリストと同期します。このステップでは、外部テーブル定義の設定も確認します。

  4. S3バケットのイベント通知を構成します。Snowflakeは、イベント通知に依存して外部テーブルメタデータを継続的に更新し、ステージングされたファイルとの一貫性を維持します。

  5. ALTER EXTERNAL TABLE ... REFRESH を使用して、外部テーブルメタデータを手動でもう一度更新し、ステップ3以降に発生した変更とメタデータを同期します。その後、S3イベント通知はメタデータの更新を自動的にトリガーします。

  6. 追加のロールにSnowflakeアクセス制御権限を設定して、外部テーブルへのクエリアクセスを付与します。

Google Cloud Storage

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

  1. GCS イベントのGooglePub/Subサブスクリプションを構成します。

  2. Snowflakeで通知統合を作成します。通知統合は、SnowflakeとPub/Subなどサードパーティのクラウドメッセージキューサービス間におけるインターフェイスを提供する、Snowflakeオブジェクトです。

  3. データファイルがステージングされる外部の場所(つまり、 GCS バケット)を参照する、名前付きステージオブジェクトを( CREATE STAGE を使用して)作成します。

  4. 名前付きステージと統合を参照する外部テーブルを作成します( CREATE EXTERNAL TABLE を使用)。

  5. ALTER EXTERNAL TABLE ... REFRESH を使用して、外部テーブルメタデータを手動で一度更新し、ステップ4以降に発生した変更とメタデータを同期します。その後、Pub/Sub通知により、メタデータの更新が自動的にトリガーされます。

  6. 追加のロールにSnowflakeアクセス制御権限を設定して、外部テーブルへのクエリアクセスを付与します。

Microsoft Azure

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

  1. Azure StorageイベントのEvent Gridサブスクリプションを構成します。

  2. Snowflakeで通知統合を作成します。通知統合は、Snowflakeと、Microsoft Event Gridといった、サードパーティのクラウドメッセージキューサービス間のインターフェイスを提供するSnowflakeオブジェクトです。

  3. データファイルがステージングされる外部の場所(つまり、Azureコンテナー)を参照する、名前付きステージオブジェクトを( CREATE STAGE を使用して)作成します。

  4. 名前付きステージと統合を参照する外部テーブルを作成します( CREATE EXTERNAL TABLE を使用)。

  5. ALTER EXTERNAL TABLE ... REFRESH を使用して、外部テーブルメタデータを手動で一度更新し、ステップ4以降に発生した変更とメタデータを同期します。その後、Event Grid通知はメタデータの更新を自動的にトリガーします。

  6. 追加のロールにSnowflakeアクセス制御権限を設定して、外部テーブルへのクエリアクセスを付与します。

外部テーブルのクエリ

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

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

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

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

  • 句に VARIANT 列を含めることはできません。

  • 句には、次の 比較演算子 の1つ以上のみを含めることができます。

    • =

    • >

    • <

  • この句には、1つ以上の 論理/ブール演算子、また STARTSWITH SQL 関数のみを含めることができます。

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

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

次のテーブルは、このセクションの動作を示す同様のクエリ構造を示しています。ここで、 et は外部テーブルであり、 c1c2、および 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';

永続的なクエリ結果

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

  • 外部テーブル定義を変更する DDL 操作。これには、外部テーブル定義の明示的な変更(ALTER EXTERNAL TABLE を使用)または外部テーブルの再作成(CREATE OR REPLACE EXTERNAL TABLE を使用)が含まれます。

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

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

  • 自動更新操作が無効になっている(つまり、 AUTO_REFRESH = FALSE)か、正しく構成されていません。

  • 外部テーブルのメタデータは、手動では更新されません。

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

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

例:

  1. 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 + "/";
    
      $$;
    
  2. ストアドプロシージャを呼び出します。

    -- 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);
    

    または、 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

情報スキーマ

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

ビュー

EXTERNAL_TABLES ビュー

指定されたオブジェクトのメタデータに登録されているデータファイルの履歴と、これらの操作に対して請求されるクレジットを取得します。

テーブル関数

AUTO_REFRESH_REGISTRATION_HISTORY

指定されたオブジェクトのメタデータに登録されているデータファイルの履歴と、これらの操作に対して請求されるクレジットを取得します。

EXTERNAL_TABLE_FILES

指定された外部テーブルのメタデータに含まれるステージングされたデータファイルに関する情報を取得します。

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

メタデータの更新時に見つかったエラーを含む、外部テーブルのメタデータ履歴に関する情報を取得します。

最上部に戻る