一 ClickHouse的数据类型
作为分析类型数据库，提供了许多数据类型，可以划分为基础类型，复合类型，特殊类型。
1.1 基础类型
只有数值，字符串，时间三种类型
1，数值类型
分为整数，浮点数，定点数三类
1）Int
有符号整数类型的具体信息
无符号整数类型的具体信息
2）Float
ClickHouse直接使用Float32和Float64代表单精度浮点数以及双精度浮点数
注意： 在使用浮点数的时候，应当要意识到它是有限精度的。
浮点数支持正无穷、负无穷以及非数字的表达方式。
- 正无穷： inf
- 负无穷： -inf 比如 select -1/0
- 非数字： nan 比如 select 0/0
- Decimal
果要求更高精度的数值运算，则需要使用定点数。ClickHouse提供了Decimal32、Decimal64和Decimal128三种精度的定点数。可以通过两种形式声明定点：简写方式有Decimal32(S)、Decimal64(S)、Decimal128(S)三种，原生方式为Decimal(P,S)，其中：
·P代表精度，决定总位数（整数部分+小数部分），取值范围是1～38；
·S代表规模，决定小数位数，取值范围是0～P。
定点数四则运算后，精度变化的规则
比如：
SELECT toDecimal64(2,4) + toDecimal32(2,2)
┌─plus(toDecimal64(2, 4), toDecimal32(2, 2))─┐
│ 4.0000 │
└───────────────────────────┘
2 字符串类型
字符串类型可以细分为String、FixedString和UUID三类。
1）String 字符串由String定义，长度不限。因此在使用String的时候无须声明大小。
2) FixString 固定长度的字符串。定长字符串通过FixedString(N)声明，其中N表示字符串长度。但如果实际串大小不足的话，FixedString使用null字节填充末尾字符。
3）UUID UUID共有32位，它的格式为8-4-4-4-12。如果一个UUID类型的字段在写入数据时没有被赋值，则会依照格式使用0填充
3 时间类型
时间类型分为DateTime、DateTime64和Date三类。
1）DateTime DateTime类型包含时、分、秒信息，精确到秒，支持使用字符串形式写入
CREATE TABLE Datetime_TEST (
c1 Datetime
) ENGINE = Memory
--以字符串形式写入
INSERT INTO Datetime_TEST VALUES('2020-06-22 00:00:00')
2）DateTime64 DateTime64可以记录亚秒，它在DateTime之上增加了精度的设置
CREATE TABLE Datetime64_TEST (
c1 Datetime64(2)
) ENGINE = Memory
--以字符串形式写入
INSERT INTO Datetime64_TEST VALUES('2019-06-22 00:00:00')
SELECT c1, toTypeName(c1) FROM Datetime64_TEST
┌─────────────c1─┬─toTypeName(c1)─┐
│ 2019-06-22 00:00:00.00 │ DateTime │
└───────────────┴──────────┘
3）Date D不包含具体的时间信息，只精确到天，它同样也支持字符串形式写入
1.2 复合类型
ClickHouse还提供了数组、元组、枚举和嵌套四类复合类型。
1 Array
SELECT array(1, 2) as a , toTypeName(a)
┌─a───┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8) │
└─────┴────────────────┘
或者
SELECT [1, 2]
CREATE TABLE Array_TEST (
c1 Array(String)
) engine = Memory
ClickHouse的数组拥有类型推断的能力，推断依据：以最小存储代价为原则，即使用最小可表达的数据类型。例如在上面的例子中，array(1,2)会通过自动推断将UInt8作为数组类型。但是数组元素中如果存在Null值，则元素类型将变为Nullable
SELECT [1, 2, null] as a , toTypeName(a)
┌─a──────┬─toTypeName([1, 2, NULL])─┐
│ [1,2,NULL] │ Array(Nullable(UInt8)) │
└────────┴─────────────────┘
注意： 在同一个数组内可以包含多种数据类型，例如数组[1,2.0]是可行的。但各类型之间必须兼容，例如数组[1,‘2’]则会报错。
2 Tuple
元组类型由1～n个元素组成，每个元素之间允许设置不同的数据类型，且彼此之间不要求兼容。元组同样支持类型推断，其推断依据仍然以最小存储代价为原则。与数组类似，元组也可以使用两种方式定义，常规方式tuple(T)：
SELECT tuple(1,'a',now()) AS x, toTypeName(x)
或者
SELECT (1,2.0,null) AS x, toTypeName(x)
在定义表字段时，元组也需要指定明确的元素类型：
CREATE TABLE Tuple_TEST (
c1 Tuple(String,Int8)
) ENGINE = Memory;
3 Enum
ClickHouse提供了Enum8和Enum16两种枚举类型，它们除了取值范围不同之外，别无二致。枚举固定使用(String:Int)Key/Value键值对的形式定义数据，所以Enum8和Enum16分别会对应(String:Int8)和(String:Int16)，例如：
CREATE TABLE Enum_TEST (
c1 Enum8('ready' = 1, 'start' = 2, 'success' = 3, 'error' = 4)
) ENGINE = Memory;
注意：Key和Value是不允许重复的，要保证唯一性。
Key和Value的值都不能为Null，但Key允许是空字符串。在写入枚举数据的时候，只会用到Key字符串部分，例如：
INSERT INTO Enum_TEST VALUES('ready');
INSERT INTO Enum_TEST VALUES('start');
4 Nested
一张数据表，可以定义任意多个嵌套类型字段，但每个字段的嵌套层级只支持一级，即嵌套表内不能继续使用嵌套类型。对于简单场景的层级关系或关联关系，使用嵌套类型也是一种不错的选择。
CREATE TABLE nested_test (
name String,
age UInt8 ,
dept Nested(
id UInt8,
name String
)
) ENGINE = Memory;
嵌套类型本质是一种多维数组的结构。嵌套表中的每个字段都是一个数组，并且行与行之间数组的长度无须对齐。
INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001,10002], ['研发部','技术支持中心','测试部']);
--行与行之间,数组长度无须对齐
注意： 在同一行数据内每个数组字段的长度必须相等。
INSERT INTO nested_test VALUES ('bruce' , 30 , [10000,10001], ['研发部','技术支持中心',
'测试部']);
DB::Exception: Elements 'dept.id' and 'dept.name' of Nested data structure 'dept' (Array columns) have different array sizes..
在访问嵌套类型的数据时需要使用点符号。
SELECT name, dept.id, dept.name FROM nested_test
┌─name─┬─dept.id──┬─dept.name─────────────┐
│ bruce │ [16,17,18] │ ['研发部','技术支持中心','测试部'] │
└────┴───────┴────────────────────┘
1.3 特殊类型
1 Nullable
Nullable并不能算是一种独立的数据类型，它更像是一种辅助的修饰符，需要与基础数据类型一起搭配使用。Nullable类型与Java8的Optional对象有些相似，它表示某个基础数据类型可以是Null值。
用法
CREATE TABLE Null_TEST (
c1 String,
c2 Nullable(UInt8)
) ENGINE = TinyLog;
通过Nullable修饰后c2字段可以被写入Null值：
INSERT INTO Null_TEST VALUES ('nauu',null)
INSERT INTO Null_TEST VALUES ('bruce',20)
SELECT c1 , c2 ,toTypeName(c2) FROM Null_TEST
注意： 它只能和基础类型搭配使用，不能用于数组和元组这些复合类型，也不能作为索引字段；
应该慎用Nullable类型，包括Nullable的数据表，不然会使查询和写入性能变慢。因为在正常情况下，每个列字段的数据会被存储在对应的[Column].bin文件中。如果一个列字段被Nullable类型修饰后，会额外生成一个[Column].null.bin文件专门保存它的Null值。这意味着在读取和写入数据时，需要一倍的额外文件操作。
2 Domain
域名类型分为IPv4和IPv6两类，本质上它们是对整型和字符串的进一步封装。IPv4类型是基于UInt32封装的，它的具体用法如下所示：
CREATE TABLE IP4_TEST (
url String,
ip IPv4
) ENGINE = Memory;
INSERT INTO IP4_TEST VALUES ('www.nauu.com','192.0.0.0')
SELECT url , ip ,toTypeName(ip) FROM IP4_TEST
┌─url──────┬─────ip─┬─toTypeName(ip)─┐
│ www.nauu.com │ 192.0.0.0 │ IPv4 │
└────────┴───────┴──────────┘
二 如何定义数据表
1 数据库
创建数据库的语法：
CREATE DATABASE IF NOT EXISTS db_name [ENGINE = engine]
数据库目前一共支持5种引擎：
·Ordinary：默认引擎，在绝大多数情况下我们都会使用默认引擎，使用时无须刻意声明。在此数据库下可以使用任意类型的表引擎。
·Dictionary：字典引擎，此类数据库会自动为所有数据字典创建它们的数据表，关于数据字典的详细介绍会在第5章展开。
·Memory：内存引擎，用于存放临时数据。此类数据库下的数据表只会停留在内存中，不会涉及任何磁盘操作，当服务重启后数据会被清除。
·Lazy：日志引擎，此类数据库下只能使用Log系列的表引擎，关于Log表引擎的详细介绍会在第8章展开。
·MySQL：MySQL引擎，此类数据库下会自动拉取远端MySQL中的数据，并为它们创建MySQL表引擎的数据表。
在绝大多数情况下都只需使用默认的数据库引擎。
CREATE DATABASE aithink
默认数据库的实质是物理磁盘上的一个文件目录，所以在语句执行之后，ClickHouse便会在安装路径下创建aithink数据库的文件目录：
docker 默认安装路径为：
docker exec -it ck bash
cd /var/lib/clickhouse
ll
与此同时，在metadata路径下也会一同创建用于恢复数据库的aithink.sql文件
2 数据表
ClickHouse目前提供了三种最基本的建表方法
1）常规定义方法
使用[db_name.]参数可以为数据表指定数据库，如果不指定此参数，则默认会使用default数据库。
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
省略…
) ENGINE = engine
2）复制其他表的结构
CREATE TABLE [IF NOT EXISTS] [db_name1.]table_name AS [db_name2.] table_name2 [ENGINE = engine]
3） 通过SELECT子句的形式创建
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name ENGINE = engine AS SELECT …
3 默认值表达式
表字段支持三种默认值表达式的定义方法，分别是DEFAULT、MATERIALIZED和ALIAS。无论使用哪种形式，表字段一旦被定义了默认值，它便不再强制要求定义数据类型，因为ClickHouse会根据默认值进行类型推断。如果同时对表字段定义了数据类型和默认值表达式，则以明确定义的数据类型为主
列如：
CREATE TABLE dfv_v1 (
id String,
c1 DEFAULT 1000,
c2 String DEFAULT c1
) ENGINE = TinyLog
默认值表达式的三种定义方法之间也存在着不同之处，可以从如下三个方面进行比较。
（1）数据写入：在数据写入时，只有DEFAULT类型的字段可以出现在INSERT语句中。而MATERIALIZED和ALIAS都不能被显式赋值，它
们只能依靠计算取值。例如试图为MATERIALIZED类型的字段写入数据，将会得到如下的错误。
DB::Exception: Cannot insert column URL, because it is MATERIALIZED column..
（2）数据查询：在数据查询时，只有DEFAULT类型的字段可以通过SELECT *返回。而MATERIALIZED和ALIAS类型的字段不会出现在SELECT *查询的返回结果集中。
（3）数据存储：在数据存储时，只有DEFAULT和MATERIALIZED类型的字段才支持持久化。如果使用的表引擎支持物理存储（例如TinyLog表引擎），那么这些列字段将会拥有物理存储。而ALIAS类型的字段不支持持久化，它的取值总是需要依靠计算产生，数据不会落到磁盘。
可以使用ALTER语句修改默认值ALTER TABLE [db_name.]table MODIFY COLUMN col_name DEFAULT value
注意： 修改动作并不会影响数据表内先前已经存在的数据。但是默认值的修改有诸多限制，例如在合并树表引擎中，它的主键字段是无法被修改的；而某些表引擎则完全不支持修改（例如TinyLog）。
4 临时表
语法：
CREATE TEMPORARY TABLE [IF NOT EXISTS] table_name (
name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
name2 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
)
特殊之处：
- 它的生命周期是会话绑定的，所以它只支持Memory表引擎，如果会话结束，数据表就会被销毁；
- 临时表不属于任何数据库，所以在它的建表语句中，既没有数据库参数也没有表引擎参数。
注意： 如果临时表和普通表名称相同，临时表优先级高。使用临时表一般作为数据在集群间传播载体
5 分区表
数据分区（partition）和数据分片（shard）是完全不同的两个概念。数据分区是针对本地数据而言的，是数据的一种纵向切分。而数据分片是数据的一种横向切分。
目前只有合并树（MergeTree）家族系列的表引擎才支持数据分区。
举例：
首先由PARTITION BY指定分区键，例如下面的数据表partition_v1使用了日期字段作为分区键，并将其格式化为年月的形式：
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');
通过system.parts系统表，查询数据表的分区状态：
SELECT table,partition,path from system.parts WHERE table = 'partition_v1'
合理设计分区键非常重要，通常会按照数据表的查询场景进行针对性设计。分区键不应该使用粒度过细的数据字段。例如，按照小时分区，将会带来分区数量的急剧增长，从而导致性能下降。
6 视图
lickHouse拥有普通和物化两种视图，其中物化视图拥有独立的存储，而普通视图只是一层简单的查询代理。
1）普通视图语法：
CREATE VIEW [IF NOT EXISTS] [db_name.]view_name AS SELECT ...
通视图不会存储任何数据，它只是一层单纯的SELECT查询映射，起着简化查询、明晰语义的作用，对查询性能不会有任何增强。
2）物化视图支持表引擎，数据保存形式由它的表引擎决定，创建物化视图的语法：
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT ...
- 物化视图创建好之后，如果源表被写入新数据，那么物化视图也会同步更新。
- POPULATE修饰符决定了物化视图的初始化策略：如果使用了POPULATE修饰符，那么在创建视图的过程中，会连带将源表中已存在的数据一并导入，如同执行了SELECT INTO；
- 如果不使用POPULATE修饰符，那么物化视图在创建之后是没有数据的，它只会同步在此之后被写入源表的数据。
- 物化视图目前并不支持同步删除，如果在源表中删除了数据，物化视图的数据仍会保留。
- 物化视图使用了.inner.特殊前缀的数据表，所以删除视图的方法是直接使用DROP TABLE查询，
SHOW TABLES
┌─name────────┐
│ .inner.view_test2 │
│ .inner.view_test3 │
└───────────┘
三 数据包表的基本操作（略，同关系数据库类似）
四 数据分区的基本操作
目前只有MergeTree系列的表引擎支持数据分区。
ClickHouse内置了许多system系统表，用于查询自身的状态信息。其中parts系统表专门用于查询数据表的分区信息。
1 查询分区消息
sql：
SELECT partition_id,name,table,database FROM system.parts WHERE table = '表名'
2 删除指定分区
sql:
partition_expr 为partition_id
ALTER TABLE tb_name DROP PARTITION partition_expr
3 复制分区数据
ClickHouse支持将A表的分区数据复制到B表，这项特性可以用于快速数据写入、多表间数据同步和备份等场景
sql:
ALTER TABLE B REPLACE PARTITION partition_expr FROM A
满足两个前提条件
- 两张表需要拥有相同的分区键；
- 它们的表结构完全相同。
4 重置分区数据
如果数据表某一列的数据有误，需要将其重置为初始值，此时可以使用下面的语句实现：
ALTER TABLE tb_name CLEAR COLUMN column_name IN PARTITION partition_expr
对于默认值的含义，笔者遵循如下原则：如果声明了默认值表达式，则以表达式为准；否则以相应数据类型的默认值为准。
5 卸载与装载分区
表分区可以通过DETACH语句卸载，分区被卸载后，它的物理数据并没有删除，而是被转移到了当前数据表目录的detached子目录下。而装载分区则是反向操作，它能够将detached子目录下的某个分区重新装载回去。卸载与装载这一对伴生的操作，常用于分区数据的迁移和备份场景。
卸载分区：
ALTER TABLE tb_name DETACH PARTITION partition_expr
例如，执行下面的语句能够将partition_v2表内整个8月份的分区卸载：
ALTER TABLE partition_v2 DETACH PARTITION 201908
装载某个分区的完整语法
ALTER TABLE tb_name ATTACH PARTITION partition_expr
再次执行下面的语句，就可以将刚才已被卸载的201908分区重新装载回去：
ALTER TABLE partition_v2 ATTACH PARTITION 201908
五 分布式DDL执行
如果在集群中任意一个节点上执行DDL语句，那么集群中的每个节点都会以相同的顺序执行相同的语句。
将一条普通的DDL语句转换成分布式执行十分简单，只需加上ON CLUSTER cluster_name声明即可。
例如，执行下面的语句后将会对ch_cluster集群内的所有节点广播这条DDL语句：
CREATE TABLE partition_v3 ON CLUSTER ch_cluster(
ID String,
URL String,
EventTime Date
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventTime)
ORDER BY ID
六 数据写入
INSERT语句支持三种语法范式，三种范式各有不同，可以根据写入的需求灵活运用。
第一种 VALUES格式
INSERT INTO [db.]table [(c1, c2, c3…)] VALUES (v11, v12, v13…), (v21, v22, v23…), ...
第二种 使用指定格式
INSERT INTO [db.]table [(c1, c2, c3…)] FORMAT format_name data_set
ClickHouse支持多种数据格式（更多格式可参见官方手册），以常用的CSV格式写入为例：
INSERT INTO partition_v2 FORMAT CSV \
'A0017','www.nauu.com', '2019-10-01' \
'A0018','www.nauu.com', '2019-10-01'
第三种是使用SELECT子句形式
INSERT INTO [db.]table [(c1, c2, c3…)] 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子句写入时是不生效的。
七 数据的删除与修改
ClickHouse提供了DELETE和UPDATE的能力，这类操作被称为Mutation查询，它可以看作ALTER语句的变种。
虽然Mutation能最终实现修改和删除，但不能完全以通常意义上的UPDATE和DELETE来理解，我们必须清醒地认识到它的不同：
1）Mutation语句是一种“很重”的操作，更适用于批量数据的修改和删除；
2）它不支持事务，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚；
3）Mutation语句的执行是一个异步的后台过程，语句被提交之后就会立即返回。所以这并不代表具体逻辑已经执行完毕，它的具体执行进度需要通过system.mutations系统表查询。
DELETE语句的完整语法
ALTER TABLE [db_name.]table_name DELETE WHERE filter_expr
举例：
LTER TABLE partition_v2 DELETE WHERE ID = 'A003'
在执行了DELETE操作之后数据目录发生了一些变化。每一个原有的数据目录都额外增加了一个同名目录，并且在末4.7 数据的删除与修改
ClickHouse提供了DELETE和UPDATE的能力，这类操作被称为Mutation查询，它可以看作ALTER语句的变种。虽然Mutation能最终实现修改和删除，但不能完全以通常意义上的UPDATE和DELETE来理解，我们必须清醒地认识到它的不同：首先，Mutation语句是一种“很重”的操作，更适用于批量数据的修改和删除；其次，它不支持事务，一旦语句被提交执行，就会立刻对现有数据产生影响，无法回滚；最后，Mutation语句的执行是一个异步的后台过程，语句被提交之后就会立即返回。所以这并不代表具体逻辑已经执行完毕，它的具体执行进度需要通过system.mutations系统表查询。
DELETE语句的完整语法如下所示：
ALTER TABLE [db_name.]table_name DELETE WHERE filter_expr
数据删除的范围由WHERE查询子句决定。例如，执行下面语句可以删除partition_v2表内所有ID等于A003的数据：
ALTER TABLE partition_v2 DELETE WHERE ID = ‘A003’
由于演示的数据很少，DELETE操作给人的感觉和常用的OLTP数据库无异。但是我们心中应该要明白这是一个异步的后台执行动作。
再次进入数据目录，让我们看看删除操作是如何实现的：
可以发现，在执行了DELETE操作之后数据目录发生了一些变化。每一个原有的数据目录都额外增加了一个同名目录，并且在末尾处增加了_7的后缀。此外，目录下还多了一个名为mutation_7.txt的文件。mutation_7.txt是一个日志文件，它完整地记录了这次DELETE操作的执行语句和时间。
Mutation操作的逻辑
- 每执行一条ALTER DELETE语句，都会在mutations系统表中生成一条对应的执行计划，当is_done等于1时表示执行完毕。
- 数据表的根目录下，会以mutation_id为名生成与之对应的日志文件用于记录相关信息。
- 数据删除的过程是以数据表的每个分区目录为单位，将所有目录重写为新的目录，新目录的命名规则是在原有名称上加上system.mutations.block_numbers.number。数据在重写的过程中会将需要删除的数据去掉。旧的数据目录并不会立即删除，而是会被标记成非激活状态（active为0）。
- 等到MergeTree引擎的下一次合并动作触发时，这些非激活目录才会被真正从物理意义上删除。
数据修改除了需要指定具体的列字段之外，整个逻辑与数据删除别无二致，它的完整语法
ALTER TABLE [db_name.]table_name UPDATE column1 = expr1 [, ...] WHERE filter_expr
UPDATE支持在一条语句中同时定义多个修改字段，分区键和主键不能作为修改字段。
扫一扫
4341
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
