CREATE EXTERNAL TABLE - Amazon Redshift

CREATE EXTERNAL TABLE

指定のスキーマに新しい外部テーブルを作成します。外部テーブルはすべて、外部スキーマで作成されている必要があります。外部スキーマと外部テーブルは検索パスをサポートしていません。詳細については、「CREATE EXTERNAL SCHEMA」を参照してください。

Amazon Redshift では、CREATE EXTERNAL TABLE コマンドを使用して作成された外部テーブルに加えて、AWS Glueまたは AWS Lake Formation カタログ、あるいは Apache Hive メタストアで定義された外部テーブルの参照が可能です。CREATE EXTERNAL SCHEMA コマンドを使用して、外部カタログで定義された外部データベースを登録し、外部テーブルを Amazon Redshift で使用できるようにします。外部テーブルが AWS Glue または AWS Lake Formation カタログあるいは Hive メタストアに存在する場合は、CREATE EXTERNAL TABLE を使用してテーブルを作成する必要はありません。外部テーブルを表示するには、SVV_EXTERNAL_TABLESシステムビューに対してクエリを実行します。

CREATE EXTERNAL TABLE AS コマンドを実行することで、クエリからの列定義に基づいて外部テーブルを作成し、そのクエリの結果を Amazon S3 に書き込むことができます。結果は、Apache Parquet または区切りテキスト形式です。外部テーブルにパーティションキーがある場合、Amazon Redshift はそれらのパーティションキーに従って新しいファイルをパーティション分割し、新しいパーティションを外部カタログに自動的に登録します。CREATE EXTERNAL TABLE AS の詳細については、「使用に関する注意事項」を参照してください。

他の Amazon Redshift テーブルで使用したものと同じ SELECT 構文で、外部テーブルにクエリを実行できます。INSERT 構文を使用して、Amazon S3 の外部テーブルの場所に新しいファイルを書き込むこともできます。詳細については、「INSERT (外部テーブル)」を参照してください。

外部テーブルでビューを作成するには、CREATE VIEWステートメントに WITH NO SCHEMA BINDING 句を含めます。

トランザクション内 (BEGIN… END) で CREATE EXTERNAL TABLE を実行することはできません。トランザクションの詳細については、「直列化可能分離」を参照してください。

必要な権限

外部テーブルを作成するには、外部スキーマの所有者またはスーパーユーザーである必要があります。外部スキーマの所有者を移行するには、「ALTER SCHEMA」を使用して所有者を変更します。外部テーブルへのアクセスは、外部スキーマへのアクセスによってコントロールされます。外部テーブルに対してアクセス権限の GRANT または REVOKE を実行することはできません。代わりに、外部スキーマに対して USAGE の付与または削除を実行します。

使用に関する注意事項 には、外部テーブルに対する特定のアクセス許可に関する追加情報があります。

構文

CREATE EXTERNAL TABLE
external_schema.table_name
(column_name data_type [, …] )
[ PARTITIONED BY (col_name data_type [, … ] )]
[ { ROW FORMAT DELIMITED row_format |
  ROW FORMAT SERDE 'serde_name'
  [ WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ] } ]
STORED AS file_format
LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' }
[ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ]

以下に示しているのは、CREATE EXTERNAL TABLE AS の構文です。

CREATE EXTERNAL TABLE
external_schema.table_name
[ PARTITIONED BY (col_name [, … ] ) ]
[ ROW FORMAT DELIMITED row_format ]
STORED AS file_format
LOCATION { 's3://bucket/folder/' }
[ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ]
 AS
 { select_statement }
         

パラメータ

external_schema.table_name

作成され、外部スキーマ名で修飾されるテーブルの名前。外部テーブルは、外部スキーマで作成されている必要があります。詳細については、「CREATE EXTERNAL SCHEMA」を参照してください。

テーブル名の最大長は 127 バイトです。それより長い名前は 127 バイトまで切り詰められます。最大 4 バイトまで UTF-8 マルチバイト文字を使用できます。Amazon Redshift では、ユーザー定義の一時テーブルと、クエリ処理またはシステムメンテナンス中に Amazon Redshift によって作成された一時テーブルを含め、クラスターごとに 9,900 テーブルの制限を適用します。必要に応じて、データベース名でテーブル名を修飾することができます。次の例では、データベース名は spectrum_db、外部スキーマ名は spectrum_schema、テーブル名は test です。

create external table spectrum_db.spectrum_schema.test (c1 int) stored as parquet location 's3://mybucket/myfolder/';

指定のデータベースまたはスキーマが存在せず、テーブルが作成されていない場合、このステートメントはエラーを返します。システムデータベース template0template1padb_harvest、または sys:internal にテーブルまたはビューを作成することはできません。

テーブル名は、指定のスキーマで一意の名前にする必要があります。

有効な名前の詳細については、「名前と識別子」を参照してください。

( column_name data_type )

作成される各列の名前とデータタイプ。

列名の最大長は 127 バイトです。それより長い名前は 127 バイトまで切り詰められます。最大 4 バイトまで UTF-8 マルチバイト文字を使用できます。列名 "$path" または "$size" は指定できません。有効な名前の詳細については、「名前と識別子」を参照してください。

デフォルトでは、Amazon Redshift は疑似列 ($path および $size) を使用して外部テーブルを作成します。セッションの疑似列の作成を無効にするには、spectrum_enable_pseudo_columns設定パラメータを false に設定します。詳細については、「疑似列 」を参照してください。

擬似列が有効な場合、1 つのテーブルで定義できる列の最大数は 1,598 です。擬似列が有効でない場合、1 つのテーブルで定義できる列の最大数は 1,600 です。

「横長のテーブル」を作成する場合は、ロードとクエリ処理の結果が即時に表示されるように、列リストが行幅の限度を超えないことを確認します。詳細については、「使用に関する注意事項」を参照してください。

CREATE EXTERNAL TABLE AS コマンドの場合、列はクエリから取得されるため、列リストは必要ありません。

data_type

次の データ型 がサポートされています。

  • SMALLINT (INT2)

  • INTEGER (INT、INT4)

  • BIGINT (INT8)

  • DECIMAL (NUMERIC)

  • REAL (FLOAT4)

  • DOUBLE PRECISION (FLOAT8)

  • BOOLEAN (BOOL)

  • CHAR (CHARACTER)

  • VARCHAR (CHARACTER VARYING)

  • VARBYTE (CHARACTER VARYING) – Parquet および ORC データファイルで、パーティション化されていないテーブルでのみ使用できます。

  • DATE – テキスト、Parquet、または ORC データファイルでのみ使用できます。またはパーティション列としてのみ使用できます。

  • TIMESTAMP

DATE では、以下に示す形式を使用できます。数字を使用して表される月の値では、次の形式がサポートされています。

  • mm-dd-yyyy は、例えば、05-01-2017 です。これがデフォルトです。

  • yyyy-mm-dd、ただし年は 2 桁以上で表します。例えば、2017-05-01 と指定します。

3 文字の略語を使用して表される月の値では、次の形式がサポートされています。

  • mmm-dd-yyyy は、例えば、may-01-2017 です。これがデフォルトです。

  • dd-mmm-yyyy、ただし年は 2 桁以上で表します。例えば、01-may-2017 と指定します。

  • yyyy-mmm-dd、ただし年は 3 桁以上で表します。例えば、2017-may-01 と指定します。

年の値が一貫して 100 未満の場合、年は次の方法で計算されます。

  • 年の値が 70 より小さい場合、年は 2000 を足した数として計算されます。例えば、mm-dd-yyyy形式での 05-01-17 という日付は 05-01-2017 に変換されます。

  • 年の値が 100 未満で 69 より大きい場合、年は 1900 を足した数として計算されます。例えば、mm-dd-yyyy形式での 05-01-89 という日付は 05-01-1989 に変換されます。

  • 2 桁で表される年の値については、先頭にゼロを追加した 4 桁として年を表します。

テキストファイルのタイムスタンプ値は、yyyy-mm-dd HH:mm:ss.SSSSSS形式であることが必要です。次のタイムスタンプ値は、2017-05-01 11:30:59.000000となっています。

VARCHAR 列の長さは、文字単位ではなくバイト単位で定義されます。例えば、VARCHAR(12) 列には、シングルバイト文字なら 12 個、2 バイト文字なら 6 個含めることができます。外部テーブルのクエリを実行すると、エラーが返されずに、定義された列サイズに合うように結果が切り捨てられます。詳細については、「ストレージと範囲」を参照してください。

最高のパフォーマンスを得るために、データに合う最小の列サイズを指定することをお勧めします。列の値の最大サイズ (バイト単位) を調べるには、OCTET_LENGTH 関数を使用します。次の例では、E メール列の値の最大サイズを返します。

select max(octet_length(email)) from users; max --- 62
PARTITIONED BY (col_name data_type [, … ] )

1 つ以上のパーティション列でパーティション化されたテーブルを定義する句。指定の組み合わせごとに独立したデータディレクトリが使用されます。これにより、一部の状況でクエリパフォーマンスが向上します。パーティション化された列はテーブルデータ内には存在しません。テーブル列と同じ col_name の値を使用すると、エラーになります。

パーティションテーブルを作成した後、ALTER TABLE… ADD PARTITION ステートメントを使用してテーブルを変更し、新しいパーティションを外部カタログに登録します。パーティションを追加する際は、パーティションデータを含む で Amazon S3 サブフォルダの位置を定義します。

たとえば、テーブル spectrum.lineitem_partPARTITIONED BY (l_shipdate date) で定義されている場合は、以下の ALTER TABLE コマンドを実行してパーティションを追加します。

ALTER TABLE spectrum.lineitem_part ADD PARTITION (l_shipdate='1992-01-29') LOCATION 's3://spectrum-public/lineitem_partition/l_shipdate=1992-01-29';

CREATE EXTERNAL TABLE AS を使用する場合、ALTER TABLE...ADD PARTITION を実行する必要はありません。Amazon Redshift は、新しいパーティションを外部カタログに自動的に登録します。また、Amazon Redshift は、テーブルに定義された 1 つまたは複数のパーティションキーに基づいて、対応するデータを Amazon S3 のパーティションに自動的に書き込みます。

パーティションを表示するには、SVV_EXTERNAL_PARTITIONSシステムビューにクエリを実行します。

注記

CREATE EXTERNAL TABLE AS コマンドの場合、この列はクエリから取得されるため、パーティション列のデータ型を指定する必要はありません。

ROW FORMAT DELIMITED rowformat

仮想データの型式を指定する句。以下に、rowformat で想定される値を示します。

  • LINES TERMINATED BY 'delimiter'

  • FIELDS TERMINATED BY 'delimiter'

'区切り記号'に 1 つの ASCII 文字を指定します。 印刷不能な ASCII 文字は、'\ddd'の形式 (d は、0~7 で表され最大 '\177' までを取る 8 進数) を使用して指定できます。次の例では、BEL (ベル) 文字を 8 進数で指定しています。

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\007'

ROW FORMAT を省略した場合のデフォルト形式は DELIMITED FIELDS TERMINATED BY '\A' (ヘッダーの開始) と LINES TERMINATED BY '\n' (改行) です。

ROW FORMAT SERDE 'serde_name'
[WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ]

基盤となるデータの SERDE 形式を指定する句。

'serde_name'

SerDe 名。以下の形式を指定できます。

  • org.apache.hadoop.hive.serde2.RegexSerDe

  • com.amazonaws.glue.serde.GrokSerDe

  • org.apache.hadoop.hive.serde2.OpenCSVSerde

    このパラメーターは、OpenCSVSerde において、次の SerDe プロパティをサポートします。

    'wholeFile' = 'true'

    wholeFile プロパティに true を設定することで、OpenCSV リクエストの引用符で囲まれた文字列内の改行文字 (\ n) を、正しく解析することができます。

  • org.openx.data.jsonserde.JsonSerDe

    • JSON SERDE は Ion ファイルもサポートしています。

    • JSON は正しい形式になっている必要があります。

    • Ion および JSON 形式のタイムスタンプには、ISO8601 形式を使用する必要があります。

    • このパラメータでは、JsonSerDe のための、次の SerDe プロパティがサポートされています。

      'strip.outer.array'='true'

      配列内に複数の JSON レコードが含まれているかのように、大括弧で囲まれた 1 つの非常に大きな配列 ( [ … ] ) を含む Ion/JSON ファイルを処理します。

  • com.amazon.ionhiveserde.IonHiveSerDe

    Amazon ION 形式では、データ型に加えて、テキスト形式とバイナリ形式を使用できます。ION 形式のデータを参照する外部テーブルの場合、外部テーブルの各列を、対応する ION 形式データの各要素にマッピングします。詳細については、「Amazon Ion」を参照してください。また、入力形式と出力形式を指定する必要もあります。

WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ]

オプションで、プロパティ名と値をコンマで区切って指定します。

ROW FORMAT を省略した場合のデフォルト形式は DELIMITED FIELDS TERMINATED BY '\A' (ヘッダーの開始) と LINES TERMINATED BY '\n' (改行) です。

STORED AS file_format

外部データファイルのファイル形式。

有効な形式は次のとおりです。

  • PARQUET

  • RCFILE (LazyBinaryColumnarSerDe ではなく ColumnarSerD eのみを使用するデータ用)

  • SEQUENCEFILE

  • TEXTFILE (JSON ファイルを含むテキストファイル)。

  • ORC

  • AVRO

  • INPUTFORMAT 'input_format_classname' OUTPUTFORMAT 'output_format_classname'

CREATE EXTERNAL TABLE AS コマンドは、TEXTFILE と PARQUET の 2 つのファイル形式のみをサポートしています。

INPUTFORMAT と OUTPUTFORMAT では、以下の例に示すように、クラス名を指定します。

'org.apache.hadoop.mapred.TextInputFormat'
LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file'}

データファイルを含む Amazon S3 バケットかフォルダ、または Amazon S3 オブジェクトパスのリストを含むマニフェストファイルへのパス。バケットは、Amazon Redshift クラスターと同じ AWS リージョン内に置かれている必要があります。サポートされている AWS リージョンの一覧は、「Amazon Redshift Spectrum の考慮事項」でご確認ください。

パスがバケットかフォルダを指定している場合 ('s3://mybucket/custdata/' など)、Redshift Spectrum は指定されたバケットかフォルダ、およびサブフォルダのファイルをスキャンします。Redshift Spectrum は、隠しファイルと、ファイル名がピリオドやアンダースコアで始まるファイルを無視します。

パスがマニフェストファイルを指定している場合、's3://bucket/manifest_file'引数は 1 つのファイル (たとえば 's3://mybucket/manifest.txt' など) を明示的に参照する必要があります。キープレフィックスを参照することはできません。

マニフェストは、JSON 形式のテキストファイルであり、Amazon S3 からロードする各ファイルの URL とファイルサイズ (バイト単位) を示します。URL にはバケット名およびファイルの完全オブジェクトパスが含まれます。マニフェストで指定するファイルの場所は異なるバケットでもかまいませんが、すべてのバケットは Amazon Redshift クラスターと同じ AWS リージョンに置かれている必要があります。ファイルが 2 回リストされている場合、ファイルは 2 回ロードされます。次の例は、3 つのファイルをロードするマニフェストの JSON を示しています。

{ "entries": [ {"url":"s3://mybucket-alpha/custdata.1", "meta": { "content_length": 5956875 } }, {"url":"s3://mybucket-alpha/custdata.2", "meta": { "content_length": 5997091 } }, {"url":"s3://mybucket-beta/custdata.1", "meta": { "content_length": 5978675 } } ] }

特定のファイルを含めることを必須にすることができます。これを行うには、マニフェストのファイルレベルで mandatory オプションを含めます。欠落している必須ファイルを使用して外部テーブルをクエリすると、SELECT ステートメントは失敗します。外部テーブルの定義に含まれるすべてのファイルが存在することを確認します。これらがすべて存在しない場合は、最初の必須ファイルが見つかりませんというエラーが表示されます。次の例では、mandatoryオプションを true に設定したマニフェストの JSON を説明します。

{ "entries": [ {"url":"s3://mybucket-alpha/custdata.1", "mandatory":true, "meta": { "content_length": 5956875 } }, {"url":"s3://mybucket-alpha/custdata.2", "mandatory":false, "meta": { "content_length": 5997091 } }, {"url":"s3://mybucket-beta/custdata.1", "meta": { "content_length": 5978675 } } ] }

UNLOAD を使用して作成したファイルを参照する場合は、UNLOADで MANIFEST パラメータを使用して作成したマニフェストを使用できます。マニフェストファイルは、「Amazon S3 からの COPY」でのマニフェストファイルと互換性がありますが、使用するキーが異なります。使用しないキーは無視されます。

TABLE PROPERTIES ( 'property_name'='property_value' [, ...] )

テーブルプロパティのテーブル定義を設定する句。

注記

テーブルのプロパティでは、大文字と小文字が区別されます。

'compression_type'='value'

ファイル名に拡張子が含まれていない場合に使用する圧縮タイプを設定するプロパティ。ファイル拡張子があるときに、このプロパティを設定すると、拡張子は無視されて、プロパティで設定された値が使用されます。圧縮タイプの有効値は次のとおりです。

  • bzip2

  • gzip

  • なし

  • snappy

'data_cleansing_enabled'='true / false’

このプロパティは、テーブルのデータ処理が有効かどうかを設定します。'data_cleansing_enabled' が true に設定されている場合は、テーブルのデータ処理が有効です。'data_cleansing_enabled' が false に設定されている場合、テーブルのデータ処理は無効です。以下は、このプロパティが制御するテーブルレベルでのデータ処理プロパティのリストです。

  • column_count_mismatch_handling

  • invalid_char_handling

  • overflow_handling

  • replacement_char

  • surplus_char_handling

例については、「データ処理の例」を参照してください。

'invalid_char_handling'='value'

クエリ結果に無効な UTF-8 文字値が含まれている場合に実行するアクションを指定します。以下のアクションを指定できます。

DISABLED

無効な文字の処理を実行しません。

FAIL

無効な UTF-8 値が含まれたデータを返すクエリをキャンセルします。

SET_TO_NULL

無効な UTF-8 値を null に置き換えます。

DROP_ROW

行内の各値を null に置き換えます。

REPLACE

replacement_char を使用して無効な文字を指定した置換文字に置き換えます。

'replacement_char'='character

invalid_char_handlingREPLACE に設定するときに使用する置換文字を指定します。

'numeric_overflow_handling'='value’

ORC データに列定義 (例えば SMALLINT や int16) よりも大きい整数 (例えば BIGINT や int64) が含まれているときに実行するアクションを指定します。以下のアクションを指定できます。

DISABLED

無効な文字の処理が無効化されています。

FAIL

データに無効な文字が含まれているときにクエリをキャンセルします。

SET_TO_NULL

無効な文字を null に設定します。

DROP_ROW

行内の各値を null に設定します。

'surplus_bytes_handling'='value'

ロードされているデータで、VARBYTE データが含まれる列に定義されたデータ型の長さを超えるデータの処理方法を指定します。Redshift Spectrum はデフォルトで、列の幅を超えるデータの値を null に設定します。

クエリがデータ型の長さを超えるデータを返すときに実行する以下のアクションを指定できます。

SET_TO_NULL

列幅を超えるデータを null に置き換えます。

無効

余剰バイトの処理を実行しません。

FAIL

列幅を超えるデータを返すクエリをキャンセルします。

DROP_ROW

列幅を超えるデータを含むすべての行を削除します。

TRUNCATE

列に定義された最大文字数を超える文字を削除します。

'surplus_char_handling'='value'

ロードされているデータで、VARCHAR、CHAR、または文字列データが含まれる列に定義されたデータ型の長さを超えるデータの処理方法を指定します。Redshift Spectrum はデフォルトで、列の幅を超えるデータの値を null に設定します。

クエリが列幅を超えるデータを返すときに実行する以下のアクションを指定できます。

SET_TO_NULL

列幅を超えるデータを null に置き換えます。

無効

余剰文字の処理を実行しません。

FAIL

列幅を超えるデータを返すクエリをキャンセルします。

DROP_ROW

行内の各値を null に置き換えます。

TRUNCATE

列に定義された最大文字数を超える文字を削除します。

'column_count_mismatch_handling'='value'

ファイルに含まれる行の値が、外部テーブル定義で指定された列数よりも少ないか多いかを識別します。このプロパティは、非圧縮テキストファイル形式でのみ使用できます。以下のアクションを指定できます。

DISABLED

列数の不一致処理が無効化されています。

FAIL

列数の不一致が検出された場合、クエリは失敗します。

SET_TO_NULL

欠落した値を NULL で埋め、各行の追加の値を無視します。

DROP_ROW

列数の不一致エラーを含むすべての行をスキャンからドロップします。

'numRows'='row_count'

テーブル定義の numRows 値を設定するプロパティ。外部テーブルの統計を明示的に更新するには、テーブルのサイズを示す numRows プロパティを設定します。Amazon Redshift は、外部テーブルを分析して、クエリオプティマイザがクエリプランを生成するために使用するテーブル統計を生成することはありません。外部テーブルに対してテーブル統計が設定されていない場合、Amazon Redshift は、外部テーブルが大きなテーブルであり、ローカルテーブルが小さいテーブルであるという前提に基づいてクエリ実行プランを生成します。

'skip.header.line.count'='line_count'

各ソースファイルの最初に省略する行数を設定するプロパティ。

'serialization.null.format'=' '

範囲を示すプロパティは、フィールドで指定されたテキストに完全に一致するものがある場合に、値 NULL を返します。

'orc.schema.resolution'='mapping_type'

ORC データ形式を使用するテーブルの列マッピングタイプを設定するプロパティ。このプロパティは、他のデータ形式では無視されます。

列マッピングタイプの有効値は次のとおりです。

  • name

  • position

orc.schema.resolution プロパティが省略されている場合、列はデフォルトで、名前を基準としてマップされます。orc.schema.resolution'name' または 'position' 以外の値に設定されている場合、列は位置を基準としてマップされます。列マッピングの詳細については、「外部テーブル列を ORC 列にマッピングする」を参照してください。

注記

COPY コマンドは、位置のみを基準として ORC データファイルにマップします。orc.schema.resolution テーブルプロパティは、COPY コマンドの動作には影響しません。

'write.parallel'='on / off'

CREATE EXTERNAL TABLE AS がデータを並列で書き込む必要があるかどうかを設定するプロパティ。デフォルトでは、CREATE EXTERNAL TABLE AS は、クラスター内のスライスの数に応じて、データを複数のファイルに並列で書き込みます。デフォルトのオプションは on です。'write.parallel' が off に設定されている場合、CREATE EXTERNAL TABLE AS は 1 つ以上のデータファイルを Amazon S3 に順次に書き込みます。このテーブルプロパティは、同じ外部テーブルへの後続の INSERT ステートメントにも適用されます。

'write.maxfilesize.mb'='size'

CREATE EXTERNAL TABLE AS によって Amazon S3 に書き込まれる各ファイルの最大サイズ (MB 単位) を設定するプロパティ。サイズは 5 〜 6200 の有効な整数であることが必要です。デフォルトの最大ファイルサイズは 6,200 MB です。このテーブルプロパティは、同じ外部テーブルへの後続の INSERT ステートメントにも適用されます。

‘write.kms.key.id’=‘value

Amazon S3 オブジェクトのサーバー側の暗号化 (SSE) を有効にする AWS Key Management Service キーを指定できます。この value は、以下のいずれかになります。

  • Amazon S3 バケットに保存されたデフォルトの AWS KMS キーを使用するための auto

  • データを暗号化するために指定する kms-key

select_statement

クエリを定義して 1 つ以上の行を外部テーブルに挿入するステートメント。クエリによって生成されるすべての行は、テーブル定義に基づいて、テキストまたは Parquet 形式で Amazon S3 に書き込まれます。

例のコレクションは、 にあります。