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 でのクエリのトラブルシューティング