Amazon Redshift Spectrum で外部テーブルを作成およびクエリするにはどうすればよいですか?

解決方法

Amazon Redshift Spectrum を使用すると、Amazon Redshift テーブルにデータを読み込まずに Amazon Simple Storage Service (Amazon S3) からデータをクエリできます。データが Amazon S3 バケットに残っていると、Amazon Redshift Spectrum がすべてのクエリを処理します。

重要: 開始前に、Amazon Redshift が S3 バケットおよび外部データカタログにアクセスする権限があるかどうかを確認してください。また、Amazon Redshift クラスターと S3 バケットは同じ AWS リージョンに存在している必要があります。

Amazon Redshift Spectrum で外部テーブルを作成するには、次の手順を実行してください。

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

2.    IAM ポリシーをアタッチします。
AWS Glue データカタログを使用している場合は、ロールに AmazonS3ReadOnlyAccess および AWSGlueConsoleFullAccess IAM ポリシーをアタッチします。
Amazon Athena データカタログを使用している場合は、ロールに AmazonAthenaFullAccess IAM ポリシーをアタッチします。

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

4.    外部スキーマを作成します。外部スキーマは、外部データカタログ内のデータベースを参照します。外部スキーマは、Amazon Redshift が S3 へアクセスするのを許可する Amazon リソースネーム (ARN) を持つ IAM ロールも提供します。

次の例では、 S3 (tickitdb.zip) のサンプルデータファイルを使用しています。次のようにして、AWS リージョンの S3 バケットに個々のファイルを解凍し、読み込みます。

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

以下のようなコマンドを使用して外部スキーマを作成できます。

create external schema spectrum
from data catalog   
database 'spectrumdb'
iam_role 'arn:aws:iam::123456789012:role/mySpectrumRole'
create external database if not exists;

注意: IAM ロールの ARN を、作成した ARN に置き換えます。データベースパラメータには、必ず外部データベースの名前 (「spectrumdb」など) を指定してください。

5.    外部テーブルを作成します。例えば、次のようにして EVENT データの外部テーブルを作成できます。

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

外部テーブルの詳細については、Redshift Spectrum 用の外部テーブルの作成を参照してください。

AWS Glue を使って外部テーブルを作成するには、必ず AWS Glue データカタログにテーブル定義を追加してください。AWS Glue データカタログにテーブル定義を追加するには、いくつかの方法があります。テーブル定義の追加の詳細については、「AWS GlueConsole でのテーブルの操作」を参照してください。

Amazon Athena を使用して外部テーブルを作成するには、次のようにテーブル定義を追加します。

CREATE EXTERNAL TABLE `spectrumdb.event`(  `eventid` int, 
      `venueid` smallint, 
      `catid` smallint, 
      `dateid` smallint, 
      `eventname` string, 
      `starttime` timestamp)
    ROW FORMAT DELIMITED 
      FIELDS TERMINATED BY '|' 
    STORED AS INPUTFORMAT 
      'org.apache.hadoop.mapred.TextInputFormat' 
    OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION  's3://<bucket_name>/tickit/spectrum/event';

6.    SVV_EXTERNAL_TABLES に次のクエリを実行して、外部スキーマが参照するすべての外部テーブルを表示します。

select schemaname , tablename , location from svv_external_tables where schemaname = 'spectrum';

schemaname | tablename | location
----------------+---------------------------------+-----------------------------------------------------------------------
spectrum | event | s3://<bucket-name>/<file-location>

7.    SELECT ステートメントを利用して、外部テーブルを（外部 Amazon Redshift Spectrum テーブルとして）クエリします。

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 テーブルを結合します。</p

---

関連情報

Amazon Redshift Spectrum を使用した外部データのクエリ実行

Amazon Redshift Spectrum のクエリのトラブルシューティング