oracle_fdw を使ってPostgreSQLからOracle ADWへのデータ連携方式の検証

#はじめに
PostgreSQLには、外部データの種類に応じた外部データラッパー（ Foreign Data Wrapper : FDW）を適用することで、様々な外部データをPostgreSQL内のテーブルと同様に扱うことができます。
oracle_fdw は外部データラッパーの1つで Oracle Database に対応しています。
この記事は、oracle_fdw を使ってPostgreSQLからOracle ADW（Autonomous Data Warehouse）へのデータ連携方式を検証します。

#検証環境に関する説明
- Oracle ADW (18c)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/a1ab004f-f4f6-c9df-c3ae-a14f8c007fb9.png)
- PostgreSQLサーバー（10.11）
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/c0c1e853-fdcb-116f-7c35-20a91d6fdc96.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/388ce26d-649b-25e1-fbf8-7344512c8910.png)

:pushpin:　oracle_fdw を利用する場合、PostgreSQLのバージョンは9.1以降になる必要があります。

- 補足説明

PostgreSQLサーバーは利用者のオンプレミス環境に存在する場合、 ADW へのアクセスはインターネット経由、または VPN 経由のいずれも可能です。接続のイメージを下記の構成図で示されます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/1acbb0a6-8752-0a41-62de-180b8182dc8c.png)

ADW へのアクセスは、TCPS プロトコルを利用しますから、インターネット経由で接続する場合も、データの安全性を確保できます。
より安全性がもっと高い VPN 経由での接続について、利用者のオンプレミス環境のネットワーク構成に応じて、 VPN 接続の設定を行う必要があります。詳細については、オラクル社の資料 [VPN Connect](https://docs.cloud.oracle.com/iaas/Content/Network/Tasks/managingIPsec.htm) をご参照ください。

#oracle_fdw の導入
- OCI ライブラリのインストール

oracle_fdw は OCI（Oracle Call Interface）ライブラリを使って Oracle ADW にアクセスします。
そのため、PostgreSQL稼動サーバに OCI ライブラリのインストールが必要になります。本記事では Oracle Instant Client 18.5をインストールします。
Oracle Instant Clientは (https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html) からダウンロードします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/e365be3c-1c6d-aba2-8c97-a1c848b29514.png)

:pushpin:　oracle_fdw を利用する場合、Oracle Instant Client のバージョンは10.1以上になる必要があります。

opcのhomeディレクトリ (/home/opc) にて、ZIPファイルを解凍します。解凍後に「instantclient_18_5」ディレクトリが生成されました。

```bash
$ unzip instantclient-basic-linux.x64-18.5.0.0.0dbru.zip
$ unzip instantclient-sdk-linux.x64-18.5.0.0.0dbru.zip
$ unzip instantclient-sqlplus-linux.x64-18.5.0.0.0dbru.zip
$ cd instantclient_18_5
$ ls -a
```

必要なモジュールやOCIライブラリの準備ができたことを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/42f1dd95-35e0-4daf-d949-9341928aed09.png)

- ADW Walletの準備
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/8444fa7b-9a7b-e53e-1903-a3317b793fe0.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/56af2b00-cb7e-57aa-dde0-d8446d5e67d5.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/89cb5605-2a73-1963-4051-ad6e2a51ea25.png)

ダウンロードした ADW Wallet のZIPファイルをPostgreSQLサーバー (/home/opc) にアップロードします。
本記事では、下記のようにopcのhomeディレクトリの下にADW Walletを格納するディレクトリ (/home/opc/adw_wallet) を作成し、アップロードじた ADW Wallet のZIPファイルを解凍します。

```bash
$ mkdir adw_wallet
$ mv Wallet_kyokuADW.zip /home/opc/adw_wallet
$ cd adw_wallet 
$ unzip Wallet_kyokuADW.zip
```

解凍された「sqlnet.ora」ファイルを編集します(実際のADW Walletの格納先を指定)。

```bash
$ vi sqlnet.ora
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/943b8253-fbe9-000a-e16c-fe2aa254a5ed.png)

- oracle_fdwのインストール

oracle_fdwのページ（https://github.com/laurenz/oracle_fdw) からリポジトリ上の最新コードをZIP形式でダウンロードすることにします。
ダウンロードした oracle_fdw のZIPファイルをPostgreSQLサーバー (/home/opc) にアップロードします。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/b258037d-05ca-aed3-c474-78727c1abb7a.png)
root ユーザーで環境変数の設定、oracle_fdwのインストール、ライブラリ構成の追加や変更の反映など一連の操作を行います。

```bash
$ sudo -s
# cd ~
# vi .bash_profile
```
以下の内容を「.bash_profile」ファイルの最後に追加します。

```shell
export PATH=$PATH:/home/opc/instantclient_18_5:/usr/pgsql-10/bin
export ORACLE_HOME=/home/opc/instantclient_18_5
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include
export LD_LIBRARY_PATH=$ORACLE_HOME
export TNS_ADMIN=/home/opc/adw_wallet
```
設定した環境変数を有効にして、引き続き後の操作を行います。

```bash
# source .bash_profile
# cd /home/opc
# unzip oracle_fdw-master.zip
# cd oracle_fdw-master
# make
# make install
# vi /etc/ld.so.conf.d/postgresql-pgdg-libs.conf
```
以下の内容を「postgresql-pgdg-libs.conf」ファイルに追加します。

```shell
/usr/pgsql-10/lib/
/home/opc/instantclient_18_5/
```
root ユーザーで ldconfig コマンドを実行します。

```bash
# ldconfig
```
oracle_fdw のインストールは完了後に、ライブラリの依存関係が正しく構築されたことを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/b350f8e6-5425-f412-f2f0-7b241faef99f.png)

#ADWへのアクセス

postgres ユーザーで環境変数の設定、PostgreSQLサーバーの再起動、ADW に存在するテーブルをPostgreSQLの外部テーブルに定義する処理など一連の操作を行います。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/b8df45d7-8441-5ce2-616d-37d1701888ac.png)

- 環境変数の設定

```bash
$ vi .bash_profile
```
以下の内容を「.bash_profile」ファイルの最後に追加します。

```shell
export PATH=$PATH:/home/opc/instantclient_18_5:/usr/pgsql-10/bin
export ORACLE_HOME=/home/opc/instantclient_18_5
export OCI_LIB_DIR=$ORACLE_HOME
export OCI_INC_DIR=$ORACLE_HOME/sdk/include
export LD_LIBRARY_PATH=$ORACLE_HOME
export TNS_ADMIN=/home/opc/adw_wallet
```
設定した環境変数を有効にします。

```bash
$ source .bash_profile
```

- PostgreSQLサーバーの再起動

```bash
$ pg_ctl restart 
```
- PostgreSQLの外部テーブルの作成

```bash
$ psql postgres
psql (10.11)
Type "help" for help.

postgres=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
postgres=# \dx oracle_fdw
                      List of installed extensions
    Name    | Version | Schema |              Description
------------+---------+--------+----------------------------------------
 oracle_fdw | 1.1     | public | foreign data wrapper for Oracle access
(1 row)
```
ここまで、PostgreSQL から ADW へのデータ連携の準備ができました。
下記の図で示されている Oracle ADW 側にある TEST テーブルをPostgreSQLの外部テーブルとして定義してみましょう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/d598aefa-1bbe-7f55-8e5f-f2cba3283df4.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/978fcff4-1be4-2c07-8810-f24df530c152.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/170f377d-6f85-6b57-8196-c0d2a36d8edb.png)

Oracle ADW にアクセスするための接続情報を外部サーバー(**oradb_adw**)として定義します。

```bash
postgres=# create server oradb_adw foreign data wrapper oracle_fdw options(dbserver 'kyokuadw_high');
CREATE SERVER
postgres=# \des oradb_adw
           List of foreign servers
   Name    |  Owner   | Foreign-data wrapper
-----------+----------+----------------------
 oradb_adw | postgres | oracle_fdw
(1 row)
```
ユーザー postgres が定義した外部サーバー(**oradb_adw**)を使用できるように、権限を付与します。

```bash
postgres=# grant usage on foreign server oradb_adw to postgres;
GRANT
```
外部サーバー（**oradb_adw**）とユーザ（**postgres**）の対応付けの定義を行います。

```bash
postgres=# create user mapping for postgres server oradb_adw options(user 'ADMIN',password 'admin_user_pw');
CREATE USER MAPPING
postgres=# \deu[+] oradb_adw
                       List of user mappings
  Server   | User name |                FDW options
-----------+-----------+--------------------------------------------
 oradb_adw | postgres  | ("user" 'ADMIN', password 'admin_user_pw')
(1 row)
```
:pushpin: 上記の "admin_user_pw" = ADW の ADMIN ユーザーの実際のパスワード

Oracle ADW 上のテーブルをPostgreSQLの外部テーブルとして定義します。ここでは Oracle ADW 上のテーブル TEST を外部テーブル adw_test として定義します。

```bash
postgres=# create foreign table ADW_TEST(
postgres(# id int options(key 'true') not null,
postgres(# item text,
postgres(# upd_date date
postgres(# ) server oradb_adw options(schema 'ADMIN', table 'TEST');
CREATE FOREIGN TABLE
postgres=# \det[+] adw_test
                             List of foreign tables
 Schema |  Table   |  Server   |           FDW options            | Description
--------+----------+-----------+----------------------------------+-------------
 public | adw_test | oradb_adw | (schema 'ADMIN', "table" 'TEST') |
(1 row)
```
ここまで、Oracle ADW 上のテーブル（**TEST**）が PostgreSQL の外部テーブル（**adw_test**）として定義しました。

#差分データの挿入例

Oracle ADW 上のテーブルが PostgreSQL の外部テーブルとして定義されたことを踏まえて、PostgreSQL から Oracle ADW へのデータ連携は簡単に実現できます。
本記事では、 PostgreSQL 側の日付別のデータを差分データとして ADW 側に追加する例（差分データの挿入例）を紹介します。

まずは、PostgreSQL から ADW に差分データ挿入前の状況を再確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/46ad09bc-3b30-6099-9384-2cddf432bf0b.png)

続いては、PostgreSQL 側の日付別のデータを差分データとして ADW 側に追加します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/fdf6e71d-c7a8-4c58-c8b2-166ca72ff9c8.png)

差分データの投入は、確かに ADW 側に反映したことを確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/548735/1f292163-74db-fe6c-eb67-b0c7e3e195f7.png)

#参照資料
- [oracle_fdw を使ってみる（前編）](https://www.sraoss.co.jp/tech-blog/pgsql/using_oracle_fdw_1/)
- [GitHub - Foreign Data Wrapper for Oracle](https://github.com/laurenz/oracle_fdw)