ClickHouse学习笔记：01数据类型和基本DDL

数据类型

常规类型

Tips:

• 1、ClickHouse 兼容了Mysql的数据类型，在建表的DDL语句中可以使用Mysql的数据类型如BIGINT、TEXT等，ClickHouse会自动将这些类型转换为ClickHouse的数据类型。

• 2、ClickHouse自身的数据类型是区分大小写的，例如Int8 就只能这样写，不能写成int8，但是如果是兼容Mysql的数据类型，则既可以大写也可以小写，如BIGINT和bigint都是可以的。

布尔类型

ClickHouse中没有布尔类型，如果需要用到布尔类型，可以使用Int8代替，限定其值为0和1.

枚举类型

• 1、定义

CREATE TABLE XSYS0003(
    SECCODE String comment '证券代码',
    MARKET Enum8('SZ'=0,'SH'=1,'HK'=3) DEFAULT 'SH'
) ENGINE =MergeTree
PRIMARY KEY (SECCODE,MARKET)
ORDER BY (SECCODE,MARKET)
COMMENT '系统码表';
1
2
3
4
5
6
7

Tips:在DDL中的枚举类型也可以使用Enum(‘SZ’=0,‘SH’=1) 这种方式进行定义，ClickHouse会根据枚举的值隐式转换为Enum8或者Enum16，如果枚举的值超过了Int16的表数范围，则会报错。

• 2、插入数据

INSERT INTO TABLE XSYS0003(SECCODE,MARKET)VALUES('000001','SZ'),('000001','SH');
1

• 3、取数据

--过滤时可以当成字符串的方式使用枚举的Key过滤
SELECT * from XSYS0003 x where MARKET = 'SH';

--使用枚举的值过滤，效率比上一方式更高
SELECT * from XSYS0003 x where MARKET = 1;

--取出真实的值，做类型转换
SELECT CAST(MARKET AS Int8) FROM XSYS0003 x;
1
2
3
4
5
6
7
8

日期类型

Date

Tips：定义成Mysql的Timestamp，ClickHouse会自动将Timestamp转换为DataTime类型。

数组类型Array(T)

Array(T): 由类型为T的数据组成的数组。

1. T可以是任何类型，包括数组，但是ClickHouse对多维数组的支持有限，MergeTree引擎就不支持多维数组。

2. 数组中的元素必须是同一数据类型。

3. ClickHouse中数组元素的下标是从1开始的。

• 1、定义

ALTER table XAI0114 add column "MEMBERS" Array(String) comment '成员';
1

• 2、插入值

INSERT into table XAI0114 (MEMBERS) values(['000001','000002']);
1

• 3、查询

SELECT MEMBERS[1]  FROM XAI0114 x ;
1

Tips: 注意，在ClickHouse中，数组的下标是从1开始的，写成MEMBERS[0]会报错。

基本的DDL操作

创建表

CREATE TABLE XAI0115(
	TDATE INT COMMENT '交易日期',
	SECCODE String COMMENT '证券代码',
	SECNAME String COMMENT '证券名称',
	MARKET Enum8('SH'=1,'SZ'=2,'HK'=3) COMMENT '市场代码',
	"OPEN" Decimal(18,2) COMMENT '开盘价',
	CREATE_TIME DateTime COMMENT '插入时间'
) engine = MergeTree
PARTITION BY TDATE
PRIMARY KEY  (TDATE,SECCODE)
ORDER BY (TDATE,SECCODE)
TTL CREATE_TIME + INTERVAL 10 SECOND;
1
2
3
4
5
6
7
8
9
10
11
12

引擎

TinyLog

以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存小数据量的表，在生产环境上的作用有限，多用于测试环境。

Memory

数据以未压缩的原始形态保存在内存中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单的查询有非常高的性能，多用于数据量不大，而又需要非常非常高的性能的场景下。

MergeTree 和*MergeTree家族

合并树引擎及其家族的其他引擎，是ClickHouse中非常重要的引擎，相当于Innodb之于Mysql，在ClickHouse被广泛运用。它有如下特征：

• 1、支持索引
• 2、支持分区
• 3、支持数据TTL

Partition by 分区(可选)

• 分区的目的主要用于降低扫描的范围，提升查询的效率。

• 如果不填，则默认只有一个分区

• MergeTree是以列文件+索引文件+表定义文件组成的。

表的定义文件存放在：clickhouse/store/XXX/XXXXX/TABLE.sql 目录中
如/home/software/clickhouse/store/56d/56d450cb-29f3-424c-8784-994f2813df5c目录中

total 20
-rw-r----- 1 clickhouse clickhouse 371 Nov 15 23:39 XAI0114.sql
-rw-r----- 1 clickhouse clickhouse 531 Nov 15 18:39 XAI0115.sql
-rw-r----- 1 clickhouse clickhouse 431 Nov 15 15:21 XSYS0001.sql
-rw-r----- 1 clickhouse clickhouse 423 Nov 15 15:04 XSYS0002.sql
-rw-r----- 1 clickhouse clickhouse 303 Nov 15 16:18 XSYS0003.sql

1
2
3
4
5
6
7

列文件存放在clickhosuse/data/数据库名称/表名/分区/

-rw-r----- 1 clickhouse clickhouse 254 Nov 15 23:41 checksums.txt
-rw-r----- 1 clickhouse clickhouse  91 Nov 15 23:41 columns.txt
-rw-r----- 1 clickhouse clickhouse   1 Nov 15 23:41 count.txt
-rw-r----- 1 clickhouse clickhouse  90 Nov 15 23:41 data.bin
-rw-r----- 1 clickhouse clickhouse 112 Nov 15 23:41 data.mrk3
-rw-r----- 1 clickhouse clickhouse  10 Nov 15 23:41 default_compression_codec.txt
-rw-r----- 1 clickhouse clickhouse  14 Nov 15 23:41 minmax_SECCODE.idx
-rw-r----- 1 clickhouse clickhouse   7 Nov 15 23:41 partition.dat
-rw-r----- 1 clickhouse clickhouse  16 Nov 15 23:41 primary.idx

1
2
3
4
5
6
7
8
9
10

• 分区后可以并行处理，分区后，面对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理。

• 数据写入与分区合并
  任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入
  后的某个时刻（大概 10-15 分钟后），ClickHouse 会自动执行合并操作（等不及也可以手动
  通过 optimize 执行），把临时分区的数据，合并到已有分区中。

optimize table XAI0114 FINAL;
1

Primary Key 主键(可选)

与传统的关系型数据库不同，ClickHouse的主键并不是唯一约束，定义了主键后用户可以插入相同的数据。在ClickHouse中，主键只提供了一级索引（同Mysql一样，主键字段上面会自动建有索引）。主键的设立依据是查询语句中的Where条件，可以将其理解为Mysql中的索引。

根据条件通过对主键进行某种形式的二分查找，能够定位到对应的 index granularity,避免了全表扫描。

index granularity：索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。ClickHouse 中的 MergeTree 默认是 8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。

• 重点1、ClickHouse的主键不是唯一约束，是可以重复的

• 重点2、ClickHouse的主键必须是ORDER BY字段的左值字段或者全部字段

ORDER BY 排序（必选）

• ORDER BY 设定了分区内的数据按照哪些字段顺序进行有序保存。它是MergeTree中唯一一个必填项，甚至比PrimaryKey更加重要。当用户不设置主键时，很多情况会按照设定的Order By字段进行处理。

• 主键必须是Order BY字段的前缀处理，比如ORDER BY（TDATE，SECCODE）,则主键必须是TDATE或者(TDATE,SECCODE).

TTL

TTL 即 Time To Live，MergeTree提供了可以管理数据表或者列的生命周期的功能。

• 列级别TTL，指的是该列的数据会在指定时间到期后被清空。
  示例：

CREATE TABLE STOCK_HQ(
	SECCODE String comment '证券代码',
	"OPEN" Decimal(9,2)  comment '开盘价' TTL UPDATE_TIME + Interval 10 SECOND ,
	UPDATE_TIME DateTime comment '更新时间'
) engine = MergeTree
ORDER BY SECCODE ;

desc table STOCK_HQ;

insert into table STOCK_HQ VALUES('000002',12.34,now());
SELECT * from STOCK_HQ ;
1
2
3
4
5
6
7
8
9
10
11

插入数据后，等待10秒钟，查询后发现字段OPEN的值被置为了0。
如果看不到效果，手工执行optimize table + tablename final,再执行查询就可以立即看到效果。

• 表级别TTL，指的是该行的数据会在指定的时间到期后被删除。

CREATE TABLE XAI0115(
	TDATE INT COMMENT '交易日期',
	SECCODE String COMMENT '证券代码',
	SECNAME String COMMENT '证券名称',
	MARKET Enum8('SH'=1,'SZ'=2,'HK'=3) COMMENT '市场代码',
	"OPEN" Decimal(18,2) COMMENT '开盘价',
	CREATE_TIME DateTime COMMENT '插入时间'
) engine = MergeTree
PARTITION BY TDATE
PRIMARY KEY  (TDATE,SECCODE)
ORDER BY (TDATE,SECCODE)
TTL CREATE_TIME + INTERVAL 10 SECOND;
1
2
3
4
5
6
7
8
9
10
11
12

TTL 的时间间隔单位有：

• SECOND 秒
• MINUTE 分钟
• HOUR 小时
• DAY 天
• WEEK 周
• MONTH 月
• QUARTER 季度
• YEAR 年

修改表

重命名表

RENAME TABLE XAI0114 TO  XAI0114_NEW;
1

增加字段

ALTER TABLE XAI0114_NEW ADD COLUMN INSERT_TIME DateTime64 default now() comment '插入时间';
1

删除字段

ALTER TABLE XAI0114_NEW DROP COLUMN INSERT_TIME ;
1

重命名字段

ALTER TABLE XAI0114_NEW RENAME COLUMN INSERT_TIME TO UPDATE_TIME ;
1

修改字段

可以按照如下语法，修改字段的数据类型，默认值，comment；

ALTER TABLE XAI0114_NEW MODIFY COLUMN "TYPE" Enum('SH'=1,'SZ'=2,'HK'=3) default 'SZ' comment '市场类型';
1

删除表

-- 方式1
drop table  if exists XAI0114_NEW;

--方式2
drop table XAI0114_NEW;
1
2
3
4
5

清空表

-- 方式1
delete from  XAI0114_NEW;

--方式2
truncate table XAI0114_NEW;
1
2
3
4
5