ストリーミング HTTP API としての ClickHouse

ClickHouse の前に薄い API 層を置き、HTTP パラメーターを受け取ってクエリを組み立て、結果を返す構成はよく見られます。ClickHouse 26.8 では、名前付き HTTP ハンドラー、結果の加工、フレーミングフォーマットを使って、その多くを ClickHouse 自体で直接実現できます。

この記事では、これらの機能を使い、英国の不動産価格データセットを題材にストリーミング HTTP API を構築します。API にビジネスロジック、オーケストレーション、アプリケーション固有のバリデーションがあるなら、アプリケーション層は引き続き必要です。しかし、内容を制御した ClickHouse クエリを公開するだけなら、アーキテクチャの層を 1 つ減らせます。

英国不動産価格データセットの準備

英国不動産価格データセットには、1995年以降に英国で売買された不動産の詳細が収められています。まずテーブルを作成します:

1CREATE TABLE uk_price_paid
2(
3    price UInt32,
4    date Date,
5    postcode1 LowCardinality(String),
6    postcode2 LowCardinality(String),
7    type Enum8(
8        'terraced' = 1,
9        'semi-detached' = 2,
10        'detached' = 3,
11        'flat' = 4,
12        'other' = 0
13    ),
14    is_new UInt8,
15    duration Enum8(
16        'freehold' = 1,
17        'leasehold' = 2,
18        'unknown' = 0
19    ),
20    addr1 String,
21    addr2 String,
22    street LowCardinality(String),
23    locality LowCardinality(String),
24    town LowCardinality(String),
25    district LowCardinality(String),
26    county LowCardinality(String)
27)
28ENGINE = MergeTree
29ORDER BY (postcode1, postcode2, addr1, addr2);

元の CSV にはヘッダーがないため、ClickHouse のスキーマ推論は各列に c1 から c16 までの名前を割り当てます。この位置に基づく列名を使い、取り込みながら元のフィールドを変換します:

1INSERT INTO uk_price_paid
2SELECT
3    toUInt32(c2) AS price,
4    toDate(c3) AS date,
5    splitByChar(' ', c4)[1] AS postcode1,
6    splitByChar(' ', c4)[2] AS postcode2,
7    transform(
8        c5,
9        ['T', 'S', 'D', 'F', 'O'],
10        ['terraced', 'semi-detached', 'detached', 'flat', 'other']
11    ) AS type,
12    c6 = 'Y' AS is_new,
13    transform(
14        c7,
15        ['F', 'L', 'U'],
16        ['freehold', 'leasehold', 'unknown']
17    ) AS duration,
18    c8 AS addr1,
19    c9 AS addr2,
20    c10 AS street,
21    c11 AS locality,
22    c12 AS town,
23    c13 AS district,
24    c14 AS county
25FROM url(
26    'http://prod1.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv',
27    'CSV'
28)
29SETTINGS
30    schema_inference_make_columns_nullable = 0,
31    max_http_get_redirects = 10;

データを探索するクエリ

データの概要をつかむために、いくつかクエリを実行して調べます。まずは取引件数と、最初と最後の取引を調べます:

1SELECT
2    count() AS transactions,
3    min(date) AS first_transaction,
4    max(date) AS last_transaction
5FROM uk_price_paid;

1┌─transactions─┬─first_transaction─┬─last_transaction─┐
2│     30452463 │        1995-01-01 │       2025-07-31 │
3└──────────────┴───────────────────┴──────────────────┘

次のクエリは、データが占めるディスク容量を計算します:

1SELECT formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
2FROM system.parts
3WHERE database = 'default' AND table = 'uk_price_paid' AND active;

1┌─size_on_disk─┐
2│ 339.08 MiB   │
3└──────────────┘

2024年1月1日以降の平均売却価格が最も高い町と地区を調べたい場合は、次のクエリで求められます:

1SELECT town, district, count() AS sales, round(avg(price)) AS average_price
2FROM uk_price_paid
3WHERE date >= '2024-01-01'
4GROUP BY ALL
5HAVING sales >= 100
6ORDER BY average_price DESC
7LIMIT 20;

1┌─town───────────────┬─district───────────────┬─sales─┬─average_price─┐
2│ LONDON             │ CITY OF WESTMINSTER    │  3830 │       2627535 │
3│ LONDON             │ CITY OF LONDON         │   367 │       2523498 │
4│ LONDON             │ KENSINGTON AND CHELSEA │  2694 │       2149896 │
5│ PURFLEET-ON-THAMES │ THURROCK               │   126 │       1813003 │
6│ VIRGINIA WATER     │ RUNNYMEDE              │   114 │       1600253 │
7│ LONDON             │ CAMDEN                 │  3200 │       1331681 │
8│ LEATHERHEAD        │ ELMBRIDGE              │   111 │       1273186 │
9│ BARNET             │ ENFIELD                │   116 │       1235428 │
10│ COBHAM             │ ELMBRIDGE              │   330 │       1222086 │
11│ RADLETT            │ HERTSMERE              │   228 │       1219858 │
12│ LONDON             │ RICHMOND UPON THAMES   │   708 │       1204016 │
13│ BEACONSFIELD       │ BUCKINGHAMSHIRE        │   384 │       1160016 │
14│ LONDON             │ HOUNSLOW               │   660 │       1157176 │
15│ TRING              │ DACORUM                │   365 │       1082008 │
16│ LONDON             │ HAMMERSMITH AND FULHAM │  3312 │       1059507 │
17│ ESHER              │ ELMBRIDGE              │   418 │       1017787 │
18│ RICHMOND           │ RICHMOND UPON THAMES   │   886 │        988095 │
19│ WEYBRIDGE          │ ELMBRIDGE              │   612 │        980195 │
20│ LEATHERHEAD        │ GUILDFORD              │   162 │        973938 │
21│ ASCOT              │ BRACKNELL FOREST       │   147 │        962569 │
22└────────────────────┴────────────────────────┴───────┴───────────────┘

API ユーザーの設定

API エンドポイントへのクエリをすべて実行する api_user を作成します。このユーザーの既定の出力フォーマットは JSONEachRow、既定の上限は 10 件です:

1CREATE USER api_user
2IDENTIFIED WITH no_password
3HOST LOCAL
4SETTINGS default_format = 'JSONEachRow', limit = 10;

リクエストで別の上限を指定しない限り、limit 設定により、このユーザーへ返る結果はすべて 10 行までになります。

次に、このユーザーが uk_price_paid テーブルをクエリできるようにします:

1GRANT SELECT ON uk_price_paid TO api_user;

名前付き HTTP エンドポイントの作成

HTTP エンドポイントは、26.8 で導入されたCREATE HANDLER文で作成できます。ハンドラーには URL と、実行するクエリが必要です。受け付ける HTTP メソッドも指定でき、指定しない場合は GET が既定になります。

Note

対応するメソッドは GET、POST、PUT、DELETE です。

次のハンドラーは 2024年1月1日以降に最も高額な地域を返し、/api/expensive-areas への GET リクエストで呼び出せます:

1CREATE HANDLER expensive_areas
2URL '/api/expensive-areas'
3METHODS (GET)
4AS
5SELECT town, district, count() AS sales, round(avg(price)) AS average_price
6FROM uk_price_paid
7WHERE date >= '2024-01-01'
8GROUP BY ALL
9HAVING sales >= 100
10ORDER BY average_price DESC;

Note

ハンドラーの作成時に検査されるのはクエリの構文だけで、意味解析は行われません。たとえば、参照しているテーブルや列が存在するかどうかは、API エンドポイントが呼び出されるまで検査されません。

また、このハンドラーのクエリには LIMIT 句がありません。API ユーザーの limit 設定は、HTTP レベルのフィルタリングと並べ替えの後、最終結果に適用されます。これにより既定では 10 行が上限になり、リクエストごとに上書きできます。

このエンドポイントは cURL で呼び出せます:

1curl --silent --user 'api_user:' 'http://localhost:8123/api/expensive-areas'

1{"town":"LONDON","district":"CITY OF WESTMINSTER","sales":3830,"average_price":2627535}
2{"town":"LONDON","district":"CITY OF LONDON","sales":367,"average_price":2523498}
3{"town":"LONDON","district":"KENSINGTON AND CHELSEA","sales":2694,"average_price":2149896}
4{"town":"PURFLEET-ON-THAMES","district":"THURROCK","sales":126,"average_price":1813003}
5{"town":"VIRGINIA WATER","district":"RUNNYMEDE","sales":114,"average_price":1600253}
6{"town":"LONDON","district":"CAMDEN","sales":3200,"average_price":1331681}
7{"town":"LEATHERHEAD","district":"ELMBRIDGE","sales":111,"average_price":1273186}
8{"town":"BARNET","district":"ENFIELD","sales":116,"average_price":1235428}
9{"town":"COBHAM","district":"ELMBRIDGE","sales":330,"average_price":1222086}
10{"town":"RADLETT","district":"HERTSMERE","sales":228,"average_price":1219858}

まずはこれで動きますが、このクエリは日付と売買件数の値がハードコードされているため用途が限られます。次はこの点を解決します。

型付きパラメーター

通常のクエリと同じように、ハンドラーのクエリにもパラメーターを含められます。次のハンドラーのクエリは、指定された町、日付、価格で取引を絞り込みます:

1CREATE HANDLER town_sales
2URL '/api/town-sales'
3METHODS (GET)
4AS
5SELECT
6    date, price, type, duration,
7    concat(postcode1, ' ', postcode2) AS postcode,
8    district, street, addr1, addr2
9FROM uk_price_paid
10WHERE town = upperUTF8({town:String})
11AND date >= {from:Date}
12AND price >= {minimum_price:UInt32}
13ORDER BY price DESC;

API エンドポイントは次のように、パラメーターをクエリ文字列で渡して呼び出します:

1curl --silent --user 'api_user:' --get 'http://localhost:8123/api/town-sales' \
2  --data-urlencode 'town=London' \
3  --data-urlencode 'from=2024-01-01' \
4  --data-urlencode 'minimum_price=1000000'

1{"date":"2024-03-20","price":164300000,"type":"other","duration":"leasehold","postcode":"E14 5GX","district":"TOWER HAMLETS","street":"WATER STREET","addr1":"15","addr2":""}
2{"date":"2024-03-20","price":164300000,"type":"other","duration":"leasehold","postcode":"E14 5GX","district":"TOWER HAMLETS","street":"WATER STREET","addr1":"15","addr2":""}
3{"date":"2024-03-20","price":161890000,"type":"other","duration":"leasehold","postcode":"E14 5GX","district":"TOWER HAMLETS","street":"WATER STREET","addr1":"UNIT D1.1, 14","addr2":""}
4{"date":"2024-12-13","price":138900000,"type":"other","duration":"leasehold","postcode":"NW1 4NT","district":"CITY OF WESTMINSTER","street":"INNER CIRCLE","addr1":"THE HOLME COTTAGE","addr2":""}
5{"date":"2024-01-31","price":129706651,"type":"other","duration":"leasehold","postcode":"SW1A 2WH","district":"CITY OF WESTMINSTER","street":"THE MALL","addr1":"ADMIRALTY ARCH HOTEL","addr2":""}
6{"date":"2025-03-31","price":124519556,"type":"other","duration":"freehold","postcode":"WC2E 7PS","district":"CITY OF WESTMINSTER","street":"TAVISTOCK STREET","addr1":"15","addr2":""}
7{"date":"2024-01-23","price":115000000,"type":"other","duration":"freehold","postcode":"SW1Y 4SP","district":"CITY OF WESTMINSTER","street":"HAYMARKET","addr1":"HAYMARKET HOUSE, 28 - 29","addr2":"FIRST FLOOR"}
8{"date":"2025-01-22","price":109500000,"type":"other","duration":"freehold","postcode":"EC2R 8EJ","district":"CITY OF LONDON","street":"POULTRY","addr1":"1","addr2":""}
9{"date":"2024-07-05","price":101000000,"type":"other","duration":"freehold","postcode":"EC1R 5EN","district":"CAMDEN","street":"BACKHILL","addr1":"6","addr2":""}
10{"date":"2024-11-30","price":93836616,"type":"other","duration":"leasehold","postcode":" ","district":"WANDSWORTH","street":"NINE ELMS LANE","addr1":"BUILDING A01 EMBASSY GARDENS","addr2":""}

パラメーターは POST リクエストのボディにフォームフィールドとして渡すこともできますが、この記事では POST エンドポイントは扱いません。

URL パス内の型付きパラメーター

パラメーターは URL パスでも指定できます。次のハンドラーは、指定された URL から town と district_path を取り出します:

1CREATE HANDLER town_sales_path
2URL REGEXP '/api/town-sales/(?P<town>[^/]+)(?P<district_path>(?:/[^/]+)?)'
3METHODS (GET)
4AS
5SELECT date, price, type, duration,
6      concat(postcode1, ' ', postcode2) AS postcode,
7      town, district, street, addr1, addr2
8FROM uk_price_paid
9WHERE town = upperUTF8({town:String})
10AND (
11    {district_path:String} = ''
12    OR district = upperUTF8(substring({district_path:String}, 2))
13);

町は必須ですが、地区は省略できます。地区を指定しない場合、クエリは町だけで絞り込みます。

次のクエリはロンドンの売買を取得します:

1curl --silent --user 'api_user:' --get 'http://localhost:8123/api/town-sales/LONDON'

1{"date":"2010-10-15","price":420000,"type":"terraced","duration":"freehold","postcode":" ","town":"LONDON","district":"ISLINGTON","street":"ST CLEMENTS STREET","addr1":"1","addr2":""}
2{"date":"2010-12-17","price":250000,"type":"flat","duration":"leasehold","postcode":" ","town":"LONDON","district":"TOWER HAMLETS","street":"BALTIMORE WHARF","addr1":"1","addr2":"APARTMENT 703"}
3{"date":"2012-12-06","price":320000,"type":"flat","duration":"leasehold","postcode":" ","town":"LONDON","district":"BARNET","street":"LICHFIELD GROVE","addr1":"1","addr2":"FLAT 2"}
4{"date":"2023-06-13","price":240000,"type":"other","duration":"leasehold","postcode":" ","town":"LONDON","district":"CITY OF LONDON","street":"GREAT ST THOMAS APOSTLE","addr1":"1 - 7","addr2":"COMMERCIAL UNIT 2"}
5{"date":"2013-04-30","price":250000,"type":"semi-detached","duration":"freehold","postcode":" ","town":"LONDON","district":"GREENWICH","street":"MYRA STREET","addr1":"1 UNITY MEWS","addr2":""}
6{"date":"2009-02-05","price":554000,"type":"detached","duration":"freehold","postcode":" ","town":"LONDON","district":"HACKNEY","street":"ANDRE STREET","addr1":"10","addr2":""}
7{"date":"2022-12-15","price":1630000,"type":"other","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"BLOOMSBURY WAY","addr1":"10","addr2":"EIGHTH FLOOR"}
8{"date":"2011-07-11","price":610000,"type":"flat","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"WILMOT PLACE","addr1":"10","addr2":"GROUND  FLOOR  FLAT"}
9{"date":"2022-12-15","price":1630000,"type":"other","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"BLOOMSBURY WAY","addr1":"10","addr2":"NINTH FLOOR"}
10{"date":"2023-08-04","price":537500,"type":"other","duration":"leasehold","postcode":" ","town":"LONDON","district":"ISLINGTON","street":"DRAYTON PARK","addr1":"100","addr2":"PARKING SPACE 35"}

たとえばカムデンに絞り込みたい場合は、次のようにします:

1curl --silent --user 'api_user:' --get 'http://localhost:8123/api/town-sales/LONDON/CAMDEN'

1{"date":"2002-09-25","price":900000,"type":"detached","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"KILBURN HIGH ROAD","addr1":"1 - 4","addr2":"UNITS"}
2{"date":"2002-03-19","price":235000,"type":"flat","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"GREENCROFT GARDENS","addr1":"106","addr2":"FLAT 7"}
3{"date":"2002-05-31","price":248000,"type":"flat","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"CROFTDOWN ROAD","addr1":"11","addr2":"FIRST FLOOR FLAT"}
4{"date":"2004-03-12","price":285000,"type":"flat","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"CROFTDOWN ROAD","addr1":"11","addr2":"SECOND FLOOR FLAT"}
5{"date":"2002-05-17","price":215000,"type":"flat","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"HAVERSTOCK HILL","addr1":"119","addr2":"STUDIO B"}
6{"date":"2002-06-26","price":239000,"type":"flat","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"FITZROY STREET","addr1":"12 - 16","addr2":"FLAT 83"}
7{"date":"2003-08-18","price":290000,"type":"flat","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"FITZROY STREET","addr1":"12 - 16","addr2":"FLAT 88"}
8{"date":"2004-09-17","price":625000,"type":"flat","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"WEDDERBURN ROAD","addr1":"13","addr2":"FIRST FLOOR FLAT"}
9{"date":"2005-03-08","price":290000,"type":"flat","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"ABBEY ROAD","addr1":"132","addr2":"BASEMENT FLAT"}
10{"date":"2003-06-27","price":310000,"type":"flat","duration":"leasehold","postcode":" ","town":"LONDON","district":"CAMDEN","street":"ABBEY ROAD","addr1":"132","addr2":"SECOND FLOOR FLAT"}

結果を加工する汎用設定

26.8 より前から、ClickHouse はクエリ本文に手を入れずに結果を加工するパラメーターとして limit と offset に対応していました。

26.8 では新しいクエリ設定(filter、select、sort、order、page)に加え、出力データフォーマットを明示的に上書きする output_format が追加されました。他の新しい設定は圧縮と入力フォーマットに関するもので、この読み取り専用 API の例では扱いません。

これらの設定の使い方を、order と limit から見ていきます:

1curl --silent --user 'api_user:' --get 'http://localhost:8123/api/town-sales/LONDON' \
2  --data-urlencode 'order=price DESC' \
3  --data-urlencode 'limit=3'

1{"date":"2017-07-31","price":594300000,"type":"other","duration":"leasehold","postcode":"W1U 8EW","town":"LONDON","district":"CITY OF WESTMINSTER","street":"BAKER STREET","addr1":"55","addr2":"UNIT 53"}
2{"date":"2018-02-08","price":569200000,"type":"other","duration":"freehold","postcode":"W1J 7BT","town":"LONDON","district":"CITY OF WESTMINSTER","street":"STANHOPE ROW","addr1":"2","addr2":""}
3{"date":"2019-11-20","price":542540820,"type":"other","duration":"freehold","postcode":"NW5 2HB","town":"LONDON","district":"CAMDEN","street":"FORTESS ROAD","addr1":"36","addr2":""}

上の例のとおり、order は SQL の並べ替え式を受け取ります。sort のほうは、より簡潔で URL に向いた構文を使います。フィールド名の前に - を付けると降順になります:

1curl --silent --user 'api_user:' --get 'http://localhost:8123/api/town-sales/LONDON' \
2  --data-urlencode 'sort=-price' \
3  --data-urlencode 'limit=3'

結果を絞り込んで、1,000,000 ポンド超で売却された物件だけを返すこともできます:

1curl --silent --user 'api_user:' --get 'http://localhost:8123/api/town-sales/LONDON' \
2  --data-urlencode 'order=date' \
3  --data-urlencode 'limit=3' \
4  --data-urlencode 'filter=price > 1000000'

1{"date":"1995-01-06","price":1250000,"type":"semi-detached","duration":"freehold","postcode":"W9 1AL","town":"LONDON","district":"CITY OF WESTMINSTER","street":"","addr1":"WHITE LODGE, 19A","addr2":""}
2{"date":"1995-01-06","price":1252500,"type":"semi-detached","duration":"leasehold","postcode":"NW1 7SR","town":"LONDON","district":"CAMDEN","street":"PRINCE ALBERT ROAD","addr1":"7","addr2":""}
3{"date":"1995-01-06","price":1600000,"type":"terraced","duration":"freehold","postcode":"SW10 9SW","town":"LONDON","district":"KENSINGTON AND CHELSEA","street":"HARLEY GARDENS","addr1":"2","addr2":""}

フィルターは複数指定できます。次のクエリは、2024年以降に 1,000,000 ポンド超で売却された物件を返します。2 つ目の並べ替えフィールドも追加します:

1curl --silent --user 'api_user:' --get 'http://localhost:8123/api/town-sales/LONDON' \
2  --data-urlencode 'order=date, price DESC' \
3  --data-urlencode 'limit=3' \
4  --data-urlencode 'filter=price > 1000000' \
5  --data-urlencode "filter=date >= '2024-01-01'"

1{"date":"2024-01-02","price":6000000,"type":"flat","duration":"leasehold","postcode":"NW8 7HN","town":"LONDON","district":"CITY OF WESTMINSTER","street":"ST JOHNS WOOD ROAD","addr1":"60","addr2":"APARTMENT 94"}
2{"date":"2024-01-02","price":2400000,"type":"flat","duration":"leasehold","postcode":"SW19 5EF","town":"LONDON","district":"MERTON","street":"HIGH STREET WIMBLEDON","addr1":"EAGLE HOUSE","addr2":"7"}
3{"date":"2024-01-02","price":1470000,"type":"flat","duration":"leasehold","postcode":"E14 9LX","town":"LONDON","district":"TOWER HAMLETS","street":"PARK DRIVE","addr1":"1","addr2":"APARTMENT 5402"}

あるいは、両方のフィルター条件を AND でつなぐこともできます:

1curl --silent --user 'api_user:' --get 'http://localhost:8123/api/town-sales/LONDON' \
2  --data-urlencode 'order=date, price DESC' \
3  --data-urlencode 'limit=3' \
4  --data-urlencode "filter=price > 1000000 AND date >= '2024-01-01'"

select を使うと、返す列を選べます:

1curl --silent --user 'api_user:' --get 'http://localhost:8123/api/town-sales/LONDON' \
2  --data-urlencode 'order=date, price DESC' \
3  --data-urlencode 'limit=3' \
4  --data-urlencode "filter=price > 1000000 AND date >= '2024-01-01'" \
5  --data-urlencode "select=date,price,postcode"

1{"date":"2024-01-02","price":6000000,"postcode":"NW8 7HN"}
2{"date":"2024-01-02","price":2400000,"postcode":"SW19 5EF"}
3{"date":"2024-01-02","price":1470000,"postcode":"E14 9LX"}

page を使うと、結果をページ送りできます。次のリクエストは続きの 3 件を取得します。日付と価格が同じ行でも順序が定まるように、並べ替えのフィールドを追加しています:

1curl --silent --user 'api_user:' --get 'http://localhost:8123/api/town-sales/LONDON' \
2  --data-urlencode 'order=date, price DESC, postcode, district, street, addr1, addr2' \
3  --data-urlencode 'limit=3' \
4  --data-urlencode 'page=2' \
5  --data-urlencode "filter=price > 1000000 AND date >= '2024-01-01'" \
6  --data-urlencode "select=date,price,postcode"

1{"date":"2024-01-02","price":1300000,"postcode":"E1W 1AG"}
2{"date":"2024-01-02","price":1300000,"postcode":"NW1 1NB"}
3{"date":"2024-01-02","price":1300000,"postcode":"W6 8JN"}

出力フォーマットも変更できます:

1curl --silent --user 'api_user:' --get 'http://localhost:8123/api/town-sales/LONDON' \
2  --data-urlencode 'order=date, price DESC' \
3  --data-urlencode 'limit=3' \
4  --data-urlencode "filter=price > 1000000 AND date >= '2024-01-01'" \
5  --data-urlencode 'output_format=JSONCompactColumns'

1[
2	["2024-01-02", "2024-01-02", "2024-01-02"],
3	[6000000, 2400000, 1470000],
4	["flat", "flat", "flat"],
5	["leasehold", "leasehold", "leasehold"],
6	["NW8 7HN", "SW19 5EF", "E14 9LX"],
7	["LONDON", "LONDON", "LONDON"],
8	["CITY OF WESTMINSTER", "MERTON", "TOWER HAMLETS"],
9	["ST JOHNS WOOD ROAD", "HIGH STREET WIMBLEDON", "PARK DRIVE"],
10	["60", "EAGLE HOUSE", "1"],
11	["APARTMENT 94", "7", "APARTMENT 5402"]
12]

動的なフィルタリング

26.8 リリースでは、新しい設定 http_allow_filters_as_unrecognized_url_parameters も追加されました。この設定を有効にすると、ClickHouse は認識できない URL パラメーターを WHERE のフィルターとして解釈します。 api_user に対しては次のように有効化します:

1ALTER USER api_user
2ADD SETTINGS http_allow_filters_as_unrecognized_url_parameters = 1;

これにより、先ほど filter 設定で書いたクエリを簡潔にできます:

1curl --silent --user 'api_user:' --get "http://localhost:8123/api/town-sales/LONDON" \
2  --data-urlencode 'order=date,price DESC' \
3  --data-urlencode 'limit=3' \
4  --data-urlencode 'price>1000000' \
5  --data-urlencode 'date>=2024-01-01'

ハンドラーの一覧

作成したハンドラーは system.handlers テーブルで確認できます:

1SELECT name, url, methods
2FROM system.handlers
3ORDER BY name;

1┌─name────────────┬─url───────────────────────────────────────────────────────────┬─methods─┐
2│ expensive_areas │ /api/expensive-areas                                          │ ['GET'] │
3│ town_sales      │ /api/town-sales                                               │ ['GET'] │
4│ town_sales_path │ /api/town-sales/(?P<town>[^/]+)(?P<district_path>(?:/[^/]+)?) │ ['GET'] │
5└─────────────────┴───────────────────────────────────────────────────────────────┴─────────┘

テーブルを API エンドポイントにする

独自のエンドポイントを作るだけでなく、テーブルそのものを API エンドポイントにすることもできます。そのためには、グローバル設定 http_allow_path_requests を有効にする必要があります:

config.d/http-path-requests.yaml

1http_allow_path_requests: 1

http_allow_path_requests はサーバーを再起動しないと変更できないため、ClickHouse を再起動して設定を反映します。

api_user 側でも、追加の設定を有効にする必要があります:

1ALTER USER api_user
2ADD SETTINGS
3    http_allow_table_as_file = 1,
4    http_allow_database_as_path = 1;

http_allow_table_as_file は、パスの最後の要素を table、table.format、または table.format.compression として解釈します。
http_allow_database_as_path は、先頭の /database/ パス要素を現在のデータベースとして解釈します。

これが済めば、ロンドンで最も高額な物件を CSV 形式で返すクエリを次のように書けます:

1curl --silent --user 'api_user:' 'http://localhost:8123/default/uk_price_paid.csv?town=LONDON&sort=-price&limit=3'

1594300000,"2017-07-31","W1U","8EW","other",0,"leasehold","55","UNIT 53","BAKER STREET","","LONDON","CITY OF WESTMINSTER","GREATER LONDON"
2569200000,"2018-02-08","W1J","7BT","other",0,"freehold","2","","STANHOPE ROW","","LONDON","CITY OF WESTMINSTER","GREATER LONDON"
3542540820,"2019-11-20","NW5","2HB","other",0,"freehold","36","","FORTESS ROAD","","LONDON","CAMDEN","GREATER LONDON"

出力フォーマットは、テーブル名の接尾辞ではなく format パラメーターで制御することもできます。また、独自エンドポイントと同じように、select パラメーターで返すフィールドを指定できます:

1curl --silent \
2    --user 'api_user:' \
3    --get 'http://localhost:8123/default/uk_price_paid' \
4    --data-urlencode 'town=LONDON' \
5    --data-urlencode 'sort=-price' \
6    --data-urlencode 'limit=3' \
7    --data-urlencode 'select=date,price,postcode1' \
8    --data-urlencode 'format=JSONEachRow'

1{"date":"2017-07-31","price":594300000,"postcode1":"W1U"}
2{"date":"2018-02-08","price":569200000,"postcode1":"W1J"}
3{"date":"2019-11-20","price":542540820,"postcode1":"NW5"}

HTTP レスポンスのストリーミング

HTTP レスポンスのストリームには、データ、進捗、合計(totals)、プロファイルイベント、サーバーログ、例外を載せられるようになりました。

フレーミングフォーマット(framing format)は、クエリの通常の出力を型付きパケットのストリームで包みます。次の例では、data パケットに JSONEachRow の結果が入り、progress パケットは ClickHouse が行った処理量を報告します。簡潔にするため、プロファイルイベントのパケットは無効にしています。

1curl --no-buffer --silent --user 'api_user:' --get \
2    'http://localhost:8123/api/town-sales/LONDON' \
3    --data-urlencode 'sort=-price' \
4    --data-urlencode 'limit=3' \
5    --data-urlencode 'select=date,price,postcode' \
6    --data-urlencode 'framing_output_format=JSONEachPacketString' \
7    --data-urlencode 'send_profile_events=0'

1{"packet":"data","data":"{\"date\":\"2017-07-31\",\"price\":594300000,\"postcode\":\"W1U 8EW\"}\n{\"date\":\"2018-02-08\",\"price\":569200000,\"postcode\":\"W1J 7BT\"}\n{\"date\":\"2019-11-20\",\"price\":542540820,\"postcode\":\"NW5 2HB\"}\n"}
2{"packet":"progress","progress":{"read_rows":"2867200","read_bytes":"39810980","total_rows_to_read":"2867200","result_rows":"3","result_bytes":"902","elapsed_ns":"10055000","memory_usage":"1860249"}}

packet フィールドが各パケットの内容を示します。ここでは、1 つ目のパケットにクエリ結果が、2 つ目に最終的な進捗カウンターが入っています。各パケットが届いた時点で表示されるように、curl の --no-buffer オプションを使っています。

フレーミング出力フォーマットに EventStream を指定すると、クエリのレスポンスを Server-Sent Events(SSE)として返すこともできます:

1curl --no-buffer --silent --user 'api_user:' --get \
2    'http://localhost:8123/api/town-sales/LONDON' \
3    --data-urlencode 'sort=-price' \
4    --data-urlencode 'limit=3' \
5    --data-urlencode 'select=date,price,postcode' \
6    --data-urlencode 'framing_output_format=EventStream' \
7    --data-urlencode 'send_profile_events=0'

1event: data
2data: eyJkYXRlIjoiMjAxNy0wNy0zMSIsInByaWNlIjo1OTQzMDAwMDAsInBvc3Rjb2RlIjoiVzFVIDhFVyJ9CnsiZGF0ZSI6IjIwMTgtMDItMDgiLCJwcmljZSI6NTY5MjAwMDAwLCJwb3N0Y29kZSI6IlcxSiA3QlQifQp7ImRhdGUiOiIyMDE5LTExLTIwIiwicHJpY2UiOjU0MjU0MDgyMCwicG9zdGNvZGUiOiJOVzUgMkhCIn0K
3
4event: progress
5data: {"read_rows":"2867200","read_bytes":"39810980","total_rows_to_read":"2867200","result_rows":"3","result_bytes":"902","elapsed_ns":"9820000","memory_usage":"1862345"}

data イベントの値は Base64 でエンコードされています。SSE は行指向の UTF-8 テキストプロトコルですが、ClickHouse の出力は複数行、不正な UTF-8、任意のバイナリデータを含み得ます。そのため ClickHouse は各 data パケットのペイロードを Base64 でエンコードします。これで 1 つの SSE フィールドに安全に載せられ、クライアント側では元のバイト列に復元できます。progress などの他のイベントは、そのまま読める JSON です。

前の例はどちらも処理が速すぎて、ClickHouse が途中の進捗パケットを返す前に終わってしまいます。途中の進捗を見るために、次のクエリではデータセット全体を走査し、年ごとの売買件数、平均価格、価格の中央値を計算します:

1curl --no-buffer --silent --user 'api_user:' --get \
2    'http://localhost:8123/' \
3    --data-urlencode 'query=SELECT toYear(date) AS year, type, count() AS sales, round(avg(price)) AS average_price, quantileExact(0.5)(price) AS median_price FROM uk_price_paid GROUP BY year, type ORDER BY year, type' \
4    --data-urlencode 'framing_output_format=EventStream' \
5    --data-urlencode 'send_profile_events=0' \
6    --data-urlencode 'interactive_delay=50000' \
7    --data-urlencode 'max_threads=1'

interactive_delay=50000 で進捗パケットを 50 ミリ秒ごとに送れるようにし、max_threads=1 でクエリを遅くしてパケットが見えるようにしています。これらの設定はデモのためのものです。本番環境では使わないでください。

1event: progress
2data: {"read_rows":"1177362","read_bytes":"8241534","total_rows_to_read":"6501466","elapsed_ns":"51300000"}
3
4event: progress
5data: {"read_rows":"14466268","read_bytes":"101263876","total_rows_to_read":"23712297","elapsed_ns":"252277000"}
6
7event: progress
8data: {"read_rows":"30452463","read_bytes":"213167241","total_rows_to_read":"30452463","elapsed_ns":"399507000"}
9
10event: data
11data: eyJ5ZWFy...Cg==
12
13event: progress
14data: {"read_rows":"30452463","read_bytes":"213167241","total_rows_to_read":"30452463","result_rows":"10","result_bytes":"917","elapsed_ns":"409242000","memory_usage":"19570057"}

進捗パケットは、ClickHouse がテーブルを走査している間に届きます。これは集計クエリなので、data パケットは ClickHouse が最終的なグループを計算し終えた終盤に届きます。

API リクエストの観測

名前付きハンドラーへのリクエストと、テーブルを直接指す API パスへのリクエストは system.query_log に記録されます。最近成功した HTTP リクエストは次のクエリで確認できます:

1SYSTEM FLUSH LOGS;
2
3SELECT event_time, http_handler_name, http_request_url, query_duration_ms, read_rows, result_rows
4FROM system.query_log
5WHERE (type = 'QueryFinish') AND (http_request_url != '')
6ORDER BY event_time DESC
7LIMIT 3;

1┌──────────event_time─┬─http_handler_name─┬─http_request_url───────────┬─query_duration_ms─┬─read_rows─┬─result_rows─┐
2│ 2026-09-02 12:28:58 │ town_sales        │ /api/town-sales            │                12 │   2300102 │          10 │
3│ 2026-09-02 12:28:16 │                   │ /default/uk_price_paid.csv │                26 │  26946287 │           3 │
4│ 2026-09-02 12:28:12 │ town_sales_path   │ /api/town-sales/LONDON     │                15 │    647168 │       13958 │
5└─────────────────────┴───────────────────┴────────────────────────────┴───────────────────┴───────────┴─────────────┘

名前付きハンドラーの場合、http_handler_name にリクエストを処理したハンドラーが入ります。テーブルを直接指す API リクエストでは空ですが、http_request_url にはクエリ文字列を除いたリクエストパスが記録されます。

26.8 では新しいシステムテーブル system.user_query_log も導入され、ユーザーはクエリログ全体へのアクセス権なしに自分のクエリ履歴を見られます。これにより、api_user が実行したすべての API リクエストは次のように取得できます:

1./clickhouse client --user api_user <<'SQL'
2SELECT
3    event_time, http_handler_name, http_request_url,
4    query_duration_ms, read_rows, result_rows
5FROM system.user_query_log
6WHERE type = 'QueryFinish' AND http_request_url != ''
7ORDER BY event_time DESC
8LIMIT 3 FORMAT Pretty;
9SQL

ハンドラーへのアクセス制御

ハンドラーの URL は HTTP サーバーに到達できるどのユーザーからでもリクエストできますが、そのクエリは認証されたユーザーの権限で実行されます。ハンドラー単位で呼び出しを許可する個別の権限はありません。

実際に確かめるために、uk_price_paid へのアクセス権を持たない別の API ユーザーを作成します:

1CREATE USER restricted_api_user
2IDENTIFIED WITH no_password
3HOST LOCAL
4SETTINGS default_format = 'JSONEachRow', limit = 10;

付与されている権限は次のコマンドで確認できます:

1SHOW GRANTS FOR restricted_api_user;

1Ok.
2
30 rows in set. Elapsed: 0.002 sec.

このユーザーで expensive_areas ハンドラーを呼び出してみます:

1curl --silent --user 'restricted_api_user:' 'http://localhost:8123/api/expensive-areas'

次の出力が返ります:

1Code: 497. DB::Exception: restricted_api_user: Not enough privileges. To execute this query, it's necessary to have the grant SELECT ON default.uk_price_paid. (ACCESS_DENIED) (version 26.9.1.466 (official build))

ハンドラーはテーブルへのクエリだけでなく、ビューへのクエリも包めます。テーブル全体を公開せずにクエリでアクセスさせる便利な方法です。たとえば、元の取引データではなく、expensive_areas の背後にある集計結果だけを公開するビューを作成できます。

ビューを所有するユーザーを作成します。このユーザーは uk_price_paid テーブルをクエリできますが、HOST NONE により誰もこのアカウントで直接ログインできません:

1CREATE USER api_view_owner
2IDENTIFIED WITH no_password
3HOST NONE;
4
5GRANT SELECT ON uk_price_paid
6TO api_view_owner;

次に、許可するクエリを含む DEFINER ビューを作成します:

1CREATE VIEW expensive_areas_api
2DEFINER = api_view_owner
3SQL SECURITY DEFINER
4AS
5SELECT town, district, count() AS sales, round(avg(price)) AS average_price
6FROM uk_price_paid
7WHERE date >= '2024-01-01'
8GROUP BY ALL
9HAVING sales >= 100;

SQL SECURITY DEFINER を付けると、ビューの元になるクエリは呼び出し元ではなく api_view_owner の権限で実行されます。

続いて、このビューへのアクセス権を restricted_api_user に付与します:

1GRANT SELECT ON expensive_areas_api
2TO restricted_api_user;

これで、最初のハンドラーが uk_price_paid を直接ではなく expensive_areas_api をクエリするように書き換えられます:

1ALTER HANDLER expensive_areas
2AS
3SELECT *
4FROM expensive_areas_api
5ORDER BY average_price DESC;

制限付きユーザーからハンドラーを呼び出せるようになりました:

1curl --silent --user 'restricted_api_user:' 'http://localhost:8123/api/expensive-areas'

1{"town":"LONDON","district":"CITY OF WESTMINSTER","sales":3830,"average_price":2627535}
2{"town":"LONDON","district":"CITY OF LONDON","sales":367,"average_price":2523498}
3{"town":"LONDON","district":"KENSINGTON AND CHELSEA","sales":2694,"average_price":2149896}
4{"town":"PURFLEET-ON-THAMES","district":"THURROCK","sales":126,"average_price":1813003}
5{"town":"VIRGINIA WATER","district":"RUNNYMEDE","sales":114,"average_price":1600253}
6{"town":"LONDON","district":"CAMDEN","sales":3200,"average_price":1331681}
7{"town":"LEATHERHEAD","district":"ELMBRIDGE","sales":111,"average_price":1273186}
8{"town":"BARNET","district":"ENFIELD","sales":116,"average_price":1235428}
9{"town":"COBHAM","district":"ELMBRIDGE","sales":330,"average_price":1222086}
10{"town":"RADLETT","district":"HERTSMERE","sales":228,"average_price":1219858}

しかし、元のテーブルにはアクセスできません:

1./clickhouse client -mn --user restricted_api_user --query "SELECT * FROM uk_price_paid"

1Received exception from server (version 26.9.1):
2Code: 497. DB::Exception: Received from localhost:9000. DB::Exception: restricted_api_user: Not enough privileges. To execute this query, it's necessary to have the grant SELECT ON default.uk_price_paid. (ACCESS_DENIED)
3(query: SELECT * FROM default.uk_price_paid LIMIT 1)

この方法でユーザーが制限されるのは、ハンドラーの URL だけではなく、ビューが公開するデータの範囲です。restricted_api_user は expensive_areas_api ビューを直接クエリできますが、元の不動産取引データには依然としてアクセスできません。

まとめ

この記事では、ClickHouse 26.8 で導入された機能を使い、英国不動産価格データセットを題材に HTTP API を構築しました。名前付きエンドポイントを作成し、型付きのパラメーターをクエリ文字列と URL パスの両方で受け取りました。元のクエリを変えずに結果を加工し、テーブルを URL 経由で直接公開し、クエリのデータと進捗を同じレスポンスでストリーミングしました。

また、クエリログで API リクエストを観測し、SQL SECURITY DEFINER ビューを使って、元の取引データへのアクセス権を付与せずに集計結果を公開しました。API が内容を制御した ClickHouse クエリを公開するだけでよいなら、これらの機能により別途パススルーサービスを用意する必要はなくなります。ただし、ビジネスロジック、オーケストレーション、アプリケーション固有のバリデーションが必要な場合は、アプリケーション層は引き続き必要です。

今すぐ始める

ご自身のデータで ClickHouse がどう動くかを確かめてみませんか? ClickHouse Cloud なら数分で始められ、$300 分の無料クレジットを受け取れます。

サインアップ

ストリーミング HTTP API としての ClickHouse

英国不動産価格データセットの準備

データを探索するクエリ

API ユーザーの設定

名前付き HTTP エンドポイントの作成

型付きパラメーター

URL パス内の型付きパラメーター

結果を加工する汎用設定

動的なフィルタリング

ハンドラーの一覧

テーブルを API エンドポイントにする

HTTP レスポンスのストリーミング

API リクエストの観測

ハンドラーへのアクセス制御

まとめ

今すぐ始める

Subscribe to our newsletter

最近の記事

ClickHouse Managed Postgres の「スケジュールされたアップグレード」のご紹介

ClickPipes の MySQL CDC コネクタが一般提供 (GA) を開始

ClickHouse Managed Postgres の新しいオンボーディング体験

ClickHouse はオブザーバビリティ戦争に勝っているのか?

ストリーミング HTTP API としての ClickHouse

英国不動産価格データセットの準備 #

データを探索するクエリ #

API ユーザーの設定 #

名前付き HTTP エンドポイントの作成 #

型付きパラメーター #

URL パス内の型付きパラメーター #

結果を加工する汎用設定 #

動的なフィルタリング #

ハンドラーの一覧 #

テーブルを API エンドポイントにする #

HTTP レスポンスのストリーミング #

API リクエストの観測 #

ハンドラーへのアクセス制御 #

まとめ #

今すぐ始める

Subscribe to our newsletter

最近の記事

ClickHouse Managed Postgres の「スケジュールされたアップグレード」のご紹介

ClickPipes の MySQL CDC コネクタが一般提供 (GA) を開始

ClickHouse Managed Postgres の新しいオンボーディング体験

ClickHouse はオブザーバビリティ戦争に勝っているのか?

英国不動産価格データセットの準備

データを探索するクエリ

API ユーザーの設定

名前付き HTTP エンドポイントの作成

型付きパラメーター

URL パス内の型付きパラメーター

結果を加工する汎用設定

動的なフィルタリング

ハンドラーの一覧

テーブルを API エンドポイントにする

HTTP レスポンスのストリーミング

API リクエストの観測

ハンドラーへのアクセス制御

まとめ