カテゴリ:

テーブル、ビュー、シーケンス DDL

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>' ]
  [ TABLE_FORMAT = DELTA ]
  [ COPY GRANTS ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ COMMENT = '<string_literal>' ]

-- 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 ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ COMMENT = '<string_literal>' ]

条件:

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

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

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

cloudProviderParams (for Microsoft Azure) ::=
  [ INTEGRATION = '<integration_name>' ]
externalStage ::=
  @[<namespace>.]<ext_stage_name>[/<path>]
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
-- If FILE_FORMAT = ( TYPE = JSON ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
-- If FILE_FORMAT = ( TYPE = AVRO ... )
     COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
-- If FILE_FORMAT = ( TYPE = ORC ... )
     TRIM_SPACE = TRUE | FALSE
     NULL_IF = ( '<string>' [ , '<string>' ... ]
-- If FILE_FORMAT = ( TYPE = PARQUET ... )
     COMPRESSION = AUTO | SNAPPY | NONE

必須パラメーター

テーブル名

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

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

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

[ WITH ] LOCATION =

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

@[名前空間.]外部ステージ名[/パス]

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

条件:

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

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

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

FILE_FORMAT = ( FORMAT_NAME = 'ファイル形式名' ) または . FILE_FORMAT = ( TYPE = CSV | JSON | AVRO | ORC | PARQUET [ ... ] )

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

FORMAT_NAME = ファイル形式名

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

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

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

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

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

デフォルト: TYPE = CSV

重要

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

注釈

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

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

列名

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

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

列タイプ

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

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

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

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

VALUE

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

CSV

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

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

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

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

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

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

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

mycol varchar as (value:"b"."c"::varchar)
Apache Parquet

半構造化データ表記を使用して要素名を参照する、 または 特定の列に対するParquetフィールド ID を参照します。

たとえば、フィールド ID を使用して、ステージングされたParquetファイルの2番目の列を参照する mycol という名前の VARCHAR 列を追加します。

mycol VARCHAR as (value:"2"::VARCHAR)
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が外部テーブルメタデータの自動更新のトリガーを有効にするかどうかを指定します。

注釈

TRUE

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

FALSE

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

デフォルト: TRUE

PATTERN = '正規表現パターン'

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

ちなみに

最高のパフォーマンスを得るには、多数のファイルをフィルターするパターンを適用しないようにしてください。

AWS_SNS_TOPIC = '文字列'

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ファイルを判別します。バックグラウンドで、更新はファイルの追加と削除の操作を実行して、外部テーブルのメタデータの同期を維持します。

注釈

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

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

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

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

COPY GRANTS

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

注意:

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

ROW ACCESS POLICY <policy_name> ON (VALUE)

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

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

TAG ( タグ名 = 'タグ値' [ , タグ名 = 'タグ値' , ... ] )

タグ 名(つまり、キー)とタグ値を指定します。

タグ値は常に文字列であり、タグ値の最大文字数は256です。オブジェクトに設定できる 一意の タグキーの最大数は20です。

注釈

テーブルまたはビューとその列の場合、設定できる一意のタグキーの総数は20です。

たとえば、テーブル内にある単一列の1列に10個の一意のタグキーが設定されている場合、Snowflakeでは、その列、テーブル内にある他の列、テーブル自体、またはテーブルとその列をいくつか組み合わせたものに10個の一意のタグキーを追加で設定できます。一意のタグキーが20の制限に達すると、テーブルまたはその列に追加のタグキーを設定できなくなります。

COMMENT = '文字列リテラル'

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

デフォルト:値なし

パーティション分割パラメーター

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

パーティション列名 列タイプ AS パーティション式

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

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

式から追加

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

パーティション式名

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

列タイプ

列のデータ型を指定する文字列(定数)。データ型は、列の パーティション式 の結果と一致する必要があります。

パーティション式

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

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

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

手動で追加

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

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

パーティション列名 パーティション型 AS ( PARSE_JSON (METADATA$EXTERNALTABLE_PARTITION:パーティション列名::データ型 )

たとえば、列 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)

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

PARTITION_TYPE = USER_SPECIFIED

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

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

[ PARTITION BY ( パーティション列名 [, パーティション列名 ... ] ) ]

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

使用法

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

... WHERE パーティション列名 = 'フィルター値'

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

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

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

Google Cloud Storage

INTEGRATION = 統合名

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

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

Microsoft Azure

INTEGRATION = 統合名

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 = '文字' | NONE

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

シングルバイト文字

8進値(\\ でプレフィックス)、または16進値(0x または \x でプレフィックス)を受け入れます。たとえば、アクサンシルコンフレックス(^)文字で区切られた記録の場合は、8進数(\\5e)または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 = '文字' | NONE

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

シングルバイト文字

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

マルチバイト文字

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

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

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

NONE の値も受け入れます。

デフォルト: コンマ(,

SKIP_HEADER = 整数

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

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

デフォルト: 0

SKIP_BLANK_LINES = TRUE | FALSE
使用

データのロードのみ

定義

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

デフォルト: FALSE

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

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

TYPE = ORC

TRIM_SPACE = TRUE | FALSE

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

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

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

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

  • MATCH_BY_COLUMN_NAME コピーオプションを使用して、 ORC データを個別の列にロードします。

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

デフォルト: FALSE

NULL_IF = ( '文字列1' [ , '文字列2' , ... ] )

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

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

例:

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

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

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

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

  • MATCH_BY_COLUMN_NAME コピーオプションを使用して、 ORC データを個別の列にロードします。

  • COPY ステートメント(つまり、 COPY 変換)でクエリを指定して、 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

アクセス制御の要件

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

権限

オブジェクト

メモ

CREATE EXTERNAL TABLE

スキーマ

CREATE STAGE

スキーマ

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

USAGE

ステージ

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

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

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

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

使用上の注意

  • 外部テーブルは、外部(つまり、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 EXTERNAL TABLE 操作と同時に実行されるクエリが、古い外部テーブルバージョンまたは新しい外部テーブルバージョンのいずれかを使用することを意味します。

  • メタデータについて。

    注意

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

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

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

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

次の例では、データファイルは次の構造でクラウドストレージに編成されています。 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/'
      ...
      ;
    

    Google Cloud Storage

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

    Microsoft Azure

    CREATE STAGE s1
      URL='azure://mycontainer/files/logs/'
      ...
      ;
    
  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 |
    +----------------------------------------+
    
  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';
    

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

    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);
    
  4. 外部テーブルのメタデータを更新します。

    ALTER EXTERNAL TABLE et1 REFRESH;
    

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

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

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

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

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

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

    Amazon S3

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

    Google Cloud Storage

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

    Microsoft Azure

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

    外部テーブルのメタデータは更新されないため、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);
    
  3. パーティション列にパーティションを追加します。

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

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

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

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

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

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

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

Amazon S3

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;

Google Cloud Storage

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)
  INTEGRATION = 'MY_INT'
  LOCATION=@mystage/daily/
  REFRESH_ON_CREATE =  FALSE
  AUTO_REFRESH = FALSE
  FILE_FORMAT = (TYPE = PARQUET)
  TABLE_FORMAT = DELTA;

Microsoft Azure

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)
  INTEGRATION = 'MY_INT'
  LOCATION=@mystage/daily/
  REFRESH_ON_CREATE =  FALSE
  AUTO_REFRESH = FALSE
  FILE_FORMAT = (TYPE = PARQUET)
  TABLE_FORMAT = DELTA;

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

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

CREATE MATERIALIZED VIEW et1_mv
  AS
  SELECT col2 FROM et1;

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