clickhous用法总结

标签：总结 -- create 用法 age clickhous table id clickhouse

clickhose的安装
安装参考：https://www.cnblogs.com/cxygg/p/18129193
clickhouse的文档支持中文，但是中文文档更新比英文的慢
clickhouse本质上是一个数据分析工具，适合用来做数仓，它是列存数据库，擅长数据分析，不擅长数据修改，和hive类似，但是比hive 快的多。所以在数据量不是特别大的数仓情况下，可以考虑clickhouse。
他的优点在于独立部署不需要依赖其他分部署数据存储和分布式计算框架，部署简单，单机就可以维护10亿级别的数据。
缺点在于它比较吃单机硬件资源，而且是 cpu 内存硬盘都吃，尤其是cpu。
clickhose单机可以承载数十亿数据
ck 不支持事务
clickhouse-client会显示默认分组信息，别的客户端比如 DBeaver不会显示

启动服务和启动客户端

#停止服务
systemctl stop  clickhouse-server

#启动服务器
systemctl stop  clickhouse-server

可执行文件位于 /usr/bin

[root@localhost bin]# find ./ -name "clickhouse*"
./clickhouse
./clickhouse-odbc-bridge
./clickhouse-library-bridge
./clickhouse-keeper-client
./clickhouse-server
./clickhouse-git-import
./clickhouse-extract-from-config
./clickhouse-keeper
./clickhouse-keeper-converter
./clickhouse-disks
./clickhouse-format
./clickhouse-benchmark
./clickhouse-client
./clickhouse-local
./clickhouse-compressor
./clickhouse-obfuscator

核心配置文件位置
server配置文件位置：/etc/clickhouse-server/
客户端配置目录：/etc/clickhouse-client/

[root@localhost clickhouse-server]# pwd
/etc/clickhouse-server
[root@localhost clickhouse-server]# ll
total 88
dr-x------. 2 clickhouse clickhouse     6 Apr 10 23:49 config.d
-r--------. 1 clickhouse clickhouse 79513 Apr 10 23:49 config.xml
dr-x------. 2 clickhouse clickhouse     6 Apr 10 23:49 users.d
-r--------. 1 clickhouse clickhouse  5672 Apr 10 23:49 users.xml

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更高
```
CREATE DATABASE mysql_db ENGINE = MySQL('localhost:3306', 'test', 'my_user', 'user_password')
```
使用表引擎创建clickhouse的表
主键和 order by 至少要指定一个

只指定排序字段的时候，主键就是排序字段，只指定主键的时候，排序字段就是主键

不指定表引擎默认是ENGINE = MergeTree，如果要指定表引擎，需要写在第一行
主键和排序的顺序无所谓
```
-- 需要注意的是 除了关键字以外，数据类型，表引擎的名字都是大驼峰风格，并且大小写敏感。
create table test1
(
	id Int32,
	name String,
	create_date DateTime
)
engine = MergeTree()
order by (id,name)
primary key(id)
```
clickhouse的主键必须是排序字段前面一部分(或者全部),clickhouse里面排序字段比主键更加重要
ReplacingMergeTree，SummingMergeTree的判断重复依据都是排序字段，而不是主键字段
```
-- 主键不是排序字段前面一部分是不被允许的
create table test5
(
	id Int32,
	name String,
	create_date DateTime
)
primary key(name)
order by (id,name)
```
clickhouse的字符串只能用单引号，不能用双引号

数据插入和查询

--插入数据
insert into table test values(1,'张三',now()),(2,'李四',now()),(3,'王五',now());


-- 查询数据
select *  from test;

数据的修改和删除

-- 删除
delete from test where id = 1;
--老版本语法
alter table test delete   where id = 1;


-- 修改字段
update  test set create_date = now() where id =2;
-- 老版本语法
alter table test update create_date = now() where id =2;

-- 但是不能修改主键和排序字段
-- 会提示：Cannot UPDATE key column `id`
alter table test update id = 100 where id =2;

即便不支持修改的数仓工具也可以通过版本和假删除来实现修改和删除效果

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 and Float64) and Decimal values
- Boolean: ClickHouse has a Boolean type
- Strings: String and FixedString
- Dates: use Date and Date32 for days, and DateTime and DateTime64 for instances in time
- JSON: the JSON object stores a JSON document in a single column
- UUID: a performant option for storing UUID values
- 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 of values
- 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 data structure is like a table inside a cell
- Tuples: A Tuple of elements, 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 and MultiPolygon
- Special data types: including Expression, Set, Nothing and [Interval]

查询表的定义

#能看到字段定义
desc table test2;

#能看到表的定于语句，比desc 看到的信息更多
show table test2;

表的分区，一般建议按天分区

-- 分区，一般按照天分区，分区字段不能用合并版本字段
create table test15
(
	id Int32,
	name String,
	age Int32 ,
	create_date DateTime
)
engine = SummingMergeTree(age)
partition by toYYYYMMDD(create_date)
primary key(id);


-- 也可以考虑hash 取模分区
create table test14
(
	id Int32,
	name String,
	age Int32 ,
	create_date DateTime
)
engine = SummingMergeTree(age)
partition by modulo(cityHash64(name),8)
primary key(id);

主键不是唯一约束，只是一级索引
ReplacingMergeTree 可以实现最终一致性，效果类似唯一索引，但是需要合并数据以后才才能在分区内唯一

clickhouse的的二级索引（跳数索引）
index indexName xxx字段 type 索引类型 granularity N

-- 二级索引(最大值最小值)
create table test20
(
	id Int32,
	name String,
	age Int32 ,
	create_date DateTime,
	index ageIndex age type minmax granularity  64
)
engine = MergeTree
primary key(id);

-- 二级索引(布隆过滤器)
create table test21
(
	id Int32,
	name String,
	age Int32 ,
	create_date DateTime,
	index ageIndex age type bloom_filter  granularity  64
)
engine = MergeTree
primary key(id);

clickhouse的索引间隔粒度

新版本好像不让指定了
settings index_granularity = 8192

ck 的 ttl,指定这个字段在另一个时间字段的值的基础上间隔多少秒以后删除
支持字段的 ttl ，也支持行的ttl（行的ttl 设置在表上的）
删除不是及时的，字段删除以后变成默认值

-- 字段设置 ttl
create table test4
(
	id Int32,
	name String,
	age Int8 TTL create_date + interval 10 SECOND,
	create_date DateTime
)
engine =MergeTree
primary key(id)
order by (id,name);

INSERT into test4 values(2,'张三',100,now() );

SELECT  * from test4;


-- 表设置ttl
create table test7
(
	id Int32,
	name String,
	age Int8 ,
	create_date DateTime
)
engine =MergeTree
primary key(id)  
order by (id,name) 
TTL create_date + INTERVAL 10  SECOND DELETE;


INSERT into test7 values(2,'张三',100,now() );
SELECT  * from test7;

clickhose的各种 mergeTree 的用途

MergeTree

主要引擎默认的就是它

create table test4
(
	id Int32,
	name String,
	age Int8 TTL create_date + interval 10 SECOND,
	create_date DateTime
)
engine =MergeTree
primary key(id)
order by (id,name);

ReplacingMergeTree(替换合并树)

Replace + ing = Replacing

支持去重，但是是分区内去重，并且只有合并以后才能能避免重复

去重的前提是排序字段完全相等，没有排序字段就按照Id，去重以后取值是 ReplacingMergeTree(age) 指定字段最大的的一条

-- ReplacingMergeTree，可以指定版本字段，也可以不指定，不指定或者版本相同的时候，取最新一条
create table test8
(
	id Int32,
	name String,
	age Int8 ,
	create_date DateTime
)
engine = ReplacingMergeTree(age)
primary key(id)
order by (id,name);

INSERT into test8 values(1,'张三',101,now() );
INSERT into test8 values(1,'张三',101,now() );

SELECT  * from test8;

--手动触发合并
OPTIMIZE TABLE test8 final;

SummingMergeTree(求和合并树)

ck会把重复的数据的指定字段合并起来，检查重复也是在合并的时候才有效
同样需要排序字段相等才合并，如果只有Id的时候是按照Id指定字段合并
注意求和数据溢出会变成负数

-- SummingMergeTree
create table test9
(
	id Int32,
	name String,
	age Int32 ,
	create_date DateTime
)
engine = SummingMergeTree(age)
primary key(id)
order by (id,name);

INSERT into test9 values(1,'张三',101,now() );
INSERT into test9 values(1,'张三',101,now() );
INSERT into test9 values(2,'张三',101,now() );
INSERT into test9 values(1,'张三2',101,now() );

SELECT  * from test9;

--手动触发合并
OPTIMIZE TABLE test9 final;

触发手动合并

--手动触发合并
OPTIMIZE TABLE test9 final;

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地址
然后把这个配置同步到所有副本节点

<zookeeper>
    <node>
        <host>vm200</host>
        <port>2181</port>
    </node>
</zookeeper>

创建副本表

在副本表的任意节点的修改操作都会同步到其他节点

-- 创建副本表
-- 节点1，第一个参数是zookeeper用来存放变化事件的路径，，第二个参数是副本编号
-- 如果第一个参数不一样不能同步数据，如果第二个参数重复就创建表会报重复错误
-- 如果有数据分片可以在 zookeeper 路径中体现
create table test1(id Int32,name String,age Int32,create_date Datetime)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/1-1/test1', '1')
primary key id

--节点2
create table test1(id Int32,name String,age Int32,create_date Datetime)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/1-1/test1', '2')
primary key id

--节点3
create table test1(id Int32,name String,age Int32,create_date Datetime)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/1-1/test1', '3')
primary key id



--节点1 上插入
insert into table test1 values(1,'张三',1,now())

--在三个节点上都能看到数据
select * from test1;


--在节点2上修改数据
update test1 set age = 11 where id = 1;

--在三个节点上都能看到数据
select * from test1;

-- 在节点三上删除
delete  from test1 where id = 1;

可以报节点标记写到宏配置里面然后直接引用

<macros>
    <shard>01</shard>
    <replica>replica-01</replica>
</macros>


create table test1(id Int32,name String,age Int32,create_date Datetime)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/1-{shard}/test1', '{replica}')

primary key id

clikchouse的分布式表
clickhouse的分布式表是关联到分配了集群的 mergeTree 表上的，由一个分布式表来映射对账子表。
分布式表的两种数据同步方式

节点规划，因为分布式数据表和分布式表都会自动在所有节点创建，所以需要不同节点的宏定义中写入分片编号和副本编号
我只有三台虚拟机，数据分成2片，第一片数据有两个副本，第二片数据只有一个副本
clickhouse 的分布式表不能再一个节点上不是多份数据分片，自动创建表依赖于宏，一份配置文件不能一个宏变量在一份配置文件中只能取到一个固定的分片编号。

	分片标号	副本编号
vm200	1	replica-01
vm201	1	replica-02
vm202	2	replica-01

修改配置文件 confi.xml

<!-- 定义集群的地方，本来有个default的集群建议保留,三个节点一样  -->    
	<remote_servers>
        .
        . 其他集群配置省略
        .
        
        

          <!-- 这个标签是定义集群的名字，集群名字不能用中横线，sql不支持 -->
          <test_cluster>
		     <!-- 设置分片1 -->	
                <shard>
                     <!-- 节点间同步 -->
                     <internal_replication>true</internal_replication>
					<!-- 指定权总 -->
                    <weight>2</weight>
                     <!-- 设置副本1 -->
                     <replica>
                          <host>vm200</host>
                          <port>9000</port>
                     </replica>
                     <!-- 设置副本2 -->
                     <replica>
                          <host>vm201</host>
                          <port>9000</port>
                     </replica>
                </shard>
                <!-- 设置分片2 -->			
                <shard>
                     <replica>
                          <host>vm202</host>
                          <port>9000</port>
                     </replica>
                </shard>
                
          </test_cluster>
    </remote_servers>



<!-- 定义宏，这个和上面定义的 shard 和 replica对应 -->

	<!-- 节点1，配置在节点1的配置文件里面 -->
    <macros>
        <shard>01</shard>
        <replica>replica-01</replica>
    </macros>

	<!-- 节点2，配置在节点2的配置文件里面 -->
    <macros>
        <shard>01</shard>
        <replica>replica-02</replica>
    </macros>


	<!-- 节点3，配置在节点3的配置文件里面 -->
    <macros>
        <shard>02</shard>
        <replica>replica-01</replica>
    </macros>

同步配置搭配所有所有节点，然后重启服务

-- 查询集群里面出现test-cluster就没可以了，默认只有一个 default
show clusters;

创建数据表和分布式表


--创建数据表
--在任意节点创建表，别的节点会自动创建
create table test3 on cluster test_cluster(id Int32,name String,age Int32,create_date Datetime)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/test2', '{replica}')
primary key id;

show tables;


--创建分布式表
create table test4 on cluster test_cluster(id Int32,name String,age Int32,create_date Datetime)
ENGINE = Distributed(test_cluster,default,test3,hiveHash(id));


-- 正常值建议只向分布式表插入数据，数据表插入数据会导致数据不一致
insert into table test4 values(5,'张三',5,now());
select * from test4;

argMax 函数和 argMin函数
argMax(第一个参数取值，第一个参数是 max比较字段)argMax
```
-- 取时间最大的 id字段
select argMax(id,create_date)  from test4;
```
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相当于自然连接，指定通过两边同名的字段连接

create table test30
(
	id Int32,
	name String,
	age Int32 ,
	create_date DateTime
)
primary key(id);


insert into table test30 values(1,'name1',5,now());
insert into table test30 values(2,'name2',4,now());
insert into table test30 values(3,'name3',3,now());
insert into table test30 values(4,'name4',2,now());
insert into table test30 values(5,'name5',1,now());

SELECT * from test30;

-- 下面的两种写法等价，on的写法出现2个id，using只会有一个唯一的id
SELECT  *  from test30 t1 left join test30 t2 on t1.id = t2.id;
SELECT  *  from test30 t1 left join test30 t2 using(id)


-- 下面的两种写法等价，on的写法出现2个id和age,using只会有一个唯一的id和age
SELECT  *  from test30 t1 left join test30 t2 on t1.id = t2.id and t1.age = t2.age;
SELECT  *  from test30 t1 left join test30 t2 using(id,age)

clickhouse支持视图，并且支持物化实体
ck的视图和别的数仓一样
ck的物化视图和hive 的不一样，不会被在查询原表的时候自动使用，需要查询指定物化视图，并且ck的物化视图会自动更新，不需要手动更新，hive 的物化视图需要手动触发更新

-- 视图
create view v1 as select * from test30 where id = age;
select * from v1;




-- 物化视图(需要指定主键或者 排序字段)
create Materialized view v2 
primary key id
as select * from test30 where id = age;

-- 物化视图也查不到之前表里面已经有的数据
select * from v2;

-- 物化视图会自动插入原表新插入的数据
insert into table test30 values(6,'name6',6,now());

-- 删除更新不会做级联到物化视图
update test30 set create_date = now() where id=3;
delete from  test30 where id = 3;

-- 物化视图可以直接插入数据
insert into table v2 values(8,'name8',8,now());

-- 如果要物化视图有老表数据
insert into table  v2  select * from  test30 where id = age;

参数配置
位于中文文档的操作-->服务器配置参数-->服务器设置页面，英文文档位置不同

配置文档地址：https://clickhouse.com/docs/zh/operations/server-configuration-parameters/settings
ck 的函数

函数很多，sql 参考下面的函数，集合函数，表函数3个目录下面
clickhouse的一些优化建议
1. clickhouse比较吃cpu，cpu 50%占用会出现明显的查询波动，70%会出现大量查询超时。
  所以建议clickhouse单独部署
2. 谓词下推
  ck 的join不支持等于以外的表达式，所以没有join和where的谓词下推，但是有有子查询 union all 等情况的谓词下推
3. ck 的 join效率比较低，是用左表和缓存的右表一一比对
  所以需要把小表写在后面，并且后面的表太大超出内存会直接提示内存不够
  
  有些join可以考虑用in代替，比如只要左边数据的join
4. ck单个查询支持多线程，不同的分区可以用不同的线程运行，并且ck 默认吃满所有核心。单个查询效率极快，但是支持的并发比较少，默认是限制的100个。不建议设置太多。ck能支持海量数据的查询，但是不支持海量连接的并发。
  为了避免单个查询占用太多资源，可以设置一个超时时间
5. ck的优化器会优化一些重复字段之类的低级问题
6. ck 里面使用有些时候会把where优化成 prewhere 。prewhere是对列存数据库的优化，先在一列或者几列查询出结果，然后关联需要显示的行信息，这种优化是列存数据库独有的。
7. 标量替换
  如果子查询只返回一行数据，那么在引用的时候会使用标量进行替换，以提高效率
```
EXPLAIN SYNTAX
WITH 
 (
 SELECT sum(bytes)
 FROM system.parts
 WHERE active
 ) AS total_disk_usage
SELECT
 (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
 table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;

-- 返回优化后的语句：
WITH CAST(0, \'UInt64\') AS total_disk_usage
SELECT 
 (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
 table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10
```
8. ck支持海量数据，尽量避免在查询中每行数据都做计算，比如虚拟列，数据处理等，因为即便是简单的数据处理做了一亿次效率也会变低
9. ck支持海量数据，要避免使用不分页的查询，不然会写数据会造成IO阻塞
10. 去重后求数量，count( distinct xxx ) 可用使用 uniqCombined(xxx)代替，但是 uniqCombined 取得的结果是一个近似值，效率高很多
11. 物化视图会占用一份空间，所以不要疯狂使用
12. 分布式表join 和in 查询 ,要加global ，减少查询放大
  会减少查询后表的次数，改为查询一次，然后传递给别的分布式节点
13. ck 有字典表，基本常驻内存
14. ck的存储不支持多目录，但是可以通过数据卷的方式支持多块磁盘
15. ck的 count 是预先算好存下来的，所以 count的时候不带字段或者 count(*)效率最高

标签：总结,--,create,用法,age,clickhous,table,id,clickhouse
From： https://www.cnblogs.com/cxygg/p/18133553

MergeTree

ReplacingMergeTree(替换合并树)

SummingMergeTree(求和合并树)

配置文件修改

创建副本表

相关文章

赞助商

阅读排行