Redshift Spectrumを利用してみる
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
という名前のテーブルが作成済みです。
以下の通り検索してみると、ちゃんと結果が表示されます。
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
- 以下の処理は、Redshift Spectrum クエリレイヤーで処理してくれるらしい