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

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

[Amazon Redshift Spectrum を使用した外部データのクエリ実行
](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c-using-spectrum.html)

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 ロールをクラスターに関連付ける](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c-getting-started-using-spectrum-add-role.html)

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

```sql: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 文字保存することができます

[クエリエディタの考慮事項](https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/query-editor.html)

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

## SQL クライアントツールを使用して Amazon Redshift クラスターに接続する
クエリエディタではSQLの実行に様々な制約があるため、SQLクライアントツールの利用が必要となることがあります。AWSのドキュメントでは[SQL Workbench/J](https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/connecting-using-workbench.html)の利用を紹介しています。

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

```sh
brew install --cask sqlworkbenchj
```

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

[Amazon Redshift JDBC ドライバーのダウンロード](https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/configure-jdbc-connection.html#download-jdbc-driver)を参考にJDBCをダウンロードします。

準備はこれでできたのでSQLワークベンチを起動します。接続を以下の図のようにします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/290902/7a8e4282-bd49-dbf8-c242-e0073998b2c7.png)

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

これで接続が可能となるのですが、クラスターの設定でパブリックアクセスを可能にする必要があります（参考：[プライベート Amazon Redshift クラスターをパブリックアクセス可能にするにはどうすればよいですか?](https://aws.amazon.com/jp/premiumsupport/knowledge-center/redshift-cluster-private-public/)
またセキュリティグループでもアクセスできるように設定しておくことが必要となります。


## INSERT

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

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

```sql
INSERT INTO external_schema.table_name
SELECT * FROM hoge_table_name
```

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


## CTAS
CTAS文も実行可能ですが、Athenaと構文が違うので記載しておきます。
参考：[CREATE EXTERNAL TABLE](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_CREATE_EXTERNAL_TABLE.html)

```sql: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' [, ...] ) ]
```

```sql
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
※一例です
費用は変更完了までは古いインスタンスものが発生する。

### クラスターは停止しておけば費用は発生しない
発生するのはバックアップのみ