9
Help us understand the problem. What are the problem?

More than 3 years have passed since last update.

posted at

updated 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における自己参照ルールの補足

こちらも是非

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

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

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
9
Help us understand the problem. What are the problem?