DoubleCloud 即将停止运营。利用限时免费迁移服务将数据迁移到 ClickHouse。立即联系我们 ->->

博客 / 工程

在 ClickHouse 中生成随机数据

Denys Golotiuk

2022 年 12 月 29 日

生成测试数据可能具有挑战性，因为现实世界中的数据从不随机。虽然 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()，因为我们最终会更频繁地获得 6 到 9 之间的数字（在给定范围内），而不是 5 和 10（范围边缘）。

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() 生成 100k 个随机数，将其舍入并计算每个数字出现的次数。我们看到，大多数情况下，该函数将生成更接近给定平均值的随机数（这正是正态分布的工作方式）。

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

`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.)

在这里，我们插入 100k 个点击事件，这些事件大约分布在 24 小时的时间段内，中午是事件高峰时间（在我们的示例中为 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 状态。

我们可以组合使用数组和随机函数，从特定子集中获取值，并将其用于填充枚举列


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 值将占 10 次中的 9 次。我们使用 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.)

✎

这将产生以下输出

自 22.10 版本以来，ClickHouse 提供了功能强大的随机函数，我们已经演示了如何使用这些函数来生成具有现实性质的数据。这些数据可用于帮助您在接近现实世界的数据上测试解决方案，而不是无关的生成集。

分享此文章

订阅我们的时事通讯

及时了解功能发布、产品路线图、支持和云服务！

正在加载表单...

博客 / 工程

在 ClickHouse 中生成随机数据

介绍

均匀随机分布

0…1 范围内的规范随机数

X…Y 范围内的随机数

随机整数

非均匀随机分布

生成随机数据

生成时间分布数据

生成时间相关的值

模拟二元状态

为枚举生成随机值

生成随机字符串

生成噪声数据

生成真实数据集

摘要

订阅我们的时事通讯

最近文章

博客 / 工程

在 ClickHouse 中生成随机数据

介绍

均匀随机分布

0…1 范围内的规范随机数

X…Y 范围内的随机数

随机整数

非均匀随机分布

生成随机数据

生成时间分布数据

生成时间相关的值

生成多峰分布

模拟二元状态

为枚举生成随机值

生成随机字符串

生成噪声数据

生成真实数据集

摘要

订阅我们的时事通讯

最近文章