GoogleSQLのパイプ構文（|>）の紹介

はじめに

googleの研究チームからGoogleSQLのパイプ構文についての論文が公開されていたので紹介します。

https://research.google/pubs/sql-has-problems-we-can-fix-them-pipe-syntax-in-sql/
ダウンロードボタンから論文のPDFをダウンロードできます。

論文サマリ

論文のはじまり(概要)

SQLはデータ操作の事実上の標準言語として広く普及していますが、学習と使用が難しく、
拡張性にも課題があります。本論文では、SQLのこれらの問題を解決するために、
他の現代的なデータ言語で実績のあるパイプ構文をSQLに導入することを提案しています。
パイプ構文により、SQLはより柔軟で拡張性の高い言語となり、
既存のSQLエコシステムとユーザーベースを活用しながら、学習と使用が容易になります。

SQLの評価

構文の問題点

• 句の順序
  • SQLの句の順序は固定されており、実際のデータフローを反映していません。
• 冗長な句
  • SQLは、WHERE、HAVING、QUALIFYなど、同じフィルタリング操作を異なる場所で異なる構文で表現しています。
• サブクエリの必要性
  • フィルタリング、集計、射影などの多くの単純な操作は、サブクエリを使用しなければ表現できません。
• 「内側から外側へ」のデータフロー
  • ネストされたサブクエリを使用するため、データフローが複雑になり、クエリの理解が困難になります。
• 拡張性の低さ
  • 新しいクエリ操作をSQLに追加することは困難です。

SQLの利点

• 基礎となるセマンティクス
  • 関係データモデルと関係演算は、優れた合成性と宣言的なセマンティクスを提供します。
• 普及
  • SQLはほぼすべてのデータベースとクエリシステムで動作し、広範なエコシステムとユーザーベースを持っています。

なぜより良い言語を作らないのか？

• 機能
  • SQLには長年にわたって蓄積された多くの機能があり、新しい言語が同じレベルの機能を提供することは困難です。
• 実装
  • 新しい言語は、実用的に使用できるようになるまで、優れた実装が必要です。
• 学習
  • 新しい言語は、ユーザーがそれを十分に学習するまで、役に立ちません。
• 相互運用性
  • 新しい言語は、多くの場合、既存のSQLとの相互運用性が不足しており、段階的な導入が困難です。

SQLにおけるパイプ構文

構文

• パイプ演算子 |> を使用して、任意の順序で複数の演算を連結できます。
• 句の順序は意味的な評価順序と一致し、柔軟性と可読性が向上します。
• パイプ演算子は、既存のSQL句の多くを再利用し、学習コストを最小限に抑えます。

この論文には、標準SQLとパイプ構文SQLの比較例を含む多数のクエリが記載されています。以下に、その一部を抜粋します。

• ベンチマークのクエリ(結合と集計)

  • 標準SQL

  SELECT c_count, COUNT(*) AS custdist
  FROM
  ( SELECT c_custkey, COUNT(o_orderkey) c_count
  FROM customer
  LEFT OUTER JOIN orders ON c_custkey = o_custkey
  AND o_comment NOT LIKE '%unusual%packages%'
  GROUP BY c_custkey
  ) AS c_orders
  GROUP BY c_count
  ORDER BY custdist DESC, c_count DESC;
  

  • パイプ構文SQL

  FROM customer
  |> LEFT OUTER JOIN orders ON c_custkey = o_custkey
  AND o_comment NOT LIKE '%unusual%packages%'
  |> AGGREGATE COUNT(o_orderkey) c_count
  GROUP BY c_custkey
  |> AGGREGATE COUNT(*) AS custdist
  GROUP BY c_count
  |> ORDER BY custdist DESC, c_count DESC;
  

• 複数レベルの集計

  • 標準SQL (サブクエリを使用)

  SELECT c_count, COUNT(*) AS custdist
  FROM 
  (
      SELECT c_custkey, COUNT(o_orderkey) c_count
      FROM customer
      LEFT OUTER JOIN orders ON c_custkey = o_custkey
      AND o_comment NOT LIKE '%unusual%packages%'
      GROUP BY c_custkey
  ) AS c_orders
  GROUP BY c_count
  ORDER BY custdist DESC, c_count DESC;
  

  • パイプ構文SQL

  FROM customer
  |> LEFT OUTER JOIN orders ON c_custkey = o_custkey
  AND o_comment NOT LIKE '%unusual%packages%'
  |> AGGREGATE COUNT(o_orderkey) c_count
  GROUP BY c_custkey
  |> AGGREGATE COUNT(*) AS custdist
  GROUP BY c_count
  |> ORDER BY custdist DESC, c_count DESC;
  

• 射影演算子の例

  FROM orders
  |> SET o_orderstatus = LOWER(o_orderstatus)
  |> EXTEND ROUND(o_totalprice) AS dollars
  |> DROP o_comment
  |> AS result
  |> SELECT o_custkey, result.dollars
  

• 集計の例

  • テーブル全体の集計

  FROM orders
  |> AGGREGATE SUM(o_totalprice) AS price, COUNT(*) AS cnt
  

  • グループ化による集計

  FROM orders
  |> AGGREGATE SUM(o_totalprice) AS price, COUNT(*) AS cnt
  GROUP BY EXTRACT(year FROM o_orderdate) AS year
  

• フィルタリングの例

  FROM lineitem
  |> EXTEND l_quantity * l_extendedprice AS cost
  |> EXTEND cost * l_discount AS discount
  |> WHERE discount > 1000
  |> AGGREGATE SUM(cost), SUM(discount)
  

• テーブル値関数 (TVF) の例

  • 標準SQL

  SELECT *
  FROM ML.PREDICT(
  MODEL `my_project.imdb_classifier`,
  (
  SELECT *
  FROM ML.PREDICT(
  MODEL `my_project.nnlm_embedding_model,`,
  (SELECT '<text>' AS input, 7 AS rating))
  )
  )
  

  • パイプ構文SQL

  SELECT '<text>' AS input, 7 AS rating
  |> CALL ML.PREDICT(MODEL `my_project.nnlm_embedding_model`)
  |> CALL ML.PREDICT(MODEL `my_project.imdb_classifier`)
  

• PIVOT演算子の例

  • 標準SQL

  SELECT *
  FROM (
  SELECT n_name, c_acctbal AS bal, c_mktsegment
  FROM customer JOIN nation ON c_nationkey = n_nationkey
  ) PIVOT(SUM(bal) AS bal
  FOR n_name IN ('PERU', 'KENYA', 'JAPAN'))
  

  • パイプ構文SQL

  FROM customer JOIN nation ON c_nationkey = n_nationkey
  |> SELECT n_name, c_acctbal AS bal, c_mktsegment
  |> PIVOT(SUM(bal) AS bal
  FOR n_name IN ('PERU', 'KENYA', 'JAPAN'))
  

論文中には上記以外にもクエリ例が紹介されています。

データモデルとセマンティクス

• 中間結果と最終結果はテーブルです。
• パイプ演算子は、1つのテーブルを入力として受け取り、1つのテーブルを出力します。
• パイプ構文を使用しても、クエリは宣言的なセマンティクスを持ちます。
• パイプ演算子は、基本的に関係演算と1対1に対応します。

拡張性

• テーブル値関数
  • パイプ構文を使用すると、テーブル値関数をクエリに直接組み込むことができ、SQLを自然に拡張できます。
• 組み込み演算子
  • パイプ構文は、新しい演算子を簡単に追加できるため、SQLの拡張性を向上させます。
• 実験的な拡張
  • デバッグ演算子など、標準SQLでは表現できない機能をパイプ構文で実現できます。

評価

• Googleでの使用状況
  • Google社内での利用状況の分析から、パイプ構文がユーザーの生産性とユーザー体験を大幅に向上させることが示されています。
• 複雑なクエリ
  • パイプ構文は、線形構造のクエリを自然に表現できます。ツリー構造のクエリも、サブクエリや一時テーブルなどを活用することで表現できます。
• 編集とデバッグのワークフロー
  • パイプ構文は、クエリを上から下へ順番に実行できるため、編集とデバッグが容易になります。
• IDEとツールサポート
  • パイプ構文は、オートコンプリート、自動リファクタリング、AIアシスタント、インタラクティブデバッガなどのIDE機能を向上させます。
• SQLコードジェネレーターとリライター
  • パイプ構文は、SQLコードの生成と書き換えを容易にし、生成されたクエリがより簡潔で人間が読めるようになります。
• AIとの潜在的なアプリケーション
  • パイプ構文は、AI、特に大規模言語モデル（LLM）がSQLクエリを理解し生成するのを容易にします。
• SQLからパイプ構文への変換
  • 標準構文からパイプ構文への自動変換は、ユーザーがパイプ構文を学習し、クエリを移行するのに役立ちます。

実装

• GoogleSQL
  • GoogleSQLは、Googleの複数のクエリエンジンで共有される再利用可能なコンポーネントとして実装されています。
• パイプ構文のサポート
  • パイプ構文は、GoogleSQLの解析および分析コード内に完全に実装されており、クエリエンジンが有効にするために必要な作業は最小限です。
• 複雑さ
  • 適切に構造化された実装により、パイプ構文の追加に伴う複雑さは管理可能です。

論文の締め

SQLは、その50年の歴史の中で、すべてのデータベースとクエリエンジンの標準言語として大きな成功を収めてきました。
しかし、その成功は、SQLを必要以上に学習および使用困難にする深刻な言語設計上の問題にもかかわらず達成されたものです。

SQLは繰り返し批判され、多くの代替言語が提案されてきました。
しかし、どの代替言語も、幅広い成功や市場浸透を達成していません。
SQLには、宣言型クエリ処理に特に適した多くの機能があり、
SQLのエコシステムとユーザーベースは、容易に置き換えるには大きすぎます。
SQLが近い将来に後継言語に置き換えられる可能性は極めて低いでしょう。

しかし、私たちはSQLの欠点を受け入れる必要はありません。
この言語は修正可能です！本論文では、他の言語やAPIに触発されたパイプ構造のデータフローが、
SQLにどのようにして適度な労力で追加できるかを示しています。
結果として得られる言語は、依然としてSQLですが、より良いSQLです。
より柔軟で、拡張性が高く、使いやすくなっています。
熟練したSQLユーザーは、最小限の労力でパイプ構文を学習し、
採用することができ、即座に生産性を向上させ、SQLとの関わり方を変革することができます。
これらの改善は、段階的な利便性や表面的な改善にとどまりません。
パイプ構文は、SQLを扱う全く新しい方法、SQLツールの改善の機会、
そして将来の言語革新への扉を開きます。

これまでの私たちの経験では、パイプ構文はSQL構文の魅力的な改善であり、
業界全体での採用に値する可能性があることが示唆されています。
SQLを置き換えることは、必要でもなければ、望ましくも、現実的でもありません。
私たちは、言語の中からSQLの最も深刻な課題を修正することができます。

おわりに

パイプ構文では、SQLの処理を上から下へ順番に記述できるため、
ネストされたサブクエリの削減や複雑な句の繰り返しを減らせることで、
可読性が向上すると思われるので
うまく活用できればクエリの作成と修正が容易になりそうですね。

追記(20241009)

https://cloud.google.com/bigquery/docs/pipe-syntax

https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax