2014年5月28日水曜日

PostgreSQLの他データベースにアクセスする仕組み(FDW(Foreign Data Wrapper)編)

こんにちは、稲垣です。

前回は PostgreSQLには他のデータベースにアクセスする仕組みとして dblink をご案内しました。
今回は FDW(Foreign Data Wrapper) についてご案内します。

前回の dblink の記事はこちらからご確認ください。

■ FDWとは?

FDWはPostgreSQL 9.1から導入され、9.2からpostgres_fdwが利用できるようになりました。現在も様々な拡張が行われています。
FDWはより汎用的な外部データベースへのアクセス手段を提供します。PostgreSQL Wikiによると以下のデータラッパーが利用できます。

SQL Databases Wrappers
postgres_fdw
oracle_fdw
mysql_fdw
tds_fdw(not working)
odbc_fdw
jdbc_fdw
informix_fdw (WIP)
NoSQL Databases Wrappers
couchdb_fdw
MonetDB FDW
mongo_fdw
redis_fdw
Neo4j fdw
Tycoon FDW
File Wrappers
file_fdw
file_text_array_fdw
file_fixed_length_record_fdw
json_fdw
Others
twitter_fdw
ldap_fdw
PGStrom
s3_fdw
www_fdw

http://wiki.postgresql.org/wiki/Foreign_data_wrappers

PostgreSQLのデータラッパーはもちろん、MySQL、Oracle、TDS(MS SQL Server)、ODBC、JDBC、Informixなども用意されています。NoSQL系のデータベース、ファイル、Webサービスのデータラッパーまであります。この他にPythonでFDWを記述できるMulticorn FDWもあります。

新しいPostgreSQLではdblinkよりpostgres_fdwを利用する方が色々頭を悩ませる事が少なくなります。

■ PostgreSQL FDW

PostgreSQL FDWはdblinkと似たような機能を提供します。しかし、トランザクション管理とリモートクエリ最適化の面でdblinkより優れています。

F.31.3. トランザクション管理

外部サーバ上のリモートテーブルを参照する際に、まだトランザクションが開始されていなければpostgres_fdwはリモートサーバ上でトランザクションを開始します。 ローカルのトランザクションがコミット、あるいはアボートした時、リモートのトランザクションも同様にコミット、あるいはアボートします。 セーブポイントも同様に管理され、リモート側に関連付けられたセーブポイントが作成されます。

F.31.4. リモート問合せの最適化

外部サーバからのデータ転送量を削減するため、postgres_fdwはリモート問合せを最適化しようと試みます。 これは問い合わせのWHERE句をリモートサーバに送出する事、およびクエリで必要とされていないカラムを取得しない事により行われます。 問い合わせの誤作動のリスクを下げるため、ビルトインのデータ型、演算子、関数だけを用いたものでない限り、リモートサーバにWHERE句は送出されません。また、WHERE句で使われる演算子と関数はIMMUTABLEでなければなりません。

http://www.postgresql.jp/document/9.3/html/postgres-fdw.html

■ PostgreSQL FDWの利用

PostgreSQL FDWはcontribディレクトリにあります。PostgreSQL 9.3から機能が拡張されています。今回は9.3を利用します。

■ インストール

PostgreSQL 9.3のソースディレクトリから以下のコマンドを実行し、インストールします。

$ make -C contrib && sudo make -C contrib install

次にPostgreSQL 9.3を起動し、データベースにpostgresql_fdw拡張を登録します。psqlのコマンドラインからCREATE EXTENSIONコマンドを実行します。

localhost ~=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
時間: 299.364 ms

これで、アクセス中のデータベースにpostgres_fdwが登録されました。psqlのコマンドラインからは以下のように確認できます。

localhost ~=# \dx
インストール済みの拡張の一覧
名前     | バージョン |  スキーマ  |                             説明
--------------+------------+------------+--------------------------------------------------------------
dblink       | 1.1        | public     | connect to other PostgreSQL databases from within a database
plpgsql      | 1.0        | pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.0        | public     | foreign-data wrapper for remote PostgreSQL servers
(3 行)

これでpostgres_fdwを利用する準備が整いました。

■ リモートデータベースの準備

テスト用にリモートデータベースを準備するにはpgbenchが便利です。テスト用にremotedbを作成し、pgbenchでデータデータを作成します。別のPostgreSQLサーバーに準備しても構いませんが、今回はテストなのでローカルのPostgreSQLに準備します。

remoteデータベースの作成

$ createdb -U postgres -h 127.0.0.1 -p 5432 -E UTF-8 remote

データベースの初期化

$ pgbench -U postgres -h 127.0.0.1 -p 5432 -i -s 1000 remote

これでテスト用のリモートデータベースが準備できました。

■ リモートデータベースの利用

FDWを利用したリモートデータベースには更に、外部サーバー登録、ユーザーマッピング登録、外部テーブル登録のステップが必要です。

リモートサーバーの登録

まずCREATE SERVERを利用して、利用するデータラッパーを指定してリモートサーバーを定義します。

CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ]
FOREIGN DATA WRAPPER fdw_name
[ OPTIONS ( option 'value' [, ... ] ) ]

http://www.postgresql.org/docs/9.3/static/sql-createserver.html

実際にローカルホストのremoteデータベースを登録する場合

localhost ~=# CREATE SERVER remote_postgres FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', port '5432', dbname 'remote');
CREATE SERVER
時間: 121.581 ms

psqlから"\des”と入力すると作成した外部サーバー名、所有者、外部データラッパーを確認できます。

localhost ~=# \des
外部サーバー一覧
名前 | 所有者 | 外部データラッパー
-----------------+---------+--------------------
remote_postgres | username | postgres_fdw
(1 行)

ユーザーマッピングの登録

マッピング登録にはCREATE USER MAPPINGを利用します。

CREATE USER MAPPING FOR { user_name | USER | CURRENT_USER | PUBLIC }
SERVER server_name
[ OPTIONS ( option 'value' [ , ... ] ) ]

http://www.postgresql.org/docs/9.3/static/sql-createusermapping.html

この記事の執筆に利用しているPostgreSQLサーバーはローカルからの接続にtrustモードを利用しているので、パスワード設定などが必要ありません。次のコマンドでマッピングを作成します。

localhost ~=# CREATE USER MAPPING FOR PUBLIC SERVER remote_postgres OPTIONS (password '');
CREATE USER MAPPING
時間: 21.034 ms

外部テーブルの登録

外部テーブルの作成にはCREATE FOREIGN TABLEを利用します。

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [
column_name data_type [ OPTIONS ( option 'value' [, ... ] ) ] [ COLLATE collation ] [ column_constraint [ ... ] ]
[, ... ]
] )
SERVER server_name
[ OPTIONS ( option 'value' [, ... ] ) ]

where column_constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL |
NULL |
DEFAULT default_expr }
http://www.postgresql.org/docs/9.3/static/sql-createforeigntable.html

pgbenchでテーブルを定義したデータベースを保存する、remote_postgresサーバーへアクセスする外部テーブル定義は以下の様に行います。

  localhost ~=# CREATE FOREIGN TABLE remote_pgbench_accounts(aid int, bid int, ablance int, filler CHAR(84)) SERVER remote_postgres OPTIONS (table_name 'pgbench_accounts');
CREATE FOREIGN TABLE
時間: 197.626 ms

これで外部データベースのpgbench_accountsテーブルにアクセスできるようになりました。

■ 外部テーブルへのアクセス

外部テーブルへアクセス方法はローカルのデータベースと変わりません。

localhost ~=# SELECT * FROM remote_pgbench_accounts LIMIT 3; aid | bid | abalance | filler
-----+-----+----------+--------------------------------------------------------------------------------------
1 |   1 |        0 | 
2 |   1 |        0 | 
3 |   1 |        0 | 
(3 行)
時間: 1.686 ms

これだけではローカルのテーブルにアクセスしているのか、リモートのテーブルにアクセスしているのかわかりません。EXPLAINでクエリプランを表示するとリモートテーブルにアクセスしている事が確認できます。

localhost ~=# EXPLAIN SELECT * FROM remote_pgbench_accounts LIMIT 3;
QUERY PLAN
-----------------------------------------------------------------------------------------
  Limit  (cost=100.00..100.14 rows=3 width=100)
  ->  Foreign Scan on remote_pgbench_accounts  (cost=100.00..129.80 rows=660 width=100)
  (2 行)
時間: 373.532 ms

ローカルテーブルにアクセスした場合には見られない"Foreign Scan"が表示され、リモートテーブルにアクセスしていることが確認できます。

■ まとめ

今回はdblinkとpostgres_fdwの利用方法のみを紹介しました。dblinkとpostgres_fdwは似たような機能を提供しますが、postgres_fdwの方がクエリを効率的に実行できます。

0 件のコメント:

コメントを投稿