CREATE EXTERNAL TABLE

現在の/指定されたスキーマに新しい外部テーブルを作成するか、既存の外部テーブルを置き換えます。クエリを実行すると、外部テーブルは指定された外部ステージの1つ以上のファイルのセットからデータをロードし、単一の VARIANT 列にデータを出力します。

追加の列を定義できます。各列の定義は、名前、データ型、およびオプションで列に値が必要か(NOT NULL)か、参照整合性制約(主キー、外部キーなど)があるかで構成されます。詳細については、 使用上の注意をご参照ください。

こちらもご参照ください。

ALTER EXTERNAL TABLEDROP EXTERNAL TABLESHOW EXTERNAL TABLESDESCRIBE EXTERNAL TABLE

構文

-- Partitions computed from expressions
CREATE [ OR REPLACE ] EXTERNAL TABLE [IF NOT EXISTS]
  <table_name>
    ( [ <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ]
      [ inlineConstraint ]
      [ , <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ... ]
      [ , ... ] )
  cloudProviderParams
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  [ WITH ] LOCATION = externalStage
  [ REFRESH_ON_CREATE =  { TRUE | FALSE } ]
  [ AUTO_REFRESH = { TRUE | FALSE } ]
  [ PATTERN = '<regex_pattern>' ]
  FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET } [ formatTypeOptions ] } )
  [ AWS_SNS_TOPIC = '<string>' ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

-- Partitions added and removed manually
CREATE [ OR REPLACE ] EXTERNAL TABLE [IF NOT EXISTS]
  <table_name>
    ( [ <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ]
      [ inlineConstraint ]
      [ , <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ... ]
      [ , ... ] )
  cloudProviderParams
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  [ WITH ] LOCATION = externalStage
  PARTITION_TYPE = USER_SPECIFIED
  FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET } [ formatTypeOptions ] } )
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]

-- Delta Lake
CREATE [ OR REPLACE ] EXTERNAL TABLE [IF NOT EXISTS]
  <table_name>
    ( [ <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ]
      [ inlineConstraint ]
      [ , <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ... ]
      [ , ... ] )
  cloudProviderParams
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  [ WITH ] LOCATION = externalStage
  PARTITION_TYPE = USER_SPECIFIED
  FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET } [ formatTypeOptions ] } )
  [ TABLE_FORMAT = DELTA ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
Copy

条件:

inlineConstraint ::=
  [ NOT NULL ]
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY | [ FOREIGN KEY ] REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> ] ) ] }
  [ <constraint_properties> ]
Copy

インライン制約の詳細については、 CREATE | ALTER TABLE ... CONSTRAINT をご参照ください。

cloudProviderParams (for Google Cloud Storage) ::=
  [ INTEGRATION = '<integration_name>' ]

cloudProviderParams (for Microsoft Azure) ::=
  [ INTEGRATION = '<integration_name>' ]
Copy
externalStage ::=
  @[<namespace>.]<ext_stage_name>[/<path>]
Copy
formatTypeOptions ::=
-- If FILE_FORMAT = ( TYPE = CSV ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     RECORD_DELIMITER = '<character>' | NONE
     FIELD_DELIMITER = '<character>' | NONE
     SKIP_HEADER = <integer>
     SKIP_BLANK_LINES = TRUE | FALSE
     ESCAPE_UNENCLOSED_FIELD = '<character>' | NONE
     TRIM_SPACE = TRUE | FALSE
     FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | NONE
     NULL_IF = ( '<string1>' [ , '<string2>' , ... ] )
     EMPTY_FIELD_AS_NULL = TRUE | FALSE
     ENCODING = '<string>'
-- If FILE_FORMAT = ( TYPE = JSON ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     ALLOW_DUPLICATE = TRUE | FALSE
     STRIP_OUTER_ARRAY = TRUE | FALSE
     STRIP_NULL_VALUES = TRUE | FALSE
     REPLACE_INVALID_CHARACTERS = TRUE | FALSE
-- If FILE_FORMAT = ( TYPE = AVRO ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
     REPLACE_INVALID_CHARACTERS = TRUE | FALSE
-- If FILE_FORMAT = ( TYPE = ORC ... )
     TRIM_SPACE = TRUE | FALSE
     REPLACE_INVALID_CHARACTERS = TRUE | FALSE
     NULL_IF = ( '<string>' [ , '<string>' ... ]
-- If FILE_FORMAT = ( TYPE = PARQUET ... )
     COMPRESSION = AUTO | SNAPPY | NONE
     BINARY_AS_TEXT = TRUE | FALSE
     REPLACE_INVALID_CHARACTERS = TRUE | FALSE
Copy

バリアント構文

CREATE EXTERNAL TABLE ... USING TEMPLATE

半構造化データを含むステージングされたファイルのセットから派生した列定義で、新しい外部テーブルを作成します。この機能は、Apache Parquet、Apache Avro、 ORC、 JSON、 CSV ファイルをサポートしています。CSV および JSON ファイルのサポートは、現在プレビュー中です。

CREATE [ OR REPLACE ] EXTERNAL TABLE <table_name>
  [ COPY GRANTS ]
  USING TEMPLATE <query>
  [ ... ]
Copy

注釈

ステートメントにより同じ名前の既存のテーブルを置き換える場合、権限は置き換えられるテーブルからコピーされます。その名前の既存のテーブルがない場合、権限はクローンされるソーステーブルからコピーされます。

COPY GRANTS の詳細については、このドキュメント内の COPY GRANTS をご参照ください。

必須パラメーター

table_name

テーブルの識別子(つまり、名前)を指定する文字列。テーブルが作成されるスキーマに対して一意である必要があります。

また、識別子はアルファベット文字で始まる必要があり、識別子文字列全体が二重引用符で囲まれていない限り、スペースや特殊文字を含めることはできません(例: "My object")。二重引用符で囲まれた識別子も大文字と小文字が区別されます。

詳細については、 識別子の要件 をご参照ください。

[ WITH ] LOCATION =

ロードするデータを含むファイルがステージングされる外部ステージとオプションのパスを指定します。

@[namespace.]ext_stage_name[/path]

ファイルは指定された名前付き外部ステージにあります。

文字列リテラルも SQL 変数もサポートされていません。

条件:

  • namespace は、外部ステージが存在するデータベースまたはスキーマ、あるいはその両方で、 database_name.schema_name または schema_name の形式です。ユーザーセッション内でデータベースとスキーマが現在使用されている場合は オプション です。それ以外の場合は必須です。

  • path は、ロードするファイルのセットを制限するクラウドストレージの場所にある、ファイルの大文字と小文字を区別するオプションのパスです(つまり、ファイルの名前は一般的な文字列で開始)。パスは、別のクラウドストレージサービスによって プレフィックス または フォルダー と呼ばれることもあります。

    外部テーブルは、このパスをステージ定義で指定された任意のパスに追加します。ステージの定義を表示するには、 DESC STAGE stage_name を実行し、 url プロパティ値を確認します。たとえば、ステージ URL にパス a が含まれ、外部テーブルの場所にパス b が含まれる場合、外部テーブルは stage/a/b にステージングされたファイルを読み取ります。

    [ WITH ] LOCATION 値は、特定のファイル名を参照 できない ことに注意してください。外部テーブルが個別のステージングされたファイルにポイントするには、 PATTERN パラメーターを使用します。

FILE_FORMAT = ( FORMAT_NAME = 'file_format_name' ) または . FILE_FORMAT = ( TYPE = CSV | JSON | AVRO | ORC | PARQUET [ ... ] )

ファイル形式を指定する文字列(定数):

FORMAT_NAME = file_format_name

スキャンするステージングされたデータファイルを記述する既存の名前付きファイル形式を指定します。名前付きファイル形式は、データファイルの形式タイプ(CSV、 JSONなど)およびその他の形式オプションを決定します。

TYPE = CSV | JSON | AVRO | ORC | PARQUET [ ... ]

外部テーブルのクエリ時にスキャンするステージングされたデータファイルの形式タイプを指定します。

ファイル形式タイプが指定されている場合は、追加の形式固有のオプションを指定できます。詳細については、 形式タイプオプション (このトピック)をご参照ください。

ファイル形式オプションは、外部テーブルまたはステージレベルで構成できます。外部テーブルレベルで指定された設定が優先されます。どちらのレベルでも指定されていない設定は、デフォルト値を想定しています。

デフォルト: TYPE = CSV

重要

外部テーブルは、ステージ定義のファイル形式を 継承しません 。 FILE_FORMAT パラメーターを使用して、外部テーブルのファイル形式オプションを明示的に指定する 必要があります

注釈

FORMAT_NAMETYPE は相互に排他的です。意図しない動作を避けるために、外部テーブルを作成するときはどちらか一方のみを指定する必要があります。

オプションのパラメーター

col_name

列識別子(名前)を指定する文字列。テーブル識別子のすべての要件は、列識別子にも適用されます。

詳細については、 識別子の要件 をご参照ください。

col_type

列のデータ型を指定する文字列(定数)。データ型は、列の expr の結果と一致する必要があります。

テーブル列に指定できるデータ型の詳細については、 SQL データ型リファレンス をご参照ください。

expr

列の式を指定する文字列。クエリすると、列はこの式から派生した結果を返します。

外部テーブルの列は、明示的な式を使用して定義される仮想列です。VALUE 列および/または METADATA$FILENAME 疑似列を使用し、式として仮想列を追加します。

VALUE

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

CSV

VALUE 列は、各行を、列の位置(つまり、 {c1: <列1の値>, c2: <列2の値>, c3: <列1の値> ...})で識別される要素を持つオブジェクトとして構成します。

たとえば、ステージングされた CSV ファイル内の最初の列を参照する mycol という名前の VARCHAR 列を追加します。

mycol varchar as (value:c1::varchar)
Copy
半構造化データ

要素の名前と値を二重引用符で囲みます。ドット表記を使用して、 VALUE 列のパスを走査します。

たとえば、次がステージングされたファイル内の半構造化データの単一行を表すとします。

{ "a":"1", "b": { "c":"2", "d":"3" } }
Copy

ステージングされたファイル内のネストされた繰り返し c 要素を参照する mycol という名前の VARCHAR 列を追加します。

mycol varchar as (value:"b"."c"::varchar)
Copy
METADATA$FILENAME

外部テーブルに含まれる各ステージングデータファイルの名前を識別する疑似列。ステージ内のパスも含まれます。例については、 パーティション列式から自動的に追加されたパーティション (このトピック内)をご参照ください。

CONSTRAINT ...

テーブル内の指定された列のインライン制約またはアウトライン制約を定義する文字列。

構文の詳細については、 CREATE | ALTER TABLE ... CONSTRAINT をご参照ください。制約の詳細については、 制約 をご参照ください。

REFRESH_ON_CREATE = TRUE | FALSE

外部テーブルが作成された直後に、外部テーブルのメタデータを1回自動的に更新するかどうかを指定します。外部テーブルのメタデータを更新すると、指定したステージパス内のデータファイルの現在のリストとメタデータが同期されます。このアクションは、メタデータが [ WITH ] LOCATION = 設定で指定された名前付き外部ステージに 既存の データファイルを登録するために必要です。

TRUE

Snowflakeは、作成後に外部テーブルのメタデータを自動的に更新します。

注釈

指定した場所に100万個近くかそれ以上のファイルが含まれている場合は、 REFRESH_ON_CREATE = FALSE を設定することをお勧めします。外部テーブルを作成した後、メタデータにその場所のファイルすべてが含まれるまで、その場所のサブパス(つまり、更新に含めるファイルのサブセット)を指定する ALTER EXTERNAL TABLE ... REFRESH ステートメントを実行して、メタデータを段階的に更新します。

FALSE

Snowflakeは、外部テーブルのメタデータを自動的に更新しません。ステージに既存のデータファイルを登録するには、 ALTER EXTERNAL TABLE ... REFRESH を使用して外部テーブルのメタデータを手動で1回更新します。

デフォルト: TRUE

AUTO_REFRESH = TRUE | FALSE

[ WITH ] LOCATION = 設定で指定された名前付き外部ステージで 新しいまたは更新された データファイルが使用可能な場合に、Snowflakeが外部テーブルメタデータの自動更新のトリガーを有効にするかどうかを指定します。

注釈

  • オブジェクト所有者がパーティションを手動で追加した場合(つまり、 PARTITION_TYPE = USER_SPECIFIED の場合)、このパラメーターを TRUE に設定することは、パーティションされた外部テーブルではサポートされて いません

  • このパラメーターを TRUE に設定することは、 S3互換の外部ステージ に格納されたデータファイルを参照する外部テーブルではサポート されませんALTER EXTERNAL TABLE ... REFRESH コマンドを実行して、手動でメタデータをリフレッシュする必要があります。

  • ストレージの場所のイベント通知を構成して、外部テーブルメタデータに読み込む新しいデータまたは更新されたデータが利用可能になったときに、Snowflakeに通知する必要が あります。詳細については、ご使用のクラウドストレージサービスの手順をご参照ください。

  • 外部テーブルが作成されると、そのメタデータは REFRESH_ON_CREATE = FALSE でない限り、自動的に1回更新されます。

TRUE

Snowflakeを使用すると、外部テーブルメタデータの自動更新をトリガーできます。

FALSE

Snowflakeは、外部テーブルメタデータの自動更新のトリガーを有効にしません。 ALTER EXTERNAL TABLE ... REFRESH を使用して、外部テーブルのメタデータを手動で定期的に更新し、メタデータをステージパスにあるファイルの現在のリストと同期する必要があります。

デフォルト: TRUE

PATTERN = 'regex_pattern'

一致する外部ステージ上のファイル名やパスを指定する、一重引用符で囲まれた正規表現パターン文字列。

ちなみに

最高のパフォーマンスを得るには、多数のファイルをフィルターするパターンの適用を回避するようにします。

AWS_SNS_TOPIC = 'string'

Amazon Simple Notification Service(SNS)を使用してAmazon S3ステージの AUTO_REFRESH を設定する場合にのみ必要です。 S3バケットの SNS トピックのAmazonリソース名(ARN)を指定します。 CREATE EXTERNAL TABLE ステートメントは、指定された SNS トピックにAmazon Simple Queue Service(SQS)キューをサブスクライブします。 SNS トピックを介したイベント通知により、メタデータの更新がトリガーされます。詳細については、 Amazon S3に対する外部テーブルの自動更新 をご参照ください。

TABLE_FORMAT = DELTA

クラウドストレージの場所にあるDelta Lakeを参照している外部テーブルを識別します。Amazon S3、Google Cloud Storage、またはMicrosoft Azureクラウドストレージ上のDelta Lakeがサポートされています。

注釈

この プレビュー機能 は、すべてのアカウントで利用できます。

このパラメーターが設定されている場合、外部テーブルは、 [ WITH ] LOCATION の場所にあるDelta Lakeトランザクションログファイルをスキャンします。Deltaログファイルには、 _delta_log/00000000000000000000.json_delta_log/00000000000000000010.checkpoint.parquet などの名前が付いています。

外部テーブルのメタデータが更新されると、SnowflakeはDelta Lakeトランザクションログを解析し、現在のParquetファイルを判別します。バックグラウンドで、更新はファイルの追加と削除の操作を実行して、外部テーブルのメタデータの同期を維持します。

注釈

  • [ WITH ] LOCATION = で指定された外部ステージとオプションのパスには、 単一 のDelta Lakeテーブルのみのデータファイルとメタデータを含める必要があります。つまり、指定されたストレージの場所には __delta_log ディレクトリを1つだけ含めることができます。

  • クラウドストレージでの DDL 操作によってトリガーされるイベント通知の順序は保証されません。したがって、Delta Lakeファイルを参照する外部テーブルでは、自動的に更新する機能は使用できません。 REFRESH_ON_CREATEAUTO_REFRESH の両方を FALSE に設定する必要があります。

    ALTER EXTERNAL TABLE ... REFRESH ステートメントを定期的に実行して、追加または削除されたファイルを登録します。

  • FILE_FORMAT 値は、ファイルタイプとしてParquetを指定する必要があります。

  • 最適なパフォーマンスを得るために、外部テーブルのパーティション列を定義することをお勧めします。

  • Delta Lakeを参照する場合、次のパラメーターはサポートされていません。

    • AWS_SNS_TOPIC = 'string'

    • PATTERN = 'regex_pattern'

COPY GRANTS

CREATE OR REPLACE TABLE バリアントを使用して外部テーブルが再作成されるときに、元のテーブルのアクセス許可を保持することを指定します。このパラメーターは、既存のテーブルから新しいテーブルにすべての権限( OWNERSHIP 以外)をコピーします。デフォルトでは、 CREATE EXTERNAL TABLE コマンドを実行するロールが新しい外部テーブルを所有します。

注釈

許可をコピーする操作は、 CREATE EXTERNAL TABLE コマンドで(つまり、同じトランザクション内で)アトミックに発生します。

COMMENT = 'string_literal'

外部テーブルのコメントを指定する文字列(リテラル)。

デフォルト: 値なし

ROW ACCESS POLICY <policy_name> ON (VALUE)

テーブルに設定する 行アクセスポリシー を指定します。

行アクセスポリシーを外部テーブルに適用する場合は、 VALUE 列を指定します。

TAG ( tag_name = 'tag_value' [ , tag_name = 'tag_value' , ... ] )

タグ の名前とタグ文字列の値を指定します。

タグ値は常に文字列であり、タグ値の最大文字数は256です。

ステートメントにあるタグの指定の詳細については、 オブジェクトおよび列のタグクォータ をご参照ください。

分割パラメーター

これらのパラメーターを使用して、外部テーブルをパーティション分割します。

part_col_name col_type AS part_expr

外部テーブルにある、1つ以上のパーティション列を指定します。

パーティション列定義の形式は、パーティションが各パーティション列の式から自動的に計算されて追加されるか、手動で追加されるかによって異なります。

式から追加

パーティション列は、 METADATA$FILENAME 疑似列のパスおよび/またはファイル名情報を解析する式として評価する 必要があります 。パーティション列は、スキャンする必要のないデータファイルを削除することにより、クエリパフォーマンスを最適化します(外部テーブルのパーティション分割)。パーティションは、パーティション列の式のパスやファイル名に一致するすべてのデータファイルで構成されます。

part_col_name

パーティション列の識別子(名前)を指定する文字列。テーブル識別子のすべての要件は、列識別子にも適用されます。

col_type

列のデータ型を指定する文字列(定数)。データ型は、列の part_expr の結果と一致する必要があります。

part_expr

列の式を指定する文字列。式には METADATA$FILENAME 疑似列を含める必要があります。

外部テーブルは現在、パーティション式で次の関数のサブセットをサポートしています。

サポートされている関数のリスト:

手動で追加

必須: また、 PARTITION_TYPE パラメーターを USER_SPECIFIED に設定します。

パーティション列定義は、内部(非表示) METADATA$EXTERNAL_TABLE_PARTITION 列の列メタデータを解析する式です。基本的に、定義は列のデータ型のみを定義します。パーティション列定義の形式は次のとおりです。

part_col_name col_type AS ( PARSE_JSON (METADATA$EXTERNALTABLE_PARTITION):part_col_name::data_type )

たとえば、列 col1col2、および col3 に、それぞれvarchar、数字、およびタイムスタンプ(タイムゾーン)データが含まれているとします。

col1 varchar as (parse_json(metadata$external_table_partition):col1::varchar),
col2 number as (parse_json(metadata$external_table_partition):col2::number),
col3 timestamp_tz as (parse_json(metadata$external_table_partition):col3::timestamp_tz)
Copy

テーブルのパーティション列を定義したら、 PARTITION BY 句を使用してこれらの列を識別します。

注釈

ユーザー指定のパーティション列名の最大長は32文字です。

PARTITION_TYPE = USER_SPECIFIED

外部テーブルのパーティション型を ユーザー定義 として定義します。外部テーブルの所有者(つまり、外部テーブルに対する OWNERSHIP 権限を持つロール)は、ALTER EXTERNAL TABLE ... ADD PARTITION ステートメントを実行して、パーティションを外部メタデータに手動で追加する必要があります。

パーティション列の式の評価時に、パーティションが外部テーブルのメタデータに自動的に追加される場合は、このパラメーターを設定 しない でください。

[ PARTITION BY ( part_col_name [, part_col_name ... ] ) ]

外部テーブルについて評価するパーティション列を指定します。

使用状況

外部テーブルをクエリする場合、 WHERE 句に1つ以上のパーティション列を含めます。例:

... WHERE part_col_name = 'filter_value'

Snowflakeはパーティション列をフィルター処理して、スキャンするデータファイルのセットを制限します。これらのファイルのすべての行がスキャンされることに注意してください。 WHERE 句に非パーティション列が含まれる場合、それらのフィルターは、データファイルがフィルター処理された 、評価されます。

一般的な方法は、時間の増分に基づいてデータファイルをパーティション分割することです。または、データファイルが複数のソースからステージングされている場合は、データソース識別子と日付またはタイムスタンプで分割します。

クラウドプロバイダーのパラメーター(cloudProviderParams

Google Cloud Storage

INTEGRATION = integration_name

Google Pub/Subイベント通知を使用して、外部テーブルメタデータを自動的に更新するのに使用される通知統合の名前を指定します。通知統合は、Snowflakeとサードパーティのクラウドメッセージキューサービス間のインターフェイスを提供するSnowflakeオブジェクトです。

このパラメーターは、外部テーブルの自動更新操作を有効にするために必要です。自動更新機能の構成手順については、 Google Cloud Storageの外部テーブルの自動更新 をご参照ください。

Microsoft Azure

INTEGRATION = integration_name

Azure Event Grid通知を使用して外部テーブルメタデータを自動的に更新するのに使用される通知統合の名前を指定します。通知統合は、Snowflakeとサードパーティのクラウドメッセージキューサービス間のインターフェイスを提供するSnowflakeオブジェクトです。

このパラメーターは、外部テーブルの自動更新操作を有効にするために必要です。自動更新機能の構成手順については、 Azure Blobストレージに対する外部テーブルの自動更新 をご参照ください。

形式タイプオプション(formatTypeOptions

形式タイプオプションは、 テーブルへのデータのロードテーブルからのデータのアンロード に使用されます。

指定したファイル形式タイプ(FILE_FORMAT = ( TYPE = ... ))に応じて、次の形式固有のオプションを1つ以上含めることができます(空白、コンマ、または改行で区切られます)。

TYPE = CSV

COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE

クエリするデータファイルの現在の圧縮アルゴリズムを指定する文字列(定数)。Snowflakeはこのオプションを使用して、ファイル内の圧縮データをクエリ用に抽出できるように、 圧縮済み データファイルの圧縮方法を検出します。

サポートされる値

メモ

AUTO

現在は自動検出できないBrotliで圧縮されたファイルを除き、圧縮アルゴリズムは自動的に検出されます。Brotliで圧縮されたファイルをクエリする場合は、 AUTO ではなく BROTLI を明示的に使用してください。

GZIP

BZ2

BROTLI

Brotliで圧縮されたファイルをクエリするときには指定する必要があります。

ZSTD

Zstandard v0.8(およびそれ以上)がサポートされています。

DEFLATE

Deflate圧縮されたファイル(zlibヘッダー、 RFC1950を使用)。

RAW_DEFLATE

生Deflate圧縮ファイル(ヘッダーなし、 RFC1951)。

NONE

データファイルは圧縮されていません。

RECORD_DELIMITER = 'character' | NONE

入力ファイルの記録を区切る、1つ以上の文字。一般的なエスケープシーケンス、または次のシングルバイトまたはマルチバイト文字を受け入れます。

シングルバイト文字

8進値(\\ でプレフィックス)、または16進値(0x または \x でプレフィックス)を受け入れます。たとえば、アクサンシルコンフレックス(^)文字で区切られた記録の場合は、8進数(\\136)または16進数(0x5e)の値を指定します。

マルチバイト文字

16進値(\x でプレフィックス)。たとえば、セント(¢)文字で区切られた記録の場合は、16進数(\xC2\xA2)の値を指定します。

RECORD_DELIMITER または FIELD_DELIMITER の区切り文字は、他のファイル形式オプション(例: FIELD_DELIMITER = 'aa' RECORD_DELIMITER = 'aabb')向けとして区切り文字の部分文字列にすることはできません。

指定された区切り文字は、ランダムなバイトのシーケンスではなく、有効な UTF-8文字でなければなりません。区切り文字は最大20文字に制限されていることにも注意してください。

NONE の値も受け入れます。

デフォルト: 改行文字。「改行」は論理的であるため、 \r\n がWindowsプラットフォーム上のファイルの改行として理解されることに注意してください。

FIELD_DELIMITER = 'character' | NONE

入力ファイルのフィールドを区切る、1つ以上のシングルバイトまたはマルチバイト文字。一般的なエスケープシーケンス、または次のシングルバイトまたはマルチバイト文字を受け入れます。

シングルバイト文字

8進値(\\ でプレフィックス)、または16進値(0x または \x でプレフィックス)を受け入れます。たとえば、アクサンシルコンフレックス(^)文字で区切られた記録の場合は、8進数(\\136)または16進数(0x5e)の値を指定します。

マルチバイト文字

16進値(\x でプレフィックス)。たとえば、セント(¢)文字で区切られた記録の場合は、16進数(\xC2\xA2)の値を指定します。

RECORD_DELIMITER または FIELD_DELIMITER の区切り文字は、他のファイル形式オプション(例: FIELD_DELIMITER = 'aa' RECORD_DELIMITER = 'aabb')向けとして区切り文字の部分文字列にすることはできません。

注釈

ASCII 以外の文字の場合は、16進数のバイトシーケンス値を使用して決定論的動作を得る必要があります。

指定された区切り文字は、ランダムなバイトのシーケンスではなく、有効な UTF-8文字でなければなりません。区切り文字は最大20文字に制限されていることにも注意してください。

NONE の値も受け入れます。

デフォルト: コンマ(,

SKIP_HEADER = integer

スキップするファイルの先頭の行数。

SKIP_HEADER は RECORD_DELIMITER または FIELD_DELIMITER の値を使用してヘッダー行を決定しないことに注意してください。むしろ、指定された数の CRLF (キャリッジリターン、ラインフィード)で区切られたファイル内の行を単にスキップします。そして、 RECORD_DELIMITER および FIELD_DELIMITER がクエリするデータの行を決定するために使用されます。

デフォルト: 0

SKIP_BLANK_LINES = TRUE | FALSE
使用

データクエリのみ

定義

データファイルで検出された空白行のスキップを指定するブール値。それ以外の場合、空白行は記録終了エラーを生成します(デフォルトの動作)。

デフォルト: FALSE

ESCAPE_UNENCLOSED_FIELD = 'character' | NONE

囲まれていないフィールド値専用のエスケープ文字として使用されるシングルバイト文字の文字列。エスケープ文字は、文字シーケンス内の後続の文字に対して代替解釈を呼び出します。ESCAPE 文字を使用して、データ内の FIELD_DELIMITER または RECORD_DELIMITER 文字のインスタンスをリテラルとして解釈できます。エスケープ文字は、データ内のそれ自体のインスタンスをエスケープするためにも使用できます。

一般的なエスケープシーケンス、8進数値、または16進数値を受け入れます。

囲まれていないフィールド専用のエスケープ文字を指定します。

注釈

  • デフォルト値は \\ です。データファイルの行がバックスラッシュ(\)文字で終わる場合、この文字は RECORD_DELIMITER ファイル形式オプションに指定された改行文字または復帰文字をエスケープします。その結果、この行と次の行は単一のデータ行として扱われます。この問題を回避するには、この値を NONE に設定します。

  • このファイル形式オプションは、シングルバイト文字のみをサポートします。UTF-8文字エンコードは、上位の ASCII 文字をマルチバイト文字として表すことに注意してください。データファイルが UTF-8文字セットでエンコードされている場合は、オプション値として上位の ASCII 文字を指定することはできません。

    さらに、上位の ASCII 文字を指定する場合は、データファイルの文字エンコードとして ENCODING = 'string' ファイル形式オプションを設定して、文字が正しく解釈されるようにすることをお勧めします。

デフォルト:バックスラッシュ(\\

TRIM_SPACE = TRUE | FALSE

フィールドから空白を削除するかどうかを指定するブール値。

たとえば、外部データベースソフトウェアがフィールドを引用符で囲み、先頭にスペースを挿入する場合、Snowflakeはフィールドの先頭として開始引用文字ではなく先頭スペースを読み取ります(つまり、引用符はフィールドデータの文字列の一部として解釈されます)。データのクエリ時に不要なスペースを削除するには、このオプションを TRUE に設定します。

別の例として、先頭または後続のスペースが文字列を囲む引用符の前後にある場合、このオプションを使用して周囲のスペースを削除し、 FIELD_OPTIONALLY_ENCLOSED_BY オプションを使用して引用文字を削除できます。引用符 のスペースは保持されることに注意してください。たとえば、 FIELD_DELIMITER = '|'FIELD_OPTIONALLY_ENCLOSED_BY = '"' を想定した場合、

|"Hello world"|    /* returned as */  >Hello world<
|" Hello world "|  /* returned as */  > Hello world <
| "Hello world" |  /* returned as */  >Hello world<
Copy

この例のブラケットは返されません。返された文字列の開始と終了を区別するために使用しています。

デフォルト: FALSE

FIELD_OPTIONALLY_ENCLOSED_BY = 'character' | NONE

文字列を囲むのに使用される文字。値は、 NONE、一重引用符(')、または二重引用符(")のいずれかです。一重引用符を使用するには、8進数または16進数表現(0x27)または二重引用符で囲まれたエスケープ('')を使用します。

フィールドにこの文字が含まれる場合、同じ文字を使用してエスケープします。例えば、値が二重引用符であり、フィールドに文字列 A "B" C が含まれる場合、二重引用符を次のようにエスケープします。

A ""B"" C

デフォルト: NONE

NULL_IF = ( 'string1' [ , 'string2' , ... ] )

SQL NULL との間の変換に使用される文字列。

データのクエリを実行すると、Snowflakeは返されたデータのこれらの値を SQL NULL に置き換えます。複数の文字列を指定するには、文字列のリストを括弧で囲み、各値をコンマで区切ります。

Snowflakeは、データ型に関係なく、値のすべてのインスタンスを NULL に変換することに注意してください。たとえば、 2 が値として指定されている場合は、文字列または数値としての 2 のインスタンスすべてが変換されます。

例:

NULL_IF = ('\\N', 'NULL', 'NUL', '')

このオプションには空の文字列を含めることができます。

デフォルト: \\N (つまり、 NULL、 ESCAPE_UNENCLOSED_FIELD 値が \\ であると想定)

EMPTY_FIELD_AS_NULL = TRUE | FALSE

2つの連続する区切り文字(例: ,,)で表される入力ファイルの空のフィールドに、 SQL NULL を返すかどうかを指定します。

FALSE に設定すると、Snowflakeは空のフィールドを対応する列型にキャストしようとします。型 STRING の列には空の文字列が返されます。他の列型の場合、クエリはエラーを返します。

デフォルト: TRUE

ENCODING = 'string'

データのクエリ時にソースデータの文字セットを指定する文字列(定数)。

文字 セット

ENCODING 値

サポートされている言語

メモ

Big5

BIG5

中国語(繁体字)

EUC-JP

EUCJP

日本語

EUC-KR

EUCKR

韓国語

GB18030

GB18030

中国語

IBM420

IBM420

アラビア語

IBM424

IBM424

ヘブライ語

IBM949

IBM949

韓国語

ISO-2022-CN

ISO2022CN

中国語(簡体字)

ISO-2022-JP

ISO2022JP

日本語

ISO-2022-KR

ISO2022KR

韓国語

ISO-8859-1

ISO88591

デンマーク語、オランダ語、英語、フランス語、ドイツ語、イタリア語、ノルウェー語、ポルトガル語、スウェーデン語

ISO-8859-2

ISO88592

チェコ語、ハンガリー語、ポーランド語、ルーマニア語

ISO-8859-5

ISO88595

ロシア語

ISO-8859-6

ISO88596

アラビア語

ISO-8859-7

ISO88597

ギリシャ語

ISO-8859-8

ISO88598

ヘブライ語

ISO-8859-9

ISO88599

トルコ語

ISO-8859-15

ISO885915

デンマーク語、オランダ語、英語、フランス語、ドイツ語、イタリア語、ノルウェー語、ポルトガル語、スウェーデン語

ユーロ通貨記号を含む8文字を除いて、 ISO-8859-1と同じです。

KOI8-R

KOI8R

ロシア語

Shift_JIS

SHIFTJIS

日本語

UTF-8

UTF8

すべての言語

区切りファイル(CSV、 TSVなど)からデータをロードする場合、 UTF-8がデフォルトです。 . . サポートされている他のすべてのファイル形式(JSON、Avroなど)からデータをロードおよびアンロードする場合、サポートされる文字セットは UTF-8のみです。

UTF-16

UTF16

すべての言語

UTF-16BE

UTF16BE

すべての言語

UTF-16LE

UTF16LE

すべての言語

UTF-32

UTF32

すべての言語

UTF-32BE

UTF32BE

すべての言語

UTF-32LE

UTF32LE

すべての言語

windows-949

WINDOWS949

韓国語

windows-1250

WINDOWS1250

チェコ語、ハンガリー語、ポーランド語、ルーマニア語

windows-1251

WINDOWS1251

ロシア語

windows-1252

WINDOWS1252

デンマーク語、オランダ語、英語、フランス語、ドイツ語、イタリア語、ノルウェー語、ポルトガル語、スウェーデン語

windows-1253

WINDOWS1253

ギリシャ語

windows-1254

WINDOWS1254

トルコ語

windows-1255

WINDOWS1255

ヘブライ語

windows-1256

WINDOWS1256

アラビア語

デフォルト: UTF8

注釈

Snowflakeは、すべてのデータを UTF-8文字セットで内部に保存します。データは UTF-8に変換されます。

TYPE = JSON

COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE

返されるデータファイルの現在の圧縮アルゴリズムを指定する文字列(定数)。Snowflakeはこのオプションを使用して、ファイル内の圧縮データをクエリ用に抽出できるように、 圧縮済み データファイルの圧縮方法を検出します。

サポートされる値

メモ

AUTO

現在は自動検出できないBrotliで圧縮されたファイルを除き、圧縮アルゴリズムは自動的に検出されます。Brotliで圧縮されたファイルをクエリする場合は、 AUTO ではなく BROTLI を明示的に使用してください。

GZIP

BZ2

BROTLI

ZSTD

DEFLATE

Deflate圧縮されたファイル(zlibヘッダー、 RFC1950を使用)。

RAW_DEFLATE

生Deflate圧縮ファイル(ヘッダーなし、 RFC1951)。

NONE

ファイルが圧縮されていないことを示します。

デフォルト: AUTO

ALLOW_DUPLICATE = TRUE | FALSE

オブジェクトフィールド名の重複を許可することを指定するブール値(最後のオブジェクトフィールド名のみが保持されます)。

デフォルト: FALSE

STRIP_OUTER_ARRAY = TRUE | FALSE

JSON パーサーに、外側の括弧(つまり [ ])を削除するように指示するブール値。

デフォルト: FALSE

STRIP_NULL_VALUES = TRUE | FALSE

null 値を含むオブジェクトフィールドまたは配列要素を削除するように JSON パーサーに指示するブール値。たとえば、 TRUE に設定されている場合:

[null]

[]

[null,null,3]

[,,3]

{"a":null,"b":null,"c":123}

{"c":123}

{"a":[1,null,2],"b":{"x":null,"y":88}}

{"a":[1,,2],"b":{"y":88}}

デフォルト: FALSE

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

無効な UTF -8文字をUnicode置換文字()で置き換えるかどうかを指定するブール値。このオプションは、1対1の文字置換を実行します。

TRUE に設定すると、Snowflakeは無効な UTF-8文字をUnicode置換文字に置き換えます。

FALSE に設定すると、無効な UTF-8文字エンコードが検出されたときにロード操作でエラーが生成されます。

デフォルト: FALSE

TYPE = AVRO

COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE

クエリするデータファイルの現在の圧縮アルゴリズムを指定する文字列(定数)。Snowflakeはこのオプションを使用して、ファイル内の圧縮データをクエリ用に抽出できるように、 圧縮済み データファイルの圧縮方法を検出します。

サポートされる値

メモ

AUTO

現在は自動検出できないBrotliで圧縮されたファイルを除き、圧縮アルゴリズムは自動的に検出されます。Brotliで圧縮されたファイルをクエリする場合は、 AUTO ではなく BROTLI を明示的に使用してください。

GZIP

BZ2

BROTLI

ZSTD

DEFLATE

Deflate圧縮されたファイル(zlibヘッダー、 RFC1950を使用)。

RAW_DEFLATE

生Deflate圧縮ファイル(ヘッダーなし、 RFC1951)。

NONE

クエリするデータファイルは圧縮されていません。

デフォルト: AUTO

注釈

ファイル圧縮とコーデック圧縮の両方が決定されるため、デフォルトの AUTO オプションを使用することをお勧めします。圧縮オプションの指定は、ブロック(コーデック)の圧縮ではなく、ファイルの圧縮を意味します。

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

無効な UTF -8文字をUnicode置換文字()で置き換えるかどうかを指定するブール値。このオプションは、1対1の文字置換を実行します。

TRUE に設定すると、Snowflakeは無効な UTF-8文字をUnicode置換文字に置き換えます。

FALSE に設定すると、無効な UTF-8文字エンコードが検出されたときにロード操作でエラーが生成されます。

デフォルト: FALSE

TYPE = ORC

TRIM_SPACE = TRUE | FALSE

文字列から先頭と末尾の空白を削除するかどうかを指定するブール値。

たとえば、外部データベースソフトウェアがフィールドを引用符で囲み、先頭にスペースを挿入する場合、Snowflakeはフィールドの先頭として開始引用文字ではなく先頭スペースを読み取ります(つまり、引用符はフィールドデータの文字列の一部として解釈されます)。不要なスペースを削除するには、このオプションを TRUE に設定します。

このファイル形式オプションは、次のアクションにのみ適用されます。

  • ステージされた ORC データファイル内のオブジェクト値のクエリ。

  • MATCH_BY_COLUMN_NAME コピーオプションを使用して、別の列の ORC データをクエリします。

  • COPY ステートメント(つまり、 COPY 変換)でクエリを指定して、 ORC データを個別の列でクエリします。

デフォルト: FALSE

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

無効な UTF -8文字をUnicode置換文字()で置き換えるかどうかを指定するブール値。このオプションは、1対1の文字置換を実行します。

TRUE に設定すると、Snowflakeは無効な UTF-8文字をUnicode置換文字に置き換えます。

FALSE に設定すると、無効な UTF-8文字エンコードが検出されたときにロード操作でエラーが生成されます。

デフォルト: FALSE

NULL_IF = ( 'string1' [ , 'string2' , ... ] )

SQL NULL との間の変換に使用される文字列。Snowflakeはデータソースのこれらの文字列を SQL NULL に置き換えます。複数の文字列を指定するには、文字列のリストを括弧で囲み、各値をコンマで区切ります。

Snowflakeは、データ型に関係なく、値のすべてのインスタンスを NULL に変換することに注意してください。たとえば、 2 が値として指定されている場合は、文字列または数値としての 2 のインスタンスすべてが変換されます。

例:

NULL_IF = ('\\N', 'NULL', 'NUL', '')

このオプションには空の文字列を含めることができます。

このファイル形式オプションは、ステージングされた ORC データファイル内のオブジェクト値をクエリするときに適用されます。

デフォルト: \\N (つまり、 NULL、 ESCAPE_UNENCLOSED_FIELD 値が \\ であると想定)

TYPE = PARQUET

COMPRESSION = AUTO | SNAPPY | NONE

Parquetファイルの列にある現在の圧縮アルゴリズムを指定する文字列(定数)。

サポートされる値

メモ

AUTO

圧縮アルゴリズムが自動的に検出されました。対応する圧縮アルゴリズム:Brotli、gzip、Lempel-Ziv-Oberhumer(LZO)、LZ4、Snappy、またはZstandard v0.8(およびそれ以上)。

SNAPPY

NONE

データファイルは圧縮されていません。

デフォルト: AUTO

BINARY_AS_TEXT = TRUE | FALSE

論理データ型が定義されていない列を UTF-8テキストとして解釈するかどうかを指定するブール値。 FALSE に設定すると、Snowflakeはこれらの列をバイナリデータとして解釈します。

デフォルト: TRUE

注釈

Snowflakeは、潜在的な変換の問題を回避するために、 BINARY_AS_TEXT を FALSE に設定することをお勧めします。

REPLACE_INVALID_CHARACTERS = TRUE | FALSE

無効な UTF -8文字をUnicode置換文字()で置き換えるかどうかを指定するブール値。このオプションは、1対1の文字置換を実行します。

TRUE に設定すると、Snowflakeは無効な UTF-8文字をUnicode置換文字に置き換えます。

FALSE に設定すると、無効な UTF-8文字エンコードが検出されたときにロード操作でエラーが生成されます。

デフォルト: FALSE

アクセス制御の要件

この SQL コマンドの実行に使用される ロール には、少なくとも次の 権限 が必要です。

権限

オブジェクト

メモ

CREATE EXTERNAL TABLE

スキーマ

CREATE STAGE

スキーマ

新しいステージを作成する場合は必須です。

USAGE

ステージ

既存のステージを参照する場合は必須です。

USAGE

ファイル形式

スキーマ内の任意のオブジェクトを操作するには、親データベースとスキーマに対する USAGE 権限も必要であることに注意してください。

指定された権限のセットを使用してカスタムロールを作成する手順については、 カスタムロールの作成 をご参照ください。

セキュリティ保護可能なオブジェクト に対して SQL アクションを実行するためのロールと権限付与に関する一般的な情報については、 アクセス制御の概要 をご参照ください。

使用上の注意

  • 外部テーブルは、外部(つまり、S3、Azure、または GCS)ステージのみをサポートします。内部(つまり、Snowflake)ステージはサポートされていません。

    取得する前に復元が必要な、アーカイブクラウドストレージクラスに保持されているデータにアクセスすることはできません。これらのアーカイブストレージクラスには、たとえば、Amazon S3 Glacier Flexible RetrievalまたはGlacier Deep Archiveストレージクラス、またはMicrosoft Azure Archive Storageが含まれます。

  • Snowflakeは、外部テーブルに整合性の制約を適用しません。特に、通常のテーブルとは異なり、SnowflakeはNOT NULL制約を適用しません。

  • 外部テーブルには、次のメタデータ列が含まれます。

    • METADATA$FILENAME :外部テーブルに含まれる各ステージングデータファイルの名前。そのステージにあるデータファイルへのパスが含まれます。

    • METADATA$FILE_ROW_NUMBER: ステージングされたデータファイルの各記録の行番号。

  • 次は外部テーブルではサポートされていません。

    • クラスタリングキー

    • クローニング

    • XML 形式のデータ

  • Time Travelは外部テーブルではサポートされていません。

  • ポリシーで外部テーブルを使用する方法の詳細については、こちらをご参照ください。

  • OR REPLACE の使用は、既存の外部テーブルで DROP EXTERNAL TABLE を使用してから、同じ名前で新しい外部テーブルを作成することと同じです。

    CREATE OR REPLACE <オブジェクト> ステートメントはアトミックです。つまり、オブジェクトが置き換えられると、単一のトランザクションで、古いオブジェクトが削除されて新しいオブジェクトが作成されます。

    これは、 CREATE OR REPLACE EXTERNAL TABLE 操作と同時に実行されるクエリが、古い外部テーブルバージョンまたは新しい外部テーブルバージョンのいずれかを使用することを意味します。

  • メタデータについて。

    注意

    Snowflakeサービスを使用する場合、お客様は、個人データ(ユーザーオブジェクト向け以外)、機密データ、輸出管理データ、またはその他の規制されたデータがメタデータとして入力されていないことを確認する必要があります。詳細については、 Snowflakeのメタデータフィールド をご参照ください。

  • 行アクセスポリシーが外部テーブルに追加された外部テーブルを作成する場合は、 POLICY_CONTEXT 関数を使用して、行アクセスポリシーで保護された外部テーブルに対するクエリをシミュレートします。

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

パーティション列式から自動的に追加されたパーティション

パーティション列定義の式から計算されたパーティションを使用して外部テーブルを作成します。

次の例では、データファイルは次の構造でクラウドストレージに編成されています。 logs/YYYY/MM/DD/HH24。例:

  • logs/2018/08/05/0524/

  • logs/2018/08/27/1408/

  1. データファイルが保存されている保存場所に s1 という名前の外部ステージを作成します。詳細については、 CREATE STAGE をご参照ください。

    ステージ定義にはパス /files/logs/ が含まれます。

    Amazon S3

    CREATE STAGE s1
      URL='s3://mybucket/files/logs/'
      ...
      ;
    
    Copy

    Google Cloud Storage

    CREATE STAGE s1
      URL='gcs://mybucket/files/logs/'
      ...
      ;
    
    Copy

    Microsoft Azure

    CREATE STAGE s1
      URL='azure://mycontainer/files/logs/'
      ...
      ;
    
    Copy
  2. ステージングされたデータの METADATA$FILENAME 疑似列をクエリします。結果を使用して、パーティション列を作成します。

    SELECT metadata$filename FROM @s1/;
    
    +----------------------------------------+
    | METADATA$FILENAME                      |
    |----------------------------------------|
    | files/logs/2018/08/05/0524/log.parquet |
    | files/logs/2018/08/27/1408/log.parquet |
    +----------------------------------------+
    
    Copy
  3. パーティション化された外部テーブルを作成します。

    パーティション列 date_part は、 METADATA$FILENAME 疑似列の YYYY/MM/DDTO_DATE , DATE を使用して日付としてキャストします。SQL コマンドは、ファイル形式タイプとしてParquetも指定します。

    Amazon S3およびMicrosoft Azureクラウドストレージの外部テーブルには、それぞれのクラウドメッセージングサービスからのイベント通知によってトリガーされたときに、メタデータを自動的に更新するために必要なパラメーターが含まれています。

    Amazon S3

    CREATE EXTERNAL TABLE et1(
     date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
       || '/' || SPLIT_PART(metadata$filename, '/', 4)
       || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
     timestamp bigint AS (value:timestamp::bigint),
     col2 varchar AS (value:col2::varchar))
     PARTITION BY (date_part)
     LOCATION=@s1/logs/
     AUTO_REFRESH = true
     FILE_FORMAT = (TYPE = PARQUET)
     AWS_SNS_TOPIC = 'arn:aws:sns:us-west-2:001234567890:s3_mybucket';
    
    Copy

    Google Cloud Storage

    CREATE EXTERNAL TABLE et1(
      date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
        || '/' || SPLIT_PART(metadata$filename, '/', 4)
        || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
      timestamp bigint AS (value:timestamp::bigint),
      col2 varchar AS (value:col2::varchar))
      PARTITION BY (date_part)
      LOCATION=@s1/logs/
      AUTO_REFRESH = true
      FILE_FORMAT = (TYPE = PARQUET);
    
    Copy

    Microsoft Azure

    CREATE EXTERNAL TABLE et1(
      date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
        || '/' || SPLIT_PART(metadata$filename, '/', 4)
        || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
      timestamp bigint AS (value:timestamp::bigint),
      col2 varchar AS (value:col2::varchar))
      PARTITION BY (date_part)
      INTEGRATION = 'MY_INT'
      LOCATION=@s1/logs/
      AUTO_REFRESH = true
      FILE_FORMAT = (TYPE = PARQUET);
    
    Copy
  4. 外部テーブルのメタデータを更新します。

    ALTER EXTERNAL TABLE et1 REFRESH;
    
    Copy

外部テーブルをクエリする場合、 WHERE 句を使用してパーティション列でデータをフィルターします。Snowflakeは、フィルター条件に一致する指定されたパーティション内のファイルのみをスキャンします。

SELECT timestamp, col2 FROM et1 WHERE date_part = to_date('08/05/2018');
Copy

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

ユーザー定義のパーティションを使用して、外部テーブルを作成します(つまり、パーティションは、外部テーブルの所有者によって手動で追加されます)。

  1. データファイルが保存されているストレージの場所に s2 という名前の外部ステージを作成します。

    ステージ定義にはパス /files/logs/ が含まれます。

    Amazon S3

    CREATE STAGE s2
      URL='s3://mybucket/files/logs/'
      ...
      ;
    
    Copy

    Google Cloud Storage

    CREATE STAGE s2
      URL='gcs://mybucket/files/logs/'
      ...
      ;
    
    Copy

    Microsoft Azure

    CREATE STAGE s2
      URL='azure://mycontainer/files/logs/'
      ...
      ;
    
    Copy
  2. パーティション化された外部テーブルを作成します。外部テーブルには、データ型が異なる3つのパーティション列が含まれています。

    パーティション式の列名では、大文字と小文字が区別されることに注意してください。次のルールが適用されます。

    • 列名が二重引用符で囲まれている場合を除き、パーティション列名は大文字にする 必要があります。または、 SQL 式で大文字と小文字を区別する : 文字の代わりに GET_IGNORE_CASE を使用します。

    • 列名が二重引用符で囲まれている場合(例:「Column1」)は、パーティションの列名も二重引用符で囲み、列名と完全に一致させる必要があります。

    外部テーブルのメタデータは更新されないため、3つのクラウドストレージサービス(Amazon S3、Google Cloud Storage、およびMicrosoft Azure)の各構文は同じです。

    create external table et2(
      col1 date as (parse_json(metadata$external_table_partition):COL1::date),
      col2 varchar as (parse_json(metadata$external_table_partition):COL2::varchar),
      col3 number as (parse_json(metadata$external_table_partition):COL3::number))
      partition by (col1,col2,col3)
      location=@s2/logs/
      partition_type = user_specified
      file_format = (type = parquet);
    
    Copy
  3. パーティション列にパーティションを追加します。

    ALTER EXTERNAL TABLE et2 ADD PARTITION(col1='2022-01-24', col2='a', col3='12') LOCATION '2022/01';
    
    Copy

    Snowflakeは、外部テーブルのメタデータにパーティションを追加します。この操作では、指定された場所にある新しいデータファイルもメタデータに追加されます。

    +---------------------------------------+----------------+-------------------------------+
    |                       file            |     status     |          description          |
    +---------------------------------------+----------------+-------------------------------+
    | mycontainer/files/logs/2022/01/24.csv | REGISTERED_NEW | File registered successfully. |
    | mycontainer/files/logs/2022/01/25.csv | REGISTERED_NEW | File registered successfully. |
    +---------------------------------------+----------------+-------------------------------+
    
    Copy

外部テーブルをクエリする場合、 WHERE 句を使用してパーティション列でデータをフィルターします。この例では、ステージングされたデータファイルに保存されている順序で記録を返します。

SELECT col1, col2, col3 FROM et1 WHERE col1 = TO_DATE('2022-01-24') AND col2 = 'a' ORDER BY METADATA$FILE_ROW_NUMBER;
Copy

Delta Lakeにあるファイルを参照する外部テーブル

mystage 外部ステージと daily パスで、Parquet形式のDelta Lakeファイルを参照する、 ext_twitter_feed という名前のパーティション化された外部テーブルを作成します。

パーティション列 date_part は、 TO_DATE , DATE を使用して、 METADATA$FILENAME 疑似列の YYYY/MM/DD を日付としてキャストします。

CREATE EXTERNAL TABLE ext_twitter_feed(
 date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
   || '/' || SPLIT_PART(metadata$filename, '/', 4)
   || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
 timestamp bigint AS (value:timestamp::bigint),
 col2 varchar AS (value:col2::varchar))
 PARTITION BY (date_part)
 LOCATION=@mystage/daily/
 REFRESH_ON_CREATE =  FALSE
 AUTO_REFRESH = FALSE
 FILE_FORMAT = (TYPE = PARQUET)
 TABLE_FORMAT = DELTA;
Copy

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

パーティション列式から自動的に追加されたパーティション の例で作成された外部テーブルの列にあるサブクエリに基づいて、マテリアライズドビューを作成します。

CREATE MATERIALIZED VIEW et1_mv
  AS
  SELECT col2 FROM et1;
Copy

この SQL コマンドの一般的な構文、使用上の注意、およびその他の例については、 CREATE MATERIALIZED VIEW をご参照ください。

検出された列定義を使用して作成された外部テーブル

Avro、Parquet、または ORC データを含むステージングされたファイルのセットから列定義が派生する外部テーブルを作成します。

ステートメントで参照されている mystage ステージと my_parquet_format ファイル形式がすでに存在している必要があることに注意してください。ファイルのセットは、ステージ定義で参照されるクラウドストレージの場所に既にステージングされている必要があります。

この例は、 INFER_SCHEMA トピックの例に基づいて構築されています。

CREATE EXTERNAL TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(
      INFER_SCHEMA(
        LOCATION=>'@mystage',
        FILE_FORMAT=>'my_parquet_format'
      )
    )
  )
  LOCATION=@mystage
  FILE_FORMAT=my_parquet_format
  AUTO_REFRESH=false;
Copy

注釈

ARRAY_AGG(OBJECT_CONSTRUCT())* を使用すると、返される結果が 16MB よりも大きいとエラーになる場合があります。大きな結果セットの場合は * の使用を避け、クエリにはクエリに必要な列、 COLUMN NAMETYPENULLABLE のみを使用することをお勧めします。 WITHIN GROUP (ORDER BY order_id) を使用する場合は、オプションの列 ORDER_ID を含めることができます。