这篇文章是我们精简系列的延续，我们在此系列中重点介绍酷炫的查询，并分享与 ClickHouse 相关的有趣技巧和窍门。
有兴趣在 ClickHouse Cloud 中试用 UDF 吗？立即开始，30 天内免费获得 300 美元信用额度。
简介
今天，我们专注于一个查询，该查询分析了使用 git-import 工具（随 ClickHouse 分发并在本系列之前的文章中介绍）捕获的 Git 数据而得出的 ClickHouse 仓库。此查询使用了 ClickHouse 的一个强大功能：SQL 用户定义函数，我们最近已在 ClickHouse Cloud 中提供此功能！
在这篇文章中，我们将创建一个查询，显示 ClickHouse 仓库中文件的完整提交历史记录。此查询将利用 UDF 来允许文件重命名。我们的最终解决方案已包含在文档中，我们欢迎改进。
这篇文章中的所有示例都可以在我们的 play.clickhouse.com 环境中重现（参见 git_clickhouse 数据库）。或者，如果您想更深入地研究此数据集，ClickHouse Cloud 是一个很好的起点，现在支持 UDF - 使用免费试用版启动集群，加载数据，让我们来处理基础设施，然后开始查询吧！
UDF 简要回顾
用户定义函数 (UDF) 允许用户通过创建可以利用 SQL 构造和函数的 lambda 表达式来扩展 ClickHouse 的行为。这些函数随后可以像查询中的任何内置函数一样使用。
要创建我们的 UDF，我们使用 CREATE FUNCTION <name> 语法，并将我们的方法签名指定为 lambda 表达式。在其最简单的形式中，它可能如下所示，它返回字符串 odd 或 even，具体取决于数字的奇偶性
CREATE FUNCTION parity_str AS (n) -> if(n % 2, 'odd', 'even'); SELECT number, parity_str(number) FROM numbers(5) ┌─number─┬─if(modulo(number, 2), 'odd', 'even')─┐ │ 0 │ even │ │ 1 │ odd │ │ 2 │ even │ │ 3 │ odd │ │ 4 │ even │ └────────┴──────────────────────────────────────┘✎
这是刻意简单的。正如我们将演示的那样，这些函数可能会变得相当复杂。
问题
我们的 git-import 为多个表生成来自仓库 git 提交历史记录的数据。其中一个表 file_changes 包含提交中每个已更改文件的行。因此，修改多个文件的提交将生成多行，从而允许我们使用简单的 SELECT 语句构建文件的历史记录。例如，在下面，我们查看了我们 ReplicatedMergeTree 的最新提交
SELECT time, substring(commit_hash, 1, 11) AS commit, change_type, author, lines_added AS added, lines_deleted AS deleted FROM git.file_changes WHERE path = 'src/Storages/StorageReplicatedMergeTree.cpp' ORDER BY time DESC LIMIT 5 ┌────────────────time─┬─commit──────┬─change_type─┬─author─────────────┬─added─┬─deleted─┐ │ 2022-10-30 16:30:51 │ c68ab231f91 │ Modify │ Alexander Tokmakov │ 13 │ 10 │ │ 2022-10-23 16:24:20 │ b40d9200d20 │ Modify │ Anton Popov │ 28 │ 30 │ │ 2022-10-23 01:23:15 │ 56e5daba0c9 │ Modify │ Anton Popov │ 28 │ 44 │ │ 2022-10-21 13:35:37 │ 851f556d65a │ Modify │ Igor Nikonov │ 3 │ 2 │ │ 2022-10-21 13:02:52 │ 13d31eefbc3 │ Modify │ Igor Nikonov │ 4 │ 4 │ └─────────────────────┴─────────────┴─────────────┴────────────────────┴───────┴─────────┘ 5 rows in set. Elapsed: 0.011 sec. Processed 3.91 thousand rows, 704.14 KB (350.59 thousand rows/s., 63.19 MB/s.)
这种方法一直有效，直到提交重命名文件为止。重命名由 change_type 值为 Rename 表示。如下所示，path 列保存了文件的新名称（也用于后续修改提交），而原始名称仅在此行的 old_path 列中引用。
SELECT time, substring(commit_hash, 1, 11) AS commit, change_type, path, old_path FROM git.file_changes WHERE path = 'src/Storages/StorageReplicatedMergeTree.cpp' ORDER BY time ASC LIMIT 2 FORMAT Vertical Row 1: ────── time: 2020-04-03 16:14:31 commit: 06446b4f08a change_type: Rename path: src/Storages/StorageReplicatedMergeTree.cpp old_path: dbms/Storages/StorageReplicatedMergeTree.cpp Row 2: ────── time: 2020-04-07 16:28:29 commit: 82a87bc0d2d change_type: Modify path: src/Storages/StorageReplicatedMergeTree.cpp old_path: 2 rows in set. Elapsed: 0.009 sec. Processed 122.88 thousand rows, 7.93 MB (13.46 million rows/s., 867.97 MB/s.)✎
虽然我们可以潜在地修改我们的查询以匹配 old_path 和 path 字段，如下所示，但这仅允许一次重命名。这个问题通常可以通过递归 CTE 来解决，但 ClickHouse 不支持递归 CTE。
path = 'src/Storages/StorageReplicatedMergeTree.cpp' OR old_path = 'src/Storages/StorageReplicatedMergeTree.cpp`
理想情况下，我们希望递归遍历完整的重命名层次结构并收集完整的文件列表。然后，我们可以简单地修改我们的限制，使其为 path IN (文件名集合)。为此，我们将使用 UDF。
注意：这个问题也可以在数据插入时解决。我们可以修改原始的 git-import 工具，在所有提交中添加权威的文件 ID，从而在所有提交中为我们提供一致的字段值。请体谅我们，假设我们不能这样做 :)
创建 UDF
在我们的案例中，我们需要递归行为，其中函数调用自身 - 每次传递之前的路径名并查找下一个重命名事件。
不幸的是，ClickHouse UDF 当前不支持递归。我们可以通过指定函数 N 次来解决此限制，其中每个函数调用另一个函数。虽然这会将我们限制为 N 的重命名深度，但这对于我们的用例来说应该足够了。下面我们创建了我们的第一个函数来显示一般结构
CREATE FUNCTION file_path_history AS n -> if(empty(n), [], arrayConcat([n], file_path_history_01(( SELECT if(empty(old_path), NULL, old_path) FROM git.file_changes WHERE (path = n) AND ((change_type = 'Rename') OR (change_type = 'Add')) LIMIT 1 ))))
我们的函数 file_path_history 接受感兴趣的文件名作为参数 n - 很可能是第一次调用时的当前已知路径。然后，此路径使用 arrayConcat 函数连接到当前结果，此外还通过 file_path_history_01 调用下一级别的 UDF（我们尚未定义此函数）。对于此函数，我们通过查询传递先前的文件名
SELECT if(empty(old_path), Null, old_path) FROM git.file_changes WHERE path = n AND (change_type = 'Rename' OR change_type = 'Add') LIMIT 1 ))))
以下函数 file_path_history_01 非常相似，不同之处在于它将接收用户指定的原始文件的旧路径。反过来，它会找到此文件的先前路径，调用 file_path_history_02。这种人为的递归会一直持续到我们达到最大深度（即，文件已被重命名超过五次）或从 SELECT 返回 No result（实际上是 Null）。
我们的完整函数定义如下所示。请注意，我们的最终函数是不同的，并提供了一个基本情况
CREATE FUNCTION file_path_history AS (n) -> if(empty(n), [], arrayConcat([n], file_path_history_01((SELECT if(empty(old_path), Null, old_path) FROM git.file_changes WHERE path = n AND (change_type = 'Rename' OR change_type = 'Add') LIMIT 1)))); CREATE FUNCTION file_path_history_01 AS (n) -> if(isNull(n), [], arrayConcat([n], file_path_history_02((SELECT if(empty(old_path), Null, old_path) FROM git.file_changes WHERE path = n AND (change_type = 'Rename' OR change_type = 'Add') LIMIT 1)))); CREATE FUNCTION file_path_history_02 AS (n) -> if(isNull(n), [], arrayConcat([n], file_path_history_03((SELECT if(empty(old_path), Null, old_path) FROM git.file_changes WHERE path = n AND (change_type = 'Rename' OR change_type = 'Add') LIMIT 1)))); CREATE FUNCTION file_path_history_03 AS (n) -> if(isNull(n), [], arrayConcat([n], file_path_history_04((SELECT if(empty(old_path), Null, old_path) FROM git.file_changes WHERE path = n AND (change_type = 'Rename' OR change_type = 'Add') LIMIT 1)))); CREATE FUNCTION file_path_history_04 AS (n) -> if(isNull(n), [], arrayConcat([n], file_path_history_05((SELECT if(empty(old_path), Null, old_path) FROM git.file_changes WHERE path = n AND (change_type = 'Rename' OR change_type = 'Add') LIMIT 1)))); CREATE FUNCTION file_path_history_05 AS (n) -> if(isNull(n), [], [n]);
使用我们的函数
我们现在可以像使用任何其他函数一样使用我们的函数 file_path_history。下面我们获取了 Replicated Merge Tree 的完整路径历史记录。
SELECT file_path_history('src/Storages/StorageReplicatedMergeTree.cpp') AS paths FORMAT Vertical Row 1: ────── paths: ['src/Storages/StorageReplicatedMergeTree.cpp', 'dbms/Storages/StorageReplicatedMergeTree.cpp', 'dbms/src/Storages/StorageReplicatedMergeTree.cpp'] 1 row in set. Elapsed: 0.041 sec. Processed 286.72 thousand rows, 20.11 MB (6.99 million rows/s., 490.36 MB/s.)
我们现在也可以在我们的原始查询中使用我们的函数来获取文件的完整提交历史记录并解决我们的原始问题。下面我们稍微修改了我们的查询，以便为每个文件名返回两个提交。
SELECT time, substring(commit_hash, 1, 11) AS commit, path FROM git.file_changes WHERE path IN file_path_history('src/Storages/StorageReplicatedMergeTree.cpp') ORDER BY time DESC LIMIT 2 BY path ┌────────────────time─┬─commit──────┬─path─────────────────────────────────────────────┐ │ 2022-10-30 16:30:51 │ c68ab231f91 │ src/Storages/StorageReplicatedMergeTree.cpp │ │ 2022-10-23 16:24:20 │ b40d9200d20 │ src/Storages/StorageReplicatedMergeTree.cpp │ │ 2020-04-03 15:21:24 │ 38a50f44d34 │ dbms/Storages/StorageReplicatedMergeTree.cpp │ │ 2020-04-02 17:11:10 │ 5b133dd1ce7 │ dbms/Storages/StorageReplicatedMergeTree.cpp │ │ 2020-04-01 19:21:27 │ 1d5a77c1132 │ dbms/src/Storages/StorageReplicatedMergeTree.cpp │ │ 2020-04-01 13:43:09 │ 46322370c00 │ dbms/src/Storages/StorageReplicatedMergeTree.cpp │ └─────────────────────┴─────────────┴──────────────────────────────────────────────────┘ 6 rows in set. Elapsed: 0.079 sec. Processed 552.77 thousand rows, 47.69 MB (6.96 million rows/s., 600.46 MB/s.)
结论
在这篇文章中，我们演示了如何使用用户定义函数来扩展 ClickHouse 的行为，以解决其他具有挑战性的查询。现在 ClickHouse Cloud 中已提供此功能，我们很乐意听取您对 UDF 的使用情况！
