jupyterからSSH接続してAWSのRDBにアクセスする方法

概要

僕が業務でデータ分析をするときは主にbigqueryをjupyterからアクセスしてクエリを叩いて分析することが多いですが、企業によっては、

Sequel ProでDBにSSH接続してからSQL投げてcsvでエクスポート
エクスポートしたcsvを読み込んでPoC検証

という手順を踏むところもあります。別にこれで良いのですが、例えば

「新しい日にちのデータで分析してくれない？」

と言われると、毎回Sequel proで接続して数回クエリを叩いて複数のcsvを取得するような解析をしないといけないので、個人的に少しイライラします。僕は元々面倒臭がりなので、毎回Sequel proにアクセスしてデータを取ってくるのが怠い。どうせ検証なんだから全てjupyter上でささっと素早く分析してタスクを片付けたい！けど向こうの細かい要望は手間を掛けずに出せるようにしておきたい！

そこで、もうjupyter側からAWSのRDSにアクセスすることにしました。

接続方針と実際のソースコード

sshtunnelで踏み台サーバをSSHで経由してRDSにアクセスします。RDSへ接続するための仕組みはこちらの記事の解説図を見ると分かりやすいと思います。

pythonのmysqlclientでSSH越しにAWSのRDSにアクセス – 或る阿呆の記

そして、上記の方法でアクセスしようとしたのですが...ちょっとうまくいかなかった汗 PC側でそのポートが開いてないからなのかなんなのか...当初は検討が付かず処理が凍結して先に進めませんでした(原因については察しがついたので最後に書いてます)。

そこで色んな方法を調べていたところ、以下のサイトを参考にして解決できました。

PythonでSSH経由し外部のMySQLデータベースを操作してみる - Qiita

以下が実際に動かして上手くいった関数になります。重要なのはserver.local_bind_portの部分でlocal_bind_addressをsshtunnelで設定せずにsshtunnelで通過した際のlocal_bind_portを代入すると上手くRDSにアクセスすることができました。

from sshtunnel import SSHTunnelForwarder
import pymysql as db
import pandas as pd
import datetime

# ssh
sshOptions = {
    "bastion": {
        "host": '踏み台のIPアドレス',
        "ssh_username": 'ユーザー名',
        "ssh_private_key": '鍵のファイルパス',
        "ssh_password": '鍵のキーワード'
    }
}
ssh = sshOptions["bastion"]
# database
dbOptions = {
    "mysql": {
        "localhost": 'hogehoge.rds.amazonaws.com(rdsのエンドポイント)',
        "user": 'ユーザー名',
        "password": 'パスワード',
        "database": 'DBの名前',
        "port": 3306
    }
}
dbConfig = dbOptions["mysql"]

# なんかのクエリ
sql_query='''
SELECT * FROM tables
'''

def query(q):
    with SSHTunnelForwarder(
    (ssh["host"], 22),
    ssh_username = ssh["ssh_username"],
    ssh_password = ssh["ssh_password"],
    ssh_private_key = ssh["ssh_private_key"],
    remote_bind_address=(dbConfig["localhost"], dbConfig["port"])
    ) as server:
        conn = db.connect(host = '127.0.0.1',
                          port = server.local_bind_port,
                          user = dbConfig["user"],
                          passwd = dbConfig["password"],
                          db = dbConfig["database"],
                          charset = 'utf8',
                          cursorclass = db.cursors.DictCursor)
        df = pd.read_sql_query(q, conn)
        conn.close()
        return df

query(sql_query)

そもそもserver.local_bind_portで上手くいったのはなぜか

そして原因ですが、おそらくjupyterが関係してると思います。jupyter自体がlocalhostにポートを開いて接続してるので、sshtunnelでlocal_bind_addressを設定しても一致せずに動かないのが原因じゃないかなと推測しています。なので、sshtunnelを経由する前にlocal_bind_addressを設定せず、with ~ as serverとSSHで経由した際にserver.local_bind_portを設定すると上手くいくんだと思います(実際、sshtunnelでlocal_bind_addressを設定して上手くいっていた記事は複数確認できたので...)。

まとめ

今回はjupyterでローカルからAWSのRDSに接続する方法を備忘録として紹介しました。これでSequel Proの呪縛から解放されました。請け負ってる委託や研究も、PoCはほとんどjupyterで片付けられる状態にできたのでちょっと嬉しい。

たけのこブログ

凡人が頑張って背伸びするブログ

jupyterからSSH接続してAWSのRDBにアクセスする方法

概要

接続方針と実際のソースコード

そもそもserver.local_bind_portで上手くいったのはなぜか

まとめ