[PostgreSQL]別DBのテーブルとJOINしたい

2019-07-17

DBを分けたテーブルとJOINしていい感じに扱えないか 尋ねられたので調べてみました。

ETL かなにかでレコードを同じDBのテーブルに格納すれば JOIN は可能ですが、 同期する手間やラグを考えるのは嫌なことです。

結論から言うと DBLINK や Foreign Data Wrapper の仕組みを使うことで透過的に参照できますが、 ハマりどころがあったので誰かの役に立つことを信じて備忘録として残しておくことにします。

目次

準備

今回は docker-compose で環境を用意します。

docker-compose.yml
version: "3.7"
services:
  pg1:
    container_name: pg1
    image: postgres:11.4
    environment:
      POSTGRES_USER: user1
      POSTGRES_PASSWORD: password
      POSTGRES_DB: db1
      POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=C"
    networks:
      - dblink-test-network
  pg2:
    container_name: pg2
    image: postgres:11.4
    environment:
      POSTGRES_USER: user2
      POSTGRES_PASSWORD: password
      POSTGRES_DB: db2
      POSTGRES_INITDB_ARGS: "--encoding=UTF-8 --locale=C"
    networks:
      - dblink-test-network
  my1:
    container_name: my1
    image: mysql:8.0.3
    environment:
      MYSQL_USER: user3
      MYSQL_PASSWORD: password
      MYSQL_DATABASE: db4
      MYSQL_ROOT_PASSWORD: root
    networks:
      - dblink-test-network

networks:
  dblink-test-network:

以降は docker-compose up によってコンテナが起動している前提で話を進めます。

pg1 pg2
$ docker exec -it pg1 /bin/bash
# psql -U user1 --dbname db1
db1=# CREATE EXTENSION dblink;
CREATE EXTENSION
db1=# CREATE TABLE a (id INTEGER, field VARCHAR(10));
CREATE TABLE
db1=# INSERT INTO a VALUES (1, 'aa');
INSERT 0 1

db1=# CREATE DATABASE db3;
CREATE DATABASE
db1=# \c db3
You are now connected to database "db3" as user "user1".
db3=# INSERT INTO c VALUES (1, 'cccc');
INSERT 0 1
db3=# CREATE TABLE c (id INTEGER, field VARCHAR(10));
CREATE TABLE

db3=# \c db1
You are now connected to database "db1" as user "user1".
$ docker exec -it pg2 /bin/bash
# psql -U user2 --dbname db2
db2=# CREATE TABLE b (id INTEGER, field VARCHAR(10));
CREATE TABLE
db2=# INSERT INTO b VALUES (1, 'bbb');
INSERT 0 1

準備したDBを俯瞰するとはこんな感じ。

pg1 (ゲスト環境1)
a in db1
id field
1 aa
c in db3
id field
1 cccc
pg2 (ゲスト環境2)
b in db2
id field
1 bbb

備考

  • CREATE EXTENSION dblink をやらないと HINT: No function matches the given name and argument types. You might need to add explicit type casts. といわれます
    • これは初回に一度実行すればOKです
  • PostgreSQL のバージョンによっては postgresql-contrib のインストールが必要かもしれません

作ったテーブルを db1 にいる状態で覗いてみましょう。

db1=# select * from a;
 id | field
----+-------
  1 | aa
(1 row)

db1=# select * from b;
ERROR:  relation "b" does not exist
LINE 1: select * from b;
                      ^
db1=# select * from c;
ERROR:  relation "c" does not exist
LINE 1: select * from c;

a は見れましたが、 b, c は 見つからないと言われてしまいました。 まぁこれは期待通りです。

Foreign Data Wrapper

Foreign Data Wrapper (頭文字をとって fdw) は外部のデータアクセスを提供します。

DB_LINK も外部DBへの接続ができましたが、 fdw は PostgreSQL 以外のDBも透過的に扱えます。 対象がリモートであれば積極的にこちらを採用したいところですね。

今回は PostgreSQL から MySQL(8.0.3) の外部サーバに接続してみましょう。

備考

MySQL 8.0.4 以上を使うと デフォルトの認証方法が caching_sha2_password に変わり、 いろいろとめんどくさいので今回は MySQL の 8.0.3 を使っています。

ERROR: failed to connect to MySQL: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/x86_64-linux-gnu/mariadb18/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

PostgreSQL から MySQL に接続するためには mysql_fdw という エクステンションが必要ですが、 通常は入っていません。

db1=# CREATE EXTENSION mysql_fdw;
ERROR:  could not open extension control file "/usr/share/postgresql/11/extension/mysql_fdw.control": No such file or directory

今回は MySQL イメージということで OS は Ubuntu なので、 postgresql-11-mysql-fdw をインストールするだけで準備完了です。

# apt install -y postgresql-11-mysql-fdw
Reading package lists... Done
Building dependency tree
Reading state information... Done
E: Unable to locate package postgresql-11-mysql-fdw
root@1bb847933995:/# apt update
Get:1 http://security.debian.org/debian-security stretch/updates InRelease [94.3 kB]
Ign:2 http://deb.debian.org/debian stretch InRelease
Get:3 http://deb.debian.org/debian stretch-updates InRelease [91.0 kB]
Get:4 http://deb.debian.org/debian stretch Release [118 kB]
Get:5 http://deb.debian.org/debian stretch Release.gpg [2,434 B]
Get:6 http://apt.postgresql.org/pub/repos/apt stretch-pgdg InRelease [51.4 kB]
Get:7 http://security.debian.org/debian-security stretch/updates/main amd64 Packages [499 kB]
Get:8 http://deb.debian.org/debian stretch-updates/main amd64 Packages [27.4 kB]
Get:9 http://deb.debian.org/debian stretch/main amd64 Packages [7,082 kB]
Get:10 http://apt.postgresql.org/pub/repos/apt stretch-pgdg/main amd64 Packages [179 kB]
Fetched 8,144 kB in 5s (1,407 kB/s)
Reading package lists... Done
Building dependency tree
Reading state information... Done
3 packages can be upgraded. Run 'apt list --upgradable' to see them.
root@1bb847933995:/# apt install -y postgresql-11-mysql-fdw
Reading package lists... Done
Building dependency tree
Reading state information... Done
Setting up manpages (4.10-2) ...
Setting up linux-libc-dev:amd64 (4.9.168-1+deb9u3) ...
Processing triggers for postgresql-common (201.pgdg90+1) ...
debconf: unable to initialize frontend: Dialog
debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)
debconf: falling back to frontend: Readline
debconf: unable to initialize frontend: Readline
debconf: (Can't locate Term/ReadLine.pm in @INC (you may need to install the Term::ReadLine module) (@INC contains: /etc/perl /usr/local/lib/x86_64-linux-gnu/perl/5.24.1 /usr/local/share/perl/5.24.1 /usr/lib/x86_64-linux-gnu/perl5/5.24 /usr/share/perl5 /usr/lib/x86_64-linux-gnu/perl/5.24 /usr/share/perl/5.24 /usr/local/lib/site_perl /usr/lib/x86_64-linux-gnu/perl-base .) at /usr/share/perl5/Debconf/FrontEnd/Readline.pm line 7.)
debconf: falling back to frontend: Teletype
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Setting up libc-dev-bin (2.24-11+deb9u4) ...
Setting up manpages-dev (4.10-2) ...
Setting up libc6-dev:amd64 (2.24-11+deb9u4) ...
Setting up zlib1g-dev:amd64 (1:1.2.8.dfsg-5) ...
Setting up libmariadbclient-dev (10.1.38-0+deb9u1) ...
Setting up libmariadbclient-dev-compat:amd64 (10.1.38-0+deb9u1) ...
Setting up default-libmysqlclient-dev:amd64 (1.0.2) ...
Setting up postgresql-11-mysql-fdw (2.5.1-1.pgdg90+1) ...

おわりました。

あとは psql で PostgreSQLに繋いで以下のことをします。

  • CREATE EXTENSION 文で mysql_fdw エクステンションを有効にする
  • CREATE SERVER 文で接続対象のサーバを登録する
  • CREATE USER MAPPING FOR 文でリモートユーザとユーザを紐付ける
  • CREATE FOREIGN TABLE 文で外部テーブルとスキーマを紐付ける
ちなみにMySQLには予めテーブル作成&データ登録をしてます
$ docker exec -it my1 /bin/bash
# mysql -u user3 --database db4 -p

mysql> CREATE TABLE d (id INTEGER, field VARCHAR(10));
Query OK, 0 rows affected (0.04 sec)

mysql> DESC d;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| field | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> INSERT INTO d VALUES (1, 'ddddd');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM d;
+------+-------+
| id   | field |
+------+-------+
|    1 | ddddd |
+------+-------+
1 row in set (0.00 sec)
-- 1. エクステンションを有効にする
db1=# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION

-- 2. 接続対象のサーバを登録する
db1=# CREATE SERVER mysql FOREIGN DATA WRAPPER mysql_fdw
db1-# OPTIONS (host 'my1', port '3306');
CREATE SERVER

-- 3. リモートユーザをローカルユーザに紐付ける
db1=# CREATE USER MAPPING FOR user1 SERVER mysql
db1-# OPTIONS (username 'user3', password 'password');
CREATE USER MAPPING

-- 4. 外部テーブルとスキーマを紐付ける
db1=# CREATE FOREIGN TABLE d (id INTEGER, field VARCHAR(10))
db1-# SERVER mysql
db1-# OPTIONS (dbname 'db4', table_name 'd');
CREATE FOREIGN TABLE

-- 外部テーブルの定義
db1=# \d d
                           Foreign table "public.d"
 Column |         Type          | Collation | Nullable | Default | FDW options
--------+-----------------------+-----------+----------+---------+-------------
 id     | integer               |           |          |         |
 field  | character varying(10) |           |          |         |
Server: mysql
FDW options: (dbname 'db4', table_name 'd')

-- JOIN してみる
db1=# SELECT a.id, a.field, d.id, d.field FROM a INNER JOIN d on a.id = d.id;
 id | field | id | field
----+-------+----+-------
  1 | aa    |  1 | ddddd
(1 row)

できましたね。

もっといいやり方あるよ~って方は教えてください。では。

参考