目的
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,