写在前面

今天学习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 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