BigQueryでIntのカラムを吐き出す時際、「Bad int64 value」のエラーを回避する

目的

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

CASTではなく、SAFE_CASTを使用すれば解決！

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