メニュー
Amazon Redshift
データベース開発者ガイド (API Version 2012年12月1日)

CREATE EXTERNAL TABLE

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

外部テーブルへのアクセスは、外部スキーマへのアクセスによってコントロールされます。外部テーブルに対してアクセス権限の GRANT または REVOKE を実行することはできません。代わりに、外部スキーマに対してアクセス権限の付与または削除を実行します。

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

他の Amazon Redshift テーブルで使用したものと同じ SELECT 構文で、外部テーブルにクエリを実行できます。外部テーブルは読み取り専用です。外部テーブルに書き込むことはできません。

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

トランザクション内 (BEGIN… END) で CREATE EXTERNAL TABLE を実行することはできません。

構文

CREATE EXTERNAL TABLE external_schema.table_name (column_name data_type [, …] ) [ PARTITIONED BY (col_name data_type [, … ] )] [ ROW FORMAT DELIMITED row_format ] STORED AS format LOCATION 's3://bucket/folder/' [ TABLE PROPERTIES ( 'numRows'='row_count' ) ]

Parameters

external_schema.table_name

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

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

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

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

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

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

( column_name data_type )

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

列名の最大長は 127 バイトです。それより長い名前は 127 バイトまで切り詰められます。最大 4 バイトまで UTF-8 マルチバイト文字を使用できます。1 つのテーブルで定義できる列の最大数は 1,600 です。有効な名前の詳細については、「名前と識別子」を参照してください。

注記

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

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 データタイフ゜はパーティション列としてのみ使用できます)

  • TIMESTAMP

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

PARTITIONED BY (col_name data_type [, … ] )

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

パーティション化されたテーブルを作成した後は、ALTER TABLE … ADD PARTITION ステートメントを使用してテーブルを変更し、パーティションを追加します。パーティションを追加する際は、パーティションデータを含む Amazon S3 でサブフォルダの位置を定義します。パーティションは各 ALTER TABLE ステートメントに 1 つだけ追加できます。

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

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

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

ROW FORMAT DELIMITED rowformat

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

  • FIELDS TERMINATED BY 'delimiter'

  • LINES TERMINATED BY 'delimiter'

ROW FORMAT DELIMITED が省略されると、デフォルト形式は '\A' and lines terminated by '\n' で終了するフィールドになります。

STORED AS format

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

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

  • PARQUET

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

  • SEQUENCEFILE

  • TEXTFILE

  • ORC

LOCATION 's3://bucket/folder/'

データファイルを含む Amazon S3 フォルダへのパス ('s3://mybucket/custdata/' など)。バケットはクラスターと同じリージョンに存在する必要があります。サポートされているリージョンは、us-east-1、us-east-2、および us-west-2 です。

Redshift Spectrum は、指定されたフォルダおよびサブフォルダ内のファイルをスキャンします。Redshift Spectrum は、隠しファイル、ファイル名がピリオド、下線、ハッシュマーク (「.」、「_」、「#」) で始まるファイル、またはファイル名がチルド (「~」) で終わるファイルは無視します。

TABLE PROPERTIES ( 'numRows'='row_count' )

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

注記

numRows プロパティは大文字と小文字を区別します。

使用に関する注意事項

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

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

Copy
create external table spectrum.sales( salesid integer, listid integer, sellerid integer, buyerid integer, eventid integer, dateid smallint, 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');

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

Copy
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 コマンドを実行します。

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

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

Copy
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 システムビューにクエリを実行します。

Copy
select schemaname, tablename, values, location from svv_external_partitions where tablename = 'sales_part';
Copy
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

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

Copy
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 ビューにクエリを実行します。

Copy
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 spectrum | sales_part_timestamp | s3://rs-spectrum/sales_partition

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

Copy
select * from svv_external_columns where schemaname like 'rsspectrum%' and tablename ='listing';
 schemaname | tablename | columnname | external_type | part_key ------------+-----------+----------------+---------------+---------- rsspectrum | listing | dateid | smallint | 0 rsspectrum | listing | eventid | int | 0 rsspectrum | listing | listid | int | 0 rsspectrum | listing | listtime | timestamp | 0 rsspectrum | listing | numtickets | smallint | 0 rsspectrum | listing | priceperticket | decimal(8,2) | 0 rsspectrum | listing | sellerid | int | 0 rsspectrum | listing | totalprice | decimal(8,2) | 0 (8 rows)

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

Copy
select schemaname, tablename, values, location from svv_external_partitions where tablename = 'sales_part';
Copy
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