BigQueryに用意されている全カラム選択系の便利な構文 EXCEPT/REPLACE

SQLでの全カラム選択でのカラム除外

ふだんSQLを使わない人がSQLで最初にええ…となるポイント、それは SELECT * の中で特定のカラムだけを除いての選択が行えないことなのではないでしょうか。

たとえば

WITH sample_data AS (
  SELECT
    1 AS id,
    'John Doe' AS name,
    25 AS age,
    'Engineer' AS occupation,
    'john.doe@example.com' AS email,
    '123-456-7890' AS phone,
    'USA' AS country,
    'New York' AS city,
    1000.50 AS salary,
    '2024-09-11' AS hire_date,
    'A+' AS blood_type,
    TRUE AS active,
    'M' AS gender,
    'single' AS marital_status,
    'blue' AS favorite_color,
    'pizza' AS favorite_food,
    'hiking' AS hobby,
    '1985-05-20' AS birthdate,
    10 AS years_of_experience,
    '12345' AS postal_code
)
SELECT *
FROM sample_data;

のような感じで、適当な20カラムのデータセットがあるとします。

ここから、 email と phone は個人情報なので除きたいな…と思ったとき

WITH sample_data AS (
  SELECT
    1 AS id,
    'John Doe' AS name,
    25 AS age,
    'Engineer' AS occupation,
    'john.doe@example.com' AS email,
    '123-456-7890' AS phone,
    'USA' AS country,
    'New York' AS city,
    1000.50 AS salary,
    '2024-09-11' AS hire_date,
    'A+' AS blood_type,
    TRUE AS active,
    'M' AS gender,
    'single' AS marital_status,
    'blue' AS favorite_color,
    'pizza' AS favorite_food,
    'hiking' AS hobby,
    '1985-05-20' AS birthdate,
    10 AS years_of_experience,
    '12345' AS postal_code
)
SELECT
    id,
    name,
    age,
    occupation,
    country,
    city,
    salary,
    hire_date,
    blood_type,
    active,
    gender,
    marital_status,
    favorite_color,
    favorite_food,
    hobby,
    birthdate,
    years_of_experience,
    postal_code
FROM sample_data;

と、たいていのデータベースではこんな感じで全部のカラムを指定する必要があります。

ではカラムがめちゃくちゃ多い場合にはどうするのかというと、

information_schema.columns のようなシステム上のカラム名情報を保持しているテーブルからカラム名一覧を取得してカンマで繋いだ文字列を生成…とか色々工夫してなんとかやっている方も多いのではないでしょうか。

しかし、BigQueryの場合はこの問題をいとも容易く解決する便利な書式がありますので今回はそちらを紹介します。

今回の話は、公式ドキュメントにおいては「Query syntax」のページをご参照いただければと思います。

特定のカラムを除外する

まず、先ほどの例のように特定のカラムを除外するパターンではどうするのかを紹介します。

これは、 SELECT * EXCEPT という便利な構文があります。

先ほどの例でいうと(WITH句部分は省略します)

SELECT * EXCEPT(email, phone)
FROM sample_data;

のように、 EXCEPT の中に除外するカラム名を指定するだけです。

たしかに消えていますね。

特定のカラムを置き換える

この発展系で抑えておきたいのが　置き換えを行える SELECT * REPLACE です。

たとえば先ほどの例で、 salary を年収に置き換えるために12倍する必要があるとします。

その場合は、 REPLACE の中に置き換えたいカラムを AS で指定した上で式を記述すれば良いです。

具体的には

SELECT * REPLACE(salary * 12 AS salary)
FROM sample_data;

のようにすればいいだけです。

たしかに12倍されているのがわかります。

これはあくまで置き換えなので、

REPLACE 内の AS の先は、 すでにテーブルの中に存在するカラム名を指定する必要があります。

たとえば、

SELECT * REPLACE(salary * 12 AS annual_salary)
FROM sample_data;

これは通りません。 sample_data 内に annual_salary というカラムは元々存在しないからです。

このように元々存在しないカラムの場合は追加ということなので

SELECT *, salary * 12 AS annual_salary
FROM sample_data;

のように、普通にカラムを足せば良いかと思います。

このとき、 salary は不要になるという話であれば、先ほどの EXCEPT との合わせ技で、

SELECT * EXCEPT(salary), salary * 12 AS annual_salary
FROM sample_data;

とすれば、 salary は消えて annual_salary が追加されたテーブルが完成します。

まとめ

今回はBigQueryで使える便利な SELECT * EXCEPT および SELECT * REPLACE の紹介でした。

SQLを書いている上での「こんなのあればいいのに…」を叶えているような構文だと思います。

ただし、一つ気をつけないといけないのが、前回の記事「QUALIFY句の使い方を知りWITH句を削る」でも書きましたが、こういったクエリを乱用しすぎるとクエリの汎用性が下がってくる(プラットフォームにロックインしてしまう)点です。

特に今回紹介したものはBigQuery特有の書き方であり、 Snowflakeでは EXCLUDE であったり、あるいは存在しなかったり、プラットフォーム毎に差があります。

なので、我々としては 場合によってはBigQueryにおいても面倒でも全部のカラム名を指定するようにしています。

ただ、ちょっとした分析を行いたい場合やテスト的に走らせたいクエリに対しては非常に有効なのではないでしょうか。

これでまた、誰かのストレス緩和に役立てていれば幸いです。

良きBigQueryライフをお送り下さい。