Databricks にて ANSI モードが有効な場合における to_date と to_timestamp にて変換できない場合にエラーになる事象の回避方法

## 概要

Databricks にて ANSI モードが TRUE の場合における to_date メソッドと to_timestamp メソッドにて変換できない場合にエラーになる事象の回避方法を共有します。to_date メソッドと to_timestamp メソッドではなく、expr メソッドにて SQL の try_to_timestamp 関数を利用することで対応することができます。

> SparkDateTimeException: [CAST_INVALID_INPUT] The value '2023/12/31 12:12:12' of the type "STRING" cannot be cast to "TIMESTAMP" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d9f38fa6-a758-0bba-fe4a-f2c0f1940417.png)

## 調査内容

ANSI モード（`spark.sql.ansi.enabled`という Spark 設定）が TRUE の場合には Hive 準拠から ANSI 準拠に変更され、データ型変換ができない場合にはエラー（ランタイム例外をスロー）が発生します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/218f1cc4-ba24-e750-5684-c243e28c40be.png)

引用元：[Databricks Runtime での ANSI 準拠 - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/sql-ref-ansi-compliance)

PySpark のドキュメントにて try_to_timestamp メソッドにて対応可能であることを確認できたのですが、 Spark 3.5 以上（Databricks Runtime 14.0 以上）でないと動作しないため過去のバージョンでも動作する方法を再検討することとしました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/fb0cc87a-d385-81e7-8549-f9591ec99090.png)

引用元：[pyspark.sql.functions.try_to_timestamp — PySpark master documentation (apache.org)](https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.try_to_timestamp.html)

SQL の try_to_timestamp 関数であれば Databricks Runtime 11.3 LTS 以上で利用可能であることが確認できたため、 expr メソッドと組み合わせて利用することとしました。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/876b05a8-0cfc-07f5-8746-a82e57e009c0.png)

引用元：[try_to_timestamp 関数 - Azure Databricks - Databricks SQL | Microsoft Learn](https://learn.microsoft.com/ja-jp/azure/databricks/sql/language-manual/functions/try_to_timestamp)

## エラーの再現方法

### 検証用データフレームを作成

```python
schema = """
timestamp_col string,
date_col string
"""
data = [
    {
        "timestamp_col": "2023/12/31 12:12:12",
        "date_col": "2023/12/31",
    },
]
df = spark.createDataFrame(data,schema)
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/c9647a97-c164-d465-7da1-5c6aed969d13.png

### エラーを再現

```python
ansi_enabled = "spark.sql.ansi.enabled"
spark.conf.set(ansi_enabled, True)
print(spark.conf.get(ansi_enabled))

from pyspark.sql.functions import to_date, to_timestamp

with_cond = {
    "timestamp_col": to_timestamp("timestamp_col"),
    "date_col": to_date("date_col"),
}
convert_df = df.withColumns(with_cond)
convert_df.display()
```

> SparkDateTimeException: [CAST_INVALID_INPUT] The value '2023/12/31 12:12:12' of the type "STRING" cannot be cast to "TIMESTAMP" because it is malformed. Correct the value as per the syntax, or change its target type. Use `try_cast` to tolerate malformed input and return NULL instead. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/2ca2c58d-9a8f-53c2-3736-fdad69bd8e49.png)

## エラーへの対応方法

### 1. expr メソッドにて SQL の try_to_timestamp 関数を利用する方法

```python
from pyspark.sql.functions import expr

with_cond = {
    "timestamp_col": expr("try_to_timestamp(timestamp_col)"),
    "date_col": expr("to_date(try_to_timestamp('date_col'))"),
}
convert_df = df.withColumns(with_cond)
convert_df.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/d4d325e7-d289-3acf-b549-d6a2bfcdded9.png)



### 2. try_to_timestamp メソッドを利用する方法

```python
from pyspark.sql.functions import to_date, try_to_timestamp

with_cond = {
    "timestamp_col": try_to_timestamp("timestamp_col"),
    "date_col": to_date(try_to_timestamp("date_col")),
}
convert_df = df.withColumns(with_cond)
convert_df.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/9d159107-dafc-9a80-0375-39364001425f.png)


## 補足

### SQL の try_to_timestamp 関数利用時にフォーマットを指定することも可能

```python
from pyspark.sql.functions import expr

date_format = "yyyy/MM/dd"
datetime_format = "yyyy/MM/dd HH:mm:ss"

with_cond = {
    "timestamp_col": expr(f"try_to_timestamp(timestamp_col, '{datetime_format}')"),
    "date_col": expr(f"to_date(try_to_timestamp(date_col, '{date_format}'))"),
}
convert_df = df.withColumns(with_cond)
convert_df.display()
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/0157c9f1-ccef-5e3b-7ee7-4e031a2630d4.png)