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:
[*]UInt16:
[*]UInt32:
[*]UInt64:
[*]UInt128:
[*]UInt256:
字符串类型
[*]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:38UUID 类型
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: 精度，范围：
[*]S: 规模，范围：
示例
-- Decimal(4,2)
-- Decimal(7,5)表操作
注意事项
[*]建表时数据类型严格区分大小写。
[*]建表时必须指定表引擎。
建表语句
CREATE TABLE users3 (
id Int8,
name FixedString(12),
gender Nullable(FixedString(3)),
clazz String
) ENGINE = TinyLog;插入数据
-- 基本格式
INSERT INTO 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 db_name
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 table_name (
name1 ,
name2 ,
...
INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,
...
) ENGINE = MergeTree()
;
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 或 ab
[*]小于：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
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]