普集网站开发,营销管理,比较好的网站建设平台,网站建设合同书模板ClickHouse介绍和使用 1. 简介2. ClickHouse特点3. 数据类型3.1. 整型3.2. 浮点型3.3. Decimal型3.4. 布尔型3.5. 字符串3.6. 枚举类型3.7. 时间类型 4. 表引擎4.1. TinyLog4.2. Memory4.3. MergeTree4.3.1. partition by分区#xff08;可选#xff09;4.3.2. primary key 主… ClickHouse介绍和使用 1. 简介2. ClickHouse特点3. 数据类型3.1. 整型3.2. 浮点型3.3. Decimal型3.4. 布尔型3.5. 字符串3.6. 枚举类型3.7. 时间类型 4. 表引擎4.1. TinyLog4.2. Memory4.3. MergeTree4.3.1. partition by分区可选4.3.2. primary key 主键(可选)4.3.3. order by必选4.3.4. 二级索引4.3.5. 数据 TTL 4.4. ReplacingMergeTree4.5. SummingMergeTree 5. SQL 操作5.1. select5.2. insert5.3. update 和 delete5.4. alter 官网https://clickhouse.com/ 测试平台https://play.clickhouse.com/play?userplay
1. 简介
ClickHouse 是俄罗斯搜索巨头 Yandex 公司早 2016年 开源的一个极具 战斗力 的实时数据分析数据库开发语言为C 是一个用于联机分析 (OLAP:Online Analytical Processing) 的列式数据库管理系统(DBMS:Database Management System)简称 CK 工作速度比传统方法快100-1000倍ClickHouse 的性能超过了目前市场上可比的面向列的DBMS。每秒钟每台服务器每秒处理数亿至十亿多行和数十千兆字节的数据。
2. ClickHouse特点
列式存储
几乎覆盖了标准SQL 的大部分语法包括 DDL 和 DML以及配套的各种函数用户管理及权限管理数据的备份与恢复。
多样化引擎
高吞吐写入能力
官方公开 benchmark 测试显示能够达到 50MB-200MB/s 的写入吞吐能力按照每行100Byte 估算大约相当于 50W-200W 条/s 的写入速度
数据分区与线程级并行
ClickHouse 将数据划分为多个 partition每个 partition 再进一步划分为多个 index granularity(索引粒度)然后通过多个 CPU核心分别处理其中的一部分来实现并行数据处理。在这种设计下单条 Query 就能利用整机所有 CPU。极致的并行处理能力极大的降低了查询延时。所以ClickHouse 即使对于大量数据的查询也能够化整为零平行处理。但是有一个弊端就是对于单条查询使用多 cpu就不利于同时并发多条查询。所以对于高 qps 的查询业务ClickHouse 并不是强项。
3. 数据类型
3.1. 整型
有符号
Int8、Int16、Int32、Int64、Int128、Int256无符号
UInt8、UInt16、UInt32、UInt64、UInt128、UInt2563.2. 浮点型
float32、float643.3. Decimal型
Decimal()、Decimal32()、Decimal64()、Decimal128()3.4. 布尔型
可以使用 UInt8 类型取值限制为 0 或 1
3.5. 字符串
StringFixedString(N)3.6. 枚举类型
Enum8、Enum163.7. 时间类型
Date、DateTime、DateTime644. 表引擎
数据的存储方式和位置。支持哪些查询操作以及如何支持。数据的并发访问。数据索引的使用。是否可以支持多线程请求。是否可以支持数据复制。
4.1. TinyLog
以列文件的形式保存在磁盘上不支持索引没有并发控制。一般保存少量数据的小表生产环境上作用有限。可以用于平时测试用。
create table t_tinylog ( id String, name String) engineTinyLog;4.2. Memory
Memory 表引擎直接将数据保存在内存中数据即不会被压缩也不会被格式转换因为基于内存所以服务重启后会丢失。
4.3. MergeTree
该系列引擎是执行高负载任务的最通用和最强大的表引擎它们的特点是可以快速插入数据以及进行后续的数据处理。支持索引和分区地位可以相当于 innodb 之于 Mysql。而且基于 MergeTree还衍生出很多其他引擎
4.3.1. partition by分区可选
作用 分区的目的主要是降低扫描的范围优化查询速度 如果不填 所有数据都存放到名为all的数据分区 分区目录 MergeTree 是以列文件索引文件表定义文件组成的但是如果设定了分区那么这些文件就会保存到不同的分区目录中 并行 分区后面对涉及跨分区的查询统计ClickHouse 会以分区为单位并行处理。 数据写入与分区合并 任何一个批次的数据写入都会产生一个临时分区不会纳入任何一个已有的分区。写入后的某个时刻大概 10-15 分钟后ClickHouse 会自动执行合并操作等不及也可以手动通过 optimize 执行把临时分区的数据合并到已有分区中。
optimize table xxxx final;4.3.2. primary key 主键(可选)
主键特点
ClickHouse 中的 MergeTree 默认是 8192。官方不建议修改这个值除非该列存在大量重复值比如在一个分区中几万行才有一个不同数据。索引字段不会全部存储而是隔一段距离记录一个查询时先找到一个区间然后再去表中查具体位置数据量小可以指定间隔间隔默认是8192索引树不会存储每个索引字段 可以重复稀疏索引索引粒度
4.3.3. order by必选
order by 设定了分区内的数据按照哪些字段顺序进行有序保存。 order by 是 MergeTree 中唯一一个必填项甚至比 primary key 还重要因为当用户不设置主键的情况很多处理会依照order by 的字段进行处理比如后面会讲的去重和汇总。 要求主键必须是 order by 字段的前缀字段 比如 order by 字段是 (id,sku_id)那么主键必须是 id
4.3.4. 二级索引
能够为非主键字段设置二级索引
4.3.5. 数据 TTL
列级TTL
建立带有TTL列的表,这里定义total_amount 这一列的数据30s后结束生命周期
create table t_order_mt3(
id UInt32,
sku_id String,
total_amount Decimal(16,2) TTL create_time interval 30 SECOND,
create_time Datetime
) engine MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id, sku_id);表级TTL
可以在MergeTree的表参数中增加TTL表达式 为整张表设置TTL
create table stu(
id Int32,
name String,
create_time DateTime
)engine MergeTree
partition by toYYYYMM(create_time)
order by id TTL create_time interval 30 SECOND DELETE ;4.4. ReplacingMergeTree
ReplacingMergeTree 是 MergeTree 的一个变种它存储特性完全继承 MergeTree只是多了一个去重的功能。尽管 MergeTree 可以设置主键但是 primary key 其实没有唯一约束的功能。如果你想处理掉重复的数据可以借助这个 ReplacingMergeTree。
去重时机 数据的去重只会在分区合并的过程中进行
去重范围 如果表经过了分区去重只会在分区内部进行去重不能执行跨分区的去重。 所以 ReplacingMergeTree 能力有限 ReplacingMergeTree 适用于在后台清除重复的数据以节省空间但是它不保证没有重复的数据出现。 ReplacingMergeTree() 填入的参数为版本字段重复数据保留版本字段值最大的。如果不填版本字段默认按照插入顺序保留最后一条。
CREATE TABLE t_order_rmt (
id UInt32,
sku_id String,
total_amount DECIMAL ( 16, 2 ),
create_time DATETIME
) ENGINE ReplacingMergeTree ( create_time )
PARTITION BY toYYYYMMDD ( create_time )
PRIMARY KEY ( id )
ORDER BY ( id, sku_id );4.5. SummingMergeTree
对于不查询明细只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话无论是存储空间的开销还是查询时临时聚合的开销都比较大。ClickHouse 为了这种场景提供了一种能够“预聚合”的引擎 SummingMergeTree 以 SummingMergeTree中指定的列作为汇总数据列 以 order by 的列为准作为维度列 不在一个分区的数据不会被聚合 其他的列按插入顺序保留第一行 对于没来得及合并分区的临时数据还是需要使用 sum(total_amount) 进行聚合
create table t_order_smt(
id UInt32,
sku_id String,
total_amount Decimal(16,2),
create_time Datetime
) engine SummingMergeTree(total_amount)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id );5. SQL 操作
基本上来说传统关系型数据库以 MySQL 为例的 SQL 语句ClickHouse 基本都支持
5.1. select
ClickHouse 基本上与标准 SQL 差别不大支持子查询、各种 JOIN、各种函数…
SELECT [DISTINCT] expr_list[FROM [db.]table | (subquery) | table_function] [FINAL][SAMPLE sample_coeff][ARRAY JOIN ...][GLOBAL] ANY|ALL INNER|LEFT JOIN (subquery)|table USING columns_list[PREWHERE expr][WHERE expr][GROUP BY expr_list] [WITH TOTALS][HAVING expr][ORDER BY expr_list][LIMIT [n, ]m][UNION ALL ...][INTO OUTFILE filename][FORMAT format][LIMIT n BY columns]5.2. insert
基本与标准 SQLMySQL基本一致
标准
insert into [table_name] select a,b,c from [table_name_2]从表到表的插入
insert into [table_name] values(…),(….)5.3. update 和 delete
ClickHouse 提供了 Delete 和 Update 的能力这类操作被称为 Mutation 查询它可以看做 Alter 的一种。 虽然可以实现修改和删除但是和一般的 OLTP 数据库不一样Mutation 语句是一种很“重”的操作而且不支持事务。 每次修改或者删除都会导致放弃目标数据的原有分区重建新分区。所以尽量做批量的变更不要进行频繁小数据的操作
删除操作
alter table t_order_smt delete where sku_id sku_001;修改操作
alter table t_order_smt update total_amounttoDecimal32(2000.00,2) where id 102;由于操作比较“重”所以 Mutation 语句分两步执行同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候才会删除旧数据释放磁盘空间一般不会开放这样的功能给用户由管理员完成。
5.4. alter
同 MySQL 的修改字段基本一致
新增字段
alter table tableName add column newcolname String after col1;删除字段
alter table tableName drop column newcolname;修改字段类型
alter table tableName modify column newcolname String;
