Oracleデータベースにアクセスする ~oracle_fdwの基本的な使い方~
PostgreSQLインサイド

PostgreSQLには、PostgreSQLの外部にある様々なデータに対してアクセスするための仕組みとして、外部データラッパ(FDW: Foreign Data Wrapper)が用意されています。その概要については、「外部データとの連携 ~FDWで様々なデータソースとつなぐ~」で紹介しています。今回は、Oracleデータベース用の外部データラッパであるoracle_fdwを利用して、Oracleデータベースにアクセスする方法について解説します。

1. oracle_fdwとは

oracle_fdwとは、Oracleデータベースに対応した外部データラッパです。Oracleデータベース上のテーブルやビュー(マテリアライズド・ビューを含む)に対応する外部テーブルを作成し、SELECT文やINSERT文などのSQL文でアクセスすることで、Oracleデータベース上のデータを扱えるようになります。利用イメージを図1に示します。
oracle_fdwは、PostgreSQLクライアントから外部テーブルにアクセスがあると、「Oracle Call Interface(以降、OCIと略します)ライブラリ」を経由して、外部にあるOracleデータベースのテーブルやビューのデータを参照/更新します。なお、OCIライブラリは、PostgreSQLサーバ上にインストールしておく必要があります。


図1 oracle_fdwの利用イメージ

oracle_fdwは、LinuxとWindowsに対応していますが、この記事ではLinux上での動作を前提に説明します。また、確認に使用した環境は、PostgreSQL 11.1、oracle_fdw 2.1.0、Oracle Instant Client 18.5(OCIライブラリ)、Oracle Database 18c XEです。

2. oracle_fdwの使い方

oracle_fdwを利用するための具体的な準備と利用手順について説明します。なお、PostgreSQLサーバには、PostgreSQL、oracle_fdw、およびOCIライブラリがインストールされているものとします。Oracleデータベースには、連携先のテーブルが存在し、リモート接続用のリスナー設定が完了しているものとします。

2.1 oracle_fdwを使うための準備

環境変数の設定

oracle_fdwからOCIライブラリを利用するために、以下の環境変数を設定します。

環境変数 説明 設定例
LD_LIBRARY_PATH OCIライブラリのインストール先のディレクトリを設定します。 /usr/lib/oracle/18.5/client64/lib
NLS_LANG 連携先Oracleデータベースの環境変数NLS_LANGと同じ値を設定します。 JAPANESE_JAPAN.AL32UTF8

Oracleデータベースにアクセスするための準備

oracle_fdwがOracleデータベースにアクセスするためには、図2のように、連携先の各種情報を4つの手順で設定します。なお、図中の括弧付き数字は、準備の手順を示しています。


図2 Oracleデータベースにアクセスするための準備

(1)oracle_fdwのエクステンション(EXTENTION)の作成

PostgreSQLのエクステンションとして、oracle_fdwを作成します。エクステンションの作成後には、外部データラッパのリストを表示して、正しく作成されていることを確認します。

$ psql
postgres=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
postgres=# \dew ← 外部データラッパのリストを表示する
                   List of foreign-data wrappers
    Name    |  Owner   |      Handler       |      Validator
------------+----------+--------------------+----------------------
 oracle_fdw | postgres | oracle_fdw_handler | oracle_fdw_validator
(1 row)

(2)外部サーバの作成

Oracleデータベースへの接続情報を外部サーバ(ora_sv)として定義します。オプションを使って外部サーバのホスト名(host_ora)、接続ポート(デフォルト値の1521)、データベースサービス名(XEPDB1)を指定します。外部サーバの作成後には、外部サーバのリストを表示して、正しく設定されていることを確認します。なお、ホスト名の名前解決が正しく行われるよう、事前にOSの設定を行っておきます。

postgres=# CREATE SERVER ora_sv FOREGIGN DATA WRAPPER oracle_fdw;
postgres-# OPTIONS (dbserver 'host_ora:1521/XEPDB1');
CREATE SERVER
postgres=# \des+ ← 外部サーバのリストを表示する
                                                      List of foreign servers
  Name  |  Owner   | Foreign-data wrapper |  Access privileges  | Type | Version |            FDW options            | Description
--------+----------+----------------------+---------------------+------+---------+-----------------------------------+-------------
 ora_sv | postgres | oracle_fdw           | postgres=U/postgres |      |         | (dbserver 'host_ora:1521/XEPDB1') |
(1 row)

さらに、PostgreSQL側で使用するローカルユーザー(postgres)が、定義された外部サーバ(ora_sv)を使用できるように、権限を付与します。

postgres=# GRANT USAGE ON FOREIGN SERVER ora_sv TO postgres;
GRANT

(3)ユーザマップの作成

ローカルユーザー(postgres)とOracle側のリモートユーザー(ora_user)を紐づけるため、外部サーバ(ora_sv)上にユーザマップを作成します。オプションでリモートユーザーのユーザー名とパスワードを指定します。ユーザマップの作成後には、ユーザマップのリストを表示して、正しく設定されていることを確認します。

postgres=# CREATE USER MAPPING FOR postgres SERVER ora_sv OPTIONS ( USER 'ora_user', PASSWORD 'xxxx');
CREATE USER MAPPING
postgres=# \deu+ ← ユーザマップのリストを表示する
                List of user mappings
 Server | User name |          FDW options
--------+-----------+--------------------------------------
 ora_sv | postgres  | ("user" 'ora_user', password 'xxxx')
(1 row)

(4)外部テーブルの作成

Oracleデータベース上のテーブルやビューに対応する、外部テーブル(f_ora_tbl)を作成します。なお、外部テーブルは、外部サーバ(ora_sv)上に関連づけて作成されます。外部テーブルの作成後には、外部テーブルのリストとテーブル定義を表示して、正しく設定されていることを確認します。

postgres=# CREATE FOREIGN TABLE f_ora_tbl(
postgres(# id int OPTIONS (key 'true'), ← 主キーカラムにkeyオプションを設定する
postgres(# name varchar(64),
postgres(# t_data timestamp)
postgres-# SERVER ora_sv OPTIONS (SCHEMA 'ORA_USER' , TABLE 'ORA_TBL'); ← スキーマ名、テーブル名は大文字で指定する
CREATE FOREIGN TABLE
postgres=# \det+ ← 外部テーブルのリストを表示する
                             List of foreign tables
 Schema |   Table    | Server |               FDW options              | Description
--------+------------+--------+----------------------------------------+-------------
 public | f_ora_tbl  | ora_sv | (schema 'ORA_USER', "table" 'ORA_TBL') |
(1 row)
postgres=# \d f_ora_tbl ← 外部テーブルのテーブル定義を表示する
                           Foreign table "public.f_ora_tbl"
 Column |            Type             | Collation | Nullable | Default | FDW options
--------+-----------------------------+-----------+----------+---------+--------------
 id     | integer                     |           |          |         | (key 'true')
 name   | character varying(64)       |           |          |         |
 t_data | timestamp without time zone |           |          |         |
Server: ora_sv
FDW options: (schema 'ORA_USER', "table" 'ORA_TBL')

外部テーブルを作成する上で、以下の注意点があります。

  • テーブルカラム定義は、Oracle側のテーブルカラムと同じ種類のデータ型を定義する必要があります。
    双方のデータベースのデータ型の仕様には違いがあります。外部テーブルとOracle側のテーブルとのデータ変換は、oracle_fdwによって自動で行われますが、正しく変換されるかどうか確認しておくことをお勧めします。
  • Oracle側のテーブルデータを更新する場合は、主キーカラムすべてにkeyオプションの設定が必要です。
    OPTIONS (key 'true')で設定します。
  • OPTIONSに指定するOracleデータベースのスキーマ名(ORA_USER)とテーブル名(ORA_TBL)は、大文字 / 小文字に注意して記述してください。
    Oracleデータベースでは、スキーマ名やテーブル名などの識別子をダブルクォーテーション無しで定義すると大文字で格納されます。そのため、連携先のOracleデータベースのスキーマ名やテーブル名がダブルクォーテーション無しで定義されていた場合は、外部テーブル作成時に大文字で記述する必要があります。なお、連携先のOracleデータベースにおいて、ダブルクォーテーションを付けてスキーマ名やテーブル名が定義されていた場合は、その定義どおりに記述します。なお、外部テーブル作成時に指定するスキーマ名とテーブル名は、コマンド構文の規則上、シングルクォーテーションを付けます。

参考

外部テーブルを作成する際に、スキーマ名とテーブル名をOracleデータベース上の定義どおりに指定しなくても、その時点ではエラーになりません。しかし、その外部テーブルにアクセスしたタイミングでエラーになります。以下に、その実行例を示します。

postgres=# CREATE FOREIGN TABLE f_ora_tbl2( id int OPTIONS(key 'true'), name varchar(64), t_data timestamp) SERVER ora_sv OPTIONS (SCHEMA 'ora_user', TABLE 'ora_tbl');
CREATE FOREIGN TABLE ← Oracle側にて大文字で定義されているスキーマ名とテーブル名を、小文字で指定してもエラーにならない

postgres=# SELECT * FROM f_ora_tbl2;
ERROR:  Oracle table "ora_user"."ora_tbl" for foreign table "f_ora_tbl2" does not exist or does not allow read access
DETAIL:  ORA-00942: 表またはビューが存在しません。
HINT:  Oracle table names are case sensitive (normally all uppercase).
 ← 外部テーブルf_ora_tbl2にアクセスしたタイミングでエラーになる

2.2 oracle_fdwを利用したOracleデータベースへのアクセス

外部テーブルを使うための準備ができましたので、次に、外部テーブルに対してSQL文の問合せを発行し、Oracleデータベースのテーブルにアクセスできることを確認します。図3に、oracle_fdwを利用したOracleデータベースへのアクセスについて、その処理の流れを示します。


図3 oracle_fdwを利用したOracleデータベースへのアクセス

(1)クライアントは、PostgreSQLに、外部テーブルに対するSQL文の問合せを行う
(2)PostgreSQLは、oracle_fdwに、Oracleデータベースの実行計画やテーブルデータの取得を依頼する
(3)oracle_fdwは、Oracle側のテーブルにアクセスするために必要な情報を、「外部サーバ」、「ユーザマップ」から取得する
(4)oracle_fdwは、OCIライブラリを経由してOracleデータベースにSQL文で問合せを行い、実データにアクセスする
(5)oracle_fdwは、Oracleデータベースから実行結果を取得し、PostgreSQLに結果を返す
(6)クライアントは、SQL文の実行結果をPostgreSQLから受け取る

実際にSQL文の問合せを行い、作成した外部テーブルの参照(SELECT)と更新(UPDATE)について確認します。

SELECT文で外部テーブル(f_ora_tbl)を参照します。

postgres=# SELECT * FROM f_ora_tbl;
 id | name |           t_data
----+------+----------------------------
  1 | abc  | 2020-01-17 21:26:01.129322
  2 | LMN  | 2020-01-17 21:26:07.786242
  3 | XYZ  | 2020-01-17 21:26:13.957481
(3 rows)

ここで、EXPLAINコマンドを使ってSELECT文の実行計画を表示して、Oracle側のテーブルにアクセスしていることを確認します。スキャン方式が「Foreign Scan」になっているところが、実際にOracle側のテーブルにアクセスする箇所になります。また、EXPLAINコマンドにANALYZEオプションを指定することにより、Oracle側で実行されるSQL文が確認でき(Oracle query: の箇所)、VERBOSEオプションを指定することにより、Oracle側での実行計画が確認できます(Oracle plan: の箇所)。

postgres=# EXPLAIN ANALYZE VERBOSE SELECT * FROM f_ora_tbl;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.f_ora_tbl  (cost=10000.00..20000.00 rows=1000 width=158) (actual time=0.919..0.963 rows=3 loops=1)
   Output: id, name, t_data
   Oracle query: SELECT /*d140cafb7e66e4d3c724281a0f23f54d*/ r1."ID", r1."NAME", r1."T_DATE" FROM "ORA_USER"."ORA_TBL" r1
    ← Oracle側で実行されるSQL文
   Oracle plan: SELECT STATEMENT ← Oracle側での実行計画
   Oracle plan:   TABLE ACCESS FULL ORA_TBL ← Oracle側での実行計画
 Planning Time: 1.719 ms
 Execution Time: 1.019 ms
(7 rows)

次に、UPDATE文で外部テーブル(f_ora_tbl)を更新し、データが更新されていることを確認します。

postgres=# UPDATE f_ora_tbl SET name = 'pot', t_date = CURRENT_TIMESTAMP WHERE id = 2;
UPDATE 1
postgres=# SELECT * FROM f_ora_tbl;
 id | name |           t_date
----+------+----------------------------
  1 | abc  | 2020-01-17 21:26:01.129322
  2 | pot  | 2020-01-20 16:58:32.359565 ← nameカラム、t_dateカラムのデータが更新されます
  3 | XYZ  | 2020-01-17 21:26:13.957481
(3 rows)

3. oracle_fdwを利用する上でのポイント

oracle_fdwの仕組みを正しく理解しないで利用した場合、外部テーブルへのアクセスに非常に時間が掛かったり、想定どおりの結果が得られなかったりすることがあります。ここでは、利用上の重要なポイントについて概要を述べます。詳細については、「Oracleデータベースにアクセスする ~oracle_fdwを使いこなすために~」で解説します。

利用上のポイント 説明
プッシュダウンの仕組み プッシュダウンとは、クライアントから問合せのあったSQL文に含まれる「WHERE句」などの部分的な処理を、リモート側で実行させる機構です。以下の句は、oracle_fdwのプッシュダウンの対象となり、条件に応じてOracle側で実行されます。
「WHERE句」は、Oracle側で処理されます。
「ORDER BY句」は、対象カラムのデータ型が数値型のようにPostgreSQLとOracleデータベース間でソート順が同じ場合、かつ、JOIN句が無い場合に、Oracle側で処理されます。
「JOIN句」は、SELECT文において、同一外部サーバ上の2つまでのテーブル結合であれば、Oracle側で処理されます。
なお、「WHERE句」「JOIN句」は、ローカルとリモートとの間のデータ転送量を抑え、通信におけるボトルネックを減少させる効果があります。
更新トランザクションの利用 oracle_fdwからOracleデータベースに問合せる際には、トランザクション分離レベルSERIALIZABLEで実行されます。そのため、1つの外部テーブルに対して複数のトランザクションから同時に更新すると、シリアライズ失敗によるエラーが発生することがあるため、注意が必要です。
また、プリペアドステートメント(PREPARE)、2相コミット(PREPARE TRANSACTIONなど)はサポートされません。
データ型の違い 外部テーブルを作成する際のカラムのデータ型は、Oracle側のカラムと同じ種類のデータ型で定義しますが、データ長や、端数の丸め方などに違いがあるため、注意が必要です。もし、文字型カラムにおいて、データ長を超えるようなアクセスがあると、ランタイムエラーが発生します。
外部テーブル定義の制約とデフォルト値 外部テーブルで定義する制約(CHECK句、NOT NULL句など)やデフォルト値(DEFAULT句)は、Oracle側のテーブル定義に合わせることを推奨します。定義を合わせない場合、外部テーブル上の制約と、テーブル内のデータの状態に矛盾が発生すると、外部テーブルのUPDATEやDELETEができなくなる可能性があります。

4. 外部テーブルの一括作成機能

PostgreSQLの外部データラッパには、外部テーブルをまとめて作成してくれる便利な機能があります。通常、CREATE FOREIGN TABLEコマンドを使用して外部テーブルを作成する際には、連携先のOracleデータベースの個々のテーブルカラムについて、同様のデータ型を定義する必要があります。そのため、テーブル数が多くなると、非常に手間が掛かる作業になります。そこで、IMPORT FOREIGN SCHEMAコマンドを使用することで、指定されたスキーマに含まれるすべてのテーブルを対象に、外部テーブルを生成してくれます。また、このコマンドのオプションで、スキーマに属するテーブルを個別に指定したり、個別に除外したりすることも可能です。なお、DEFAULT句については適用されないため、別途、外部テーブル定義に追加する必要があります。

以下に、IMPORT FOREIGN SCHEMAコマンドを使用して、Oracleデータベース上のORA_USERスキーマにある4つのテーブルを、PostgreSQLのimp_schemaにインポートする例を示します。なお、Oracleデータベースのスキーマ(ORA_USER)を指定する際には、Oracleデータベースのシステムカタログに定義されている情報(基本的には大文字)と同様に指定する必要があるため、ダブルクォーテーションで囲みます。
コマンド実行後は、外部テーブルのリストを表示して、正しく作成されていることを確認します。

postgres=# CREATE SCHEMA imp_schema;
CREATE SCHEMA
postgres=# IMPORT FOREIGN SCHEMA "ORA_USER" FROM SERVER ora_sv INTO imp_schema;
IMPORT FOREIGN SCHEMA
postgres=# \det *.* ← 外部テーブルのリストを表示する
     List of foreign tables
   Schema   |   Table   | Server
------------+-----------+--------
 imp_schema | dept      | ora_sv ← インポートされた外部テーブル
 imp_schema | members   | ora_sv ← インポートされた外部テーブル
 imp_schema | ora_tbl   | ora_sv ← インポートされた外部テーブル
 imp_schema | personal  | ora_sv ← インポートされた外部テーブル
 public     | f_ora_tbl | ora_sv
(5 rows)

oracle_fdwを利用することにより、PostgreSQLからOracleデータベースのテーブルに直接アクセスできることが分かりました。さらに詳細を知りたい場合は、「Oracleデータベースにアクセスする ~oracle_fdwを使いこなすために~」を参照してください。PostgreSQLは、Oracleデータベース以外のデータベースや、RDB以外のデータソースとの連携が可能ですので、PostgreSQLの適用範囲を大きく広げることができます。適用シーンに合わせた利用をご検討ください。

2020年3月13日公開

本コンテンツに関するお問い合わせ

お電話でのお問い合わせ

Webでのお問い合わせ

当社はセキュリティ保護の観点からSSL技術を使用しております。

ページの先頭へ