clickhouse组件介绍
写在前面
今天学习clickhouse部分的知识。
ClickHouse
OLTP (联机事务处理系统)
例如 MySQL 等关系型数据库,适用于小数据量时的快速查询和分析。OLTP 主要针对增删改操作,数据经常发生变化。
OLAP (联机分析处理系统)
适用于数据长期不变且有大量历史数据的场景,主要进行分析操作,增删改操作较少。
OLAP 特点
- 绝大多数是读请求。
- 数据以批次(> 1000 行)更新,或根本没有更新。
- 已添加到数据库的数据不能修改。
- 提取大量行,但仅提取列的一小部分。
- 宽表,每个表包含大量列。
- 查询较少(每秒查询数百次或更少)。
- 简单查询允许约 50 毫秒延迟。
- 列中的数据相对较小:数字和短字符串(例如,每个 URL 60 字节)。
- 高吞吐量处理单个查询(每秒可达数十亿行)。
- 事务不是必须的。
- 对数据一致性要求低。
- 每个查询涉及一个大表,其他表很小。
- 查询结果明显小于源数据(数据经过过滤或聚合)。
数据类型
整数类型
-
负数:
Int8
: [-128 : 127]Int16
: [-32768 : 32767]Int32
: [-2147483648 : 2147483647]Int64
: [-9223372036854775808 : 9223372036854775807]Int128
: [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]Int256
: [-57896044618658097711785492504343953926634992332820282019728792003956564819968 : 57896044618658097711785492504343953926634992332820282019728792003956564819967]
-
只有0和正数:
UInt8
: [0 : 255]UInt16
: [0 : 65535]UInt32
: [0 : 4294967295]UInt64
: [0 : 18446744073709551615]UInt128
: [0 : 340282366920938463463374607431768211455]UInt256
: [0 : 115792089237316195423570985008687907853269984665640564039457584007913129639935]
字符串类型
String
: 可变长字符串FixedString(length)
: 固定长字符串,参数为字节数,执行效率较高
日期类型
Date
: 年-月-日Date32
: 年-月-日DateTime
: 年-月-日 时-分-秒DateTime64
: 年-月-日 时-分-秒.毫秒
示例
-- 建表语句
CREATE TABLE date_test (
date1 Date,
date2 Date32,
date3 DateTime,
date4 DateTime64
) ENGINE = TinyLog;
-- 插入语句
INSERT INTO date_test VALUES ('2023-11-21', '2023-11-21', '2023-11-21', '2023-11-21');
INSERT INTO date_test VALUES (1711435333589, 1711435333589, 1711435333589, 1711435333589);
-- 结果:2024-03-26 15:33:38
UUID 类型
ClickHouse 提供了一个函数 generateUUIDv4()
,生成 UUID,例如 bee32020-a6cb-49a6-a10b-427381b11613
可为空(Nullable)
- 使用
Nullable
处理不确定的字段值
CREATE TABLE test2 (
id Int32,
name Nullable(String)
) ENGINE = TinyLog;
INSERT INTO test2 VALUES (1001, NULL);
数组
- 使用
Array(T)
类型,数据类型在建表时指定。MergeTree 表引擎不允许出现数组嵌套。
示例
CREATE TABLE t1 (
col1 Array(Int8)
) ENGINE = TinyLog;
INSERT INTO t1 VALUES (array(11, 12, 13));
小数类型
- Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S)
- P: 精度,范围:[1:38]
- S: 规模,范围:[0:P]
示例
-- Decimal(4,2)
-- Decimal(7,5)
表操作
注意事项
- 建表时数据类型严格区分大小写。
- 建表时必须指定表引擎。
建表语句
CREATE TABLE users3 (
id Int8,
name FixedString(12),
gender Nullable(FixedString(3)),
clazz String
) ENGINE = TinyLog;
插入数据
-- 基本格式
INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
-- 示例
CREATE TABLE IF NOT EXISTS bigdata31.students_test (
id Int32,
name String,
gender FixedString(6),
clazz String
) ENGINE = TinyLog;
-- 插入数据
INSERT INTO students_test VALUES
(1001, '陆澳', '男', '特训营24期'),
(1002, '李佳豪', '男', '特训营24期'),
(1003, '郭香香', '女', '特训营24期');
INSERT INTO students_test VALUES
(1004, '王宇杰', '男', '特训营24期'),
(1005, '张怀远', '男', '特训营24期'),
(1006, '史俊超', '男', '特训营24期');
INSERT INTO students_test (name, gender, clazz) VALUES
('张玮', '男', '特训营24期');
-- 查看表结构
DESC 表名;
引擎
数据库引擎
- Atomic: 默认引擎,支持非阻塞的
DROP TABLE
和RENAME TABLE
查询。支持原子的EXCHANGE TABLES
查询。 - MySQL: 用于将远程 MySQL 服务器中的表映射到 ClickHouse 中。支持
INSERT
和SELECT
查询,不支持RENAME
,CREATE TABLE
,ALTER
。
创建数据库并映射远程 MySQL 服务
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE = MySQL('host:port', ['database' | database], 'user', 'password');
-- 示例
CREATE DATABASE IF NOT EXISTS shujia_ck_mysql ENGINE = MySQL('192.168.136.100:3306', 'bigdata31', 'root', '123456');
数据表引擎
Log
- 不支持索引,适用于临时数据、write-once 表、测试或演示目的。
TinyLog
- 简单的表引擎,适合相对较小的表(建议最多 1,000,000 行)。不支持索引。
StripeLog
- 将数据块逐列写入,支持并行读取,不支持
ALTER UPDATE
和ALTER DELETE
操作。
建表语句
CREATE TABLE stripe_log_table (
timestamp DateTime,
message_type String,
message String
) ENGINE = StripeLog;
-- 插入数据
INSERT INTO stripe_log_table VALUES
(now(), 'REGULAR', 'The first regular message'),
(now(), 'REGULAR', 'The second regular message'),
(now(), 'WARNING', 'The first warning message');
-- 示例
CREATE TABLE students_stripelog (
id Int32,
name String,
gender FixedString(3),
clazz String
) ENGINE = StripeLog;
-- 添加数据
INSERT INTO students_stripelog VALUES
(1001, '陆澳', '男', '特训营24期'),
(1002, '李佳豪', '男', '特训营24期'),
(1003, '郭香香', '女', '特训营24期');
MergeTree
- ClickHouse 中最强大的表引擎,用于快速写入大量数据,支持分区、数据副本和数据采样。
注意:默认是针对每一批数据按照分区字段的值进行分区
建表语句
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,
...
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr]
[SETTINGS name=value, ...];
PARTITION BY:用于数据分区,以提高查询效率。
ORDER BY:定义数据的排序方式。
PRIMARY KEY:指定主键索引。
SAMPLE BY:采样数据的列。
TTL:定义数据过期时间。
SETTINGS:表级别的设置。
示例
CREATE TABLE IF NOT EXISTS bigdata31.students (
id Int32,
name String,
gender FixedString(6),
clazz String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY id
PRIMARY KEY id;
-- 插入数据
INSERT INTO students VALUES
(1001, '陆澳', '男', '特训营24期'),
(1002, '李佳豪', '男', '特训营24期');...
ClickHouse 表引擎和数据优化
数据表优化
-
手动优化
使用OPTIMIZE TABLE
命令来合并分区数据,减少碎片,提高查询性能。OPTIMIZE TABLE goods_orders FINAL;
FINAL
参数用于执行最终的合并操作,确保数据在所有分区和数据副本中都被合并。此操作会将分区中的数据合并到一个单一的文件中,消除数据的冗余和碎片。 -
自动合并
ClickHouse 自动合并数据,以维护表的性能和存储空间。这是通过后台进程定期进行的。
表引擎选择
-
TinyLog
适用于小规模表,不支持索引,通常用于临时数据和测试。适用于行数较少、更新频繁的场景。 -
MergeTree
适用于大规模数据表,支持分区、排序和索引,提供高效的数据读取和写入性能。适用于数据量较大、查询复杂的场景。
常用函数
算术函数
-
plus(a, b)
或a + b
计算两个数值的总和。也可以将Date
或DateTime
与整数相加,表示增加相应的天数或秒数。SELECT plus(5, 10); -- 结果: 15 SELECT toDate('2024-01-01') + 10; -- 结果: '2024-01-11'
-
minus(a, b)
或a - b
计算两个数值之间的差。也可以将Date
或DateTime
减去整数,表示减少相应的天数或秒数。SELECT minus(15, 5); -- 结果: 10 SELECT toDate('2024-01-11') - 10; -- 结果: '2024-01-01'
-
multiply(a, b)
或a * b
计算两个数值的乘积。SELECT multiply(3, 4); -- 结果: 12
-
divide(a, b)
或a / b
计算两个数值的商。结果类型为浮点数。SELECT divide(10, 3); -- 结果: 3.3333333
-
intDiv(a, b)
计算整数的商,结果向下舍入(按绝对值),除以零或将最小负数除以 -1 时会抛出异常。SELECT intDiv(10, 3); -- 结果: 3
-
max2(value1, value2)
返回两个值中的最大值。SELECT max2(5, 10); -- 结果: 10
比较函数
比较函数始终返回 0 或 1(UInt8),用于比较数值、字符串、日期和日期时间类型。
- 等于:
a = b
或a == b
- 不等于:
a != b
或a <> b
- 小于:
a < b
- 大于:
a > b
- 小于等于:
a <= b
- 大于等于:
a >= b
字符串按字节进行比较,较短的字符串小于较长的字符串。
数据类型转换
将数据从一种类型转换为另一种类型时,需注意可能的数据丢失问题。通常,数据丢失发生在以下情况:
- 将较大的数据类型转换为较小的数据类型时,例如从
Int64
转换为Int32
。 - 不同数据类型之间的转换,例如从
Float64
转换为Int32
。
例如,以下示例演示了如何进行类型转换:
-- 从 Float64 转换为 Int32,可能会丢失小数部分
SELECT toInt32(12.34); -- 结果: 12
-- 从 Int32 转换为 String
SELECT toString(123); -- 结果: '123'
-- 从 String 转换为 Date
SELECT toDate('2024-01-01'); -- 结果: 2024-01-01