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

## はじめに

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



## VALUES句

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

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

```sql
-- 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` を毎回記述するのが面倒です。
構造体の配列は最初に列名と型を記述すれば、あとは値を記述するだけで済みます。

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

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

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

## WITH句のカラム名

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


## 再帰クエリ

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


<blockquote class="twitter-tweet" data-partner="tweetdeck"><p lang="ja" dir="ltr">えっちょっと待って、 ZetaSQL に WITH RECURSIVE 入っているんですけど<a href="https://t.co/MZ1t1pZXWU">https://t.co/MZ1t1pZXWU</a></p>&mdash; _ (@apstndb) <a href="https://twitter.com/apstndb/status/1268779931837755392?ref_src=twsrc%5Etfw">June 5, 2020</a></blockquote>
<script async src="https://platform.twitter.com/widgets.js" charset="utf-8"></script>


2022-02-03 できるようになりました！
[Release notes  |  BigQuery  |  Google Cloud](https://cloud.google.com/bigquery/docs/release-notes#February_02_2022)
[Query syntax  |  BigQuery  |  Google Cloud](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#with_clause)

## カラム名にUnicode文字

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

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

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/15117/fcafd672-714d-3df2-8050-d52414a0cb2c.png)


```sql
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` 句を省略します。

```sql
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](https://medium.com/google-cloud-jp/bigquery-2020-update-summary-dfd245e77e44)
を参考にさせていただきました。

## さいごに

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