开发任何产品，无论是视频服务还是磁带，故事或文章，我都希望能够衡量用户的条件“幸福感”。 要了解我们是在做出更好还是更坏的更改，请调整产品开发的方向，而不是凭直觉和我们自己的感觉，而要依靠您可以相信的指标和数字。
在本文中，我将告诉您我们如何在每月接收9700万观众的服务上启动产品统计和分析，同时又收到了非常高性能的分析查询。 我们将讨论ClickHouse，使用的引擎和查询的功能。 我将描述一种数据聚合方法，该方法使我们能够在一瞬间获得复杂的指标，并讨论数据转换和测试。
现在，我们每天大约有60亿个食品事件，在不久的将来，我们将达到20–250亿。 然后-并非以如此快的速度，到今年年底，当我们描述所有我们感兴趣的食品事件时，我们将增长到40-500亿。
设置1行。 耗时：0.287秒。 已处理598.5亿行，59.85 GB（2081.6亿行/秒，208.16 GB /秒）细节剪下。
前言
分析工具以前是VKontakte。 考虑到唯一用户，可以按片段构建事件计划，从而落入服务的深度。 但是，这是一个预先确定的问题，汇总的数据，HLL的唯一问题，有些僵化和无法快速回答比“多少？”更复杂的问题。
当然，hadoop已经存在，已经存在并且将要存在，它也已经被编写，编写并且将要编写很多，很多使用服务的日志。 不幸的是，只有某些团队使用hdfs来实现自己的任务。 更可悲的是，hdfs并不是关于快速分析查询的：许多领域都存在问题，必须在代码中找到答案，而不是在所有人都可以访问的文档中找到答案。
我们得出的结论是，不再可以这样生活。 每个团队都应该有数据，对它们的查询应该快速，并且数据本身应该准确并且包含有用的参数。
因此，我们对新的统计/分析系统制定了明确的要求：
- 分析查询应该很快；
- 数据非常准确，理想情况下，这些是与服务的原始用户交互事件；
- 事件的结构应得到描述，理解和访问；
- 可靠的数据存储，一次性交付保证；
- 可以按一组切片计算唯一性，受众（每天，每周，每月），保留指标，用户在服务中花费的时间，针对唯一性和其他指标的量化操作；
- 测试，数据转换和可视化正在进行中。
在厨房里
经验表明，我们需要两个数据库：一个慢速数据库，可以聚合和丰富数据；一个快速数据库，可以处理此数据并在其上构建图形。 这是最常见的方法之一，其中在一个缓慢的数据库中（例如，在hdfs中），在特定时间段内以及基于切片的事件数上构建了不同的投影。
在9月的一个温暖的日子里，我们一边在俯瞰喀山大教堂的厨房里喝茶边聊天，一边想到了将ClickHouse用作快速基础-当时我们已经使用它存储技术日志了。 人们主要对速度和可靠性存在很多疑问：宣称的性能测试似乎不切实际，并且新的数据库版本会定期破坏现有功能。 因此，该建议很简单-可以尝试。
首批样品
我们使用此配置部署了两台计算机的集群：
2xE5-2620 v4（总共32个内核），256G内存，28T位置（带有ext4的raid10）。
最初，它是靠近布局的，但是后来我们切换到了较远的位置。 ClickHouse具有许多不同的表引擎，但是主要的表引擎来自MergeTree系列。 我们选择了具有以下大致设置的ReplicatedReplacingMergeTree：
PARTITION BY dt ORDER BY (toStartOfHour(time), cityHash64(user_id), event_microsec, event_id) SAMPLE BY cityHash64(user_id) SETTINGS index_granularity = 8192;
复制 -表示该表已复制，这解决了我们的可靠性要求之一。
替换 -表通过主键支持重复数据删除：默认情况下，主键与排序键匹配，因此ORDER BY部分仅告诉您什么是主键。
采样方式 -我还想尝试采样：样本返回一个统一的伪随机样本。
index_granularity = 8192是索引衬线之间的数据行的幻数（是的，它是稀疏的），默认情况下使用。 我们没有更改。
分区按天进行（尽管默认情况下按月）。 许多数据请求本应在一天之内进行-例如，为给定的一天制作一份分钟的视频观看次数图表。
接下来，我们获取了一条技术日志，并在表中填充了大约十亿行。 出色的压缩效果，按Int *列类型分组，计算唯一值-一切工作都非常快！
说到速度，我的意思是说没有一个请求的持续时间超过500毫秒，并且大多数都适合50-100毫秒。 这是在两台计算机上-实际上，只有一台计算机参与了计算。
我们查看了所有这些内容，并想象到，不是UInt8列而是国家的ID，而Int8列将被替换为例如有关用户年龄的数据。 他们意识到，只要一切正确完成，ClickHouse完全适合我们。
强大的数据输入
当形成正确的数据模式时，ClickHouse的好处才真正开始。 示例：platform字符串-错误，平台Int8 +词典-良好，LowCardinality（字符串）-方便且良好（稍后再讨论LowCardinality）。
我们在php中创建了一个特殊的生成器类，根据需要，该类基于ClickHouse中的表为事件创建了包装类，并为日志记录创建了一个入口点。 我将解释该方案的示例：
- 分析师/数据工程师/开发人员描述了该文档：需要记录哪些字段，可能的值，事件。
- 将根据上一段中的数据结构在ClickHouse中创建一个表。
- 生成基于表的事件的包装类。
- 产品团队实现填写此类的对象的字段，然后发送。
在不首先更改ClickHouse中的表的情况下，无法在php级别更改方案和记录的数据类型。 反过来，如果没有团队的协调，文档的更改和事件的描述，就无法做到这一点。
对于每个事件，您可以设置两个设置，分别控制发送到ClickHouse和hadoop的事件的百分比。 设置主要是为了逐步滚动，如果出现问题可以减少日志记录。 在hadoop之前，使用Kafka以标准方式传递数据。 在ClickHouse中，他们
与KittenHouse一起以持久模式执行一项
计划，该
计划至少保证了单个事件的交付。
根据将user_id的一些哈希除以集群中的分片数的余数，将事件传递到缓冲区表中所需的分片。 接下来，缓冲区表将数据刷新到本地ReplicatedReplacingMergeTree。 在本地表的顶部，使用分布式引擎提取了一个分布式表，使您可以访问所有分片中的数据。
非规范化
ClickHouse是一个柱状DBMS。 这与正常形式无关，这意味着在事件中拥有所有信息比加入要好。 也有Join，但是如果正确的表不能容纳在内存中，就会开始痛苦。 因此，我们做出了一个坚定的决定：我们感兴趣的所有信息都应存储在事件本身中。 例如，性别，用户的年龄，国家/地区，城市，生日-所有可用于受众分析的公共信息以及有关交互对象的所有有用信息。 例如，如果我们谈论的是视频，则为video_id，video_owner_id，视频上传日期，时长，事件发生时的质量，最高质量，等等。
总计，每个表中有50到200列，而所有表中都有服务字段。 例如，错误日志是error_log-实际上，我们将错误称为类型错误。 如果奇怪的值超过了年龄的字段的大小。
类型低基数（T）
ClickHouse可以使用外部词典。 它们存储在内存中，并定期更新，可以有效地用于各种情况，包括作为经典参考书。 例如，您要登录操作系统，并且有两种选择：字符串或数字+目录。 当然，对于大量数据和高性能分析查询，逻辑上写一个数字，并在需要时从字典中获取字符串表示形式是合乎逻辑的：
dictGetString('os', 'os_name', toUInt64(os_id))
但是，还有一种更方便的方法-使用类型LowCardinality（String），它会自动构建字典。 在一组低基数的情况下，使用LowCardinality的性能要比使用String的性能高得多。
例如，我们对事件类型“播放”，“暂停”，“倒带”使用LowCardinality（字符串）。 或针对平台：“网络”，“ Android”，“ iphone”：
SELECT vk_platform, count() FROM t WHERE dt = yesterday() GROUP BY vk_platform Elapsed: 0.145 sec. Processed 1.98 billion rows, 5.96 GB (13.65 billion rows/s., 41.04 GB/s.)
该功能仍处于试验阶段，因此必须使用以下功能才能使用：
SET allow_experimental_low_cardinality_type = 1;
但是有一种感觉，一段时间后她将不再处于这种情况下。
VKontakte数据聚合
因为有很多列，并且有很多事件，所以自然的愿望是削减“旧的”分区，但首先是组装单元。 有时，有必要分析原始事件（一个月或一年前），因此我们不会削减hdfs中的数据-任何分析师都可以在任何日期联系所需的实木复合地板。
通常，在一个时间间隔内进行聚合时，我们始终基于这样一个事实，即每单位时间的行数等于切割功率的乘积。 这就施加了限制：各国开始以“俄罗斯”，“亚洲”，“欧洲”，“世界其他地区”和年龄段为单位进行收集，每隔一段时间将维度缩减为有条件的百万行。
通过dt，user_id进行汇总
但是我们有一个反应性的ClickHouse！ 我们是否可以将日期增加到50-100百万条？
快速测试表明，在那时，我们可以提出一个简单的想法-让用户留在机器中。 即，在执行数据的某些“转置”时，不是通过使用Spark工具按“日期，切片”进行聚合，而是按ClickHouse的“日期，用户”进行聚合。
通过这种方法，我们将用户存储在汇总数据中，这意味着我们仍然可以考虑受众指标，保留率和频率指标。 我们可以连接各个单元，将几种服务的普通用户数计入整个VKontakte用户。 所有这一切都可以通过表中存在的任何片同时有条件地完成。
我将举一个例子说明：
聚合后（右侧还有更多列）：
在这种情况下，聚合是通过（dt，user_id）精确进行的。 对于具有用户信息的字段，通过这种聚合，您可以使用any，anyHeavy（选择一个经常出现的值）功能。 例如，您可以收集汇总中的anyHeavy（平台），以从视频事件中了解用户大部分使用的平台。 如果需要，可以使用groupUniqArray（平台）并存储用户引发事件的所有平台的数组。 如果这还不够，您可以为平台创建单独的列并存储，例如，从特定平台上筛选的独特视频的数量减半：
uniqCombinedIf(cityHash64(video_owner_id, video_id), (platform = 'android') AND (event = '50p')) as uniq_videos_50p_android
通过这种方法，可以获得相当广泛的聚合，其中每一行都是唯一的用户，并且每一列都包含有关该用户或其与服务的交互的信息。
事实证明，为了计算服务的DAU，在其聚合之上执行这样的请求就足够了：
SELECT dt, count() as DAU FROM agg GROUP BY dt Elapsed: 0.078 sec.
或计算一周内用户使用该服务的天数：
SELECT days_in_service, count() AS uniques FROM ( SELECT uniqUpTo(7)(dt) AS days_in_service FROM agg2 WHERE dt > (yesterday() - 7) GROUP BY user_id ) GROUP BY days_in_service ORDER BY days_in_service ASC 7 rows in set. Elapsed: 2.922 sec.
我们可以通过采样来加快速度，而几乎不会丢失准确性：
SELECT days_in_service, 10 * count() AS uniques FROM ( SELECT uniqUpTo(7)(dt) AS days_in_service FROM agg2 SAMPLE 1 / 10 WHERE dt > (yesterday() - 7) GROUP BY user_id ) GROUP BY days_in_service ORDER BY days_in_service ASC 7 rows in set. Elapsed: 0.454 sec.
应当立即指出，采样不是根据事件的百分比，而是根据用户的百分比，因此，采样成为一种功能非常强大的工具。
或以1/100采样持续4周相同-获得的准确结果降低了约1％。
SELECT days_in_service, 100 * count() AS uniques FROM ( SELECT uniqUpTo(7)(dt) AS days_in_service FROM agg2 SAMPLE 1 / 100 WHERE dt > (yesterday() - 28) GROUP BY user_id ) GROUP BY days_in_service ORDER BY days_in_service ASC 28 rows in set. Elapsed: 0.287 sec.
另一方面，聚合
通过（dt，user_id）进行汇总时，我们不会丢失用户，不会错过有关其与服务交互的信息，但是，当然，我们会丢失有关特定交互对象的指标。 但是您也不能失去它-让我们通过
（dt，video_owner_id，video_id），并遵循相同的想法。 我们会尽可能保留有关视频的信息，我们不会错过有关视频与用户互动的数据，而我们会完全错过有关特定用户的信息。
SELECT starts FROM agg3 WHERE (dt = yesterday()) AND (video_id = ...) AND (video_owner_id = ...) 1 rows in set. Elapsed: 0.030 sec
或昨天的前10个视频观看次数：
SELECT video_id, video_owner_id, watches FROM video_agg_video_d1 WHERE dt = yesterday() ORDER BY watches DESC LIMIT 10 10 rows in set. Elapsed: 0.035 sec.
结果，我们有了以下形式的单位方案
- 按产品中的“日期，用户”进行汇总；
- 按产品内“日期，交互对象”进行汇总；
- 有时还会出现其他预测。
Azkaban和TeamCity
最后，谈谈基础架构。 我们的集合收集从晚上开始，在每个具有原始数据的表上从OPTIMIZE开始，以触发ReplicatedReplacingMergeTree中的非常规数据合并。 该操作可以持续足够长的时间，但是，如果发生汇整，则有必要将其删除。 值得注意的是，到目前为止，我从未遇到过重复的内容，但不能保证它们将来不会出现。
下一步是创建聚合。 这些是bash脚本，其中会发生以下情况：
- 首先，我们从分片获得分片的数量和一些主机：
SELECT shard_num, any(host_name) AS host FROM system.clusters GROUP BY shard_num
- 然后脚本针对每个分片（clickhouse-client -h $ host）按顺序执行以下形式的请求（用于用户的汇总）：
INSERT INTO ... SELECT ... FROM ... SAMPLE 1/$shards_count OFFSET 1/$shard_num
这并非完全最佳，并且可能在主机之间产生大量网络交互。 但是，添加新的分片后，所有内容均可继续使用，保持单元数据的本地性，因此我们决定不必为此担心。
我们有Azkaban作为任务调度程序。 我不会说这是一个超级方便的工具，但它可以完美地完成其任务，包括涉及构建稍微更复杂的管道以及何时需要等待一个脚本来完成其他脚本。
将现在存在的事件转换为聚合所花费的总时间为15分钟。
测试中
每天早上，我们都会运行自动化测试，回答有关原始数据以及聚合的就绪性和质量的问题：“检查昨天，原始数据或聚合中的数据或唯一数据是否减少了不超过0.5％与一周前的同一天相比。”
从技术上讲，这些是使用JUnit并为ClickHouse实现jdbc驱动程序的普通单元测试。 所有测试的运行在TeamCity中启动，并在1个线程中花费大约30秒，如果发生故障，我们会从我们出色的TeamCity机器人收到VKontakte通知。
结论
仅使用ClickHouse的稳定版本，您的头发将柔软如丝。 值得补充的是，
ClickHouse不会减慢速度 。