[小ネタ]BigQueryで空白や空文字をNULLに一括変換するクエリを書いてみた

最近お寿司を食べる時に白身魚の方が好きになってきました。そんな中でもサーモンだけはブレずにずっと好きだな……と思っていたのですが、よくよく考えたらサーモンは白身魚だったことを思い出し色々腹落ちして今日も元気に生きています。

▲ 炙りサーモンが一番好きです。「炙り」のポテンシャルに惹かれるので

こんにちは。データアナリティクス事業本部 インテグレーション部 機械学習チームのShirotaです。
最近BigQueryでクエリを書いていてちょっとしたデータの前処理を行う機会が多く、自分の備忘録として記録しておきたい小ネタがあったのでブログにしてみました。

空文字とNULLは異なる概念

突然ですがクイズです。
以下のテーブルの comment カラム（STRING型）のレコードは三種類とも異なるものです。この画像だけを見て当ててみてください。

▲ 2/3、賭け要素が混ざっています

「null」という文字列を入れた、のようないやらしいことはしていません。user_id が13の comment には NULL が入っています。
問題は user_id が12と14の comment です。この画像だけでは判断できないと思います。「あれ」か「これ」かな……という当たりはつけられるかもしれません。
では、続けて12と14の答えを出します。

▲ こちらは空文字で

▲ こちらは半角スペース、つまり「空白」の文字列でした

念のため、13の答えも出力してみました。

▲ ちゃんとNULLですね

クエリの結果から、「空文字」「空白の文字列」「NULL」はそれぞれ別のレコードとして扱われていることが分かると思います。
この中でも空白の文字列はわかりやすく、いわゆる「半角スペース」や「全角スペース」といった文字列のことを指しています。

では、「空文字」と「NULL」はどう異なるのでしょうか？

空文字とは「長さが0の文字列」

空文字とは、 長さが0の文字列 のことを指します。
ここで間違えやすいのですが、後述するNULLのような「何もない状態」と「長さが0の文字列」は違う概念となります。
空文字は、その名の通り長さが0の文字列が存在しています。
空白の文字列同様、人間がコンソール上などで表示したテーブルを見た時には何もないように見えますが1レコードとしてちゃんとメモリを消費して格納されているのです。

NULLとは「型もない、本当に何もないことを指すもの」

対してNULLは、 何もない状態 を指しています。より正確に定義すると、文字列や半角スペースのような「値」でなく 「値」でないことから「データ型」もありません。
よってSTRING型で何もない状態を指すことも、INT型で何もない状態を指すことも、共通してNULLが使われます。
そして値ではないため、先述した空文字と比較して考えると長さが存在しないことになります。
この通り、空文字とNULLは異なる定義です。

今回はSTRING型の話をしているため特に問題ないのですが、空白の文字列や空文字についてはデータ型によって扱える・定義外のデータであり扱えないから他の値に変換されるといった意図しない挙動をすることもあります。
それ以外に気をつけるべきこともあるので続けてお話ししていきます。

データベース管理システムによって扱いが違うこともあるので注意

ここまで空文字とNULLの定義の違いを説明しましたが、データベース管理システムによって扱いが異なることがあります。
Oracle Databaseでは、NULLは以下のように定義されています。（2023年5月18日現在の最新長期リリースバージョン Oracle Database 19cについて）

Oracle Databaseは、長さが0(ゼロ)の文字値をNULLとして処理します。ただし、数値0を表すのにnullを使用しないでください。この2つは等価ではありません。 ノート:Oracle Databaseは現在、長さが0(ゼロ)の文字値をNULLとして処理します。ただし、将来のリリースではこの処理が変更される場合があるため、空の文字列をnullと同じように処理しないことをお薦めします。

このように、先ほど空文字と定義したものをNULLと処理しています。
今利用しているBigQueryやSQL Server、PostgreSQLは 空文字 ≠ NULL で処理されていますがOracle Database のリファレンスにあるように処理が変わることは無いとは言い切れないので、利用しているデータベース管理システムの最新のリファレンスを適宜確認することをおすすめします。

空文字・空白・NULLが混ざったレコードをNULLに統一する

さて、現在先ほどのクイズで使った 空文字 空白 NULL の三つが混ざったレコードを抱えたテーブルがあります。
どれの我々の目から見れば値がないように見えますがBigQueryでは前述のクイズで示した通り、全て違うものとして扱われています。
そのため、このテーブルデータを扱いやすくしよう！ということで今回はNULLに統一しようと思います。世は大NULL時代。

そして折角なので、今回は 空文字 → NULL 変換と 空白 → NULL 変換を同時にこなしてしまおう！と思いました。
その結果、今回書いたクエリがこちらになります。

SELECT user_id,NULLIF(TRIM(comment), "") AS comment FROM `XXXXXXXXXXX.dataset_1.review`　ORDER BY user_id;

上記のクエリについて、簡単に言葉で解説すると以下のようになります。

• TRIM関数で comment カラムの空白を取り除く
  • この時点で、 comment カラムの空白は空文字になる
  • 今回は特に影響がないが、空白のみが入ったレコード以外の文頭と文末にある空白もトリムされる
• NULLIF関数で、空文字が入ったデータをNULLに変換する

このクエリを実行した結果、以下のように全てNULLでレコードが統一されました。

▲ 扱いやすいテーブルデータになりました

中身がなさそうに見えるレコードもしっかり精査しよう

今回は人間の目には同じ「何もない」状態に見えるレコードも実は種類が異なることがあるという話とそれを統一するクエリについてお話ししました。
データ分析や機械学習でデータを扱う際、どのデータが同一と扱われるかというのはとても大切なことです。
欠損値です、と言われたレコードが上記のように違うものが入っていた場合、前処理をしておかないと後々の学習時に想定外の挙動をするリスクもあります。

このブログが、データの前処理をしている人や空文字等の定義を知りたい人の助けになれば幸いです。