Zenn
🌊

GoogleSQL の pipe syntax と Spanner Graph GQL を比較する

2024/10/09に公開
BigQuery
GQL
Cloud Spanner
Cloud Spanner
ZetaSQL
tech

この記事では BigQuery に追加された pipe syntax そのものについては他の記事に任せて pipe syntax と Cloud Spanner に追加された GQL の対応関係について書きます。

pipe syntax についてより知りたい人はここからリンクされている Google Cloud 公式のドキュメントや論文や Medium などのコミュニティの記事を読むと良いかもしれません。

導入

BigQuery に追加された pipe syntax

2024年10月8日付のリリースノートで BigQuery の pipe syntax の Preview が発表されました。

You can now use pipe syntax anywhere you write GoogleSQL. Pipe syntax supports a linear query structure designed to make your queries easier to read, write, and maintain. This feature is in Preview.

プロジェクトごとにフォームでの利用申請が必要な限られたユーザに対するプレビューですが、ドキュメントはパブリックなので Public Preview に分類されるものです。

同時に Google Cloud blog にて Write better log queries, faster: Introducing pipe syntax in BigQuery and Cloud Logging
 という pipe syntax を含む BigQuery の複数の機能が Cloud Logging log analytics で有用なことが語られています。

詳しくは他の記事等を参照すればわかりますが、 pipe syntax は SQL の中の操作を単一機能で合成可能な pipe operator に分解することで、従来の SQL と共存可能な形で SQL の複数の問題を改善するものです。

上記記事では下記のように等価なクエリで比較されています。

-- Standard Syntax 
SELECT error_type, COUNT(user_id)
FROM (
   SELECT user_id, error_type, 
   count (log_id) AS error_count 
   FROM log_table 
   WHERE datetime BETWEEN DATETIME '2024-01-01' AND DATETIME '2024-01-31'
   GROUP BY user_id, error_type
)
GROUP BY error_type
WHERE error_count > 100;

-- Pipe Syntax 
FROM log_table 
|> WHERE datetime BETWEEN DATETIME '2024-01-01' AND '2024-01-31'
|> AGGREGATE COUNT(log_id) AS error_count GROUP BY user_id, error_type
|> WHERE error_count>100
|> AGGREGATE COUNT(user_id) AS user_count GROUP BY

サブクエリや SELECT FROM GROUP BY WHERE のような、上から下に読むことができない SQL がそれぞれ上から下に結果を処理するセマンティクスを持つ pipe operator を適用する形に書き直されています。(SQL と同様宣言的なものなので実際の処理は最適化されます。)

余談: ウォッチャーが見た pipe syntax

ところでこの pipe syntax は今月になっていきなり湧いて出てきたものではなく、公開情報としても OSS として公開されている GoogleSQL のフロントエンドそのものである ZetaSQL の 2024.08.01 リリースに含まれることがが確認されていました。

その後 Pipe syntax の追加された経緯やデザインとセマンティクス、有用性などについて書かれた論文 SQL Has Problems. We Can Fix Them: Pipe Syntax In SQL
 も8月中旬にはアクセス可能になっており、論文中では下記のように製品としての BigQuery や Cloud Spanner に追加されることも示唆されていました。(まだ Cloud Spanner で使えるようになる時期は分かっていませんが。)

We’re now exploring enabling pipe syntax for use in public BigQuery and Spanner products in Google Cloud.

この論文は海外のコミュニティなどでも話題になっており、ウォッチャーたちにとっては約2ヶ月待ちに待った機能と言って良いでしょう。

https://twitter.com/apstndb/status/1826455756717392198

全ユーザに公開せずにフォームでの登録を必要としているのはまだ徐々に Google 社外のユーザからのフィードバックを増やしていって GA までに破壊的変更をすることも検討しているという意思の現れかもしれません。

ZetaSQL で pipe syntax を試す

BigQuery ではまだ誰でも pipe syntax を使える状態にはなっていませんが、フロントエンドの ZetaSQL は OSS なので試すことができます。

ZetaSQL の README.md には Run with Docker として Docker イメージで ZetaSQL を試す方法が書かれています。

Releases にある Docker イメージを使うことを想定すると下記のような手順になります

$ curl -sL https://github.com/google/zetasql/releases/download/2024.08.2/zetasql_docker.tar.gz | docker load
$ docker run --init -it -h localhost -p 8080:8080 zetasql execute_query --web

# macOS などの非 AMD64 環境ではこちらで警告を無くせる
$ docker run --init -it -h localhost -p 8080:8080 --platform linux/amd64 zetasql execute_query --web

https://localhost:8080 で ZetaSQL Web UI にアクセスできるようになり、デフォルトで pipe syntax も有効になっています。

Pipe syntax と Spanner Graph の GQL を比べる

さて、ここまでの導入とは大きく話が変わりますが、今年の8月に Cloud Spanner に Spanner Graph の一部として、グラフ処理言語の ISO GQL が Public Preview で追加されています。Spanner Edition の Enterprise 以上で利用可能です。

GQL は SQL とは異なる言語であり、異なる言語を無理に対応づけるのはあまり良い考えではありません。
しかし、 Spanner Graph においては GoogleSQL と同じ API(ExecuteSQL, ExecuteStreamingSQL) で実行でき、 GRAPH_TABLE operator を使って SQL に GQL を組み込むことや、 GQL を SQL のサブクエリとして、 SQL を GQL のサブクエリとして使うこともできるため GQL と SQL は連続性があるものと考えることができます。
更に、 GQL の処理単位である statement (SQL の statement とは一致しない)は上から入力した working table を処理して新しい working table を出力します。これは従来の SQL よりも pipe syntax の pipe operator のセマンティクスとほぼ一致しています。

https://cloud.google.com/spanner/docs/reference/standard-sql/graph-intro#working_table

A working table refers to the intermediate table representing the input or output of a GQL statement.
A GQL statement receives an incoming working table and produces an outgoing working table.

まだ Cloud Spanner での pipe syntax 対応は公開されていませんし、 BigQuery での GQL 対応もほぼ影も形もないため、現状同じ処理系で実行することはできません。
それでも Pipe syntax と GQL の処理単位の対応関係をまとめると何かが見えてくるかもしれません。というわけでやってみましょう。

Pipe syntax と GQL の対応表

ここから GoogleSQL pipe syntax と GQL と、おまけに通常の GoogleSQL の syntax(GoogleSQL Standard Syntax) の比較を無理やり一つの表に詰め込んで説明してみます。

表記はおよそ下記のルールに従います。単純にするため省略可能でも AS alias などをそのまま書いていることなどもあるためフォーマルとは限りません。

  • [pattern] pattern は省略可能
  • {pattern1|pattern2|...} pattern1 か pattern2, その他列挙されているもののどれか
  • pattern, ... pattern が1回以上続く

なお、一般的に pipe operator は1段のサブクエリに対応するので、 GoogleSQL Standard Syntax の列では pipe operator の入力を input として説明します。

GoogleSQL Pipe Syntax GQL GoogleSQL Standard Syntax 備考
FROM input N/A(直接的には) SELECT * FROM input
|> SELECT expr AS alias, ... WITH expr AS alias, ...
or
RETURN expr AS alias, ... ※		 SELECT expr AS alias, ... FROM input
|> EXTEND expr AS alias, ... LET name = expr, ... SELECT *, expr AS alias, ... FROM input
|> SET name = expr, ... N/A SELECT * REPLACE (expr AS name) FROM input
|> DROP name, ... N/A SELECT * EXCEPT (name) FROM input
|> RENAME old_name AS new_name, ... N/A SELECT * EXCEPT (old_name, ...), old_name AS new_name, ... FROM input ※ SQL では * EXCEPT (old_name, ...) の部分が空になるとエラーになる。
|> AS alias N/A SELECT input AS alias FROM input
or
(SELECT * FROM input) AS alias
|> WHERE bool_expr FILTER bool_expr SELECT * FROM input WHERE bool_expr ※ input の中身によっては HAVING, QUALIFY のユースケースにも対応
|> LIMIT count [OFFSET skip_rows] LIMIT count [OFFSET skip_rows]
or
LIMIT count [SKIP skip_rows]
or
RETURN * LIMIT count [OFFSET skip_rows] 		SELECT * FROM input LIMIT count [OFFSET skip_rows] ※ GQL の LIMIT, OFFSET はそれぞれ独立した statement で SKIP は OFFSET の別名
|> AGGREGATE aggregation_expr AS alias, ... WITH aggregation_expr AS alias, ...
or
RETURN aggregation_expr AS alias, ... 		SELECT aggregation_expr AS alias, ... FROM input aggregation_expr は集約関数の呼び出しを含む
|> AGGREGATE aggregate_expr AS alias, ... GROUP BY groupable_items AS alias, ... WITH groupable_items AS alias, ..., aggregation_expr AS alias, ... GROUP BY groupable_items, ...
or
RETURN groupable_items AS alias, ..., aggregation_expr AS alias, ... GROUP BY groupable_items, ... 		SELECT groupable_items AS alias, ..., aggregate_expr AS alias, ..., FROM input GROUP BY groupable_items, ...
|> AGGREGATE aggregate_expr AS alias [{ASC|DESC}], ... GROUP [AND ORDER] BY groupable_items AS alias [{ASC|DESC}], ... RETURN groupable_items AS alias, ..., aggregate_expr AS alias GROUP BY groupable_items, ... ORDER BY groupable_items {ASC|DESC}, ..., aggregate_expr {ASC|DESC}, ... SELECT groupable_items AS alias, ..., aggregate_expr AS alias, ..., FROM input GROUP BY groupable_items, ... ORDER BY groupable_items {ASC|DESC}, ..., aggregate_expr {ASC|DESC}, ... ASC, DESC が一つもなく GROUP AND ORDER BY の場合 groupable_items すべて ASC NULLS FIRST 指定扱い
|> ORDER BY expr {ASC|DESC}, ... ORDER BY expr {ASC|DESC}, ...
or
RETURN * ORDER BY expr {ASC|DESC}, ... 		SELECT * FROM input ORDER BY expr {ASC|DESC}, ... GQL の ORDER BY statement は LIMIT statement と組み合わせて Top-N するためにあるため、最終的なクエリ結果をソートしたい場合は必ず RETURN ... ORDER BY を使う。
|> [join_type] JOIN from_item AS alias [ON (bool_expr)] N/A SELECT * FROM input [join_type] JOIN from_item AS alias [ON (bool_expr)] ON 以外にも USING も使用可能なことは省略
|> CALL table_function (argument, ...) AS alias N/A SELECT * FROM table_function(input, argument, ...) table_function がテーブル引数を受け取るのが最後の引数だったとしても、引数のうち最初にテーブルを受け取る引数に input が割り当てられる。
|> WINDOW window_expr OVER(...) AS alias , ... N/A SELECT *, window_expr OVER(...) AS alias 論文では EXTEND でも同じことができるので不要という結論
|> TABLESAMPLE SYSTEM (percent PERCENT) N/A SELECT * FROM input TABLESAMPLE SYSTEM (percent PERCENT)
|> PIVOT (aggregate_expr FOR input_col IN (pivot_col , ...)) N/A SELECT * FROM input PIVOT (aggregate_expr FOR input_col IN (pivot_col , ...))
|> UNPIVOT (values_column FOR name_column IN (column_to_unpivot [, ...])) N/A SELECT * FROM input PIVOT (aggregate_expr FOR input_col IN (pivot_col , ...))
ここから pipe syntax ではなく GQL にある statement
|> JOIN UNNEST(array_expr) [WITH OFFSET AS name] FOR element in array_expr [WITH OFFSET AS name] SELECT * FROM input JOIN UNNEST(array_expr) [WITH OFFSET AS name]
|> JOIN UNNEST(array_expr) [WITH OFFSET AS name] FOR element in array_expr [WITH OFFSET AS name] SELECT * FROM input JOIN UNNEST(array_expr) [WITH OFFSET AS name]
N/A [ OPTIONAL ] MATCH graph_pattern N/A 等価ではないが CREATE PROPERTY GRAPH で定義したプロパティグラフに対する INNER JOIN または OUTER JOIN (OPTIONAL) に対応する。
AGGREGATE, LIMIT, SELECT の組み合わせ RETURN [{ALL|DISTINCT}] {expr AS alias|*}, ... [group_by_clause] [order_by_clause] [limit_and_offset_clauses] SELECT [{ALL|DISTINCT}] {expr AS alias|*}, ... [group_by_clause] [order_by_clause] [limit_and_offset_clauses] GQL の RETURN は WITH と違い途中に書くことができない。
|> SELECT ... RETURN ... NEXT SELECT * FROM (SELECT ... FROM input) NEXT は RETURN で終わる一連の statement からなる linear graph query 同士を連結するために使われる。
AGGREGATE, SELECT の組み合わせ WITH [{ALL|DISTINCT}] {expr AS alias|*}, ... [GROUP BY expr, ...] SELECT [{ALL|DISTINCT}] {expr AS alias|*}, ... FROM input [GROUP BY expr, ...] GQL の WITH は RETURN と違い最後に書くことができない。
(query) {UNION|INTERSECT|EXCEPT} {ALL|DISTICNT} (query) query {UNION|INTERSECT|EXCEPT} {ALL|DISTICNT} query query {UNION|INTERSECT|EXCEPT} {ALL|DISTICNT} query GQL のみ列の順序ではなく列名でマッチする。pipe syntax に集合演算専用の構文は用意されておらず通常の SQL のものを適用するため必要に応じて括弧で囲む必要がある。
  • 現れる {ASC|DESC} は実際には {NULLS FIRST | NULLS LAST} などを含む。

これでまとめ終わったはずです。
驚くことに、 GQL はグラフデータベース用の言語でありながら、グラフにパターンマッチした結果を新しい working table として得るための MATCH statement 以外は一つのプロパティグラフに関係するものはありません。中間結果としてはテーブルを処理するための言語だというのは SQL も GQL も同じです。
かなりの割合が pipe syntax と GQL では対応関係にあるが、 pipe syntax の方が細かい書き方が出来る上に簡潔に書けるというように感じたのではないでしょうか。

応用: GQL で pipe syntax の真似をする

前述した通り、 SQL から直接 GQL のプロパティグラフにアクセスすることや、逆に GQL で直接 SQL テーブルにアクセスすることはできませんが、 SQL に GQL を、 GQL に SQL を埋め込むことで似たことができます。

具体的には GRAPH_TABLE operator を使うことで SQL の FROM や JOIN の対象として GQL をテーブル式として埋め込むことができます。

FROM GRAPH_TABLE (
  property_graph_name
  multi_linear_query_statement
) [ [ AS ] alias ]

逆に GQL から SQL のテーブルを使う方法はドキュメント上は明記されていませんが、 GQL からは GoogleSQL の式の一つである ARRAY サブクエリが使えるため、 GQL FOR statement と組み合わせることで任意のテーブルを GQL に持ってくることができます。

FOR name IN ARRAY(SELECT ...)

試しにこれを使って BigQuery pipe syntax のサンプルを一つ Spanner Graph の GQL に書き換えてみましょう。

今回プロパティグラフにパターンマッチしてデータを取得するための GQL MATCH statement は使いませんが、 GQL を使うために必要なので空のプロパティグラフを作成しましょう。 CREATE PROPERTY GRAPH の NODE TABLES は必須なので多分これが一番早いと思います。

CREATE TABLE IF NOT EXISTS EmptyNodes(PK INT64) PRIMARY KEY(PK);
CREATE OR REPLACE PROPERTY GRAPH EmptyGraph NODE TABLES(EmptyNodes);

BigQuery pipe syntax の例のためののサンプルスキーマとそこに入れるデータを Cloud Spanner データベースに投入します。BigQuery と異なり Cloud Spanner ではプライマリキーが必須なので適宜サロゲートキーを導入します。

CREATE TABLE produce(surrogate INT64 NOT NULL, item STRING(MAX) NOT NULL, sales INT64 NOT NULL, category STRING(MAX)) PRIMARY KEY(surrogate);
INSERT INTO produce(surrogate, item, sales, category)
VALUES(1,'apples',2,'fruit'),
(2,'apples',7,'fruit'),
(3,'carrots',0,'vegetable'),
(4,'bananas',15,'fruit');

CREATE TABLE item_data(item STRING(MAX) NOT NULL, id STRING(MAX) NOT NULL) PRIMARY KEY(id);
INSERT item_data(item, id) (
  SELECT "apples" AS item, "123" AS id
  UNION ALL
  SELECT "bananas" AS item, "456" AS id
  UNION ALL
  SELECT "carrots" AS item, "789" AS id
);

これでサンプルデータの用意が終わりました。次の2つのテーブルの JOIN と集約を含むクエリを書き換えてみましょう。

FROM produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN mydataset.item_data USING(item);

GQL で書くと下記のようなクエリに書き換えることができます。

GRAPH EmptyGraph
FOR p IN ARRAY(SELECT AS STRUCT * FROM produce)
FILTER p.sales > 0
WITH p.item AS p_item, SUM(p.sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY p.item
FOR id IN ARRAY(SELECT id FROM item_data WHERE p_item = item)
RETURN p_item AS item, total_sales, num_sales, id

このクエリを spanner-cli で実行すると、下記のような結果となります。

spanner> GRAPH EmptyGraph
      -> FOR p IN ARRAY(SELECT AS STRUCT * FROM produce)
      -> FILTER p.sales > 0
      -> WITH p.item AS p_item, SUM(p.sales) AS total_sales, COUNT(*) AS num_sales
      ->    GROUP BY p.item
      -> FOR id IN ARRAY(SELECT id FROM item_data WHERE p_item = item)
      -> RETURN p_item AS item, total_sales, num_sales, id;
+---------+-------------+-----------+-----+
| item    | total_sales | num_sales | id  |
+---------+-------------+-----------+-----+
| apples  | 9           | 2         | 123 |
| bananas | 15          | 1         | 456 |
+---------+-------------+-----------+-----+

ORDER BY を指定していないことにより順番の保証はありませんが、 pipe syntax のドキュメントの例 とも一致しているので書き換えは正しいことが分かります。

FROM mydataset.produce
|> WHERE sales > 0
|> AGGREGATE SUM(sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY item
|> JOIN mydataset.item_data USING(item);

/*---------+-------------+-----------+-----+
 | item    | total_sales | num_sales | id  |
 +---------+-------------+-----------+-----+
 | apples  | 9           | 2         | 123 |
 | bananas | 15          | 1         | 456 |
 +---------+-------------+-----------+-----*/

雑感として GQL と pipe syntax の表現力の違いについて列挙してみましょう。

  • 中間データとしてテーブルを入力してテーブルを出力する処理をする言語として共通点があるが、総じて GQL の方が洗練されていない。
  • GQL の WITH, RETURN にも GoogleSQL の expression.* があれば STRUCT を扱いやすくなるはず。
  • Pipe syntax の AGGREGATE は GQL や SQL の GROUP BY と比べて同じ列を繰り返し書く必要が減り確かに簡潔。
  • FOR IN ARRAY を使った SQL テーブルの JOIN についてはやはりハックとしか言えないので、 JOIN 条件なども考えると大人しく SQL に持っていってやる方が良い。
  • 最後の GQL RETURN statement でやっているのは実質 RENAME そのものなので、 RENAME があると嬉しいし、可能であれば RETURN は省略したい。

別解

ARRAY サブクエリの中ではなく、サブクエリの外で FILTER を使って JOIN 条件を表現することもできますが、その場合は STRUCT の扱いが多くなります。

GRAPH EmptyGraph
FOR p IN ARRAY(SELECT AS STRUCT * FROM produce)
FILTER p.sales > 0
WITH p.item, SUM(p.sales) AS total_sales, COUNT(*) AS num_sales
   GROUP BY p.item
FOR i IN ARRAY(SELECT AS STRUCT * FROM item_data)
FILTER item = i.item
RETURN item, total_sales, num_sales, i.id

Discussion