DB

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

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が返ってくる。


まとめ

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

NULLや空白があるカラムをintに変換するには、 SAFE_CAST() を使うと数字ではない値の変換もエラーにならずにNULLが返される


これで変換できるね
空白やNULLのようなデータ欠損はあったりするので、エラーにせずに変換できるのは助かりますね


他にもBigQuery関連の記事もあるので、もし気になったら見て欲しい。

ITipsと同じようなブログを作る方法

ブログに興味がありますか?

もしブログに興味がある場合は↓このページ↓を参考にすれば、ITipsと同じ構成でブログを作ることができます

サーバー、ドメイン、ASPと【ブログに必要なものは全て】このページに書きました。
同じ構成でブログ作るのはいいけど、記事はマネしないでネ (TДT;)

ランキング参加中

にほんブログ村 IT技術ブログへ

他にもブログやSNSで紹介してくれると励みになります。

はてブのコメントで酷評されると泣きます(´;ω;`)

-DB
-

Translate »

© 2021 ITips