AlloyDBからクロスクラウド接続でOracle DatabaseのMViewを作ってみた

# はじめに

Google CloudとOCIをクロスクラウド接続し、その接続経由で AlloyDB for PostgreSQL から Oracle Database(BaseDB)へ oracle_fdw を利用してリンクし、Oracle Databaseのテーブルを Materialized View(以下MView) として AlloyDB上に作成してみた話になります。

Google CloudとOCIはクロスクラウド接続で直接接続することが可能です。
- [Google Cloud から クロスクラウド接続で OCI のデータベースに接続してみた](https://qiita.com/asahide/items/fe0b33928b676dc1a760)

なお、Google Cloud上のCloud SQLやAlloyDBはユーザーのVPCではなく Google Cloud のInternal VPCに配置されるため、クロスクラウド接続を行っただけでは直接接続できず、カスタムルートなどの設定が必要になります。

- [Cloud SQLからクロスクラウド経由でOCIのOracle Databaseへ直接接続してみた](https://qiita.com/asahide/items/a66534d2fd30ec29711a)

# テスト構成

Google Cloud上のAlloyDBからクロスクラウド接続経由でOCIのOracle Database(BaseDB)へリンク(oracle_fdw)で接続しています。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2313926/0e12c9bd-30e7-035c-8db8-b04342686d88.png)


# AlloyDBからOracle Databaseへの接続

Google CloudとOCIの間のクロスクラウド接続が設定されていることが前提となります。

#### カスタムルート設定

AlloyDBのIPは **10.102.97.25** となります。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2313926/6b36a90d-298e-5ed8-fd7f-bd7f35f9355b.png)

カスタムルートは **10.102.97.0/24** として設定します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2313926/25728f85-75e0-e8fb-0f85-2a8b281bcf1b.png)

VPCネットワークピアリングは設定してあるため、OCI側のルート表を確認します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2313926/708d9012-5d15-51b7-bbd8-b98a797fe577.png)

#### AlloyDBとOracle Databsaeのリンク設定

AlloyDBにOracle fdw イクステンションをインストールし、Oracle Database側の ORA_TBL を外部テーブルとして設定します。

```SQL:Extensionのインストールと確認

$ psql -h 10.102.97.25 -p 5432 -U postgres -d postgres
Password for user postgres: 
psql (10.23, server 16.3)
WARNING: psql major version 10, server major version 16.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=> CREATE EXTENSION oracle_fdw;
CREATE EXTENSION

postgres=> \dew+
                                                   List of foreign-data wrappers
    Name    |  Owner   |      Handler       |      Validator       | Access privileges | FDW options |         Description         
------------+----------+--------------------+----------------------+-------------------+-------------+-----------------------------
 oracle_fdw | postgres | oracle_fdw_handler | oracle_fdw_validator |                   |             | Oracle foreign data wrapper
(1 row)

postgres=> SELECT name, default_version FROM pg_available_extensions WHERE name LIKE 'oracle%';
    name    | default_version 
------------+-----------------
 oracle_fdw | 1.2
(1 row)

postgres=> SELECT oracle_diag();
                         oracle_diag                          
--------------------------------------------------------------
 oracle_fdw 2.6.0, PostgreSQL 16.3, Oracle client 21.10.0.0.0
(1 row)

```

```SQL:外部サーバーとユーザマッピングの作成

postgres=> CREATE SERVER ora_server FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '192.168.1.25:1521/PDBTEST.testsub.testvpc.oraclevcn.com');
CREATE SERVER

postgres=> \des+
                                                                        List of foreign servers
    Name    |  Owner   | Foreign-data wrapper | Access privileges | Type | Version |                             FDW options                              | Description 
------------+----------+----------------------+-------------------+------+---------+----------------------------------------------------------------------+-------------
 ora_server | postgres | oracle_fdw           |                   |      |         | (dbserver '192.168.1.25:1521/PDBTEST.testsub.testvpc.oraclevcn.com') | 
(1 row)

postgres=> CREATE USER MAPPING FOR postgres SERVER ora_server OPTIONS (user 'HR', password '********');
CREATE USER MAPPING

postgres=> \deu+
                       List of user mappings
   Server   | User name |               FDW options                
------------+-----------+------------------------------------------
 ora_server | postgres  | ("user" 'HR', password '********')
(1 row)
```

```SQL:外部テーブルの作成
postgres=> CREATE FOREIGN TABLE f_ora_tbl(
postgres(>              id integer OPTIONS (key 'true'),
postgres(>              name varchar(128),
postgres(>              update_time timestamp)
postgres->              SERVER ora_server OPTIONS (schema 'HR',table 'ORA_TBL');
CREATE FOREIGN TABLE

postgres=> \det+ 
                              List of foreign tables
 Schema |   Table   |   Server   |           FDW options            | Description 
--------+-----------+------------+----------------------------------+-------------
 public | f_ora_tbl | ora_server | (schema 'HR', "table" 'ORA_TBL') | 
(1 row)

postgres=> SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'f_ora_tbl' order by 1;
 column_name |          data_type          
-------------+-----------------------------
 id          | integer
 name        | character varying
 update_time | timestamp without time zone
(3 rows)
```

```SQL:Oracle Database側のORA_TBL定義
SQL> desc ora_tbl
 Name            Null?    Type
 --------------- -------- ----------------
 ID              NOT NULL NUMBER
 NAME                     VARCHAR2(128)
 UPDATE_TIME              TIMESTAMP(6)
 ```
#### MViewの作成

まずは現在の f_ora_tbl(Oracle側:ORA_TBL) の状態を確認します。

```SQL
postgres=> select * from f_ora_tbl order by id;
 id |   name   |        update_time         
----+----------+----------------------------
  1 | asahide  | 2024-09-28 11:12:42.26059
  2 | hogehoge | 2024-09-28 11:26:01.636484
  3 | mvtest   | 2024-09-29 00:48:34.099975
  4 | oratest  | 2024-09-29 09:52:06
(4 rows)
```

f_ora_mvという名前のMViewをf_ora_tblという外部テーブルから作成します。

```SQL:MView作成
postgres=> CREATE MATERIALIZED VIEW f_ora_mv as select * from f_ora_tbl;
SELECT 4
postgres=> commit;
WARNING:  there is no transaction in progress
COMMIT

postgres=> select * from f_ora_mv order by id;
 id |   name   |        update_time         
----+----------+----------------------------
  1 | asahide  | 2024-09-28 11:12:42.26059
  2 | hogehoge | 2024-09-28 11:26:01.636484
  3 | mvtest   | 2024-09-29 00:48:34.099975
  4 | oratest  | 2024-09-29 09:52:06
(4 rows)
```

f_ora_tbl へ新しいデータを挿入します。

```SQL:外部テーブルへのデータ挿入と確認
postgres=> insert into f_ora_tbl values(5,'mvtest2',current_timestamp);
INSERT 0 1
postgres=> commit;
WARNING:  there is no transaction in progress
COMMIT

postgres=> select * from f_ora_tbl order by id;
 id |   name   |        update_time         
----+----------+----------------------------
  1 | asahide  | 2024-09-28 11:12:42.26059
  2 | hogehoge | 2024-09-28 11:26:01.636484
  3 | mvtest   | 2024-09-29 00:48:34.099975
  4 | oratest  | 2024-09-29 09:52:06
  5 | mvtest2  | 2024-09-29 01:41:47.675034
(5 rows)
```

MViewの状態を確認します、MView側ではデータは反映されてません。

```SQL:MViewの状態確認
postgres=> select * from f_ora_mv order by id;
 id |   name   |        update_time         
----+----------+----------------------------
  1 | asahide  | 2024-09-28 11:12:42.26059
  2 | hogehoge | 2024-09-28 11:26:01.636484
  3 | mvtest   | 2024-09-29 00:48:34.099975
  4 | oratest  | 2024-09-29 09:52:06
(4 rows)
```

リフレッシュを行うとデータが反映されます。
なお、現時点では pg_ivmはサポートされていないため、完全リフレッシュ(全データの更新)のみとなります。

```SQL:Refreshの実行
postgres=> REFRESH MATERIALIZED VIEW f_ora_mv;
REFRESH MATERIALIZED VIEW

postgres=> select * from f_ora_mv order by 1;
 id |   name   |        update_time         
----+----------+----------------------------
  1 | asahide  | 2024-09-28 11:12:42.26059
  2 | hogehoge | 2024-09-28 11:26:01.636484
  3 | mvtest   | 2024-09-29 00:48:34.099975
  4 | oratest  | 2024-09-29 09:52:06
  5 | mvtest2  | 2024-09-29 01:41:47.675034
(5 rows)
```

無事MView環境を構築することができました。

# まとめ

集計データを利用したい場合に利用されるMViewですが、クロスクラウド接続でも利用できることが確認できました。
Oracle Databaseに入っているデータを AlloyDB から参照したい、という場合に利用しやすくなると想定されますね。

#### おまけ

今回は AlloyDB から Oracle Databaseにデータを入れて、それを確認する、という形でした。
ユースケースとしては Oracle Database側でデータ更新を行ってそれをAlloyDB側でリフレッシュして利用する、という形の方がスタンダードになると思いますので、その形も確認してみました。

```SQL:Oracle Database側でのデータインサート
SQL> select * from ora_tbl order by id;

        ID NAME       UPDATE_TIME
---------- ---------- ------------------------------
         1 asahide    28-SEP-24 11.12.42.260590 AM
         2 hogehoge   28-SEP-24 11.26.01.636484 AM
         3 mvtest     29-SEP-24 12.48.34.099975 AM
         4 oratest    29-SEP-24 09.52.06.000000 AM
         5 mvtest2    29-SEP-24 01.41.47.675034 AM

SQL>  insert into ora_tbl values (6, 'oratest2', sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from ora_tbl order by id;

        ID NAME       UPDATE_TIME
---------- ---------- ------------------------------
         1 asahide    28-SEP-24 11.12.42.260590 AM
         2 hogehoge   28-SEP-24 11.26.01.636484 AM
         3 mvtest     29-SEP-24 12.48.34.099975 AM
         4 oratest    29-SEP-24 09.52.06.000000 AM
         5 mvtest2    29-SEP-24 01.41.47.675034 AM
         6 oratest2   29-SEP-24 10.43.50.000000 AM

6 rows selected.
```

AlloyDB側でリフレッシュを行うとデータが反映されていることが確認できますね。
タイムゾーンとかがずれているため時刻がずれてしまってますが。。

```SQL:AlloyDB側でのリフレッシュ確認
postgres=> select * from f_ora_mv order by id;
 id |   name   |        update_time         
----+----------+----------------------------
  1 | asahide  | 2024-09-28 11:12:42.26059
  2 | hogehoge | 2024-09-28 11:26:01.636484
  3 | mvtest   | 2024-09-29 00:48:34.099975
  4 | oratest  | 2024-09-29 09:52:06
  5 | mvtest2  | 2024-09-29 01:41:47.675034
(5 rows)

postgres=> REFRESH MATERIALIZED VIEW f_ora_mv;
REFRESH MATERIALIZED VIEW

postgres=> select * from f_ora_mv order by id;
 id |   name   |        update_time         
----+----------+----------------------------
  1 | asahide  | 2024-09-28 11:12:42.26059
  2 | hogehoge | 2024-09-28 11:26:01.636484
  3 | mvtest   | 2024-09-29 00:48:34.099975
  4 | oratest  | 2024-09-29 09:52:06
  5 | mvtest2  | 2024-09-29 01:41:47.675034
  6 | oratest2 | 2024-09-29 10:43:50
(6 rows)
```