BigQueryの「Bad int64 value」のエラーを回避する【CASTとSAFE_CASTの違い】

目的

BigQueryでNULLや空白があるカラムをintに変換する方法

背景

BigQueryで、吐き出したいカラムがIntかNullのときCastしようとするとBad int64 valueのエラーが発生する

例えば、以下のようなJSONをRDB型式に吐き出したいとする。

{
 {
  id: 1,
  name: "トマト",
  price: 200
 },
 {
 id:2,
 name: "キャベツ",
 price: null
 }
}  

解決方法

本来であればpriceにNULLのときは0を初期値で入れておこうよというコメントは一回置いておいて、JSONを加工して整数値型にするには以下にすれば良い。

CAST(JSON_EXTRACT_SCALAR(DATA, '$.price')  as INT64) AS price,

ただ、これだとNULLがあるため、Bad int64 valueのエラーが発生してしまいます。理由としてはBigQueryの仕様上、CAST関数が実行できない場合はエラーになるため。
こういうときはCASTではなく、SAFE_CASTを使用すれば解決します。

SAFE_CAST(JSON_EXTRACT_SCALAR(DATA, '$.price')  as INT64) AS price,

Googleの公式情報にも下記のようなコメントが有りました。

ほとんどのデータ型は、CAST 関数を使用してある型から別の型にキャストできます。 CAST を使用する場合、BigQuery がそのキャスティングを実行できなければクエリは失敗します。このようなエラーからクエリを守るには、SAFE_CAST を使用できます。CAST、SAFE_CAST などのキャスト関数の詳細については、変換関数をご覧ください。

CASTとSAFE CASTの違いとは？

BigQueryにおけるCASTとSAFECASTは、両方ともデータ型を変換するために使用されますが、その違いは何でしょうか？

CAST

CASTは、データ型を明示的に変換するために使用されます。この変換は、データ型が互換性がある場合にのみ行うことができます。例えば、INTEGERをFLOATに変換することができますが、STRINGをINTEGERに変換することはできません。

以下は、CASTの使用例です。

SELECT CAST(column_name AS new_data_type) FROM table_name

SAFE_CAST

SAFE_CASTは、CASTと同様にデータ型を変換するために使用されますが、データ型が互換性がない場合でも、エラーを回避するために使用されます。この関数は、変換が成功した場合には変換された値を返し、失敗した場合にはNULLを返します。

以下は、SAFE_CASTの使用例です。

SELECT SAFE_CAST(column_name AS new_data_type) FROM table_name  

例えば、STRING型のカラムをINTEGER型に変換しようとする場合、CASTではエラーが発生しクエリが中断されますが、SAFECASTではNULLが返されます。
以上が、BigQueryにおけるCASTとSAFECASTの違いについての説明です。