简介
许多数据集是随着时间推移收集的，用于分析和发现有意义的趋势。通常，每个数据点都会分配一个收集日志或业务事件的时间。在分析阶段探索数据时，我们经常按不同的时间段进行切片或分组，以了解数据随时间的变化情况。任何以任何方式随时间变化的数据都是时间序列数据。ClickHouse 具有强大的工具来高效存储和处理时间序列数据，可用于简单的解决方案和数据发现，以及为 PB 级实时分析应用程序提供支持。
这篇博文基于我们用户日常需要执行的任务，提供了使用时间序列数据的技巧和窍门。我们涵盖了查询和常见数据类型问题（例如处理仪表），并探讨了随着规模扩展如何提高性能。
本文中的所有示例都可以在我们的 sql.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() 函数根据 bin 的数量自动生成自适应直方图，然后使用 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.)✎
我们只筛选了浏览量超过 1 万的页面。在结果集中，l 和 r 是 bin 的左右边界，w 是 bin 宽度（此 bin 中的项目计数）。
趋势
有时我们想通过计算连续值之间的差异来了解指标随时间的变化情况。让我们计算给定页面（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 个字节，并且允许我们存储最大值高达约 40 亿
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 已经具备了一切。
