ClickHouse 26.8：构建流式 HTTP 查询 API

在ClickHouse前面加一个薄薄的API层，接受HTTP参数、构建查询并返回结果，这种做法很常见。在ClickHouse 26.8中，我们可以直接使用命名HTTP处理器、结果修改和分帧格式在很大程度上实现这一点。

在这篇文章中，我们将使用这些功能围绕英国房产价格数据集构建一个流式HTTP API。如果我们的API有业务逻辑、编排或特定于应用程序的验证，我们仍然需要应用程序层。但如果它只暴露受控的ClickHouse查询，我们可以从架构中移除一层。

设置英国房产价格数据集

该英国房产价格数据集包含自1995年以来在英国售出的房产详细信息。让我们从创建表开始：

1CREATE TABLE uk_price_paid2(3    price UInt32,4dateDate,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'=013    ),14    is_new UInt8,15    duration Enum8(16'freehold'=1,17'leasehold'=2,18'unknown'=019    ),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 = MergeTree29ORDERBY (postcode1, postcode2, addr1, addr2);

源CSV没有表头，所以ClickHouse的模式推断将其列命名为c1至c16。我们将在摄取数据时使用这些位置名称来转换源字段：

1INSERT INTO uk_price_paid2SELECT3    toUInt32(c2) AS price,4    toDate(c3) ASdate,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 county25FROM url(26'http://prod1.publicdata.landregistry.gov.uk.s3-website-eu-west-1.amazonaws.com/pp-complete.csv',27'CSV'28)29SETTINGS30    schema_inference_make_columns_nullable =0,31    max_http_get_redirects =10;

一些探索性查询

让我们运行一些探索性查询来了解数据，从交易数量和第一笔及最后一笔交易开始：

1SELECT2count() AS transactions,3min(date) AS first_transaction,4max(date) AS last_transaction5FROM 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_disk2FROM system.parts3WHERE database ='default'ANDtable='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_price2FROM uk_price_paid3WHEREdate>='2024-01-01'4GROUPBYALL5HAVING sales >=1006ORDERBY average_price DESC7LIMIT 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_user，我们将使用它来运行所有对API端点的查询。该用户具有默认输出格式JSONEachRow和默认限制10条记录：

1CREATEUSER api_user2IDENTIFIED WITH no_password3HOST LOCAL4SETTINGS default_format ='JSONEachRow', limit =10;

该limit设置将返回给我们的用户的每个结果限制为10行，除非请求指定了不同的限制。

然后我们将授予此用户查询uk_price_paid表的权限：

1GRANTSELECTON uk_price_paid TO api_user;

创建命名HTTP端点

我们可以使用26.8中引入的CREATE HANDLER语句创建HTTP端点。处理器需要URL和要运行的查询。我们还可以指定它支持哪些HTTP方法；如果我们不指定，默认为GET。

注意

支持的方法有GET、POST、PUT和DELETE。

以下处理器返回自2024年1月1日以来最昂贵的区域，并将可通过GET请求到/api/expensive-areas进行查询：

1CREATE HANDLER expensive_areas2URL '/api/expensive-areas'3METHODS (GET)4AS5SELECT town, district, count() AS sales, round(avg(price)) AS average_price6FROM uk_price_paid7WHEREdate>='2024-01-01'8GROUPBYALL9HAVING sales >=10010ORDERBY average_price DESC;

注意

创建处理器时会检查查询的语法正确性，但不会进行语义分析。例如，ClickHouse不会检查引用的表和列是否存在，直到调用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_sales2URL '/api/town-sales'3METHODS (GET)4AS5SELECT6date, price, type, duration,7    concat(postcode1, ' ', postcode2) AS postcode,8    district, street, addr1, addr29FROM uk_price_paid10WHERE town = upperUTF8({town:String})11ANDdate>= {from:Date}12AND price >= {minimum_price:UInt32}13ORDERBY 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_path2URL REGEXP '/api/town-sales/(?P<town>[^/]+)(?P<district_path>(?:/[^/]+)?)'3METHODS (GET)4AS5SELECTdate, price, type, duration,6      concat(postcode1, ' ', postcode2) AS postcode,7      town, district, street, addr1, addr28FROM uk_price_paid9WHERE town = upperUTF8({town:String})10AND (11    {district_path:String} =''12OR 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"}

结果修改的通用设置

在ClickHouse 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英镑的房产。我们还会添加一个次要排序字段：

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让我们分页浏览结果。以下请求获取接下来的三个结果。我们在排序中包含额外字段，以便具有相同日期和价格的行按确定性顺序返回：

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启用它，如下所示：

1ALTERUSER api_user2ADD 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, methods2FROM system.handlers3ORDERBY 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启用更多设置：

1ALTERUSER api_user2ADD SETTINGS3    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响应流现在可以携带数据、进度、总计、分析事件、服务器日志和异常。

一种分帧格式将查询的常规输出包装在类型化数据包的流中。在以下示例中，数据包包含JSONEachRow结果，而进度包报告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字段标识每个数据包包含的内容。这里，第一个数据包包含查询结果，第二个包含最终进度计数器。我们使用curl的--no-buffer选项，以便在数据包到达时立即显示。

我们还可以通过将分帧输出格式指定为EventStream来将查询响应作为服务器推送事件（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: data2data: eyJkYXRlIjoiMjAxNy0wNy0zMSIsInByaWNlIjo1OTQzMDAwMDAsInBvc3Rjb2RlIjoiVzFVIDhFVyJ9CnsiZGF0ZSI6IjIwMTgtMDItMDgiLCJwcmljZSI6NTY5MjAwMDAwLCJwb3N0Y29kZSI6IlcxSiA3QlQifQp7ImRhdGUiOiIyMDE5LTExLTIwIiwicHJpY2UiOjU0MjU0MDgyMCwicG9zdGNvZGUiOiJOVzUgMkhCIn0K34event: progress5data: {"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对每个数据包的有效负载进行Base64编码，以便在单个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: progress2data: {"read_rows":"1177362","read_bytes":"8241534","total_rows_to_read":"6501466","elapsed_ns":"51300000"}34event: progress5data: {"read_rows":"14466268","read_bytes":"101263876","total_rows_to_read":"23712297","elapsed_ns":"252277000"}67event: progress8data: {"read_rows":"30452463","read_bytes":"213167241","total_rows_to_read":"30452463","elapsed_ns":"399507000"}910event: data11data: eyJ5ZWFy...Cg==1213event: progress14data: {"read_rows":"30452463","read_bytes":"213167241","total_rows_to_read":"30452463","result_rows":"10","result_bytes":"917","elapsed_ns":"409242000","memory_usage":"19570057"}

进度包在ClickHouse扫描表时到达。因为这是一个聚合，数据包在接近结束时到达，一旦ClickHouse计算出最终分组。

观察API请求

对命名处理器和直接表API路径的请求记录在system.query_log中。我们可以使用以下查询检查最近的成功的HTTP请求：

1SYSTEM FLUSH LOGS;23SELECT event_time, http_handler_name, http_request_url, query_duration_ms, read_rows, result_rows4FROM system.query_log5WHERE (type ='QueryFinish') AND (http_request_url !='')6ORDERBY event_time DESC7LIMIT 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'2SELECT3    event_time, http_handler_name, http_request_url,4    query_duration_ms, read_rows, result_rows5FROM system.user_query_log6WHERE type = 'QueryFinish' AND http_request_url != ''7ORDER BY event_time DESC8LIMIT 3 FORMAT Pretty;9SQL

控制对处理器的访问

处理器的URL可以被任何能够访问HTTP服务器的用户请求，但其查询以经过身份验证的用户权限运行。没有单独的权限用于调用单个处理器。

为了实际演示，让我们创建另一个无权访问uk_price_paid的API用户：

1CREATEUSER restricted_api_user2IDENTIFIED WITH no_password3HOST LOCAL4SETTINGS default_format ='JSONEachRow', limit =10;

我们可以通过运行以下命令确认其授权：

1SHOW GRANTS FOR restricted_api_user;

1Ok.230 rows inset. 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阻止任何人直接登录该账户：

1CREATEUSER api_view_owner2IDENTIFIED WITH no_password3HOST NONE;45GRANTSELECTON uk_price_paid6TO api_view_owner;

接下来，我们将创建一个包含允许查询的定义者视图：

1CREATEVIEW expensive_areas_api2DEFINER = api_view_owner3SQL SECURITY DEFINER4AS5SELECT town, district, count() AS sales, round(avg(price)) AS average_price6FROM uk_price_paid7WHEREdate>='2024-01-01'8GROUPBYALL9HAVING sales >=100;

SQL SECURITY DEFINER表示视图的底层查询以api_view_owner的权限运行，而不是调用者的权限。

然后，我们将对该视图的访问权限授予我们的restricted_api_user：

1GRANTSELECTON expensive_areas_api2TO restricted_api_user;

然后，我们可以将最初的处理器更新为查询expensive_areas_api而不是直接查询uk_price_paid：

1ALTER HANDLER expensive_areas2AS3SELECT*4FROM expensive_areas_api5ORDERBY 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查询，这些功能可以消除对单独传递服务的需求，但当我们需要业务逻辑、编排或特定于应用程序的验证时，我们仍然需要应用程序层。