5万
SQL クエリは、私たちが頭の中で考える順番通りに書かれるとは限りません。通常はテーブルを選択し、行をフィルタリングし、集約し、最後に結果をソートするという順序で考えますが、従来の SQL ではクエリが返すカラムの定義から書き始めます。
ClickHouse 22.12 以降、FROM 句を SELECT の前に配置できるようになりました。ClickHouse 26.8 では、クエリを一連の変換処理として記述できる新しい |> 演算子を用いたパイプライン SQL(pipelined SQL)が導入され、このアプローチがさらに進化しました。
本記事では、英国の不動産価格データセットを使って、従来のクエリ、FROM 先頭のクエリ、そしてパイプラインクエリを比較します。さらに、パイプラインを活用して、通常であればネストしたサブクエリや CTE が必要になるようなクエリを組み立てていきます。
従来の SQL クエリ
まずは、2024年以降における不動産価格の中央値が高いロンドンの地区(district)を取得するクエリから始めましょう。
SELECT district, count() AS sales, round(median(price)) AS median_price
FROM uk_price_paid
WHERE (town = 'LONDON') AND (date >= '2024-01-01')
GROUP BY district
ORDER BY median_price DESC
LIMIT 10;┌─district───────────────┬─sales─┬─median_price─┐
│ KENSINGTON AND CHELSEA │ 2694 │ 1125000 │
│ RICHMOND UPON THAMES │ 708 │ 960000 │
│ CITY OF WESTMINSTER │ 3830 │ 900000 │
│ CITY OF LONDON │ 367 │ 842000 │
│ HARROW │ 2 │ 806750 │
│ HOUNSLOW │ 660 │ 780000 │
│ CAMDEN │ 3200 │ 760000 │
│ HAMMERSMITH AND FULHAM │ 3312 │ 730000 │
│ ISLINGTON │ 3316 │ 650000 │
│ WANDSWORTH │ 7155 │ 625000 │
└────────────────────────┴───────┴──────────────┘FROM を先頭に配置する
ClickHouse SQL のあまり知られていない機能として、FROM 句を SELECT の前に配置できます。そのため、次のようなクエリも有効です。
FROM uk_price_paid
SELECT district, count() AS sales, round(median(price)) AS median_price
WHERE (town = 'LONDON') AND (date >= '2024-01-01')
GROUP BY district
ORDER BY median_price DESC
LIMIT 10;データソースを先頭に置くことで、クエリの流れを視覚的に把握しやすくなります。操作対象のデータをまず指定し、その後にカラム、フィルタリング、集約、並べ替え、そして制限を記述します。FROM の位置が変わった点を除けば、これは依然として従来の SQL クエリです。
パイプラインを構築する
ClickHouse 26.8 は、このトップダウンの記述スタイルをパイプライン SQL によってさらに推し進めています。|> 演算子は、あるステージの結果を次のステージへと引き渡し、テーブルの読み取り、行のフィルタリング、集約、結果のソート、そして最後にリミットの適用という一連の変換手順を明示的に表現します。
同じクエリをパイプラインとして記述した例がこちらです。
FROM uk_price_paid
|> WHERE (town = 'LONDON') AND (date >= '2024-01-01')
|> AGGREGATE count() AS sales, round(median(price)) AS median_price
GROUP BY district
|> ORDER BY median_price DESC
|> LIMIT 10;従来のクエリ、FROM 先頭のクエリ、そしてパイプライン版のいずれも、まったく同じ結果を返します。
通常の SQL でも段階的にクエリをシンプルにすることは可能ですが、パイプラインでは各 |> が明確なチェックポイントとなり、そこまでの処理だけで完結したクエリとして機能します。これにより、変換処理を 1 ステージずつ構築しながら動作を確認するのが非常に容易になります。
ClickHouse はパイプラインをどのように変換するのか？
パイプライン化されたクエリは、実行前に標準的な SQL へと変換されます。
クエリの先頭に EXPLAIN SYNTAX を付加する(筆者自身、この記事を書いている最中に知りました！)と、パイプラインから生成された従来の SQL を確認できます。
EXPLAIN SYNTAX
FROM uk_price_paid
|> WHERE (town = 'LONDON') AND (date >= '2024-01-01')
|> AGGREGATE count() AS sales, round(median(price)) AS median_price
GROUP BY district
|> ORDER BY median_price DESC
|> LIMIT 10
FORMAT LineAsString;SELECT * FROM (
SELECT * FROM (
SELECT district, count() AS sales, round(median(price)) AS median_price
FROM (
SELECT * FROM (
SELECT *
FROM uk_price_paid
)
WHERE and(equals(town, 'LONDON'), greaterOrEquals(date, '2024-01-01'))
)
GROUP BY district
)
ORDER BY median_price DESC
)
LIMIT 10;変換後のクエリにはネストした SELECT 文が複数含まれていますが、ClickHouse は中間結果を個別に実体化(マテリアライズ)するわけではなく、実行前にクエリ全体を最適化します。
EXTEND によるカラムの追加
EXTEND を使用すると、パイプライン内にすでに存在するすべてのカラムを保持したまま、計算カラムを追加できます。
FROM uk_price_paid
|> WHERE town = 'LONDON' AND date >= '2024-01-01'
|> EXTEND round(price / 1000000, 2) AS price_millions
|> SELECT date, district, price, price_millions
|> ORDER BY price DESC
|> LIMIT 3;┌───────date─┬─district──────┬─────price─┬─price_millions─┐
│ 2024-03-20 │ TOWER HAMLETS │ 164300000 │ 164.3 │
│ 2024-03-20 │ TOWER HAMLETS │ 164300000 │ 164.3 │
│ 2024-03-20 │ TOWER HAMLETS │ 161890000 │ 161.89 │
└────────────┴───────────────┴───────────┴────────────────┘この例における EXTEND の使用は、SELECT *, round(price / 1000000, 2) AS price_millions と同等です。このクエリに対する EXPLAIN SYNTAX の出力は以下のようになります。
SELECT * FROM (
SELECT date, district, price, price_millions
FROM (
SELECT *, round(divide(price, 1000000), 2) AS price_millions
FROM (
SELECT *
FROM (
SELECT *
FROM uk_price_paid
)
WHERE and(equals(town, 'LONDON'), greaterOrEquals(date, '2024-01-01'))
)
)
ORDER BY price DESC
)
LIMIT 3;中間結果の再利用
パイプライン構文を使用すると、既存の集約処理を拡張することもできます。例えば、次のクエリは郡(county)ごとにグループ化し、各地区の不動産価格の中央値を算出します。
SELECT county, district, median(price) AS district_median
FROM uk_price_paid
WHERE date >= '2024-01-01'
GROUP BY county, districtdistrict_median というエイリアスは、後続のパイプラインステージで利用可能なカラムになります。これにより、ネストしたサブクエリや CTE を自分で書くことなく、このカラムを対象に再度集約を行えます。
したがって、このクエリを拡張して、各郡における地区レベルの中央値の平均を算出し、その値が最も高い上位 10 の郡を返すようにできます。
SELECT county, district, median(price) AS district_median
FROM uk_price_paid
WHERE date >= '2024-01-01'
GROUP BY county, district
|> AGGREGATE round(avg(district_median)) AS average_district_median
GROUP BY county
|> ORDER BY average_district_median DESC
|> LIMIT 10;┌─county─────────────────┬─average_district_median─┐
│ GREATER LONDON │ 558476 │
│ WINDSOR AND MAIDENHEAD │ 520000 │
│ SURREY │ 497455 │
│ WOKINGHAM │ 478000 │
│ HERTFORDSHIRE │ 452250 │
│ BUCKINGHAMSHIRE │ 443870 │
│ ISLES OF SCILLY │ 430000 │
│ BRIGHTON AND HOVE │ 405000 │
│ OXFORDSHIRE │ 400451 │
│ BRACKNELL FOREST │ 400000 │
└────────────────────────┴─────────────────────────┘ステージの順序に関する注意点
注意すべき点として、パイプラインステージの順序が実行結果を左右することが挙げられます。もし LIMIT 10 を 2 つ目の集約処理の前に移動すると、中間結果が 10 件の地区中央値だけに制限されます。その結果、郡の平均値はすべての地区からではなく、その 10 行のみから算出されることになります。
SELECT county, district, median(price) AS district_median
FROM uk_price_paid
WHERE date >= '2024-01-01'
GROUP BY county, district
|> LIMIT 10
|> AGGREGATE round(avg(district_median)) AS average_district_median
GROUP BY county
|> ORDER BY average_district_median DESC;┌─county──────────────────────────────┬─average_district_median─┐
│ BUCKINGHAMSHIRE │ 443000 │
│ BRIGHTON AND HOVE │ 405000 │
│ BRACKNELL FOREST │ 400000 │
│ BATH AND NORTH EAST SOMERSET │ 390000 │
│ BEDFORD │ 330000 │
│ BOURNEMOUTH, CHRISTCHURCH AND POOLE │ 325000 │
│ BRIDGEND │ 205000 │
│ BLACKBURN WITH DARWEN │ 150000 │
│ BLAENAU GWENT │ 126250 │
│ BLACKPOOL │ 125000 │
└─────────────────────────────────────┴─────────────────────────┘Note
最初の LIMIT の前に ORDER BY がないため、選択される 10 件の地区行は決定的ではなく、正確な結果は実行ごとに異なる可能性があります。
他のステートメントでパイプラインを使用する
パイプラインは SELECT で始まるクエリに限定されず、サブクエリ、INSERT ... SELECT 文、ビューなど、ClickHouse が SELECT クエリを期待するあらゆる場所で使用できます。
次の例では、ビューの基盤となるクエリとしてパイプラインを使用しています。
CREATE VIEW million_pound_london_sales AS
FROM uk_price_paid
|> WHERE town = 'LONDON' AND price >= 1000000
|> SELECT date, price, district, postcode1, postcode2;その後、従来の SQL 構文を使用してそのビューを照会できます。
SELECT *
FROM million_pound_london_sales
ORDER BY price DESC
LIMIT 3;┌───────date─┬─────price─┬─district────────────┬─postcode1─┬─postcode2─┐
│ 2017-07-31 │ 594300000 │ CITY OF WESTMINSTER │ W1U │ 8EW │
│ 2018-02-08 │ 569200000 │ CITY OF WESTMINSTER │ W1J │ 7BT │
│ 2019-11-20 │ 542540820 │ CAMDEN │ NW5 │ 2HB │
└────────────┴───────────┴─────────────────────┴───────────┴───────────┘また、INSERT のソースとしてパイプラインを使用することも可能です。例えば、高額なロンドンの不動産を保存する次のようなテーブルがあるとします。
CREATE TABLE expensive_london_sales
(
date Date,
price UInt32,
district LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY (district, date);従来の INSERT ... SELECT クエリでは、ソーステーブルの前に選択するカラムを指定します。
INSERT INTO expensive_london_sales (date, price, district)
SELECT date, price, district
FROM uk_price_paid
WHERE town = 'LONDON' AND price >= 1000000;パイプライン構文を使用すると、挿入先の選択、ソースの読み取り、行のフィルタリング、挿入するカラムの選択という、実際の処理順序に沿って同じ操作を記述できます。
INSERT INTO expensive_london_sales (date, price, district)
FROM uk_price_paid
|> WHERE town = 'LONDON' AND price >= 1000000
|> SELECT date, price, district;まとめ
パイプライン SQL は、ClickHouse のクエリを表現する新たな手段を提供します。従来の SQL を完全に置き換えるものではありませんが、クエリを一連の変換処理として記述することで、複数ステージにわたるクエリの構築や処理の流れの把握が容易になります。
ぜひ使ってみた感想や、ご自身のクエリをシンプルにするために役立ったかどうかをお聞かせください。
