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