clickhouse的一些相关知识
clickhouse概述
OLTP(联机事务处理系统)
例如mysql等关系型数据库,在对于存储小数据量的时候,查询数据并分析速度很快,OLTP本身其实是一个逻辑上的概念,指的是某个数据库,主要是针对增删改操作的。 里面的数据经常发生变化
OLAP(联机分析处理系统)
指的是数据库中的数据长期不变,有着大量的历史数据,并且可以随时的做分析,而增删改操作很少。
OLAP 种类系统架构的的特点
- 绝大多数是读请求
- 数据以相当大的批次(>1000行)更新,而不是单行更新;或者根本没有更新
- 已添加到数据库的数据不能修改。
- 对于读取,从数据库中提取相当多的行,但是提取列的一小部分。
- 宽表,即每个表包含着大量的列
- 查询相对较少(通常每台服务器每秒查询数百次或更少)
- 对于简单查询,允许延迟大约50毫秒
- 列中的数据相对较小:数字和短字符串(例如,每个URL 60个字节)
- 处理单个查询时需要高吞吐量(每台服务器每秒可达数十亿行)
- 事务不是必须的
- 对数据一致性要求低
表操作
数据类型
注意:
1、建表写数据类型的时候,严格区分大小写
2、建表的时候,必须要指定表引擎
- 整数类型
UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256
- 字符串类型
String:可变长字符串
FixedString(长度):固定长度字符串,参数是字节数,执行效率比String要高
- 日期类型
Date 年-月-日
Date32 年-月-日
DateTime 年-月-日 时-分-秒
DateTime64 年-月-日 时-分-秒.毫秒
- UUID类型
clickhouse提供了一个函数:generateUUIDv4() 生成一个 00000000-0000-0000-0000-000000000000 的编号 编号的类型就是UUID类型
例:bee32020-a6cb-49a6-a10b-427381b11613
- 可为空Nullable
例如建表的时候,有一个id字段类型时Int32,如果当id不确定的时候,我们应该使用null进行填充,而不应该用默认值0,所以,我们这里应该添加的是null
Nullable(Int32)
- 数组Array(T)
字段类型是数组,对于同一个数组,在建表的时候指定数据类型,注意:在MergeTree表引擎中是不允许出现数组嵌套的
注意:需要使用array()函数,将元素组成数组,将来还可以使用toTypeName()查看某一列的数据类型
当数组是整数类型时,返回地址值
- 小数类型
# Decimal(P,S),Decimal32(S),Decimal64(S),Decimal128(S)
有符号的定点数,可在加、减和乘法运算过程中保持精度。对于除法,最低有效数字会被丢弃(不舍入)
P - 精度。有效范围:[1:38],决定可以有多少个十进制数字(包括分数)。
S - 规模。有效范围:[0:P],决定数字的小数部分中包含的小数位数。
1、 建表语句
create table users3 (id Int8,name FixedString(12),gender Nullable(FixedString(3)),clazz String) ENGINE = TinyLog;
2、插入数据
# 基本格式:
INSERT INTO [db.]table [(c1, c2, c3)] VALUES (v11, v12, v13), (v21, v22, v23), ...
# 举例
insert into students_test values (1001,'奥德表','男','一班'),(1002,'蔡坤坤','男','二班'),(1003,'欧阳辣辣','女','三班');
#查看表结构
desc 表名
引擎
1、数据库引擎
-
Atomic
clickhouse数据库建库默认指定的数据库引擎
它支持非阻塞的DROP TABLE和RENAME TABLE查询和原子的EXCHANGE TABLES t1 AND t2查询。默认情况下使用Atomic数据库引擎。
-
Mysql
MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中,并允许您对表进行
INSERT
和SELECT
查询,以方便您在ClickHouse与MySQL之间进行数据交换MySQL
数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中,因此您可以执行诸如SHOW TABLES
或SHOW CREATE TABLE
之类的操作。但您无法对其执行以下操作:
RENAME
CREATE TABLE
ALTER
# 在clickhouse中创建数据库并指定远程的MySQL服务,将其中的某一个数据库映射过来(就将这新建的数据库看成一个远程客户端连接了mysql) # 建库语句 CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster] ENGINE = MySQL('host:port', ['database' | database], 'user', 'password') # 举例 create database IF NOT EXISTS clickhouse_mysql ENGINE = MySQL('192.168.160.100:3306','clickhousedb','root','123456'); # 参数理解: host:port — MySQL服务地址,既可以是ip地址,也可以是主机名(如果是主机名,要配置hosts映射) database — MySQL数据库名称 user — MySQL用户名 password — MySQL用户密码 # 操作注意事项 1、mysql的数据和ck数据库映射的数据几乎是同步的 2、在任意一端添加数据,另一端哦都可以观察添加后的结果 3、对于删除数据,只能在mysql端删除,不能够在ck端删除
2、表引擎
-
日志引擎
a.log
Log
与TinyLog
的不同之处在于,«标记» 的小文件与列文件存在一起。这些标记写在每个数据块上,并且包含偏移量,这些偏移量指示从哪里开始读取文件以便跳过指定的行数。这使得可以在多个线程中读取表数据。对于并发数据访问,可以同时执行读取操作,而写入操作则阻塞读取和其它写入。Log
引擎不支持索引。同样,如果写入表失败,则该表将被破坏,并且从该表读取将返回错误。Log
引擎适用于临时数据,write-once 表以及测试或演示目的。而且日志引擎表中数据不能删除b. TinyLog
最简单的表引擎,用于将数据存储在磁盘上。每列都存储在单独的压缩文件中。写入时,数据将附加到文件末尾。
并发数据访问不受任何限制:
- 如果同时从表中读取并在不同的查询中写入,则读取操作将抛出异常
- 如果同时写入多个查询中的表,则数据将被破坏。
这种表引擎的典型用法是 write-once:首先只写入一次数据,然后根据需要多次读取。查询在单个流中执行。换句话说,此引擎适用于相对较小的表(建议最多1,000,000行)。如果您有许多小表,则使用此表引擎是适合的,因为它比Log引擎更简单(需要打开的文件更少)。当您拥有大量小表时,可能会导致性能低下,但在可能已经在其它 DBMS 时使用过,则您可能会发现切换使用 TinyLog 类型的表更容易。不支持索引。
c.StripeLog
写数据:
StripeLog
引擎将所有列存储在一个文件中。对每一次Insert
请求,ClickHouse 将数据块追加在表文件的末尾,逐列写入。ClickHouse 为每张表写入以下文件:
data.bin
— 数据文件。index.mrk
— 带标记的文件。标记包含了已插入的每个数据块中每列的偏移量。
StripeLog
引擎不支持ALTER UPDATE
和ALTER DELETE
操作。读取数据:
带标记的文件使得 ClickHouse 可以并行的读取数据。这意味着
SELECT
请求返回行的顺序是不可预测的。使用ORDER BY
子句对行进行排序。 -
合并树家族
MergeTree
Clickhouse 中最强大的表引擎当属
MergeTree
(合并树)引擎及该系列(*MergeTree
)中的其他引擎。
MergeTree
系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入,数据片段在后台按照一定的规则进行合并。相比在插入时不断修改(重写)已存储的数据,这种策略会高效很多。主要特点:
# 建表语句规范:
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,
INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2
) ENGINE = MergeTree()
ORDER BY expr
[PARTITION BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]
optimize table 表名 final;
-- 产生结果不会立刻的将所有相同的分区进行合并,如果想要很快的看到结果,可以手动的进行合并
开发的时候,常用的表引擎:针对数据量小的表引擎用TinyLog,数据量大表引擎就用MergeTree
常用函数
1、 算数函数
对于所有算术函数,结果类型为结果适合的最小数值类型(如果存在这样的类型)。最小数值类型是根据数值的位数,是否有符号以及是否是浮点类型而同时进行的。如果没有足够的位,则采用最高位类型。简单理解:会自动的根据我们的数值大小,来选用最适合的数据类型存储。
# plus(a, b), a + b operator
计算数值的总和。 您还可以将Date或DateTime与整数进行相加。在Date的情况下,和整数相加整数意味着添加相应的天数。对于DateTime,这意味着添加相应的秒数。
# minus(a, b), a - b operator
计算数值之间的差,结果总是有符号的。
您还可以将Date或DateTime与整数进行相减。见上面的’plus’。
# multiply(a, b), a * b operator
计算数值的乘积。
# divide(a, b), a / b operator
计算数值的商。结果类型始终是浮点类型。 它不是整数除法。对于整数除法,请使用’intDiv’函数。 当除以零时,你得到’inf’,‘- inf’或’nan’。
# intDiv(a,b)
计算数值的商,向下舍入取整(按绝对值)。 除以零或将最小负数除以-1时抛出异常。
# max2(a,b)
value1 — 第一个值,类型为Int/UInt或Float。
value2 — 第二个值,类型为Int/UInt或Float。
2、比较函数
比较函数始终返回0或1(UInt8)。
可以比较以下类型:
- 数字
- String 和 FixedString
- 日期
- 日期时间
以上每个组内的类型均可互相比较,但是对于不同组的类型间不能够进行比较。
例如,您无法将日期与字符串进行比较。您必须使用函数将字符串转换为日期,反之亦然。
字符串按字节进行比较。较短的字符串小于以其开头并且至少包含一个字符的所有字符串。
等于,a=b和a==b 运算符
不等于,a!=b和a<>b 运算符
少, < 运算符
大于, > 运算符
小于等于, <= 运算符
大于等于, >= 运算符
3、数据类型转换
当你把一个值从一个类型转换为另外一个类型的时候,你需要注意的是这是一个不安全的操作,可能导致数据的丢失。数据丢失一般发生在你将一个大的数据类型转换为小的数据类型的时候,或者你把两个不同的数据类型相互转换的时候。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步