Clickhouse 与 MySQL 常用转换
一、MySQL数据类型与ClickHouse数据类型的对应关系
| MySQL类型 | ClickHouse类型 | 备注 |
|---|---|---|
| TINYINT | Int8 | MySQL的无符号版本（TINYINT UNSIGNED）对应ClickHouse的UInt8 |
| SMALLINT | Int16 | MySQL的无符号版本（SMALLINT UNSIGNED）对应ClickHouse的UInt16 |
| MEDIUMINT | Int32 | MySQL的无符号版本（MEDIUMINT UNSIGNED）对应ClickHouse的UInt32 |
| INT | Int32 | MySQL的无符号版本（INT UNSIGNED）对应ClickHouse的UInt32 |
| BIGINT | Int64 | MySQL的无符号版本（BIGINT UNSIGNED）对应ClickHouse的UInt64 |
| FLOAT | Float32 | |
| DOUBLE | Float64 | |
| DATE | Date | |
| DATETIME, TIMESTAMP | DateTime | ClickHouse的DateTime可以指定时区 |
| TIME | Int32/String | 可以用Int32表示秒数，或String存储原始时间字符串 |
| CHAR, VARCHAR | String | ClickHouse中没有VARCHAR的长度限制，统一使用String类型 |
| TEXT, MEDIUMTEXT, LONGTEXT | String | ClickHouse的String类型可以存储大文本 |
| BINARY, VARBINARY | FixedString(N)/String | FixedString(N)用于固定长度，String用于可变长度 |
| BLOB | String | |
| ENUM | Int8/Int16/String | 通常将ENUM映射为整数类型或String，并在应用层维护映射 |
| SET | Array(Int8/Int16)/String | 将SET映射为整数数组或String，并在应用层维护映射 |
| JSON | String | 使用String存储JSON数据，可用内置函数解析 |
请注意，这个表格提供了一般的对应关系和建议。在实际的迁移或数据设计过程中，可能需要根据特定的数据特征和使用场景进行适当的调整。例如，对于时间类型，如果需要精确到毫秒或微秒，ClickHouse提供了DateTime64类型。此外，ClickHouse也支持更多复杂的数据类型如数组（Array）、元组（Tuple）和嵌套类型（Nested），可以用于表示更复杂的数据结构。
二、ClickHouse 包含了各种数据类型的建表语句
sqlCopy code
CREATE TABLE sample_table
(
-- 数字类型
user_id UInt32,
age Int8,
score Float32,
height Float64,
-- 日期和时间类型
birth_date Date,
last_login DateTime('Asia/Shanghai'), -- 指定时区
event_timestamp DateTime64(3), -- 支持毫秒精度
-- 字符串类型
user_name String,
email FixedString(255),
-- 数组类型（Array）
recent_scores Array(Int16),
tags Array(String),
-- 枚举类型
gender Enum8('male' = 1, 'female' = 2, 'other' = 3),
-- 元组类型（Tuple）
coordinate Tuple(Float64, Float64),
-- 嵌套类型（Nested）
visits Nested
(
date Date,
url String
),
-- 低基数类型（适用于有限的字符串集合）
device_type LowCardinality(String),
-- JSON字符串
user_preferences String,
-- UUID类型
session_id UUID
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(birth_date)
ORDER BY (user_id, last_login)
SETTINGS index_granularity = 8192;
这个示例展示了ClickHouse支持的各种数据类型：
- 数字类型：如
UInt32,Int8,Float32,Float64等。 - 日期和时间类型：
Date,DateTime（可以指定时区），以及支持高精度的DateTime64。 - 字符串类型：
String和固定长度的FixedString。 - 数组类型（
Array）：可以存储同一类型数据的数组，如Array(Int16),Array(String)等。 - 枚举类型（
Enum8,Enum16）：用于表示有限的集合，每个元素有对应的整数值。 - 元组类型（
Tuple）：可以包含多个不同类型的字段。 - 嵌套类型（
Nested）：用于表示重复的结构化数据，如日志条目。 - 低基数类型（
LowCardinality）：用于优化存储和查询性能，适用于值的集合较小的字段。 - UUID类型：用于存储UUID值。
请注意，user_preferences字段虽然声明为String类型，但可以用于存储JSON字符串，并使用ClickHouse的JSON函数进行解析和查询。此外，表的分区和排序键以及索引粒度（index_granularity）都是根据数据访问模式来设定的，以优化查询性能。
三、SETTINGS index_granularity = 8192; 这句话是什么意思
在ClickHouse中，SETTINGS index_granularity = 8192; 这句话用于指定表的索引粒度。索引粒度是ClickHouse中一个重要的性能配置参数，它决定了数据存储时的索引间隔大小。
索引粒度（Index Granularity）的含义
- 索引粒度定义了每个索引块（也称为数据块或标记）中存储的数据行数。在给定的粒度下，每8192行数据会存储一个索引项。
- 当执行查询时，ClickHouse利用这些索引项来快速定位到包含所需数据的数据块，从而减少需要读取的数据量，提高查询效率。
对性能的影响
- 较小的索引粒度可以提高查询性能，因为它使得ClickHouse更精确地定位到包含查询数据的小块。然而，这会增加索引的大小，从而占用更多的内存和磁盘空间。
- 较大的索引粒度减少了索引的大小，节约内存和磁盘空间，但可能会降低查询性能，因为每次查询可能需要读取更多的数据。
默认值和调整
- ClickHouse的默认索引粒度通常是8192，这是一个在许多场景下都比较合理的平衡值。
- 根据您的具体数据访问模式和性能需求，您可以调整这个值。例如，如果您的查询通常涉及到大量的顺序扫描，增加索引粒度可能有助于提高性能。相反，如果您的查询需要非常精确地定位少量行，减小索引粒度可能更有效。
总的来说，SETTINGS index_granularity = 8192; 这句话在创建表时设置了表的索引粒度为8192，这是影响查询性能和存储效率的一个关键配置。在实际应用中，应根据数据特性和查询需求调整此参数以达到最优性能。
四、MySQL分区语句转化为 CK
在ClickHouse中，如果您想创建与您提供的MySQL分区语句（例如PARTITION jfgw_point_pay_info_202001 VALUES LESS THAN ('2020-01-01 00:00:00')）相对应的特定时间范围的分区，您通常会使用基于日期或时间列的PARTITION BY表达式。ClickHouse根据您提供的分区键表达式动态地处理分区，因此您不需要像在MySQL中那样手动定义每个分区的范围。
下面是在ClickHouse中定义类似分区方案的方法：
sqlCopy code
CREATE TABLE jfgw_point_pay_info
(
-- 您的列定义在这里
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(INSERT_TIME) -- 通过月份对数据进行分区
ORDER BY (ID, INSERT_TIME); -- 您的主键，用于每个分区内的排序
在这个例子中，PARTITION BY toYYYYMM(INSERT_TIME)会根据INSERT_TIME列自动为每个月创建一个新的分区。每个分区将包含所有INSERT_TIME的月份与分区键对应的行。您不需要手动为每个分区指定LESS THAN值；ClickHouse会根据PARTITION BY表达式自动管理这些。
如果您需要插入数据并确保它进入特定分区，您只需确保INSERT_TIME值落在所需的月份内。ClickHouse将根据PARTITION BY表达式自动将数据放置在正确的分区中。
对于高级用例，如果您需要更多控制分区的能力，超出了PARTITION BY表达式提供的范围，ClickHouse支持通过更复杂的表达式或手动数据操作来支持自定义分区方案。然而，对于大多数基于时间的分区需求，上面显示的方法应该足够了。