ClickHouse编程指南之DatabaseEngine和TableEngine

数据类型

数据类型	范围	备注
UUID		通用唯一标识符(UUID)是用于标识记录的16字节数。ClickHouse提供了 generateuidv4 功能。
Datetime64		允许存储时间瞬时时间，可以表示为日历日期和一天中的时间
Int[8-126]/UInt[8-256]		指定数值类型，Int是整数，UInt是无符号整数
Float32/Float64		表示浮点数float32表示float类型float64表示double类型
Decimal32/64/128		有符号的定点数，可在加、减和乘法运算过程中保持精度。
String		字符串类型，用于表示不定长参数
FixedString(N)		表示定长字符串类型，不足长度的补充为0
Date/Datetime		Date类型精确到年月日/Datetime能够精确到年-月-日 时:分:秒
Enum		枚举类型
Array(T)		数组类型参数
Tuple		元组类型参数
Nullable		可以为空类型的元素

CREATE TABLE t_user on cluster perftest_3shards_1replicas(
    id UInt32 ,
    name String,
    salary Decimal32(2),
    sex Enum('true'=1,'false'=0),
    birthDay Date,
    hobbies Array(String),
    address Tuple(String,String),
    info Nullable(String)
) engine=Log

CentOSA :) insert into t_user values(1,'jiangzz',1000,1,'1990-12-13',['TV','Game'],('Beijing','Shangdi'),null);
INSERT INTO t_user VALUES
Ok.
1 rows in set. Elapsed: 0.192 sec.

CentOSA :) select * from t_user;

SELECT *
FROM t_user

┌─id─┬─name────┬──salary─┬─sex──┬───birthDay─┬─hobbies───────┬─address───────────────┬─info─┐
│  1 │ jiangzz │ 1000.00 │ true │ 1990-12-13 │ ['TV','Game'] │ ('Beijing','Shangdi') │ ᴺᵁᴸᴸ │
└────┴─────────┴─────────┴──────┴────────────┴───────────────┴───────────────────────┴──────┘

1 rows in set. Elapsed: 0.011 sec.

Database Engines

Database Engines允许用户使用表，默认情况下ClickHouse使用本地的数据库引擎，因为使用本地的数据库引擎用户可以配置表引擎和SQL方言。我们可以使用一下数据库引擎：

• MySQL - 这种引擎允许用户连接远程的MySQL数据库服务执行一些INSERT和 SELECT查询用于实现ClickHouse和MySQL的交互。使用MySQL数据库引擎的时候，ClickHouse会将SQL查询传输给MySQL的服务器执行一些操作

CREATE DATABASE IF NOT EXISTS click house ENGINE = MySQL('192.168.119.1:3306','test', 'root', 'root')

CentOSA :) CREATE DATABASE IF NOT EXISTS click_house ENGINE = MySQL('192.168.119.1:3306','test', 'root', 'root');

CREATE DATABASE IF NOT EXISTS click_house
ENGINE = MySQL('192.168.119.1:3306', 'test', 'root', 'root')

Ok.

0 rows in set. Elapsed: 9.423 sec.

CentOSA :) show databases;

SHOW DATABASES

┌─name────────┐
│ click_house │
│ default     │
│ system      │
└─────────────┘

3 rows in set. Elapsed: 0.002 sec. 

• Lazy - 在内存中创建一个机遇时间的过期的数据库，这里的表仅仅只能使用Log类型表。这种引擎主要用于优化存储一些比较小的Log表。

CREATE DATABASE t_lazy ENGINE = Lazy(60);

Table Engines

MergeTree

MergerTree这种引擎和其他的*MergerTree引擎是ClickHouse使用场景最多的引擎，也是经常使用的引擎。

针对于MergerTree引擎有如下特性：

• 按照数据的主键对数据排序存储，这就有助于创建一个小的稀疏索引，帮助引擎快速查找数据。

• 用户在存储数据的时候可以指定分区参数。ClickHouse支持分区查询的某些操作，可以在查询数据的时候对分区进行减枝处理，这样也可以提升查询效率。

• 支持数据副本，例如使用ReplicatedMergeTree 表引擎可以实现数据的复制。

• 支持数据采样，如果需要实现采样可以在建表的时候指定采样方法。

建表语法：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
    ...
    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

对于建表的语法这里不再特殊说明，用户可以查阅：https://clickhouse.tech/docs/en/sql-reference/statements/create/table/文档查看详情，接下来我们重点研究的是MergeTree引擎的一些语法。

参数	示例	说明
ENGINE	ENGINE = MergeTree()	指定表的引擎是MergeTree引擎，在创建MergerTree引擎的时候无需提供参数。
ORDER BY	ORDER BY (CounterID, EventDate)	后面跟上列明或者任意表达式的元组，注意如果用户建表的时候没有指定主键，系统会使用排序的键作为主键，如果不需要排序可以使用ORDER BY tuple()
PARTITION BY	PARTITION BY toYYYYMM(EventDate)	可选指定分区参数
PRIMARY KEY	PRIMARY KEY （CounterID）	注意如果指定了主键，主键必须是放在ORDER BY排序第一个元素位置
SAMPLE BY	-	可选参数，后面跟上一个采样的表达式
TTL	-	指定表的记录的过期时间，以及过期之后的一些动作
SETTINGS		可选，通常设置一些额外的参数，控制MergerTree的一些行为

• 案例一

create table t_click_1(
  uid Int32,
  url String,
  click_time Datetime
) engine=MergeTree()
  ORDER BY (uid,click_time);

CentOSA :) insert into t_click_1 values(1,'/order/1',now()) ;

INSERT INTO t_click_1 VALUES

Ok.

1 rows in set. Elapsed: 0.081 sec.

CentOSA :) insert into t_click_1 values(1,'/order/1',now()) ;

INSERT INTO t_click_1 VALUES

Ok.

1 rows in set. Elapsed: 0.009 sec.

查看数据库目录结构

root@CentOSA ~]# tree /var/lib/clickhouse/data/
/var/lib/clickhouse/data/
├── default
├── jiangzz
│   └── t_click_1
│       ├── all_1_1_0
│       │   ├── checksums.txt
│       │   ├── click_time.bin
│       │   ├── click_time.mrk2
│       │   ├── columns.txt
│       │   ├── count.txt
│       │   ├── primary.idx
│       │   ├── uid.bin
│       │   ├── uid.mrk2
│       │   ├── url.bin
│       │   └── url.mrk2
│       ├── all_2_2_0
│       │   ├── checksums.txt
│       │   ├── click_time.bin
│       │   ├── click_time.mrk2
│       │   ├── columns.txt
│       │   ├── count.txt
│       │   ├── primary.idx
│       │   ├── uid.bin
│       │   ├── uid.mrk2
│       │   ├── url.bin
│       │   └── url.mrk2
│       ├── detached
│       └── format_version.txt
└── system

实际上我们会发现，每次的插入系统都会创建一个小文件夹，然后创建相应的数据库文件。

CentOSA :) select * from t_click_1;

SELECT *
FROM t_click_1

┌─uid─┬─url──────┬──────────click_time─┐
│   1 │ /order/1 │ 2020-12-18 23:12:39 │