2019-07-17

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

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

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

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

準備

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

以降は 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
    • idfield
      1aa
  • c in db1
    • idfield
      1cccc
pg2 (ゲスト環境2)
  • b in db2
    • idfield
      1bbb
info
  • 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 は 見つからないと言われてしまいました。 まぁこれは期待通りです。

今度は DBLINK 関数を使って 接続します。

db1=# SELECT * FROM DBLINK('host=pg2 user=user2 password=password dbname=db2', 'SELECT * FROM b') db1-# AS f(id INTEGER, field VARCHAR(10)); id | field ----+------- 1 | bbb (1 row) db1=# SELECT * FROM DBLINK('user=user1 dbname=db3', 'SELECT * FROM c') db1-# AS f(id INTEGER, field VARCHAR(10)); id | field ----+------- 1 | cccc (1 row)

レコードが取れました! ここまでくればもうできたようなもんですね。

warning
  • DBLINK で取れた表に対しては AS でフィールドと型を紐づけてあげないとエラーになります
    • ERROR: a column definition list is required for functions returning "record"
  • 接続条件に user を指定しないと postgres で接続しに行くので DETAIL: FATAL: role \"postgres\" does not exist と言われることがあります。
    • あらかじめ postgres を作っておくか存在するユーザを指定しましょう。

まぁでもこのままでは少し冗長なので VIEW にしてみます。

db1=# CREATE VIEW b AS db1-# SELECT * FROM DBLINK('host=pg2 user=user2 password=password dbname=db2', 'SELECT * FROM b') db1-# AS f(id INTEGER, field VARCHAR(10)); CREATE VIEW db1=# CREATE VIEW c AS db1-# SELECT * FROM DBLINK('user=user1 dbname=db3', 'SELECT * FROM c') db1-# AS f(id INTEGER, field VARCHAR(10)); CREATE VIEW

作ったビューと結合してみましょう。 結合条件は適当です。

db1=# SELECT db1-# a.id AS id_a, a.field AS field_a, db1-# b.id AS id_b, b.field AS field_b, db1-# c.id AS id_c, c.field AS field_c db1-# FROM a db1-# LEFT JOIN b ON a.id = b.id db1-# LEFT JOIN c ON a.id = c.id db1-# ; id_a | field_a | id_b | field_b | id_c | field_c ------+---------+------+---------+------+--------- 1 | aa | 1 | bbb | 1 | cccc (1 row)

bc は VIEW なので変更は即座に反映されます。

実験のために b のレコードを削除します

db2=# DELETE FROM b; DELETE 1
db1=# select * from b; -- db1 から見える b は ビュー id | field ----+------- (0 rows)

db1から見ても b は空になってますね

DBLINK は使用するたびにセッションが新たに作られるようで、これはあまり好ましくありません。

また、認証情報も毎回指定するのだるいです。こんなときに使うのが dblink_connect です。

dblink_connect は 接続を別名としてセッション内で永続化します。

db1=# select dblink_connect('db2', 'host=pg2 user=user2 password=password dbname=db2'); dblink_connect ---------------- OK (1 row)

ここでは pg2 の db2 データベースへのコネクションを db2 という名前で保存します。

試しにこのタイミングで (db2 データベースの) user2 パスワードを password2 に変更し、 dblink_connect で作ったコネクションを使った接続と使わない接続を試してみましょう。

db2=# alter role user2 with password 'password2'; ALTER ROLE
  • 使う
  • 使わない
  • db1=# SELECT * FROM DBLINK('db2', 'SELECT * FROM b') db1-# AS f(id INTEGER, field VARCHAR(10));
  • db1=# SELECT * FROM DBLINK('host=pg2 user=user2 password=password dbname=db2', 'SELECT * FROM b') db1-# AS f(id INTEGER, field VARCHAR(10)); ERROR: could not establish connection DETAIL: FATAL: password authentication failed for user "user2"

dblink_connect で作った接続を使っている クエリした方は同じ接続を使い回すのでエラーになりませんでしたね。

warning
  • セッション内なので、一度セッションを抜けると接続は切れます。

Foreign Data Wrapper

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

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

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

info
  • 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)

できましたね。

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

参考

dblink | Let's POSTGREShttps://lets.postgresql.jp/documents/technical/contrib/dblink dblink_connecthttps://www.postgresql.jp/document/11/html/contrib-dblink-connect.html PostgreSQL: ERROR: 42601: a column definition list is required for functions returning "record"As far as I can tell, my function properly resembles the samples I've seen. Can someone clue me in as to how I get this to work? create or replace function get_user_by_username( username varcha...https://stackoverflow.com/questions/8605174/postgresql-error-42601-a-column-definition-list-is-required-for-functions-ret FDWでPostgreSQLからMySQLのテーブルを操作する - QiitaやりたいことRaspberryPi上のMySQLのデータを、VirtualBox上のPostgreSQLから操作したい。どうやらFDWという機能を使う事で実現できる模様。環境macOS Hi…https://qiita.com/msrx9/items/734d644b7b2aad796b09 Foreign Data Wrapper(FDW)の機能強化https://thinkit.co.jp/article/9933