[AWS] Redshift Spectrumチュートリアル

# はじめに
技術選定にてRedshiftの使用を検討していたのですが、デメリットとして同時接続数に比例してクエリの実行時間が増加していくという問題がありました。

- [Redshiftの課題 - Redshift Spectrumを使ってみた話](https://www.slideshare.net/YoshikiKouno/redshift-spectrum-76121332/6)


そこでいろいろなサービスの技術調査をしたのですが、**Redshift Spectrum**（以下、Spectrum）のAWS公式のチュートリアルをやったので、そのときの内容をまとめてみました。

間違いがあれば気軽にコメントくださいm(_ _)m

- [Amazon Redshift（高速でシンプルなデータウェアハウス）｜AWS](https://aws.amazon.com/jp/redshift/)
- [Amazon Redshift Spectrum を使用して外部データにクエリを実行する - Amazon Redshift](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c-using-spectrum.html?shortFooter=true)
    - ざっくりまとめると、SpectrumはS3のファイルに直接クエリを実行できるRedshiftの機能。

# 1. Spectrum用のロールを作成する。
```bash:terminal
# 新規ポリシー「SpectrumPolicy」を作成する
$ aws iam create-policy \
    --policy-name SpectrumPolicy \
    --policy-document file://path/to/SpectrumPolicy.json

# ポリシー「SpectrumPolicy」を既存ロール「RedshiftRole」にアタッチする。
$ attach-role-policy \
    --role-name RedshiftRole \
    --policy-arn arn:aws:iam::[acount_id]:policy/PolicyName
```

```json:SpectrumPolicy.json
{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Effect":"Allow",
         "Action":[
            "s3:GetBucketLocation",
            "s3:GetObject",
            "s3:ListMultipartUploadParts",
            "s3:ListBucket",
            "s3:ListBucketMultipartUploads"
         ],
         "Resource":[
            "arn:aws:s3:::bucketname", 
            "arn:aws:s3:::bucketname/folder1/folder2/*"
         ]
      },
      {
         "Effect":"Allow",
         "Action":[
            "glue:CreateDatabase",
            "glue:DeleteDatabase",
            "glue:GetDatabase",
            "glue:GetDatabases",
            "glue:UpdateDatabase",
            "glue:CreateTable",
            "glue:DeleteTable",
            "glue:BatchDeleteTable",
            "glue:UpdateTable",
            "glue:GetTable",
            "glue:GetTables",
            "glue:BatchCreatePartition",
            "glue:CreatePartition",
            "glue:DeletePartition",
            "glue:BatchDeletePartition",
            "glue:UpdatePartition",
            "glue:GetPartition",
            "glue:GetPartitions",
            "glue:BatchGetPartition"
         ],
         "Resource":[
            "*"
         ]
      }
   ]
}
```
- [Amazon Redshift Spectrum用のIAMポリシー - Amazon Redshift](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c-spectrum-iam-policies.html#spectrum-iam-policies-minimum-permissions)


# 2. 外部スキーマを作成する
```sql
create external schema spectrum
from data catalog
database 'spectrumdb'
iam_role 'arn of your iam role'
create external database if not exists;
```
- [CREATE EXTERNAL SCHEMA - Amazon Redshift](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_CREATE_EXTERNAL_SCHEMA.html?shortFooter=true)

# 3. 外部テーブルを作成する
```sql
create external table spectrum.sales (
    salesid     integer,
    listid      integer,
    sellerid    integer,
    buyerid     integer,
    eventid     integer,
    dateid      smallint,
    qtysold     smallint,
    pricepaid   decimal(8,2),
    commission  decimal(8,2),
    saletime    timestamp
)
row format              delimited
fields terminated by    '\t'
stored as               textfile
location                's3://awssampledbuswest2/tickit/spectrum/sales/'
table properties ('numRows'='172000');
```
- [CREATE EXTERNAL TABLE - Amazon Redshift](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_CREATE_EXTERNAL_TABLE.html?shortFooter=true)


# 4. S3にクエリを実行する
## パターン1. 外部テーブル"sales"にクエリを実行する
```sql
select count(*)
from spectrum.sales;
```

## パターン2. 外部テーブル"sales"とローカルテーブル"event"を結合するクエリを実行する
```sql
select
    top 10 spectrum.sales.eventid,
    sum(spectrum.sales.pricepaid)
from
    spectrum.sales,
    event
where
    spectrum.sales.eventid = event.eventid
    and spectrum.sales.pricepaid > 30
group by
    spectrum.sales.eventid
order by
    2 desc
;
```
- [Amazon Redshift Spectrum を使用して外部データにクエリを実行する - Amazon Redshift](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c-using-spectrum.html?shortFooter=true)


# FYI
- 5 [$/TB] で課金される。（Athenaと同じ）


# References
- [Amazon Redshift Spectrum の開始方法 - Amazon Redshift](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c-getting-started-using-spectrum.html?shortFooter=true)
- [Amazon Redshift Spectrum 10 のベストプラクティス | Amazon Web Services ブログ](https://aws.amazon.com/jp/blogs/news/10-best-practices-for-amazon-redshift-spectrum/)
- [Amazon Redshift Spectrum クエリパフォーマンスの向上 - Amazon Redshift](https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c-spectrum-external-performance.html)
- [AWS Solutions Architect ブログ: Amazon Redshiftのパフォーマンスチューニングテクニック Top 10 ](http://aws.typepad.com/sajp/2015/12/top-10-performance-tuning-techniques-for-amazon-redshift.html)

Posted on 2018-10-11