介绍
许多数据集是随着时间的推移收集的，以便分析和发现有意义的趋势。每个数据点通常在收集日志或业务事件时被分配一个时间。在分析阶段探索数据时，我们通常会根据不同的时间段进行切片或分组，以了解数据如何随时间变化。任何以任何方式随时间变化的数据都是 **时间序列数据**。ClickHouse 拥有强大的工具来高效地存储和处理时间序列数据，可用于简单的解决方案和数据发现，以及为 PB 级别的实时分析应用程序提供支持。
这篇博文提供了处理时间序列数据的技巧和窍门，这些技巧和窍门是基于我们看到用户需要执行的日常任务。我们涵盖了查询和常见的 数据类型 问题，例如处理仪表盘，并探讨了如何在扩展时提高性能。
本文中的所有示例都可以在我们的 play.clickhouse.com 环境中复制（请参阅 blogs 数据库）。或者，如果您想更深入地研究此数据集，ClickHouse Cloud 是一个不错的起点——使用免费试用版启动一个集群，加载数据，让我们处理基础设施，然后开始查询吧！
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（维基百科页面浏览量数据）数据集来探索 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 分钟）轻松分组。
自定义分组间隔
我们还可以使用 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 │ └───────────┘✎
鉴于此值，我们可以使用 `UInt32`，它只占用 4 个字节，并且允许我们存储高达 ~4b 的最大值。
ALTER TABLE wikistat MODIFY COLUMN `hits` UInt32
这将使此列的内存大小至少减少 2 倍。请注意，由于压缩，磁盘上的大小将保持不变。但要小心，选择不要太小的数据类型！
用于优化序列存储的编解码器
当我们处理顺序数据时，时间序列数据实际上就是顺序数据，我们可以通过使用 特殊编解码器 来进一步提高存储效率。基本思想是存储值之间的变化而不是绝对值本身，这在处理缓慢变化的数据时需要更少的空间。
ALTER TABLE wikistat MODIFY COLUMN `time` CODEC(Delta, ZSTD)
我们对 `time` 列使用了 Delta 编解码器，它最适合时间序列数据。 正确的排序键 也可以节省磁盘空间。由于我们通常希望按路径进行过滤，因此我们还应将其添加到键中。这需要重新创建表。让我们将所有内容都封装起来，并比较使用和不使用优化类型的存储效率。
如我们所见，我们在没有任何数据实际丢失的情况下将存储优化了十倍。有关使用类型和编解码器优化存储的更多详细信息，请查看我们最近的博客 使用模式和编解码器优化 ClickHouse。
提高时间序列查询性能
优化 ORDER BY 键
在尝试其他优化之前，用户应优化其 排序键，以确保 ClickHouse 生成最快可能的結果。正确的键选择很大程度上取决于您将要运行的查询。假设我们的大多数查询都按 `project` 和 `subproject` 列进行过滤。在这种情况下，将它们添加到排序键中是一个好主意，以及 `time` 列，因为我们也按时间进行查询。
CREATE TABLE optimized_wikistat (…) ENGINE = MergeTree ORDER BY (project, subproject, time)
现在，让我们比较多个查询，以了解排序键表达式对性能有多重要。请注意，我们还应用了之前的数据类型和编解码器优化。
请注意，通过选择更合适的排序键，我们的性能提升了 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 已经具备了所有必要的功能。
