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.

調査内容

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

引用元：Databricks Runtime での ANSI 準拠 - Azure Databricks - Databricks SQL | Microsoft Learn

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

引用元：pyspark.sql.functions.try_to_timestamp — PySpark master documentation (apache.org)

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

引用元：try_to_timestamp 関数 - Azure Databricks - Databricks SQL | Microsoft Learn

エラーの再現方法

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

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

エラーを再現

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.

エラーへの対応方法

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

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()

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

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()

補足

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

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()