博客 / 工程

在 ClickHouse 中生成随机数据

Denys Golotiuk

2022 年 12 月 29 日 - 17 分钟阅读

生成测试数据可能具有挑战性，因为真实世界的数据永远不是随机的。虽然 generateRandom() 函数作为快速填充表的方法很有用，但生成具有真实世界属性的数据将有助于在更真实的上下文中测试系统。真实数据具有独特的属性 - 一定的范围限制它，它倾向于特定值，并且永远不会随时间均匀分布。自 22.10 版本以来，ClickHouse 中添加了强大的函数，可以高度灵活地生成随机数据。让我们来看看其中的一些函数，并生成一些有用的测试数据！

本文中的所有示例都可以在我们的 play.clickhouse.com 环境中重现。或者，本文中的所有示例都是在 ClickHouse Cloud 中的开发者实例上创建的，您可以在其中在几分钟内启动免费试用集群，让我们处理基础设施并开始查询！

概率分布知识虽然有用，但对于使用本博文中的内容并非必不可少。大多数示例可以通过简单的复制和粘贴来重用。我们将首先介绍随机函数，每个函数都有一个简单的示例，然后再在一个组合示例中使用它们来生成实际有用的数据集。

在某些情况下，数据可以均匀分布，即数据点之间的间隔是恒定的。这些函数在 ClickHouse 中已经存在一段时间了，但对于具有可预测分布的列仍然有用。

ClickHouse 具有所有数据库和编程语言都具有的规范随机函数。此函数返回从 0（包括）到 1（不包括）均匀分布的伪随机值


SELECT randCanonical()

✎

要在给定范围内生成随机数（包括下限，不包括上限值），我们可以使用 randUniform


SELECT randUniform(5,10)

✎

此函数生成 `5...9.9(9)` 范围内的随机浮点数。`randUniform()` 函数使用均匀分布，这意味着在整个给定范围内，我们将看到相同数量的随机值（当我们多次调用该函数时）。换句话说 - 这为我们提供了给定范围内真正的随机数。

要生成随机整数，我们可以使用 floor() 函数进行舍入


SELECT floor(randUniform(5, 10)) AS r

✎

这会输出 5...9 范围内的随机数。

注意：由于均匀分布的性质，我们不能在此处使用 round()，因为最终我们会比 5 和 10（范围边缘）更频繁地获得 6 到 9（给定范围内的所有内容）的数字。

ClickHouse 的 22.10 版本提供了能够生成非均匀（和连续）分布的随机函数。非均匀分布意味着通过多次调用这样的函数，我们获得的一些随机数比其他随机数更频繁。生成的分布的性质是函数特定的。阅读更多关于非均匀分布及其常见应用的信息。

最流行的分布是正态分布，它由 randNormal() 函数实现


SELECT randNormal(100, 5)

✎

此函数将平均值作为第一个参数，将方差作为第二个参数，输出平均值附近的浮点数 - 在我们的示例中为 100。让我们看一下这些生成的数字是如何分布的


SELECT
    floor(randNormal(100, 5)) AS k,
    count(*) AS c,
    bar(c, 0, 50000, 100)
FROM numbers(100000) GROUP BY k ORDER BY k ASC

45 rows in set. Elapsed: 0.005 sec. Processed 130.82 thousand rows, 1.05 MB (24.44 million rows/s., 195.53 MB/s.)

✎

在这里，我们使用 randNormal() 生成 10 万个随机数，将它们舍入并计算每个数字出现的次数。我们看到，大多数时候，该函数将生成一个更接近给定平均值的随机数（这正是正态分布的工作方式）。

当我们对许多独立变量求和时，例如，聚合系统中的错误类型时，就会出现正态分布。其他可用的非均匀随机分布包括

`randBinomial()` 常用于模拟一系列是或否问题中成功的数量的概率。通常用于在模拟抛硬币时模拟总共正面朝上的次数。可视化时与正态分布相似。		SELECT floor(randBinomial(100, 0.85)) AS k, bar(count(*), 0, 50000, 100) AS b1 FROM numbers(100000) GROUP BY k ORDER BY k ASC ✎
`randNegativeBinomial()` 概念上类似于二项分布，但用于模拟实现特定二元事件的尝试次数，例如，在一系列中获得指定次数反面朝上所需的抛硬币次数。		SELECT floor(randNegativeBinomial(100, 0.85)) AS k, bar(count(*), 0, 50000, 100) AS b1 FROM numbers(100000) GROUP BY k ORDER BY k ASC ✎
`randLogNormal()` 右偏连续分布，通常用于模拟自然现象，例如故障率、游戏时长（例如，国际象棋）和收入分布。		SELECT floor(randLogNormal(1 / 100, 0.75)) AS k, bar(count(*), 0, 50000, 10) AS b1 FROM numbers(100000) GROUP BY k ORDER BY k ASC ✎
`randExponential()` 适用于模拟电话通话时长或客户销售总额。		SELECT floor(randExponential(1 / 2)) AS k, bar(count(*), 0, 50000, 10) AS b1 FROM numbers(100000) GROUP BY k ORDER BY k ASC ✎
`randChiSquared()` 这主要用于测试统计假设 - 特别是数据集是否与分布匹配。		SELECT floor(randChiSquared(10)) AS k, bar(count(*), 0, 10000, 10) AS b1 FROM numbers(100000) GROUP BY k ORDER BY k ASC ✎
`randStudentT()` 类似于正态分布，但具有更长的“尾部”。		SELECT floor(randStudentT(4.5)) AS k, bar(count(*), 0, 10000, 10) AS b1 FROM numbers(100000) GROUP BY k ORDER BY k ASC ✎
`randFisherF()` 主要用于统计测试，以评估两个总体的变异是否在其分布方面相同。		SELECT floor(randFisherF(3, 20)) AS k, bar(count(*), 0, 10000, 10) AS b1 FROM numbers(100000) GROUP BY k ORDER BY k ASC ✎
`randPoisson()` 可用于模拟一段时间内发生的某些事件的数量（例如，足球比赛中的进球数）或事件之间的间隔，例如日志消息。		SELECT floor(randPoisson(10)) AS k, bar(count(*), 0, 15000, 10) AS b1 FROM numbers(100000) GROUP BY k ORDER BY k ASC ✎
`randBernoulli()` 二元分布，适用于模拟特定操作的失败和成功。	┌─k─┬─────c─┐ │ 0 │ 24821 │ │ 1 │ 75179 │ └───┴───────┘	SELECT floor(randBernoulli(0.75)) AS k, count(*) as c FROM numbers(100000) GROUP BY k ORDER BY k ASC ✎

我们可以根据我们的要求使用任何给定的随机生成器，并使用测试数据填充我们的表。让我们填充一个表示产品销售的 purchases 表


CREATE TABLE purchases
(
    `dt` DateTime,
    `customer_id` UInt32,
    `total_spent` Float32
)
ENGINE = MergeTree
ORDER BY dt

我们将使用 randExponential() 函数为列 total_spent 生成数据，以模拟客户销售的分布


INSERT INTO purchases SELECT
    now() - randUniform(1, 1000000.),
    number,
    15 + round(randExponential(1 / 10), 2)
FROM numbers(1000000)

我们已将序列号用于客户 ID，并在时间上使用均匀随机偏移来分散数据。我们可以看到 total_spent 值根据指数定律分布，趋向于值 15（假设 $15.00 是可以花费的最低值）


SELECT
    floor(total_spent) AS s,
    count(*) AS n,
    bar(n, 0, 350000, 50)
FROM purchases
GROUP BY s
ORDER BY s ASC

✎

请注意我们如何使用指数分布来获得总支出的逐渐减少。我们可以使用正态分布（使用 randNormal() 函数）或任何其他分布来获得不同的峰值和形式。

虽然在我们之前的示例中，我们使用随机分布来模拟值，但我们也可以模拟时间。假设我们将客户端事件收集到下表中


CREATE TABLE events
(
    `dt` DateTime,
    `event` String
)
ENGINE = MergeTree
ORDER BY dt

实际上，更多事件可能发生在一天中的特定时间。泊松分布是模拟一系列随时间推移的独立事件的好方法。为了模拟时间的分布，我们只需将生成的随机值添加到时间列中


INSERT INTO events SELECT
    toDateTime('2022-12-12 12:00:00') - (((12 + randPoisson(12)) * 60) * 60),
    'click'
FROM numbers(100000)

0 rows in set. Elapsed: 0.014 sec. Processed 100.00 thousand rows, 800.00 KB (7.29 million rows/s., 58.34 MB/s.)

在这里，我们插入了大约 24 小时内分布的 10 万个点击事件，其中中午是事件高峰时间（在我们的示例中为 12:00）


SELECT
    toStartOfHour(dt) AS hour,
    count(*) AS c,
    bar(c, 0, 15000, 50)
FROM blogs.events
GROUP BY hour
ORDER BY hour ASC

750 rows in set. Elapsed: 0.095 sec. Processed 20.10 million rows, 80.40 MB (211.36 million rows/s., 845.44 MB/s.)

✎

在这种情况下，我们没有生成值，而是使用随机函数在计算的时间点插入新记录

在前一个示例的基础上，我们可以使用分布来生成依赖于时间的值。例如，假设我们要模拟硬件指标收集，例如 CPU 利用率或 RAM 使用率，到下表中


CREATE TABLE metrics
(
    `name` String,
    `dt` DateTime,
    `val` Float32
)
ENGINE = MergeTree
ORDER BY (name, dt)

在实际情况下，我们肯定会有高峰时段，届时我们的 CPU 将满负荷运转，并且会有负载较低的时段。为了对此进行建模，我们可以使用所需分布的随机函数来计算指标值和时间点值


INSERT INTO metrics SELECT
    'cpu',
    t + ((60 * 60) * randCanonical()) AS t,
    round(v * (0.95 + (randCanonical() / 20)), 2) AS v
FROM
(
    SELECT
        toDateTime('2022-12-12 12:00:00') - INTERVAL k HOUR
 AS t,
        round((100 * c) / m, 2) AS v
    FROM
    (
        SELECT
            k,
            c,
            max(c) OVER () AS m
        FROM
        (
            SELECT
                floor(randBinomial(24, 0.5) - 12) AS k,
                count(*) AS c
            FROM numbers(1000)
            GROUP BY k
            ORDER BY k ASC
        )
    )
) AS a
INNER JOIN numbers(1000000) AS b ON 1 = 1

0 rows in set. Elapsed: 3.952 sec. Processed 1.05 million rows, 8.38 MB (265.09 thousand rows/s., 2.12 MB/s.)

在这里，我们生成 1k 个二项分布随机值，以获得每个生成的数字及其关联的计数。然后，我们使用窗口最大函数计算这些值的最大值，并将其作为列添加到每个结果中。最后，在外部查询中，我们基于该计数除以最大值生成一个指标值，以获得 0...100 范围内的随机值，这对应于可能的 CPU 负载数据。我们还使用 randCanonical() 向 time 和 val 添加噪声，并在数字上加入以生成 100 万个指标事件。让我们检查一下我们的值是如何分布的


SELECT
    toStartOfHour(dt) AS h,
    round(avg(val), 2) AS v,
    bar(v, 0, 100)
FROM metrics
GROUP BY h
ORDER BY h ASC

✎

我们之前的所有示例都生成了具有单峰或最优值的数据。多峰分布包含多个峰值，可用于模拟真实世界的事件，例如销售的多个季节性峰值。我们可以通过按某个序列号对生成的值进行分组来重复我们生成的数据来实现此目的


SELECT
    floor(randBinomial(24, 0.75)) AS k,
    count(*) AS c,
    number % 3 AS ord,
    bar(c, 0, 10000)
FROM numbers(100000)
GROUP BY
    k,
    ord
ORDER BY
    ord ASC,
    k ASC

✎

这将重复我们的二项分布数据三次

这是一个聚合查询示例。稍后我们将再次使用这种方法，以便在“生成点击流测试数据”部分中实际将多模型分布数据插入到表中。

randBernoulli() 函数根据给定的概率返回 0 或 1，例如，如果我们想在 90% 的时间内获得 1，我们使用


SELECT randBernoulli(0.9)

✎

这在为二元状态生成数据时非常有用，例如失败或成功的事务


SELECT
    If(randBernoulli(0.95), 'success', 'failure') AS status,
    count(*) AS c
FROM numbers(1000)
GROUP BY status

┌─status──┬───c─┐
│ failure │  49 │
│ success │ 951 │
└─────────┴─────┘

2 rows in set. Elapsed: 0.004 sec. Processed 1.00 thousand rows, 8.00 KB (231.05 thousand rows/s., 1.85 MB/s.)

✎

在这里，我们生成了 95% 的 success 状态和仅 5% 的 failure。

我们可以结合使用数组和随机函数来从某个子集中获取值，并使用它来填充 ENUM 列


SELECT
    ['200', '404', '502', '403'][toInt32(randBinomial(4, 0.1)) + 1] AS http_code,
    count(*) AS c
FROM numbers(1000)
GROUP BY http_code

┌─http_code─┬───c─┐
│ 403       │   5 │
│ 502       │  43 │
│ 200       │ 644 │
│ 404       │ 308 │
└───────────┴─────┘

4 rows in set. Elapsed: 0.004 sec. Processed 1.00 thousand rows, 8.00 KB (224.14 thousand rows/s., 1.79 MB/s.)

✎

在这里，我们使用了二项分布来获取具有 4 个可能的 HTTP 响应代码之一的请求数。我们通常会期望 200 多于错误，因此这样建模。

ClickHouse 还允许使用 randomString()、randomStringUTF8() 和 randomPrintableASCII() 函数生成随机字符串。所有函数都接受字符串长度作为参数。要创建具有随机字符串的数据集，我们可以将字符串生成与随机函数结合起来，以获得任意长度的字符串。下面我们使用这种方法生成 10 个随机字符串，这些字符串由可读字符组成，长度为 5 到 25 个符号


SELECT
    randomPrintableASCII(randUniform(5, 25)) AS s,
    length(s) AS length
FROM numbers(10)

┌─s────────────────────┬─length─┐
│ (+x3e#Xc>VB~kTAtR|!  │     19 │
│ "ZRKa_               │      6 │
│ /$q4I/^_-)m;tSQ&yGq5 │     20 │
│ 2^5$2}6(H>dr         │     12 │
│ Gt.GO                │      5 │
│ 0WR4_6V1"N^/."DtB!   │     18 │
│ ^0[!uE               │      6 │
│ A&Ks|MZ+P^P^rd\      │     15 │
│ '-K}|@y$jw0z?@?m?S   │     18 │
│ eF(^"O&'^'           │     10 │
└──────────────────────┴────────┘
10 rows in set. Elapsed: 0.001 sec.

✎

在现实世界中，数据总是包含错误。这可以在 ClickHouse 中使用 fuzzBits() 函数进行模拟。此函数可以通过以指定的概率随机移动位来基于用户指定的有效值生成错误数据。假设我们要向字符串字段值添加错误。以下操作将基于我们的初始值随机生成错误


SELECT fuzzBits('Good string', 0.01)
FROM numbers(10)

┌─fuzzBits('Good string', 0.01)─┐
│ Good�string                    │
│ g/od string                   │
│ Goe string                    │
│ Good strhfg                   │
│ Good0string                   │
│ Good0spring                   │
│ Good string                   │
│ �ood string                    │
│ Good string                   │
│ Good string                   │
└───────────────────────────────┘

10 rows in set. Elapsed: 0.001 sec.

✎

请务必调整概率，因为生成的错误数量取决于您传递给函数的值的长度。对于获得较少错误的概率，请使用较低的值


SELECT
    IF(fuzzBits('Good string', 0.001) = 'Good string', 1, 0) AS has_errors,
    count(*)
FROM numbers(1000)
GROUP BY has_errors

┌─has_errors─┬─count()─┐
│          0 │     295 │
│          1 │     705 │
└────────────┴─────────┘

2 rows in set. Elapsed: 0.004 sec. Processed 1.00 thousand rows, 8.00 KB (276.99 thousand rows/s., 2.22 MB/s.)

✎

在这里，我们使用了 0.001 的概率来获得约 25% 的带有错误的值

为了总结所有内容，让我们模拟一个为期 30 天的点击流，该点击流在一天内具有接近真实世界的分布，中午达到峰值。我们将为此使用正态分布。每个事件还将具有两种可能的状态之一：success 或 fail，使用伯努利函数分布。我们的表


CREATE TABLE click_events
(
    `dt` DateTime,
    `event` String,
    `status` Enum8('success' = 1, 'fail' = 2)
)
ENGINE = MergeTree
ORDER BY dt

让我们用 1000 万个事件填充此表


INSERT INTO click_events SELECT
    (parseDateTimeBestEffortOrNull('12:00') - toIntervalHour(randNormal(0, 3))) - toIntervalDay(number % 30),
    'Click',
    ['fail', 'success'][randBernoulli(0.9) + 1]
FROM numbers(10000000)

0 rows in set. Elapsed: 3.726 sec. Processed 10.01 million rows, 80.06 MB (2.69 million rows/s., 21.49 MB/s.)

我们使用了成功概率为 90% 的 randBernoulli()，因此对于 status 列，我们将有十分之九的时间为 success 值。我们使用了 randNormal() 来生成事件的分布。让我们使用以下查询可视化该数据


SELECT
    dt,
    count(*) AS c,
    bar(c, 0, 100000)
FROM click_events
GROUP BY dt
ORDER BY dt ASC

722 rows in set. Elapsed: 0.045 sec. Processed 10.00 million rows, 40.00 MB (224.41 million rows/s., 897.64 MB/s.)

✎

这将产生以下输出