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

最大三角形三个桶是一种用于对数据进行下采样的算法，以便于可视化。它试图在减少点数的同时保留初始数据的视觉相似性。特别是，它似乎非常擅长保留局部最小值和最大值，而这些值在使用其他下采样方法时往往会丢失。

我们将借助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"

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

让我们看看最大三角形三个桶算法的效果如何。查询（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 数组，并通过流行的平台 HuggingFace 以 npy 格式 分发。每个向量还具有伴随的 以 Parquet 文件格式的元数据，其属性包括标题、图像的高度和宽度，以及图像和文本之间的相似度得分。

为了将这些数据插入 ClickHouse，我们当时被迫 编写 Python 代码 来合并 npy 文件和 Parquet 文件——目的是拥有一个包含所有列的单个表。虽然 ClickHouse 对 Parquet 有很好的支持，但 npy 格式不受支持。为了使问题更具挑战性，npy 文件仅设计为包含浮点数组。因此，数据集的连接需要基于行位置进行。虽然 Python 方法足够且易于在文件级别并行化（超过 2300 个文件集需要合并），但当我们无法仅使用 ClickHouse 本地解决问题时，我们总是感到沮丧！这个问题对于其他 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.)

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

这里关于性能的一点说明。以上方法并没有比原始 Python 实现（需要 227 秒）快多少，并且内存效率也较低，因为前者一次只执行一个块的连接——在这方面，我们的 Python 脚本受益于成为一个针对问题的定制解决方案。我们还被迫使用单个线程执行读取以保持行顺序。但是，它具有通用性和足够满足大多数数据集的需求。对于希望跨多个文件并行化处理的用户，还可以应用一个相对简单的 bash 命令。