AlteryxでRedshiftと接続

# はじめに

ここのところ、Alteryxで色々なデータソースとつないでみています。

https://qiita.com/a_b_/items/4c58e6bf7f4761273b36

https://qiita.com/a_b_/items/7c9243b5aa9ab6c7bbc2

https://qiita.com/a_b_/items/83e900dcd4dd89a270b7

https://qiita.com/a_b_/items/be55bf75a34719d6ff5d

今回はRedshiftです。
Redshiftはビッグデータを扱うため、データを入れる方法としてInsert以外に、”S3においてあるファイルから読み込むCOPY”もありますが、それも対応していました。

# Redshift　

## 環境

### VPC

RDSの時に作ったものを使います。

https://qiita.com/a_b_/items/83e900dcd4dd89a270b7#vpc

### セキュリティグループ

Redshift用にインバウンドルールを許可します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/b0ca000c-9725-eb06-ea6c-d01db96e0d30.png)


### クラスターサブネットグループ

https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/managing-cluster-subnet-group-console.html#create-cluster-subnet-group

VPCを選んで、すべてのサブネットを追加するだけでした。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/bfec41b1-8c77-ae9e-5f1a-86ee462133ec.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/e2574c3a-8e1a-33e4-d548-11f3841e7ab4.png)

### クラスター作成
なるべく安価になるように作っていきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/de949acb-a15b-7d25-4ed2-050566faedaa.png)

追加設定でネットワークを指定します。
VPCとセキュリティグループ、先ほど作成したサブネットグループを指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/452ec3a7-4187-69e9-6428-fbabf345536b.png)
データベースはデフォルトのままで作ってもらいます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/f3c11c15-5ae1-0796-45e7-d47e5dd009b7.png)

これでクラスターを作成します。
起動後に、クラスターにチェックを入れ、アクションから<B>パブリックアクセス可能な設定を変更</B>を選択し有効化します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/0d04830f-f19a-9e17-bc64-bc9a2fe31aa8.png)
Elastic IPは未指定で問題なかったです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/c239f662-4440-f46d-6d8f-bd1f6b44d157.png)


### ODBCドライバー
ドライバーは以下のページを参考にインストールできます。

https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/configure-odbc-connection.html#install-odbc-driver-windows

Redshiftのエンドポイントは、一般的な情報の右に記載されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/392f41d9-a9bd-aaa0-70d1-5843d1a4324b.png)
一番上のエンドポイントをコピーしましたが、末尾にポート番号とデータベース名がありますので、それら文字列をカットして使います。

設定値は以下の様になりました。Serverには、先ほど取得し文字列カットしたエンドポイントを設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/dc0da434-2df6-dc94-e71a-787b75c81f12.png)

### テーブル作成
Redshift query editorから、テーブルを作成します。

```sql
CREATE TABLE "public"."hoshiiro2_cast"(
    話数  VARCHAR(100) ,
    パート VARCHAR(100) ,
    ポプ子 VARCHAR(100) ,
    ピピ美 VARCHAR(100) 
);
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/7377cf6c-b348-f05f-261a-12eaade57752.png)
以下の様に作成されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/ff79a520-f3d5-6933-0fb3-885b228592b0.png)

データも入れていきます。

```sql
INSERT INTO "public"."hoshiiro2_cast" VALUES
('第1話','前半','平野綾','茅原実里'),
('第1話','後半','井上和彦','堀川りょう'),
('第2話','前半','朴璐美','釘宮理恵'),
('第2話','後半','檜山修之','森川智之'),
('第3話','前半','大谷育江','犬山イヌコ'),
('第3話','後半','榎木淳弥','内田雄馬'),
('第4話','前半','潘めぐみ','伊瀬茉莉也'),
('第4話','後半','石丸博也','水島裕')
;
```
Redshift関連の準備はこれで完了です。

## 入力

Alteryxでデータ入力ツールをドラッグして、データソースにRedshiftを指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/1816c179-dfb9-2d0f-cf43-2541a17d72ee.png)
作成したODBCの設定を指定して、ユーザー名/パスワードを入れます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/6fdbc226-9441-16e4-f816-ee8b661efaff.png)
エディタ画面でクエリを作成して、OKを押します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/a1b9a537-1868-d735-bd6b-11889acf0f3d.png)
閲覧ツールをつなげて実行すると、テーブルのデータが取得できました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/3cc6aa07-0b02-1a56-5f8d-6f525db24dca.png)


## 出力(ODBC)
以下のCSVをAlteryxで取り込みます。
```
話数,パート,ポプ子,ピピ美
第5話,前半,皆川純子,甲斐田ゆき
第5話,後半,豊永利行,浅沼晋太郎
```
データ出力ツールと繋げて、データソースにRedshiftのODBCを指定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/bf3fab7d-ffc4-7f7e-5e7c-2a7a865ecc1b.png)
入力時と同じように設定します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/f33514a1-eb3c-ca38-d9fa-d9c32940c50d.png)
テーブル名に、作成済みのテーブル名を指定しました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/8641794a-2e91-0afd-4bb4-c7f35025ab2f.png)

RDBのPasSと同じように、出力オプションが色々選べます。
「既存のものを付加する」から試してみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/f444cab3-5c91-84a9-4d75-3763e9c9015b.png)
テーブルにデータが追加されました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/305a96e9-2939-c424-d511-8272f492aeb1.png)

次は「データを削除して付加する」です。
前回と同様、既に入っていたものは無くなって、取り込みデータが入りました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/c14ffc9f-e13a-17f1-ffee-1b7555837304.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/bfaf32b2-3340-b0a4-6b0b-c7bd6f6150ef.png)

「テーブルを上書きする(ドロップ)」も前回と同様、テーブル定義が変わっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/1b6400e5-e21f-b946-cab4-9666c5927915.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/25e35047-18ae-32db-8166-d73846e35eb9.png)

「新しいテーブルを作成する」も同様に、エラーになりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/53b583ea-7455-e144-8c22-12121e6f46e3.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/5a32168d-9d06-7f11-db43-ba72b6908103.png)

## 出力(バルク)

COPYに該当する出力方法もあるようなので試してみました。参考にしたページは以下になります。

https://dev.classmethod.jp/articles/data-load-to-redshift-using-alteryx/

参考のページによると、内部でS3にアップして、そのファイルに対してCOPYを行っているようなので、以下のCloudFormationのファイルでS3バケットと専用のユーザーを作成します。

```yaml
AWSTemplateFormatVersion: 2010-09-09

Resources:
  TargetBucket:
    Type: AWS::S3::Bucket

  AlteryxUser:
    Type: AWS::IAM::User
    Properties:
      Policies:
       -  PolicyName: S3Access
          PolicyDocument:
            Version: 2012-10-17
            Statement:
             -  Effect: Allow
                Action: 
                 - s3:ListBucket
                Resource:
                 - !Sub arn:aws:s3:::${TargetBucket}
             -  Effect: Allow
                Action: 
                 - s3:*Object
                Resource:
                 - !Sub arn:aws:s3:::${TargetBucket}/*
```
その後、作成されたユーザーのアクセスキーを作成してダウンロードしておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/4862a459-c5bd-aeaa-e67c-4e245cdbcb2b.png)

Redshiftのデータも、最初の状態に戻しておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/062e173e-3857-1a42-2bf7-5166c1619dae.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/dfa15e30-dcf8-9896-8926-1f039c97cd6e.png)


Alteryxに戻り、先ほどと同様出力ツールをドロップし、データソースから今度はRedshiftの`バルク`を選択します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/fe0aa30d-513f-bba1-456f-06a47e3d808c.png)

出てきた画面に情報を入力していきます。ユーザー名とパスワードは未指定でOKでした。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/c795f929-2a05-57a6-c7be-4b6e3ba059b8.png)
テーブル名も同様に指定しておきます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/3285232a-3771-6159-beeb-2cbb4794e482.png)

出力オプションはODBC経由のものとは異なり`更新`で始まるオプションがありません。
「既存のものを付加する」から試してみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/fd38945c-64ea-b06a-23e8-ac7176214d74.png)

S3では途中でファイルが生成されており、完了後は削除されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/d140b34b-2990-42e4-9010-d3a4bd196d2c.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/4bb25e2d-52c5-a692-a0f4-4656e9564daa.png)

取り込まれたデータが挿入されています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/ec2abfe7-9683-85a8-e9ac-1e8d464dc943.png)

次に「データを削除して付加する」を試してみます。
ODBCの出力と同様に、取り込みデータのみになりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/aac4dbd1-a9df-bd02-33c1-4bb7ccedde6c.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/32d74211-3562-2f1b-1e10-820866f13a19.png)

「テーブルを上書きする(ドロップ)」も同様にテーブル定義が変わっています。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/d1f8ac18-6f7f-abcc-8032-ed0ad90a94dd.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/28425416-1a55-ba20-0f91-27259ffe4261.png)

「新しいテーブルを作成する」の結果も、同名のテーブルが存在するためエラーになりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/6db16e79-3748-f0d1-a0f4-9b67e7c6062c.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/553680/deef67b9-71e2-3b44-6e33-22d5cacea3c3.png)

# おわりに
今回はAlteryxとAmazon Redshiftをつなげてみました。
Redshiftは大量データを入れる際にCOPYを使って入れていましたが、「TRUNCATEして、都度S3にアクセスして、ファイルを置いて…」と操作が面倒でした。
Alteryxを使えば、そのあたりの手間がだいぶ軽減できるかと思います。