clickhouse组件介绍

写在前面

今天学习clickhouse部分的知识。

ClickHouse

OLTP (联机事务处理系统)

例如 MySQL 等关系型数据库,适用于小数据量时的快速查询和分析。OLTP 主要针对增删改操作,数据经常发生变化。

OLAP (联机分析处理系统)

适用于数据长期不变且有大量历史数据的场景,主要进行分析操作,增删改操作较少。

OLAP 特点

  1. 绝大多数是读请求。
  2. 数据以批次(> 1000 行)更新,或根本没有更新。
  3. 已添加到数据库的数据不能修改。
  4. 提取大量行,但仅提取列的一小部分。
  5. 宽表,每个表包含大量列。
  6. 查询较少(每秒查询数百次或更少)。
  7. 简单查询允许约 50 毫秒延迟。
  8. 列中的数据相对较小:数字和短字符串(例如,每个 URL 60 字节)。
  9. 高吞吐量处理单个查询(每秒可达数十亿行)。
  10. 事务不是必须的。
  11. 对数据一致性要求低。
  12. 每个查询涉及一个大表,其他表很小。
  13. 查询结果明显小于源数据(数据经过过滤或聚合)。

数据类型

整数类型

  • 负数:

    • 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)

表操作

注意事项

  1. 建表时数据类型严格区分大小写
  2. 建表时必须指定表引擎

建表语句

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 TABLERENAME TABLE 查询。支持原子的 EXCHANGE TABLES 查询。
  • MySQL: 用于将远程 MySQL 服务器中的表映射到 ClickHouse 中。支持 INSERTSELECT 查询,不支持 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 UPDATEALTER 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
    计算两个数值的总和。也可以将 DateDateTime 与整数相加,表示增加相应的天数或秒数。

    SELECT plus(5, 10); -- 结果: 15
    SELECT toDate('2024-01-01') + 10; -- 结果: '2024-01-11'
    
  • minus(a, b)a - b
    计算两个数值之间的差。也可以将 DateDateTime 减去整数,表示减少相应的天数或秒数。

    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 = ba == b
  • 不等于a != ba <> 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