Clickhouse精细入门

最新推荐文章于 2024-08-19 00:01:56 发布

神龙龙

最新推荐文章于 2024-08-19 00:01:56 发布

阅读量228

点赞数 1

分类专栏：大数据文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_43597431/article/details/118557238

版权

大数据专栏收录该内容

16 篇文章 0 订阅

订阅专栏

ClickHouse 是俄罗斯的Yandex于2016年开源的列式存储数据库（DBMS），使用C++语言编写，主要用于在线分析处理查询（OLAP），能够使用SQL查询实时生成分析数据报告。

Clickhouse特点

真正的列式数据库管理系统：节省磁盘空间提高数据的压缩比重。
DBMS的功能：几乎覆盖了标准sql的大部分用法包括DDL DML以及配套的各种函数
多样化引擎：与mysql类似把表级的存储引擎插件化根据表的不同需求可以设定不同的存储引擎
实时的数据更新
多核心并行处理
数据的压缩
....
缺点：不支持事务不支持常规意义的修改行和删除行数据
疏索引使得ClickHouse不适合通过其键检索单行的点查询

2.数据类型

整型：(使用场景：个数、数量、也可以存储数字型id)

整型范围

Int8 - [-128 : 127]

Int16 - [-32768 : 32767]

Int32 - [-2147483648 : 2147483647]

Int64 - [-9223372036854775808 : 9223372036854775807]

无符号整型范围

UInt8 - [0 : 255]

UInt16 - [0 : 65535]

UInt32 - [0 : 4294967295]

UInt64 - [0 : 18446744073709551615]

浮点型（一般数据值比较小，不涉及大量的统计计算，精度要求不高的时候。比如保存商品的重量）

Float32 - float

Float64 – double

布尔型

没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1。

Decimal型（一般金额字段、汇率、利率等字段为了保证小数点精度，都使用Decimal进行存储）

有符号的浮点点数，可在加、减和乘法运算过程中保持精度。对于除法，最低有效数字会被丢弃（不舍入）。

有三种声明：Decimal32(s)，相当于Decimal(9-s,s)

Decimal64(s)，相当于Decimal(18-s,s)

Decimal128(s)，相当于Decimal(38-s,s)

s标识小数位

字符串（名称、文字描述、字符型编码。固定长度的可以保存一些定长的内容，比如一些编码，性别等但是考虑到一定的变化风险，带来收益不够明显，所以定长字符串使用意义有限）

1）String

字符串可以任意长度的。它可以包含任意的字节集，包含空字节。

2）FixedString(N)

固定长度 N 的字符串，N 必须是严格的正自然数。当服务端读取长度小于 N 的字符串时候，通过在字符串末尾添加空字节来达到 N 字节长度。当服务端读取长度大于 N 的字符串时候，将返回错误消息。

与String相比，极少会使用FixedString，因为使用起来不是很方便。

时间类型

目前clickhouse 有三种时间类型

Date 接受 年-月-日 的字符串比如 ‘2019-12-16’

Datetime 接受 年-月-日时:分:秒 的字符串比如 ‘2019-12-16 20:50:10’

Datetime64 接受 年-月-日时:分:秒.亚秒 的字符串比如 ‘2019-12-16 20:50:10.66’

数组

Array(T)：由 T 类型元素组成的数组。

T 可以是任意类型，包含数组类型。但不推荐使用多维数组，ClickHouse 对多维数组的支持有限。例如，不能在 MergeTree 表中存储多维数组。

3.Clickhouse表引擎

TinyLog

以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表，生产环境上作用有限。可以用于平时练习测试用。

最简单的表引擎，它将数据存储在磁盘上。每一列都存储在一个单独的压缩文件中。在编写时，数据被追加到文件的末尾

并发数据访问不受任何限制：

如果您正在从一个表中读取并在另一个查询中写入它，会报错
如果您同时在多个查询中写入表格，数据将被破坏

使用这个表的典型方法是写一次：只写一次数据，然后根据需要多次读取数据。查询是在单个流中执行的。换句话说，这个引擎是为相对较小的表准备的（建议最多为1百万行）。如果有许多小的表，那么使用这个表引擎是有意义的，因为它比日志引擎更简单（需要打开的文件更少）。当拥有大量的小表时，这种情况就会导致效率低下，另外不支持索引

Yandex。在小批量处理的中间数据中，使用的是TinyLog表格。

Memory

内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现（超过10G/s）。

一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大（上限大概 1 亿行）的场景

MergeTree

Clickhouse 中最强大的表引擎当属 MergeTree （合并树）家族引擎及该系列（*MergeTree）中的其他引擎。地位可以相当于innodb之于Mysql。而且基于MergeTree，还衍生出了很多家族成员，也是非常有特色的引擎。可以使用单个大表，并在小块中不断地向其添加数据——这就是MergeTree的目的，在MergeTree家族中，所有类型的表都可以进行数据复制
create table t_order_mt(

uid UInt32,

sku_id String,

    total_amount Decimal(16,2),

    create_time  Datetime

 ) engine =MergeTree

 partition by toYYYYMMDD(create_time)  ----可选

   primary key (sku_id)                                ----可选

   order by (uid,sku_id);                               -----必选
ReplacingMergeTree

ReplacingMergeTree是MergeTree的一个变种，它存储特性完全继承MergeTree，只是多了一个去重的功能。

尽管MergeTree可以设置主键，但是primary key其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个ReplacingMergeTree。

去重时机：数据的去重只会在合并的过程中出现。合并会在未知的时间在后台进行，所以你无法预先作出计划。有一些数据可能仍未被处理。

去重范围：如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。

所以ReplacingMergeTree能力有限， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

去重不能跨分区，只有合并分区才会进行去重，认定重复的数据保留版本字段值最大的。如果版本字段相同则保留最后一笔。

SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。

Clickhouse 为了这种场景，提供了一种能够“预聚合”的引擎，SummingMergeTree.

SummingMergeTree 是非幂等的

4.sql操作

Insert：

基本与标准SQL（MySQL）基本一致

包括标准 insert into [table_name] values(…),(….)

以及从表到表的插入
  insert into  [table_name] select a,b,c from [table_name_2]
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_amount=toDecimal32(2000.00,2)

where uid =102;
查询操作

clickhouse基本上与标准SQL 差别不大。

   支持子查询

   支持CTE(with 子句)

   支持各种JOIN，但是JOIN操作无法使用缓存，所以即使是两次相同的JOIN语句，Clickhouse也会视为两条新SQL。

   不支持窗口函数。

   不支持自定义函数。

   GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。

alter操作

新增字段
alter table tableName  add column  newcolname  String after col1
after col1 是指在哪个字段后面加新字段

修改字段类型
alter table tableName  modify column  newcolname  String   ;
删除字段
alter table tableName  drop column  newcolname   ;
导出数据
clickhouse-client  --query    "select toHour(create_time) hr  ,count(*) from test1.order_wide where dt='2020-06-23'  group by hr" --format CSVWithNames> ~/rs1.csv