这篇文章继续我们的小系列文章，我们将在其中重点介绍酷炫的查询，并分享与 ClickHouse 相关的有趣技巧和窍门。
有兴趣在 ClickHouse 云中尝试 UDF 吗？立即开始，享受 30 天内 300 美元的免费赠送额度。
简介
今天，我们将重点关注使用 ClickHouse 附带的 git-import 工具捕获的 Git 数据对 ClickHouse 存储库进行分析得到的查询。该查询使用了 ClickHouse 的一项强大功能：SQL 用户定义函数，我们最近在 ClickHouse 云中提供了该功能！
对于这篇文章，我们将创建一个查询，以显示 ClickHouse 存储库中文件的完整提交历史记录。此查询将使用 UDF 来允许文件重命名。我们的最终解决方案已包含在文档中，我们欢迎您改进。
这篇文章中的所有示例都可以在我们的 play.clickhouse.com 环境中重现（请参阅 git_clickhouse 数据库）。或者，如果您想更深入地了解此数据集，ClickHouse 云 是一个很好的起点，现在支持 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 (set of filenames) 即可。为此，我们将使用 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 未返回任何结果（实际上是 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 云中可用，我们很乐意听取您对 UDF 的使用情况！
