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: 年-月-日 时-分-秒.毫秒
  

示例

1. -- 建表语句
2. CREATE TABLE date_test (
3.     date1 Date,
4.     date2 Date32,
5.     date3 DateTime,
6.     date4 DateTime64
7. ) ENGINE = TinyLog;
9. -- 插入语句
10. INSERT INTO date_test VALUES ('2023-11-21', '2023-11-21', '2023-11-21', '2023-11-21');
11. INSERT INTO date_test VALUES (1711435333589, 1711435333589, 1711435333589, 1711435333589);
12. -- 结果：2024-03-26 15:33:38

复制代码

UUID 类型

ClickHouse 提供了一个函数 generateUUIDv4()，生成 UUID，例如 bee32020-a6cb-49a6-a10b-427381b11613
可为空（Nullable）

• 使用 Nullable 处置惩罚不确定的字段值
  

1. CREATE TABLE test2 (
2.     id Int32,
3.     name Nullable(String)
4. ) ENGINE = TinyLog;
6. INSERT INTO test2 VALUES (1001, NULL);

复制代码

数组

• 使用 Array(T) 类型，数据类型在建表时指定。MergeTree 表引擎不允许出现数组嵌套。
  

示例

1. CREATE TABLE t1 (
2.     col1 Array(Int8)
3. ) ENGINE = TinyLog;
5. INSERT INTO t1 VALUES (array(11, 12, 13));

复制代码

小数类型

• Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S)
  
  
  
  • P: 精度，范围：[1:38]
    
  • S: 规模，范围：[0]
    
  
  

示例

1. -- Decimal(4,2)
2. -- Decimal(7,5)

复制代码

表操作

留意事项

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

建表语句

1. CREATE TABLE users3 (
2.     id Int8,
3.     name FixedString(12),
4.     gender Nullable(FixedString(3)),
5.     clazz String
6. ) ENGINE = TinyLog;

复制代码

插入数据

1. -- 基本格式
2. INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
4. -- 示例
5. CREATE TABLE IF NOT EXISTS bigdata31.students_test (
6.     id Int32,
7.     name String,
8.     gender FixedString(6),
9.     clazz String
10. ) ENGINE = TinyLog;
12. -- 插入数据
13. INSERT INTO students_test VALUES
14. (1001, '陆澳', '男', '特训营24期'),
15. (1002, '李佳豪', '男', '特训营24期'),
16. (1003, '郭香香', '女', '特训营24期');
18. INSERT INTO students_test VALUES
19. (1004, '王宇杰', '男', '特训营24期'),
20. (1005, '张怀远', '男', '特训营24期'),
21. (1006, '史俊超', '男', '特训营24期');
23. INSERT INTO students_test (name, gender, clazz) VALUES
24. ('张玮', '男', '特训营24期');
26. -- 查看表结构
27. DESC 表名;

复制代码

引擎

数据库引擎

• Atomic: 默认引擎，支持非阻塞的 DROP TABLE 和 RENAME TABLE 查询。支持原子的 EXCHANGE TABLES 查询。
  
• MySQL: 用于将远程 MySQL 服务器中的表映射到 ClickHouse 中。支持 INSERT 和 SELECT 查询，不支持 RENAME, CREATE TABLE, ALTER。
  

创建数据库并映射远程 MySQL 服务

1. CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
2. ENGINE = MySQL('host:port', ['database' | database], 'user', 'password');
4. -- 示例
5. 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 操作。
  

建表语句

1. CREATE TABLE stripe_log_table (
2.     timestamp DateTime,
3.     message_type String,
4.     message String
5. ) ENGINE = StripeLog;
7. -- 插入数据
8. INSERT INTO stripe_log_table VALUES
9. (now(), 'REGULAR', 'The first regular message'),
10. (now(), 'REGULAR', 'The second regular message'),
11. (now(), 'WARNING', 'The first warning message');
13. -- 示例
14. CREATE TABLE students_stripelog (
15.     id Int32,
16.     name String,
17.     gender FixedString(3),
18.     clazz String
19. ) ENGINE = StripeLog;
21. -- 添加数据
22. INSERT INTO students_stripelog VALUES
23. (1001, '陆澳', '男', '特训营24期'),
24. (1002, '李佳豪', '男', '特训营24期'),
25. (1003, '郭香香', '女', '特训营24期');

复制代码

MergeTree

• ClickHouse 中最强大的表引擎，用于快速写入大量数据，支持分区、数据副本和数据采样。
  留意：默认是针对每一批数据按照分区字段的值进行分区
  

建表语句

1. CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (
2.   name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],
3.   name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],
4.   ...
5.   INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
6.   ...
7. ) ENGINE = MergeTree()
8. [PARTITION BY expr]
9. [ORDER BY expr]
10. [PRIMARY KEY expr]
11. [SAMPLE BY expr]
12. [TTL expr]
13. [SETTINGS name=value, ...];
14. PARTITION BY：用于数据分区，以提高查询效率。
15. ORDER BY：定义数据的排序方式。
16. PRIMARY KEY：指定主键索引。
17. SAMPLE BY：采样数据的列。
18. TTL：定义数据过期时间。
19. SETTINGS：表级别的设置。

复制代码

示例

1. CREATE TABLE IF NOT EXISTS bigdata31.students (
2.   id Int32,
3.   name String,
4.   gender FixedString(6),
5.   clazz String
6. ) ENGINE = MergeTree()
7. PARTITION BY toYYYYMM(date)
8. ORDER BY id
9. PRIMARY KEY id;

复制代码

-- 插入数据

1. INSERT INTO students VALUES
2.   (1001, '陆澳', '男', '特训营24期'),
3.   (1002, '李佳豪', '男', '特训营24期');...

复制代码

ClickHouse 表引擎和数据优化

数据表优化

• 手动优化
  使用 OPTIMIZE TABLE 命令来合并分区数据，减少碎片，进步查询性能。
  1. OPTIMIZE TABLE goods_orders FINAL;

  复制代码
  FINAL 参数用于执行最终的合并操作，确保数据在所有分区和数据副本中都被合并。此操作会将分区中的数据合并到一个单一的文件中，消除数据的冗余和碎片。
  
• 自动合并
  ClickHouse 自动合并数据，以维护表的性能和存储空间。这是通过背景进程定期进行的。
  

表引擎选择

• TinyLog
  适用于小规模表，不支持索引，通常用于临时数据和测试。适用于行数较少、更新频繁的场景。
  
• MergeTree
  适用于大规模数据表，支持分区、排序和索引，提供高效的数据读取和写入性能。适用于数据量较大、查询复杂的场景。
  

常用函数

算术函数

• plus(a, b) 或 a + b
  计算两个数值的总和。也可以将 Date 或 DateTime 与整数相加，表示增长相应的天数或秒数。
  1. SELECT plus(5, 10); -- 结果: 15
  2. SELECT toDate('2024-01-01') + 10; -- 结果: '2024-01-11'

  复制代码
• minus(a, b) 或 a - b
  计算两个数值之间的差。也可以将 Date 或 DateTime 减去整数，表示减少相应的天数或秒数。
  1. SELECT minus(15, 5); -- 结果: 10
  2. SELECT toDate('2024-01-11') - 10; -- 结果: '2024-01-01'

  复制代码
• multiply(a, b) 或 a * b
  计算两个数值的乘积。
  1. SELECT multiply(3, 4); -- 结果: 12

  复制代码
• divide(a, b) 或 a / b
  计算两个数值的商。结果类型为浮点数。
  1. SELECT divide(10, 3); -- 结果: 3.3333333

  复制代码
• intDiv(a, b)
  计算整数的商，结果向下舍入（按绝对值），除以零或将最小负数除以 -1 时会抛出异常。
  1. SELECT intDiv(10, 3); -- 结果: 3

  复制代码
• max2(value1, value2)
  返回两个值中的最大值。
  1. SELECT max2(5, 10); -- 结果: 10

  复制代码

比力函数

比力函数始终返回 0 或 1（UInt8），用于比力数值、字符串、日期和日期时间类型。

• 等于：a = b 或 a == b
  
• 不等于：a != b 或 a  b
  
• 小于：a < b
  
• 大于：a > b
  
• 小于等于：a = b
  

字符串按字节进行比力，较短的字符串小于较长的字符串。
数据类型转换

将数据从一种类型转换为另一种类型时，需留意大概的数据丢失问题。通常，数据丢失发生在以下环境：

• 将较大的数据类型转换为较小的数据类型时，例如从 Int64 转换为 Int32。
  
• 不同数据类型之间的转换，例如从 Float64 转换为 Int32。
  

例如，以下示例演示了如何进行类型转换：

1. -- 从 Float64 转换为 Int32，可能会丢失小数部分
2. SELECT toInt32(12.34); -- 结果: 12
4. -- 从 Int32 转换为 String
5. SELECT toString(123); -- 结果: '123'
7. -- 从 String 转换为 Date
8. SELECT toDate('2024-01-01'); -- 结果: 2024-01-01

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。