Amazon Redshift Spectrum で外部テーブルを作成してクエリを実行する方法を教えてください。

解決策

Amazon Simple Storage Service (Amazon S3) からデータをクエリする際、Amazon Redshift テーブルにデータをロードする必要はありません。Redshift Spectrum がクエリを処理している間、データは S3 バケットに残ります。

重要: Amazon Redshift クラスターと S3 バケットが同じ AWS リージョンにあることを確認してください。

Redshift Spectrum を使用して外部テーブルを作成するには、次の手順を実行します。

1. Amazon Redshift 用の AWS Identity and Access Management (IAM) ロールを作成します。

2. 次の IAM ポリシーをアタッチし、データカタログへのアクセスに必要なアクセス許可を Amazon Redshift に付与します。
   AWS Glue データカタログを使用する場合は、ロールに IAM ポリシー AmazonS3ReadOnlyAccess および AWSGlueConsoleFullAccess をアタッチします。
   Amazon Athena データカタログを使用する場合は、ロールに IAM ポリシー AmazonAthenaFullAccess をアタッチします。

3. IAM ロールを Amazon Redshift クラスターに関連付けます。

4. 外部スキーマを作成します。

5. データファイルをダウンロードし、お使いのリージョン内の S3 バケットにアップロードします。次の例では、S3 用の TICKIT サンプルデータファイルを使用しています。

   s3://<bucket_name>/tickit/spectrum/event/' and 's3://bucket_name/tickit/spectrum/sales/

   注: bucket name は、実際の S3 バケット名に置き換えます。

6. 外部テーブルを作成します。次の例では、EVENT データ用の外部テーブルを作成します。

   create external table spectrum.event(
       eventid integer,
       venueid smallint,
       catid smallint,
       dateid smallint,
       eventname varchar(200),
       starttime timestamp
   )
   row format delimited
   fields terminated by '|'
   stored as textfile
   location 's3://bucket_name/tickit/spectrum/event/';

   注: bucket name は、実際の S3 バケット名に置き換えます。AWS Glue を使用して外部テーブルを作成するには、データカタログにテーブル定義を追加する必要があります。
   Amazon Athena を使用して外部テーブルを作成するには、テーブル定義を追加します。
   Athena を使用したテーブル定義の例

   CREATE EXTERNAL TABLE spectrum.event (
         eventid int,
         venueid smallint,
         catid smallint,
         dateid smallint,
         eventname varchar(max),
         starttime timestamp)
       ROW FORMAT DELIMITED
         FIELDS TERMINATED BY '|'
       STORED AS INPUTFORMAT
         'org.apache.hadoop.mapred.TextInputFormat'
       OUTPUTFORMAT

7. 外部スキーマが参照しているすべての外部テーブルを確認するには、SVV_EXTERNAL_TABLES に対して次のクエリを実行します。

   select schemaname , tablename , location from svv_external_tables where schemaname = 'spectrum';
   
   schemaname | tablename | location
   ----------------+---------------------------------+-----------------------------------------------------------------------
   spectrum | event | s3://bucket-name/file-location

   注: 実際のものでそれぞれ、bucket name を S3 バケット名に、file location をファイルの場所に置き換えます。

8. 外部テーブルを外部の Redshift Spectrum テーブルとしてクエリします。SELECT ステートメントを使用します。

   select top 3 spectrum.sales.eventid, sum(spectrum.sales.pricepaid) from spectrum.sales, spectrum.event
   where spectrum.sales.eventid = spectrum.event.eventid
   and spectrum.sales.pricepaid > 30
   group by spectrum.sales.eventid
   order by 2 desc;
   
    eventid |   sum
   ---------+----------
        289 | 51846.00
       7895 | 51049.00
       1602 | 50301.00

   上記のクエリ例では、外部の SALES テーブルを外部の EVENT テーブルと結合します。

関連情報

Amazon Redshift Spectrum

Amazon Redshift Spectrum でのクエリに関するトラブルシューティング