Clickhouse(5)---Clickhouse语法

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 = 32021-10-27 14:10:321 │
└──────────┴───────────┴─────────────────────┴─────────────────────┴─────────┘
字段含义
database库名
table表名
command更新/删除语句
create_timemutation任务创建时间,系统按这个时间顺序处理数据变更
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>

和表得物理文件在同一个目录
在这里插入图片描述

  • 2
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Abner G

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值