[OCI]Autonomous Database : Amazon Redshiftに対するデータベース・リンクを作成してデータを参照してみた

# はじめに
Autonomous Databaseは、Amazon Redshift、MySQL、PostgreSQL、SnowflakeといったOracle Database以外の様々なデータベースに対してデーターベース・リンクが作成できます。

https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/database-links-other-databases-oracle-managed.html#GUID-9FBC138F-7B22-4D14-96CF-349ADE2959F5

現時点では、以下の前提条件があります。
・ターゲット・データベースがパブリック・インターネットからアクセスできること
・ターゲット・データベースが[こちら](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/database-links-other-databases-oracle-managed.html#GUID-51E46547-E6A3-4D12-8392-31723B37896B)に記載されているポートを使用していること
・ターゲット・データベースがSSL/TLS接続を許可する設定になっていること

ということで、今回はAutonomous DatabaseからAmazon Redshiftに対してデータベース・リンクを作成し、Autonomous DatabaseからAmazon Redshift内のデータを参照してみました。
# 1. Autonomous DatabaseのアウトバウンドIPアドレスの確認

Amazon RedshiftのファイアウォールでAutonomous Databaseからの接続を許可する設定をするために、Autonomous DatabaseのアウトバウンドIPアドレスを確認します。

Autonomous DatabaseのアウトバウンドIPアドレスを確認には以下のSQLを実行します。
```
SELECT json_value(cloud_identity, '$.OUTBOUND_IP_ADDRESS[0]') outbound_ip_address FROM v$pdbs;
```
SQL*PlusやDatabase Actionsを使用してAutonomous Databaseにadminユーザで接続し、上記のSQLを実行してみます。

```
SQL> SELECT json_value(cloud_identity, '$.OUTBOUND_IP_ADDRESS[0]') outbound_ip_address FROM v$pdbs;

OUTBOUND_IP_ADDRESS
--------------------------------------------------------------------------------
xxx.xxx.xxx.xxx

SQL>
```
表示されたAutonomous DatabaseのアウトバウンドIPアドレスをメモしておきます。

# 2. Amazon Redshiftの準備
以下の設定で、Amazon Redshiftサーバレスをデプロイしました。
ワークグループ：default
名前空間：test
データベース名：dev（デフォルト）
管理者ユーザ名：redshiftuser
管理者ユーザパスワード：Oracle#1Oracle#1
![スクリーンショット 2022-10-09 16.20.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/d05daf3f-0d93-9e5f-4311-afe29f42e1b9.png)
デプロイが完了したら、ワークグループの「パブリックにアクセス可能」をオンにし、RedshiftサーバレスがデプロイされているVPCのセキュリティグループにAutonomous Databaseからの接続を許可するインバウンドルールを設定します。
タイプ：Redshift
ソース：「カスタム」を選択肢、Autonomous DatabaseのアウトバウンドIPアドレスをCIDR形式（xxx.xxx.xxx.xxx/32）で入力
![スクリーンショット 2022-10-09 17.44.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/9589d67e-fc61-f095-102a-b53f5ccd2a89.png)

# 3. Amazon RedshiftのサンプルDBの確認
Redshift query editor v2にアクセスし、データベース管理者としてRedshiftサーバレスに接続します。
今回はあらかじめ用意されているサンプルDB（sample_data_dev）をインストールして検証しました。
![スクリーンショット 2022-10-09 17.54.10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/e65172e8-0aa9-e7e8-ac27-27e9a036d071.png)
検証で使用するtickitスキーマ内にあるvenue表の構成を確認します。
![スクリーンショット 2022-10-09 17.37.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/1865c35d-1336-229d-8d18-c271b074eef7.png)
venueid、venuename、venuecity、venuestate、venueseatsの5つの列から構成されていることがわかりました。

以下のSQL文を実行し、tickitスキーマ内にあるvenue表のレコード数を確認します。
```
SELECT COUNT(*) FROM tickit.venue
```
![スクリーンショット 2022-10-09 17.33.45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/27e7af15-f2b3-dd04-e909-ff9486a994d4.png)
venue表には202件のレコードが格納されていることがわかりました。

以下のSQL文を実行し、tickitスキーマ内にあるvenue表の内容を確認します。
```
SELECT * FROM tickit.venue
```
![スクリーンショット 2022-10-09 16.56.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/784824/9f707188-6d81-6443-5566-ef2690866937.png)

Amazon Redshiftのsample_data_devデータベースのtickitスキーマ内にあるvenue表の内容が確認できました。

# 4. Autonomous DatabaseからAmazon Redshiftへのデータベース・リンクの作成

Amazon Redshiftの準備が整ったので、Autonomous DatabaseからAmazon Redshiftに対するデータベース・リンクを作成します。

Amazon Redshiftに対するデータベース・リンクを作成するには、以下の2つの手順が必要です。
1. Amazon RedshiftにアクセスするためのクレデンシャルをDBMS_CLOUD.CREATE_CREDENTIALプロシージャで作成する
2. Amazon Redshiftに対するデータベース・リンクをDBMS_CLOUD_ADMIN.CREATE_DATABASE_LINKプロシージャで作成する

SQL*PlusやDatabase Actionsを使用して、Autonomous Databaseにadminユーザとして接続します。

DBMS_CLOUD.CREATE_CREDENTIALは以下のような形式で実行します。
```
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => '作成するクレデンシャルの名前',
    username => 'Amazon Redshiftユーザのユーザ名',
    password => 'Amazon Redshiftユーザのパスワード'
  );
END;
/
```
以下のPL/SQLブロックを実行して、「AWS_REDSHIFT_LINK_CRED」という名前のクレデンシャルを作成します。
```
SQL> BEGIN
  2    DBMS_CLOUD.CREATE_CREDENTIAL(
  3      credential_name => 'AWS_REDSHIFT_LINK_CRED',
  4      username => 'redshiftuser',
  5      password => 'Oracle#1Oracle#1'
  6    );
  7  END;
  8  /

PL/SQLプロシージャが正常に完了しました。

SQL>
```

DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINKは以下のような形式で実行します。
```
BEGIN
     DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
          db_link_name => '作成するデータベース・リンク名', 
          hostname => 'Redshiftのエンドポイントのホスト名', 
          port => '5439',
          service_name => 'Redshiftのデータベース名',
          credential_name => '認証に使用するクレデンシャル名',
          gateway_params => JSON_OBJECT('db_type'  value 'AWSREDSHIFT'),
          ssl_server_cert_dn => NULL);
END;
/
```
以下のPL/SQLブロックを実行して、「AWS_REDSHIFT_LINK」という名前のデータベース・リンクを作成します。
※こちらの実行には数分かかります。
```
SQL> BEGIN
  2       DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK(
  3            db_link_name => 'AWS_REDSHIFT_LINK', 
  4            hostname => 'default.xxxxxxxxxxxx.ap-northeast-1.redshift-serverless.amazonaws.com', 
  5            port => '5439',
  6            service_name => 'sample_data_dev',
  7            credential_name => 'AWS_REDSHIFT_LINK_CRED',
  8            gateway_params => JSON_OBJECT('db_type' value 'AWSREDSHIFT'),
  9            ssl_server_cert_dn => NULL);
 10  END;
 11  /

PL/SQLプロシージャが正常に完了しました。
SQL>
```

# 5. 動作確認
describeコマンド（desc）でAmazon Redshiftのsamlpe_data_devデータベース内にあるtickit.venue表の表定義を確認してみます。
```
SQL> desc tickit.venue@AWS_REDSHIFT_LINK
 名前                                       NULL?    型
 ----------------------------------------- -------- ----------------------------
 venueid					                        NUMBER(5)
 venuename					                        VARCHAR2(300)
 venuecity					                        VARCHAR2(90)
 venuestate					                        CHAR(6)
 venueseats					                        NUMBER(10)

SQL>
```
tickit.venue表のレコード数を確認してみます。
```
SQL> SELECT COUNT(*) FROM tickit.venue@AWS_REDSHIFT_LINK;

  COUNT(*)
----------
       202
SQL>
```
202レコードですので、Redshift query editor v2から確認したAmazon Redshift内のtickit.venue表のレコード数と一致しています。

最後に、tickit.venue表に格納されているレコードの内容を確認してみます。

```
SQL> SELECT * FROM tickit.venue@AWS_REDSHIFT_LINK
  2  FETCH FIRST 5 ROWS ONLY;

   venueid
----------
venuename
--------------------------------------------------------------------------------
venuecity
--------------------------------------------------------------------------------
venues venueseats
------ ----------
	31
Pepsi Center
Denver
CO		0


   venueid
----------
venuename
--------------------------------------------------------------------------------
venuecity
--------------------------------------------------------------------------------
venues venueseats
------ ----------
	98
Yankee Stadium
New York City
NY	    52325


   venueid
----------
venuename
--------------------------------------------------------------------------------
venuecity
--------------------------------------------------------------------------------
venues venueseats
------ ----------
       253
Mirage Hotel
Las Vegas
NV


   venueid
----------
venuename
--------------------------------------------------------------------------------
venuecity
--------------------------------------------------------------------------------
venues venueseats
------ ----------
	60
Rexall Place
Edmonton
AB		0


   venueid
----------
venuename
--------------------------------------------------------------------------------
venuecity
--------------------------------------------------------------------------------
venues venueseats
------ ----------
	93
Edward Jones Dome
St. Louis
MO	    66965


SQL> 
```
問題なくtickit.venue表の内容を参照できました。

以上で、作成したデータベース・リンクを経由して、Autonomous DatabaseからAmazon Redshiftのデータを参照できることが確認できました。

# 参考資料
・[Using Oracle Autonomous Database on Shared Exadata Infrastructure：Obtain Tenancy Details](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/autonomous-database-support-ocid.html)
・[Create Database Links to Non-Oracle Databases with Oracle-Managed Heterogeneous Connectivity](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/database-links-other-databases-oracle-managed.html#GUID-9FBC138F-7B22-4D14-96CF-349ADE2959F5)
・[Amazon Redshift Database Developer Guide:Sample Database](https://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html)