前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >ClickHouse DDL

ClickHouse DDL

作者头像
CoderJed
发布2022-01-07 21:12:34
1.2K0
发布2022-01-07 21:12:34
举报
文章被收录于专栏:Jed的技术阶梯

1. 数据库

数据库起到了命名空间的作用,可以有效规避命名冲突的问题,也为后续的数据隔离提供了支撑。任何一张数据表,都必须归属在某个数据库之下。创建数据库的完整语法如下所示:

代码语言:javascript
复制
CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine];

数据库目前一共支持5种引擎,如下所示。

  • Ordinary:默认引擎,在绝大多数情况下我们都会使用默认引擎,使用时无须刻意声明。在此数据库下可以使用任意类型的表引擎。
  • Dictionary:字典引擎,此类数据库会自动为所有数据字典创建它们的数据表。
  • Memory:内存引擎,用于存放临时数据。此类数据库下的数据表只会停留在内存中,不会涉及任何磁盘操作,当服务重启后数据会被清除。
  • Lazy:日志引擎,此类数据库下只能使用Log系列的表引擎。
  • MySQL:MySQL引擎,此类数据库下会自动拉取远端MySQL中的数据,并为它们创建MySQL表引擎的数据表。

在绝大多数情况下都只需使用默认的数据库引擎。例如执行下面的语句,即能够创建属于我们的第一个数据库:

代码语言:javascript
复制
CREATE DATABASE DB_TEST;

默认数据库的实质是物理磁盘上的一个文件目录,所以在语句执行之后,ClickHouse便会在安装路径下创建DB_TEST数据库的文件目录:

代码语言:javascript
复制
# ls -l /var/lib/clickhouse/data/
total 12
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 16 16:44 DB_TEST
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 16 16:45 default
drwxr-x--- 2 clickhouse clickhouse 4096 Aug 16 13:38 system

查看数据库:

代码语言:javascript
复制
show databases;
┌─name────┐
│ DB_TEST │
│ default │
│ system  │
└─────────┘

切换数据库并查看该数据库下的表:

代码语言:javascript
复制
use DB_TEST;
show tables;

删除数据库:

代码语言:javascript
复制
DROP DATABASE [IF EXISTS] db_name;

2. 数据表

常规方法创建表:

代码语言:javascript
复制
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
    name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    ...
) ENGINE = engine;
  • 如果不指定数据库名,则默认把表创建在default数据库下。
  • ENGINE参数,它被用于指定数据表的引擎。表引擎决定了数据表的特性,也决定了数据将会被如何存储及加载。例如示例中使用的Memory表引擎,是ClickHouse最简单的表引擎,数据只会被保存在内存中,在服务重启时数据会丢失。

复制其他表的表结构:

代码语言:javascript
复制
CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name AS [db_name2.]table_name2 [ENGINE = engine];
  • 新创建表的引擎可以与被复制表的引擎不一样

通过SELECT子句的形式创建表:不仅会根据SELECT子句建立相应的表结构,同时还会将SELECT子句查询的数据顺带写入

代码语言:javascript
复制
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name ENGINE = engine AS SELECT ...

查看表结构:desc table;

删除表:DROP TABLE [IF EXISTS] [db_name.]table_name;

3. 默认值表达式

表字段支持三种默认值表达式的定义方法,分别是DEFAULT、MATERIALIZED和ALIAS。无论使用哪种形式,表字段一旦被定义了默认值,它便不再强制要求定义数据类型,因为ClickHouse会根据默认值进行类型推断。如果同时对表字段定义了数据类型和默认值表达式,则以明确定义的数据类型为主,例如下面的例子:

代码语言:javascript
复制
CREATE TABLE dfv_v1 ( 
    id String,
    c1 DEFAULT 1000,
    c2 String DEFAULT c1
) ENGINE = TinyLog;
INSERT INTO dfv_v1(id) VALUES ('A000');
SELECT c1,c2,toTypeName(c1),toTypeName(c2) from dfv_v1;
┌───c1─┬─c2───┬─toTypeName(c1)─┬─toTypeName(c2)─┐
│ 1000 │ 1000 │ UInt16         │ String         │
└──────┴──────┴────────────────┴────────────────┘

由查询结果可以验证,默认值的优先级符合我们的预期,其中c1字段根据默认值被推断为UInt16;而c2字段由于同时定义了数据类型和默认值,所以它最终的数据类型来自明确定义的String。

默认值表达式的三种定义方法之间也存在着不同之处,可以从如下三个方面进行比较。 (1)数据写入:在数据写入时,只有DEFAULT类型的字段可以出现在INSERT语句中。而MATERIALIZED和ALIAS都不能被显式赋值,它们只能依靠计算取值。例如试图为MATERIALIZED类型的字段写入数据,将会得到如下的错误。

代码语言:javascript
复制
DB::Exception: Cannot insert column URL, because it is MATERIALIZED column..

(2)数据查询:在数据查询时,只有DEFAULT类型的字段可以通过SELECT *返回。而MATERIALIZED和ALIAS类型的字段不会出现在SELECT *查询的返回结果集中。 (3)数据存储:在数据存储时,只有DEFAULT和MATERIALIZED类型的字段才支持持久化。如果使用的表引擎支持物理存储(例如TinyLog表引擎),那么这些列字段将会拥有物理存储。而ALIAS类型的字段不支持持久化,它的取值总是需要依靠计算产生,数据不会落到磁盘。

可以使用ALTER语句修改默认值,例如:

代码语言:javascript
复制
ALTER TABLE [db_name.]table MODIFY COLUMN col_name DEFAULT value;

修改动作并不会影响数据表内先前已经存在的数据。但是默认值的修改有诸多限制,例如在合并树表引擎中,它的主键字段是无法被修改的;而某些表引擎则完全不支持修改(例如TinyLog)。

4. 临时表

ClickHouse也有临时表的概念,创建临时表的方法是在普通表的基础之上添加TEMPORARY关键字,它的完整语法如下所示:

代码语言:javascript
复制
CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name (
    name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
    ...
);

相比普通表而言,临时表有如下两点特殊之处:

  • 它的生命周期是会话绑定的,所以它只支持Memory表引擎,如果会话结束,数据表就会被销毁;
  • 临时表不属于任何数据库,所以在它的建表语句中,既没有数据库参数也没有表引擎参数。

临时表的优先级是大于普通表的。当两张数据表名称相同的时候,会优先读取临时表的数据。 在ClickHouse的日常使用中,通常不会刻意使用临时表。它更多被运用在ClickHouse的内部,是数据在集群间传播的载体。

5. 分区表

数据分区(partition)和数据分片(shard)是完全不同的两个概念。数据分区是针对本地数据而言的,是数据的一种纵向切分。而数据分片是数据的一种横向切分。数据分区对于一款OLAP数据库而言意义非凡:借助数据分区,在后续的查询过程中能够跳过不必要的数据目录,从而提升查询的性能。合理地利用分区特性,还可以变相实现数据的更新操作,因为数据分区支持删除、替换和重置操作。假设数据表按照月份分区,那么数据就可以按月份的粒度被替换更新。 分区虽好,但不是所有的表引擎都可以使用这项特性,目前只有合并树(MergeTree)家族系列的表引擎才支持数据分区。接下来通过一个简单的例子演示分区表的使用方法。首先由PARTITION BY指定分区键,例如下面的数据表partition_v1使用了日期字段作为分区键,并将其格式化为年月的形式:

代码语言:javascript
复制
CREATE TABLE partition_v1 ( 
    ID String,
    URL String,
    EventTime Date
) ENGINE =  MergeTree()
PARTITION BY toYYYYMM(EventTime) 
ORDER BY ID;

INSERT INTO partition_v1 VALUES 
('A000','www.nauu.com','2019-05-01'),
('A001','www.brunce.com','2019-06-02');

SELECT table,partition,path from system.parts WHERE table = 'partition_v1';
┌─table────────┬─partition┬─path────────────────────────────────────────────────────────┐
│ partition_v1 │ 201905   │ /var/lib/clickhouse/data/DB_TEST/partition_v1/201905_1_1_0/ │
│ partition_v1 │ 201906   │ /var/lib/clickhouse/data/DB_TEST/partition_v1/201906_2_2_0/ │
└──────────────┴──────────┴─────────────────────────────────────────────────────────────┘

可以看到,partition_v1按年月划分后,目前拥有两个数据分区,且每个分区都对应一个独立的文件目录,用于保存各自部分的数据。合理设计分区键非常重要,通常会按照数据表的查询场景进行针对性设计。例如在刚才的示例中数据表按年月分区,如果后续的查询按照分区键过滤,例如:

代码语言:javascript
复制
SELECT * FROM  partition_v1 WHERE EventTime ='2019-05-01';

那么在后续的查询过程中,可以利用分区索引跳过6月份的分区目录,只加载5月份的数据,从而带来查询的性能提升。

当然,使用不合理的分区键也会适得其反,分区键不应该使用粒度过细的数据字段。例如,按照小时分区,将会带来分区数量的急剧增长,从而导致性能下降。

6. 视图

ClickHouse拥有普通和物化两种视图,其中物化视图拥有独立的存储,而普通视图只是一层简单的查询代理。创建普通视图的完整语法如下所示:

代码语言:javascript
复制
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ...

普通视图不会存储任何数据,它只是一层单纯的SELECT查询映射,起着简化查询、明晰语义的作用,对查询性能不会有任何增强。假设有一张普通视图view_tb_v1,它是基于数据表tb_v1创建的,那么下面的两条SELECT查询是完全等价的:

代码语言:javascript
复制
-- 普通表
SELECT * FROM tb_v1;
-- tb_v1的视图
SELECT * FROM view_tb_v1;

物化视图支持表引擎,数据保存形式由它的表引擎决定,创建物化视图的完整语法如下所示:

代码语言:javascript
复制
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

物化视图创建好之后,如果源表被写入新数据,那么物化视图也会同步更新。

POPULATE修饰符决定了物化视图的初始化策略:

  • 如果使用了POPULATE修饰符,那么在创建视图的过程中,会连带将源表中已存在的数据一并导入,如同执行了SELECT INTO一般;
  • 反之,如果不使用POPULATE修饰符,那么物化视图在创建之后是没有数据的,它只会同步在此之后被写入源表的数据。
  • 当使用了 TO [db].[table],会将数据保存到指定的表中,所以就不能使用POPULATE语法了,如果没有使用TO [db].[table]语法,就必须设置存储数据的表(这里指新创建的视图)的ENGINE

物化视图目前并不支持同步删除,如果在源表中删除了数据,物化视图的数据仍会保留。物化视图本质是一张特殊的数据表,使用SHOW TABLES可以看到物化视图的表名,表名为.inner.[物化视图的名称],删除视图的语法是:DROP TABLE view_name;

7. 数据表的修改操作

目前只有MergeTree、Merge和Distributed这三类表引擎支持ALTER语法。

  • 追加字段
代码语言:javascript
复制
ALTER TABLE tb_name ADD COLUMN [IF NOT EXISTS] name [type] [default_expr] [AFTER name_after];
# 在表的末尾增加列
ALTER TABLE testcol_v1 ADD COLUMN OS String DEFAULT 'mac';
# 在指定列之后增加列
ALTER TABLE testcol_v1 ADD COLUMN IP String AFTER ID;

对于数据表中已经存在的旧数据而言,新追加的字段会使用默认值补全。

  • 修改数据类型
代码语言:javascript
复制
ALTER TABLE tb_name MODIFY COLUMN [IF EXISTS] name [type] [default_expr];

修改某个字段的数据类型,实质上会调用相应的toType转型方法。如果当前的类型与期望的类型不能兼容,则修改操作将会失败。例如,将String类型的IP字段修改为IPv4类型是可行的,而尝试将String类型转为UInt类型就会出现错误。

  • 修改备注

追加备注的语法如下所示:

代码语言:javascript
复制
ALTER TABLE tb_name COMMENT COLUMN [IF EXISTS] name 'some comment';
  • 删除字段
代码语言:javascript
复制
ALTER TABLE tb_name DROP COLUMN [IF EXISTS] name;

列字段在被删除之后,它的数据也会被连带删除。

  • 移动数据表
代码语言:javascript
复制
RENAME TABLE [db_name11.]tb_name11 TO [db_name12.]tb_name12,[db_name21.]tb_name21 TO [db_name22.]tb_name22, ...

RENAME可以修改数据表的名称,如果将原始数据库与目标数据库设为不同的名称,那么就可以实现数据表在两个数据库之间移动的效果。而原始数据库与目标数据库的名称相同的话,就是修改表名称的操作。

需要注意的是,数据表的移动只能在单个节点的范围内。换言之,数据表移动的目标数据库和原始数据库必须处在同一个服务节点内,而不能是集群中的远程节点。

  • 清空数据表
代码语言:javascript
复制
TRUNCATE TABLE [IF EXISTS] [db_name.]tb_name;

8. 分区基本操作

目前只有MergeTree系列的表引擎支持数据分区。

  • 查询分区信息

ClickHouse内置了许多system系统表,用于查询自身的状态信息。其中parts系统表专门用于查询数据表的分区信息。例如执行下面的语句,就能够得到数据表partition_v2的分区状况:

代码语言:javascript
复制
SELECT partition_id,name,table,database FROM system.parts WHERE table = 'partition_v2';
┌─partition_id───┬─name───────────┬─table────────┬─database┐
│ 201905         │ 201905_1_1_0_6 │ partition_v2 │ default │
│ 201910         │ 201910_3_3_0_6 │ partition_v2 │ default │
│ 201911         │ 201911_4_4_0_6 │ partition_v2 │ default │
│ 201912         │ 201912_5_5_0_6 │ partition_v2 │ default │
└────────────────┴────────────────┴──────────────┴─────────┘
# 目前partition_v2表共拥有4个分区,其中partition_id或者name等同于分区的主键,可以基于它们的取值确定一个具体的分区。
  • 删除指定分区
代码语言:javascript
复制
ALTER TABLE tb_name DROP PARTITION partition_expr;

# 假如现在需要更新partition_v2数据表整个7月份的数据,则可以先将7月份的分区删除
ALTER TABLE partition_v2 DROP PARTITION 201907;
# 然后将整个7月份的新数据重新写入,就可以达到更新的目的
INSERT INTO partition_v2 VALUES ('A004-update','www.bruce.com', '2019-07-02');...
  • 复制分区数据

ClickHouse支持将A表的分区数据复制到B表,这项特性可以用于快速数据写入、多表间数据同步和备份等场景,它的完整语法如下:

代码语言:javascript
复制
ALTER TABLE B REPLACE PARTITION partition_expr FROM A;

并不是任意数据表之间都能够相互复制,它们还需要满足两个前提条件: (1)两张表需要拥有相同的分区键;

(2)它们的表结构完全相同。

代码语言:javascript
复制
# 假设数据表partition_v2与先前的partition_v1分区键和表结构完全相同
# 那么应先在partition_v1中写入一批8月份的新数据
INSERT INTO partition_v1 VALUES ('A006-v1','www.v1.com','2019-08-05'),('A007-v1','www.v1.com','2019-08-20');
# 再执行下面的语句
ALTER TABLE partition_v2 REPLACE PARTITION 201908 FROM partition_v1;
# 即能够将partition_v1的整个201908分区中的数据复制到partition_v2
  • 重置分区数据

如果数据表某一列的数据有误,需要将其重置为初始值,此时可以使用下面的语句实现:

代码语言:javascript
复制
ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr;

对于默认值的含义,遵循如下原则:如果声明了默认值表达式,则以表达式为准;否则以相应数据类型的默认值为准。

  • 卸载与装载分区

表分区可以通过DETACH语句卸载,分区被卸载后,它的物理数据并没有删除,而是被转移到了当前数据表目录的detached子目录下。而装载分区则是反向操作,它能够将detached子目录下的某个分区重新装载回去。卸载与装载这一对伴生的操作,常用于分区数据的迁移和备份场景。

卸载某个分区的语法如下所示:

代码语言:javascript
复制
ALTER TABLE tb_name DETACH PARTITION partition_expr;

一旦分区被移动到了detached子目录,就代表它已经脱离了ClickHouse的管理,ClickHouse并不会主动清理这些文件。这些分区文件会一直存在,除非我们主动删除或者使用ATTACH语句重新装载它们。

装载某个分区的完整语法如下所示:

代码语言:javascript
复制
ALTER TABLE tb_name ATTACH PARTITION partition_expr;

9. 分布式DDL

ClickHouse支持集群模式,一个集群拥有1到多个节点。CREATE、ALTER、DROP、RENMAE及TRUNCATE这些DDL语句,都支持分布式执行。这意味着,如果在集群中任意一个节点上执行DDL语句,那么集群中的每个节点都会以相同的顺序执行相同的语句。这项特性意义非凡,它就如同批处理命令一样,省去了需要依次去单个节点执行DDL的烦恼。

将一条普通的DDL语句转换成分布式执行十分简单,只需加上ON CLUSTER cluster_name声明即可。例如,执行下面的语句后将会对ch_cluster集群内的所有节点广播这条DDL语句:

代码语言:javascript
复制
CREATE TABLE partition_v3 ON CLUSTER ch_cluster( 
    ID String,
    URL String,
    EventTime Date
) ENGINE =  MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID;

10. 数据写入

INSERT语句支持三种语法范式,三种范式各有不同,可以根据写入的需求灵活运用。

第一种是使用VALUES格式的常规语法:

代码语言:javascript
复制
INSERT INTO [db.]table [(c1, c2, c3…)] VALUES (v11, v12, v13…), (v21, v22, v23…), ...

其中,c1、c2、c3是列字段声明,可省略。VALUES后紧跟的是由元组组成的待写入数据,通过下标位与列字段声明一一对应。数据支持批量声明写入,多行数据之间使用逗号分隔

在使用VALUES格式的语法写入数据时,支持加入表达式或函数,例如:

代码语言:javascript
复制
INSERT INTO partition_v2 VALUES ('A0014',toString(1+2),now());

第二种是使用指定格式的语法:

代码语言:javascript
复制
INSERT INTO [db.]table [(c1, c2, c3…)] FORMAT format_name data_set

以常用的CSV格式写入为例:

代码语言:javascript
复制
INSERT INTO partition_v2 FORMAT CSV \
'A0017','www.nauu.com','2019-10-01' \
'A0018','www.nauu.com','2019-10-01'

第三种是使用SELECT子句形式的语法:

代码语言:javascript
复制
INSERT INTO [db.]table [(c1, c2, c3…)] SELECT ...

在通过SELECT子句写入数据的时候,同样也支持加入表达式或函数,虽然VALUES和SELECT子句的形式都支持声明表达式或函数,但是表达式和函数会带来额外的性能开销,从而导致写入性能的下降。所以如果追求极致的写入性能,就应该尽可能避免使用它们。

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子句写入时是不生效的。

11. 数据的删除与修改

ClickHouse提供了DELETE和UPDATE的能力,这类操作被称为Mutation查询,它可以看作ALTER语句的变种。虽然Mutation能最终实现修改和删除,但不能完全以通常意义上的UPDATE和DELETE来理解,我们必须清醒地认识到它的不同:

  • 首先,Mutation语句是一种"很重"的操作,更适用于批量数据的修改和删除;
  • 其次,它不支持事务,一旦语句被提交执行,就会立刻对现有数据产生影响,无法回滚;
  • 最后,Mutation语句的执行是一个异步的后台过程,语句被提交之后就会立即返回。所以这并不代表具体逻辑已经执行完毕,它的具体执行进度需要通过system.mutations系统表查询。

DELETE语句的完整语法如下所示:

代码语言:javascript
复制
ALTER TABLE [db_name.]table_name DELETE WHERE filter_expr

删除过程:

  • 每一此删除操作,都会生成一个mutation_id
代码语言:javascript
复制
SELECT database, table, mutation_id, block_numbers.number as num, is_done FROM system.mutations;

┌─database─┬─table────────┬─mutation_id────┬─num──┬─is_done─┐
│ default  │ partition_v2 │ mutation_6.txt │ [6]  │ 1       │
└──────────┴──────────────┴────────────────┴──────┴─────────┘

在数据表的根目录下,会以mutation_id为名生成与之对应的日志文件,其中完整地记录了这次DELETE操作的执行语句和时间,数据删除的过程是以数据表的每个分区目录为单位,将所有目录重写为新的目录,新目录的命名规则是在原有名称上加上system.mutations.block_numbers.number的值,所以执行删除操作之前的分区目录如下:

代码语言:javascript
复制
201905_1_1_0

删除操作:

代码语言:javascript
复制
201905_1_1_0
# 生成了新的目录
201905_1_1_0_6
# 日志文件
mutation_6.txt

数据会从201905_1_1_0目录中重写一份到201905_1_1_0_6目录中,这个过程中会在201905_1_1_0_6目录中将需要删除的数据去掉。

旧的数据目录并不会立即删除,而是会被标记成非激活状态。等到MergeTree引擎的下一次合并动作触发时,这些非激活目录才会被真正从物理意义上删除。

据修改除了需要指定具体的列字段之外,整个逻辑与数据删除别无二致,它的完整语法如下所示:

代码语言:javascript
复制
ALTER TABLE [db_name.]table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr;

UPDATE支持在一条语句中同时定义多个修改字段,分区键和主键不能作为修改字段。例如,执行下面的语句即能够根据WHERE条件同时修改partition_v2内的URL和OS字段:

代码语言:javascript
复制
ALTER TABLE partition_v2 UPDATE URL = 'www.wayne.com',OS = 'mac' WHERE ID IN (SELECT ID FROM partition_v2 WHERE EventTime = '2019-06-01');
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2021.08.20 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 1. 数据库
  • 2. 数据表
  • 3. 默认值表达式
  • 4. 临时表
  • 5. 分区表
  • 6. 视图
  • 7. 数据表的修改操作
  • 8. 分区基本操作
  • 9. 分布式DDL
  • 10. 数据写入
  • 11. 数据的删除与修改
相关产品与服务
数据库
云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档