改訂新版前処理大全第5章2節「条件指定による列の抽出」をBigQueryで(無理やり)やってみる

パターン1: 特定の文字列から始まる列名を抽出するパターン
パターン2: 欠損のある列の抽出
パターン3: 数値型の列の抽出

最近、これを読んでいる。

作者:本橋智光,橋本秀太郎
技術評論社

データ分析における前処理過程でよく見るパターンに対する、「望ましい書き方」を教えてくれる本。特にSQL(BigQuery で使える GoogleSQL)、pandas、Polars の 3 種類それぞれで実装してくれるのが個人的には嬉しい。BigQuery はかなり使うけど、pandas とか Polars とかは使ったことあんまりないので。

楽しく読んでいたんだけど、この本の第5章「抽出」第2節「条件指定による列の抽出」(p67) において、下記のような記述があった。

BigQuery では抽出対象の列が動的に変化するような処理を記載できないため、条件指定による列抽出は実現できません。

たしかに SQL では列などを動的に変化させて扱うのは難しいと思う。ただ、BigQuery でもなんとかできないだろうか？　と思って色々調べてみた結果できそうなので、それをメモとして残しておくことにした。 ~~Python(pandas, Polars) でやるほうが性能・コスト的に望ましいのはわかっているけど……~~

また最近は BigQuery DataFrames とかも話には聞くけど、あんまり使ったことないんだよね〜ということで基本的に、INFORMATION_SCHEMA.COLUMNS ビューと BigQuery Scripting を活用して SQL だけでやっていく。

INFORMATION_SCHEMA ビューについてはこちらの公式ドキュメントを

cloud.google.com

BigQuery Scripting についてはこちらのつよつよ記事を

www.yasuhisay.info

それぞれ参考にしてから読んでもらえると助かる(上記記事内にあることを再度説明はしない)

パターン1: 特定の文字列から始まる列名を抽出するパターン

例えば、列名に特定の prefix がついているパターンなど。今回は "tag_" という文字列が先頭についている列を抽出することを考える。

BigQuery における exapmle データセットの INFORMATION_SCHEMA.COLUMNS ビューには列名などの情報が含まれているので、そこから該当する列を抽出し、該当する列の文字列をクエリに代入している。

DECLARE target_columns STRING;

SET target_columns = (
    SELECT STRING_AGG(column_name)
    FROM `example.INFORMATION_SCHEMA.COLUMNS`
    WHERE table_name = 'hotel'
    AND SUBSTR(column_name, 1, 4) = 'tag_'
);

EXECUTE IMMEDIATE FORMAT("""
  SELECT %s
  FROM `your_dataset.hotel`
""", target_columns);

記事にしているときに気がついたけど、SUBSTR() を使っているのはよくないな。suffix など prefix ではない条件で抽出したいときにはどうしたらいいんだろうね

パターン2: 欠損のある列の抽出

次は null が含まれる列だけを抽出したいパターン。

これは本当に無理やりなんだけど、BigQuery では FOR .. IN が使用できるので、各列ごとに null が含まれるかチェックする方針でやっていくしかないのかなと思う。もっとスマートな書き方がある気がする……

cloud.google.com

DECLARE column_names ARRAY<STRING>;

-- 1. 列名を取得
SET column_names = (
  SELECT 
    ARRAY_AGG(column_name)
  FROM `your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'example_table'
);

-- 2. 欠損値のある列を保存する一時テーブルを作成
CREATE TEMP TABLE null_summary (
  column_name STRING
);

-- 3. 各列について欠損値をチェック
FOR target_column IN (
  SELECT *
  FROM UNNEST(column_names) AS column
)
DO
  -- 動的 SQL 文を生成
  EXECUTE IMMEDIATE FORMAT("""
    INSERT INTO null_summary (
      SELECT DISTINCT  '''%s''' AS column_name
      FROM `your_dataset.example_table`
      WHERE %s IS NULL
  )""", target_column.column, target_column.column
  );
END FOR;

-- 4. 結果を確認
SELECT column_name FROM null_summary;

BigQuery は配列(Array) や構造体(Struct) 型が扱えるんだけど、それについてはこの記事が参考になる。

blog.g-gen.co.jp

パターン3: 数値型の列の抽出

特定のデータ型の列だけ抽出したいパターン。data_type = 'INT64' だからこれでもなんとかなってるけど、Struct の中にあるデータの型に基づいて……とか言われたらどうすりゃいいんだろうね。

DECLARE column_names ARRAY<STRING>;
DECLARE target_columns STRING;

-- INT64 型の列を取得
SET column_names = (
  SELECT ARRAY_AGG(column_name)
  FROM `my_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'example_table'
    AND data_type = 'INT64'
);

SET target_columns = (
  SELECT
    STRING_AGG(column, ',')
  FROM UNNEST(column_names) AS column
);

-- 動的クエリの構築と実行
EXECUTE IMMEDIATE FORMAT("""
  SELECT %s
  FROM `my_dataset.example_table`
""", target_columns);

さいごに

なんとか BigQuery (GoogleSQL) だけでも突破できるっぽいけど、ごまかしごまかしやっているだけなので、素直に Python 使ったほうが良いな。

そしてもっと良い書き方おしえてください

シャツとソックスの日記

@shatsutosokks の日記帳です。

改訂新版前処理大全第5章2節「条件指定による列の抽出」をBigQueryで(無理やり)やってみる

パターン1: 特定の文字列から始まる列名を抽出するパターン

パターン2: 欠損のある列の抽出

パターン3: 数値型の列の抽出