clickhouse 创建数据库和本地表

张映 发表于 2021-08-20

分类目录: clickhouse

标签:, , ,

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