怎么用clickhouse建数据（从TDW到ClickHouse的最佳实践）

作者：aoxiangpeng，PCG内容平台部数据工程组

| 导语 本篇文章结合司内现有腾讯云、TDW、US、idex、DataHub、DataTalk等工具，手把手教你认识ClickHouse、申请集群、建表导数、制作看板、监控运维，快速掌握ClickHouse的使用。

ClickHouse的接入，主要驱动力来自于内容平台部-企鹅号的工作台项目。在20年11月份，随着数据量的增加和业务的不断丰富，工作台最初版本使用的底层查询引擎已满足不了用户需求，于是在对比司内多个OLAP引擎之后，ClickHouse能给工作台既席查询的业务场景带来更快捷的查询，在20年12月正式接入ClickHouse。起初使用腾讯云的erm集群，需要手动维护ClickHouse环境。随着腾讯云cdwch环境的完善，在21年8月份正式接入了腾讯云ClickHouse数据仓库。目前ClickHouse已经在数据科学与分析中心普遍使用，涉及多个业务的实时和离线场景。

本文是作者结合自身工作经验，总结了从腾讯数仓TDW到ClickHouse的详细操作过程，相信阅读之后，能让开发同学快速受益于这款优秀的OLAP引擎。

ClickHouse是俄罗斯Yandex在2016年年开源的⼀个⾼性能分析型SQL数据库，是一个用于联机分析处理（OLAP）的列式数据库管理系统（columnar DBMS）。开源之后，凭借优异的查询性能，受到业界的青睐。Clickhouse在OLAP领域快速崛起，并且腾讯云提供了比较完善的集群服务。关于ClickHouse的底层原理，推荐此篇文章，此处就不多介绍。文章主要介绍了从TDW到ClickHouse的实践过程。

简单列举一下ClickHouse的优缺点，以此判断你的业务是否适合使用ClickHouse。

​ 优点

• 基于shard replica实现的线性扩展和高可靠。
• 采用列式存储，数据类型一致，压缩性能更高。
• 硬件利用率高，连续IO提高了磁盘驱动器的效率。
• 向量化引擎与SIMD提高了CPU利用率，多核多节点并行化大查询。

​ 缺点

• 不支持真正的删除/更新支持，不支持事务。
• 不支持高并发，官方建议qps为100，但是在资源允许情况下可以通过修改配置文件增加连接数。
• 没融合HDFS的大数据环境，需要自己维护数据的导入、元数据管理等，一些聚合SQL与常见SQL有一定不同，join性能不好。
• 尽量做1000条以上批量写入，避免逐行insert或小批量的insert，update，delete操作，因为ClickHouse底层会不断的做异步的数据合并，影响查询性能。

通过以上优缺点，可以知道ClickHouse的适用场景大致如下：

• 数据存储：业务底层表存储列的值是比较小的数值和短字符串，数据需要从TDW表中大批次导入ClickHouse中，没有update操作，数据导入后的所有请求都是读访问。
• 表设计：业务底层数据表很“宽”，即表中包含大量文章的属性列用作聚合和筛选，读取数据时，会从库表中提取出大量的行，但只用到一小部分列，这是ClickHouse对“大宽表”使用的优势。
• 数据查询：业务的查询场景大多是不同维度的漏斗建设，查询过程都是【过滤 聚合】多维聚合的典型OLAP即席查询场景，查询频率相对较低（通常每台服务器每秒查询数不到百次或更少），查询只有约50毫秒的延迟，用户感受很好。
• 查询结果：查询结果显著小于数据源，数据有过滤或聚合，返回结果占有很小的服务器内存。

​ 申请腾讯云ClickHouse集群，成功后打开实例可以看到如下信息，可参考腾讯云提供的文档，对集群配置做好修改。

注意：对于习惯使用WeTerm的同学，通过ClickHouse内置的JDBC接口连接集群，可以选择跳过此节。

​[idex]（https://idex.woa.com/#/sql）是面向TDW的一款优秀数据分析语言编辑器，现在idex已经支持接入不同的数据源，为了方便后续对集群中库表的操作，可以先配置好ClickHouse的数据源。具体方法如下：

1.设置中心中，配置集群和资源池

2.按照idex的官方文档数据源注册，填写内容大致如下：

3. 在idex上执行的常用SQL

--给不同应用组鉴权，这样【修改】group_name组内的其他成员也可以使用到ClickHouse中的表了 grant describe on datasource clickhouse_test to role group_name; -- 查看该数据源下的所有表 show tables in clickhouse_test; --设置使用库 use clickhouse_test; -- 使用原生的语法 SET `supersql.bypass.forceAll` = true; -- 查看库下表的详情 desc db_name.table_name;

注意：后文中会介绍TDW的数据导入，但是无论通过哪种方式导入数据，都必须先在CllickHouse集群中建好表。

【本地表写，分布式表读】的策略是目前主流的CllickHouse建表方式，这里选择【ReplicatedmergeTree引擎复制表写入、Distributed引擎分布式表读数据】配合使用的策略，这样单分片、多副本才能够保证最大化性能与稳定性。下面给出创建表的参考语句，可以在idex上选好创建的数据源之后直接copy执行。

-- 第一步：创建一个ReplicatedMergeTree引擎复制表 CREATE TABLE IF NOT EXISTS db_name.table_name ON CLUSTER `default_cluster` ( `imp_date` Int64 COMMENT 'imp_date', `id` String COMMENT '文章ID', `pub_date` String COMMENT '发文日期' ) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/{database}/{table}', '{replica}') PARTITION BY imp_date ORDER BY tuple(pub_date) SETTINGS index_granularity = 8192; -- 第二步：创建复制表对应的Distributed引擎分布式表 CREATE TABLE IF NOT EXISTS db_name.table_name_all ON CLUSTER `default_cluster` ( `imp_date` Int64 COMMENT 'imp_date', `id` String COMMENT '文章ID', `pub_date` String COMMENT '发文日期' ) ENGINE = Distributed('default_cluster', 'db_name', 'table_name');

这里不建议直接使用分布式表，原因可参考文章（https://www.jianshu.com/p/ab811cceb856），大致可总结为：

• 分布式表的数据是先写到一个分布式表的实例中并缓存起来，再逐渐分发到各个分片上去，实际是双写了数据（写入放大），浪费资源；
• 数据写入默认是异步的，短时间内可能造成不一致；
• 目标表中会产生较多的小parts，使merge（即compaction）过程压力增大。

​ 准备好前期工作之后，就有了集群、操作平台、表，下面主要介绍了TDW存储数据=>导入ClickHouse集群=>ClickHouse数据使用，大致分为数据接入层、数据存储层、数据服务层、数据应用层，架构图如下：

​ 团队在接入Clickhouse初期，TDW到Clickhouse数据导入都是使用Spark程序读到本地后批量导入，有一定的开发成本。随着公司内部工具的升级，总结了下面三种数据导入的方式。

​ 可参考Datahub3.0提供的功能文档（https://iwiki.woa.com/display/Datahub3/ClickHouse）。Datahub3.0是目前腾讯内部主推的一款数据开发治理工具，并且在不断迭代优化中，建议使用！！

• 优点：几乎没有代码开发，按照文章步骤可以直接配置导入任务，可以兼容TDW表中的NULL值情况。
• 缺点：只支持有分区的表，并且还未上线表的生命周期管理，为了防止Clickhouse的存储爆，目前需要定期手动删分区来控制表生命周期。

配置内容大致可参考下图：

​ US调度平台任务选择【TDW出库到ClickHouse】，配置任务可以将数据导入到ClickHouse中，具体过程参考文章

• 优点：配置任务就可以，几乎没有较多代码量，可以配置前置语句来清除历史分区，以此来控制表的生命周期。
• 缺点：如果字段没有设置Nullable，null值会自动转换为默认值，需要手动处理NULL值之后导入。

配置内容大致可参考下图：

​ 通过Spark程序读取TDW的数据，按照业务要求的数据格式调整好之后，通过调用ClickHouse的HTTP接口或者JDBC接口，将数据分批导入，注意需要代码中处理好TDW表中NULL值，不然程序会报错，可以在代码中手动控制数据存储周期。

• 优点：代码对数据进行处理更加灵活，能手动设置清除历史分区，控制表生命周期，是最可控、最灵活的方式。
• 缺点：需要较多代码量，门槛较高，有能力者还是鼓励使用代码开发，对任务把控和自身技术提高都有很大帮助。

​ 数据成功导入到表中之后，可以通过下面的语法得知数据的占用空间。对于离线数据来说，存入到ClickHouse的数据一般用于ODS层分析或者SQL接口调用，基本使用大宽表的全量表，所以存储层面一般不会存太久的数据，建议合理控制表的生命周期，保证ClickHouse环境的可持续性。通过以下代码可以查看表的大小。

-- 查看库中所有表的存储 select sum(rows) as "总行数", formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小", formatReadableSize(sum(data_compressed_bytes)) as "压缩大小", round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) as "压缩率" from system.parts where database='db_name'; -- 查看库中指定表的存储 select sum(rows) as "总行数", formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小", formatReadableSize(sum(data_compressed_bytes)) as "压缩大小", round(sum(data_compressed_bytes) / sum(data_uncompressed_bytes) * 100, 0) as "压缩率" from system.parts where database='db_name' and table='table_name';

• 对外：将集群查询统一封装为scf服务(RPC)，一般采用HTTP访问和TCP访问，高可用集群可以对外提供统一接口供调用。
• 对内：提供了客户端工具idex/WetTerm直接供分析师及开发人员使用，日常查bade case和局部小问题使用，很快很方便。DataTalk的报表分析使用ClickHouse查询响应也会更快。

DataTalk数据接入后进行看板建设和数据分析：使用了DataHub3.0工具导入数据之后，可以在DataTalk中引入后直接使用，配置大致如下：

数据引入之后，可以直接在Datatalk的看板中使用了，这时候ClickHouse毫秒级的查询速度，能够让你的看板非常“丝滑”！！

scf服务(RPC)后端http接口访问：可以先在idex上测试SQL，成功之后，利用下面的协议POST请求，此功能主要服务于各种页面或者系统的分析。下面是接口协议和请求结果：

POST请求： curl -H 'X-ClickHouse-Format: JSON' -H 'X-ClickHouse-User: username' -H 'X-ClickHouse-Key: password' -H 'X-ClickHouse-Database: db_name' -X POST "http://localhost:8123/" -d "select imp_date,count(*) as cnt from table_name group by imp_date;" 输出的结果： { "meta": [ { "name": "imp_date", "type": "Int64" }, { "name": "cnt", "type": "UInt64" } ], "data": [ { "imp_date": "20211207", "cnt": "1234567" } ], "rows": 1, "statistics": { "elapsed": 0.028966841, "rows_read": 1234567, "bytes_read": 2345678 } }

客户端工具便捷查询：数据分析的过程中，各种思路的验证、异常的定位，都可以在idex/WeTerm中执行和验证，idex前面已经介绍了用法，下面是WeTerm的使用截图：

以上详细介绍了TDW到ClickHouse的全过程，每层都介绍了现已知的多种处理方式，最终作者在这里给出一个目前数据开发团队比较流行的方案，建议使用！

1. 腾讯云cdwch集群
2. idex管理库表
3. DataHub导入数据
4. DataTalk使用数据制作看板

​ 腾讯云平台依托于grafana监控平台来监控各种指标，采用Prometheus采集clickhosue集群信息在grafana做展现，硬件指标监控硬件指标监控主要指ClickHouse服务节点的负载、内存、磁盘IO、网卡流量等一般的监控指标有top排名(慢查询、内存占用、查询失败 )、QPS、读写压力、HTTP&TCP连接数、zookeeper状态等，当这些指标出现异常时通过alertmanager插件配置的规则触发报警。