在ClickHouse中处理时序数据

ClickHouseInc 2023-10-25

本文字数：16975；估计阅读时间：43 分钟
审校：庄晓东（魏庄）

Meetup活动

首届ClickHouse官方Meetup活动报名中，11月4日在北京举行，立即扫码报名！

简介

许多数据集是随时间收集的，目的是为了分析和发现有意义的趋势。当我们收集日志或业务事件时，每个数据点通常都有一个指定的时间。在分析阶段探索我们的数据时，我们经常按照不同的时期进行切片或分组，以了解我们的数据如何随时间变化。任何随时间变化的数据都是时序数据。ClickHouse具有强大的工具，可以高效地存储和处理时序数据，并可用于简单的解决方案和数据发掘，以及支持PB级的实时分析应用。

本博客文章为处理时序数据提供了技巧和窍门，这些技巧都是基于用户执行的日常任务所总结的。我们涵盖了查询和常见的数据类型问题，例如如何处理仪表，并探讨了随着我们的规模扩大，如何提高性能。

ClickHouse中可用的日期和时间类型

ClickHouse有几种日期和时间类型。根据您的用例，可以应用不同的类型。在大多数情况下，使用Date类型的日期应该足够了。这种类型只需要2字节来存储一个日期，但范围限制为[1970-01-01, 2149-06-06]。DateTime允许存储到2106年的日期和时间。在需要更高精度的情况下，可以使用DateTime64。这允许存储精度可达纳秒的时间：

CREATE TABLE dates
(
    `date` Date,
    `datetime` DateTime,
    `precise_datetime` DateTime64(3),
    `very_precise_datetime` DateTime64(9)
)
ENGINE = MergeTree
ORDER BY tuple()

我们可以使用 now() 函数返回当前时间，使用 now64() 在第一个参数中以指定的精度获取它。

INSERT INTO dates SELECT NOW(), NOW(), NOW64(3), NOW64(9);

这将根据列类型相应地填充我们的列与时间：

SELECT * FROM dates


Row 1:
──────
date:                  2022-12-27
datetime:              2022-12-27 12:51:15
precise_datetime:      2022-12-27 12:51:15.329
very_precise_datetime: 2022-12-27 12:51:15.329098089

时区

在许多实际情况中，存储时区也是必要的。ClickHouse允许我们将时区设置为DateTime或DateTime64类型的最后一个参数：

CREATE TABLE dtz
(
    `id` Int8,
    `t` DateTime('Europe/Berlin')
)
ENGINE = MergeTree
ORDER BY tuple()

定义了DDL中的时区后，我们现在可以使用不同的时区插入时间：

INSERT INTO dtz SELECT 1, toDateTime('2022-12-12 12:13:14', 'America/New_York')


INSERT INTO dtz SELECT 2, toDateTime('2022-12-12 12:13:14')


SELECT * FROM dtz


┌─id─┬───────────────────t─┐
│  1 │ 2022-12-12 18:13:14 │
│  2 │ 2022-12-12 13:13:14 │
└────┴─────────────────────┘

注意，我们是如何插入 America/New_York 格式的时间，并在查询时自动转换为 Europe/Berlin 的。当未指定时区时，将使用服务器的本地时区。

查询

我们将利用Wikistat（维基百科pageviews数据）数据集探索ClickHouse的时序查询功能：

CREATE TABLE wikistat
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = MergeTree
ORDER BY (time)

让我们用10亿条记录填充这个表：

INSERT INTO wikistat SELECT *
FROM s3('https://ClickHouse-public-datasets.s3.amazonaws.com/wikistat/partitioned/wikistat*.native.zst') LIMIT 1e9


0 rows in set. Elapsed: 421.868 sec. Processed 2.00 billion rows, 100.89 GB (4.74 million rows/s., 239.15 MB/s.)

基于时间段进行聚合

最常见的需求是基于时段对数据进行聚合，例如获取每天的点击总数：

SELECT
    sum(hits) AS h,
    toDate(time) AS d
FROM wikistat
GROUP BY d
ORDER BY d ASC
LIMIT 5


┌────────h─┬──────────d─┐
│ 31045470 │ 2015-05-01 │
│ 30309226 │ 2015-05-02 │
│ 33704223 │ 2015-05-03 │
│ 34700248 │ 2015-05-04 │
│ 34634353 │ 2015-05-05 │
└──────────┴────────────┘


5 rows in set. Elapsed: 0.264 sec. Processed 1.00 billion rows, 12.00 GB (3.78 billion rows/s., 45.40 GB/s.)

我们在这里使用了toDate()函数，该函数将指定的时间转换为日期类型。或者，我们可以按小时批处理并过滤特定日期：

SELECT
    sum(hits) AS v,
    toStartOfHour(time) AS h
FROM wikistat
WHERE date(time) = '2015-05-01'
GROUP BY h
ORDER BY h ASC
LIMIT 5


┌───────v─┬───────────────────h─┐
│ 1199168 │ 2015-05-01 01:00:00 │
│ 1207276 │ 2015-05-01 02:00:00 │
│ 1189451 │ 2015-05-01 03:00:00 │
│ 1119078 │ 2015-05-01 04:00:00 │
│ 1037526 │ 2015-05-01 05:00:00 │
└─────────┴─────────────────────┘
5 rows in set. Elapsed: 0.013 sec. Processed 7.72 million rows, 92.54 MB (593.64 million rows/s., 7.12 GB/s.)

此处使用的 toStartOfHour() 函数将给定时间转换为小时的开始。ClickHouse有丰富的函数来生成几乎所有可能情况的时间周期，允许您轻松地按年、月、日、小时或甚至任意间隔（例如5分钟）进行group by。

自定义分组间隔

我们还可以使用toStartOfInterval()函数按自定义间隔分组。假设我们想按4小时的间隔进行分组：

SELECT
    sum(hits) AS v,
    toStartOfInterval(time, INTERVAL 4 HOUR) AS h
FROM wikistat
WHERE date(time) = '2015-05-01'
GROUP BY h
ORDER BY h ASC
LIMIT 6


┌───────v─┬───────────────────h─┐
│ 3595895 │ 2015-05-01 00:00:00 │
│ 4161080 │ 2015-05-01 04:00:00 │
│ 4602523 │ 2015-05-01 08:00:00 │
│ 6072107 │ 2015-05-01 12:00:00 │
│ 6604783 │ 2015-05-01 16:00:00 │
│ 6009082 │ 2015-05-01 20:00:00 │
└─────────┴─────────────────────┘


6 rows in set. Elapsed: 0.020 sec. Processed 7.72 million rows, 92.54 MB (386.78 million rows/s., 4.64 GB/s.)

使用 toStartOfInterval() 函数，我们使用INTERVAL子句设置所需的批处理周期。

填充空组

在很多情况下，我们处理的数据是稀疏的，有一些缺失的间隔。这会导致空桶。让我们看下面的例子，我们按1小时的间隔分组数据。这将显示以下统计数据，其中一些小时缺少值：

SELECT
    toStartOfHour(time) AS h,
    sum(hits)
FROM wikistat
WHERE (project = 'it') AND (subproject = 'm') AND (date(time) = '2015-06-12')
GROUP BY h
ORDER BY h ASC


┌───────────────────h─┬─sum(hits)─┐
│ 2015-06-12 00:00:00 │     16246 │
│ 2015-06-12 01:00:00 │      7900 │
│ 2015-06-12 02:00:00 │      4517 │
│ 2015-06-12 03:00:00 │      2982 │
│ 2015-06-12 04:00:00 │      2748 │
│ 2015-06-12 05:00:00 │      4581 │
│ 2015-06-12 06:00:00 │      8908 │
│ 2015-06-12 07:00:00 │     13514 │
│ 2015-06-12 08:00:00 │     18327 │
│ 2015-06-12 09:00:00 │     22541 │
│ 2015-06-12 10:00:00 │     25366 │
│ 2015-06-12 11:00:00 │     25383 │
│ 2015-06-12 12:00:00 │     29074 │ <- missing values
│ 2015-06-12 23:00:00 │     27199 │
└─────────────────────┴───────────┘


14 rows in set. Elapsed: 0.029 sec. Processed 6.98 million rows, 225.76 MB (237.19 million rows/s., 7.67 GB/s.)

ClickHouse提供了WITH FILL修饰符来解决这个问题。这将用零填充所有空小时，这样我们可以更好地了解随时间的分布：

SELECT
    toStartOfHour(time) AS h,
    sum(hits)
FROM wikistat
WHERE (project = 'it') AND (subproject = 'm') AND (date(time) = '2015-06-12')
GROUP BY h
ORDER BY h ASC WITH FILL STEP toIntervalHour(1)


┌───────────────────h─┬─sum(hits)─┐
│ 2015-06-12 00:00:00 │     16246 │
│ 2015-06-12 01:00:00 │      7900 │
│ 2015-06-12 02:00:00 │      4517 │
│ 2015-06-12 03:00:00 │      2982 │
│ 2015-06-12 04:00:00 │      2748 │
│ 2015-06-12 05:00:00 │      4581 │
│ 2015-06-12 06:00:00 │      8908 │
│ 2015-06-12 07:00:00 │     13514 │
│ 2015-06-12 08:00:00 │     18327 │
│ 2015-06-12 09:00:00 │     22541 │
│ 2015-06-12 10:00:00 │     25366 │
│ 2015-06-12 11:00:00 │     25383 │
│ 2015-06-12 12:00:00 │     29074 │
│ 2015-06-12 13:00:00 │         0 │
│ 2015-06-12 14:00:00 │         0 │
│ 2015-06-12 15:00:00 │         0 │
│ 2015-06-12 16:00:00 │         0 │
│ 2015-06-12 17:00:00 │         0 │
│ 2015-06-12 18:00:00 │         0 │
│ 2015-06-12 19:00:00 │         0 │
│ 2015-06-12 20:00:00 │         0 │
│ 2015-06-12 21:00:00 │         0 │
│ 2015-06-12 22:00:00 │         0 │
│ 2015-06-12 23:00:00 │     27199 │
└─────────────────────┴───────────┘


24 rows in set. Elapsed: 0.039 sec. Processed 6.98 million rows, 225.76 MB (180.92 million rows/s., 5.85 GB/s.)

滚动时间窗口

有时，我们不想处理间隔的开始（例如一天的开始或一个小时的开始），而是窗口间隔。假设我们想了解一个窗口的总点击次数，不是基于天数，而是基于下午6点偏移的24小时周期。我们使用date_diff()函数来计算基点时间和每条记录的时间之间的差异。在这种情况下，d 列将代表天数的差异（例如，1天前，2天前等）：

SELECT
    sum(hits),
    dateDiff('day', toDateTime('2015-05-01 18:00:00'), time) AS d
FROM wikistat
GROUP BY d
ORDER BY d ASC
LIMIT 5


┌─sum(hits)─┬─d─┐
│  31045470 │ 0 │
│  30309226 │ 1 │
│  33704223 │ 2 │
│  34700248 │ 3 │
│  34634353 │ 4 │
└───────────┴───┘


5 rows in set. Elapsed: 0.283 sec. Processed 1.00 billion rows, 12.00 GB (3.54 billion rows/s., 42.46 GB/s.)

快速视觉分析

ClickHouse提供了bar()函数，用于构建快速的视觉效果并帮助数据分析。这将快速地以页面浏览量的形式可视化最受欢迎和最不受欢迎的小时数：

SELECT
    toHour(time) AS h,
    sum(hits) AS t,
    bar(t, 0, max(t) OVER (), 50) AS bar
FROM wikistat
GROUP BY h
ORDER BY h ASC


┌──h─┬─────────t─┬─bar────────────────────────────────────────────────┐
│  0 │ 146208847 │ ██████████████████████████████████████▋            │
│  1 │ 143713140 │ █████████████████████████████████████▊             │
│  2 │ 144977675 │ ██████████████████████████████████████▎            │
│  3 │ 145089174 │ ██████████████████████████████████████▎            │
│  4 │ 139602368 │ ████████████████████████████████████▊              │
│  5 │ 130795734 │ ██████████████████████████████████▌                │
│  6 │ 126456113 │ █████████████████████████████████▍                 │
│  7 │ 127328312 │ █████████████████████████████████▋                 │
│  8 │ 131772449 │ ██████████████████████████████████▋                │
│  9 │ 137695533 │ ████████████████████████████████████▍              │
│ 10 │ 143381876 │ █████████████████████████████████████▊             │
│ 11 │ 146690963 │ ██████████████████████████████████████▋            │
│ 12 │ 155662847 │ █████████████████████████████████████████▏         │
│ 13 │ 169130249 │ ████████████████████████████████████████████▋      │
│ 14 │ 182213956 │ ████████████████████████████████████████████████▏  │
│ 15 │ 188534642 │ █████████████████████████████████████████████████▋ │
│ 16 │ 189214224 │ ██████████████████████████████████████████████████ │
│ 17 │ 186824967 │ █████████████████████████████████████████████████▎ │
│ 18 │ 185885433 │ █████████████████████████████████████████████████  │
│ 19 │ 186112653 │ █████████████████████████████████████████████████▏ │
│ 20 │ 187530882 │ █████████████████████████████████████████████████▌ │
│ 21 │ 185485979 │ █████████████████████████████████████████████████  │
│ 22 │ 175522556 │ ██████████████████████████████████████████████▍    │
│ 23 │ 157537595 │ █████████████████████████████████████████▋         │
└────┴───────────┴────────────────────────────────────────────────────┘


24 rows in set. Elapsed: 0.264 sec. Processed 1.00 billion rows, 12.00 GB (3.79 billion rows/s., 45.53 GB/s.)

请注意，我们如何使用窗口max()来计算每小时的最大点击次数，并将其传递给bar()函数进行可视化。

计数器和仪表度量

当我们处理时间序列时，我们遇到两种基本类型的指标：

计数器用于计算按属性切分并按时间帧分组的跟踪事件的总数。
这里的一个流行示例是跟踪网站访问者。
仪表用于设置一个倾向于随时间变化的指标值。
这里的一个好例子是跟踪CPU负载。

在ClickHouse中，这两种度量类型都很容易使用，不需要任何额外的配置。计数器可以使用 count() 或 sum() 函数轻松查询，具体取决于存储策略。为了有效地查询仪表，可以使用 any() 聚合函数，结合INTERPOLATE修饰符填充任何缺失的数据点：

CREATE TABLE metrics
( `time` DateTime, `name` String, `value` UInt32 )
ENGINE = MergeTree ORDER BY tuple();


INSERT INTO metrics VALUES
('2022-12-28 06:32:16', 'cpu', 7), ('2022-12-28 14:31:22', 'cpu', 50), ('2022-12-28 14:30:30', 'cpu', 25), ('2022-12-28 14:25:36', 'cpu', 10), ('2022-12-28 11:32:08', 'cpu', 5), ('2022-12-28 10:32:12', 'cpu', 5);


SELECT
    toStartOfHour(time) AS h,
    any(value) AS v
FROM metrics
GROUP BY h
ORDER BY h ASC WITH FILL STEP toIntervalHour(1)
INTERPOLATE ( v AS v )


┌───────────────────h─┬──v─┐
│ 2022-12-28 06:00:00 │  7 │
│ 2022-12-28 07:00:00 │  7 │ <- filled
│ 2022-12-28 08:00:00 │  7 │ <- filled
│ 2022-12-28 09:00:00 │  7 │ <- filled
│ 2022-12-28 10:00:00 │  5 │
│ 2022-12-28 11:00:00 │  5 │ <- filled
│ 2022-12-28 12:00:00 │  5 │ <- filled
│ 2022-12-28 13:00:00 │  5 │ <- filled
│ 2022-12-28 14:00:00 │ 50 │
└─────────────────────┴────┘

在这种情况下，突出显示的值是由ClickHouse自动填充的，以遵循仪表的指标在时间范围内连续的性质。

直方图

时间序列数据的另一个流行案例是基于跟踪事件构建直方图。假设我们想了解基于其总点击数的页面数量的分布情况。我们可以使用histogram()函数自动生成基于箱数的自适应直方图，然后使用arrayJoin()和bar()进行可视化：

WITH histogram(10)(hits) AS h
SELECT
    round(arrayJoin(h).1) AS l,
    round(arrayJoin(h).2) AS u,
    arrayJoin(h).3 AS w,
    bar(w, 0, max(w) OVER (), 20) AS b
FROM
(
    SELECT
        path,
        sum(hits) AS hits
    FROM wikistat
    WHERE date(time) = '2015-06-15'
    GROUP BY path
    HAVING hits > 10000.
)


┌───────l─┬───────u─┬──────w─┬─b────────────────────┐
│   10034 │   27769 │ 84.375 │ ████████████████████ │
│   27769 │   54281 │  19.75 │ ████▋                │
│   54281 │   79020 │  3.875 │ ▊                    │
│   79020 │   96858 │   2.75 │ ▋                    │
│   96858 │  117182 │   1.25 │ ▎                    │
│  117182 │  173244 │      1 │ ▏                    │
│  173244 │  232806 │  1.125 │ ▎                    │
│  232806 │  405693 │   1.75 │ ▍                    │
│  405693 │ 1126826 │  1.125 │ ▎                    │
│ 1126826 │ 1691188 │      1 │ ▏                    │
└─────────┴─────────┴────────┴──────────────────────┘


10 rows in set. Elapsed: 0.134 sec. Processed 6.64 million rows, 268.25 MB (49.48 million rows/s., 2.00 GB/s.)

我们过滤了浏览量超过10k的页面。在结果集中， l 和 r 是箱的左右边界，而 w 是一个箱的宽度(这个箱中的项目数)。

趋势

有时我们想通过计算连续值之间的差异来了解指标随时间的变化。让我们计算给定页面（ path 列）的每日点击次数以及与前一天的此值的变化：

SELECT
    toDate(time) AS d,
    sum(hits) AS h,
    lagInFrame(h) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS p,
    h - p AS trend
FROM wikistat
WHERE path = 'Ana_Sayfa'
GROUP BY d
ORDER BY d ASC
LIMIT 15


┌──────────d─┬──────h─┬──────p─┬──trend─┐
│ 2015-05-01 │ 214612 │      0 │ 214612 │
│ 2015-05-02 │ 211546 │ 214612 │  -3066 │
│ 2015-05-03 │ 221412 │ 211546 │   9866 │
│ 2015-05-04 │ 219940 │ 221412 │  -1472 │
│ 2015-05-05 │ 211548 │ 219940 │  -8392 │
│ 2015-05-06 │ 212358 │ 211548 │    810 │
│ 2015-05-07 │ 208150 │ 212358 │  -4208 │
│ 2015-05-08 │ 208871 │ 208150 │    721 │
│ 2015-05-09 │ 210753 │ 208871 │   1882 │
│ 2015-05-10 │ 212918 │ 210753 │   2165 │
│ 2015-05-11 │ 211884 │ 212918 │  -1034 │
│ 2015-05-12 │ 212314 │ 211884 │    430 │
│ 2015-05-13 │ 211192 │ 212314 │  -1122 │
│ 2015-05-14 │ 206172 │ 211192 │  -5020 │
│ 2015-05-15 │ 195832 │ 206172 │ -10340 │
└────────────┴────────┴────────┴────────┘


15 rows in set. Elapsed: 0.550 sec. Processed 1.00 billion rows, 28.62 GB (1.82 billion rows/s., 52.00 GB/s.)

我们使用了lagInFrame()窗口函数来获得前一个 hits 值，然后使用此值来计算差异作为 trend 列。

累积值

接上一个例子，有时我们希望做相反的事情 - 随着时间获得某些指标的累积总和。这通常用于计数器，以可视化累积增长，并可以使用窗口函数轻松实现：

SELECT
    toDate(time) AS d,
    sum(hits) AS h,
    sum(h) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) AS c,
    bar(c, 0, 3200000, 25) AS b
FROM wikistat
WHERE path = 'Ana_Sayfa'
GROUP BY d
ORDER BY d ASC
LIMIT 15


┌──────────d─┬──────h─┬───────c─┬─b─────────────────────────┐
│ 2015-05-01 │ 214612 │  214612 │ █▋                        │
│ 2015-05-02 │ 211546 │  426158 │ ███▎                      │
│ 2015-05-03 │ 221412 │  647570 │ █████                     │
│ 2015-05-04 │ 219940 │  867510 │ ██████▋                   │
│ 2015-05-05 │ 211548 │ 1079058 │ ████████▍                 │
│ 2015-05-06 │ 212358 │ 1291416 │ ██████████                │
│ 2015-05-07 │ 208150 │ 1499566 │ ███████████▋              │
│ 2015-05-08 │ 208871 │ 1708437 │ █████████████▎            │
│ 2015-05-09 │ 210753 │ 1919190 │ ██████████████▊           │
│ 2015-05-10 │ 212918 │ 2132108 │ ████████████████▋         │
│ 2015-05-11 │ 211884 │ 2343992 │ ██████████████████▎       │
│ 2015-05-12 │ 212314 │ 2556306 │ ███████████████████▊      │
│ 2015-05-13 │ 211192 │ 2767498 │ █████████████████████▌    │
│ 2015-05-14 │ 206172 │ 2973670 │ ███████████████████████▏  │
│ 2015-05-15 │ 195832 │ 3169502 │ ████████████████████████▋ │
└────────────┴────────┴─────────┴───────────────────────────┘


15 rows in set. Elapsed: 0.557 sec. Processed 1.00 billion rows, 28.62 GB (1.80 billion rows/s., 51.40 GB/s.)

我们构建了累积的每日点击总和，并在15天的时间内可视化了给定页面的增长。

速率

当我们处理时间序列时，计算度量的速率（每时间单位的速度）也很受欢迎。假设我们想按小时分组，获取给定日期的某个页面每秒的点击率：

SELECT
    toStartOfHour(time) AS t,
    sum(hits) AS h,
    round(h  (60 * 60), 2) AS rate,
    bar(rate * 10, 0, max(rate * 10) OVER (), 25) AS b
FROM wikistat
WHERE path = 'Ana_Sayfa'
GROUP BY t
ORDER BY t ASC
LIMIT 23


┌───────────────────t─┬─────h─┬─rate─┬─b───────────────────────┐
│ 2015-05-01 01:00:00 │  6749 │ 1.87 │ ████████████▊           │
│ 2015-05-01 02:00:00 │  6054 │ 1.68 │ ███████████▋            │
│ 2015-05-01 03:00:00 │  5823 │ 1.62 │ ███████████▏            │
│ 2015-05-01 04:00:00 │  5908 │ 1.64 │ ███████████▎            │
│ 2015-05-01 05:00:00 │  6131 │  1.7 │ ███████████▋            │
│ 2015-05-01 06:00:00 │  7067 │ 1.96 │ █████████████▌          │
│ 2015-05-01 07:00:00 │  8169 │ 2.27 │ ███████████████▋        │
│ 2015-05-01 08:00:00 │  9526 │ 2.65 │ ██████████████████▎     │
│ 2015-05-01 09:00:00 │ 10474 │ 2.91 │ ████████████████████▏   │
│ 2015-05-01 10:00:00 │ 10389 │ 2.89 │ ████████████████████    │
│ 2015-05-01 11:00:00 │  9830 │ 2.73 │ ██████████████████▊     │
│ 2015-05-01 12:00:00 │ 10712 │ 2.98 │ ████████████████████▋   │
│ 2015-05-01 13:00:00 │ 10301 │ 2.86 │ ███████████████████▋    │
│ 2015-05-01 14:00:00 │ 10181 │ 2.83 │ ███████████████████▌    │
│ 2015-05-01 15:00:00 │ 10324 │ 2.87 │ ███████████████████▊    │
│ 2015-05-01 16:00:00 │ 10497 │ 2.92 │ ████████████████████▏   │
│ 2015-05-01 17:00:00 │ 10676 │ 2.97 │ ████████████████████▌   │
│ 2015-05-01 18:00:00 │ 11121 │ 3.09 │ █████████████████████▍  │
│ 2015-05-01 19:00:00 │ 11277 │ 3.13 │ █████████████████████▋  │
│ 2015-05-01 20:00:00 │ 11553 │ 3.21 │ ██████████████████████▏ │
│ 2015-05-01 21:00:00 │ 11637 │ 3.23 │ ██████████████████████▎ │
│ 2015-05-01 22:00:00 │ 11298 │ 3.14 │ █████████████████████▋  │
│ 2015-05-01 23:00:00 │  8915 │ 2.48 │ █████████████████▏      │
└─────────────────────┴───────┴──────┴─────────────────────────┘


23 rows in set. Elapsed: 0.572 sec. Processed 1.00 billion rows, 28.62 GB (1.75 billion rows/s., 50.06 GB/s.)

提高时间序列存储效率

类型优化

优化存储效率的一般方法是使用最佳的数据类型。我们看一下 project 和 subprojects 列。这些列的类型为String，但唯一值的数量相对较少：

SELECT
    uniq(project),
    uniq(subproject)
FROM wikistat


┌─uniq(project)─┬─uniq(subproject)─┐
│          1095 │               99 │
└───────────────┴──────────────────┘


1 row in set. Elapsed: 0.895 sec. Processed 1.00 billion rows, 20.43 GB (1.12 billion rows/s., 22.84 GB/s.)

这意味着我们可以使用LowCardinality()数据类型，该类型使用基于字典的编码。这导致ClickHouse存储内部值ID而不是原始字符串值，从而节省了大量空间：

ALTER TABLE wikistat
    MODIFY COLUMN `project` LowCardinality(String),
    MODIFY COLUMN `subproject` LowCardinality(String)

我们还为 hits 列使用了UInt64类型，它需要8字节，但最大值相对较小：

SELECT max(hits)
FROM wikistat


┌─max(hits)─┐
│    237913 │
└───────────┘

鉴于这个值，我们可以改用只需要4字节的 UInt32 ，它允许我们存储高达约40亿的最大值：

ALTER TABLE wikistat
MODIFY COLUMN `hits` UInt32

这将至少将此列在内存中的大小减少2倍。请注意，由于压缩，磁盘上的大小将保持不变。但要小心，选择不太小的数据类型！

用于优化序列存储的编解码器

当我们处理顺序数据时，实际上时间序列数据也是这样，我们可以使用特殊的编解码器(CODEC)进一步提高存储效率。一般的想法是存储值之间的变化，而不是绝对值本身，这导致处理缓慢变化的数据时所需的空间要少得多：

ALTER TABLE wikistat
MODIFY COLUMN `time` CODEC(Delta, ZSTD)

我们为time列使用了Delta编解码器，它最适合时间序列数据。正确的排序键也可以节省磁盘空间。由于我们通常希望按path过滤，我们还应将其添加到主键中。这需要重新创建表。让我们总结一下，并比较有和没有优化类型的存储效率：

Unoptimized table

CREATE TABLE wikistat
(
    `time` DateTime,
    `project` String,
    `subproject` String,
    `path` String,
    `hits` UInt64
)
ENGINE = MergeTree
ORDER BY (time)

Optimized table

CREATE TABLE optimized_wikistat
(
    `time` DateTime CODEC(Delta(4), ZSTD(1)),
    `project` LowCardinality(String),
    `subproject` LowCardinality(String),
    `path` String,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY (path, time)

优化后的表存储量仅为1/10，而实际的数据没有损失。有关使用类型和编解码器优化存储的更多细节，请参阅我们后续的博客《使用Schemas和Codecs优化ClickHouse》。

提高时间序列查询性能

优化ORDER BY键

在尝试其他优化之前，用户应优化他们的排序键，以确保ClickHouse产生最快的可能结果。选择正确的键主要取决于你打算运行的查询。假设我们的大多数查询都按 project 和 subproject 列进行过滤。在这种情况下，将它们添加到排序键是个好主意 - 以及 time 列，因为我们也在时间上进行查询：

CREATE TABLE optimized_wikistat
(…)
ENGINE = MergeTree
ORDER BY (project, subproject, time)

现在，让我们比较多个查询，以了解我们的排序键表达式对性能的重要性。请注意，我们还应用了之前的数据类型和codec优化：

Query	Ordering Key (time)	Ordering Key (project, subproject, time)
SELECT project, sum(hits) AS h FROM wikistat GROUP BY project ORDER BY h DESC LIMIT 10	0.518 sec	0.258 sec
SELECT subproject, sum(hits) AS h FROM wikistat WHERE project = 'it' GROUP BY subproject ORDER BY h DESC LIMIT 10	0.67 sec	0.025 sec
SELECT toStartOfMonth(time) AS m, sum(hits) AS h FROM wikistat WHERE (project = 'it') AND (subproject = 'zero') GROUP BY m ORDER BY m DESC LIMIT 10	0.65 sec	0.014 sec
SELECT path, sum(hits) AS h FROM wikistat WHERE (project = 'it') AND (subproject = 'zero') GROUP BY path ORDER BY h DESC LIMIT 10	0.148 sec	0.010 sec

注意，我们通过选择更合适的排序键，实现了2…40倍的性能提升。有关选择主键的更多细节，包括如何决定列的顺序，请阅读官网关于主键的介绍。

物化视图

另一个选择是使用物化视图来汇总并存储热门查询的结果。这些结果可以代替原始表来查询。假设我们的情况下经常执行以下查询：

SELECT
    path,
    SUM(hits) AS v
FROM wikistat
WHERE toStartOfMonth(time) = '2015-05-01'
GROUP BY path
ORDER BY v DESC
LIMIT 10


┌─path──────────────────┬────────v─┐
│ -                     │ 89742164 │
│ Angelsberg            │ 19191582 │
│ Ana_Sayfa             │  6376578 │
│ Academy_Awards        │  4901470 │
│ Accueil_(homonymie)   │  3810047 │
│ 2015_in_spaceflight   │  2077195 │
│ Albert_Einstein       │  1621175 │
│ 19_Kids_and_Counting  │  1432484 │
│ 2015_Nepal_earthquake │  1406457 │
│ Alive                 │  1390624 │
└───────────────────────┴──────────┘


10 rows in set. Elapsed: 1.016 sec. Processed 256.84 million rows, 10.17 GB (252.69 million rows/s., 10.01 GB/s.)

我们可以创建以下物化视图：

CREATE MATERIALIZED VIEW blogs.wikistat_top
ENGINE = SummingMergeTree
ORDER BY (month, hits) POPULATE AS
SELECT
    path,
    toStartOfMonth(time) AS month,
    sum(hits) AS hits
FROM blogs.wikistat
GROUP BY
    path,
    month


0 rows in set. Elapsed: 8.589 sec. Processed 1.00 billion rows, 40.52 GB (116.43 million rows/s., 4.72 GB/s.)

现在，我们可以查询物化视图而不是原始表：

SELECT
    path,
    hits
FROM wikistat_top
WHERE month = '2015-05-01'
ORDER BY hits DESC
LIMIT 10


┌─path──────────────────┬─────hits─┐
│ -                     │ 89742164 │
│ Angelsberg            │ 19191582 │
│ Ana_Sayfa             │  6376578 │
│ Academy_Awards        │  4901470 │
│ Accueil_(homonymie)   │  3810047 │
│ 2015_in_spaceflight   │  2077195 │
│ Albert_Einstein       │  1621175 │
│ 19_Kids_and_Counting  │  1432484 │
│ 2015_Nepal_earthquake │  1406457 │
│ Alive                 │  1390624 │
└───────────────────────┴──────────┘
10 rows in set. Elapsed: 0.005 sec. Processed 24.58 thousand rows, 935.16 KB (5.26 million rows/s., 200.31 MB/s.)

此处的性能提升显著。有关物化视图的介绍请参考之前的文章。

扩展时间序列

ClickHouse在存储和查询上都很高效，并且可以轻松地扩展到PB级别，同时保持相同的性能和简单性。在未来的文章中，我们将探索使用完整的Wikistat数据集扩展到近4000亿行的技术。我们将展示如何使用我们的服务，在存储和处理能力方面进行扩展，该服务将存储和计算分开，并自动处理，或使用手动集群解决方案。

总结

在这篇文章中，我们展示了如何使用SQL的功能和ClickHouse的性能，有效地存储和查询时间序列数据。鉴于此，您不需要安装其他扩展或工具来收集和处理时间序列，因为ClickHouse已经完全具备相关能力。

Meetup 活动报名通知

好消息：ClickHouse Beijing User Group第1届 Meetup 已经开放报名了，将于11月4日在北京世纪金源大饭店第九会议室人举行，扫码免费报名

clickhouse

文章转载自ClickHouseInc，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

在ClickHouse中处理时序数据

评论