🦑
(BigQuery)DATE型とSTRING型を型判定する方法
背景
- event_dateというフィールドのスキーマを変更したかった(STRING型 > DATE型)
- DBのスキーマを変更前と変更後でやるべき処理が違ったため、動的に処理を変えたかった
やりたいこと
-
BigQueryでSTRING型かDATE型かを型判定して、処理を動的に変えたかった
事前条件
- フィールド名は仮に
event_dateとする -
event_dateには日付情報が入っており、以下のようなデータとする
- STRING型の場合は"20220101"
- DATE型の場合は"2022-01-01"
検証１
SAFE_CAST()の特性(errの代わりにNULLを返す)を利用して、STRING型とDATE型を型判定する
SAFE_CAST(event_date AS STRING)を使う場合（失敗)
SELECT
CASE
WHEN SAFE_CAST(event_date AS STRING) IS NOT NULL
THEN STRING型の処理
ELSE DATE型の処理
END
- 期待値
- event_date="20220101"(STRING型)の場合は THENに進む
- event_date="2022-01-01"(DATE型)の場合は ELSEに進む
- 結果
- event_date="20220101"(STRING型)の場合は THENに進む
- event_date="2022-01-01"(DATE型)の場合は THENに進む
この記事を書いた理由だが、これがなぜか失敗する。他の方法で試す。
SAFE_CAST(event_date AS DATE)を使う場合（成功)
SELECT
CASE
WHEN SAFE_CAST(event_date AS DATE) IS NOT NULL
THEN DATE型の処理
ELSE STRING型の処理
END
- 期待値
- event_date="20220101"(STRING型)の場合は ELSEに進む
- event_date="2022-01-01"(DATE型)の場合は THENに進む
- 結果
- event_date="20220101"(STRING型)の場合は ELSEに進む
- event_date="2022-01-01"(DATE型)の場合は THENに進む
検証2
- 特定の文字列が合致するかチェック(DATE型の場合は"-"が含まれている)
正規表現(成功)
SELECT
CASE
WHEN REGEXP_CONTAINS(SAFE_CAST(event_date as string), r'^[0-9]{4}-[0-9]{2}-[0-9]{2}$')
THEN DATE型の処理
ELSE STRING型の処理
END
- 期待値
- event_date="20220101"(STRING型)の場合は THENに進む
- event_date="2022-01-01"(DATE型)の場合は ELSEに進む
- 結果
- event_date="20220101"(STRING型)の場合は THENに進む
- event_date="2022-01-01"(DATE型)の場合は ELSEに進む
LIKE句(成功)
SELECT
CASE
WHEN event_date like '%-%'
THEN DATE型の処理
ELSE STRING型の処理
END
- 期待値
- event_date="20220101"(STRING型)の場合は THENに進む
- event_date="2022-01-01"(DATE型)の場合は ELSEに進む
- 結果
- event_date="20220101"(STRING型)の場合は THENに進む
- event_date="2022-01-01"(DATE型)の場合は ELSEに進む
このやり方は泥臭すぎて、コードレビューでパスできる気がしなかったので却下した
検証3
- このリポジトリのtypeofを使う)
- DATE型の判定はできないが、STRING型の判定はできる
SELECT
CASE
WHEN bqutil.fn.typeof(event_date) = 'STRING'
THEN STRING型の処理
ELSE DATE型の処理
END
- 期待値
- event_date="20220101"(STRING型)の場合は THENに進む
- event_date="2022-01-01"(DATE型)の場合は ELSEに進む
- 結果
- event_date="20220101"(STRING型)の場合は THENに進む
- event_date="2022-01-01"(DATE型)の場合は ELSEに進む
めっちゃスマートやん。最初からこれでやればよかった
結論
- 型判定したい時はbigquery-utilsリポジトリのtypeofを使おう
Discussion