CREATE EXTERNAL TABLE - Amazon Redshift

「翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。」

CREATE EXTERNAL TABLE

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

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

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 を実行することはできません。トランザクションの詳細については、「直列化可能分離」を参照してください。

Syntax

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 }
         

Parameters

external_schema.table_name

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

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

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

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

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

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

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

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

  • TIMESTAMP

テキストファイルのタイムスタンプ値は、次のタイムスタンプ値に示すように、 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 は、テーブルで定義されたパーティションキーに基づいて、対応するデータを Amazon S3 のパーティションに自動的に書き込みます。

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

注記

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

ROW FORMAT DELIMITED rowformat

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

  • LINES TERMINATED BY 'delimiter'

  • FIELDS TERMINATED BY 'delimiter'

'区切り記号'に 1 つの ASCII 文字を指定します。 '\ddd' の形式で 8 進数を使用して非表示の ASCII 文字を指定することができます。ここで、d は「\177」までの 8 進数 (0-7) です。次の例では、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

  • org.openx.data.jsonserde.JsonSerDe

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

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

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

    • 次のSerDeプロパティは、 でサポートされていますJsonSerDe。

      'strip.outer.array'='true'

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

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

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

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

STORED AS file_format

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

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

  • PARQUET

  • RCFILE ( のみを使用するデータ、 を使用しないColumnarSerDe)LazyBinaryColumnarSerDe

  • SEQUENCEFILE

  • TEXTFILE

  • 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

'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 データ形式を使用するテーブルの列マッピングタイプを設定するプロパティ。このプロパティは、他のデータ形式では無視されます。

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

  • 名前

  • 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 ステートメントにも適用されます。

select_statement

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

使用に関する注意事項

PG_TABLE_DEFSTV_TBL_PERM、PG_CLASS、information_schema など、標準の Amazon Redshift テーブルに使用したものと同じリソースを使用して Amazon Redshift Spectrum テーブルの詳細を表示することはできません。ビジネスインテリジェンスまたは分析ツールが Redshift Spectrum 外部テーブルを認識しない場合は、SVV_EXTERNAL_TABLES および SVV_EXTERNAL_COLUMNS にクエリを実行するようにアプリケーションを設定します。

CREATE EXTERNAL TABLE AS

場合によっては、AWS Glue データカタログ、AWS Lake Formation 外部カタログ、または Apache Hive メタストアに対して CREATE EXTERNAL TABLE AS コマンドを実行することがあります。このような場合は、AWS Identity and Access Management (IAM) ロールを使用して外部スキーマを作成します。この IAM ロールには、Amazon S3 に対する読み取りと書き込みの両方のアクセス許可が必要です。

Lake Formation カタログを使用する場合、IAM ロールにはカタログにテーブルを作成するアクセス許可が必要です。この場合、ターゲット Amazon S3 パスに対するデータレイクの場所のアクセス許可が必要です。この IAM ロールは新しい AWS Lake Formation テーブルの所有者になります。

ファイル名が必ず一意になるように、Amazon Redshift ではデフォルトで、Amazon S3 にアップロードされる各ファイルの名前に以下の形式が使用されます。

<date>_<time>_<microseconds>_<query_id>_<slice-number>_part_<part-number>.<format>.

例: 「20200303_004509_810669_1007_0001_part_00.parquet」。

CREATE EXTERNAL TABLE AS コマンドを実行するときは、以下の点を考慮してください。

  • Amazon S3 の場所は空であることが必要です。

  • Amazon Redshift では、STORED AS 句を使用する場合、PARQUET および TEXTFILE 形式のみがサポートされます。

  • 列定義リストを定義する必要はありません。新しい外部テーブルの列の名前とデータ型は、SELECT クエリから直接派生します。

  • PARTITIONED BY 句でパーティション列のデータ型を定義する必要はありません。パーティションキーを指定する場合、この列の名前が SELECT クエリの結果にあることが必要です。複数のパーティション列がある場合、SELECT クエリでのそれらの順序は重要ではありません。Amazon Redshift は、PARTITIONED BY 句で定義された順序を使用して、外部テーブルを作成します。

  • Amazon Redshift は、パーティションキーの値に基づいて、出力ファイルをパーティションフォルダに自動的にパーティション分割します。デフォルトでは、Amazon Redshift はパーティション列を出力ファイルから削除します。

  • LINES TERMINATED BY 'delimiter' 句はサポートされていません。

  • ROW FORMAT SERDE 'serde_name' 句はサポートされていません。

  • マニフェストファイルの使用はサポートされていません。したがって、Amazon S3 のマニフェストファイルに LOCATION 句を定義することはできません。

  • は、コマンドの最後で「Amazon Redshift」テーブルプロパティnumRowsを自動的に更新します。

  • 'compression_type' テーブルプロパティは、PARQUET ファイル形式に基づいて 'none' または 'snappy' のみを受け入れます。

  • Amazon Redshift では、外側の SELECT クエリで LIMIT 句を使用できません。代わりに、ネストされた LIMIT 句を使用できます。

  • STL_UNLOAD_LOG を使用して、各 CREATE EXTERNAL TABLE AS オペレーションによって Amazon S3 に書き込まれたファイルを追跡できます。

外部テーブルの作成およびクエリのアクセス許可

外部テーブルを作成するには、外部スキーマの所有者またはスーパーユーザーであることを確認してください。外部スキーマの所有者を移行するには、「ALTER SCHEMA」を使用します。次の例は、spectrum_schema スキーマの所有者を newowner に変更します。

alter schema spectrum_schema owner to newowner;

Redshift Spectrum クエリを実行するには、次のアクセス許可が必要です。

  • スキーマのアクセス権限の使用

  • 現在のデータベースに一時テーブルを作成するアクセス権限

次の例では、スキーマ spectrum_schema の使用許可を spectrumusers ユーザーグループに付与しています。

grant usage on schema spectrum_schema to group spectrumusers;

次の例では、データベース spectrumdb の一時アクセス権限を spectrumusers ユーザーグループに付与しています。

grant temp on database spectrumdb to group spectrumusers;

疑似列

Amazon Redshift はデフォルトで疑似列 $path および $size を使用して外部テーブルを作成します。これらの列を選択すると、Amazon S3 のデータファイルへのパスとクエリによって返された各行のデータファイルのサイズが表示されます。列名 ($path および $size) は、二重引用符で囲む必要があります。SELECT * 句は、疑似列を返しません。次の例に示すように、$path$size の列名をクエリに明示的に含める必要があります。

select "$path", "$size" from spectrum.sales_part where saledate = '2008-12-01';

セッションの疑似列の作成を無効にするには、spectrum_enable_pseudo_columns 設定パラメータを false に設定します。

重要

Redshift Spectrum では、Amazon S3 のデータファイルをスキャンして結果セットのサイズを決定するため、$size または $path を選択すると料金が発生します。詳細については、「Amazon Redshift 料金表」を参照してください。

Examples

次の例では、 という名前のAmazon Redshift外部スキーマに SALES という名前のテーブルを作成しますspectrum。 データはタブ区切りのテキストファイルになっています。TABLE PROPERTIES 句は、numRowsプロパティを 170,000 行に設定します。

create external table spectrum.sales( salesid integer, listid integer, sellerid integer, buyerid integer, eventid integer, saledate date, qtysold smallint, pricepaid decimal(8,2), commission decimal(8,2), saletime timestamp) row format delimited fields terminated by '\t' stored as textfile location 's3://awssampledbuswest2/tickit/spectrum/sales/' table properties ('numRows'='170000');

次の例では、 を使用して JSON 形式のデータを参照するテーブルを作成します。JsonSerDe

create external table spectrum.cloudtrail_json ( event_version int, event_id bigint, event_time timestamp, event_type varchar(10), awsregion varchar(20), event_name varchar(max), event_source varchar(max), requesttime timestamp, useragent varchar(max), recipientaccountid bigint) row format serde 'org.openx.data.jsonserde.JsonSerDe' with serdeproperties ( 'dots.in.keys' = 'true', 'mapping.requesttime' = 'requesttimestamp' ) location 's3://mybucket/json/cloudtrail';

以下の CREATE EXTERNAL TABLE AS の例では、パーティション分割されていない外部テーブルを作成します。次に、SELECT クエリの結果を Apache Parquet として Amazon S3 のターゲットの場所に書き込みます。

CREATE EXTERNAL TABLE spectrum.lineitem STORED AS parquet LOCATION 'S3://mybucket/cetas/lineitem/' AS SELECT * FROM local_lineitem;

以下の例では、パーティション分割された外部テーブルを作成し、パーティション列を SELECT クエリに含めます。

CREATE EXTERNAL TABLE spectrum.partitioned_lineitem PARTITIONED BY (l_shipdate, l_shipmode) STORED AS parquet LOCATION 'S3://mybucket/cetas/partitioned_lineitem/' AS SELECT l_orderkey, l_shipmode, l_shipdate, l_partkey FROM local_table;

外部データカタログ内の既存のデータベースについて、SVV_EXTERNAL_DATABASES システムビューにクエリを実行します。

select eskind,databasename,esoptions from svv_external_databases order by databasename;
eskind | databasename | esoptions -------+--------------+---------------------------------------------------------------------------------- 1 | default | {"REGION":"us-west-2","IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"} 1 | sampledb | {"REGION":"us-west-2","IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"} 1 | spectrumdb | {"REGION":"us-west-2","IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"}

外部テーブルの詳細を表示するには、SVV_EXTERNAL_TABLES および SVV_EXTERNAL_COLUMNS システムビューにクエリを実行します。

次の例では、SVV_EXTERNAL_TABLES ビューにクエリを実行します。

select schemaname, tablename, location from svv_external_tables;
schemaname | tablename | location -----------+----------------------+-------------------------------------------------------- spectrum | sales | s3://awssampledbuswest2/tickit/spectrum/sales spectrum | sales_part | s3://awssampledbuswest2/tickit/spectrum/sales_partition

次の例では、SVV_EXTERNAL_COLUMNS ビューにクエリを実行します。

select * from svv_external_columns where schemaname like 'spectrum%' and tablename ='sales';
schemaname | tablename | columnname | external_type | columnnum | part_key -----------+-----------+------------+---------------+-----------+--------- spectrum | sales | salesid | int | 1 | 0 spectrum | sales | listid | int | 2 | 0 spectrum | sales | sellerid | int | 3 | 0 spectrum | sales | buyerid | int | 4 | 0 spectrum | sales | eventid | int | 5 | 0 spectrum | sales | saledate | date | 6 | 0 spectrum | sales | qtysold | smallint | 7 | 0 spectrum | sales | pricepaid | decimal(8,2) | 8 | 0 spectrum | sales | commission | decimal(8,2) | 9 | 0 spectrum | sales | saletime | timestamp | 10 | 0

テーブルパーティションを表示するには、次のクエリを使用します。

select schemaname, tablename, values, location from svv_external_partitions where tablename = 'sales_part';
schemaname | tablename | values | location -----------+------------+----------------+------------------------------------------------------------------------- spectrum | sales_part | ["2008-01-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01 spectrum | sales_part | ["2008-02-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02 spectrum | sales_part | ["2008-03-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-03 spectrum | sales_part | ["2008-04-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-04 spectrum | sales_part | ["2008-05-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-05 spectrum | sales_part | ["2008-06-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-06 spectrum | sales_part | ["2008-07-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-07 spectrum | sales_part | ["2008-08-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-08 spectrum | sales_part | ["2008-09-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-09 spectrum | sales_part | ["2008-10-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-10 spectrum | sales_part | ["2008-11-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-11 spectrum | sales_part | ["2008-12-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-12

次の例では、外部テーブルの関連データファイルの合計サイズを返します。

select distinct "$path", "$size" from spectrum.sales_part; $path | $size ---------------------------------------+------- s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01/ | 1616 s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/ | 1444 s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/ | 1444

パーティション化の例

日付でパーティション化された外部テーブルを作成するには、次のコマンドを実行します。

create external table spectrum.sales_part( salesid integer, listid integer, sellerid integer, buyerid integer, eventid integer, dateid smallint, qtysold smallint, pricepaid decimal(8,2), commission decimal(8,2), saletime timestamp) partitioned by (saledate date) row format delimited fields terminated by '|' stored as textfile location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/' table properties ('numRows'='170000');

パーティションを追加するには、次の ALTER TABLE コマンドを実行します。

alter table spectrum.sales_part add if not exists partition (saledate='2008-01-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-02-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-03-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-03/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-04-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-04/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-05-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-05/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-06-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-06/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-07-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-07/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-08-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-08/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-09-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-09/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-10-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-10/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-11-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-11/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-12-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-12/';

パーティション化されたテーブルからデータを選択するには、次のクエリを実行します。

select top 10 spectrum.sales_part.eventid, sum(spectrum.sales_part.pricepaid) from spectrum.sales_part, event where spectrum.sales_part.eventid = event.eventid and spectrum.sales_part.pricepaid > 30 and saledate = '2008-12-01' group by spectrum.sales_part.eventid order by 2 desc;
eventid | sum --------+--------- 914 | 36173.00 5478 | 27303.00 5061 | 26383.00 4406 | 26252.00 5324 | 24015.00 1829 | 23911.00 3601 | 23616.00 3665 | 23214.00 6069 | 22869.00 5638 | 22551.00

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

select schemaname, tablename, values, location from svv_external_partitions where tablename = 'sales_part';
schemaname | tablename | values | location -----------+------------+----------------+-------------------------------------------------- spectrum | sales_part | ["2008-01-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01 spectrum | sales_part | ["2008-02-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02 spectrum | sales_part | ["2008-03-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-03 spectrum | sales_part | ["2008-04-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-04 spectrum | sales_part | ["2008-05-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-05 spectrum | sales_part | ["2008-06-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-06 spectrum | sales_part | ["2008-07-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-07 spectrum | sales_part | ["2008-08-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-08 spectrum | sales_part | ["2008-09-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-09 spectrum | sales_part | ["2008-10-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-10 spectrum | sales_part | ["2008-11-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-11 spectrum | sales_part | ["2008-12-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-12

行形式の例

次に、AVRO 形式で保存されたデータファイルの ROW FORMAT SERDE パラメータの指定例を示します。

create external table spectrum.sales(salesid int, listid int, sellerid int, buyerid int, eventid int, dateid int, qtysold int, pricepaid decimal(8,2), comment VARCHAR(255)) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe' WITH SERDEPROPERTIES ('avro.schema.literal'='{\"namespace\": \"dory.sample\",\"name\": \"dory_avro\",\"type\": \"record\", \"fields\": [{\"name\":\"salesid\", \"type\":\"int\"}, {\"name\":\"listid\", \"type\":\"int\"}, {\"name\":\"sellerid\", \"type\":\"int\"}, {\"name\":\"buyerid\", \"type\":\"int\"}, {\"name\":\"eventid\",\"type\":\"int\"}, {\"name\":\"dateid\",\"type\":\"int\"}, {\"name\":\"qtysold\",\"type\":\"int\"}, {\"name\":\"pricepaid\", \"type\": {\"type\": \"bytes\", \"logicalType\": \"decimal\", \"precision\": 8, \"scale\": 2}}, {\"name\":\"comment\",\"type\":\"string\"}]}') STORED AS AVRO location 's3://mybucket/avro/sales' ;

を使用して ROW FORMAT SERDE パラメータを指定する例を以下に示しますRegEx。

create external table spectrum.types( cbigint bigint, cbigint_null bigint, cint int, cint_null int) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' with serdeproperties ('input.regex'='([^\\x01]+)\\x01([^\\x01]+)\\x01([^\\x01]+)\\x01([^\\x01]+)') stored as textfile location 's3://mybucket/regex/types';

Grok を使用して ROW FORMAT SERDE パラメータを指定する例を以下に示します。

create external table spectrum.grok_log( timestamp varchar(255), pid varchar(255), loglevel varchar(255), progname varchar(255), message varchar(255)) row format serde 'com.amazonaws.glue.serde.GrokSerDe' with serdeproperties ('input.format'='[DFEWI], \\[%{TIMESTAMP_ISO8601:timestamp} #%{POSINT:pid:int}\\] *(?<loglevel>:DEBUG|FATAL|ERROR|WARN|INFO) -- +%{DATA:progname}: %{GREEDYDATA:message}') stored as textfile location 's3://mybucket/grok/logs';

次の例では、S3 バケットで Amazon S3 サーバーアクセスログを定義します。Redshift Spectrum を使用して Amazon S3 アクセスログにクエリを実行できます。

CREATE EXTERNAL TABLE spectrum.mybucket_s3_logs( bucketowner varchar(255), bucket varchar(255), requestdatetime varchar(2000), remoteip varchar(255), requester varchar(255), requested varchar(255), operation varchar(255), key varchar(255), requesturi_operation varchar(255), requesturi_key varchar(255), requesturi_httpprotoversion varchar(255), httpstatus varchar(255), errorcode varchar(255), bytessent bigint, objectsize bigint, totaltime varchar(255), turnaroundtime varchar(255), referrer varchar(255), useragent varchar(255), versionid varchar(255) ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ( 'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \"([^ ]*)\\s*([^ ]*)\\s*([^ ]*)\" (- |[^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\") ([^ ]*).*$') LOCATION 's3://mybucket/s3logs’;