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

最終更新日: 2020 年 10 月 15 日

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 の IAM ロールを作成します

2.    AWS Identity and Access Management (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/

この例では、AWS Glue データカタログに外部データベースを作成します。

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 delimited
fields terminated by '|'
stored as textfile 
location 's3://<bucket_name>/tickit/spectrum/event/';

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

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

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