ClickHouse

一.介绍

ClickHouse是一种OLAP类型的列式数据库管理系统，在大数据量的分析处理应用中Clickhouse表现很优秀。

ClickHouse 在 Hadoop 生态中常作为高性能查询引擎，用于加速大数据的分析过程.

列式数据库的优势

1、针对分析类查询，通常只需要读取表的一小部分列。在列式数据库中你可以只读取你需要的数据。例如，如果只需要读取100列中的5列，这将帮助你最少减少20倍的I/O消耗。

2、由于数据总是打包成批量读取的，所以压缩是非常容易的。同时数据按列分别存储这也更容易压缩。这进一步降低了I/O的体积。

3、由于I/O的降低，这将帮助更多的数据被系统缓存。

OLAP和OATP

OLTP全称是On-line Transaction Processing，是一种联机事务型数据库，典型的数据库就是关系型数据库，OLTP关注的是对业务数据的增删改查，面向用户的事务操作，追求效率的最优解。但是遇到需要对数据进行分析的场景，OLTP类型的数据库就不占优势了。

OLAP全称是On-Line Analytical Processing，是一种联机分析处理数据库，一般用于数据仓库或者大数据分析处理，这种类型的数据库在事务能力上很弱，但是在分析的场景下很强大。

OLAP型数据库使用场景

1、绝大多数是读请求

2、数据以相当大的批次(> 1000行)更新，而不是单行更新;或者根本没有更新。

3、已添加到数据库的数据不能修改。

4、对于读取，从数据库中提取相当多的行，但只提取列的一小部分。

5、宽表，即每个表包含着大量的列

6、查询相对较少(通常每台服务器每秒查询数百次或更少)

7、对于简单查询，允许延迟大约50毫秒

8、列中的数据相对较小：数字和短字符串(例如，每个URL 60个字节)

9、处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)

10、事务不是必须的

11、对数据一致性要求低

12、每个查询有一个大表。除了他以外，其他的都很小。

13、查询结果明显小于源数据。换句话说，数据经过过滤或聚合，因此结果适合于单个服务器的RAM中

ClickHouse的特性

ClickHouse的优点

1、真正的列式数据库管理系统

2、优秀的数据压缩能力

3、数据的磁盘存储，降低设备预算

4、多核心并行处理，ClickHouse会使用服务器上一切可用的资源，从而以最自然的方式并行处理大型查询。

5、多服务器分布式处理

6、支持SQL，降低学习成本

7、向量引擎，数据不仅仅按列存储，同时还按向量(列的一部分)进行处理，这样可以更加高效地使用CPU。

8、实时的数据更新，数据可以持续不断地高效的写入到表中，并且写入的过程中不会存在任何加锁的行为。

9、索引，按照主键对数据进行排序，这将帮助ClickHouse在几十毫秒以内完成对数据特定值或范围的查找。

10、适合在线查询

11、支持近似计算

12、自适应的join算法，JOIN多个表，它更倾向于散列连接算法，如果有多个大表，则使用合并-连接算法。

13、支持数据复制和数据完整性

14、角色的访问控制。

ClickHouse的缺点

1、没有完整的事务支持。

2、缺少高频率，低延迟的修改或删除已存在数据的能力。仅能用于批量删除或修改数据。

3、ClickHouse不适合通过检索单行的点查询。

ClickHouse对应用侧的支持

对于ClickHouse的使用，官方提供了命令行客户端、JDBC驱动、ODBC驱动、C++客户端。同时社区中还有很多第三方库可以使用，因此在应用上的会便利很多。

近似计算

ClickHouse 提供了以准确性换取性能的方法。例如，它的一些聚合函数大约计算非重复值 count、中位数和分位数。此外，还可以对数据样本运行查询，以快速计算近似结果。最后，可以使用有限数量的键运行聚合，而不是针对所有键运行聚合。根据键分布的偏斜程度，这可以提供一个相当准确的结果，该结果使用的资源比精确计算少得多。

二.基本使用

基础数据类型

整数类型

有符号整数：Int8, Int16, Int32, Int64
无符号整数：UInt8, UInt16, UInt32, UInt64

在ck中，也可以通过一些别名来代替整型

Int8 — TINYINT, BOOL, BOOLEAN, INT1.
Int16 — SMALLINT, INT2.
Int32 — INT, INT4, INTEGER.
Int64 — BIGINT.

浮点类型

Float32 - 单精度浮点(等同于Java的float)
Float64 - 双精度浮点(等同于Java的double)

十进制类型

Decimal(P, S) - 精确小数，P是精度(1-38)，S是小数位数
Decimal32(S), Decimal64(S), Decimal128(S) - 特定精度的十进制

布尔类型

Bool - 存储为UInt8，0或1

字符串类型

String - 字符串的长度可以是任意的。长度没有限制。值可以包含任意字节集合，包括空字节。
String 类型替代了其他数据库管理系统中的类型 VARCHAR、BLOB、CLOB 等。

创建表时，可以为字符串字段设置数值参数（例如 VARCHAR(255)），但 ClickHouse 会忽略它们。
FixedString(N) - 固定长度N的字符串

日期时间类型

Date - 日期(精度到天)

创建一个带有 Date 类型列的表并插入数据：

CREATE TABLE dt
(
    `timestamp` Date,
    `event_id` UInt8
)
ENGINE = TinyLog;

-- Parse Date
-- - from string,
-- - from 'small' integer interpreted as number of days since 1970-01-01, and
-- - from 'big' integer interpreted as number of seconds since 1970-01-01.
INSERT INTO dt VALUES ('2019-01-01', 1), (17897, 2), (1546300800, 3);

SELECT * FROM dt;

┌──timestamp─┬─event_id─┐
│ 2019-01-01 │        1 │
│ 2019-01-01 │        2 │
│ 2019-01-01 │        3 │
└────────────┴──────────┘

Date32 - 扩展日期范围(1900-2299)
DateTime - 时间戳(秒级精度)

DateTime64 - 高精度时间戳(可指定小数位)

语法：

1	DateTime64(precision, [timezone])

示例：

创建一个包含 DateTime64 类型列的表并插入数据：

CREATE TABLE dt64
(
    `timestamp` DateTime64(3, 'Asia/Istanbul'),
    `event_id` UInt8
)
ENGINE = TinyLog;

-- Parse DateTime
-- - from integer interpreted as number of seconds since 1970-01-01.
-- - from string,
INSERT INTO dt64 VALUES (1546300800123, 1), (1546300800.123, 2), ('2019-01-01 00:00:00', 3);

SELECT * FROM dt64;

┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00.123 │        1 │
│ 2019-01-01 03:00:00.123 │        2 │
│ 2019-01-01 00:00:00.000 │        3 │
└─────────────────────────┴──────────┘

2.筛选 DateTime64 值

1	SELECT * FROM dt64 WHERE timestamp = toDateTime64('2019-01-01 00:00:00', 3, 'Asia/Istanbul');

1
2
3

┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 00:00:00.000 │        3 │
└─────────────────────────┴──────────┘

与 DateTime 不同，DateTime64 值不会自动从 String 转换。

1	SELECT * FROM dt64 WHERE timestamp = toDateTime64(1546300800.123, 3);

┌───────────────timestamp─┬─event_id─┐
│ 2019-01-01 03:00:00.123 │        1 │
│ 2019-01-01 03:00:00.123 │        2 │
└─────────────────────────┴──────────┘

相对于插入，toDateTime64 函数将所有值视为小数变体，因此需要在小数点后给出精度。

3.时区转换

SELECT
toDateTime64(timestamp, 3, 'Europe/London') as lon_time,
toDateTime64(timestamp, 3, 'Asia/Istanbul') as istanbul_time
FROM dt64;

┌────────────────lon_time─┬───────────istanbul_time─┐
│ 2019-01-01 00:00:00.123 │ 2019-01-01 03:00:00.123 │
│ 2019-01-01 00:00:00.123 │ 2019-01-01 03:00:00.123 │
│ 2018-12-31 21:00:00.000 │ 2019-01-01 00:00:00.000 │
└─────────────────────────┴─────────────────────────┘

Time64- 存储具有亚秒精度的时间值。与 DateTime64 不同，它不包括日历日期，仅表示时间。精度定义了存储值在小数秒中的分辨率。

Time64 以从一天开始（000:00:00.000000000）以来的 Int64 类型的刻度数存储数据。

刻度大小（精度）：10^(-precision)秒。有效范围：[ 0 : 9 ]。通常使用 3（毫秒）、6（微秒）、9（纳秒）。

语法：
1
Time64(precision)

复合数据类型

数组类型

Array(T) - T类型元素的数组，如Array(Int32)

创建数组

使用函数来创建数组：

array(T)

还可以使用方括号。

[]

创建数组的示例：

1	SELECT array(1, 2) AS x, toTypeName(x)

1
2
3

┌─x─────┬─toTypeName(array(1, 2))─┐
│ [1,2] │ Array(UInt8)            │
└───────┴─────────────────────────┘

参数类型（TypeName）定义为能够存储所有列出参数的最小数据类型

1	SELECT [1, 2] AS x, toTypeName(x)

1
2
3

┌─x─────┬─toTypeName([1, 2])─┐
│ [1,2] │ Array(UInt8)       │
└───────┴────────────────────┘

如果尝试创建不兼容数据类型的数组，ClickHouse 会抛出异常：

1	SELECT array(1, 'a')

1
2

Received exception from server (version 1.1.54388):
Code: 386. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: There is no supertype for types UInt8, String because some of them are String/FixedString and some of them are not.

元组类型

Tuple(T1, T2, ...) - 元组，可包含不同类型，必须至少包含一个元素

元组用于临时列分组。在查询中使用 IN 表达式时，可以对列进行分组，并用于指定某些 lambda 函数的正式参数。

创建元组

您可以使用函数来创建元组：

1	tuple(T1, T2, ...)

创建元组的示例：

1	SELECT tuple(1, 'a') AS x, toTypeName(x)

1
2
3

┌─x───────┬─toTypeName(tuple(1, 'a'))─┐
│ (1,'a') │ Tuple(UInt8, String)      │
└─────────┴───────────────────────────┘

语法 (tuple_element1, tuple_element2) 可用于在不调用 tuple() 函数的情况下创建多个元素的元组。

示例：

1	SELECT (1, 'a') AS x, (today(), rand(), 'someString') AS y, ('a') AS not_a_tuple;

1
2
3

┌─x───────┬─y──────────────────────────────────────┬─not_a_tuple─┐
│ (1,'a') │ ('2022-09-21',2006973416,'someString') │ a           │
└─────────┴────────────────────────────────────────┴─────────────┘

引用元组元素

可以通过名称或索引来引用元组元素：

CREATE TABLE named_tuples (`a` Tuple(s String, i Int64)) ENGINE = Memory;
INSERT INTO named_tuples VALUES (('y', 10)), (('x',-10));

SELECT a.s FROM named_tuples; -- by name
SELECT a.2 FROM named_tuples; -- by index

结果：

┌─a.s─┐
│ y   │
│ x   │
└─────┘

┌─tupleElement(a, 2)─┐
│                 10 │
│                -10 │
└────────────────────┘

嵌套类型

Nested(Name1 Type1, Name2 Type2, ...) - 嵌套数据结构

特殊类型

Nullable(T) - 可为空的T类型
LowCardinality(T) - 低基数优化类型
IPv4 - IPv4地址(内部存储为UInt32)
IPv6 - IPv6地址(内部存储为FixedString(16))
UUID - 通用唯一标识符

高级数据类型

枚举类型

Enum8 - 枚举值(8位，最多256个值)
Enum16 - 枚举值(16位，最多65536个值)
- 命名值可以被声明为 'string' = integer 对或仅为 'string' 名称。 ClickHouse 仅存储数字，但通过它们的名称支持与这些值的操作。
  
  在插入数据时，ClickHouse 自动选择 Enum 的类型。你也可以使用 Enum8 或 Enum16 类型以确保存储的大小。
  1
  2
  3
  4
  5
  CREATE TABLE t_enum
  (
  x Enum('hello' = 1, 'world' = 2)
  )
  ENGINE = TinyLog
  如果你需要查看行的数字等效值，必须将 Enum 值转换为整数类型。
  1
  SELECT CAST(x, 'Int8') FROM t_enum
  1
  2
  3
  4
  ┌─CAST(x, 'Int8')─┐
  │ 1 │
  │ 2 │
  └─────────────────┘
  与元组的比较操作
  
  两个元组通过从左到右依次比较其元素进行比较。如果第一个元组的元素大于（小于）第二个元组的对应元素，则第一个元组大于（小于）第二个元组，否则（如果两个元素相等），则比较下一个元素。
  
  示例：
  1
  SELECT (1, 'z') > (1, 'a') c1, (2022, 01, 02) > (2023, 04, 02) c2, (1,2,3) = (3,2,1) c3;
  1
  2
  3
  ┌─c1─┬─c2─┬─c3─┐
  │ 1 │ 0 │ 0 │
  └────┴────┴────┘

聚合函数类型

AggregateFunction(name, types...) - 聚合函数中间状态

地理数据类型

Point - 二维点
Ring - 闭合线
Polygon - 多边形
MultiPolygon - 多多边形

其他特殊类型

SimpleAggregateFunction - 简单聚合函数

Map(key, value) - 键值对集合（与其他数据库不同，ClickHouse 中的映射并不是唯一的，即一个映射可以包含两个具有相同键的元素。这是因为映射在内部实现为 Array(Tuple(K, V))。）

将元组转换为映射

类型为 Tuple() 的值可以使用函数 CAST 转换为类型为 Map() 的值：

示例

查询：

1	SELECT CAST(([1, 2, 3], ['Ready', 'Steady', 'Go']), 'Map(UInt8, String)') AS map;

结果：

1
2
3

┌─map───────────────────────────┐
│ {1:'Ready',2:'Steady',3:'Go'} │
└───────────────────────────────┘

创建表

在ck中创建表的方式有很多，结构化语句建表是最常见的方式之一。

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1] [compression_codec] [TTL expr1],
    name2 [type2] [NULL|NOT NULL] [DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr2] [compression_codec] [TTL expr2],
    ...
) ENGINE = engine

在属性字段中：

第一个字段name表示属性的名称
第二个字段[type]表示数据的类型
第三个字段[NULL|NOT NULL]定义该字段允许或不允许为Nullable
第四个字段[DEFAULT|MATERIALIZED|EPHEMERAL|ALIAS expr1]表示该字段的默认值表达式，如果该字段为空，默认值为零（数字）、空字符串（字符串）、空数组（数组）等。其中最常用的是DEFAULT expr，比如Hits UInt32 DEFAULT 0。
- DEFAULT expr
- - 普通的默认值，如果INSERT中不包含指定的列，那么将通过表达式计算它的默认值并填充它。
- MATERIALIZED expr
- - 物化表达式，被该表达式指定的列不能包含在INSERT的列表中，因为它总是被计算出来的。对于INSERT而言，不需要考虑这些列。另外，在SELECT查询中如果包含星号，此列不会被用来替换星号，这是因为考虑到数据转储，在使用SELECT *查询出的结果总能够被’INSERT’回表。
- ALIAS expr
- - 别名。这样的列不会存储在表中。它的值不能够通过INSERT写入，同时使用SELECT查询星号时，这些列也不会被用来替换星号。但是它们可以显示的用于SELECT中，在这种情况下，在查询分析中别名将被替换。
第五个字段[compression_codec]定义字段的压缩方法，默认采用lz4压缩方法，可以通过下面的方式dt Date CODEC(ZSTD)指定压缩方法。
第六个字段[TTL expr1]代表值的存储时间，只能为 MergeTree 系列表指定。

通过ENGINE可以指定表所用到的引擎，比如最常用的MergeTree。通过PRIMARY KEY(expr1[, expr2,...])]可以定义表的主键。

了结构化方式建表之外，ck还提供了更多创建表的方式从另一张表中创建表：

1	CREATE TABLE [IF NOT EXISTS] [db.]table_name AS [db2.]name2 [ENGINE = engine]

例子：

1	CREATE TABLE user2 AS user

通过select语句建表：

1	CREATE TABLE [IF NOT EXISTS] [db.]table_name[(name1 [type1], name2 [type2], ...)] ENGINE = engine AS SELECT ...

例子：

1 2	CREATE TABLE user3 ENGINE = MergeTree PRIMARY KEY(id) AS select * from user;

DQL与MySQL基本一致也用`LIMIT m` 从结果中选择前 `m` 行

更多语法见CHECK TABLE 语句 | ClickHouse Docs

三.ClickHouse的表引擎

ClickHouse 的表引擎是其最核心的特性之一，引擎决定了表的存储方式、访问方式、并发能力和功能特性。

一、MergeTree 系列引擎（核心引擎）

1. MergeTree

基础引擎，支持索引和分区，适合大规模数据分析

CREATE TABLE table_name (
    ...
) ENGINE = MergeTree()
ORDER BY (columns)          -- 必须指定排序键
PARTITION BY (columns)      -- 可选分区

2. ReplacingMergeTree

去重引擎，相同排序键的数据会被去重（仅合并时触发）

1 2	ENGINE = ReplacingMergeTree([ver_column]) -- 可选版本参数，默认按插入顺序保留最后一条

3. SummingMergeTree

预聚合引擎，合并时对相同排序键的数值列求和

1 2	ENGINE = SummingMergeTree([columns_to_sum]) -- 不指定列则对所有数值列求和

4. AggregatingMergeTree

聚合状态引擎，存储聚合函数中间状态

1 2	ENGINE = AggregatingMergeTree() -- 需配合AggregateFunction类型使用

5. CollapsingMergeTree

折叠引擎，通过sign标记实现行级”删除/更新”

1 2	ENGINE = CollapsingMergeTree(sign_column) -- sign=1表示有效行，sign=-1表示要删除的行

6. VersionedCollapsingMergeTree

带版本的折叠引擎，解决乱序数据问题

1	ENGINE = VersionedCollapsingMergeTree(sign_column, version_column)

7. GraphiteMergeTree

时序数据专用引擎，内置Graphite聚合规则

1	ENGINE = GraphiteMergeTree('config_section')

二、日志系列引擎（轻量级引擎）

1. TinyLog

微型引擎，不支持索引，数据存储在磁盘

1 2	ENGINE = TinyLog -- 适合小表(1万行以下)

2. Log

日志引擎，比TinyLog多标记文件支持并行读

1	ENGINE = Log

3. StripeLog

带列标记的日志引擎，支持按列读取

1	ENGINE = StripeLog

三、集成引擎（外部数据接口）

1. Kafka

Kafka集成引擎，直接消费Kafka数据

ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'host:port',
    kafka_topic_list = 'topic',
    kafka_group_name = 'group'

2. MySQL

MySQL映射引擎，像表一样查询MySQL数据

1	ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password')

3. JDBC/ODBC

通用数据库连接引擎

1	ENGINE = JDBC('dsn', 'database', 'table')

4. HDFS

HDFS文件引擎，直接读取HDFS文件

1	ENGINE = HDFS('hdfs://path/to/file', 'format')

四、特殊用途引擎

1. Memory

内存引擎，数据不持久化，重启丢失

1	ENGINE = Memory

2. Distributed

分布式引擎，不存储数据，作为集群代理

1	ENGINE = Distributed(cluster, database, table[, sharding_key])

3. MaterializedView

物化视图引擎，实际是持久化查询结果

1
2
3

CREATE MATERIALIZED VIEW mv 
ENGINE = MergeTree()...  -- 必须指定存储引擎
AS SELECT ...

4. Dictionary

字典引擎，访问外部字典数据

1	ENGINE = Dictionary(dict_name)

5. Merge

合并引擎，同时查询多张表

1	ENGINE = Merge(database, 'table_regexp')

6. File

文件引擎，将查询结果输出到文件

1	ENGINE = File(format_name)

7. URL

URL引擎，通过HTTP访问远程数据

1	ENGINE = URL('url', format)

五、引擎选择建议

主要业务表：优先选择MergeTree系列引擎
临时数据/小表：使用Log/TinyLog引擎
外部数据集成：使用对应的集成引擎
分布式环境：本地表+Distributed表组合使用
特殊场景：根据需求选择折叠/聚合等变种引擎

每种引擎都有特定的配置参数和优化方式，实际使用时需要根据数据规模、访问模式和功能需求综合考虑。

ClickHouse数据库