【解説】Google Cloudブログ:パイプ構文による SQL の変革が BigQuery と Cloud Logging でも可能に

はじめに

Google Cloudからブログ 「パイプ構文による SQL の変革が BigQuery と Cloud Logging でも可能に」が公開されました。
このブログを読んでわかりづらいところを自分なりに解釈して解説したいと思います。各章はブログに合わせています。なお、この機能は利用申請が必要な Preview 版となります。

パイプ構文とは

このブログでは、従来のSQLの標準構文の問題を示し、これを解決する手段としてGoogle Cloudの新しい機能であるパイプ構文を紹介しています。パイプ構文は標準構文と単語が同じなため学習コストが低く、さらに可読性が高いという特徴を持っています。

SQL の構文の問題点

ブログでは、次のSQLの構文の問題点が3つ記載してあります。サンプルコードを示しながらそれぞれを解説します。

SQLはこれらの問題のため、新規ユーザーは当然として、エキスパートもSQLの読み書きに労力がかかります。そのため、SQLに代わる何かがあると便利ですが、新しいものを学ぶ学習コストも変わります。そのため、**Google CloudはSQLにその何かを追加することで、SQLの優れた機能を維持して使えるものが必要、それがパイプ構文である、**としています。

①厳格な構造: クエリは特定の順序に従う必要があり（SELECT … FROM … WHERE … GROUP BY…）、そのとおりにしない場合はサブクエリや他の複雑なパターンが必要になります。

（解説)
サブクエリや他の複雑なパターンが必要になるとはどういうことでしょうか。

例として次のクエリを見てみましょう。従業員テーブルから各従業員の部署とその人数を求めるものです。FROM句の中にサブクエリが入っています。このサブクエリでは、2020/1/1以降に採用された従業員のレコードを抽出します。この抽出された従業員のレコードをテーブルと見立てて、所属部署毎の人数をカウントします。

SELECT department, COUNT(*) AS employee_count
FROM (
    SELECT *
    FROM employees
    WHERE hire_date > '2020-01-01'
) AS recent_hires
GROUP BY department;

このサブクエリを使った複雑なクエリを、WHERE句を使ってシンプルにしてみます。逆に言うと、こういった便利な句を使わなければクエリが複雑になるということをブログで言っているのでしょう。

SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department;

②不便な内外のデータフロー: クエリが中ほどから始まり、サブクエリまたは共通テーブル式（CTE）に埋め込まれた FROM 句で始まるロジックが外に向かって構築されます。

(解説)
共通テーブル式（CTE）とは、SQLクエリの一部として一時的な結果セットを定義する方法です。共通テーブル式（CTE）とは、SQLクエリの一部として一時的な結果セットを定義する方法です。

次のクエリでは、クエリの本体であるSELCT分が真ん中から記載されており、テーブルであるRecentHiresの定義は冒頭にあります。普通はSELECT分から読むので、上から下に読み進めることはできません。サブクエリの場合も、同様です。

WITH RecentHires AS (
    SELECT *
    FROM employees
    WHERE hire_date > '2020-01-01'
)
SELECT department, COUNT(*) AS employee_count
FROM RecentHires
GROUP BY department;

この例では、まず WITH 句を使用して RecentHires という名前のCTEを定義し、その中で hire_date が 2020-01-01 より後の従業員を選択しています。その後、FROM句で RecentHires を使用しています、

ちなみに次のサブクエリで記載すると同じ結果を得られます。この場合もFROM句の中のSELECT分から読み、冒頭にある冒頭のSELECT分を読むことになります。

SELECT department, COUNT(*) AS employee_count
FROM (
    SELECT *
    FROM employees
    WHERE hire_date > '2020-01-01'
) AS recent_hires
GROUP BY department;

ちなみに、CTEとサブクエリでは大きな違いがあります。**CTEは同じクエリ内で複数回参照することができます。一方で、サブクエリは一度しか使用できません。**同じ結果セットを複数回使用する場合、同じサブクエリを繰り返し記述する必要があります。そのため、CTEの方が可読性が高いです。いずれにせよ、不便な内外のデータフローであるということでしょう。可読性が悪そうです。

SQL パイプ構文の導入: 両方の特長を活かす

"Google Cloud では、データ分析を簡単かつ直感的に行えるようにすることを使命としています。そこで、パイプ構文を導入しました。" と記載があります。

(解説)
この部分を詳しく見ていきましょう。

例：BigQuery の公開データセットを使用して、シカゴにおけるタクシーの年間平均利用回数を payment_type（支払いの種類）別に調べるとします。

この調査のために、標準の構文だとサブクエリを使います。FROM句の中に、［SELECT EXTRACT(YEAR FROM trip_start_timestamp)］から始まるサブクエリが入っています。

-- 標準構文 
SELECT AVG(num_trips) AS avg_trips_per_year, payment_type
FROM
(
 SELECT EXTRACT(YEAR FROM trip_start_timestamp) as year, payment_type, COUNT(*) AS num_trips
 FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
 GROUP BY year, payment_type
)
GROUP BY payment_type
ORDER BY payment_type;

ここで、SELECT EXTRACT(YEAR FROM trip_start_timestamp)は、SQLのクエリで、trip_start_timestampという日時型のカラムから年の部分だけを抽出するための関数です。例えば、trip_start_timestampが「2024-11-22 18:28:46」の場合、この関数は「2024」を返します。

FROM句の中のサブクエリの結果を表形式にすると以下のようになります。COUNT(*) AS num_tripsは、各年と支払い方法ごとの乗車回数をカウントし、num_tripsというエイリアスを付けます。

year	payment_type	num_trips
2019	Credit Card	150,000
2019	Cash	80,000
2020	Credit Card	120,000
2020	Cash	70,000

さらに、外部のクエリで、支払い方法ごとの年間平均乗車回数を計算します。AVG(num_trips) AS avg_trips_per_yearでは、各支払い方法ごとの年間平均乗車回数を計算し、avg_trips_per_yearというエイリアスを付けます。

payment_type	avg_trips_per_year
Cash	1578920
Credit Card	1234567

これをパイプ構文に変えるとだいぶすっきりしますね。

-- パイプ構文 
FROM `bigquery-public-data.chicago_taxi_trips.taxi_trips`
|> EXTEND EXTRACT(YEAR FROM trip_start_timestamp) AS year
|> AGGREGATE COUNT(*) AS num_trips 
   GROUP BY year, payment_type
|> AGGREGATE AVG(num_trips) AS avg_trips_per_year GROUP BY payment_type ASC;

・1行目のFROM句で、データソースとなるテーブルを指定しています。
・2行目の|> EXTEND～では、レコードから[年]の情報を抽出し、year列としてテーブルに追加します。
・3行目の|> AGGREGATE COUNT(*) ～では、yearとpayment_type別にグループ化して、レコード数を集計し、それをnum_trips列として持ちます。
・4行目の|> AGGREGATE AVG(num_trips)では、そのnum_tripsの平均をpayment_typeごとに集計し、 avg_trips_per_yearとして表示します。
この最後の部分は結果の表として出てくるのです。

試しに、上記の従来の標準構文とパイプ構文によるクエリの２つをGeminiに入れてみたところ、同じ結果サンプルが返ってきました（当然ですが）。
　
（再掲）

payment_type	avg_trips_per_year
Cash	1578920
Credit Card	1234567

パイプ構文の特徴は以下です。

・クエリを FROM で始めることができます。
冒頭にFROM句を記載することで、どこのテーブルからデータを抽出するのかが明示されます。
・演算子は、パイプ記号「|>」を使用して順番に記述します。
・クエリは上から下に順番に読むことができます。

ブログの残りの章では、パイプライン構文のメリットが記載されていたので、まとめてみます。

HSBC における実際の影響

ブログでは、HSBCにおけるパイプ構文の生産性向上のメリットが記載されています。

・クエリ文字列の最大 80% が再利用可能
・複雑なクエリの作成やデバッグがはるかに簡単

SQL 内にパイプ構文を追加する利点

パイプ構文を利用することで生産性向上が図られますが、次のような利点もあります。

・パイプは既存のSQLと同じ単語（SELECT、WHERE、JOIN、ORDER、BY、LIMIT 等）を使っています。先に書いたように、パイプ構文とは「標準 SQL 構文の拡張」と言えますね。
・SQL は、既存の SQL コードと完全に相互運用可能です。たとえば、パイプ構文を使用したクエリでは、標準構文で記述された標準ビューを呼び出すことができ、その逆も可能です。
・パイプ構文は、変換プロキシなどの追加のレイヤを一切必要とせずに、BigQuery といった既存のプラットフォームで機能します。
・クエリを標準構文で記述してもパイプ構文で記述しても、通常、パフォーマンスはまったく同じです。

まとめ

パイプ構文は従来の標準構文のSQLの単語を使うため学習コストが低いです。クエリの可読性を高め、再利用、メンテナンスが容易になり、開発の生産性が高くなります。