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](https://learn.microsoft.com/en-us/azure/databricks/sql/language-manual/functions/try_to_timestamp)

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

```sql
%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
```

```text
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.
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/2173f025-4d28-a900-b6e1-09a1a6052273.png)

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/109e5627-b90b-54b5-c4a6-879c82a4cfff.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#useful-functions-for-ansi-mode)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/18916546-2d1b-6fe3-62fc-159e5d3c62a1.png)

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/77a29f9b-ff16-5e80-8a64-8353c4de3226.png)

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


## 検証コードと結果

### 1. 事前準備

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

```python
%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`関数の動作確認

```python
%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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/349877d2-b077-aafd-d628-3a59ae3e91a5.png)

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

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

```python
%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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/183559b9-aa9d-101e-e313-49e8fae1011d.png)

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

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

```python
%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
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/24031/7dd0a88e-1cc9-6d1f-fdb5-5a5d28f697ad.png)