2024 年 10 月 9 日の Google Cloud Blog にて、BigQuery と Cloud Logging におけるパイプ構文導入の記事がアップされました。このパイプ構文を、BigQuery で試してみたいと思います。

BigQuery パイプ構文 - 直感的で読みやすい新しいSQLクエリの書き方を試す

2024 年 10 月 9 日の Google Cloud Blog にて、BigQuery と Cloud Logging における「パイプ構文」導入の記事がアップされました。
<a href="https://cloud.google.com/blog/products/data-analytics/introducing-pipe-syntax-in-bigquery-and-cloud-logging?hl=en" rel="noopener noreferrer" target="_blank">Introducing pipe syntax in BigQuery and Cloud Logging | Google Cloud Blog</a>
SQL のクエリといえば、長らく同じ記法で歴史を刻んできた古に伝わる呪文ですが、今回の「パイプ構文」は、それに一石を投じる新しい書き方になっています。まさに黒船。ペリー来航です。
そんなパイプ構文を、BigQuery で試してみたいと思います。
<h2 id="パイプ構文">パイプ構文</h2>
BigQuery のパイプ構文は、SQL クエリをより直感的で読みやすくするための記法です。パイプ演算子 (|>) を使って処理を順番にチェーンすることで、クエリの各操作をステップごとに記述できるのが特徴です。 これにより、クエリの記述量の削減、可読性の向上が期待できます。
<ul>
<li><a href="https://cloud.google.com/bigquery/docs/pipe-syntax" rel="noopener noreferrer" target="_blank">Pipe syntax | BigQuery | Google Cloud</a></li>
<li><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax" rel="noopener noreferrer" target="_blank">Pipe query syntax | BigQuery | Google Cloud</a></li>
</ul>
従来の SQL のようにすべての操作を 1 つのブロックでまとめるのではなく、フィルタリング、選択、集約などの操作をそれぞれの行で分けて記述します。
<h2 id="パイプ構文を体験">パイプ構文を体験</h2>
では、実際にデータを用意してクエリを書いてみましょう。
用意したのは、e-sports のゲームをプレイした履歴のデータです。

























<table><thead><tr><th>player_id</th><th>esports_genre</th><th>session_date</th></tr></thead><tbody><tr><td>31</td><td>sports_games</td><td>2024/01/01</td></tr><tr><td>133</td><td>fps</td><td>2024/01/01</td></tr><tr><td>32</td><td>fighting_games</td><td>2024/01/01</td></tr></tbody></table>
どのプレイヤーが、いつ、どのジャンルのゲームをプレイしたかの履歴データです。
<a href="https://docs.google.com/spreadsheets/d/1fU5-RYHgNFsBXfNskWwt1tU8SDQguiiAD_d6Vf6htrQ" rel="noopener noreferrer" target="_blank">esports_play_sessions - Google スプレッドシート</a>
（件数は 30,000 件収録しています。スプレッドシートで公開しているので、CSV ダウンロードしてご自身の BigQuery にインポートするなど、ご自由にお使いください。）
さて、こんなオーダーが来ました。
「sports_games と、card_games をプレイしたプレイヤーの、最終プレイ日を抽出したい」
このとき、どんなクエリを書くでしょうか。通常の SQL なら、以下のようなクエリになると思います。
<pre><code class="hljs language-sql">SELECT
 player_id,
 esports_genre,
 MAX(session_date) AS session_date
FROM test.esports_play_sessions
WHERE esports_genre IN ('sports_games', 'card_games')
GROUP BY player_id, esports_genre
ORDER BY player_id, session_date
</code></pre>
ジャンルを絞り込み、それに対して集計。ユーザーごとの sports_games, card_games をプレイした最新の日時を抽出します。
これをパイプ構文を使用してクエリを組み立てると、以下になります。
<pre><code class="hljs language-sql">FROM test.esports_play_sessions
|> WHERE esports_genre IN ('sports_games', 'card_games')
|> AGGREGATE MAX(session_date) AS session_date GROUP BY player_id, esports_genre
|> ORDER BY player_id, session_date
</code></pre>
<ol>
<li>ジャンルを絞り込む（|> WHERE）</li>
<li>集計し最新日を算出（|> AGGREGATE）</li>
<li>並び替え（|> ORDER BY）</li>
</ol>
上から順番に読めるので、可読性は確かに上がる気がしますね。記述量は、、若干減っていそうな感じかな。
次に、各ジャンルにおける、一年間のプレイ数トップ 3 を出してみます。まずは通常の SQL です。
<pre><code class="hljs language-sql">WITH ranked_sessions AS (
 SELECT 
 esports_genre, player_id, COUNT(*) AS session_count,
 RANK() OVER (PARTITION BY esports_genre ORDER BY COUNT(*) DESC) AS session_rank
 FROM test.esports_play_sessions
 WHERE session_date BETWEEN '2024-01-01' AND '2024-12-31'
 GROUP BY esports_genre, player_id
)
SELECT 
 esports_genre, player_id, session_count, session_rank
FROM ranked_sessions
WHERE session_rank &#x3C;= 3
ORDER BY esports_genre, session_rank;
</code></pre>
ランキングで絞りトップ 3 としたいため、一度 CTE を定義し session_rank を付与、それから絞り込んでいます。
対して、このクエリをパイプ構文で記述すると以下になります。
<pre><code class="hljs language-sql">FROM test.esports_play_sessions
|> WHERE session_date BETWEEN '2024-01-01' AND '2024-12-31'
|> AGGREGATE COUNT(*) AS session_count GROUP BY esports_genre, player_id
|> WINDOW RANK() OVER (PARTITION BY esports_genre ORDER BY session_count DESC) AS session_rank
|> WHERE session_rank &#x3C;= 3
|> ORDER BY esports_genre, session_rank
</code></pre>
<ol>
<li>日付を絞り込む（|> WHERE）</li>
<li>集計し各ジャンルのプレイ数を算出（|> AGGREGATE）</li>
<li>プレイ数によってジャンルごとにユーザーをランク付け（|> WINDOW RANK()）</li>
<li>トップ 3 に絞り込む（|> WHERE）</li>
<li>並び替え（|> ORDER BY）</li>
</ol>
抽出の順も追いやすいですし、記述量も大分減りました。
<h2 id="直感的で追いやすくクエリ冗長に成り難し">直感的で追いやすくクエリ冗長に成り難し</h2>
2024 年 10 月 14 日現在、パイプ構文の<a href="https://cloud.google.com/products?hl=ja#product-launch-stages" rel="noopener noreferrer" target="_blank">リリースステージ</a>は「プレビュー」であり、一般提供はされていません。
BigQuery でパイプ構文を使ってみたい場合は、<a href="https://docs.google.com/forms/d/e/1FAIpQLSfEILymn_k1L-0K2qVTn6IbtftuBp_GnGxzVHyERNjiWUPXWQ/viewform" rel="noopener noreferrer" target="_blank">BigQuery パイプ構文登録フォーム</a>に記入し、パイプ構文プレビューにプロジェクトを登録する必要があります。
また、今回紹介したパイプ構文は、GoogleSQL としての機能です。MySQL や PostgreSQL などでは現状パイプ構文をサポートしていません。
さて、パイプ構文を使ってみましたが、読みやすさの向上、記述量の削減など、複雑なクエリほどパイプ構文の恩恵を受けやすくて良い機能でした。パイプ構文で記述したクエリも、もちろん CTE として切り出せます。
「直感的で追いやすくクエリ冗長に成り難し」
BigQuery のパイプ構文、是非使ってみてください。

Author