外部データとの連携 ～FDWで様々なデータソースとつなぐ～
富士通株式会社 ソフトウェアプロダクト事業本部 データマネジメント事業部
デジタル技術の進化により、ビジネスは大きく変わりつつあります。IoTにより取得できる多種多様なデータと、既存の業務データとを組み合わせて、新たな価値へとつなげるなど、システムは様々なデータ（システム）と連携できることが求められています。PostgreSQLには、Foreign Data Wrapper（日本語では「外部データラッパ」と呼ばれ、以降「FDW」と略します）という機能があり、RDBやNoSQLなど様々な外部データにアクセスできます。ここでは、FDWの概要と仕組み、利用時のポイントについて説明します。なお、この記事は、PostgreSQL 11.1を前提として作成しています。
1. FDWとは
FDWとは、SELECT文やUPDATE文などのSQL文を使用して、外部にあるデータにアクセスできるようにするための、PostgreSQLの拡張機能です。
FDWは、PostgreSQLが公開しているライブラリを利用し、RDBやNoSQLなど、連携したいデータに合わせて独自に作成できます。この仕組みを利用して、これまでに多くの企業、プロジェクト、個人がFDWを作成しています。現在では約120種類近くものFDWが公開されていますので、それらを利用することで多様なデータにアクセスできます。
PostgreSQLからアクセスできる外部データ
PostgreSQLからアクセスできる外部データの主なカテゴリーには、以下のようなものがあります。
- PostgreSQL
- PostgreSQL以外のRDB（Oracle、SQL Server、MySQLなど）
- NoSQLデータベース（MongoDB、Redis、Cassandraなど）
- ファイル（CSVファイル、プレーンテキストなど）
- 地理空間情報（PostGISなど）
- LDAP
- ビッグデータ（Apache Hadoop、BigQueryなど）
- SNS（Facebook、Twitterなど）
これら外部データのアクセス先を、データソースと呼びます。
参考
以下のサイトに、FDWがまとめて掲載されています。目的に見合ったFDWを選ぶうえでの参考にしてください。
- PostgreSQL Wiki
データソースの分野によって13のカテゴリーに分類されたFDWが掲載されています。 - PGXN(PostgreSQL Extension Network)
PostgreSQLの拡張モジュールがFDWを含め多数公開されています。トップページで「fdw」「foreign data wrapper」といったタグを選択することでFDWに絞り込むことができます。
FDWの活用シーン：IoTデータを活用して新たなサービスを提供
FDWの様々なデータとつながる特性を活かし、既存のシステムを利用して新たなサービスを拡張できます。例えば、すでにある基幹データベースにある利用者情報と、位置情報やセンサー情報などのIoTデータを組み合わせて、新たなサービスを提供するようなシーンへの適用が期待できます。
2. FDWの仕組みと利用について
FDWは外部データを参照できることに加え、FDWによっては更新系のSQLを実行できたり、データの絞り込みやソートの処理などを、連携先で実行させる「プッシュダウン」機構を使えるものもあります。ここでは、FDWの仕組みと利用方法についての概要を説明します。
2.1 FDWの仕組み
FDWが外部のデータソースと連携するための仕組み、およびPostgreSQL内部での処理の流れについて、説明します。
外部のデータソースにアクセスする仕組み
外部のデータソースにアクセスするためには、対応するFDWをPostgreSQLの拡張機能としてインストール、および外部データを「外部テーブル」として定義しておきます。外部テーブルに対して、SQL文で問い合わせることで、FDWを介して外部データにアクセスできます。
PostgreSQL内部での処理の流れ
クライアントから外部テーブルを含むSQL文を発行すると、PostgreSQLの内部では、以下の流れで処理が実行されます。
- クライアントからFROM句に外部テーブルを指定したSQL文を実行
- パーサが構文を解析
- プランナ(オプティマイザ)がFDWに実行プランを問い合わせて最適なプランを取得
- エクゼキュータが、FROM句に指定された外部テーブルに対応するFDWに処理を依頼
- FDWが外部データにアクセス
- 実行した結果をPostgreSQLの内部表現に変換してエクゼキュータに返却
- クライアントに実行結果を返却
2.2 FDWの利用の流れ
FDWを利用する際は、関連するオブジェクトを作成します。オブジェクトを作成するためには、連携先のデータソースに対応するFDWのインストール、接続先のサーバ定義、ユーザーのマッピング定義、および外部テーブルの作成を行います。ここでは、その概要を説明します。
以下は、外部にあるPostgreSQLと連携するためのFDW「postgres_fdw」を利用した場合の例を示したものです。postgres_fdwの場合、利用するための準備として下図のローカル側で上から示した順番にオブジェクトを作成します。
備考 CREATE SERVER、CREATE USERMAPPING、CREATE FOREIGN TABLEのOPTIONSに指定できる項目や書式は、利用するFDWごとに違いがあります。
| オブジェクトの種類 | 説明 |
|---|---|
| EXTENSION (拡張) |
作成済のFDWモジュールを、SQL文のCREATE EXTENSIONで、PostgreSQLの拡張として作成します。 |
| SERVER (外部サーバ) |
SQL文のCREATE SERVERで、接続する外部サーバへの接続情報、および、利用するFDWを定義します。 |
| USER MAPPING (ユーザマップ) |
リモート側のテーブルには、テーブルに対して権限を持つ既存のユーザー名でアクセスします。SQL文のCREATE USER MAPPINGで、外部サーバとローカルユーザーを対応づけます。また、FDWによってはオプションとして、リモート側のユーザーも指定できます。これにより、必要な場合はローカル側のユーザーにリモート側のユーザーを対応づけられます。 |
| FOREIGN TABLE (外部テーブル) |
SQL文のCREATE FOREIGN TABLEで、外部テーブルを定義します。リモート側のテーブルと同じ列定義を指定します。 |
参考
外部テーブルの作成は、CREATE FOREIGN TABLE以外に、SQL文のIMPORT FOREIGN SCHEMAを使用することで、接続先の表定義を取り込むことができます。リモート側のスキーマ単位にローカル側で一括して外部テーブルが作成されるため、同一スキーマ内に複数の外部テーブルを作成する場合に便利です。
2.3 プッシュダウンの利用
FDWにはプッシュダウン（pushdown）という機構があります。プッシュダウンは、クライアントから問い合せのあったSQL文に含まれるWHERE句（絞り込み処理）、ORDER BY句（ソート処理）などをリモート側で実行させる機構です。例えば、WHERE句のプッシュダウンは、リモート側で対象データの絞り込み処理が行われるため、ローカルとリモートとの間のデータ転送量を抑え、通信におけるボトルネックを減少させる効果があります。具体的には、1000行の外部データに対してWHERE句で絞り込み条件を指定して5行が抽出される処理において、プッシュダウンなしの場合と、プッシュダウンありの場合では、以下のようにデータ転送量が200分の1に抑えられます。
なお、FDWの種類によっては、プッシュダウンの対象となる句が異なります。また、プッシュダウン対象の句の組み合わせによっては、プッシュダウンされないケースもありますので、利用するFDWのドキュメントなどで仕様を確認してください。
2.4 利用時のポイント
ここでは、主にpostgres_fdwを例に挙げ、FDWを利用するうえでのポイントについて、いくつかご紹介します。なお、他のFDWではポイントが異なる場合がありますので、別途FDWのドキュメントを確認してください。
更新トランザクションの制御
外部テーブルに対する更新トランザクションの利用において、留意する点があります。
- リモート側のCOMMITのタイミング
ローカル側で外部テーブルに対して実施された更新は、ローカル側でCOMMITを発行するとリモート側に反映されます。リモート側で更新が成功してCOMMITされると、ローカル側でもCOMMITされます。ロールバックも同様です。なお、FDWは、2相コミットやプリペアステートメントに対応していませんので、注意してください。 - トランザクション分離レベル
外部テーブルに対して発行したSQL文が単一のSQL文であっても、FDWを介してリモート側に発行される際に、複数のSQL文となる場合があります。このとき、リモート側ではトランザクションの一貫性を保つためにREPEATABLE READやSERIALIZABLEで動作します。ローカル側とリモート側のトランザクション分離レベルを一致させない場合、ローカル側とリモート側でトランザクションの実行結果が異なる可能性があります。
統計情報の最新化
統計情報を最新化することは、最適な実行計画作成のために重要です。通常のテーブルでは、自動バキュームによりANALYZEコマンドも自動的に実行され、統計情報が最新化されます。しかし、外部テーブルに対しては、自動バキュームによるANALYZEコマンドが実行されません。したがって、別途、ANALYZEコマンドを実行する必要があります。なお、オプションの指定により、外部テーブルにアクセスするタイミングで、統計情報の最新化を同時にできるFDWもあります。
連携先へのアクセス時のデータ型変換
連携先がPostgreSQL以外のデータソースの場合、PostgreSQLとはサポートしているデータ型の種類や仕様の違いがあります。FDWでは、外部テーブルと連携先のテーブルとのデータ受け渡し時に、FDWがデータを自動で変換しますが、正しく変換されない場合があります。例えば、oracle_fdwにおいて、文字データ型のVARCHAR(n)を利用する場合、PostgreSQLではnを文字数として扱い、Oracleではnをバイト数として扱うという違いがあります。そのため、データ変換が行われた際に容量を超えると、エラーが発生します。
パーティショニングと組み合わせた利用形態
FDWと、テーブルのパーティショニング機能とを組み合わせることで、複数サーバに分散して格納された外部データを、外部テーブル（パーティション）を介してパーティションテーブルに集約するといった使い方ができます。例えば、各支店にある売上情報を本店のサーバにあるパーティションテーブルに集約して、効率よく参照したり、分析したりするようなユースケースが考えられます。
備考 パーティショニングは、テーブルを分割することで検索性能やメンテナンス性を向上させる機能です。
3．まとめ
ここまで、FDWによって様々なデータソースにアクセスし、外部データの参照や更新系のSQLを実行できることを説明しました。ただし、更新系SQLやプッシュダウンの対応は、FDWごとに異なります。
以下に、代表的なFDWの例をまとめました。表中「○」はサポートあり、「-」はサポートなしを表しています。
| データソース | 対応するFDW | 版数 | 参照系SQL | 更新系SQL | プッシュダウンの対応 | ||
|---|---|---|---|---|---|---|---|
| WHERE句 | ORDER BY句 | JOIN句 | |||||
| PostgreSQL | postgres_fdw | PostgreSQL 11.1に同梱 | ○ | ○ | ○ | ○ | ○ |
| CSV | file_fdw | PostgreSQL 11.1に同梱 | ○ | - | - | - | - |
| Oracle | oracle_fdw | 2.1.0 | ○ | ○ | ○ | ○ | ○ |
| MySQL | mysql_fdw | 2.5.3 | ○ | ○ | ○ | - | - |
| Microsoft SQL Server | tds_fdw | 1.0.8 | ○ | - | ○ | - | - |
| MongoDB | mongo_fdw | 5.1.0 | ○ | ○ | ○ | - | - |
| LDAP | ldap_fdw | 0.1.1 | ○ | - | - | - | - |
| Apache Hadoop | hdfs_fdw | 2.0.5 | ○ | - | ○ | - | - |
出典：https://www.pgecons.org/
http://creativecommons.org/licenses/by/2.1/jp/
上記に示した更新系SQLやプッシュダウン以外にも、FDWによって利用可能な操作やオプションが異なります。また、機能以外にもライセンス形態やサポート体制も異なりますので、FDWを利用する際は、業務で使用するのに問題ないライセンス形態であることや、トラブルやQAが生じた際の対応を、事前に検討することをお勧めします。
外部リンク
下記記事も参照ください。更に詳しい解説があります。
- Oracleデータベースにアクセスする～oracle_fdwの基本的な使い方～ (PostgreSQLインサイド）
- Oracleデータベースにアクセスする～oracle_fdwを使いこなすために～ (PostgreSQLインサイド)
- パーティショニングの概要 (PostgreSQLインサイド)
本ページ掲載情報を無断で複製・転載する事を禁止します。