clickHouse基础语法

clichouse数据类型

整形

• lnt8 8bit,1字节 （-128-127）
• lnt16 16bit
• lnt32 32bit
• lnt64 64bit

无符号整型

相比于上面，就是把负数部分挪到正数部分

• Ulnt8 （0-255）
• Ulnt16
• Ulnt32
• Ulnt64

浮点型

• Float32 也就是float
• Float64 也就是double

布尔型

• 没有单独的布尔值，可以使用Ulnt8，取值限制为0或1 ，达到相同效果

Decimal

精确存储小数，超过最低有效数字会被丢弃（不是四舍五入）

• Decimal32(s)，相当于Decimal(9-s,s)，有效位数为：1-9
• Decimal64(s)，相当于Decimal(18-s,s)，有效位数为：1-18
• Decimal128(s)，相当于Decimal(38-s,s)，有效位数为：1-38

decimal(10,2)中的“2”表示小数部分的位数，如果插入的值未指定小数部分或者小数部分不足两位则会自动补到2位小数，若插入的值小数部分超过了2为则会发生截断，截取前2位小数。

“10”指的是整数部分加小数部分的总长度，也即插入的数字整数部分不能超过“10-2”位，否则不能成功插入，会报超出范围的错误。

例如Decimal32(5)，就是整数加小数一共9位，小数部分为5位

• 123.12311111结果为：123.12311

字符串

• String 任意长度的字符串
• FixedString(N) 固定长度字符串，N必须是正自然数，字符串小于N的时候会追加 空字节 ，大于N会报错

枚举类型

• Enum8 相当于String - lnt8
• Enum16 相当于String - lnt16

时间类型

• Date 年月日
• Datetime 年月日时分秒
• Datetime64 年月日时分秒毫秒

数组

• Array(T) 由T类型元素构成的数组

空

一般不要使用空，可以给一些默认值，空值会影响性能

--Nullable 表示可以为null
CREATE TABLE 
t_null(
    x Int8,
    y Nullable(Int8)
) ENGINE TinyLog

INSERT INTO t_null VALUES (1, NULL), (2, 3)

还有很多数据类型，详情见官网

https://clickhouse.com/docs/zh/sql-reference/data-types/int-uint

表引擎

引擎大小写敏感

日志家族

• 需要写入许多小数据量（少于一百万行）的表的场景而开发的：

  • TingLog
  • Log
  • StripeLog

• 以列文件的形式保存在磁盘上，不支持索引，没有并发控制，一般保存少量数据，生产基本不会用

Memory

• 内存引擎，数据以未压缩的原始形式保存在内存中，服务器重启数据就会消失，不支持索引，读写操作不会相互阻塞
• 简单查询效率非常高，超过10G/s，但一般不用，除了测试以外，就是在非常需要高性能且数据量不大的情况下才会考虑（上限大概为1亿行）

集成引擎

• 例如，数据在mysql，想要通过clickhouse查询mysql的数据，可以把mysql的数据导入clickhouse来查，但是这样会很麻烦，所以提供了这种集成引擎，直接通过clickhouse去mysql中查询数据

有很多种集成，并不真正拥有数据

MergeTree家族（合并树家族）

MergeTree

• MergeTree以及该系列（*MergeTree）的引擎是clickhouse最强大的引擎，支持索引和分区，相当于innodb于mysql的地位

cd /var/lib/clickhouse/

该目录下，有以下子目录：data是数据，metadata是表信息

clickhouse默认的user表

CREATE TABLE `system`.users (
	name String,
	id UUID,
	storage String,
	auth_type Enum8('no_password' = 0, 'plaintext_password' = 1, 'sha256_password' = 2, 'double_sha1_password' = 3, 'ldap' = 4, 'kerberos' = 5),
	auth_params String,
	host_ip Array(String),
	host_names Array(String),
	host_names_regexp Array(String),
	host_names_like Array(String),
	default_roles_all UInt8,
	default_roles_list Array(String),
	default_roles_except Array(String),
	grantees_any UInt8,
	grantees_list Array(String),
	grantees_except Array(String)
) ENGINE = MergeTree()
ORDER BY name;

clickhouse默认记录了数据的条数

分区

​ 分区的目的就是降低扫描的范围，优化了查询速度，例如按照日志分区，就可以快速按照日期去查询，一个分区一个目录，

​ 如果不分区，就会默认一个all分区，也就是把所有的数据放在一个分区

​ 而且分区可以提供并行访问，一个分区一个线程，面对跨分区的查询，clickhouse会以分区为单位并行处理

​

分区文件命名格式如上图，含义是：

• 分区值-最小区块编号-最大区块编号-合并层级
• 分区值：由partiton by 分区主键决定
  • 没有partiton by ，默认生成一个all目录，所有数据存于all目录下
  • 分区键为整型，直接用整型值的字符串作为分区id
  • 分区键为日期类，或者可以转换为日期类型
  • 其他类型的分区键，通过hash算法取hash值作为分区id
• 最小区块编号
• 最大区块编号
• 合并层级

数据的写入于分区合并

• 任何一次数据写入都会产生新的临时分区，不会纳入任何一个已有的分区，
• 写入后的某个时刻，大概10-15分钟，会执行合并操作，如果不想等待，也可以手动执行合并操作，把临时分区中的数据合并到已有分区

-- 合并操作：
optimize table 表名 final;
-- 只针对某个分区进行合并
optimize table 表名 partition '分区名'  final;

主键

• clickhouse主键提供了一级索引，但并不唯一，而且并不是必须的

• 主键提供的索引为稀疏索引，默认间隔为8192，除非有大量的重复值，一般不需要改这个值，可以减少索引文件的大小，以及快速定位，但是没办法避免在一个区间内的扫描

排序

• order by（） 排序是的必须的，多个字段“，”分开
• 因为稀疏索引只能确定一个范围，所以数据必须有序，不然就无法通过索引来定位数据
• 而且操作有序的数据，效率会更高

主键必须是order by的前缀字段，也就是 order by (id,name) ，那么主键就必须是id或者id,name

二级索引

在20.1.2.4之前，二级索引是实验阶段，需要手动开启，后续版本该设置已被删除，默认开启

建表语句如下：

CREATE TABLE test (
	name String,
	id UUID,
	ctime Datetime,
	INDEX a id TYPE minmax GRANULARITY 5
) ENGINE = MergeTree()
ORDER BY (name,id);

INDEX 索引名 字段名 TYPE minmax（索引的一种类型，这里是记录最小和最大值） GRANULARITY（粒度） 5

​ 粒度，指的是在一级索引的前提下，在记录数据的粒度，比如说，有很多重复的数据，一级索引记录的区间为1-10，10-20，20-50等，想要判断35数据在哪一个区间，就需要判断3次，这里的粒度是5，也就是把五个区间合并为一个二级索引，使得区间的范围更大，查找更高效，也就是对于一级索引粒度的粒度

数据TTL

也就是数据存活时间，MergeTree可以单独对表或者列设置过期时间，过期了数据合并后就会变成默认值

CREATE TABLE test (
	name String,
	age UInt8 TTL ctime+interval 10 SECOND,
	ctime Date
) ENGINE = MergeTree()
ORDER BY (name,ctime);

TTL 表中现有的时间列+（加或减固定时间值）interval 10 SECOND（时间单位：秒分时天月等）, 这里表中现有的时间列 不能是主键

对于已经建好的表，可以修改列的定义加上ttl

ALTER TABLE 表名 MODIFY COLUMN 列名 列的数据类型 TTL 表中现有的时间列+interval 10 SECOND

表及别的TTL，相等于给所有的列加上过期时间，也可以在建表的时候，在order by 后加上TTL

ALTER TABLE 表名 MODIFY TTL 表中现有的时间列+interval 10 SECOND

ReplacingMergeTree

• 是MergeTree的一个变种，增加了去重的作用，会根据order by 的字段来去重，
• 只有在同一批插入（新版本）或者合并的时候去重，所以在合并前是有可能出现重复数据的，只能保证最终的数据不重复
• 而且如果表经过了分区，去重也只会在分区内部进行，不能跨分区去重
• 如果出现重复值，会保留ReplacingMergeTree（）填的字段，保留该字段最大的值的数据，如果该字段的值也一样或者ReplacingMergeTree（）不填，就默认按照插入序，保留最后一条

CREATE TABLE test (
	name String,
	age UInt8 TTL ctime+interval 10 SECOND,
	ctime Date
) ENGINE = ReplacingMergeTree(ctime)
ORDER BY (name,ctime);

​

SummingMergeTree

• 是MergeTree的一个变种，因为临时查询时聚合的开销很大，增加了求和功能

• 提供一种分区内的预聚合，不能跨分区，也不是实时聚合，在同一批次插入（新版本）或者合并的时候会聚合，所以有可能出现没有聚合的情况

• SummingMergeTree（）里填写的是想要聚合的列，可以填多个，但必须是数值，如果不填就会以所有非维度列的字段来汇总（也就是除了order by 的字段），按插入顺序保留第一行

• 根据order by 的维度（类似于 group by ）来聚合

  CREATE TABLE test1 (
  	name String,
  	age UInt8 TTL ctime+interval 10 SECOND,
  	ctime Date
  ) ENGINE = SummingMergeTree(age)
  ORDER BY (name,ctime);
  

  sql语句

insert

和标准sql基本一样

update 和 delete

• 这类操作被称为Mutation查询，可以看成是alter的一种
• 虽然可以实现修改和删除，但并不是传统意义上的update 和 delete，不支持事务
• 而且每次update 和 delete都会放弃目标原有的分区，重新建立分区，所以尽量进行批量操作，不要频繁的小数据操作

语法例如：

alter table 表名  delete where 过滤条件

alter table 表名  update 字段名 = “”  where 过滤条件

​ 具体操作是先新增分区，并把旧的分区变为逻辑失效，直到触发分区合并，才会删除旧的分区

​ 如果真的想要删除更新数据，也可以增加一个版本号，每次取数据的时候，只取版本号最大的数据，这样可以避免删除和更新操作，然后定期清理过期数据。也是一种思路

查询

基本和标准sql也差别不大

• 支持子查询
• 支持CTE（with）
• 支持各种join，但是尽量避免join的使用，而且join语句不支持缓存
• 窗口函数
• 不支持自定义函数，官方的函数还是很充足的
• group by 增加了一些操作（with rollup/cube/total），以group by (a,b)为例
  • rollup上卷，会得到group by (a,b)和group by (a)，group by ()
  • cube 维度分析，会得到group by (a,b)，group by (a)，group by (b)，group by ()
  • total，会得到group by (a,b)，group by ()

alter

基本和mysql一致

-- 新增字段
alter table 表名 add  column  字段名 字段类型 after column1  ; (这里是指在那一列之后)
-- 修改字段类型
alter table 表名 modify column 列名 字段类型 ;
-- 删除字段
alter table 表名 drop column 列名;

导出

clickhouse-client --password xxxx --query '查询语句' --format CSVWithNames>  导出路径

clickhouse-client --password 123456 --query 'SELECT  * from `system`.aggregate_function_combinators afc' --format CSVWithNames>  /usr/data.csv

其他格式参考官网：

https://clickhouse.com/docs/zh/interfaces/formats