Glueの使い方的な㉕(S3からRedshiftにロード_実行編)

前編でS3からRedshiftにロードする際の準備を行いました。
ここでは、Glueを使ってS3からRedshiftへのロードを行います。

作成される構成は以下です。

![glue_redshift-Final.png](https://qiita-image-store.s3.amazonaws.com/0/27932/c5b000bf-48e0-fc19-ce9e-e97b91aa8798.png)

## 前提

##### 前提の構成は以下です。

![glue_redshift-Page-4.png](https://qiita-image-store.s3.amazonaws.com/0/27932/a4fe2279-6591-5796-8d1b-085c9a60791d.png)

### VPC、セキュリティグループ、ルートテーブル、Redshiftを作成しておく

"[Glueの使い方的な㉔(S3からRedshiftにロード_準備編)](https://qiita.com/pioho07/items/05c912333e88788a1391)"(以後㉔と書きます)を実施した後の作業とします。未実施の場合は先に㉔を実施しておいてください。

### Redshift関連情報

クラスタ名：se2-gluetest-dwh
データベース：db
ユーザー：admin
パスワードxxxx(任意)

### "[Glueの使い方的な①(GUIでジョブ実行)](https://qiita.com/pioho07/items/c9ce1d0677777f974ffe)"(以後①とだけ書きます)のIAMロール、クローラー、入力データを使います。まだの場合は作っておいてください。

・IAMロール:test-glue
・クローラー：se2_in0
・入力データ：19件のcsvファイル

## 今回作るリソース

ジョブ：se2_job13
クローラー：se2_out13

# 全体の流れ

* Glueの"接続"の作成とテスト
* Glueの"ジョブ"の作成と実行
* (補足)Redshiftのテーブルへのクローラー作成

# Glueの"接続"作成とテスト

##### ここでの通信フローと構成は以下です。

![glue_redshift-Page-5 (1).png](https://qiita-image-store.s3.amazonaws.com/0/27932/887cd471-3977-8d52-6db7-e4d11450dd76.png)

GlueからJDBCへの接続のための定義である"接続"を作成します。
Glueの画面を開き、左側メニューの"Connection"をクリックし、[Add connection]をクリックする

![スクリーンショット 0030-09-09 16.06.48.png](https://qiita-image-store.s3.amazonaws.com/0/27932/6906478e-272b-ea3a-2692-89212f756da9.png)

以下を入力し、[Next]をクリックする
Connection Name："se2-connect-dwh1"(任意)
Connect Type："Amazon Redshift"

![スクリーンショット 0030-09-09 16.07.11.png](https://qiita-image-store.s3.amazonaws.com/0/27932/bca3326a-6675-3ae7-7679-414f80d9338f.png)

以下を入力し、[Next]をクリックする。次のページで[Finish]をクリックする
Cluster："se2-gluetest-dwh"
Database name："db"
Username："admin"
Password："xxxxx"(設定してあるパスワード)

![スクリーンショット 0030-09-09 16.07.29.png](https://qiita-image-store.s3.amazonaws.com/0/27932/b262a118-9e1c-2109-195e-1ccbfc343c21.png)

connection名をクリックし、[Edit]をクリックすることで詳細な編集ができる

![スクリーンショット 0030-09-09 16.07.45.png](https://qiita-image-store.s3.amazonaws.com/0/27932/14471096-faf0-d87d-58b2-c3446ef92af6.png)

connection名をクリックし、[Edit]をクリックし[Next]をクリックした画面にいくと、Redshiftの情報から自動で入力されたVPCやセキュリティグループの情報が確認できる。何かあればここで確認や修正ができる
今回はセキュリティグループを㉔で作成した自己参照セキュリティグループの"se2-glue-jikosansho-sg"に変更する。Passwordは空の状態なので入力し、[Next]をクリックし、次のページで[Finish]をクリックする

![スクリーンショット 0030-09-09 16.23.36.png](https://qiita-image-store.s3.amazonaws.com/0/27932/b35881ea-df53-534a-871c-c714808f93e3.png)

#### 接続テスト

Connectionの画面で、作成したConnectionの"se2-connect-dwh1"にチェックを入れ、[Add connection]をクリックする

![スクリーンショット 0030-09-09 16.21.28.png](https://qiita-image-store.s3.amazonaws.com/0/27932/7dfc1312-d87a-00c3-2c94-75e18db8db01.png)

IAMロールに"test-glue"を選択し、[Test connection]をクリックする。IAMロールはクローラーやジョブを実行しているものと同じ内容で構わない

![スクリーンショット 0030-09-09 16.21.38.png](https://qiita-image-store.s3.amazonaws.com/0/27932/16615cca-5ec0-b8c1-523c-a6ec5a05131a.png)

数分後に以下の画面になればTest Connectionは完了

![スクリーンショット 0030-09-09 16.26.22.png](https://qiita-image-store.s3.amazonaws.com/0/27932/ac0407fe-22de-3572-5e77-8285d2d8dcdc.png)

##### 自己参照セキュリティグループを使ったフローの構成図

自己参照セキュリティグループを使ったより正確なGlueの通信フローは以下のようになります。
Glue自体はパブリックなAWSサービスで、Glueに自己参照セキュリティグループをアタッチすることでVPC内のリソース(RDS,Redshift)にアクセスします。
少しわかりずらいですが、GlueのENIがVPC内に出現し、そのENIにセキュリティグループがアタッチされるようなイメージです。

![glue_redshift-Page-77.png](https://qiita-image-store.s3.amazonaws.com/0/27932/4db9af05-962b-7644-5276-c55f0b93f128.png)

# Glueの"ジョブ"の作成と実行

##### ここでの通信フローと構成は以下です。

![glue_redshift-Page-6.png](https://qiita-image-store.s3.amazonaws.com/0/27932/f5b8ecd3-f271-8c60-fab5-24e2ce466de2.png)


Glueのメニューからジョブをクリックし、[ジョブの追加]をクリックする

![スクリーンショット 0030-09-09 16.41.32.png](https://qiita-image-store.s3.amazonaws.com/0/27932/2f4171b4-a7fd-599a-d6fe-b1f498f84639.png)

以下を入力し、[次へ]をクリックする
名前：se2-job13
IAMロール名：test-glue

![スクリーンショット 0030-09-09 16.42.28.png](https://qiita-image-store.s3.amazonaws.com/0/27932/84ac98d9-2c74-a4a5-7fe7-b8248b6feb93.png)

"se2_in0"にチェックを入れ、[次へ]をクリックする

![スクリーンショット 0030-09-09 16.43.01.png](https://qiita-image-store.s3.amazonaws.com/0/27932/c31eff2f-593a-72f6-df4a-c1ac27fc6e47.png)

以下を入力し、[次へ]をクリックする
データストア:JDBC
接続：se2-connect-dwh1
データベース名：db

![スクリーンショット 0030-09-09 16.43.16.png](https://qiita-image-store.s3.amazonaws.com/0/27932/4a252ee9-b9b8-0e9a-8022-6307d8bd9a9f.png)

このまま[次へ]をクリックし、次の画面で[ジョブを保存してスクリプトを編集する]をクリック

![スクリーンショット 0030-09-09 16.43.27.png](https://qiita-image-store.s3.amazonaws.com/0/27932/5a4f14b3-9ffa-07af-0295-1628f61c3670.png)

コードの一部を修正する。この状態だとテーブル名が"se2_in0"になっているので、"se2_out13"に変更する
変更箇所は下から2行目

修正前

```python:
datasink4 = glueContext.write_dynamic_frame.from_jdbc_conf(frame = dropnullfields3, catalog_connection = "se2-connect-dwh1", connection_options = {"dbtable": "se2_in0", "database": "db"}, redshift_tmp_dir = args["TempDir"], transformation_ctx = "datasink4")
```

修正後

```python:
datasink4 = glueContext.write_dynamic_frame.from_jdbc_conf(frame = dropnullfields3, catalog_connection = "se2-connect-dwh1", connection_options = {"dbtable": "se2_out13", "database": "db"}, redshift_tmp_dir = args["TempDir"], transformation_ctx = "datasink4")
```

画面左上の[ジョブの実行]をクリックする

![スクリーンショット 0030-09-09 16.52.23.png](https://qiita-image-store.s3.amazonaws.com/0/27932/21672999-e6ee-d34b-356c-e64dc274356c.png)

# Redshiftから確認

##### ここでの通信フローと構成は以下です。

![glue_redshift-Page-7.png](https://qiita-image-store.s3.amazonaws.com/0/27932/5f5c63ed-2733-f633-55dc-ef36d396b52b.png)


ログインし、テーブルの確認、レコードの確認

```bash:
# psql -h se2-gluetest-dwh.xxxxxxxxxxxx.ap-northeast-1.redshift.amazonaws.com -U admin -d db -p 5439
Password for user admin: 
psql (9.2.24, server 8.0.2)
WARNING: psql version 9.2, server version 8.0.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

db=# \d
         List of relations
 schema |   name    | type  | owner 
--------+-----------+-------+-------
 public | se2_out13 | table | admin
(1 row)
db=# select * from se2_out13;
 deviceid | uuid  | appid | country | year | month | day | hour 
----------+-------+-------+---------+------+-------+-----+------
 android  | 11112 |     1 | FR      | 2017 |    12 |  14 |   14
 iphone   | 11114 |     7 | AUS     | 2017 |    12 |  17 |   18
 iphone   | 11116 |     1 | JP      | 2017 |    12 |  15 |   11
 pc       | 11117 |     9 | FR      | 2017 |    12 |   2 |   18
 other    | 11110 |     5 | JP      | 2017 |    11 |  29 |   15
 android  | 11122 |     1 | FR      | 2017 |    11 |  30 |   20
 iphone   | 11124 |     7 | AUS     | 2017 |    12 |  17 |   14
 iphone   | 11126 |     1 | JP      | 2017 |    12 |  19 |    8
 iphone   | 11128 |     9 | FR      | 2017 |    12 |   9 |    4
 iphone   | 11111 |     1 | JP      | 2017 |    12 |  14 |   12
 iphone   | 11113 |     9 | FR      | 2017 |    12 |  16 |   21
 other    | 11115 |     5 | JP      | 2017 |    12 |  29 |   15
 pc       | 11118 |     1 | FR      | 2017 |    12 |   1 |    1
 iphone   | 11119 |     7 | AUS     | 2017 |    11 |  21 |   14
 iphone   | 11121 |     1 | JP      | 2017 |    11 |  11 |   12
 iphone   | 11123 |     9 | FR      | 2017 |    11 |  14 |   14
 iphone   | 11125 |     5 | JP      | 2017 |    11 |  29 |   15
 android  | 11127 |     1 | FR      | 2017 |    12 |  19 |   14
 iphone   | 11129 |     7 | AUS     | 2017 |    11 |  30 |   14
(19 rows)
```

# (補足)出力のクローラー作成

Glueの画面で、左側メニューのクローラをクリックし、[クローラの追加]をクリックする

![スクリーンショット 0030-09-09 17.01.04.png](https://qiita-image-store.s3.amazonaws.com/0/27932/9dc19014-752a-9372-a17f-6d99f9dbcb3b.png)

クローラーの名前を"se2_out13"を入力し、[次へ]をクリックする

![スクリーンショット 0030-09-09 17.01.17.png](https://qiita-image-store.s3.amazonaws.com/0/27932/dbe5075f-43d9-f744-c44e-630da09df887.png)

Choose a data storeに"JDBC"を選択し、接続を先程作成した"se2-connect-dwh1"を選択し、インクルードパスに"db/publicse2_out13"を入力し、[次へ]をクリックする
次の画面も[次へ]をクリックし、次の画面でIAMロールを"glue-test"を選び[次へ]をクリックし、次の画面で"オンデマンドで実行"を選び[次へ]をクリックする

![スクリーンショット 0030-09-09 17.01.56.png](https://qiita-image-store.s3.amazonaws.com/0/27932/23650293-25e7-dbda-3ca3-95c1f813a75a.png)

データベースに"se2"(任意)を選び、プレフィックスに"se2_"(任意)を入力し、[次へ]をクリックし、次の画面で[完了]をクリックする

![スクリーンショット 0030-09-09 17.07.22.png](https://qiita-image-store.s3.amazonaws.com/0/27932/848eb50a-8736-fb05-eb0e-96036cc7a35d.png)

"今すぐ実行しますか？"の文字をクリックする

![スクリーンショット 0030-09-09 17.08.41.png](https://qiita-image-store.s3.amazonaws.com/0/27932/d64f149b-0679-0b88-6c64-7ba04387faf8.png)

#### テーブル確認

![スクリーンショット 0030-09-09 17.11.32.png](https://qiita-image-store.s3.amazonaws.com/0/27932/c36f4420-5670-a998-5c77-e115c26165e0.png)

![スクリーンショット 0030-09-09 17.11.44.png](https://qiita-image-store.s3.amazonaws.com/0/27932/5556e081-59b6-5ae6-6503-98a6bb854aa6.png)

#### GlueジョブによりRedshiftで実行されたクエリはこれっぽい

```
db=# SELECT                      
  I.userid
  , I.query
  , convert_timezone('JST',Q.starttime) AS starttime
  , convert_timezone('JST',Q.endtime) AS endtime
  , I.rows
  , Q.querytxt
FROM stl_insert AS I
  LEFT OUTER JOIN stl_query AS Q ON I.query = Q.query
WHERE Q.aborted = 0
  AND Q."database" = 'db'
ORDER BY Q.starttime DESC;


 userid | query |         starttime          |          endtime           | rows | querytxt                                                                                    
----------------------------------------------------------------------------
    100 |  8707 | 2018-09-09 16:56:32.005422 | 2018-09-09 16:56:32.494869 |   10 | COPY "PUBLIC".se2_out13 FROM 's3://test-glue00/se2/tmp/2a1c8f31-6493-46a9-9712-2b258
3cb297e/manifest.json' CREDENTIALS '' FORMAT AS CSV NULL AS '@NULL@' manifest                                                                                          
    100 |  8707 | 2018-09-09 16:56:32.005422 | 2018-09-09 16:56:32.494869 |    9 | COPY "PUBLIC".se2_out13 FROM 's3://test-glue00/se2/tmp/2a1c8f31-6493-46a9-9712-2b258
3cb297e/manifest.json' CREDENTIALS '' FORMAT AS CSV NULL AS '@NULL@' manifest                                                                                          
    100 |  8705 | 2018-09-09 16:56:31.958689 | 2018-09-09 16:56:31.964935 |   10 | analyze compression phase 1                                                             　　　　　　　　100 |  8705 | 2018-09-09 16:56:31.958689 | 2018-09-09 16:56:31.964935 |    9 | analyze compression phase 1                                                
    100 |  8703 | 2018-09-09 16:56:31.914159 | 2018-09-09 16:56:31.920817 |   10 | analyze compression phase 1                                                
    100 |  8703 | 2018-09-09 16:56:31.914159 | 2018-09-09 16:56:31.920817 |    9 | analyze compression phase 1                                                
    100 |  8701 | 2018-09-09 16:56:31.865956 | 2018-09-09 16:56:31.874886 |   10 | analyze compression phase 1                                                
    100 |  8701 | 2018-09-09 16:56:31.865956 | 2018-09-09 16:56:31.874886 |    9 | analyze compression phase 1                                                
    100 |  8699 | 2018-09-09 16:56:31.81817  | 2018-09-09 16:56:31.82505  |   10 | analyze compression phase 1                                                
    100 |  8699 | 2018-09-09 16:56:31.81817  | 2018-09-09 16:56:31.82505  |    9 | analyze compression phase 1                                                
    100 |  8697 | 2018-09-09 16:56:31.77334  | 2018-09-09 16:56:31.779454 |    9 | analyze compression phase 1                                                
    100 |  8697 | 2018-09-09 16:56:31.77334  | 2018-09-09 16:56:31.779454 |   10 | analyze compression phase 1                                                
    100 |  8695 | 2018-09-09 16:56:31.728984 | 2018-09-09 16:56:31.73534  |   10 | analyze compression phase 1                                                
    100 |  8695 | 2018-09-09 16:56:31.728984 | 2018-09-09 16:56:31.73534  |    9 | analyze compression phase 1                                                
    100 |  8693 | 2018-09-09 16:56:31.683023 | 2018-09-09 16:56:31.68996  |   10 | analyze compression phase 1                                                
    100 |  8693 | 2018-09-09 16:56:31.683023 | 2018-09-09 16:56:31.68996  |    9 | analyze compression phase 1                                                
    100 |  8691 | 2018-09-09 16:56:31.583146 | 2018-09-09 16:56:31.590865 |   10 | analyze compression phase 1                                                
    100 |  8691 | 2018-09-09 16:56:31.583146 | 2018-09-09 16:56:31.590865 |    9 | analyze compression phase 1                                                
    100 |  8690 | 2018-09-09 16:56:30.991903 | 2018-09-09 16:56:31.581719 |   10 | COPY ANALYZE se2_out13                                                     
    100 |  8690 | 2018-09-09 16:56:30.991903 | 2018-09-09 16:56:31.581719 |    9 | COPY ANALYZE se2_out13                                                                               
```

##　※Glueにおける自己参照ルールの補足

https://qiita.com/pioho07/items/7223c77f341b7909431e

## こちらも是非

Glueの使い方的な㉔(S3からRedshiftにロード_準備編)
https://qiita.com/pioho07/items/05c912333e88788a1391

Glueの使い方まとめ
https://qiita.com/pioho07/items/32f76a16cbf49f9f712f