BigQueryの標準SQLでできないこと

はじめに

BigQueryを触り始めた頃、標準SQLというからにはできるだろうと思ったのにできなかったことがいくつかあり戸惑っていました。
他のRDBMSの経験がある方がBigQueryを触り始めた時にハマらないよう、少しでもお役に立てればと思い、BigQueryの標準SQLではできないことを雑多にピックアップしてみました。

VALUES句

PostgreSQLやMySQLで使える VALUES 句は使えません。
なので、無からテーブルをつくる場合、UNION ALL を使うか、構造体の配列を駆使する必要があります。
BigQuery で無からリレーションを出現させる（StandardSQL 編） - Qiita

-- PostgreSQL
WITH
    t(foo, bar) AS (
        VALUES
            ('aaa', 1),
            ('bbb', 2)
    )
SELECT
    t.*
FROM
    t

-- UNION
WITH
    t AS (
        SELECT 'aaa' AS foo, 1 AS bar UNION ALL
        SELECT 'bbb' AS foo, 2 AS bar 
    )
SELECT
    t.*
FROM
    t

UNIONの場合、行や列が増えるとカラム名のエイリアスや UNION ALL を毎回記述するのが面倒です。
構造体の配列は最初に列名と型を記述すれば、あとは値を記述するだけで済みます。

WITH
    t AS (
        SELECT
            *
        FROM
            UNNEST(ARRAY<STRUCT<foo STRING, bar INT64>>[
                ('aaa', 1),
                ('bbb', 2)
            ])
    )
SELECT
    t.*
FROM
    t

また、他のRDBMSと同様、INSERT文の値には VALUES 句を利用できます。

INSERT INTO baz_table
    (foo, bar)
VALUES
    ('aaa', 1),
    ('bbb', 2)

WITH句のカラム名

上記のPostgreSQLの例のように、 WITH 句内のクエリにカラム名を指定することはできません。

再帰クエリ

連番を生成したり、組織階層、承認ルート、部品表などの階層構造を扱う時に再帰クエリを使うことがあります。
Oracleでは CONNECT BY 、PostgreSQLでは WITH RECURSIVE を使いますが、BigQueryにはどちらもありません。
連番生成は GENERATE_ARRAY を利用することでできますが、こんなパズルを解くには再帰クエリが便利なので、使えないのは残念です。（実際のBigQueryの業務で再帰クエリが必要になった場面はまだありませんが、、、）
ZetaSQLにはきてるらしいので、もしかしたら近い将来BigQueryでも使える様になるかもしれません。

えっちょっと待って、 ZetaSQL に WITH RECURSIVE 入っているんですけどhttps://t.co/MZ1t1pZXWU
— _ (@apstndb) June 5, 2020

2022-02-03 できるようになりました！
Release notes | BigQuery | Google Cloud
Query syntax | BigQuery | Google Cloud

カラム名にUnicode文字

2020年中のアップデートで、テーブル名にUnicode文字を使えるようになりましたが、カラム名にはまだ利用できません。
また、クエリ内の最終SELECTリストのカラム名エイリアスについても、Unicode文字を使うことはできません。
実行前の解析時にはエラーはでませんが、実行時に Invalid field name "あああ" のようなエラーが表示されます。

2022-03-22
最終的な列名エイリアスにもマルチバイト文字を指定することができるようになったようです。
アドホックなレポートをシュッと出すときにたすかります。

WITH
    t AS (
        SELECT
            1 AS `あああ`, -- 中間テーブルでは使える
            2 AS b,
    ),
    t1 AS (
        SELECT
            *, -- これもOK
            `あああ` AS c, -- これもOK
        FROM
            t
    )
SELECT
    -- `あああ`, -- これはNGだけど
    b, -- これはOK
    c, -- これもOK
FROM
    t1

DUAL表

OracleのDUAL表にあたるものはありません。
PostgreSQLやMySQLと同様に、 FROM 句を省略します。

SELECT 1

EXCEPT ALL, INTERSECT ALL

集合演算子 EXCEPT INTERSECT において、重複を省かない ALL はありません。
EXCEPT DISTINCT INTERSECT DISTINCT があります。
UNION ALL はあります。

おまけ　（2020年中のアップデートでできるようになったもの）

ALTER TABLE ADD COLUMN
TRUNCATE TABLE
DDL におけるUnicode文字列
スクリプト、ストアドプロシージャ
動的SQL（EXECUTE IMMEDIATE）
ORDER BY のNULLS FIRST、NULLS LAST
連結演算子 ||

こちらは、BigQuery Advent Calendar 2020の4日目の記事、
BigQueryの2020年アップデートを（だいたい）全部振り返る | by Yuta Hono | google-cloud-jp | Dec, 2020 | Medium
を参考にさせていただきました。

さいごに

BigQueryはアップデートが頻繁にあるので、来年もリリースノートを注視しつつ、この記事も更新していきたいと思います。