Clickhouse语法
- DDL基础
- 创建表
- 方式1
- 方式2
- 方式3
- 集群创建表+副本
- 修改表
- DML基础
- 插入数据
- 方式1
- 方式2
- 方式3
- 更新删除数据
- 操作数据
- 条件删除数据
- 条件更新数据
- 通过操作分区操作数据
- 创建分区
- 查看分区
- 删除分区
- 复制分区
- 卸载分区
- 装载分区
- 重置分区数据
- 集群清空表数据
- 视图
- 普通视图
- 物化视图
DDL基础
clickhouse建表必须指定引擎!
clickhouse建表必须指定引擎!
clickhouse建表必须指定引擎!
目前只有MergeTree、Merge和Distributed这三类表引擎支持 ALTER修改,所以在进行alter操作的时候注意表的引擎!
创建表
方式1
完整语法
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
column_name1 type [DEFAULT|MATERIALIZED|ALIAS expr],
column_name2 type [DEFAULT|MATERIALIZED|ALIAS expr],
…
) ENGINE = engine
CREATE TABLE IF NOT EXISTS test_alter1
(
`id` Int8,
`name` String default 'abner' comment '用户'
)
ENGINE = MergeTree()
primary key id
order by id
-- 参数设置 索引力度 默认是 8192
SETTINGS index_granularity = 8192;
方式2
在不同的数据库之间复制表结构
完整语法
CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name1 AS [db_name2.]table_name2 [ENGINE = engine]
-- 将 A 库下的 a 表拷贝一份到 B 库下的 b 表, 注意:引擎可以更换
CREATE TABLE IF NOT EXISTS A.a AS B.b ENGINE = TinyLog
方式3
根据 SELECT 子句建立相应的表结构。
这种方式还会将 SELECT 子句查询的数据一起写入。
CREATE TABLE [IF NOT EXISTS] [db_name].table_name ENGINE = engine AS SELECT …
CREATE TABLE IF NOT EXISTS db.not_exists_table ENGINE = Memory AS SELECT * FROM db.exists_table
集群创建表+副本
修改表
只有 MergeTree支持表结构的修改
-- 添加字段
alter table test_alter1 add column age UInt8 ;
alter table test_alter1 add column gender String after name ;
-- 删除字段
alter table test_alter1 drop column age ;
-- 修改表字段类型
alter table test_alter1 modify column gender UInt8 default 0 ;
-- 修改表字段注解
alter table test_alter1 comment column name '用户名' ;
-- 修改表名
rename table test_alter1 to t1 ;
-- 修改多张表名
rename table test_alter1 to t2 , t1 to tt1 ;
-- 移动表到另一数据库中
rename table t2 to test1.t ;
-- 查看数据库下的所有的表
show tables ;
show tables from cktest ;
DML基础
插入数据
方式1
VALUES格式的常规语法
insert into test_alter values ( 1,'abner'),(2,'candy'),(3,'Kim'),( 1,'abner'),(2,'candy'),(3,'Kim');
方式2
这种方式必须指定数据库
文件导入,这里使用csv导入。【支持的数据文件格式](https://clickhouse.com/docs/en/interfaces/formats/#formats)】
cat user.txt | clickhouse-client -q 'insert into cktest.test_alter format CSV
clickhouse-client -q 'insert into cktest.test_alter format CSV' < user.txt
可以执行数据行属性的分割符
clickhouse-client --format_csv_delimiter='-' -q 'insert into default.test_load1 format CSV' < user.txt
方式3
INSERT INTO [db.]table [(c1, c2, c3…)] SELECT …
insert into test_alter2 select * from test_alter;
图中表一用传统VALUES语句分两次插入12条数据,表二是使用方式3一次插入12条数据。发现在文件中一次插入12条的列文件,比分两次插入12条的列文件小。
表1因为两次插入是两个数据块,表2是一个数据块。
ClickHouse内部所有的数据操作都是面向Block数据块的,所以INSERT查询最终会将数据转换为Block数据块。也正因如此,INSERT语句在单个数据块的写入过程中是具有原子性的。在默认的情况下,每个数据块最多可以写入1048576行数据(由max_insert_block_size参数控制)。也就是说,如果一条INSERT语句写入的数据少于max_insert_block_size行,那么这批数据的写入是具有原子性的,即要么全部成功,要么全部失败。需要注意的是,只有在ClickHouse服务端处理数据的时候才具有这种原子写入的特性,例如使用JDBC或者HTTP接口时。因为max_insert_block_size参数在使用CLI命令行或 者INSERT SELECT子句写入时是不生效的。
更新删除数据
只有MergeTree引擎的数据才能修改,更新功能不支持更新有关主键或分区键的列
操作数据
Clickhouse通过alter方式实现更新、删除,它把update、delete操作叫做mutation(突变)。
mutation 官方链接mutation与标准的update、delete区别
标准SQL的更新、删除操作是同步的,即客户端要等服务端反回执行结果(通常是int值);而Clickhouse的update、delete是通过异步方式实现的,当执行update语句时,服务端立即反回,但是实际上此时数据还没变,而是排队等着。
它不支持事务,一旦语句被提交执行,就会立刻对现有数据产生影响,无法回滚。
语法
ALTER TABLE [db.]table DELETE WHERE filter_expr
ALTER TABLE [db.]table UPDATE column1 = expr1 [, …] WHERE filter_expr
查看mutation队列
SELECT
database,
table,
command,
create_time,
is_done
FROM system.mutations
LIMIT 10
┌─database─┬─table─────┬─command─────────────┬─────────create_time─┬─is_done─┐
│ cktest │ test_muta │ DELETE WHERE id = 3 │ 2021-10-27 14:10:32 │ 1 │
└──────────┴───────────┴─────────────────────┴─────────────────────┴─────────┘
字段 | 含义 |
database | 库名 |
table | 表名 |
command | 更新/删除语句 |
create_time | mutation任务创建时间,系统按这个时间顺序处理数据变更 |
is_done | 是否完成,1为完成,0为未完成 |
Mutation具体过程
首先,使用where条件找到需要修改的分区;
然后,重建每个分区,用新的分区替换旧的,分区一旦被替换,就不可回退;
对于每个分区,可以认为是原子性的;但对于整个mutation,如果涉及多个分区,则不是原子性的。注意事项
更新功能不支持更新有关主键或分区键的列
更新操作没有原子性,即在更新过程中select结果很可能是一部分变了,一部分没变,从上边的具体过程就可以知道
更新是按提交的顺序执行的
更新一旦提交,不能撤销,即使重启clickhouse服务,也会继续按照system.mutations的顺序继续执行
已完成更新的条目不会立即删除,保留条目的数量由finished_mutations_to_keep存储引擎参数确定。 超过数据量时旧的条目会被删除
更新可能会卡住,比如update intvalue='abc’这种类型错误的更新语句执行不过去,那么会一直卡在这里,此时,可以使用KILL MUTATION来取消
作者:user0650 原文地址 链接:https://www.jianshu.com/p/521f2d1611f8
来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
条件删除数据
alter table test_muta delete where id=3 ; -- 一定加条件
条件更新数据
ALTER TABLE test_muta
UPDATE name = 'abner' WHERE id = '2' ;
通过操作分区操作数据
分区是表的分区,具体的DDL操作关键词是 PARTITION BY,指的是一个表按照某一列数据(比如日期)进行分区,对应到最终的结果就是不同分区的数据会写入不同的文件中。
创建分区
partition by 关键字
create table test_muta(
id UInt8 ,
name String ,
city String
)engine=MergeTree()
partition by city
order by id ;
查看分区
SELECT * FROM test_muta;
┌─id─┬─name──┬─city─┐
│ 4 │ kim │ js │
│ 5 │ candy │ js │
└────┴───────┴──────┘
┌─id─┬─name────┬─city─┐
│ 1 │ guochao │ sh │
└────┴─────────┴──────┘
┌─id─┬─name───┬─city─┐
│ 7 │ wallen │ sh │
└────┴────────┴──────┘
┌─id─┬─name──┬─city─┐
│ 2 │ abner │ gz │
│ 6 │ Bob │ gz │
└────┴───────┴──────┘
-- 系统表查看分区
SELECT
name,
table,
partition
FROM system.parts
WHERE table = 'test_muta';
┌─name─────────────────────────────────────┬─table─────┬─partition─┐
│ 2ce0f00e3f89ed58e2dadca87cc37b1c_4_4_0_6 │ test_muta │ js │
│ 639d654382e2ba7be8162fd75112ebd1_1_1_0_6 │ test_muta │ sh │
│ 8524469df63406197e2087b37543e596_2_2_0_6 │ test_muta │ gz │
└──────────────────────────────────────────┴───────────┴───────────┘
添加新数据,合并分区
insert into test_muta values (7,'wallen','sh');
SELECT * FROM test_muta;
┌─id─┬─name──┬─city─┐
│ 4 │ kim │ js │
│ 5 │ candy │ js │
└────┴───────┴──────┘
┌─id─┬─name────┬─city─┐
│ 1 │ guochao │ sh │
└────┴─────────┴──────┘
┌─id─┬─name───┬─city─┐
│ 7 │ wallen │ sh │
└────┴────────┴──────┘
┌─id─┬─name──┬─city─┐
│ 2 │ abner │ gz │
│ 6 │ Bob │ gz │
└────┴───────┴──────┘
-- 合并分区
optimize table test_muta;
SELECT * FROM test_muta;
┌─id─┬─name──┬─city─┐
│ 4 │ kim │ js │
│ 5 │ candy │ js │
└────┴───────┴──────┘
┌─id─┬─name────┬─city─┐
│ 1 │ guochao │ sh │
│ 7 │ wallen │ sh │
└────┴─────────┴──────┘
┌─id─┬─name──┬─city─┐
│ 2 │ abner │ gz │
│ 6 │ Bob │ gz │
└────┴───────┴──────┘
删除分区
-- 删除分区
alter table test_mutadrop partition 'sh' ;
-- 删除分区以后 , 分区中的所有的数据全部删除
复制分区
clickHouse支持将A表的分区数据复制到B表,这项特性可以用于快速数据写入、多表间数据同步和备份等场景。
不过需要注意的是,并不是任意数据表之间都能够相互复制,它们还需要满足两个前提 条件:
·两张表需要拥有相同的分区键
·它们的表结构完全相同。
它的完整语法如下:
-- 复制分区(两张表需要拥有相同的分区键,它们的表结构完全相同)
alter table test_muta_back replace partition 'sh' from test_muta;
select * from test_muta_back ;
┌─id─┬─name────┬─city─┐
│ 1 │ guochao │ sh │
│ 7 │ wallen │ sh │
└────┴─────────┴──────┘
卸载分区
表分区可以通过DETACH语句卸载,分区被卸载后,它的物理数据并没有删除,而是被转移到了当前数据表目录的detached子目录下。而装载分区则是反向操作,它能够将detached子目录下的某个分区重新装载回去。卸载与装载这一对伴生的操作,常用于分区数据的迁移和备份场景。
-- 卸载分区
alter table test_muta detach partition 'sh';
SELECT * FROM test_muta;
┌─id─┬─name──┬─city─┐
│ 4 │ kim │ js │
│ 5 │ candy │ js │
└────┴───────┴──────┘
┌─id─┬─name──┬─city─┐
│ 2 │ abner │ gz │
│ 6 │ Bob │ gz │
└────┴───────┴──────┘
装载分区
alter table test_muta attach partition 'sh';
SELECT * FROM test_muta;
┌─id─┬─name──┬─city─┐
│ 4 │ kim │ js │
│ 5 │ candy │ js │
└────┴───────┴──────┘
┌─id─┬─name────┬─city─┐
│ 1 │ guochao │ sh │
│ 7 │ wallen │ sh │
└────┴─────────┴──────┘
┌─id─┬─name──┬─city─┐
│ 2 │ abner │ gz │
│ 6 │ Bob │ gz │
└────┴───────┴──────┘
重置分区数据
如果数据表某一列的数据有误,需要将其重置为初始值,如果设置了默认值那么就是默认值数据,如果没有设置默认值,系统会给出默认的初始值,此时可以使用下面的语句实现:
ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr ;
注意: 不能重置主键和分区字段
alter table test_muta clear column name in partition 'sh' ;
SELECT * FROM test_muta;
┌─id─┬─name──┬─city─┐
│ 4 │ kim │ js │
│ 5 │ candy │ js │
└────┴───────┴──────┘
┌─id─┬─name─┬─city─┐
│ 1 │ │ sh │
│ 7 │ │ sh │
└────┴──────┴──────┘
┌─id─┬─name──┬─city─┐
│ 2 │ abner │ gz │
│ 6 │ Bob │ gz │
└────┴───────┴──────┘
集群清空表数据
-- TRUNCATE TABLE 表名 on cluster 集群名
TRUNCATE TABLE enterprise.company_info_125city on cluster perftest_1shards_3replicas;
视图
ClickHouse拥有普通和物化两种视图,其中物化视图拥有独立的存储**,而普通视图只是一层简单的查询代理**
普通视图
普通视图不会存储任何数据,它只是一层单纯的SELECT查询映射,起着简化查询、明晰语义的作用,对查询性能不会有任何增强。
语法:
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT …
-- 普通视图
create view test_muta_view as select * from test_muta where city='gz';
select * from test_muta_view;
┌─id─┬─name──┬─city─┐
│ 2 │ abner │ gz │
│ 6 │ Bob │ gz │
└────┴───────┴──────┘
物化视图
其实物化视图就是一种特殊的表
物化视图支持表引擎,数据保存形式由它的表引擎决定,创建物化视图的完整语法如下所示
create materialized view mv_log engine=Log populate as select * from log ;
物化视图创建好之后,如果源表被写入新数据,那么物化视图也会同步更新。POPULATE修饰符决定了物化视图的初始化策略:如果使用了POPULATE修饰符,那么在创建视图的过程中,会连带将源表中已存在的数据一并导入,如同执行了INTO SELECT 一般;反之,如果不使用POPULATE修饰符,那么物化视图在创建之后是没有数据的,它只会同步在此之后被写入源表的数据。物化视图目前并不支持同步删除,如果在源表中删除了数据,物化视图的数据仍会保留。
create materialized view test3_view engine = Log populate as select * from tb_test3 ;
-- 建表的时候同步数据 , 当数据更新以后 物化视图中的数据会同步更新 , 但是当删除数据以后,物化视图中的数据不会被删除
SELECT *
FROM test3_view ;
┌─id─┬─name───┬─role─┐
│ 1 │ HANGGE │ VIP │
│ 2 │ BENGE │ VIP │
│ 3 │ PINGGE │ VIP │
└────┴────────┴──────┘
-- 向源表中擦混入数据
SELECT *
FROM test3_view
┌─id─┬─name──┬─role─┐
│ 4 │ TAOGE │ VIP │
└────┴───────┴──────┘
┌─id─┬─name───┬─role─┐
│ 1 │ HANGGE │ VIP │
│ 2 │ BENGE │ VIP │
│ 3 │ PINGGE │ VIP │
└────┴────────┴──────┘
-- 删除源表中的数据 , 物化视图中的数据 不会变化 ****
-- 注意: 数据删除语法只适用于MergeTree引擎的表 基本语法如下
ALTER TABLE db_name.table_name DROP PARTITION '20210601'
ALTER TABLE db_name.table_name DELETE WHERE day = '20210618'
ALTER TABLE <table_name> UPDATE col1 = expr1, ... WHERE <filter>
和表得物理文件在同一个目录