云Clickhouse冷热数据分层存储

1 云 ClickHouse冷热数据分层存储最佳实践 技术架构 场景描述 基于云 ClickHouse可以给电商、游戏、 互联网以及其他行业提供高性能、高稳定 性、低维护成本、高性价比的实时数据分 析、精准营销、业务运营、业务分析、业 务预警、业务营销、数仓加速等场景化方 案，本实践会向客户提供数据库低维护成 本、数据库链路构建、冷热分层存储、快 熟分析等操作实践。 方案优势 1. 运维管控优势明显，方便客户扩展集群 升降配、监控告警、针对、升级，可以 产品列表 自动均衡倾斜数据等。 ⚫ 专有网络 VPC 2. 数据链路完整，基于 DTS、 dataworks 、 ⚫ 云服务器 ECS SLS可以构建数据链路。 ⚫ 云数据库 ClickHouse 3. 内核优势：bug修复、资源管理 ⚫ 日志服务 SLS 4. 安全优势：白名单、VPC网络 ⚫ 云数据库 RDS MySQL版 5. 冷热分层存储 ⚫ 数据传输 DTS 解决问题 最佳实践频道 阿里云最佳实践技术分享群 1. 维护成本低不用建设维护体系，稳定性 高，数据倾斜自动均衡。 2. 完善的数据同步链路，可以平滑将业务 库、大数据、日志服务的数据同步到 Clickhouse，降低研发成本。 3. 平滑升级版本，业务中断小。 4. 冷热分层后透明读取，帮客户节约整体 数据存储成本。

2云服务器 ECS（产品名称） 文档模板（手册名称）/文档版本信息 阿里云 云 ClickHouse 冷热数据分层存储 最佳实践 文档版本：20210416（发布日期） 文档版本：20150122（发布日期） II

3云 ClickHouse冷热数据分层存储最佳实践 文档版本信息 文档版本信息 文本信息 属性 内容 文档名称 云 ClickHouse冷热数据分层存储最佳实践 文档编号 209 文档版本 V1.0 版本日期 2021-04-16 文档状态 外部发布 制作人 君远、七凌 审阅人 文档变更记录 版本编号 日期 作者 审核人 说明 V1.0 2021-04-16 君远、七凌 创建 文档版本：20210416 I

4云 ClickHouse冷热数据分层存储最佳实践 前言 前言 概述 基于云 ClickHouse可以给电商、游戏、互联网以及其他行业提供高性能、高稳定性、 低维护成本、高性价比的实时数据分析、精准营销、业务运营、业务分析、业务预警、 业务营销、数仓加速等场景化方案，本实践会向客户提供数据库低维护成本、数据库 链路构建、冷热分层存储、快熟分析等操作实践。 名词解释 ⚫ VPC：专有网络 VPC（Virtual Private Cloud）是用户基于阿里云创建的自定义 私有网络, 不同的专有网络之间二层逻辑隔离，用户可以在自己创建的专有网络 内创建和管理云产品实例，比如 ECS、负载均衡、RDS等。更多信息，请参见： www.aliyun.com/product/vpc ⚫ ECS：云服务器 ECS（Elastic Compute Service）是一种简单高效、处理能力 可弹性伸缩的计算服务。帮助您构建更稳定、安全的应用，提升运维效率，降低 IT 成本，使您更专注于核心业务创新。更多信息，请参见： www.aliyun.com/product/ecs ⚫ SLS：日志服务（SLS）是云原生观测分析平台，为 Log/Metric/Trace等数据提 供大规模、低成本、实时平台化服务。一站式提供数据采集、加工、分析、告警 可视化与投递功能，全面提升研发、运维、运营和安全等场景数字化能力。更多 信息，请参见：www.aliyun.com/product/sls ⚫ 云数据库 ClickHouse：云数据库 ClickHouse 是分布式实时分析型列式数据库 服务。具有高性能、开箱即用、企业特性支持。广泛应用于流量分析、广告营销 分析、行为分析、人群划分、客户画像、敏捷 BI、数据集市、网络监控、分布 式 服 务 和 链 路 监 控 等 业 务 场 景 。 更 多 信 息 ， 请 参 见 www.aliyun.com/product/clickhouse ⚫ 数据传输服务 DTS：数据传输服务(Data Transmission Service) DTS支持关系 型数据库、NoSQL、大数据(OLAP)等数据源间的数据传输。 它是一种集数据 迁移、数据订阅及数据实时同步于一体的数据传输服务。数据传输致力于在公 共云、混合云场景下，解决远距离、毫秒级异步数据传输难题。 它底层的数据 文档版本：20210416 III

5云 ClickHouse冷热数据分层存储最佳实践 前言 流基础设施为阿里双 11异地多活基础架构， 为数千下游应用提供实时数据流， 已在线上稳定运行 6年之久。 您可以使用数据传输轻松构建安全、可扩展、高 可用的数据架构。更多信息，请参见 www.aliyun.com/product/dts ⚫ 云数据库 RDS MySQL 版：云数据库 RDS MySQL 版是全球最受欢迎的开源数 据库之一，作为开源软件组合 LAMP（ Linux + Apache + MySQL + Perl/PHP/Python） 中的重要一环，广泛应用于各类应用场景。更多信息，请参见 www.aliyun.com/product/rds/mysql 文档版本：20210416 IV

6云 ClickHouse冷热数据分层存储最佳实践 目录 目录 文档版本信息 .............................................................................................................................................................. I 法律声明 ..................................................................................................................................................................... II 前言 ............................................................................................................................................................................ III 目录 ............................................................................................................................................................................. V 最佳实践概述 ............................................................................................................................................................. 1 前置条件 ..................................................................................................................................................................... 3 1. 云资源创建和配置 ............................................................................................................................................. 4 1.1. 部署云上 VPC环境 ............................................................................................................................. 4 1.2. 创建安全组 ......................................................................................................................................... 6 1.3. 创建云 Clickhouse集群 ...................................................................................................................... 8 1.4. 创建云 RDS MySQL实例并造数据 ................................................................................................... 12 1.4.1. 创建 RDS实例 ........................................................................................................................... 12 1.4.2. 构造数据 ................................................................................................................................... 17 1.5. 创建云服务器 ECS实例 .................................................................................................................... 20 1.6. 创建日志服务 SLS Project并采集 RDS审计日志数据.................................................................... 24 2. 使用云控制台管理云 ClickHouse ..................................................................................................................... 32 2.1. 利用云控制台垂直升配云 ClickHouse集群 .................................................................................... 32 2.2. 利用云控制台垂直降配云 ClickHouse ............................................................................................. 33 2.3. 利用云控制台水平扩容云 ClickHouse ............................................................................................. 34 2.4. 利用云控制台水平缩容云 ClickHouse ............................................................................................. 36 2.5. 通过云 ClickHouse白名单控制权限 ................................................................................................ 38 3. 使用云 ClickHouse进行数据冷热分层存储和查询 ........................................................................................ 40 3.1. 设置云 ClickHouse冷热分层存储 .................................................................................................... 40 3.2. 通过空间阈值设置自动进行冷热分层存储 .................................................................................... 42 3.3. 通过建表设置 TTL自动进行冷热分层存储 .................................................................................... 44 3.4. 冷热分层存储数据查询 ................................................................................................................... 48 3.5. ClickHouse典型 SQL冷热分层存储数据查询 ................................................................................. 54 4. 配置云 ClickHouse数据链路 ............................................................................................................................ 59 4.1. 配置 RDS MySQL业务库 DTS同步云 ClickHouse数据链路 ........................................................... 59 4.2. 配置 SLS日志投递 ............................................................................................................................ 66 5. 小结和参考 SQL ................................................................................................................................................ 73 5.1. 最佳实践小结 ................................................................................................................................... 73 5.2. 最佳实践参考 SQL ............................................................................................................................ 73 文档版本：20210416 V

7云 ClickHouse冷热数据分层存储最佳实践 最佳实践概述 最佳实践概述 概述 基于云 ClickHouse构建开箱即用、低维护成本高稳定性、高安全性、高扩展性、高性 能、低运维成本、支持冷热分层存储、高数据压缩比的 OLAP分布式数据库解决方案。 适用场景 ⚫ 交互式查询报表 交互式查询报表，实时分析订单、收入、用户数等核心业务指标；构建用户来源分 析系统，跟踪各渠道 PV、UV来源。 ⚫ 海量数据实时多维查询 在数亿至数百亿记录规模大宽表，数百以上维度自由查询，响应时间通常在 100 毫秒以内。让业务人员能持续探索式查询分析，无需中断分析思路，便于深挖业务 价值，具有非常好的查询体验。 ⚫ 用户特征行为分析系统 利用 ClickHouse对人群标签数据进行实时筛选并进行群体画像统计；自定义条件 对海量明细日志记录进行过滤，分析用户行为。 ⚫ 用户分群统计 构建用户特征大宽表，任意选择用户属性标签数据和筛选条件，进行人群特征统计 分析。 ⚫ 访客来源分析展示 通过批量离线计算对用户访问日志进行用户行为关联，生成用户行为路径大宽表 同步到 ClickHouse，基于 ClickHoue构建交互式访客来源探索分析可视化系统。 方案优势 ⚫ 扩展性强：提供数据库扩容管理模块，能便捷地按需对集群进行扩容，无需手动 进行数据搬迁。 ⚫ 高可靠存储引擎：基于阿里飞天分布式系统的云盘存储，充分利用云平台存储 文档版本：20210416 1

8云 ClickHouse冷热数据分层存储最佳实践 最佳实践概述 可靠性特性。 ⚫ 数据闭环流通：与云上日志系统、数据库、数据应用工具无缝集成，能便捷地进 行数据同步，降低数据迁移工作量。 ⚫ 架构灵活：支持单节点、单副本、多节点、多副本多种架构。 ⚫ 便捷运维：专业监控和数据库管理平台 ⚫ 支持冷热分层存储：冷热分层存储降低历史数据存储成本，支持冷热数据透明 查询。 ⚫ 数据安全：访问白名单和一键恢复，多层网络安全防护。 文档版本：20210416 2