关于ClickHouse的表引擎和SQL操作

目录

前言：

一.表引擎 (严格区分大小写)

1.TinyLog引擎

2.Memory 

3.MergeTree 

二.Sql操作 

clickhouse 和 mysql 的比较

1 create 

2  Insert

3 Update 和 Delete

---

前言：

在学习使用clickhouse时，首先就要先认识它的一大特点就是表引擎（在创建表时使用）。

表引擎是ClickHouse中用于存储和管理数据的一种技术，它定义了ClickHouse如何组织数据以及如何处理数据的方式。表引擎是ClickHouse的一个关键特性，因为它允许用户选择适合其数据需求的最佳存储方案。

不同的表引擎提供不同的功能和优缺点，用户可以根据其数据类型和使用情况选择最适合自己的表引擎。

例如，

针对日志类型的数据，ClickHouse的默认表引擎MergeTree可以提供更好的性能和扩展性；而针对时间序列数据，AggregatingMergeTree和SummingMergeTree则提供了更好的聚合计算功能。

表引擎可以帮助提高查询性能、节约存储空间、优化数据分区和满足不同数据类型的处理需求。ClickHouse的表引擎是其高性能和可扩展性的核心，因此选择合适的表引擎对于提高ClickHouse的性能和效率至关重要。

常用表引擎：

1. TinyLog       不支持索引

2. Memory       内存引擎

3. MergeTree    最强大的表引擎，支持索引和分区

一.表引擎 (严格区分大小写)

1.TinyLog引擎

TinyLog引擎 TinyLog是ClickHouse的一种表引擎，它提供了一种简单的方式来存储数据，并且能够支持高并发的写入操作。TinyLog表引擎将数据存储在磁盘上，并且支持压缩。TinyLog引擎可以用于存储实时数据流，或者用于在ClickHouse数据仓库中进行快速的筛选和排序操作。下面是使用TinyLog引擎的案例说明：

创建表并使用TinyLog引擎：

CREATE TABLE tinylog_example (id UInt32,name String,age UInt8
) ENGINE = TinyLog;

2.Memory 

Memory引擎 Memory引擎是ClickHouse中非常常用的引擎之一，它将数据存储在RAM中，因此非常适用于需要快速处理的数据。Memory引擎不支持压缩，因此对于大量数据的存储和处理，它并不是最佳选择。下面是使用Memory引擎的案例说明：

创建表并使用Memory引擎：

CREATE TABLE memory_example (id UInt32,name String,age UInt8
) ENGINE = Memory;

3.MergeTree 

MergeTree引擎 MergeTree是ClickHouse中最常用的引擎之一，它是一种支持分区和索引的表引擎，它使用了一种基于时间的分区方式，可以快速地进行数据查询和统计。MergeTree引擎支持多种类型的索引，例如BloomFilter和RangeIndex，可以快速地过滤和查找数据。下面是使用MergeTree引擎的案例说明：

创建表并使用MergeTree引擎：

CREATE TABLE mergetree_example (id UInt32,name String,age UInt8
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(created_at)
ORDER BY (created_at, id)
SETTINGS index_granularity = 8192;

注意：

partition by   : 分区（可选）

order by       : 排序（必选）

primary ky    : 主键（可选）

二.Sql操作 

clickhouse 和 mysql 的比较

共同点：

• 都是关系型数据库，支持SQL查询语言；
• 支持事务处理，具备 ACID 特性（原子性、一致性、隔离性、持久性）；
• 可以使用索引来提高查询效率；
• 支持备份和恢复数据。

优点：

ClickHouse：

• 面向列的存储模式，在 OLAP 场景下查询速度更快；
• 支持高并发和大规模数据的处理；
• 可以快速地处理复杂的数据分析和聚合操作；
• 支持分布式部署。

MySQL：

• 支持 OLTP 场景下的高并发，适用于在线事务处理；
• 支持多种存储引擎，可以根据不同场景选择不同的存储引擎；
• 支持主从复制和集群部署；
• 有丰富的社区支持，应用广泛

1 create 

在 ClickHouse 中创建表的 SQL 语句与 MySQL 类似，但是需要指定引擎类型，例如，使用 MergeTree 引擎存储数据：

CREATE TABLE example_table (column1 String,column2 Int32,column3 Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(column3)
ORDER BY (column1, column2);

解释：

• example_table 是表名；
• column1、column2、column3 是表中的列名；
• String、Int32、Float64 是列的数据类型；
• ENGINE 指定了使用 MergeTree 引擎存储数据（还有TinyLog 和 memory）
• PARTITION BY 根据日期分区，partition key 为 column3；
• ORDER BY 指定了排序规则，按照 column1 和 column2 排序。

---

2  Insert

ClickHouse 的 INSERT 语法和 MySQL 基本一致

   1.标准 INSERT

INSERT INTO example_table(column1, column2, column3)
VALUES ('value1', 123, 1.23), ('value2', 456, 4.56);

    解释：

• example_table 是要插入数据的表名；
• column1、column2、column3 是表中的列名；
• VALUES 后跟着要插入的数据，多条数据用逗号分隔2

    2 2.从表到表的插入

INSERT INTO example_table(column1, column2, column3)
SELECT column_a, column_b, column_c FROM example_table_2;

解释：

• example_table 是要插入数据的表名；
• column1、column2、column3 是表中的列名；
• SELECT 子句用于选取要插入的数据，从 example_table_2 表中选取 column_a、column_b、column_c 这三个列。

注意，使用 INSERT INTO 语句插入数据时，数据会先缓存在内存中，并不是立即写入磁盘，需要手动执行 optimize table  或等待后台自动执行。

3 Update 和 Delete

在 ClickHouse 中，Mutation 查询包括 Delete 和 Update 两种操作，并且不支持事务。Mutation 查询会导致目标数据的原有分区被放弃，重建新分区，因此最好进行批量的变更，避免频繁小数据的操作。

以下是 Delete 和 Update 的示例 SQL 语句：

   3.1.删除操作

ALTER TABLE t_order_smt DELETE WHERE sku_id = 'sku_001';

解释：

• t_order_smt 是要删除数据的表名；
• sku_id = 'sku_001' 为删除的过滤条件。

    3.2.修改操作

ALTER TABLE t_order_smt UPDATE total_amount = toDecimal32(2000.00, 2) WHERE id = 102;

解释：

• t_order_smt 是要修改数据的表名；
• total_amount = toDecimal32(2000.00, 2) 为修改的内容；
• id = 102 为修改的过滤条件。

Mutation 查询的操作比较“重”，因此它分两步执行。同步执行的部分其实只是进行新增数据新增分区和把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间。一般不会开放这样的功能给用户，由管理员完成。

示例：

-- 创建表 t_order_smt 并插入数据
CREATE TABLE t_order_smt
(id UInt32,sku_id String,total_amount Float32
)
ENGINE = MergeTree
PARTITION BY id
ORDER BY (id, sku_id);INSERT INTO t_order_smt VALUES (101, 'sku_001', 1000.00), (102, 'sku_002', 1500.00);-- 修改 id 为 102 的 total_amount
ALTER TABLE t_order_smt UPDATE total_amount = toDecimal32(2000.00, 2) WHERE id = 102;-- 删除 sku_id 为 'sku_001' 的数据
ALTER TABLE t_order_smt DELETE WHERE sku_id = 'sku_001';