- clickhose的 安装
安装参考: - clickhouse的文档支持中文,但是中文文档更新比英文的慢
- clickhouse本质上是一个 数据分析工具,适合用来做数仓,它是列存数据库,擅长数据分析,不擅长数据修改,和hive类似,但是比hive 快的多。所以在数据量不是特别大的数仓情况下,可以考虑clickhouse。
他的优点在于独立部署不需要依赖其他分部署数据存储和分布式计算框架,部署简单,单机就可以维护10亿级别的数据。
缺点在于它比较吃单机硬件资源,而且是 cpu 内存 硬盘都吃,尤其是cpu。 - clickhose单机可以承载数十亿数据
- ck 不支持事务
- clickhouse-client会显示默认分组信息,别的客户端比如 DBeaver不会显示
- 启动 服务 和 启动客户端
- 可执行文件位于 /usr/bin
- 核心配置文件位置
server配置文件位置:/etc/clickhouse-server/
客户端配置目录:/etc/clickhouse-client/
config.xml主要是 服务器相关的配置
users.xml是用户相关的配置和和一些硬件参数限制
config.d 用来写额外配置的
config.d,users.d 用来写额外配置的 ,然后再住配置文件里面引用
- 日志位置
/var/log/clickhouse-server/ - 数据文件位置
/var/lib/clickhouse 下面
data存的是数据表数据文件
metadata存的是表和数据库的结构文件 - 允许远程访问
去掉 config.xml 里面 <listen_host>::</listen_host> 的注释 - click默认端口 8123 ,模拟mysql端口 9004,模拟postgresql 9005
- 表引擎和数据库引擎
- 表引擎是在建表的时候指定的类似mysql的 innodb
- 数据库引擎是建立数据库的时候指定的,比如建立一个数据库连接到mysql做数据同步,数据库引擎类似flink的catalog
- 使用表引擎 可以映射mysql数据库和postgresql
clickhouse的表引擎,可以同步别的数据库的数据,并且存下来,支持mysql postgresql
依赖mysql的主从,通过binlog获取同步数据,并且支持读取之前已有的数据。
在clickhouse这边的配置
这样的效果就是在这个clickhouse的数据库里面可以查询到mysql的数据,并且clickhouse把这些数据存了下来,数据量大的时候查询分析效率比mysql更高
- 使用表引擎 创建clickhouse的表
主键和 order by 至少要指定一个
只指定排序字段的时候,主键就是排序字段,只指定主键的时候,排序字段就是主键
不指定表引擎默认是ENGINE = MergeTree,如果要指定表引擎,需要写在第一行
主键和排序的顺序无所谓
- clickhouse的主键必须是排序字段前面一部分(或者全部),clickhouse里面排序字段比主键更加重要
ReplacingMergeTree,SummingMergeTree的判断重复依据都是 排序字段,而不是主键字段
- clickhouse的 字符串只能用单引号,不能用双引号
- 数据插入和查询
- 数据的修改和删除
即便不支持修改的数仓工具也可以通过版本和假删除来实现修改和删除效果
- clikchouse的数据类型
数据类型大小写敏感,并且是大驼峰命名
文旦地址: https://clickhouse.com/docs/en/sql-reference/data-types
- Integer types: signed and unsigned integers (
UInt8
,UInt16
,UInt32
,UInt64
,UInt128
,UInt256
,Int8
,Int16
,Int32
,Int64
,Int128
,Int256
) - Floating-point numbers: floats(
Float32
andFloat64
) and Decimal - Boolean: ClickHouse has a Boolean
- Strings: String and FixedString
- Dates: use Date and Date32 for days, and DateTime and DateTime64 for instances in time
- JSON: the JSON stores a JSON document in a single column
- UUID: a performant option for storing UUID
- Low cardinality types: use an Enum when you have a handful of unique values, or use LowCardinality when you have up to 10,000 unique values of a column
- Arrays: any column can be defined as an Array
- Maps: use Map for storing key/value pairs
- Aggregation function types: use SimpleAggregateFunction and AggregateFunction for storing the intermediate status of aggregate function results
- Nested data structures: A Nested is like a table inside a cell
- Tuples: A Tuple, each having an individual type.
- Nullable: Nullable allows you to store a value as
NULL
when a value is "missing" (instead of the column settings its default value for the data type) - IP addresses: use IPv4 and IPv6 to efficiently store IP addresses
- Geo types: for geographical data, including
Point
,Ring
,Polygon
andMultiPolygon
- Special data types: including Expression, Set, Nothing and [
Interval
]
- 查询表的定义
- 表的分区,一般建议按天分区
- 主键不是唯一约束,只是一级索引
ReplacingMergeTree 可以实现最终一致性,效果类似唯一索引,但是需要合并数据以后才才能在分区内唯一 - clickhouse的 的二级索引(跳数索引)
index indexName xxx字段 type 索引类型 granularity N
- clickhouse的索引间隔粒度
新版本好像不让指定了
settings index_granularity = 8192 - ck 的 ttl,指定这个字段在 另一个时间字段的值的基础上 间隔多少秒以后删除
支持 字段的 ttl ,也支持 行的ttl(行的ttl 设置在表上的)
删除不是及时的,字段删除以后变成默认值
- clickhose的 各种 mergeTree 的用途
MergeTree
主要引擎默认的就是它
ReplacingMergeTree(替换合并树)
Replace + ing = Replacing
支持去重,但是是分区内去重,并且只有合并以后才能能避免重复
去重的前提是排序字段完全相等,没有排序字段就按照Id,去重以后取值是 ReplacingMergeTree(age) 指定字段最大的的一条
SummingMergeTree(求和合并树)
ck会把重复的数据的指定字段合并起来,检查重复也是在合并的时候才有效
同样需要排序字段相等才合并,如果只有Id的时候是按照Id指定字段合并
注意求和数据溢出会变成负数
- 触发手动合并
- ck 的 final关键字 的几种用法,
- optimize table test30 final;
这是让表合并数据,是分区内合并,不会夸分区 - select * from test30 final settings do_not_merge_across_partitions_select_final = 1;
这是让表查询结果合并数据,不会影响到存储的数据,单次请求有效,不会夸分区合并数据 - select * from test30 final settings do_not_merge_across_partitions_select_final = 0;
或夸分区合并数据 - select * from test30 final
等价于 settings do_not_merge_across_partitions_select_final = 0的情况 - select final 只对 ReplacingMergeTree 之类可以合并的引擎能用
- 注意 ReplacingMergeTree 和 ReplicatedMergeTree
ReplacingMergeTree 是去重的,并且只有ck执行合并以后才能保证去重,也可以通过 group by + argMax 函数 然后取最新版本拿到唯一的数据
ReplicatedMergeTree 是数据副本,数据备份 - clikchouse的 副本机制 ,需要依赖zk,并且每个节点是对等的,也就是没有主次之分
使用的 Replicated开头的表引擎就会自动同步副本,然后指定 zk的 同步路径,和当前的节点标志
配置文件修改
vi config.xml,查询 zookeeper 相关的配置,去掉注释,修改为你的zk地址
然后把这个配置同步到所有副本节点
创建副本表
在副本表的任意节点的修改操作都会同步到其他节点
- 可以报 节点标记写到宏配置里面然后直接引用
- clikchouse的 分布式表
clickhouse的分布式表是关联到 分配了集群的 mergeTree 表上的,由一个分布式表来映射对账 子表。
分布式表的两种数据同步方式
节点规划,因为分布式数据表和分布式表都会自动在所有节点创建,所以需要不同节点的宏定义中写入分片编号和副本编号
我只有三台虚拟机,数据分成2片,第一片数据有两个副本,第二片数据只有一个副本
clickhouse 的分布式表不能再一个节点上不是 多份数据分片,自动创建表依赖于宏,一份配置文件不能一个宏变量在一份配置文件中只能取到一个固定的分片编号。如果一定要在一台机子上部署两个数据分片,只能在这台机子上启动两个服务进程,这样有两份配置文件。
分片标号 | 副本编号 | |
vm200 | 1 | replica-01 |
vm201 | 1 | replica-02 |
vm202 | 2 | replica-01 |
修改配置文件 confi.xml
同步配置搭配所有所有节点,然后重启服务
创建数据表和分布式表
- argMax 函数和 argMin函数
argMax(第一个参数取值,第一个参数是 max比较字段)argMax
- clickhouse的多维分组统计
with rollup,和别的数仓一样,按照分组维度递减的多维度统计
withcube , 和别的数据一样,任意分组字段组合的多维度统计
with total,ck独有,hive 没有,两个维度统计分组信息,一个全部字段,一个空(只分一组) - explain 查看执行计划,由4种类型的
- ast:查看语法树
- syntax:查询优化语法
- plan:用于查看执行计划,默认值,有额外小参数
- header 默认关闭
- description 默认开启
- actions 默认关闭
- pipeline:查看 pipeline 计划,有额外小参数
- header 默认关闭
- graph 默认关闭
- actions 默认开启
- using(userId) 等价于 a.userId = b.userId
using相当于自然连接,指定通过两边同名的字段连接
- clickhouse支持 视图,并且支持物化实体
ck的视图和别的数仓一样
ck的物化视图和hive 的不一样,不会被在查询原表的时候自动使用,需要查询指定物化视图,并且ck的物化视图会自动更新,不需要手动更新,hive 的物化视图需要手动触发更新
- 参数配置
位于中文文档的操作-->服务器配置参数-->服务器设置 页面,英文文档位置不同
配置文档地址: https://clickhouse.com/docs/zh/operations/server-configuration-parameters/settings - ck 的函数
函数很多 ,sql 参考下面 的 函数,集合函数,表函数3个目录下面 - clickhouse的一些优化建议
- clickhouse比较吃cpu,cpu 50%占用会出现明显的查询波动,70%会出现大量查询超时。
所以建议clickhouse单独部署 - 谓词下推
ck 的join不支持 等于以外的表达式,所以没有join和where的谓词下推,但是有有子查询 union all 等情况的谓词下推 - ck 的 join效率比较低,是用左表和缓存的右表一一比对
所以需要把小表写在后面,并且后面的表太大超出内存会直接提示内存不够
有些join可以考虑用in代替,比如只要左边数据的join - ck单个查询支持多线程,不同的分区可以用不同的线程运行,并且ck 默认吃满所有核心。单个查询效率极快,但是支持的并发比较少,默认是限制的100个。不建议设置太多。ck能支持海量数据的查询,但是不支持海量连接的并发。
为了避免单个查询占用太多资源,可以设置一个超时时间 - ck的优化器会优化一些 重复字段之类的低级问题
- ck 里面 使用 有些时候会把where优化 成 prewhere 。prewhere是对 列存数据库的优化,先在一列或者几列查询出结果,然后关联需要显示的行信息,这种优化是列存数据库独有的。
- 标量替换
如果子查询只返回一行数据,那么在引用的时候会使用标量进行替换,以提高效率
- ck支持海量数据,尽量避免在查询中每行数据都做计算,比如虚拟列,数据处理等,因为即便是简单的数据处理做了一亿次效率也会变低
- ck支持海量数据,要避免使用不分页的查询,不然会写数据会造成IO阻塞
- 去重后求数量,count( distinct xxx ) 可用使用 uniqCombined(xxx)代替,但是 uniqCombined 取得的结果是一个近似值,效率高很多
- 物化视图会占用一份空间,所以不要疯狂使用
- 分布式表join 和in 查询 ,要加global ,减少查询放大
会减少查询后表的次数,改为查询一次,然后传递给别的分布式节点 - ck 有字典表,基本常驻内存
- ck的存储不支持多目录,但是可以通过数据卷的方式支持多块磁盘
- ck的 count 是预先算好存下来的,所以 count的时候不带字段或者 count(*)效率最高