clickhouse建宽表多少列最合适？
大家在使用ClickHouse时候经常会问: “多少列才算合适？我可以要一个有100列的表吗？1000列？5000列?”事实上，没有确切的答案。让我们还是先去测试下。于是便决定创建一个极端的实验，比如创建10000列的表。
也不用想那么多，不管三七二十一，试验下看看，其实只要足够的内存和cpu是可以的，只是不清楚需要多少。如果你想了解实验的具体情况，那就来看这篇文章吧~
分析一波
- metrics ：clickhouse的监控指标
考虑一个从各种设备类型收集指标的监视应用程序。每种设备类型都可能有一组独特的不同指标。当存储在单个表中时，可能产生数百甚至数千个metrics。最有效的方法是将每个metrics保存在单独的列中。当提前知道metrics标准时，这种方法效果最好。对于可变的时间序列，例如当动态添加新的设备类型时，可以使用其他方法，例如在键值数组或类地图结构中存储metrics。
但是，向 ClickHouse 添加新列的成本非常低廉，而且我们已经看到了 ClickHouse 用户定期添加新列的常规操作。例如，当出现一个新的指标时，或者为了实现存储在原始 JSON 或数组中最常用的指标。这可能导致具有数百或数千列的非常宽的表。
创建10K 列的表
当列数不太大时，使用 CREATETABLE 语句创建表很容易。然而，如果我们需要数以千计的列数，那就需要用到自动化语句，下面是自动化语句sql:
WITH cols as (SELECT 'col' || toString(number) as col_name, 'Int32' as col_type from numbers(10000))
SELECT
'CREATE TABLE events_wide (
timestamp DateTime,
device_id String,
' || arrayStringConcat(arrayMap( (k, t) -> k || ' ' || t, groupArray(col_name), groupArray(col_type)), ',\n')
|| '
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (device_id, timestamp)
' as create_table
FROM cols
FORMAT TSVRaw
在这里，我们使用 ClickHouse 数字生成器生成一个具有10K 行的数据集，这些行定义列和数据类型。然后，我们使用例程数组魔术将行转换为列定义。其中FORMAT TSVRaw 很重要——它保持所有行尾和引号字符就位。
最终结果，是一个10K + 行的大表，创表语句:
CREATE TABLE events_wide (
timestamp DateTime,
device_id String,
col0 Int32,
col1 Int32,
col2 Int32,
...
col9999 Int32
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (device_id, timestamp)
为了将表定义加载到 ClickHouse，我们可以复制/粘贴巨大的 DDL 语句，或者使用管道:
clickhouse-local --query="WITH cols as (SELECT 'col' || toString(number) as col_name, 'Int32' as col_type from numbers(10000))
SELECT
'CREATE TABLE events_wide (
timestamp DateTime,
device_id String,
' || arrayStringConcat(arrayMap( (k, t) -> k || ' ' || t, groupArray(col_name), groupArray(col_type)), ',\n')
|| '
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (device_id, timestamp)
' as create_table
FROM cols
FORMAT TSVRaw" | clickhouse-client -h <my-server> --user=<my-user> --password
现在我们有了一个10K 列表，接下来是什么？我们还要创建一个表，将所有指标存储在一个 Map 中以进行比较:
CREATE TABLE events_map (
timestamp DateTime,
device_id String,
metrics Map(LowCardinality(String), Int32)
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (device_id, timestamp)
创建了表之后，我们需要加载测试数据。
插入数据到10K 列的表中
有多种方法可以将数据加载到 ClickHouse 中。为了生成巨大的 INSERT 语句，我们将在这里再次使用元编程。它将允许我们探索一些 ClickHouse 的限制:
WITH cols as (SELECT 'col' || toString(number) as col_name, 'Int32' as col_type from numbers(10000)),
1000 as devices,
1000000 as rows
SELECT
'
INSERT INTO events_wide
SELECT
now() timestamp,
toString(rand(0)%' || toString(devices) || ') device_id,
' || arrayStringConcat( arrayMap( c -> 'number as ' || c, groupArray(col_name)),',\n')
||
'
FROM numbers(' || toString(rows) || ')'
FROM cols
FORMAT TSVRaw
这个脚本有三个可以修改的变量:
- Protocol：生成列列表，就像我们对 CREATE TABLE 所做的那样
- device：指定唯一设备 _ ids 的数量
- rows：指定要插入的行数
在每一行中，所有的列都得到相同的值，但是对于本文的研究来说，这是可以的。
生成的INSERT语句可以存储在一个文件中，因此我们可以快速重新运行它并进行小的调整。它会像这样:
INSERT INTO events_wide
SELECT
now() timestamp,
toString(rand(0)%1000) device_id,
number as col0,
number as col1,
number as col2,
...
number as col9999
FROM numbers(1000000)
一旦我们生成了 INSERT 语句并尝试执行它，我们就得到了第一个凸点:
Max query size exceeded: '9114'. (SYNTAX_ERROR)
我们的 INSERT 语句大小为287K，高于默认的 ClickHouse 限制(256K)。我们需要增加 max _ query _ size 设置。它可以作为参数添加到 clickhouse-client，例如:
Cat q.sql | clickhouse-client-max _ query _ size = 1000000
让我们将它设置为1M 并再次尝试运行加载脚本。
AST is too big. Maximum: 50000. (TOO_BIG_AST)
又报错！现在 ClickHouse 解析器抱怨这个查询太复杂了。Max _ ast _ element 需要增加，默认值为50K。我们也增加一点。现在我们遇到了记忆问题:
Memory limit (total) exceeded: would use 12.60 GiB (attempt to allocate chunk of 4194320 bytes)
最初，我们使用了一个4vCPU 和16GB RAM 的服务器进行这些测试，但似乎不足以支持10K 列。ClickHouse 为每个列分配2MB 的缓冲区，因此对于10K 列，可能需要超过20GB 的 RAM。让我们将集群重新扩展到更大的节点大小，并证明如果有更多 RAM，我们可以加载数据。于是，便尝试用16 vCPU/64 GB RAM 服务器，但报了下面的错误:
Memory limit (for query) exceeded: would use 53.11 GiB (attempt to allocate chunk of 8000256 bytes), maximum: 53.10 GiB. (MEMORY_LIMIT_EXCEEDED)
内存溢出了，再用 32 vCPU/128 GB 的服务器试下。
在等待了167秒后，我们终于完成了1M 行数据存到了一个10K 列的表中！现在我们可以检查 query _ log 并查看内存使用峰值为64GB。
有可能减少内存占用吗？如果我们为 MergeTree 表启用紧凑部分会怎样？压缩部分中的列被存储在一个单独的文件中(准确地说，是数据和标记的单独文件) ，每个列的偏移量也被存储。因此，它仍然是柱状的，但不是每列写和读单独的文件，ClickHouse 寻找少量的文件。使用较少数量的文件，我们可以期望更好的插入性能和更少的内存消耗。
为了启用紧凑型部件，我们需要将 min _ bytes _ for _ wide _ part 和 min _ rows _ for _ wide _ part 设置为一些更大的值。这些是表级设置，可以用 ALTERTABLE 语句修改。然而，为了进行比较，我们将创建一个新表:
CREATE TABLE events_compact as events_wide
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (device_id, timestamp)
SETTINGS
min_bytes_for_wide_part=1048576000,
min_rows_for_wide_part=1048576;
因此我们用这种方法为紧凑的部件设置100MB 和1M 的行限制。
让我们运行与前面相同的 insert 语句，但是对象是已更改的表。相当令人惊讶的是，插入 events _ compacttable 需要多花费25% 的时间和 RAM: 214s 和82GB！
最后，让我们使用以下 SQL 将数据加载到 events _ map 表中。我们不需要元编程，因为我们可以在查询执行期间构造 map:
INSERT INTO events_map
WITH arrayMap( n -> 'col' || toString(n), range(10000)) as cols
SELECT
now() timestamp,
toString(rand(0)%1000) device_id,
arrayMap( c -> (c,number), cols)
FROM numbers(1000000)
这个查询为在 INSERT 上转换为 Map 数据的每一行构造一个元组数组[(‘ col0’，number) ，... ，(‘ col9999’，number)]。这又耗尽了内存，所以我们必须做另一个技巧: 减少块大小。方法如下:
SELECT ... SETTINGS max_block_size=100000
加载到 events _ map 表的速度慢如龟速。即使块大小减小，45 GB 的峰值内存使用也需要13分钟。但是，本例中的问题不是 INSERT，而是查询的 SELECT 部分。与对宽表和紧凑表的查询不同，在这里我们必须对每一行使用10K 元素数组进行操作。它是缓慢和内存密集型的。为了优化这个查询，我们已经尝试了许多实验，但是即使在插入常量或从文件中加载准备好的数据时，也不能得到更好的结果！看起来主要的开销是处理巨大的数组。
由于100K 行块有助于减少 events _ map 的内存占用，因此让我们对宽表和紧凑表尝试同样的做法。结果非常有趣: 紧凑部件表的内存使用显著减少，但宽表的内存使用几乎保持不变。以下是一个总结:
| 表名 | 加载1M 行 | RAM 1M 行 | 加载10x100K 行 | RAM 10x100K 行 |
|---|---|---|---|---|
| 事件范围 | 167 | 64GB | 156 | 50GB |
| Events _ 压缩 | 212 | 82GB | 157 | 9.3 GB |
| Events _ map | — | — | 780 | 45GB |
现在让我们看看所有表的各个部分:
select table, part_type, count(), sum(rows), sum(bytes_on_disk) bytes_on_disk, sum(data_uncompressed_bytes) uncompressed from system.parts where table like 'events_%' group by table, part_type order by table
┌─table──────────┬─part_type─┬─count()─┬─sum(rows)─┬─bytes_on_disk─┬─uncompressed─┐
│ events_compact │ Compact │ 10 │ 1000000 │ 41794344282 │ 40007890218 │
│ events_map │ Wide │ 2 │ 1000000 │ 843832859 │ 60463651504 │
│ events_wide │ Wide │ 10 │ 1000000 │ 41089353783 │ 40007889959 │
└────────────────┴───────────┴─────────┴───────────┴───────────────┴──────────────┘
Events _ map 表中未压缩的数据大小更大，因为列名现在存储在 map 中的每一行上，但是压缩的数据大小明显更小——这要归功于有大量重复的测试数据，这些数据可以更好地压缩为行格式。
宽表的另一个潜在警告是后台合并性能。我们还来看一下对各个表运行 OPTIMIZETABLEFINAL 语句的合并性能。
| 表 | 合并时间到 |
|---|---|
| 事件范围 | 410 |
| Events _ 压缩 | 2500 |
| Events _ map | 1060 |
首先，我们可以看到合并是非常缓慢的！合并10M 宽的行要比最初加载它们花费更多的时间。合并性能为一个紧凑的部件表只是可怕的！尽管我们在插入 events _ compact 时节省了 RAM，但是后来我们在 merge 上花费了很大的负载。
实验要求:
- 将 max _ query _ size 设置增加到1M，这是处理大型 SQL 所必需的
- 将 max _ ast _ element 设置增加到256K，这也是处理大型 SQL 所必需的
- 内存超过64GB
- 如果内存不足，减小 max _ block _ size。
- 使用紧凑的部件可能会降低 RAM 的利用率，但也有其他缺点，如插入速度较慢和合并速度非常慢。
查询10K 行的表
为了测试查询性能，我们运行以下查询:
- 全面扫描查询:
select count() from events_wide where not ignore(*)
- 以某种标准衡量，排名前十的设备有:
select device_id, max(col1234) col1234
from events_wide
group by device_id
order by col1234 desc limit 10
- 按时间维度分组的单个设备的多个指标:
select timestamp ts,
avg(col111) col111, avg(col222) col222, avg(col333) col333, avg(col444) col444
from events_wide
where device_id = '555'
group by ts
order by ts
对 events _ map 表的查询需要稍作修改，以使用指标[‘ colXYZ’]而不是 colXYZ。
以下是查询的运行时间和内存使用情况:
| 表 | 问题1 | 问2 | 问3 |
|---|---|---|---|
| 事件范围 | 126s/47GB | 0.052秒/58MB | 0.047秒/13 MB |
| Events _ 压缩 | 160s/40GB | 0.046秒/59MB | 0.072秒/10MB |
| Events _ map | 4.5秒/255 MB | 8.8 s/9 GB | 0.170秒/102 MB |
当选择所有列时，10K 列表的速度预计会很慢，而且内存不足，但是对于触及列子集的查询来说，速度快得惊人。
在全面扫描时，Map 表的速度要快得多，在单个设备上运行良好，但是在所有设备上扫描一个metrics时，速度要慢得多，因为 ClickHouse 每次都必须读取和扫描庞大的 Map 列。
一般来说，典型的监视查询 Q2和 Q3的查询性能不受许多列的影响——这要感谢 ClickHouse 柱状数据格式。只有 events _ map 表(本质上是一种行格式)感觉到额外的负载。
缩减到1000列
在加载10,000个列时，我们遇到了一些挑战。如果我们把列的数量减少到只有一千列呢？我们来测试一下！
创建模式和数据的相同查询可以像上面那样使用，只需将列数从10,000改为1000即可。对于1000列，我们不需要应用任何特殊的设置，系统默认设置工作得很好，我们可以在一个块中加载1M 行。
1000列的加载性能是10000列的10倍，而 merge 也很快:
| 表名 | 加载1M 行 | RAM 1M 行 | 合并1M 行 |
|---|---|---|---|
| 事件范围 | 12 | 6.5 GB | 11分 |
| Events _ 压缩 | 13 | 8.2 GB | 9.3秒 |
| Events _ map | 74 | 36GB | 4.1秒 |
ClickHouse 默认的插入块大小是1048545。所以我们可以假设，在默认情况下，对于更大的插入，ClickHouse 也将消耗相同数量的 RAM。尽管如此，events _ map 表的内存使用率仍然很高。处理巨大的映射和数组是内存密集型的。这会引导我们进入下一部分。
稀疏的宽表怎么样？
在实际应用中，一个设备永远不会产生10K 的指标。通常，一种设备类型负责10-100个指标。但是，多种设备类型可能会产生不同的metrics指标集。当存储在单个监视表中时，可能会产生上一节所讨论的宽表。这些表必须是稀疏的，因为每行只有一小部分列有数据，其他列为空。由于我们按设备对表进行排序，并且单个设备的指标通常分组在一起，因此矩阵中的数据分布是块稀疏的。
在这种情况下，映射表将更加紧凑，因为它不必存储空值。在这个场景中测量 ClickHouse 也会很有趣，但是这会使本文太长。让我们演示一下稀疏映射表是什么样子的。
下面的脚本生成1M 行，但是对于每个设备，它只在10K 可能的行中放入100个指标。
INSERT INTO events_map_sparse
SELECT
now() timestamp,
toString(rand(0)%1000 as did) device_id,
arrayMap( c -> ('col' || toString(c), number), range(did*10, did*10 + 100))
FROM numbers(1000000)
SETTINGS function_range_max_elements_in_block=1000000000
这只需要8加载在一个单一的块！与完整的10K 值相比，数据大小减少了100倍:
┌─table─────────────┬─part_type─┬─count()─┬─sum(rows)─┬─bytes_on_disk─┬─uncompressed─┐
│ events_map │ Wide │ 2 │ 1000000 │ 843832859 │ 60463651504 │
│ events_map_sparse │ Wide │ 1 │ 1000000 │ 8014874 │ 601591449 │
└───────────────────┴───────────┴─────────┴───────────┴───────────────┴──────────────┘
查询也相应地更快。
宽表或多表
另一种设计方法是使用多个表，每个设备类型一个。因此，我们可以有100个表，每个表有100个列，而不是10K 列宽的表。乍看之下，这似乎有些道理，但这只是另一种权衡。虽然插入到这样的“普通”表很容易，但插入次数要多100倍。此外，管理大量表可能会很复杂。与一个宽表相比，100个表的合并总开销将更高。摄入管道看起来也更复杂。应用程序层需要知道如何将查询路由到适当的表等等。当然，这些根本不是阻碍因素，可能适用于那些准备自己承担管理开销的用户。
最终结论
极限测试clickhouse这个过程挺有意思，虽然过程有点艰难。
现在让我们总结下测试的结论吧。clickhouse对于10000列也是能扛住的，但是在插入数据和执行维护操作(例如合并或突变)时可能需要额外的调优和大量 RAM。但只要RAM足够，用户可以获得出色的 ClickHouse 查询性能。对于典型的分析查询，10个列表和10000个列表没有什么不同。当不需要处理数千列时，可以使用 Map 数据类型。它可以在方便性和性能之间提供很好的折衷，特别是对于稀疏表，因为在一行中只使用了10K 列的一小部分。
回答标题提到的问题：**clickhouse建宽表多少列最合适？**1000列内都可以，原因看文章实验即可。
ClickHouse 的魅力在于它的灵活性，用户不一定要使用单个模式设计，有多种选择。理解不同方法的优缺点对于每个 ClickHouse 用户都很重要，同时实践才能检验真理！
转载自:https://juejin.cn/post/7118744460908822559