【PostgreSQL】DBLINKで外部のデータベースにアクセスしてビューテーブルを作成する方法

ホリ

異なるシステムのデータを使ってテーブル結合したい。
結合したデータをビューにしてBIツールで活用したい。

今回は、こういった悩みを解決していきます。

昨今、製造業界では「スマートファクトリー」や「DX」に注目が集まっています。

その中で避けては通れない社内データの一元化による見える化や分析業務があります。

企業で活用しているデータベースは、それぞれの業務に応じて導入してきたシステムの数だけあります。

生産管理には生産管理システム、在庫には在庫管理システムと点在しているはずです。

ETLツール等を使い、ノーコードで各種データベースの情報を収集して、一元管理する情報基盤を作るのが専門知識が不要なためおすすめです。

しかし、複雑な集計などが必要でETLツールだけではやりたいことが実現できない場合があります。

そのような場合に、あらかじめ異なるデータベース間のデータを結合してビューを作っておく方法をとることがあります。

本記事では下記の内容についてまとめています。

  • PostgreSQLのDBLINKを使って、ビューを作成する方法

DBLINKでデータ抽出をする方法

実務でも使うことが多い『A5:SQL MK-2』というWindows向けのSQLクライアントを使うのがわかりやすくておすすめです。

インストール方法などは下の記事でまとめていますので、確認してみてください。

PostgreSQLをインストールして、A5:SQL MK-2から接続する方法

dblinkのエクステンションの作成

CREATE EXTENSION dblink

接続と実行

SELECT * FROM dblink(’host=*** port=*** dbname=*** user=*** password=***’ , ’select name, id  from table’) as t (name text, id int)

from句以降は接続先のデータベース情報とSQLの実行文を記述します。

ポイントとしては、抽出するカラムの型指定が必要なことです。

DBLINKを使ってビューを作成する方法

DBLINKを使ってビューを作成しておくことで、後々通常のテーブルのように使うことができるので便利です。

他のテーブルと結合して集計したり、それをBIツールで可視化したりすることが可能です。

CREATE OR REPLACE VIEW dblink_view AS SELECT * FROM dblink(’host=*** port=*** dbname=*** user=*** password=***’ , ’select name, id  from table’) as t (name text, id int)

DBLINKの実行文の前に`CREATE OR REPLACE VIEW dblink_view AS`が付いただけなので簡単です。

作成したビューのデータを抽出する方法

通常のテーブルから抽出するSQLと同じです。

SELECT * FROM dblink_view

まとめ

少し見慣れないSQL文ではありますが、簡単に外部データベースとの連携をすることができます。

あらかじめDBLINKで抽出したデータとテーブル結合したビューを作成しておけば、通常のテーブルと同じように扱うことができるため便利です。

システム間をまたいでデータを利用する機会などがありましたら、参考にしてみてください。

postgreSQL プログラミング言語