ClickHouse读书笔记(四)—数据库与数据表

一、数据库

1、概念

数据库起到命名空间的作用，有效规避命名冲突问题，为后续的数据隔离提供支撑。任何一张数据表必须归属于某个数据库。

2、数据库引擎

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

3、数据库相关命令

创建数据库完整语法：CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]。创建完数据库之后会有一个db_name.sql文件，用于恢复数据库。
删除数据库完整语法：DROP DATABASE [IF EXISTS] db_name

二、数据表

1、建表方法

常规定义

CREATE TABLE user (
    `name` String COMMENT '昵称',
    `age`  UInt8 COMMENT '年龄',
    `dept` Nested(
        id UInt8,
        name String
    ) COMMENT '部门'
) ENGINE = Memory;

常规定义

复制其他表结构：CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name AS [db_name2.] table_name2 [ENGINE = engine]
通过select子句的形式，会把查询结果一并写入数据表：CREATE TABLE [IF NOT EXISTS] [db_name.]table_name ENGINE = engine AS SELECT ...

2、默认表达式

DEFAULT、MATERIALIZED、ALIAS，表字段一旦定义了默认值，便不再强制要求明确数据类型，会根据默认值进行推断。三者区别如下：

数据写入：只有DEFAULT类型的字段可以出现在insert语句中，另外两种不能显示赋值。
数据查询：只有DEFAULT类型的字段可以通过select * 返回。
数据存储：ALIAS类型的字段不支持持久化，它的取值总是需要依靠计算产生，数据不会落到磁盘。

修改默认值：通过ALTER语句修改默认值，修改动作不会影响已经存在的数据。但默认值的修改受限于表引擎，例如在合并树表引擎中，主键字段无法修改；某些表引擎完全不支持修改，例如TinyLog。

alter table [db_name.]table modify column col_name DEFAULT value

3、临时表

建表只需要在table前加temporary并且不写表引擎，例如create temporary table tmp1(c1 String);

生命周期是会话绑定的，它只支持Memory表引擎，如果结束会话，数据表就会被销毁。

临时表不属于任何数据库，所以它的建表语句中，没有数据库参数和表引擎参数。如果普通表和临时表名称相同，临时表的优先级大于普通表，优先读取临时表数据。

日常工作一般不会使用临时表，主要运用在CK内部，是数据在集群间传播的载体。

4、分区表

数据分区与数据分片完全不同。数据分区是针对本地数据而言的，是数据的一种纵向切分，而数据分片是数据的横向切分。

举例，对一张表的时间字段按月分区，可以通过system.parts表指定表名查看分区的文件。

create table pv1 (
    ID String,
    URL String, 
    EventTime Date
)ENGINE MergeTree()
partition by toYYYYMM(EventTime)
order by ID;

insert into pv1 values('A000','https://www.cnblogs.com/shoulinniao/','2022-02-10 00:00:00' ),
('A001', 'https://leetcode-cn.com/u/shoulinniao/', '2022-03-01');

-- 表名、分区编号、路径
select table, partition, path from system.parts where table = 'pv1';

两个不同的月份分别有各自独立的文件目录，用于保存各自部分的数据，在查询过程中，如果某个月份不需要查询则跳过该目录，提高性能。删除表后，分区也跟着删除。

5、视图

普通视图不会存储数据，只是一层单纯的select查询映射，作用是简化查询、明晰语义。

CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ...

物化视图拥有独立的存储，支持表引擎。

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...

建立视图时候，后续源表被写入新数据，符合创建视图的查询语句，就会同步更新是物化视图中，但是源表删除数据的时候不会同步删除。

POPULATE修饰符代表视图的初始化策略，在创建视图过程中会把表中数据一并导入。PS：如果在创建视图的过程中对源表插入了新数据，CK会丢失它们。

三、表的基本操作

目前只有MergeTree、Merge和Distributed支持alter操作。

1、加字段

alter table tb_name add column [if not exists] col_name [type] [default_expr] [col_name]

2、修改数据类型

修改字段类型实质上会调用toType转型方法，需要保证原类型与修改类型兼容

alter table tb_name modify column [if exists] name [type] [default_expr]

3、修改备注

alter table tb_name comment colmn [if exists] name 'some comment'

4、删除字段

alter table tb_name drop column [if exists] name

5、移动数据表

数据库中名字不能相同，所以可能需要先改名。

rename table db1.tb_name1 to db2.tb_name2, db3.tb_name3 to db4.tb_name4,

6、清空数据表

truncate table [if exists] [db_name.]tb_name

四、数据分区的基本操作

1、查询分区信息，查询system数据库的parts表，它是专门用于查询数据表的分区信息，partiton_id和name等同于分区主键。

select partition_id, name, table, database from system.parts where table 'partition_v2'

2、删除指定分区

删除分区后，数据表的数据也会被删除，重新插入数据又会产生分区

alter table tb_name drop partition partition_expr

3、复制分区数据

用于快速数据写入、多表间数据同步和备份等场景，前提条件：两张表又相同的分区键和表结构

-- 将A表的分区数据复制到B表
alter table B replace partition partition_expr from A

4、重置分区数据

把某一列的数据重置为默认值

alter table tb_name CLEAR column col_name in partition partition_expr

5、卸载与装载分区

卸载：通过DETACH语句卸载分区，物理数据并没有删除，被转移到当前数据表目录的detached子目录下。卸载后代表它已经脱离了CK的管理，CK不会主动清理这些文件，除非用户主动删除或者重新装载

装载：ATTACH，卸载的反向操作。

alter table tb_name DETACH | ATTCH partition partition_expr

6、备份与还原分区

7、分布式DDL

CK的集群模式，将普通的DDL语句转换成分布式执行语句，只需要在表名后面加上 ON CLUSTER cluster_name

五、表数据的基本操作

1、插入

values常规插入
select查询插入
指定格式插入，例如CSV格式等

常规插入和查询插入支持函数和表达式，但是使用函数和表达式会有额外的性能开销。

CK的所有内部操作数据都是面向Block数据块的，insert语句最终会将数据转换成Block数据块。因此，insert语句在单个数据块的写入过程中是具有原子性的。在默认的情况下，每个数据块最多可以写入1048576行数据，有max_insert_block_size参数控制，简单记为10⁶行。并且这个数据块的写入要么全部成功要么全部失败。PS：只有在CK服务端处理数据的时候才具有这种原子写入特性，使用JDBC或HTTP接口时不会生效。

-- values常规插入
insert into [db_name.]table_name (c1,c2...) values (v1, v2...), (v11, v22...)
-- select查询插入
insert into [db_name.]table_name (c1,c2...) select

2、数据的删除与修改

delete和update操作被称为Mutation(突变)查询，是alter语句的变种，有以下特性

Mutation是“很重”的操作，更适用于批量数据的修改和删除；
不支持事务，一旦提交无法回滚；
异步执行，一经提交立刻返回，但不代表具体逻辑已经执行完毕，具体进度可以通过system.mutations系统表查询；

ALTER TABLE [db_name.]table_name DELETE WHERE filter_expr
ALTER TABLE [db_name.]table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr

mutations有这么几个字段可以留意，database数据库、table表名、metation_id执行的语句ID、block_numbers.number执行的语句块编号、parts_to_do数据变化的数量、is_done语句执行是否完成(1完成，0未完成)。

每执行一条Mutation语句，都会在mutations系统表中生成一条对应的执行计划。

(1)delete操作内在逻辑简单剖析

语句执行后数据目录会额外增加一个新的同名目录，只是末尾有后缀_x，并且还有一个mutation_x.txt的日志文件，记录执行语句与执行时间；
后缀的数字来源于这条语句在system.mutations表的block_numbers.number字段；
数据删除的过程是以数据表的每个分区目录为单位，将所有目录重写为新的目录，命名规则如上所述；
语句执行后，删除的数据存在于旧目录，不会立即删除，会被标记为非激活状态(active=0)，等到MergeTree引擎下一次合并动作触发时才真正删除；

(2)update操作（整体逻辑与delete操作类似）

需要指定的列字段；
分区键和主键不能作为修改字段；

posted @ 2022-03-19 17:12 守林鸟阅读(498) 评论(0) 编辑收藏举报

刷新页面返回顶部

守林鸟

ClickHouse读书笔记(四)—数据库与数据表

公告