SSHトンネルを使ってリモートデータベースをBI

クラウド上のDBを分析する為、ローカルPCにSSHトンネル（ポートフォワード）環境を作ります。

リモートDBポートにクラウド外からセキュアに接続できれば、クラウドにBIツールを入れる手間が省けます。

ssh -NL ローカルPCポート:接続先IP:接続先ポート踏み台サーバユーザ@踏み台サーバIP

ローカルPCと踏み台サーバでsshコマンドが使える環境であれば、この1コマンドでリモートPCポートをローカルPCポートに持ってくることが出来、後はDBツールで接続するなりBIするなり自由自在です。

1 動作イメージ
2 環境
3 手順
- 3.1 SSHトンネルを作成
- 3.2 Metabaseで接続
4 まとめ

動作イメージ

DBポートをSSHトンネルでローカルPC（192.168.0.5）に持ってきて、ローカルPCにいれたMetabaseでBI。

環境

ローカルPC（Windows 10 + Cygwin、WSL等sshコマンドが使える環境）
踏み台サーバ（CentOS 7.6）
DBサーバ（CentOS 7.6）

以下の記事で環境が構築されている状態です。

PostgreSQL11にdvdrentalデータベースを構築。

One IT Thing

PostgreSQL11に一般ユーザ権限でサンプルDB「dvdrental」をpg_restore

https://one-it-thing.com/3111

データベースインストール後にサンプルデータを投入することで、DBをバックエンドにして動作する他ミドルの検証が楽になったりすることがあります。それぞれのデータベースで代表的なサンプルデータセットの存在を知っておくと、自分でデータを作らなくて済む分、検証作業が捗ります。PostgreSQLでは代表的なデータセットとして公式サンプルデータベースの「dvdrental」が設計も実戦的になっていて使いやすいです。ただこのサンプルデータ、pg_dumpしたユーザがpostgresユーザなのでpg_restoreするとpostgresユーザのテーブルとしてレ...

WindowsにMetabaseを導入。

One IT Thing

BIツール「metabase」でサンプルDB「dvdrental」をグラフ化する

https://one-it-thing.com/3184

「売り上げやユーザ増加傾向をDBから抽出して上役に報告したりチームで共有したい」こういった時、SQL結果をExcelでグラフ化、とかしていませんか？SQLを流して結果をコピーしてExcelに張り付けて・・・結構面倒で骨が折れます。そんな時はBIツールを使えばワンストップでDB内容をグラフ化出来て効率化出来ます。無料のBIツールとして「Re:dash」がメジャーですが、インストールが手間で導入で詰まる方も結構居らっしゃるんじゃないかな、と思います。その点今回のテーマのmetabaseは実行するPCにJavaさえ入っていれば一瞬で導入するこ...

手順

SSHトンネルを作成

192.168.0.4:5432を、192.168.0.2経由で、192.168.0.5:5432に持ってきます。

踏み台サーバ（192.168.0.2）にSSHログイン後、-LでローカルPC:5432とDBサーバ:5432をポートフォワード。踏み台サーバが両PCを繋ぐ架け橋になります。

-Nはssh中にリモートコマンドを叩いてしまわない為の保険です。

C:\> ssh -NL 5432:192.168.0.4:5432 naka@192.168.0.2
Could not create directory '/home/naka/.ssh'.
The authenticity of host '192.168.0.2 (192.168.0.2)' can't be established.
ECDSA key fingerprint is SHA256:LUmE4Zs3L78GwDvkWy25D69/r3h4Iym4x+QCf6IuN6U.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Failed to add the host to the list of known hosts (/home/naka/.ssh/known_hosts).
naka@192.168.0.2's password:

この時点でlocalhost:5432がPostgreSQLのポートになっているので、A5 Mk2やJailerでDBに接続することが出来ます。この時点で既に便利ですね ^^