外部テーブルの概要

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

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

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

このトピックの内容:

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

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

読み取り時のスキーマ

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

VALUE

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

METADATA$FILENAME

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

外部テーブルを作成するには、ソースデータファイルのファイル形式とレコード形式に関する知識が必要です。データファイルのスキーマを知る必要はありません。クエリを実行すると、外部テーブルはすべての通常または半構造化データを VALUE 列のバリアントにキャストします。

仮想列

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

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

外部テーブルをパーティション化することを強くお勧めします。これには、パスに日付、時刻、国、または同様のディメンションを含む論理パスを使用して基礎データを編成する必要があります。パーティション化は、パーティション列を使用して外部テーブルデータを複数の部分に分割します。パーティション列は、 METADATA$FILENAME 疑似列に格納されているパスまたはファイル名情報を解析する式として評価する 必要があります 。パーティションは、パーティション列の式のパスやファイル名に一致するすべてのデータファイルで構成されます。

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

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

パーティション列は、パーティション化の列定義のリストを含む CREATE EXTERNAL TABLE ... PARTITION BY 構文を使用して、外部テーブルが作成されたときにのみ定義できます。

構文

パーティション列を定義して、外部テーブルデータにパーティションを定義します:

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

例については、 CREATE EXTERNAL TABLE をご参照ください。

列の追加またはドロップ

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

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

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

注釈

デフォルトの VALUE および METADATA$FILENAME 列はドロップできません。

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

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

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

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

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

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

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

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

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

注釈

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

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

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

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

さらに、外部テーブルが一般的に利用可能になると(日付 TBD)、外部テーブルメタデータを手動で更新するために、わずかなメンテナンスオーバーヘッドが課金されます( ALTER EXTERNAL TABLE ... REFRESH を使用)。このオーバーヘッドは、外部ステージおよびパスで各オブジェクトに関する説明情報とともに、オブジェクトのリストを取得するときに消費されるリソースに含まれます。このオーバーヘッドのレートはまだ設定されていません。

ワークフロー

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 バケット)を参照する、名前付きステージオブジェクトを( CREATE STAGE を使用して)作成します。

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

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

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

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アクセス制御権限を設定して、外部テーブルへのクエリアクセスを付与します。

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 情報スキーマ には、外部テーブルとそのステージングされたデータファイルに関する情報を取得するためにクエリできるビューとテーブル関数が含まれています。

ビュー

EXTERNAL_TABLES ビュー

指定された(または現在の)データベース内の外部テーブルのリストを取得します。

テーブル関数

EXTERNAL_TABLE_FILES

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

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

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