ClickHouse基础入门

一、ClickHouse简介

ClickHouse是俄罗斯的Yandex于2016年开源的一个用于联机分析(OLAP：Online Analytical Processing)的列式数据库管理系统(DBMS：Database Management System)，主要用于在线分析处理查询（OLAP）,能够使用SQL查询实时生成分析数据报告。

ClickHouse是一个完全的列式数据库管理系统，允许在运行时创建表和数据库，加载数据和运行查询，而无需重新配置和重新启动服务器，支持线性扩展，简单方便，高可靠性，容错。它在大数据领域没有走Hadoop生态，而是采用Local attached storage作为存储，这样整个IO可能就没有Hadoop那一套的局限。它的系统在生产环境中可以应用到比较大的规模，因为它的线性扩展能力和可靠性保障能够原生支持shard + replication 这种解决方案。它还提供了一些SQL直接接口，有比较丰富的原生client。

在传统的行式数据库系统中，数据按如下顺序存储：

行存储

处于同一行中的数据总是被物理的存储在一起。常见的行式数据库系统有：MySQL、Postgres和MS SQL Server。

在列式数据库系统中，数据按如下的顺序存储：

列存储

这些示例只显示了数据的排列顺序。来自不同列的值被单独存储，来自同一列的数据被存储在一起。

常见的列式数据库有： Vertica、 Paraccel (Actian Matrix，Amazon Redshift)、 Sybase IQ、 Exasol、 Infobright、 InfiniDB、 MonetDB (VectorWise， Actian Vector)、 LucidDB、 SAP HANA、 Google Dremel、 Google PowerDrill、 Druid、 kdb+。

1.1 优点

• 灵活的MPP架构，支持线性扩展，简单方便，高可靠性

• 多服务器分布式处理数据，完备的DBMS系统

• 底层数据列式存储，支持压缩，优化数据存储，优化索引数据，优化底层存储

• 容错跑分快：比Vertica快5倍，比Hive快279倍，比MySQL快800倍，其处理的数据级别已达到10亿级别

• 功能多：支持数据统计分析各种场景，支持类SQL查询，异地复制部署，海量数据存储，分布式运算，快速闪电的性能，几乎实时的数据分析，友好的SQL语法，出色的函数支持

1.2 缺点

• 不支持事务，不支持真正的删除/更新

• 不支持高并发（单节点），官方建议qps为100，可以通过修改配置文件增加连接数（在服务器配置足够好的情况下）

• 不支持二级索引

• 不擅长多表join（建议用大宽表）

• 元数据管理需要人为干预

• 尽量做1000条以上批量的写入，避免逐行insert或小批量的insert、update、delete操作

1.3 应用场景

• 绝大多数请求都是用于读访问的，要求实时返回结果

• 数据需要以大批次（大于1000行）进行更新，而不是单行更新，或者根本没有更新操作

• 数据只是添加到数据库，没有必要修改

• 读取数据时，会从数据库中提取出大量的行，但只用到一小部分列

• 表很“宽”，即表中包含大量的列

• 查询频率相对较低（通常每台服务器每秒查询数百次或更少）

• 对于简单查询，允许大约50毫秒的延迟

• 列的值是比较小的数值和短字符串（例如，每个URL只有60个字节）

• 在处理单个查询时需要高吞吐量（每台服务器每秒高达数十亿行）

• 不需要事务

• 数据一致性要求较低

• 每次查询中只会查询一个大表，除了一个大表，其余都是小表

• 查询结果显著小于数据源，即数据有过滤或聚合，返回结果不超过单个服务器内存大小

二、OLAP架构的分类

2.1 ROLAP

ROLAP：Relational OLAP

2.1.1 背景

基于关系型数据库实现，使用关系型数据库存储，采用星型、雪花模型建模。

2.1.2 挑战

当数据量大、维度多的时候，数据查询会变得十分困难。

2.1.3 总结

ROLAP最具有群众基础，但存在性能瓶颈。

2.1.4 历史

• 第一代：Oralce、MySQL、Postgresql
• 第二代：Hive、SparkSQL、ElasticSearch

2.2 MOLAP

MOLAP：Multidimensional OLAP

2.2.1 背景

基于非关系型数据库实现，采用多维数组的方式存储数据，使用立方体模型建模，有十分良好的查询性能。

2.2.2 挑战

通过空间换时间的方式实现，在提升效率的同时会带来额外的存储开销，不能查询立方体之外的数据。

2.2.3 总结

MOLAP性能快，但模型不友好，是为了解决ROLAP性能问题另辟蹊径的方法。

2.3.4 历史

• 第一代：物化视图、位图索引
• 第二代：Apache Kylin、HBase

2.3 HOLAP

HOLAP：Hybrid OLAP

2.3.1 背景

采用混合架构，在MOLAP架构的基础上兼顾明细数据查询的支持（立方体外的数据）。

2.3.2 挑战

架构的复杂性增加

2.3.3 总结

H=R+M，简单粗暴

三、ClickHouse发展历史

Yandex在2016年6月15日开源了一个数据分析的数据库，名字叫做ClickHouse。ClickHouse最初是为 YandexMetrica 世界第二大Web分析平台 而开发的。

3.2 Yandex是一家怎样的公司

1、欧洲最大的互联网公司之一
2、拥有俄罗斯第一的搜索引擎
3、超过50种不同的B2C和B2B产品
4、大数据、机器学习

3.3 Yandex.Metrica

1、全球第三大网络分析工具
2、每天处理超过30亿个事件
3、分析涵盖数百万个网站
4、每天超过10万分析师用户

3.4 ClickHouse发展历程

ClickHouse发展历程

3.4.1 另辟蹊径的Metrage时期

1、摒弃关系模型采用key value
2、LSM树索引
3、实时计算改为预处理（立方体）

问题：只提供了内置的40多种固定分析场景

截至到2015年，Metrage中存储了超过3万亿行的数据，集群规模超过了60台，而查询性能也由26秒降到了惊人的1秒以内。

3.4.2 水到渠成的ClickHouse时代

结合Metrage和OLAPServer为基础进一步完善以实现一个完备的数据库管理系统（DBMS）为目标，最终打造出了ClickHouse并于2016年开源。

3.5 为什么叫ClickHouse

ClickHouse = Click Stream + Data WareHouse

四、ClickHouse的特点

4.1 OLAP数据库、ROLAP模型

完整的DBMS，支持SQL、DDL、DML语句。数十种表引擎，内置数百个函数。

4.2 接口丰富

提供TCP、HTTP底层访问接口，提供JDBC、CLI等封装接口，支持Java、Python、Nodejs等众多第三方接口。

4.3 在线查询

实时应答，无需预处理，也支持立方体预聚合。

4.4 分布式

MPP架构，支持集群模式，支持数据分区、分片、副本。

4.5 高性能

列存、高压缩、向量化引擎，秒杀一切的性能。单机部署，即拥有高性能。

4.6 安全可靠

熔断机制、防误删机制

4.7 权限

客户端接入权限、资源访问权限、操作访问权限、数据行级权限。

4.8 Everything is table

面向表编程，包括代理访问外部资源（例如Zookeeper、文件等），贡献者名单也专门有一张表。

4.9 开源免费、社区活跃

2016年开源，Apache-2.0协议

五、ClickHouse核心概念

5.1 数据分片

数据分片是将数据进行横向切分，这是一种在面对海量数据场景下，解决存储和查询瓶颈的有效手段，是一种分治思想的体现。ClickHouse支持分片，而分片则依赖集群。每个集群由1到多个分片组成，而每个分片则对应了ClickHouse的1个服务节点。分片的数据量上限取决于节点数量（1个分片只能对应1个服务节点）。ClickHouse并不像其他分布式系统那样，拥有高度自动化的分片功能。ClickHouse提供了本地表（Local Table）与分布式表（Distributed Table）的概念。一张本地表等同于一份数据的分片。而分布式表本身不存储任何数据，它是本地表的访问代理，其作用类似于分库中间件。借助分布式表，能够代理访问多个数据分片，从而实现分布式查询。这种设计类似于数据库的分库和分表，十分灵活。例如在业务系统上线的初期，数据体量并不高，此时数据表并不需要多个分片。所以使用单个节点的本地表（单个数据分片）即可满足业务需求，待到业务增长、数据量增大的时候，再通过新增数据分片的方式分流数据，并通过分布式表实现分布式查询。

5.2 列式存储

列式数据库更适合于OLAP场景，对于大多数查询而言，处理速度至少提高了100倍。

1、针对分析类查询，通常只需要读取表的一小部分列。在列式数据库中你可以只读取你需要的数据。例如，如果只需要读取100列中的5列，这将帮助你最少减少20倍的I/O消耗。

2、由于数据总是打包成批量读取的，所以压缩是非常容易的。同时数据按列分别存储这也更容易压缩。这进一步降低了I/O的体积。

3、由于I/O的降低，这将帮助更多的数据被系统缓存。

5.3 向量化（可理解为多线程）

ClickHouse不仅将数据按列存储，而且按列进行计算。传统OLTP数据库通常采用按行计算，原因是事务处理中以点查为主，SQL计算量小，实现这些技术的收益不够明显。但是在分析场景下，单个SQL所涉及计算量可能极大，将每行作为一个基本单元进行处理会带来严重的性能损耗。

ClickHouse实现了向量执行引擎（Vectorized execution engine）,对内存中的列式数据，一个batch调用一次SIMD指令（而非每一行调用一次），不仅减少了函数调用次数、降低了cache miss，而且可以充分发挥SIMD指令的并行能力，大幅缩短了计算耗时。向量执行引擎，通常能够带来数倍的性能提升。

SIMD全称Single Instruction Multiple Data , 单指令多数据流，能够复制多个操作数，并把它们打包在大型寄存器的一组指令集。以同步方式，在同一时间内执行同一条指令。

5.4 表

上层数据的视图展示概念，包括表的基本结构和数据

5.5 分区

ClickHouse支持partition by 子句，在建表时可以指定按照任意合法表达式进行数据分区操作，比如通过toYYYYMM()将数据按月进行分区（分区存储）、toMonday() 将数据按照周几进行分区（分区存储），数据以分区的形式统一管理和维护一批数据（批量插入分区，按分区批量删除）。

5.6 副本

数据存储副本，在集群模式下实现高可用，简单理解就是相同的数据备份。

5.7 引擎

不同是引擎决定了表数据的存储特点，位置和表数据的操作行为。

• 决定表存储在哪里以及以何种方式存储
• 支持哪些查询以及如何支持
• 并发数据访问
• 索引的使用
• 是否可以执行多线程请求
• 数据复制参数
• 并发操作，如insert into tb_x select * from tb_x; 同时进行读和写

表引擎决定了数据在文件系统中的存储方式，常用的也是官方推荐的存储引擎MergeTree系列，如果需要数据副本的话可以使用ReplicateMergeTree系列，相当于MergeTree的副本版本。读取集群数据需要使用分布式表引擎Distribute

六、表引擎

表引擎 MergeTree表引擎家族 MergeTree 数据预聚合 数据去重 数据预聚合 数据修改 数据修改

七、为什么ClickHouse那么快

7.1 高层次架构

• Shared nothing
• 并行计算
• 列式存储
• MergeTree
• 稀疏索引
• 数据压缩

7.2 低层次架构-着眼硬件

从硬件功能层面着手设计，基于CPU指令的向量化执行

• 将要使用的硬件水平是怎样？包括CPU、内存、硬盘、网络等等

• 在这样的硬件上，需要达到怎样的性能？包括延迟、吞吐量等等。

• 使用怎样的数据结构？包括String、HashTable、Vector等等。

• 选择的这些数据结构，在硬件上会如何工作？

7.3 算法优化，及时引入世界一流的先进算法

• Volnitsky Substring Search
• Hyperscan和RS2
• SIMD JSON
• Roaring Bitmaps

7.4 特定场景，特殊优化

函数的优化、查询的优化

八、ClickHouse最适合的场景

• 宽表(<10000列)，结构化数据
• 查询QPS相对较低，但是每个请求的数据使用量很高
• 可应用于BI、电信、金融、电子商务、信息安全、网络游戏、物联网等领域

九、ClickHouse不适合的场景

• OLTP场景
• Key Value数据库

十、Ubuntu 安装ClickHouse

官方安装文档

安装命令

sudo  apt-get install apt-transport-https ca-certificates dirmngr

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv E0C56BD4

echo "deb https://repo.clickhouse.com/deb/stable/ main/" | sudo tee \
    /etc/apt/sources.list.d/clickhouse.list
    
sudo apt-get update

sudo apt-get install -y clickhouse-server clickhouse-client

启动服务

sudo service clickhouse-server start

启动服务后，可以使用命令行客户端连接到它
1、默认情况下，使用default用户并不携带密码连接到localhost:9000。还可以使用--host参数连接到指定服务器

clickhouse-client

2、如果设置了密码，则

//默认用户
clickhouse-client  --multiline --password 设定的密码

//指定用户名密码
clickhouse-client  --user 用户名 --password 密码

连接成功

输入show databases(查看当前已有的数据库) 和 select 1测试安装是否成功

测试安装是否成功

十一、客户端连接ClickHouse

第三方开发的可视化界面

11.1 DBeaver

DBeaver 具有ClickHouse支持的通用桌面数据库客户端。

特征：

• 使用语法高亮显示查询开发。
• 表格预览。
• 自动完成

11.2 允许远程连接

先查看ClickHouse server端监听端口的状态：

lsof -i :8123

root@iZm5eetszs07500os8erolZ:~# lsof -i :8123
COMMAND    PID       USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
clickhous 8277 clickhouse  113u  IPv4 701715      0t0  TCP localhost:8123 (LISTEN)
root@iZm5eetszs07500os8erolZ:~#

这里显示监听本地端口，需要修改配置：

vi /etc/clickhouse-server/config.xml

网上的文章都是把注释掉的<listen_host>::</listen_host>取消注释，然后重启服务：

sudo service clickhouse-server restart

改了配置后，我的服务一直起不来，通过查看日志文件，异常信息如下：

error.log

2021.10.09 11:22:40.809266 [ 14610 ] {} <Error> Application: DB::Exception: Listen [::]:8123 failed: Poco::Exception. Code: 1000, e.code() = 0, DNS error: EAI: Address family for hostname not supported (version 21.9.4.35 (official build))

本机没有开放ipv6，只能对ipv4生效。在/etc/click-house/config.xml中，把<listen_host> 改成0.0.0.0

config.xml

重启服务

sudo service clickhouse-server restart

现在就的端口监听情况：

lsof -i :8123

root@iZm5eetszs07500os8erolZ:~# lsof -i :8123
COMMAND     PID       USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
clickhous 15330 clickhouse  158u  IPv4 727063      0t0  TCP *:8123 (LISTEN)
root@iZm5eetszs07500os8erolZ:~#

11.3 DBeaver连接ClickHouse

DBeaver连接ClickHouse

十二、建库建表

12.1 建库

ClickHouse在逻辑上将表分组为数据库。包含一个default数据库，但我们将创建一个新的数据库db_test:

CREATE DATABASE IF NOT EXISTS db_test;

建库

12.2 建表

内存引擎，数据存储在内存，服务重启后数据丢失

Memory

create table db_test.t_user
(
    id UInt64,
    user_name String,
    birthday Date,
    sex UInt8
) 
ENGINE = Memory()

MySQL引擎(表引擎)可以对存储在远程 MySQL 服务器上的数据执行 SELECT 查询

MySQL引擎(表引擎)

CREATE TABLE db_test.t_medal_member_counter_log (
  id UInt64,
  week UInt64,
  member_id UInt64,
  action_key_id String,
  type String,
  modify_time DateTime,
  create_time DateTime
) 
ENGINE = MySQL('ip:3306', 'testdb', 't_medal_member_counter_log', 'test_u', 'test_PWD_123');

在ClickHouse查询t_medal_member_counter_log其实查询的是MySQL数据库中的t_medal_member_counter_log表

select * from db_test.t_medal_member_counter_log;

补充：MySQL数据库表结构

CREATE TABLE `t_medal_member_counter_log` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `week` int DEFAULT '1' COMMENT '周',
  `member_id` bigint NOT NULL DEFAULT '0' COMMENT '会员Id',
  `type` varchar(20) CHARACTER SET utf8mb4  NOT NULL DEFAULT '' COMMENT '条件类型',
  `action_key_id` varchar(45) DEFAULT '' COMMENT '业务ID',
  `deleted` bit(1) NOT NULL,
  `modify_time` datetime NOT NULL,
  `create_time` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_deleted` (`deleted`),
  KEY `search_index` (`member_id`,`type`,`action_key_id`,`week`)
) ENGINE=InnoDB AUTO_INCREMENT=181691593719812 DEFAULT CHARSET=utf8mb4 AVG_ROW_LENGTH=2048 COMMENT='会员行为记录流水'

12.3 视图

官网 CREATE VIEW

创建普通视图

create  view db_test.view_medal_member_counter_log 
as select 
member_id,
COUNT(CASE WHEN type = 'AC_CONTENT_PUB' THEN type END) ac_content_pub_num,
COUNT(CASE WHEN type = 'AC_ACTIVITY' THEN type END) ac_activity_num,
COUNT(CASE WHEN type = 'AC_LIKE' THEN type END) ac_like_num,
COUNT(CASE WHEN type = 'AC_COMMENT_PUB' THEN type END) ac_comment_pub_num,
COUNT(CASE WHEN type = 'AC_COLL' THEN type END) ac_coll_num,
COUNT(CASE WHEN type = 'AC_FORWARD' THEN type END) ac_forward_num,
COUNT(CASE WHEN type = 'AC_PICKME' THEN type END) ac_pickme_num,
COUNT(CASE WHEN type = 'AC_GETME' THEN type END) ac_getme_num,
COUNT(CASE WHEN type = 'AC_JOINME' THEN type END) ac_joinme_num,
toDate(create_time)create_date 
from t_medal_member_counter_log  
group by member_id,create_date;

创建物化视图

create materialized view view_medal_member_counter_log
ENGINE = SummingMergeTree
PARTITION BY create_date ORDER BY (member_id,create_date)
POPULATE
as 
select 
member_id,
COUNT(CASE WHEN type = 'AC_CONTENT_PUB' THEN type END) ac_content_pub_num,
COUNT(CASE WHEN type = 'AC_ACTIVITY' THEN type END) ac_activity_num,
COUNT(CASE WHEN type = 'AC_LIKE' THEN type END) ac_like_num,
COUNT(CASE WHEN type = 'AC_COMMENT_PUB' THEN type END) ac_comment_pub_num,
COUNT(CASE WHEN type = 'AC_COLL' THEN type END) ac_coll_num,
COUNT(CASE WHEN type = 'AC_FORWARD' THEN type END) ac_forward_num,
COUNT(CASE WHEN type = 'AC_PICKME' THEN type END) ac_pickme_num,
COUNT(CASE WHEN type = 'AC_GETME' THEN type END) ac_getme_num,
COUNT(CASE WHEN type = 'AC_JOINME' THEN type END) ac_joinme_num,
toDate(create_time)create_date 
from t_medal_member_counter_log  
group by member_id,create_date;

注意：如果视图里用的表是MySQL的映射表，当MySQL源表插入数据时，物化视图数据不会变化，因此建议用普通视图，实时查询。