4.ClickHouse系列之SQL操作与数据类型与表引擎介绍

上篇文章已经创建过表及熟悉了基本语法，本文介绍CK的数据类型以及表引擎的一些分类与作用

一、SQL操作

首先我们建表，表引擎我们后续文章在详细介绍，我们首先了解下基本SQL语法

CREATE DATABASE study;

CREATE TABLE study.customer (
    id UInt8,
    city String,
    name String,
    score Float32,
    sex Enum8('男' = 1, '女' = 0),
    create_date DateTime
) ENGINE=MergeTree() ORDER BY city

1.1 Insert操作

对于clickhouse在插入时一般大批量数据插入，演示仅仅插入几条数据

INSERT INTO study.customer VALUES (1, '临沂', '张三', 99.8, '男', '2022-09-29'),
                                  (2, '上海', '李四', 97.8, '男', '2022-09-29'),
                                  (3, '武汉', '小花', 95.8, '女', '2022-09-29')
SELECT * FROM study.customer

1.2 Update与Delete操作

不建议对clickhouse中数据进行更新与删除操作，即使做也尽量大批量操作，如操作某一城市数据

ALTER TABLE study.customer DELETE WHERE city='武汉';
ALTER TABLE study.customer UPDATE score=99 WHERE id=1;

由于操作比较重，所以Mutation语句分两步进行，同步进行的部分是进行新增数据新增分区和并把旧分区打上逻辑上的失效标识。直到触发分区合并的时候，才会删除旧数据释放磁盘空间。

1.3 rollup cube totals

ALTER TABLE study.customer DELETE WHERE id=id;
INSERT INTO study.customer VALUES (1, '临沂', '张三', 99.8, '男', '2022-09-29'),
                                  (2, '上海', '李四', 97.8, '男', '2022-09-29'),
                                  (3, '武汉', '小花', 95.8, '女', '2022-09-29'),
				  (4, '临沂', '张三', 96.8, '男', '2022-09-29'),
                                  (5, '上海', '李四', 95.8, '男', '2022-09-29'),
                                  (6, '武汉', '小花', 91.8, '女', '2022-09-29')

SELECT city, name, AVG(score) FROM study.customer GROUP BY city, name WITH ROLLUP;
SELECT city, name, AVG(score) FROM study.customer GROUP BY city, name WITH CUBE;
SELECT city, name, AVG(score) FROM study.customer GROUP BY city, name WITH TOTALS;

结果依次为:

)

)

聪明的你，看出区别的吧

1.4 Alter操作

# 在字段score后面新增字段rank
ALTER TABLE study.customer ADD COLUMN rank UInt8 after score;
# 修改rank字段类型为String
ALTER TABLE study.customer MODIFY COLUMN rank String;
# 删除字段rank
ALTER TABLE study.customer DROP COLUMN rank;

二、数据类型与表引擎介绍

2.1 数据类型

2.2 表引擎的使用

表引擎【引擎名称大小写敏感】决定了如何存储表的数据。包括：

• 数据的存储方式和位置，写入读取位置
• 支持哪些查询以及如何支持
• 并发数据访问
• 索引的使用（如果存在）
• 是否可以执行多线程请求
• 数据复制参数

2.2.1 TinyLog引擎

已列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表。

CREATE TABLE t_tiny_log(id String, name String) ENGINE=TinyLog

2.2.2 Memory引擎

内存引擎，数据已未压缩的原始形式保存在内存中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单查询下有非常高的性能表现(超过10G/S)

CREATE TABLE t_memory(id String, name String) ENGINE=Memory

2.2.3 MergeTree引擎

ClickHouse中最强大引擎当属MergeTree(合并树)引擎及该系列(*MergeTree)中的其他引擎，支持索引和分区。

DROP TABLE IF EXISTS study.house;
CREATE TABLE study.house (
    id String,
    city String,
    region String,
    name String,
    price Float32 TTL publish_date + interval 3 MONTH,
    publish_date DateTime,
    INDEX price price TYPE minmax GRANULARITY 3
) ENGINE=MergeTree PARTITION BY toYYYYMMDD(publish_date) PRIMARY KEY(id) ORDER BY (id, city, region, name)
SETTINGS index_granularity=8192

• 对于主键id来说只是提供了数据的一级索引，但是却不是唯一约束。
• 对于index_granularity索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔
• 主键【PRIMARY KEY(id)】必须是order by字段的前缀字段
• 二级索引【INDEX price price TYPE minmax GRANULARITY 3】是对于一级索引粒度的粒度。如一级索引范围为【1，15】、【15，50】、【50，100】，【100，900】，如果二级索引粒度为3，则前三个一级索引范围合并【1，100】、【100，900】
• TTL即time to live,MergeTree提供了可以管理表或列的生命周期的功能

2.2.4 ReplacingMergeTree引擎

DROP TABLE IF EXISTS study.house;
CREATE TABLE study.house (
    id String,
    city String,
    region String,
    name String,
    price Float32,
    publish_date DateTime
) ENGINE=ReplacingMergeTree(publish_date) PARTITION BY toYYYYMMDD(publish_date) PRIMARY KEY(id) ORDER BY (id, city, region, name)
SETTINGS index_granularity=8192

• 实际上是以order by作为唯一键
• 去重不能跨分区
• 只有同一批插入或合并分区(合并时间不定)时才会去重，也可以手工合并optimize table study.house final;
• 去重保留版本字段值【ReplacingMergeTree括号中参数publish_date为版本号】最大的，如果版本字段相同则按插入顺序保留最后一笔

2.2.5 SummingMergeTree引擎

DROP TABLE IF EXISTS study.house;
CREATE TABLE study.house (
    id String,
    city String,
    region String,
    name String,
    price Float32,
    publish_date DateTime
) ENGINE=SummingMergeTree(price) PARTITION BY toYYYYMMDD(publish_date) PRIMARY KEY(id) ORDER BY (id, city, region, name)
SETTINGS index_granularity=8192

• 以SummingMergeTree()中指定的列作为汇总列
• 以order by 列为准，作为维度列
• 可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段作为汇总列
• 聚合不能跨分区
• 只有同一批插入或合并分区(合并时间不定)时才会去重，也可以手工合并optimize table study.house final;
• 其他的列按插入顺序保留第一行