この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
Amazon Redshift SpectrumはAmazon S3にあるデータに対して、Amazon Redshiftから直接クエリを投げる事が出来る機能です。前回のブログで Amazon Redshift Spectrumは RedshiftからS3のデータ用に外部スキーマと外部テーブルを定義すると Amazon Athena に自動的にデータベースとテーブルが追加されるということが判明しました。そこで、今回は逆に Amazon Redshift Spectrum が既存の Amazon Athena のテーブルを参照できるか試してみました。
RedshiftからS3のデータ用に外部スキーマと外部テーブルを定義してテーブルを参照する手順については以下のブログを参照してください。今回はこのブログの続編となります。
S3データを直接クエリ出来る新機能『Amazon Redshift Spectrum』を実際に試してみました
Spectrumの設定・利用手順
Spectrumの設定は Amazon Athena からデータ定義言語(DDL)コマンドを使用してデータベースとテーブルを定義した後、Amazon Redshift からデータ定義言語(DDL)コマンドを使用してテーブルを定義します。
ステップ1:Amazon Redshift 用の IAM ロールを作成する
バックエンドではAmazon Athenaと連携しているので、Amazon Athenaの外部データカタログとAmazon S3のデータファイルにアクセス持つIAMロールを作成してRedshiftクラスタに付与、もしくは権限を追加する必要があります。Amazon Redshiftでロールを使用する方法の詳細については、「IAMロールを使用したCOPYおよびUNLOAD操作の承認」を参照してください。
ここでは mySpectrumRole というロール名に、AmazonS3ReadOnlyAccess と AmazonAthenaFullAccessの2つのマネージドポリシーを付与しています。
ステップ2:IAM ロールをクラスタに関連付ける
Amazon Redshiftが外部データカタログとAmazon S3にアクセスすることを許可するIAMロールを作成したら、そのロールをAmazon Redshiftクラスタに関連付けます。ステップ1で作成した IAMロールの ロール ARN は外部テーブルを定義する際に利用します。
AWS Management Consoleにサインインし、Amazon Redshiftコンソール を開き、ステップ1で作成したmySpectrumRole を選択します。
ステップ3:Amazon Athena に データベースとテーブルを作成
Redshift から照会する Amazon Athena のデータベースとテーブルを作成します。
データベースの作成
データベースが存在しない場合はデータベースを作成します。
CREATE DATABASE IF NOT EXISTS spectrumdb;
テーブルの作成
後で以前のブログで作成した sales テーブルと比較できるように同じテーブル定義の sales_athena を作成します。
CREATE EXTERNAL TABLE IF NOT EXISTS spectrumdb.sales_athena (
salesid int,
listid int,
sellerid int,
buyerid int,
eventid int,
dateid smallint,
qtysold smallint,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '\t',
'field.delim' = '\t'
) LOCATION 's3://awssampledb/tickit/spectrum/sales/'
TBLPROPERTIES ('has_encrypted_data'='false');
ステップ4:Amazon Redshift に 外部スキーマの作成
外部スキーマは外部データカタログ内のデータベースを参照し、Amazon Athena が Amazon Redshift の代わりにAmazon S3にアクセスを認可するための IAMロールのARN を提供します。Amazon Redshift から Amazon Athena の Hiveメタストアを使用して、データベースを作成します。この例では、外部スキーマを作成するときにAmazon Redshiftでデータベースを作成します。
外部スキーマの作成
外部スキーマを作成するには、CREATE EXTERNAL SCHEMAコマンドを実行します。次のコマンドのIAMロールARNを、手順1で作成したロールARNに置き換えてから、SQLクライアントでコマンドを実行します。
cmdb=# create external schema spectrum
cmdb-# from data catalog
cmdb-# database 'spectrumdb'
cmdb-# iam_role 'arn:aws:iam::123456789012:role/mySpectrumRole';
CREATE SCHEMA
外部テーブルの作成は?
外部テーブルはすでに作成済みの Amazon Athena のテーブルを参照できるため、作成する必要はありません。
外部スキーマ名の一覧を取得できます。
cmdb=# select * FROM pg_external_schema pe join pg_namespace pn ON pe.esoid = pn.oid;
esoid | eskind | esdbname | esoptions | nspname | nspowner | nspacl
--------+--------+------------+-------------------------------------------------------------------------+----------+----------+--------
707623 | 1 | spectrumdb | {"IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"} | spectrum | 100 |
(1 row)
ステップ3で作成した sales_athena テーブルのテーブル定義を確認します。
cmdb=# \x
Expanded display is on.
cmdb=# select * from SVV_EXTERNAL_TABLES;
-[ RECORD 1 ]-----+--------------------------------------------------------------------------------------
schemaname | athena_schema
tablename | sales
location | s3://awssampledb/tickit/spectrum/sales
input_format | org.apache.hadoop.mapred.TextInputFormat
output_format | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
serialization_lib | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
serde_parameters | {"field.delim":"\t","serialization.format":"\t"}
compressed | 0
parameters | {"EXTERNAL":"TRUE","transient_lastDdlTime":"1492843944"}
-[ RECORD 2 ]-----+--------------------------------------------------------------------------------------
schemaname | athena_schema
tablename | sales_athena
location | s3://awssampledb/tickit/spectrum/sales
input_format | org.apache.hadoop.mapred.TextInputFormat
output_format | org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
serialization_lib | org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
serde_parameters | {"field.delim":"\t","serialization.format":"\t"}
compressed | 0
parameters | {"EXTERNAL":"TRUE","has_encrypted_data":"false","transient_lastDdlTime":"1492964468"}
ステップ4:Amazon S3でデータを照会する
SPECTRUM.SALESテーブルの行数を取得します。
Timing is on.
cmdb=# select count(*) from spectrum.sales_athena;
count
--------
172462
(1 row)
Time: 1211.174 ms
制限事項
執筆時点では、Amazon Redshift Spectrum がサポートしていない SerDeを利用した Amazon Athena のテーブルはエラーとなります。例えば、jsonのSerDe(org.openx.data.jsonserde.JsonSerDe)などを実行すると以下のようなエラーが出力されます。
cmawsteamdb=# select * from athena_default.json_athena;
ERROR: External Catalog Error: Unsupported file format. org.apache.hadoop.mapred.TextInputFormat org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat org.openx.data.jsonserde.JsonSerDe
Time: 326.571 ms
まとめ
想定通り、Amazon Redshift Spectrumは、Amazon Athena のデータベース名に対応する外部スキーマを登録することで、Amazon Athena のテーブルに対してクエリーを実行できることを確認できました。すでに Amazon Redshift と Amazon Athenaをお使いの環境であれば、外部スキーマを定義するだけでこれらのテーブル間の結合や条件フィルタリング、集約が可能になりますのでご活用ください。