goodbyegangsterのブログ

備忘録的な

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 という名前のテーブルが作成済みです。

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