clickhouse的存储引擎很多，主要用到的有以下三种
MergeTree，是CK里最Advanced的引擎，性能超高，本地表
ReplicatedMergeTree，基于MergeTree，同时引入ZK，做了复制，分布式表
Distributed，分布式引擎，本身不存储数据，相当于mysql的merge引擎，如果写入，会把请求丢到集群里的节点，如果查询，会帮你做查询转发再聚合返回
1，查看版本号
[tidb@testjian ~]$ clickhouse-client -u default --password netjoy1234 -h127.0.0.1 -m ClickHouse client version 20.8.3.18. Connecting to 127.0.0.1:9000 as user default. Connected to ClickHouse server version 20.8.3 revision 54438. testjian :-] select version(); SELECT version() ┌─version()─┐ │ 20.8.3.18 │ └───────────┘ 1 rows in set. Elapsed: 0.001 sec.
注意clickhouse-client参数中的-m，加了后执行sql时，可以多行执行。
2，创建数据库
testjian :-] select * from system.clusters; SELECT * FROM system.clusters //查集群名称 ┌─cluster────────────────┬─shard_num─┬─shard_weight─┬─replica_num─┬─host_name──┬─host_address─┬─port─┬─is_local─┬─user────┬─default_database─┬─errors_count─┬─estimated_recovery_time─┐ │ clickhouse_test_netjoy │ 1 │ 1 │ 1 │ 10.0.55.17 │ 10.0.55.17 │ 9000 │ 0 │ default │ │ 0 │ 0 │ │ clickhouse_test_netjoy │ 1 │ 1 │ 2 │ 10.0.10.23 │ 10.0.10.23 │ 9000 │ 1 │ default │ │ 0 │ 0 │ │ clickhouse_test_netjoy │ 2 │ 1 │ 1 │ 10.0.55.16 │ 10.0.55.16 │ 9000 │ 0 │ default │ │ 0 │ 0 │ │ clickhouse_test_netjoy │ 2 │ 1 │ 2 │ 10.0.10.24 │ 10.0.10.24 │ 9000 │ 0 │ default │ │ 0 │ 0 │ └────────────────────────┴───────────┴──────────────┴─────────────┴────────────┴──────────────┴──────┴──────────┴─────────┴──────────────────┴──────────────┴─────────────────────────┘ 4 rows in set. Elapsed: 0.001 sec. testjian :-] create database netjoytest1 ON CLUSTER clickhouse_test_netjoy; CREATE DATABASE netjoytest1 ON CLUSTER clickhouse_test_netjoy ┌─host───────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐ │ 10.0.55.17 │ 9000 │ 0 │ │ 3 │ 0 │ │ 10.0.10.24 │ 9000 │ 0 │ │ 2 │ 0 │ │ 10.0.10.23 │ 9000 │ 0 │ │ 1 │ 0 │ │ 10.0.55.16 │ 9000 │ 0 │ │ 0 │ 0 │ └────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘ et. Elapsed: 0.159 sec.
注意ON CLUSTER clickhouse_test_netjoy，加了这个后在集群中的每个节点都会创建这个数据库。在节点多的时候非常有用。
3，创建表
testjian :-] CREATE TABLE netjoytest1.tanktest ON CLUSTER clickhouse_test_netjoy
:-] (
:-] `id` UInt8,
:-] `name` String,
:-] `create_time` DateTime
:-] )
:-] ENGINE = MergeTree()
:-] PARTITION BY toYYYYMM(create_time)
:-] PRIMARY KEY id
:-] ORDER BY (id, create_time);
CREATE TABLE netjoytest1.tanktest ON CLUSTER clickhouse_test_netjoy
(
`id` UInt8 default 0 comment 'id',
`name` Nullable(String) comment '名字',
`create_time` DateTime default toDateTime(now()) comment '创建时间'
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(create_time)
PRIMARY KEY id
ORDER BY (id, create_time)
┌─host───────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ 10.0.55.17 │ 9000 │ 0 │ │ 3 │ 0 │
│ 10.0.10.24 │ 9000 │ 0 │ │ 2 │ 0 │
│ 10.0.10.23 │ 9000 │ 0 │ │ 1 │ 0 │
│ 10.0.55.16 │ 9000 │ 0 │ │ 0 │ 0 │
└────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘
4 rows in set. Elapsed: 0.158 sec.
testjian :-] desc tanktest4;
DESCRIBE TABLE tanktest4
┌─name────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment──┬─codec_expression─┬─ttl_expression─┐
│ id │ UInt8 │ DEFAULT │ 0 │ id │ │ │
│ name │ Nullable(String) │ │ │ 名字 │ │ │
│ create_time │ DateTime │ DEFAULT │ toDateTime(now()) │ 创建时间 │ │ │
└─────────────┴──────────────────┴──────────────┴────────────────────┴──────────┴──────────────────┴────────────────┘
3 rows in set. Elapsed: 0.001 sec.
建集群表的前提，每个集群节点都有该数据库。如果集群节点没有库的表建不成功。如果去掉ON CLUSTER ，只在当前结点。如果没有primary key，order by后面的字段会被设置成主键。
clickhouse命令行操作，很多根mysql差不多。熟悉mysql，在使用层面上手clickhouse很快。
4，增，删，改，查
insert into tanktest2 values(1, 'tank', '2021-08-19 14:21:30'); insert into tanktest values(2, 'test1', '2021-08-19 14:22:30'); insert into tanktest values(2, 'test2', '2021-08-19 14:22:30'); insert into tanktest4 (id,name) values(1, 'test1'); insert into netjoytest.tanktest1 select * from netjoytest.tanktest; alter table tanktest update name='test12' where id = 2; alter table tanktest delete where id = 2;
clickhouse的主键是可重复的，更新和删除写法和标准的sql不太一样。
5，复制表
//复制表结构 create table if not exists netjoytest.tanktest1 as netjoytest.tanktest; //复制表结构和数据 create table if not exists netjoytest.tanktest2 engine=MergeTree partition by toYYYYMM(create_time) primary key id order by (id,create_time) as select * from netjoytest.tanktest;
复制表的前提，表结构都要一样
6，表结构修改
alter table tanktest drop column mobile; //删除列 alter table tanktest comment column name '姓名'; //添加comment alter table tanktest add column age UInt8 default 0 comment '年龄' after name; //添加列 alter table tanktest modify column age UInt16; //字段类型修改 alter table tanktest clear column tel; //清空列，值会变成默认值 alter table tanktest rename COLUMN mobile to tel; //这里的column必须大写，字段改名
clickhouse不需要设置字段长度，不同的类型对应的长度不一样。
整型范围
Int8-[-128:127]
Int16-[-32768:32767]
Int32-[-2147483648:2147483647]
Int64-[-9223372036854775808:9223372036854775807]
无符号整型范围
UInt8-[0:255]
UInt16-[0:65535]
UInt32-[0:4294967295]
UInt64-[0:18446744073709551615]
主键改类型很麻烦，不建议换主键类型。换主键类型会报以下错误。
Received exception from server (version 20.8.3):
Code: 524. DB::Exception: Received from 127.0.0.1:9000. DB::Exception: ALTER of key column id from type UInt8 to type UInt32 must be metadata-only.
7，表改名
rename table tanktest to tanktest6; rename table tanktest6 to netjoytest1.tanktest3;
8，创建视图
#普通视图 CREATE VIEW testview AS SELECT * from tanktest; #物化视图 create materialized view real_test_view engine=MergeTree partition by toYYYYMM(create_time) primary key id order by (id,create_time) populate as select * from tanktest2;
物化视图存储数据，查询数据时不用到原来表里面查数据了，直接在视图里面查就好了，并且原表数据更新会同步到视图中，这对bi来说特别的友好。性能比普通视图快很多。
9，ttl过期时间属性
testjian :-] CREATE TABLE netjoytest1.tanktest6 ( `id` UInt8 default 0 comment 'id', `name` Nullable(String) comment '名字' TTL create_time + INTERVAL 1 MONTH, `create_time` DateTime default toDateTime(now()) comment '创建时间' ) engine = MergeTree() partition by toYYYYMM(create_time) primary key id order by (id,create_time); testjian :-] desc tanktest6; DESCRIBE TABLE netjoytest1.tanktest6 ┌─name────────┬─type─────────────┬─default_type─┬─default_expression─┬─comment──┬─codec_expression─┬─ttl_expression───────────────────┐ │ id │ UInt8 │ DEFAULT │ 0 │ id │ │ │ │ name │ Nullable(String) │ │ │ 名字 │ │ create_time + toIntervalMonth(1) │ │ create_time │ DateTime │ DEFAULT │ toDateTime(now()) │ 创建时间 │ │ │ └─────────────┴──────────────────┴──────────────┴────────────────────┴──────────┴──────────────────┴──────────────────────────────────┘ //修改字段的ttl属性 testjian :-] ALTER TABLE netjoytest1.tanktest2 MODIFY COLUMN name String TTL create_time + INTERVAL 1 DAY;
name这个列的数据保留1个月，住意create_time，以插入时间为基准。也就是说没有时间字段，用不了ttl过期属性。
TTL不能用于主键，TTL不能用于主键
CREATE TABLE netjoytest1.tanktest4
(
`id` UInt8 default 0 comment 'id',
`name` Nullable(String) comment '名字' ,
`create_time` DateTime default toDateTime(now()) comment '创建时间'
)
engine = MergeTree()
partition by toYYYYMM(create_time)
primary key id
order by (id,create_time)
TTL create_time + INTERVAL 1 MONTH DELETE;
整表的数据只保留一个月，TTL有一个比较适用的场景就是日志表，一个月前的日志我就不要了。
10，分片表
testjian :-] select * from tanktest3; SELECT * FROM tanktest3 ┌─id─┬─name─┬─────────create_time─┐ │ 1 │ tank │ 2021-08-19 14:21:30 │ └────┴──────┴─────────────────────┘ ┌─id─┬─name──┬─────────create_time─┐ │ 2 │ test1 │ 2021-08-19 15:22:30 │ └────┴───────┴─────────────────────┘ ┌─id─┬─name──┬─────────create_time─┐ │ 3 │ test1 │ 2021-07-19 14:22:30 │ └────┴───────┴─────────────────────┘ ┌─id─┬─name──┬─────────create_time─┐ │ 4 │ test1 │ 2021-06-19 14:22:30 │ └────┴───────┴─────────────────────┘ ┌─id─┬─name──┬─────────create_time─┐ │ 4 │ test1 │ 2021-06-19 14:22:30 │ └────┴───────┴─────────────────────┘ 5 rows in set. Elapsed: 0.002 sec. testjian :-] optimize table tanktest3; //优化表会合并分片数据 OPTIMIZE TABLE tanktest Ok. 0 rows in set. Elapsed: 0.001 sec. testjian :-] select * from tanktest3; SELECT * FROM tanktest3 ┌─id─┬─name──┬─mobile─┬─────────create_time─┐ │ 3 │ test1 │ 0 │ 2021-07-19 14:22:30 │ └────┴───────┴────────┴─────────────────────┘ ┌─id─┬─name──┬─mobile─┬─────────create_time─┐ │ 4 │ test1 │ 0 │ 2021-06-19 14:22:30 │ │ 4 │ test1 │ 0 │ 2021-06-19 14:22:30 │ └────┴───────┴────────┴─────────────────────┘ ┌─id─┬─name──┬─mobile─┬─────────create_time─┐ │ 1 │ tank │ 0 │ 2021-08-19 14:21:30 │ │ 2 │ test1 │ 0 │ 2021-08-19 15:22:30 │ └────┴───────┴────────┴─────────────────────┘ 5 rows in set. Elapsed: 0.002 sec.
clickhouse，没有自增主键，，没有自增主键，没有自增主键，不支持auto_increment。
//查看分片 testjian :-] select path,database,table,name,partition from system.parts where database = 'netjoytest1' and table='tanktest3'; //删除分片 testjian :-] alter table netjoytest1.tanktest3 drop partition '202106';
删除分片后，该分片数据会丢失，并且剩下的分片会进行合并
转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/clickhouse/2520.html