Databricks にて ANSI モードにてタイムスタンプ型のカラムを変換できない場合に NULL に変換する方法

概要

Databricks にて ANSI モードにてタイムスタンプ型のカラムを変換できない場合に NULL に変換する方法として、try_to_timestamp関数を利用する方法を紹介します。

ANSI モード時のデータ型変換

ANSI モードとは ANSI SQL 標準への準拠をサポートするための機能であり、算術エラーやのデータの変換エラー時の動作が異なります。データ型の変換ができない場合、ANSI モードが無効（従来の Spark での動作）のときには NULL となり、ANSI モードが有効のときにはエラーとなります。

たとえば、SQL の演算子/関数への入力が無効である場合、Spark は、null の結果を返すのではなく、実行時に例外をスローします。

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

ANSI モードが有効のときに次のようなエラーが発生します。

%sql
set spark.sql.ansi.enabled=true;

SELECT
  to_date(to_timestamp(ts)) as ts_patter01
  ,to_date(to_timestamp(ts, 'm/d/y')) as ts_patter02
  FROM
    ts_test

org.apache.spark.SparkDateTimeException: [CANNOT_PARSE_TIMESTAMP] Text '2020-01-01' could not be parsed at index 4. If necessary set "spark.sql.ansi.enabled" to "false" to bypass this error.

データ型の変換できない場合に、エラーではなく、NULL とする場合の対応方法としてtry_cast関数がドキュメントにて紹介されています。ただし、try_cast関数では、日付のフォーマットなどを指定できず困っていました。

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

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

関数一覧を眺めていたところ、try_cast関数と同等の機能をもつtry_to_timestamp関数を見つけました。本記事ではその関数の動作確認を行ったコードを共有します。本記事では検証しておりませんが、数値型の変換を行うtry_to_number関数もあるようです。2023年9月18日時点では、try_to_date関数を確認できたいないため、日付型への変換にはtry_to_timestamp関数とto_date関数を組み合わせる必要がありそうです。

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

検証コードと結果

1. 事前準備

1-1. データをもつビューを作成

%sql
CREATE OR REPLACE VIEW ts_test
AS
SELECT '2020-01-01' AS ts
UNION ALL
SELECT '2020-01-32'
UNION ALL
SELECT '5/20/2021'
;
SELECT
  *
  FROM
    ts_test

2. ANSI モードが無効（従来の Spark）の場合

2-1. try_to_timestamp関数の動作確認

%sql
set spark.sql.ansi.enabled=false;
 
SELECT
  try_to_timestamp(ts) as ts_patter01
  ,try_to_timestamp(ts, 'm/d/y') as ts_patter02
  FROM
    ts_test

3. ANSI モードが有効の場合

3-1. try_to_timestamp関数の動作確認

%sql
set spark.sql.ansi.enabled=true;
 
SELECT
  try_to_timestamp(ts) as ts_patter01
  ,try_to_timestamp(ts, 'm/d/y') as ts_patter02
  FROM
    ts_test

4. ANSI モードが有効の場合の日付型の変換

4-1. try_to_timestamp関数の動作確認

%sql
set spark.sql.ansi.enabled=true;
 
SELECT
  to_date(try_to_timestamp(ts)) as ts_patter01
  ,to_date(try_to_timestamp(ts, 'm/d/y')) as ts_patter02
  FROM
    ts_test