Databricks にて Oracle Autonomous Database からデータフレームを作成した際のタイムゾーンの確認

## 概要

Databricks にて Oracle Autonomous Database からデータフレームを作成した際のタイムゾーンの確認結果を共有します。

以下のクエリを実行して、その差異を確認しました。

```sql
SELECT
    SESSIONTIMEZONE AS SESSIONTIMEZONE
    ,DBTIMEZONE AS DBTIMEZONE
    ,LOCALTIMESTAMP AS LOCALTIMESTAMP
    ,CURRENT_DATE AS CURRENT_DATE
    ,SYSTIMESTAMP AS SYSTIMESTAMP
    ,CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP
    ,CURRENT_TIMESTAMP AT LOCAL AS CURRENT_TIMESTAMP_AT_LOCAL
    ,CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS CURRENT_TIMESTAMP_AT_TIME_ZONE
    FROM DUAL;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/4a56e914-23dc-008c-c521-c13d5207d116.png)

次表に検証したパターンとその結果です。

| #    | パターン                                                     | SESSIONTIMEZONE | DBTIMEZONE | LOCALTIMESTAMP | CURRENT_DATE |
| ---- | ------------------------------------------------------------ | --------------- | ---------- | -------------- | ------------ |
| 1    | デフォルトでの動作確認                                       | UTC             | UTC        | UTC            | UTC          |
| 2    | `sessionInitStatement`にてタイムゾーンを変更                 | JST             | UTC        | JST            | JST          |
| 3    | jdbc オプションでタイムゾーンを変更                          | UTC             | UTC        | UTC            | UTC          |
| 4    | Spark Config にてタイムゾーンを変更                          | UTC             | UTC        | JST            | JST          |
| 5    | DBTIMEZONE の変更後、`sessionInitStatement`にてタイムゾーンを変更 | JST             | JST        | JST            | JST          |


## コードと結果

### 事前準備

```python
username = ""  ## ユーザー名
password = ""  ## パスワード

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/30e8c8a7-626b-0885-520d-0ba28e6f83f3.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/3704fe80-4871-e169-5916-d51e5216224d.png)

### 1. デフォルトでの動作確認

Databricks にて以下のコードを実行

```python
spark.conf.unset("spark.sql.session.timeZone")
print(spark.conf.get("spark.sql.session.timeZone"))

# 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",
    "query": """
    SELECT
        SESSIONTIMEZONE AS SESSIONTIMEZONE
        ,DBTIMEZONE AS DBTIMEZONE
        ,LOCALTIMESTAMP AS LOCALTIMESTAMP
        ,CURRENT_DATE AS CURRENT_DATE
        ,SYSTIMESTAMP AS SYSTIMESTAMP
        ,CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP
        ,CURRENT_TIMESTAMP AT LOCAL AS CURRENT_TIMESTAMP_AT_LOCAL
        ,CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS CURRENT_TIMESTAMP_AT_TIME_ZONE
        FROM DUAL
    """,
}

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

df.display()
```

| SESSIONTIMEZONE | DBTIMEZONE | LOCALTIMESTAMP                | CURRENT_DATE                  | SYSTIMESTAMP                  | CURRENT_TIMESTAMP             | CURRENT_TIMESTAMP_AT_LOCAL    | CURRENT_TIMESTAMP_AT_TIME_ZONE |
| --------------- | ---------- | ----------------------------- | ----------------------------- | ----------------------------- | ----------------------------- | ----------------------------- | ------------------------------ |
| Etc/UTC         | UTC        | 2024-02-15T03:49:07.523+00:00 | 2024-02-15T03:49:07.000+00:00 | 2024-02-15T03:49:07.523+00:00 | 2024-02-15T03:49:07.523+00:00 | 2024-02-15T03:49:07.523+00:00 | 2024-02-15T03:49:07.523+00:00  |

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/c05ee4fb-fc05-f4e7-c5da-c8996346bdc6.png)

### 2. `sessionInitStatement`にてタイムゾーンを変更

Databricks にて以下のコードを実行

```python
spark.conf.unset("spark.sql.session.timeZone")
print(spark.conf.get("spark.sql.session.timeZone"))

# 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'",
    "query": """
    SELECT
        SESSIONTIMEZONE AS SESSIONTIMEZONE
        ,DBTIMEZONE AS DBTIMEZONE
        ,LOCALTIMESTAMP AS LOCALTIMESTAMP
        ,CURRENT_DATE AS CURRENT_DATE
        ,SYSTIMESTAMP AS SYSTIMESTAMP
        ,CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP
        ,CURRENT_TIMESTAMP AT LOCAL AS CURRENT_TIMESTAMP_AT_LOCAL
        ,CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS CURRENT_TIMESTAMP_AT_TIME_ZONE
        FROM DUAL
    """,
}

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

df.display()
```

| SESSIONTIMEZONE | DBTIMEZONE | LOCALTIMESTAMP                | CURRENT_DATE                  | SYSTIMESTAMP                  | CURRENT_TIMESTAMP             | CURRENT_TIMESTAMP_AT_LOCAL    | CURRENT_TIMESTAMP_AT_TIME_ZONE |
| --------------- | ---------- | ----------------------------- | ----------------------------- | ----------------------------- | ----------------------------- | ----------------------------- | ------------------------------ |
| Asia/Tokyo      | UTC        | 2024-02-15T12:49:09.991+00:00 | 2024-02-15T12:49:09.000+00:00 | 2024-02-15T03:49:09.991+00:00 | 2024-02-15T03:49:09.991+00:00 | 2024-02-15T03:49:09.991+00:00 | 2024-02-15T03:49:09.991+00:00  |

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/67460841-197b-53e5-a9a0-de5f2ad21977.png)

### 3. jdbc オプションでタイムゾーンを変更

Databricks にて`oracle.jdbc.timezoneAsRegion`と`oracle.jdbc.timezone`のオプションに追加した以下のコードを実行

```python
spark.conf.unset("spark.sql.session.timeZone")
print(spark.conf.get("spark.sql.session.timeZone"))

# 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",
    "query": """
    SELECT
        SESSIONTIMEZONE AS SESSIONTIMEZONE
        ,DBTIMEZONE AS DBTIMEZONE
        ,LOCALTIMESTAMP AS LOCALTIMESTAMP
        ,CURRENT_DATE AS CURRENT_DATE
        ,SYSTIMESTAMP AS SYSTIMESTAMP
        ,CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP
        ,CURRENT_TIMESTAMP AT LOCAL AS CURRENT_TIMESTAMP_AT_LOCAL
        ,CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS CURRENT_TIMESTAMP_AT_TIME_ZONE
        FROM DUAL
    """,
    "oracle.jdbc.timezoneAsRegion": "false",
    "oracle.jdbc.timezone": "Asia/Tokyo",
}

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

df.display()
```

| SESSIONTIMEZONE | DBTIMEZONE | LOCALTIMESTAMP                | CURRENT_DATE                  | SYSTIMESTAMP                  | CURRENT_TIMESTAMP             | CURRENT_TIMESTAMP_AT_LOCAL    | CURRENT_TIMESTAMP_AT_TIME_ZONE |
| --------------- | ---------- | ----------------------------- | ----------------------------- | ----------------------------- | ----------------------------- | ----------------------------- | ------------------------------ |
| +00:00          | UTC        | 2024-02-15T03:49:12.482+00:00 | 2024-02-15T03:49:12.000+00:00 | 2024-02-15T03:49:12.482+00:00 | 2024-02-15T03:49:12.482+00:00 | 2024-02-15T03:49:12.482+00:00 | 2024-02-15T03:49:12.482+00:00  |

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/bcee9e04-80ab-cb02-6220-4ab5bfdb31b7.png)


### 4. Spark Config にてタイムゾーンを変更

Databricks にて以下のコードを実行

```python
# Set Spark configuration timezone to Asia/Tokyo
spark.conf.set("spark.sql.session.timeZone","JST")
print(spark.conf.get("spark.sql.session.timeZone"))

# 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'",
    "query": """
    SELECT
        SESSIONTIMEZONE AS SESSIONTIMEZONE
        ,DBTIMEZONE AS DBTIMEZONE
        ,LOCALTIMESTAMP AS LOCALTIMESTAMP
        ,CURRENT_DATE AS CURRENT_DATE
        -- 以下は Spark config を設定するとエラーとなる
        --,SYSTIMESTAMP AS SYSTIMESTAMP
        --,CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP
        --,CURRENT_TIMESTAMP AT LOCAL AS CURRENT_TIMESTAMP_AT_LOCAL
        --,CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS CURRENT_TIMESTAMP_AT_TIME_ZONE
        FROM DUAL
    """,
}

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

df.display()
```

| SESSIONTIMEZONE | DBTIMEZONE | LOCALTIMESTAMP                | CURRENT_DATE                  |
| --------------- | ---------- | ----------------------------- | ----------------------------- |
| Etc/UTC         | UTC        | 2024-02-15T12:49:14.895+09:00 | 2024-02-15T12:49:14.000+09:00 |

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/2854c1fb-bb1b-fc1f-be11-1255839ace32.png)

### 5. DBTIMEZONE の変更後、`sessionInitStatement`にてタイムゾーンを変更

Oracle Autonomous Database にて DBTIMEZONE の設定を変更後、再起動を実施。

```sql
ALTER DATABASE SET TIME_ZONE='Asia/Tokyo';
SELECT DBTIMEZONE FROM dual;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d0050ced-8273-e23e-12eb-2b5253cc384c.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/01a4d43a-7219-814b-67aa-f40e2436c40f.png)

```sql
SELECT DBTIMEZONE FROM dual;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/e2ee51b1-a45c-f3af-1a04-a6de2365e748.png)


Databricks にて以下のコードを実行

```python
# `spark.sql.session.timeZone`の設定をアンセット
spark.conf.unset("spark.sql.session.timeZone")
print(spark.conf.get("spark.sql.session.timeZone"))

# 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'",
    "query": """
    SELECT
        SESSIONTIMEZONE AS SESSIONTIMEZONE
        ,DBTIMEZONE AS DBTIMEZONE
        ,LOCALTIMESTAMP AS LOCALTIMESTAMP
        ,CURRENT_DATE AS CURRENT_DATE
        ,SYSTIMESTAMP AS SYSTIMESTAMP
        ,CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP
        ,CURRENT_TIMESTAMP AT LOCAL AS CURRENT_TIMESTAMP_AT_LOCAL
        ,CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS CURRENT_TIMESTAMP_AT_TIME_ZONE
        FROM DUAL
    """,
}

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

df.display()
```

| SESSIONTIMEZONE | DBTIMEZONE | LOCALTIMESTAMP                | CURRENT_DATE                  | SYSTIMESTAMP                  | CURRENT_TIMESTAMP             | CURRENT_TIMESTAMP_AT_LOCAL    | CURRENT_TIMESTAMP_AT_TIME_ZONE |
| --------------- | ---------- | ----------------------------- | ----------------------------- | ----------------------------- | ----------------------------- | ----------------------------- | ------------------------------ |
| Asia/Tokyo      | Asia/Tokyo | 2024-02-15T12:51:24.063+00:00 | 2024-02-15T12:51:24.000+00:00 | 2024-02-15T03:51:24.063+00:00 | 2024-02-15T03:51:24.063+00:00 | 2024-02-15T03:51:24.063+00:00 | 2024-02-15T03:51:24.063+00:00  |

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/15b52115-8a10-b9c1-7ed4-772c8b2f19a3.png)



## 現状の課題

### 1. Spark Conifg にタイムゾーンを変更した場合に`SYSTIMESTAMP`と`CURRENT_TIMESTAMP`が動作しなくなった

次のようなエラーが発生。本検証では、Spark Conifg 変更の検証時にコメントを実施。

```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",
    "query": """
    SELECT
        SYSTIMESTAMP AS SYSTIMESTAMP
        -- CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP
        -- CURRENT_TIMESTAMP AT LOCAL AS CURRENT_TIMESTAMP_AT_LOCAL
        -- CURRENT_TIMESTAMP AT TIME ZONE 'Asia/Tokyo' AS CURRENT_TIMESTAMP_AT_TIME_ZONE
        FROM DUAL
    """,
}

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

df.display()
```

```text
org.apache.spark.SparkSQLException: [UNRECOGNIZED_SQL_TYPE] Unrecognized SQL type - name: TIMESTAMP WITH TIME ZONE, id: -101.
```