Redshift Spectrumを利用してみる - goodbyegangsterのブログ

Glueのデータカタログを使用して、Redshift Spectrumを利用する方法について、まとめておきます。

公式のマニュアルはこちら。

Amazon Redshift Spectrum を使用して外部データにクエリを実行する - Amazon Redshift

利用にあたり前提条件

Redshift Clusterのバージョン 1.0.1294 以降
Redshift ClusterとS3は、同一リージョンに存在
Redshiftの設定で、 拡張されたVPCのルーティング は利用不可
Spectrumでアクセスするテーブル(外部テーブル)は、読み取り専用
外部テーブルに対して、ユーザのアクセス権を制御することはできない
- 外部スキーマを利用して、制御することになる
クエリを実行するには、クエリ実行ユーザが一時テーブル作成権限を持っている必要あり

Spectrumで利用するIAMロールを作成

Spectrumでは、以下の他AWSサービスへのアクセスが必要になります

データカタログ
- Glue、もしくはAthenaで作成されているもの
S3
- 検索対象のS3バケットへの読み取り専用アクセス

そのため、上記アクセスするためのIAMロールを作成が必要があります。必要となるのは、以下の権限です。

AmazonS3ReadOnlyAcces
(Glueデータカタログを利用する場合) AWSGlueConsoleFullAccess
(Athenaデータカタログを利用する場合) AmazonAthenaFullAccess

AmazonS3ReadOnlyAcces では、全てのS3バケットへの参照権限を与えてしまうため、対象バケットを絞りたい場合、別途IAM Policyを作成して対応しましょう。公式のページにて、最小限のIAMポリシーについて紹介されています。こちらです。

ステップ 1. Amazon Redshift 用の IAM ロールを作成する - Amazon Redshift

作成したIAMロールを、利用する事になるRedshift Clusterに付与してあげます。

外部スキーマの作成

外部スキーマ(Spectrumでは、外部DBと同義)として、Athenaのデータカタログ、もしくはHiveメタストアを指定します。

以下のSQLでは、Athena内にある sample というデータカタログを使用して、 external_sample_schema という外部スキーマを定義しています。IAM_ROLEでは、上記で作成してRedshiftに付与したIAMロールを指定してください。

test=# CREATE EXTERNAL SCHEMA external_sample_schema FROM data catalog
  DATABASE 'sample'
  IAM_ROLE 'arn:aws:iam::XXXXXXXXXXXXX:role/RoleForRedshiftSpectrum'
  REGION 'ap-northeast-1';
Schema external_sample_schema created

確認コマンドです。 svv_external_schemas を確認します。

test=# select schemaname, databasename from svv_external_schemas;
       schemaname       | databasename
------------------------+--------------
 external_sample_schema | sample
(1 row)

公式のマニュアルはこちらです。

CREATE EXTERNAL SCHEMA - Amazon Redshift

外部テーブルへアクセスするための権限

Redshift内DBユーザが外部テーブルにアクセスするためには、以下のDB権限を付与する必要があります。

外部スキーマへのアクセス権限
RedshiftのDBに一時テーブルを作成するアクセス権限

どちらもgrant文で、必要となるDBユーザに権限を付与してあげてください。今回の記事では、すべてのSQLを、Redshiftマスターユーザで実行してしまっているので、この点をあまり意識していません。

外部テーブルへのアクセス

アクセスするにあたっては、上記の外部スキーマで指定したDB(データカタログ)内にテーブル定義が存在する場合、それを利用できます。すごい。GlueやAthenaのデータカタログを利用していない場合、別途外部テーブルを定義してあげる必要があります。

たとえば、今回はS3上にある以下csvファイルを、既にGlueデータカタログに登録しているため、新たな外部テーブルの定義なしに検索することができます。

voiceroid_id,name,lover,year,month,day
1,結月ゆかり,弦巻マキ,2011,12,22
2,弦巻マキ,結月ゆかり,2010,11,12
3,東北ずんこ,ずんだ,2012,9,28
4,東北きりたん,東北ずんこ,2016,10,27
5,結星あかり,結月ゆかり,2017,12,22
6,琴葉茜,琴葉葵,2014,4,25
7,琴葉葵,琴葉茜,2014,4,25

以下が、Glueデータカタログ画面です。 voiceloid という名前のテーブルが作成済みです。

f:id:goodbyegangster:20180830111627p:plain

以下の通り検索してみると、ちゃんと結果が表示されます。

test=# select name from external_sample_schema.voiceloid;
     name
--------------
 結月ゆかり
 弦巻マキ
 東北ずんこ
 東北きりたん
 結星あかり
 琴葉茜
 琴葉葵
(7 rows)

SVV_EXTERNAL_TABLES で、定義されている外部テーブルを確認できます。

test=# select schemaname, tablename, location from SVV_EXTERNAL_TABLES;
       schemaname       | tablename |            location
------------------------+-----------+---------------------------------
 external_sample_schema | voiceloid | s3://test-XXXXXXX-dev/voiceloid/

SVV_EXTERNAL_COLUMNS で、カラム定義を確認できます。

test=# select * from SVV_EXTERNAL_COLUMNS where tablename = 'voiceloid';
       schemaname       | tablename |  columnname  | external_type | columnnum | part_key
------------------------+-----------+--------------+---------------+-----------+----------
 external_sample_schema | voiceloid | voiceroid_id | bigint        |         1 |        0
 external_sample_schema | voiceloid | name         | string        |         2 |        0
 external_sample_schema | voiceloid | lover        | string        |         3 |        0
 external_sample_schema | voiceloid | year         | bigint        |         4 |        0
 external_sample_schema | voiceloid | month        | bigint        |         5 |        0
 external_sample_schema | voiceloid | day          | bigint        |         6 |        0
(6 rows)

ほんと、サクッと利用できます。

検索処理の高速化

高速化するために、以下のtipsが紹介されています。

Amazon Redshift Spectrum クエリパフォーマンスの向上 - Amazon Redshift

大きなファイルを小さいファイルに分割
64MBより大きいファイルサイズが推奨
ファイルは、すべてほぼ同じサイズとする
時系列なデータは、パーティション化する
Parquet 形式のデータファイルを使用
外部テーブル定義時の、 numRowsパラメータ を設定する
- Redshiftは、外部テーブルの統計情報を更新してくれないので、テーブル行数を明示的に指定しておく必要があり
Redshift Spectrum クエリレイヤー(S3からデータを取得するプロセス)で、可能な限り処理を実行させる
- 以下の処理は、Redshift Spectrum クエリレイヤーで処理してくれるらしい
  - GROUP BY
  - 比較条件とパターンマッチング条件 such as LIKE
  - 集計関数 such as COUNT, SUM, AVG, MIN, and MAX
  - 文字列関数
- 以下の処理は、処理しれくれないらしい
  - DISTINCT
  - ORDER BY