ClickHouse 23.10 版本发布

我们非常激动地分享 23.10 版本中大量令人惊叹的功能

并且，我们已经确定了 23.11 版本的发布日期，请立即注册参加 12 月 5 日上午 9:00（PDT）/晚上 6:00（CET）的社区电话会议。

23 个新功能。26 项性能优化。60 个错误修复。

以下是精选功能的一小部分...但该版本涵盖了新的 SHOW MERGES 和 SHOW SETTINGS 命令，新的 byteSwap、 arrayRandomSample、 jsonMergePatch、 formatQuery、 formatQuerySingleLine 函数，作为组合器的 argMin 和 argMax，带分区的参数化 ALTER 命令，具有更好名称的 untuple 函数，强制执行投影，允许不带主键的表，以及更多...更多内容请见此处。

与往常一样，我们特别欢迎 23.10 版本的所有新贡献者！ClickHouse 的受欢迎程度很大程度上归功于社区的贡献。看到社区不断壮大总是令人感到谦卑。

如果您在这里看到了您的名字，请与我们联系...但我们也会在 Twitter 等平台上找到您。

AN, Aleksa Cukovic, Alexander Nikolaev, Avery Fischer, Daniel Byta, Dorota Szeremeta, Ethan Shea, FFish, Gabriel Archer, Itay Israelov, Jens Hoevenaars, Jihyuk Bok, Joey Wang, Johnny, Joris Clement, Lirikl, Max K, Priyansh Agrawal, Sinan, Srikanth Chekuri, Stas Morozov, Vlad Seliverstov, bhavuk2002, guoxiaolong, huzhicheng, monchickey, pdy, wxybear, yokofly

最大三角形三桶算法 (Largest Triangle Three Buckets) 是一种用于数据降采样的算法，使其更易于可视化。它试图在减少点数量的同时保留初始数据的视觉相似性。特别是，它似乎非常擅长保留局部最小值和最大值，而这些值通常在其他降采样方法中丢失。

我们将借助 Kaggle SF Bay Area Bike Share 数据集来了解它的工作原理。该数据集包含一个 CSV 文件，该文件按分钟跟踪每个站点的可用停靠点数量。

让我们创建一个数据库

CREATE DATABASE BikeShare;
USE BikeShare;

然后创建一个表 status，由 status.csv 文件填充

create table status engine MergeTree order by (station_id, time) AS
from file('Bay Area Bikes.zip :: status.csv', CSVWithNames)
SELECT *
SETTINGS schema_inference_make_columns_nullable=0;

SELECT formatReadableQuantity(count(*))
FROM status

┌─formatReadableQuantity(count())─┐
│ 71.98 million                   │
└─────────────────────────────────┘

原始数据

首先，让我们看一下几天内其中一个站点的原始数据。以下查询返回了 4,537 个点，存储在文件 raw.sql 中

from BikeShare.status select toUnixTimestamp64Milli(time), docks_available
where toDate(time) >= '2013-08-29' and toDate(time) <= '2013-09-01'
and station_id = 70
FORMAT CSV

我们可以通过运行以下查询来可视化一段时间内的可用停靠点

clickhouse local --path bikeshare.chdb < raw.sql |
uplot line -d, -w 100 -t "Raw Data"

接下来，我们将看看如果我们大约减少 10 倍的点数会发生什么，这可以通过对 10 分钟间隔内的点进行平均来实现。此查询将存储在文件 avg.sql 中，如下所示

WITH buckets AS (
  SELECT
    toStartOfInterval(time, INTERVAL 10 minute) AS bucket,
    AVG(docks_available) AS average_docks_available,
    AVG(toUnixTimestamp64Milli(time)) AS average_bucket_time
  FROM BikeShare.status
  where toDate(time) >= '2013-08-29' and toDate(time) <= '2013-09-01'
  AND (station_id = 70)
  GROUP BY bucket
  ORDER BY bucket
)
SELECT average_bucket_time, average_docks_available
FROM buckets
FORMAT CSV

我们可以像这样生成可视化

clickhouse local --path bikeshare.chdb < avg.sql |
uplot line -d, -w 100 -t "Average every 5 mins"

这种降采样效果还不错，但它丢失了曲线形状中一些更细微的变化。丢失的变化在原始数据可视化中以红色圈出

让我们看看最大三角形三桶算法 (Largest Triangle Three Buckets) 的表现。查询 (lttb.sql) 如下所示

from BikeShare.status
select untuple(arrayJoin(
  largestTriangleThreeBuckets(50)(
    toUnixTimestamp64Milli(time), docks_available
)))
where toDate(time) >= '2013-08-29' and toDate(time) <= '2013-09-01' AND station_id = 70
FORMAT CSV

我们可以像这样生成可视化

clickhouse local --path bikeshare.chdb < lttb.sql |
uplot line -d, -w 100 -t "Largest Triangle Three Buckets"

从目视检查来看，此版本的可视化仅缺少以下局部最小值

ClickHouse 提供的 SQL 具有许多扩展和强大的改进，使其对分析任务更加友好。ClickHouse SQL 超集的一个例子是对数组的广泛支持。数组对于其他编程语言（如 Python 和 JavaScript）的用户来说是众所周知的。它们通常可用于以优雅而简单的方式建模和解决各种问题。ClickHouse 拥有 70 多个用于处理数组的函数，其中许多函数是 高阶函数，提供高级别的抽象，使您能够以简洁且声明式的方式表达数组上的复杂操作。我们自豪地宣布，此数组函数系列现在有了一个新的、期待已久且最强大的成员：arrayFold。

arrayFold 相当于 JavaScript 中的 Array.reduce 函数，用于通过将 lambda 函数应用于数组元素，以累积方式从左到右折叠或减少数组中的元素，从最左边的元素开始，并在处理每个元素时累积结果。这种累积过程可以被认为是将数组的元素折叠在一起。

以下是一个简单的示例，我们使用 arrayFold 来计算数组 [10, 20, 30] 中所有元素的总和

SELECT arrayFold((acc, v) -> (acc + v), [10, 20, 30],  0::UInt64) AS sum

┌─sum─┐
│  60 │
└─────┘

请注意，在上面的 arrayFold 示例调用中，我们同时传递了 lambda 函数 (acc, v) -> (acc + v) 和初始累加器值 0。

然后，lambda 函数被调用，其中 acc 设置为初始累加器值 0，v 设置为第一个（最左边）数组元素 10。接下来，lambda 函数被调用，其中 acc 设置为上一步的结果，v 设置为第二个数组元素 20。此过程继续进行，从左到右迭代折叠数组元素，直到到达数组末尾，从而产生最终结果 60。

下图可视化了 lambda 函数主体中的 + 运算符如何以累积方式应用于初始累加器和所有数组元素（从左到右）：

我们上面的示例仅用作介绍。我们可以使用 arraySum 或 arrayReduce(sum) 来计算所有数组元素的总和。但是 arrayFold 功能更强大。它是 ClickHouse 数组函数系列中最通用和最灵活的成员之一，可用于对数组执行各种操作，例如聚合、过滤、映射、分组和更复杂的任务。

（1）以 lambda 函数的形式提供自定义折叠函数，以及（2）在每个迭代步骤中保持、检查和塑造折叠状态（累加器）的可能性，这是一个强大的组合，允许以简洁且可组合的方式进行复杂的数据处理。我们通过一个更复杂的示例来演示这一点。大约一年前，我们 挑战 我们的社区制定一个查询来重建 git blame 命令，并为第一个解决方案提供一件 T 恤。我们甚至提到

“从提交历史中重建这一点尤其具有挑战性 - 尤其是因为 ClickHouse 目前没有 arrayFold 函数来迭代当前状态。”

好吧，现在是您赢得 T 恤衫的机会 🤗

以下是一个相关的简化示例，它模拟了一个由 ClickHouse 驱动的文本编辑器，该编辑器提供无限的时间/版本旅行，我们仅存储每行更改，并利用 arrayFold 轻松重建每个版本（或时间点）的完整文本。

我们创建表来存储行更改历史记录（每个版本，我们也可以使用 DateTime 字段来跟踪更改时间）

CREATE OR REPLACE TABLE line_changes
(
    version UInt32,
    line_change_type Enum('Add' = 1, 'Delete' = 2, 'Modify' = 3),
    line_number UInt32,
    line_content String
)
ENGINE = MergeTree
ORDER BY time;

我们存储行更改的历史记录

INSERT INTO default.line_changes VALUES
(1, 'Add'   , 1, 'ClickHouse provides SQL'),
(2, 'Add'   , 2, 'with improvements'),
(3, 'Add'   , 3, 'that makes it more friendly for analytical tasks.'),
(4, 'Add'   , 2, 'with many extensions'),
(5, 'Modify', 3, 'and powerful improvements'),
(6, 'Delete', 1, ''),
(7, 'Add'   , 1, 'ClickHouse provides a superset of SQL');

我们创建了三个用户定义的函数来操作数组内容（我们创建这些 UDF 只是为了提高可读性；或者，我们可以将它们的主体内联到下面的主查询中）

-- add a string (str) into an array (arr) at a specific position (pos)
CREATE OR REPLACE FUNCTION add AS (arr, pos, str) -> 
  arrayConcat(arraySlice(arr, 1, pos-1), [str], arraySlice(arr, pos));
  
-- delete the element at a specific position (pos) from an array (arr)
CREATE OR REPLACE FUNCTION delete AS (arr, pos) -> 
  arrayConcat(arraySlice(arr, 1, pos-1), arraySlice(arr, pos+1));

-- replace the element at a specific position (pos) in an array (arr)
CREATE OR REPLACE FUNCTION modify AS (arr, pos, str) -> 
  arrayConcat(arraySlice(arr, 1, pos-1), [str], arraySlice(arr, pos+1));

我们创建了一个参数化视图，其中包含利用 arrayFold 的主查询

CREATE OR REPLACE VIEW text_version AS
WITH T1 AS (
  SELECT arrayZip(
    groupArray(line_change_type),
    groupArray(line_number),
    groupArray(line_content)) as line_ops
  FROM (SELECT * FROM line_changes 
        WHERE version <= {version:UInt32} ORDER BY version ASC)
)
SELECT arrayJoin(
  arrayFold((acc, v) -> 
    if(v.'change_type' = 'Add',       add(acc, v.'line_nr', v.'content'),
    if(v.'change_type' = 'Delete', delete(acc, v.'line_nr'),
    if(v.'change_type' = 'Modify', modify(acc, v.'line_nr', v.'content'), []))),
    line_ops::Array(Tuple(change_type String, line_nr UInt32, content String)),
    []::Array(String))) as lines
FROM T1;

我们浏览文本版本

SELECT * FROM text_version(version = 2);

┌─lines─────────────────────────────────────────────┐
│ ClickHouse provides SQL                           │
│ that makes it more friendly for analytical tasks. │
└───────────────────────────────────────────────────┘


SELECT * FROM text_version(version = 3);

┌─lines─────────────────────────────────────────────┐
│ ClickHouse provides SQL                           │
│ with improvements                                 │
│ that makes it more friendly for analytical tasks. │
└───────────────────────────────────────────────────┘


SELECT * FROM text_version(version = 7);

┌─lines─────────────────────────────────────────────┐
│ ClickHouse provides a superset of SQL             │
│ with many extensions                              │
│ and powerful improvements                         │
│ that makes it more friendly for analytical tasks. │
└───────────────────────────────────────────────────┘

在上面的主查询中，我们使用了 ClickHouse 中的典型设计模式，即使用 groupArray 聚合函数（临时）将表的特定行值转换为数组。然后可以通过数组函数方便地处理它，并通过 arrayJoin 聚合函数将结果转换回单个表行。请注意我们如何利用 arrayFold 以累积方式重建文本版本，从空数组作为初始累加器值开始，并使用累加器数组内的位置来表示行号。

今年早些时候，我们通过 两部分 博客系列 探索了 ClickHouse 对向量的支持。作为其中的一部分，我们从 LAION 数据集 及其随附的元数据中加载了超过 20 亿个向量到 ClickHouse 中。此数据集包含超过 20 亿张图像及其标题的向量嵌入，这些图像和标题是从 分布式爬网 中收集的。这些嵌入是使用多模态模型生成的，允许用户使用文本搜索图像，反之亦然。

这些向量以 Numpy 数组的形式分布在 npy 格式中，通过流行的 HuggingFace 平台。每个向量还附带有 Parquet 文件格式的元数据，其中包含标题、图像的高度和宽度以及图像和文本之间的相似度得分等属性。

为了在当时将此数据插入 ClickHouse，我们不得不 编写 Python 代码 来合并 npy 文件和 Parquet 文件 - 目标是拥有一个包含所有列的单个表。虽然 ClickHouse 对 Parquet 提供了出色的支持，但不支持 npy 格式。更具挑战性的是，npy 文件仅设计为包含浮点数组。因此，连接数据集需要基于行位置完成。虽然 Python 方法足够用，并且在文件级别上易于并行化，有超过 2300 个文件集要合并，但当我们无法仅使用 clickhouse local 解决问题时，我们总是感到沮丧！对于其他 Hugging Face 数据集来说，这个问题也很常见，这些数据集由嵌入和元数据组成。因此，需要一种轻量级、无需代码的方法将此数据加载到 ClickHouse 中。

在 23.10 版本中，ClickHouse 现在支持 npy 文件，允许我们重新审视这个问题。

对于 LAION 数据集，文件以 4 位后缀编号，例如 text_emb_0023.npy、 metadata_0023.parquet，通用后缀表示子集。对于每个子集，我们有 3 个文件：一个用于图像嵌入的 npy 文件，一个用于文本嵌入的文件，以及一个 Parquet 元数据文件。

SELECT array AS text_emb
FROM file('input/text_emb/text_emb_0000.npy')
LIMIT 1
FORMAT Vertical

Row 1:
──────
text_emb: [-0.0126877,0.0196686,..,0.0177155,0.00206757]

1 row in set. Elapsed: 0.001 sec.


SELECT *
FROM file('input/metadata/metadata_0000.parquet')
LIMIT 1
FORMAT Vertical
SETTINGS input_format_parquet_skip_columns_with_unsupported_types_in_schema_inference = 1

Row 1:
──────
image_path:  	185120009
caption:     	Color version PULP FICTION alternative poster art
NSFW:        	UNLIKELY
similarity:  	0.33966901898384094
LICENSE:     	?
url:         	http://cdn.shopify.com/s/files/1/0282/0804/products/pulp_1024x1024.jpg?v=1474264437
key:         	185120009
status:      	success
width:       	384
height:      	512
original_width:  768
original_height: 1024
exif:        	{"Image Orientation": "Horizontal (normal)", "Image XResolution": "100", "Image YResolution": "100", "Image ResolutionUnit": "Pixels/Inch", "Image YCbCrPositioning": "Centered", "Image ExifOffset": "102", "EXIF ExifVersion": "0210", "EXIF ComponentsConfiguration": "YCbCr", "EXIF FlashPixVersion": "0100", "EXIF ColorSpace": "Uncalibrated", "EXIF ExifImageWidth": "768", "EXIF ExifImageLength": "1024"}
md5:         	46c4bbab739a2b71639fb5a3a4035b36

1 row in set. Elapsed: 0.167 sec.

ClickHouse 文件读取和查询执行高度并行化以提高性能。乱序读取通常对于允许快速解析和读取至关重要。但是，要连接这些数据集，我们需要确保按顺序读取所有文件，以便允许按行号连接。因此，我们需要使用 max_threads=1。窗口函数 row_number() OVER () AS rn 为我们提供了可以连接数据集的行号。因此，我们替换自定义 Python 的查询是

INSERT INTO FUNCTION file('0000.parquet')
SELECT *
FROM
(
	SELECT
    	row_number() OVER () AS rn,
    	*
	FROM file('input/metadata/metadata_0000.parquet')
) AS metadata
INNER JOIN
(
	SELECT *
	FROM
	(
    	SELECT
        	row_number() OVER () AS rn,
        	array AS text_emb
    	FROM file('input/text_emb/text_emb_0000.npy')
	) AS text_emb
	INNER JOIN
	(
    	SELECT
        	row_number() OVER () AS rn,
        	array AS img_emd
    	FROM file('input/img_emb/img_emb_0000.npy')
	) AS img_emd USING (rn)
) AS emb USING (rn)
SETTINGS max_threads = 1, input_format_parquet_skip_columns_with_unsupported_types_in_schema_inference = 1

0 rows in set. Elapsed: 168.860 sec. Processed 2.82 million rows, 3.08 GB (16.68 thousand rows/s., 18.23 MB/s.)

在这里，我们将带有后缀 0000 的 npy 和 parquet 文件连接起来，并将结果输出到一个新的 0000.parquet 文件中。此示例可以轻松地调整为直接从 Hugging Face 读取文件。

关于此处性能的一点说明。以上方法并没有比原始 Python 实现（耗时 227 秒）快多少，并且内存效率较低，因为前者一次执行一个块的连接 - 我们的 Python 脚本在这方面受益于成为针对问题量身定制的自定义解决方案。我们还被迫使用单线程执行读取以保留行顺序。但是，它对于大多数数据集来说是通用的且足够用的。对于那些想要跨多个文件并行化该过程的人来说，也可以应用相对简单的 bash 命令。