Edited at

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

前編でS3からRedshiftにロードする際の準備を行いました。

ここでは、Glueを使ってS3からRedshiftへのロードを行います。

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

glue_redshift-Final.png


前提


前提の構成は以下です。

glue_redshift-Page-4.png


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

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


Redshift関連情報

クラスタ名:se2-gluetest-dwh

データベース:db

ユーザー:admin

パスワードxxxx(任意)


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

・IAMロール:test-glue

・クローラー:se2_in0

・入力データ:19件のcsvファイル


今回作るリソース

ジョブ:se2_job13

クローラー:se2_out13


全体の流れ


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

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

  • (補足)Redshiftのテーブルへのクローラー作成


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


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

glue_redshift-Page-5 (1).png

GlueからJDBCへの接続のための定義である"接続"を作成します。

Glueの画面を開き、左側メニューの"Connection"をクリックし、[Add connection]をクリックする

スクリーンショット 0030-09-09 16.06.48.png

以下を入力し、[Next]をクリックする

Connection Name:"se2-connect-dwh1"(任意)

Connect Type:"Amazon Redshift"

スクリーンショット 0030-09-09 16.07.11.png

以下を入力し、[Next]をクリックする。次のページで[Finish]をクリックする

Cluster:"se2-gluetest-dwh"

Database name:"db"

Username:"admin"

Password:"xxxxx"(設定してあるパスワード)

スクリーンショット 0030-09-09 16.07.29.png

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

スクリーンショット 0030-09-09 16.07.45.png

connection名をクリックし、[Edit]をクリックし[Next]をクリックした画面にいくと、Redshiftの情報から自動で入力されたVPCやセキュリティグループの情報が確認できる。何かあればここで確認や修正ができる

今回はセキュリティグループを㉔で作成した自己参照セキュリティグループの"se2-glue-jikosansho-sg"に変更する。Passwordは空の状態なので入力し、[Next]をクリックし、次のページで[Finish]をクリックする

スクリーンショット 0030-09-09 16.23.36.png


接続テスト

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

スクリーンショット 0030-09-09 16.21.28.png

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

スクリーンショット 0030-09-09 16.21.38.png

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

スクリーンショット 0030-09-09 16.26.22.png


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

自己参照セキュリティグループを使ったより正確なGlueの通信フローは以下のようになります。

Glue自体はパブリックなAWSサービスで、Glueに自己参照セキュリティグループをアタッチすることでVPC内のリソース(RDS,Redshift)にアクセスします。

少しわかりずらいですが、GlueのENIがVPC内に出現し、そのENIにセキュリティグループがアタッチされるようなイメージです。

glue_redshift-Page-77.png


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


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

glue_redshift-Page-6.png

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

スクリーンショット 0030-09-09 16.41.32.png

以下を入力し、[次へ]をクリックする

名前:se2-job13

IAMロール名:test-glue

スクリーンショット 0030-09-09 16.42.28.png

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

スクリーンショット 0030-09-09 16.43.01.png

以下を入力し、[次へ]をクリックする

データストア:JDBC

接続:se2-connect-dwh1

データベース名:db

スクリーンショット 0030-09-09 16.43.16.png

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

スクリーンショット 0030-09-09 16.43.27.png

コードの一部を修正する。この状態だとテーブル名が"se2_in0"になっているので、"se2_out13"に変更する

変更箇所は下から2行目

修正前

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")

修正後

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


Redshiftから確認


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

glue_redshift-Page-7.png

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

# 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

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

スクリーンショット 0030-09-09 17.01.17.png

Choose a data storeに"JDBC"を選択し、接続を先程作成した"se2-connect-dwh1"を選択し、インクルードパスに"db/publicse2_out13"を入力し、[次へ]をクリックする

次の画面も[次へ]をクリックし、次の画面でIAMロールを"glue-test"を選び[次へ]をクリックし、次の画面で"オンデマンドで実行"を選び[次へ]をクリックする

スクリーンショット 0030-09-09 17.01.56.png

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

スクリーンショット 0030-09-09 17.07.22.png

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

スクリーンショット 0030-09-09 17.08.41.png


テーブル確認

スクリーンショット 0030-09-09 17.11.32.png

スクリーンショット 0030-09-09 17.11.44.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