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

Share this for your friends.

BigQueryで文字列（string）のカラムを整数（int）に変換したい。

複数のデータをテーブルで管理することができるBigQuery。

しかし数値以外の空白やNULLなどが含まれているカラムを CAST() で型を変換しようとすると Bad int64 value: のエラーが出てしまう。

Bad int64 value:

一体どうすれば変換できるのか。

そこで今回はBigQueryでNULLや空白があるカラムをintに変換する方法について紹介する。

この記事を書いている人

システムエンジニア、AIエンジニアと、IT業界で10年以上働いている中堅。PythonとSQLが得意。最近GCPを色々と習得中。

記事を読むメリット

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

BigQueryで文字列（string）を整数（int）に変換する方法

先にBigQueryで文字列（string）を整数（int）に変換する方法を紹介する。

まずデータを用意する。

(select "1" as col)
union all
(select "2" as col)
union all
(select "3" as col)

結果

col
1
2
3

これを整数（int）に変換するには CAST() を使えば良い。

select
cast(col as int64) as cast_col,
cast(col as int64) +1 as plus_col
from
(
  (select "1" as col)
  union all
  (select "2" as col)
  union all
  (select "3" as col)
)

結果

cast_col　plus_col
1　　　　2
2　　　　3
3　　　　4

BigQueryでNULLや空白があるカラムをintにCASTするとエラーになる

ではNULLや空白があるカラムに対して同じ処理をするとどうなるか。

まずはNULLや空白を含む数字テキストカラムを用意する。

(select "1" as col)
union all
(select "2" as col)
union all
(select "3" as col)
union all
(select "" as col)
union all
(select NULL as col)

結果

col
1
2
3

null

そして CAST() をすると

select
cast(col as int64) as cast_col,
cast(col as int64) +1 as plus_col
from
(
  (select "1" as col)
  union all
  (select "2" as col)
  union all
  (select "3" as col)
  union all
  (select "" as col)
  union all
  (select NULL as col)
)

結果

Bad int64 value:

このように Bad int64 value: というエラーになってしまう。

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

ではNULLや空白があるカラムをどうやって整数（int）型に変換すればよいのか。

NULLや空白があるカラムをintに変換するには SAFE_CAST() を用いる。

SAFE. 接頭辞
関数の先頭に SAFE. 接頭辞を付けると、エラーではなく NULL が返されます。
(中略)
CAST 関数や EXTRACT 関数も SAFE. 接頭辞をサポートしていません。エラーのキャストを防ぐには、SAFE_CAST を使用します。

参考：標準 SQL の式、関数、演算子 | BigQuery | Google Cloud

使い方は CAST() と同じように使える。

select 
safe_cast(col as int64) as cast_col,
safe_cast(col as int64) +1 as plus_col
from
(
  (select "1" as col)
  union all
  (select "2" as col)
  union all
  (select "3" as col)
  union all
  (select "" as col)
  union all
  (select NULL as col)
)

結果

cast_col　plus_col
1　　　　2
2　　　　3
3　　　　4
null　　null
null　　null

NULLや空白の他にも abc のようなテキストデータも SAFE_CAST() でint変換しようとするとNULLが返ってくる。