AthenaからRedshift Spectrumへの移行とわかったこと

Amazon Redshift Spectrumとは

Amazon Redshift Spectrum を使用すると、効率的にクエリを実行し、Amazon Redshift テーブルにデータをロードすることなく、Amazon S3 のファイルから構造化または半構造化されたデータを取得することができます。

Amazon Redshift Spectrum を使用した外部データのクエリ実行

Redshiftを利用する場合はRedshiftにデータをロードする必要があったがRedshift Spectrumを利用することでS3にあるファイルをそのまま利用できるようになります。

Redshift Spectrumの利用を検討した理由

Amazon Athenaではクエリに時間がかかりすぎる、またはリソースが足りなくて実行できないクエリを実行するためにRedshift Spectrumの利用を検討し始めた。

開始方法

1. クラスターの作成
2. Amazon Redshift 用の IAM ロールを作成する
3. IAM ロールをクラスターに関連付ける
4. 外部スキーマと外部テーブルを作成する

のステップでSQLが叩けるようになります。

クラスターの作成

Redshiftダッシュボードからクラスターの作成を選択します
作成画面で最低でも以下を設定します。

• 名称
• サイズ
• データベース名（オプション）
• ポート番号（オプション）
• マスターユーザー名
• マスターユーザーのパスワード

次の工程で作成するIAMロールは先に作成しておいて、ここで付与することも可能です。

Amazon Redshift 用の IAM ロールを作成する

Redshiftでは作成したクラスターにIAMロールを付与して、クラスターの権限管理を行うのですが、Spectrumでは通常のRedshiftの操作に加えてS3の利用が加わるのでS3の権限を設定します。
単純に読み取るだけであるならAmazonS3ReadOnlyAccessで十分ですが、CTAS文の実行やINSERTなども行うのであれば書き込み権限も必要となります。
またAWS Glue データカタログを利用する場合はAWSGlueConsoleFullAccessをAthena データカタログを使用する場合はAmazonAthenaFullAccessも付与します。
今回Athenaのデータカタログを利用するためAmazonAthenaFullAccessを付与しました。

IAM ロールをクラスターに関連付ける

先程作成したIAMロールをクラスターに付与します。これはコンソールからポチポチやればすぐに終わります。

AWS マネジメントコンソールにサインインし、Amazon Redshift コンソール (https://console.aws.amazon.com/redshift/) を開きます。
ナビゲーションメニューで [CLUSTERS] を選択し、更新するクラスター名を選択します。
[アクション] で、[IAM ロールの管理] を選択します。[IAM ロール] のページが表示されます。
[Enter ARN (ARN の入力) ] を選択し、ARN または IAM ロール を入力するか、リストから IAM ロールを選択します。その後、[Add IAM role (IAM ロールの追加)] を選択して、[Attached IAM roles (アタッチされている IAM ロール)] のリストに追加します。
[完了] を選択し、IAM ロールをクラスターに関連付けます。これで、クラスターが変更され、変更が完了します。

ステップ 2: IAM ロールをクラスターに関連付ける

外部スキーマと外部テーブルを作成する

今回はAthenaのデータカタログを参照するスキーマを作成するためスキーマ作成までしたら外部テーブルは作成しません。

create_external_schema.sql

create external schema athena_schema from data catalog -- 任意のスキーマ名を指定
database 'sampledb'  -- Athenaのデータベース名を指定
iam_role 'arn:aws:iam::123456789012:role/MySpectrumRole'  -- ステップ２で作成したIAMロールのarnを指定
region 'us-east-2'; --  Athena データカタログが置かれている AWS リージョンを指定

外部スキーマを作成するとAtheaで利用していたデータカタログが利用できるようになり、そのままSELECT文などの利用が可能となります。

クエリエディタの利用

まずクエリを実行したいのであればAWSコンソールからクエリエディタを選択します。
先程設定したユーザー名・パスワードを利用するとログインできて、そのままSQLの実行画面に移ります。ただクエリエディタには以下の注意点があります。

同時に最大 50 名のユーザーがクエリエディタを使用してクラスターに接続することができます。
クラスターに接続するユーザーの最大数には、クエリエディタを介して接続するユーザーが含まれます。
同時に最大 50 のワークロード管理 (WLM) クエリスロットをアクティブにできます。クエリスロットの詳細については、「ワークロード管理の実装」を参照してください。
クエリエディタは、10 分以内に完了する短いクエリのみを実行します。
クエリ結果セットはページごとに 100 行で分割されています。
拡張された VPC のルーティングではクエリエディタを使用できません。詳細については、「Amazon Redshift 拡張された VPC のルーティング」を参照してください。
クエリエディタでトランザクションを使用することはできません。トランザクションの詳細については、https://docs.aws.amazon.com/redshift/latest/dg/r_BEGIN.html の「BEGINAmazon Redshift Database Developer Guide」を参照してください。
クエリは最大 3,000 文字保存することができます

クエリエディタの考慮事項

この中でも自分はクエリエディタは、10 分以内に完了する短いクエリのみを実行します。というものに引っかかりました。大きなクエリを実行してみたところ、10分を過ぎたと過ぎたところでクエリがキャンセルされてしまい、この情報に行き着きました。

SQL クライアントツールを使用して Amazon Redshift クラスターに接続する

クエリエディタではSQLの実行に様々な制約があるため、SQLクライアントツールの利用が必要となることがあります。AWSのドキュメントではSQL Workbench/Jの利用を紹介しています。

SQL Workbench/Jのインストール

AWSのドキュメントでも説明されていますが自分はbrewコマンドでインストールしました。

brew install --cask sqlworkbenchj

でインストールします。https://formulae.brew.sh/cask/sqlworkbenchj
Java Runtime Environmentの環境も必要なので用意します。参考：https://www.java.com

Amazon Redshift JDBC ドライバーのダウンロードを参考にJDBCをダウンロードします。

準備はこれでできたのでSQLワークベンチを起動します。接続を以下の図のようにします。

ドライバーは先程ダウンロードしたJDBCドライバーを設定。
RedShiftコンソールからJDBCのURLを取得し設定。
ユーザー名・パスワードはクラスター作成時のものを設定。
CTAS文を利用する場合はAutocommitをチェックします。

これで接続が可能となるのですが、クラスターの設定でパブリックアクセスを可能にする必要があります（参考：プライベート Amazon Redshift クラスターをパブリックアクセス可能にするにはどうすればよいですか?
またセキュリティグループでもアクセスできるように設定しておくことが必要となります。

INSERT

参考：INSERT (外部テーブル)
AthenaでできないことでRedshift Spectrumでできることの一つにINSERTができるということが挙げられる。
Athenaは基本読み取りで、INSERTやUPDATEはできず、データの加工の手段とするのであればCTAS（Create Table As Select）を利用する必要があった。ただCTASを利用するたびにテーブルが増え、重複データも増えることがあり、不便に感じる側面もありました。

それがRedshift SpectrumではINSERTが可能となっています。

INSERT INTO external_schema.table_name
SELECT * FROM hoge_table_name

上記のようなSQLでINSERTが可能です。

CTAS

CTAS文も実行可能ですが、Athenaと構文が違うので記載しておきます。
参考：CREATE EXTERNAL TABLE

syntax.sql

CREATE EXTERNAL TABLE
external_schema.table_name  
(column_name data_type [, …] )
[ PARTITIONED BY (col_name data_type [, … ] )] 
[ { ROW FORMAT DELIMITED row_format |
  ROW FORMAT SERDE 'serde_name' 
  [ WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ] } ]
STORED AS file_format
LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' }
[ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ]

CREATE EXTERNAL TABLE
    external_schema.hoge_table
STORED AS PARQUET
LOCATION 's3://example-backet/hoge/'
 AS
SELECT * FEOM external_schema.table_name

メモ

以下気づいたことのメモです。

クラスタサイズの変更時間

クラスタサイズ変更に30分程度かかった
dc2.large → dc2.8xlarge
※一例です
費用は変更完了までは古いインスタンスものが発生する。

クラスターは停止しておけば費用は発生しない

発生するのはバックアップのみ