jupyterの様なデータ分析ツールからrdsに接続してデータフレームを手にいれる方法について書かせていただきます。
モチベーション
データベースからデータを引っ張って来てデータ分析をしたいなと思い立った時に
- SQL打って欲しい結果セットを作成
- 結果セットをCSVを落とす
- CSVを分析環境に置く
- CSVを読み込んでデータフレーム作成
- データフレームが手元に入ってready to go
という工程に沿うことが多いと思います。
しかし
- 様々なデータフレームを試したいとなった場合
- 状況によって欲しいデータフレームが変わってくる場合
- 常に新鮮なデータが欲しい場合
と言った状況下では、先の工程を何度も繰り返すことになります。
結果として作業者はデータベースと分析環境の間を反復横跳びすることになったり、あるいはデータを用意するエンジニアと分析するデータサイエンティストの間でそれなりのコミュニケーションコストが発生することがあります。
そこで、分析環境をデータベースにそのまま繋いでしまえばそのコストを削減できるのではと思いました。
どうしたか
自分はデータ分析にPythonを使うことが多いですが、今回はPythonのMySQLクライアントである mysqlclient を使いデータベースとの接続を試みました（mysqlclientはPythonのwebフレームワークDjangoの推奨ドライバです）
踏み台サーバーなしの場合
import MySQLdb
connect = MySQLdb.connect(
host='rdsのエンドポイント',
user='ユーザー名',
db='データベース名',
passwd='パスワード'
)
cursor = connect.cursor()
sql = "selectといったsql文を文字列でかく"
cursor.execute(sql)
for row in cursor.fetchall():
print(row)
cursor.close()
connect.close()
結果はタプルで返ってきます。
テーブルが大きいとプロセスが終わらないので、sqlの指定には必ずlimitをつけてください。
踏み台サーバーありの場合
踏み台サーバーがある場合はmysqlclientと一緒に、sshtunnelライブラリを使ってsshトンネルを掘ります。
import MySQLdb
from sshtunnel import SSHTunnelForwarder
with SSHTunnelForwarder(
('踏み台サーバーのipアドレス', 踏み台サーバーのポート番号),
ssh_host_key=None,
ssh_username='踏み台サーバーにアクセスするユーザー名',
ssh_password=None,
ssh_pkey='ssh秘密鍵のパスを指定する',
remote_bind_address=('rdsのエンドポイント', rdsのポート番号),
local_bind_address=('ローカルホスト', 任意のポート番号)
) as tunnel:
connect = MySQLdb.connect(
host='ローカルホスト',
port=上のlocal_bind_addressで決めた任意のポート番号,
user='ユーザー名',
db='データベース名',
passwd='パスワード'
)
cursor = connect.cursor()
sql = "selectといったsql文を文字列でかく"
cursor.execute(sql)
for row in cursor.fetchall():
print(row)
cursor.close()
connect.close()
こちらも結果はタプルで返ってきます。
まとめ
- 様々なデータフレームを試したい
- 状況によって欲しいデータフレームが変わってくる
- 常に新鮮なデータが欲しい
これらの要望を満たすことができ、無事に分析者が欲しいデータをすぐに手に入れられる環境を提供することができました。
またpython側で変数を指定できるので、sqlを指定する文字列に式展開ができるなど柔軟な対応ができる副次的なメリットもありました。