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

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

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

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

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

動作イメージ

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

 

4 Pockets

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

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

「売り上げやユーザ増加傾向をDBから抽出して上役に報告したりチームで共有したい」資料を作る為に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

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

Metabaseで接続

localhost:5432にMetabaseを繋いでBI環境を作ります。

まずはMetabaseを起動。metabase.jarがあるディレクトリでjarを実行します。

C:\tools> java -jar metabase.jar

localhost:3000で起動するのでブラウザからアクセス。新たに分析対象のデータベースを追加します。

メニューから「管理者」をクリックして管理者画面へ移動。

管理者画面で「データベースを追加する」をクリック。

データソースを設定。
sshコマンドで作成したlocalhost:5432を指定します。

設定を保存するとlocalhost:5432経由でDBサーバ:5432に接続され、dvdrentalデータベースの分析を開始できます。

リモートPCに入っているPostgreSQLを、まるでローカルPCに入っているかのような設定でBI出来るようになりました。

リモートPCのPostgreSQLポートを開ける必要が無いのでセキュリティ的にもプラス効果です。

まとめ

Metabaseに限って言えば実はsshトンネルを独自に作ってDB接続する機能は標準搭載されています。

ただMetabaseが起動していないとリモートDBにSSHトンネル出来ないのは不便ですね。

sshコマンドでトンネルを作る知識は汎用かつ実戦的で重宝します。