前編でS3からRedshiftにロードする際の準備を行いました。
ここでは、Glueを使ってS3からRedshiftへのロードを行います。
作成される構成は以下です。
前提
前提の構成は以下です。
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からJDBCへの接続のための定義である"接続"を作成します。
Glueの画面を開き、左側メニューの"Connection"をクリックし、[Add connection]をクリックする
以下を入力し、[Next]をクリックする
Connection Name："se2-connect-dwh1"(任意)
Connect Type："Amazon Redshift"
以下を入力し、[Next]をクリックする。次のページで[Finish]をクリックする
Cluster："se2-gluetest-dwh"
Database name："db"
Username："admin"
Password："xxxxx"(設定してあるパスワード)
connection名をクリックし、[Edit]をクリックすることで詳細な編集ができる
connection名をクリックし、[Edit]をクリックし[Next]をクリックした画面にいくと、Redshiftの情報から自動で入力されたVPCやセキュリティグループの情報が確認できる。何かあればここで確認や修正ができる
今回はセキュリティグループを㉔で作成した自己参照セキュリティグループの"se2-glue-jikosansho-sg"に変更する。Passwordは空の状態なので入力し、[Next]をクリックし、次のページで[Finish]をクリックする
接続テスト
Connectionの画面で、作成したConnectionの"se2-connect-dwh1"にチェックを入れ、[Add connection]をクリックする
IAMロールに"test-glue"を選択し、[Test connection]をクリックする。IAMロールはクローラーやジョブを実行しているものと同じ内容で構わない
数分後に以下の画面になればTest Connectionは完了
自己参照セキュリティグループを使ったフローの構成図
自己参照セキュリティグループを使ったより正確なGlueの通信フローは以下のようになります。
Glue自体はパブリックなAWSサービスで、Glueに自己参照セキュリティグループをアタッチすることでVPC内のリソース(RDS,Redshift)にアクセスします。
少しわかりずらいですが、GlueのENIがVPC内に出現し、そのENIにセキュリティグループがアタッチされるようなイメージです。
Glueの"ジョブ"の作成と実行
ここでの通信フローと構成は以下です。
Glueのメニューからジョブをクリックし、[ジョブの追加]をクリックする
以下を入力し、[次へ]をクリックする
名前：se2-job13
IAMロール名：test-glue
"se2_in0"にチェックを入れ、[次へ]をクリックする
以下を入力し、[次へ]をクリックする
データストア:JDBC
接続：se2-connect-dwh1
データベース名：db
このまま[次へ]をクリックし、次の画面で[ジョブを保存してスクリプトを編集する]をクリック
コードの一部を修正する。この状態だとテーブル名が"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")
画面左上の[ジョブの実行]をクリックする
Redshiftから確認
ここでの通信フローと構成は以下です。
ログインし、テーブルの確認、レコードの確認
# 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の画面で、左側メニューのクローラをクリックし、[クローラの追加]をクリックする
クローラーの名前を"se2_out13"を入力し、[次へ]をクリックする
Choose a data storeに"JDBC"を選択し、接続を先程作成した"se2-connect-dwh1"を選択し、インクルードパスに"db/publicse2_out13"を入力し、[次へ]をクリックする
次の画面も[次へ]をクリックし、次の画面でIAMロールを"glue-test"を選び[次へ]をクリックし、次の画面で"オンデマンドで実行"を選び[次へ]をクリックする
データベースに"se2"(任意)を選び、プレフィックスに"se2_"(任意)を入力し、[次へ]をクリックし、次の画面で[完了]をクリックする
"今すぐ実行しますか？"の文字をクリックする
テーブル確認
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における自己参照ルールの補足
こちらも是非
Glueの使い方的な㉔(S3からRedshiftにロード_準備編)
https://qiita.com/pioho07/items/05c912333e88788a1391
Glueの使い方まとめ
https://qiita.com/pioho07/items/32f76a16cbf49f9f712f