Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

前編で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

pioho07
こちらに記載の内容は所属会社とは関係ありませぬ。
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした