MongoDBのデータをmongo_fdwを使用してPostgreSQLのテーブルにインポート

概要

PostgreSQLの拡張機能FDWを使用して、MongoDBのデータに連携することができる。mongo_fdwを使用し、外部のMongoDBのデータの一部をPostgreSQLのテーブルにインポートした。

環境

• PostgreSQL 14
• CentOS9 Stream(docker)
• MongoDB server 4.4.6

mongo_fdwの設定

githubのREADMEに従ってコンパイル、インストールする。
ソースコードは、git cloneで、/tmpフォルダに配置した。

mongo-c/json-のインストール

以下のコマンドでビルドからインストールまで実行する。

autogen.sh --with-master

が、途中エラーで止まるたびに以下をインストールしていった。

dnf install wget
dnf install cmake
dnf install gcc
dnf install openssl
dnf install openssl-devel
dnf install rpm-build
dnf install ccache

mongo_fdwのインストール

環境変数に設定を行ったのち、ビルド、インストールを実行する。
PKG_CONFIG_PATHは、/tmpフォルダ配下にコードをダウンロードして作業をしている前提で環境変数を設定。LD_LIBRARY_PATHは、libmongoc-1.0.soとlibbson-1.0.soのインストール先を設定した。

export PKG_CONFIG_PATH=/tmp/mongo_fdw/mongo-c-driver/src/libmongoc/src:/tmp/mongo_fdw/mongo-c-driver/src/libbson/src
export LD_LIBRARY_PATH=/lib64:$LD_LIBRARY_PATH
export PATH=/usr/pgsql-14/bin/:$PATH

make USE_PGXS=1
make USE_PGXS=1 install

上記実行して、またエラーになったので以下インストールしたのち再度make
を実行した。

wget https://rpmfind.net/linux/centos-stream/9-stream/CRB/x86_64/os/Packages/perl-IPC-Run-20200505.0-6.el9.noarch.rpm
wget https://rpmfind.net/linux/centos-stream/9-stream/CRB/x86_64/os/Packages/perl-IO-Tty-1.16-4.el9.x86_64.rpm

rpm -ivh perl-IO-Tty-1.16-4.el9.x86_64.rpm
rpm -ivh perl-IPC-Run-20200505.0-6.el9.noarch.rpm

dnf install postgresql14-devel*

MonogDB接続用の外部テーブルを作成

接続先のMonogoDBの接続情報

• 接続IP 192.168.11.4
• ポート　27017
• データベース　webcam
• コレクション　webcam
• ユーザ/パスワード　webcam/webcam

データフォーマット

MongoDBには、以下の形式のデータを追加している。
(ここではデータについては説明しないが、Webカメラ情報の一部、元ネタはhttps://api.windy.com/webcams)

{
    _id:string
    id:string;
    status: string;
    title:  string;
    location : {
        latitude: number;
        longitude: number;
    }
    player : {
        day:{
            available:string;
            link:string;
        }
    } 
    image : {
            current : {
            thumbnail:string;
        }
    };
};

PostgreSQLからMongoDBとの接続

CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address '192.168.11.4', port '27017');

CREATE USER MAPPING FOR postgres SERVER mongo_server OPTIONS (username 'webcam', password 'webcam');

外部テーブルの作成

webcamという名前の外部テーブルを作成。カラムは、MongoDB側のデータの全部ではなく一部を定義。

CREATE FOREIGN TABLE webcam
	(
		_id name,
		id text,
		"player.day.link" text,
		"image.current.thumbnail" text
	)
	SERVER mongo_server
	OPTIONS (database 'webcam', collection 'webcam');

データインポート先のテーブルを作成

PostgreSQLの実テーブルとして、サムネイル画像へのリンク"image.current.thumbnail"をインポートするテーブルを作成する。また、MongoDB側には存在しないが、独自のカラムを追加。

create table thumbnail_link(
   link varchar(100),
   download_flag integer default 0 );

インポートの実行

INSERT INTO文でデータを登録する。

INSERT INTO thumbnail_link (link) SELECT "image.current.thumbnail" FROM webcam;