Databricks にて Oracle Autonomous Database に接続して Spark データフレームを作成する方法

## 概要

Databricks にて Oracle Autonomous Database に接続して Spark データフレームを作成する方法を共有します。クライアント資格証明(ウォレット)を利用せずに、 Oracle Autonomous Database に Databricks から接続する手順となっております。

Oracle Database JDBC driver を事前に Databricks 環境に配置しておく必要があります。Databricks での JAR ファイルの保管場所としては、Volumes、あるいは、外部のストレージを選択することが推奨されており、dbfs 上に配置することはあまり推奨されていないようです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/07c77b26-5071-d119-a287-4417bfb4ae1a.png)

引用元：[ライブラリと init スクリプトとのコンピューティング互換性 - Azure Databricks | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/compute/compatibility#single-user-access-mode-support-for-libraries-and-init-scripts)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/20dbe0eb-290d-12ac-a96d-1cf007eb1947.png)


## 事前準備

### 1. Oracle Autonomous Database にてネットワークを設定

Oracle Autonomous Database のページにて、以下が設定されていることを確認。

1. アクセス制御リストを有効に設定（疎通確認を`0.0.0.0/0`で実施）
2. 相互TLS (mTLS)認証を不要に設定（`相互TLS 相互TLS (mTLS)認証が必要詳細`のチェックを外す）

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/2b3c3d82-bf78-5fe1-5939-770b132fefcc.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/b64c6464-6e01-d6e5-c8e4-ad4dacc68d89.png)

### 2. Oracle Database JDBC driver を Databaricks 環境に配置

[リンク](https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html)から Oracle Database JDBC driver の`ojdbc8.jar`をダウンロードする。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/6a9848cf-fdef-af51-898d-fd70cfe3bb12.png)

引用元： [JDBC and UCP Downloads page (oracle.com)](https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html)

Databricks の Volumes に`ojdbc8.jar`を配置する。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/33f20753-91ee-aa98-2a1a-a703267d664a.png)

### 3. Databricks のクラスターに`ojdbc8.jar`をインストール

Databricsk のクラスターのページにて、`ライブラリ` -> `新規をインストール` -> Volumes 上の`ojdbc8.jar`を選択 -> `インストール`を選択して、クラスターにライブラリをインストール。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/548e4ed3-d5a1-8b0e-eac0-697547f87ccd.png)

## Databricks から疎通確認

### 1. Oracle Autonomous Database の接続文字列を取得

Oracle Autonomous Database のページにて、`データベース接続` -> `TLS認証`を`TLS`に設定 -> `{データベース名}_high`（例：`kpwq304szjg90by8_high`）の接続文字列の`コピー`を選択。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/4d0acee5-a718-e789-0616-e76bdae3ac21.png)

### 2. Databricks にてデータフレーム作成と表示

次のコードにユーザー ID、パスワード、および、接続文字列を入力して実行。

```python
username = ""  ## ユーザー名
password = ""  ## パスワード
# connection strings
conn_str = "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.ap-tokyo-1.oraclecloud.com))(connect_data=(service_name=g55ca460616b750_kpwq304szjg90by8_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))"
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/ccdce2c9-a31e-b7b0-8875-6a0b27b752c9.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/70e8a7b5-59dd-b7fc-8ab6-a8efb271e325.png)

次のコードを実行して、データが表示されることを確認。

```pyhon
# JDBC connection URL
jdbc_url = f"jdbc:oracle:thin:@{conn_str}"

# JDBC connection properties
properties = {
    "url": jdbc_url,
    "user": username,
    "password": password,
    "driver": "oracle.jdbc.driver.OracleDriver",
    "sessionInitStatement": "ALTER SESSION SET TIME_ZONE = 'Asia/Tokyo'",
    "query": """
    SELECT
        SESSIONTIMEZONE AS SESSIONTIMEZONE
        ,DBTIMEZONE AS DBTIMEZONE
        ,CURRENT_DATE AS CURRENT_DATE
        FROM DUAL
    """,
}

# Create spark dataframe
df = spark.read.format("jdbc").options(**properties).load()

df.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/f454a5ac-5d66-ec62-1699-34e3f01dd6b4.png)

## Databricks から Oracle のテーブル参照

### 1. Oracle Autonomous Database にてサンプルテーブルを作成

```sql
/*
DROP TABLE dbx_test_table;
*/
CREATE TABLE dbx_test_table(
    int_col int,
    str_col VARCHAR2(10),
    timestamp_col date
);

INSERT INTO dbx_test_table (int_col, str_col, timestamp_col)
SELECT 1 AS int_col, 'a', TO_TIMESTAMP('2022-01-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
UNION ALL
SELECT 2 AS int_col, 'b', TO_TIMESTAMP('2022-02-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
UNION ALL
SELECT 3 AS int_col, 'c', TO_TIMESTAMP('2022-03-01 12:00:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
;
SELECT
    *
    FROM
        dbx_test_table
;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/8f13aafc-0237-49b8-7571-1f2e1f4628ad.png)

### 2. Databricks にてデータフレーム作成と表示

次のコードを実行して、データが表示されることを確認。

```python
username = ""  ## ユーザー名
password = ""  ## パスワード
# connection strings
conn_str = "(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.ap-tokyo-1.oraclecloud.com))(connect_data=(service_name=g55ca460616b750_kpwq304szjg90by8_tp.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))"
```

```python
# JDBC connection URL
jdbc_url = f"jdbc:oracle:thin:@{conn_str}"

# JDBC connection properties
properties = {
    "url": jdbc_url,
    "user": username,
    "password": password,
    "driver": "oracle.jdbc.driver.OracleDriver",
    "sessionInitStatement": "ALTER SESSION SET TIME_ZONE = 'Asia/Tokyo'",
    "dbtable": "dbx_test_table",
}

# Create spark dataframe
df = spark.read.format("jdbc").options(**properties).load()

df.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/0a6ff981-053d-accb-26dc-a5068f71e966.png)

## 接続文字列の変数の簡素化

### 1. Databricks にてデータフレーム作成と表示

接続文字列を確認すると、ホスト名とサービス名の変更のみでよさそうなため、その値を変数で定義するように変更

```python
hostname = "adb.ap-tokyo-1.oraclecloud.com"  # ホスト名
service_name = "g55ca460616b750_kpwq304szjg90by8_high.adb.oraclecloud.com"  # サービス名
```

```python
tgt_table = "dbx_test_table"  # データを取得するテーブル名
```

```python
# connection strings
conn_str = "("
conn_str += f"description=(retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host={hostname})"
conn_str += ")"
conn_str += "("
conn_str += f"connect_data=(service_name={service_name})"
conn_str += ")"
conn_str += "(security=(ssl_server_dn_match=yes))"
conn_str += ")"

# JDBC connection URL
jdbc_url = f"jdbc:oracle:thin:@{conn_str}"

# JDBC connection properties
properties = {
    "url": jdbc_url,
    "user": username,
    "password": password,
    "driver": "oracle.jdbc.driver.OracleDriver",
    "sessionInitStatement": "ALTER SESSION SET TIME_ZONE = 'Asia/Tokyo'",
    "dbtable": tgt_table,
}

# Create spark dataframe
df = spark.read.format("jdbc").options(**properties).load()

df.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/489dd7a4-6744-0be8-bee3-3a96da824596.png)