CREATE EXTERNAL TABLE¶

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 をご参照ください。

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

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

part_col_name col_type AS part_expr

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

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

式から追加:

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

part_col_name	パーティション列の識別子（名前）を指定する文字列。テーブル識別子のすべての要件は、列識別子にも適用されます。
col_type	列のデータ型を指定する文字列（定数）。データ型は、列の part_expr の結果と一致する必要があります。
part_expr	列の式を指定する文字列。式には METADATA$FILENAME 疑似列を含める必要があります。

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

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

• =、 <>、 >、 >=、 <、 <=

• ||

• +、 -

• - （符号切り替え）

• *

• AND、 OR

• ARRAY_CONSTRUCT

• CASE

• CAST、 ::

• CONCAT、 ||

• ENDSWITH

• IS [ NOT ] NULL

• IFF

• IFNULL

• [ NOT ] IN

• LOWER

• NOT

• NULLIF

• NVL2

• SPLIT_PART

• STARTSWITH

• SUBSTR , SUBSTRING

• UPPER

• ZEROIFNULL

手動で追加:

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

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

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

たとえば、列 col1、 col2、および 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 句に非パーティション列が含まれる場合、それらのフィルターは、データファイルがフィルター処理された 後 、評価されます。

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

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 = 'string' | 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 = 'string' | 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）または二重引用符で囲まれたエスケープ（''）を使用します。

デフォルト: 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	すべての言語
ウィンドウズ874	WINDOWS874	タイ
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。

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）

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

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

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

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

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

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

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;

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;

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

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

CREATE MATERIALIZED VIEW et1_mv
  AS
  SELECT col2 FROM et1;

この 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()) に * を使用すると、返される結果が16 MB よりも大きい場合にエラーの発生する可能性があります。次の例で示すように、大きな結果セットの場合は * の使用を避け、クエリにはクエリに必要な列、 COLUMN NAME、 TYPE、および NULLABLE のみを使用します。 WITHIN GROUP (ORDER BY order_id) を使用する場合は、オプションの列 ORDER_ID を含めることができます。

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

Copy