常见数据类型讲解一
- 数值类型（整形，浮点数，定点数）
- 整型
- 固定长度的整型，包括有符号整型或无符号整型 IntX X是位的意思，1Byte字节=8bit位
-
- 浮点型（存在精度损失问题）
- 建议尽可能以整型形式存储数据
- Float32 - mysql里面的float类型
- Float64 - mysql里面的double类型
- Decimal类型
- 需要要求更高的精度的数值运算，则需要使用定点数
- 一般金额字段、汇率、利率等字段为了保证小数点精度，都使用 Decimal
- Clickhouse提供了Decimal32，Decimal64，Decimal128三种精度的定点数
- 用Decimal(P,S)来定义：
- P代表精度（Precise），表示总位数（整数部分 + 小数部分）
- S代表规模（Scale），表示小数位数
- 例子：Decimal(10,2) 小数部分2位，整数部分 8位（10-2)
- 也可以使用Decimal32(S)、Decimal64(S)和Decimal128(S)的方式来表示
- 用Decimal(P,S)来定义：
- 浮点型（存在精度损失问题）
CREATE TABLE xdclass_shop.clickstream1 (
customer_id String,
time_stamp Date,
click_event_type String,
page_code FixedString(20),
source_id UInt64,
money Decimal(2,1)
)
ENGINE = MergeTree()
ORDER BY (time_stamp)
INSERT INTO xdclass_shop.clickstream1
VALUES ('customer1', '2021-10-02', 'add_to_cart', 'home_enter', 568239,2.11 ) - 字符串类型
- UUID
- 通用唯一标识符(UUID)是由一组32位数的16进制数字所构成，用于标识记录
61f0c404-5cb3-11e7-907b-a6006ad3dba0
- 要生成UUID值，ClickHouse提供了 generateuidv4 函数。
- 如果在插入新记录时未指定UUID列的值，则UUID值将用零填充
00000000-0000-0000-0000-000000000000
- 建表和插入例子
CREATE TABLE t_uuid (x UUID, y String) ENGINE=TinyLogINSERT INTO t_uuid SELECT generateUUIDv4(), 'Example 1'
- 通用唯一标识符(UUID)是由一组32位数的16进制数字所构成，用于标识记录
- UUID
- FixedString固定字符串类型（相对少用）
- 类似MySQL的Char类型，属于定长字符，固定长度 N 的字符串（N 必须是严格的正自然数）
- 如果字符串包含的字节数少于`N’,将对字符串末尾进行空字节填充。
- 如果字符串包含的字节数大于
N,将抛出Too large value for FixedString(N)异常。- 应用场景
- ip地址二进制表示的IP地址
- 语言代码（ru_RU, en_US … ）
- 货币代码（USD, RUB … ）当数据的长度恰好为N个字节时，
FixedString类型是高效的，在其他情况下，这可能会降低效率
- 应用场景
- 字符串可以任意长度的。它可以包含任意的字节集，包含空字节
- 字符串类型可以代替其他 DBMSs中的 VARCHAR、BLOB、CLOB 等类型
- ClickHouse 没有编码的概念，字符串可以是任意的字节集，按它们原本的方式进行存储和输出String 字符串类型
- 计算机基础补充
- ASCII码：一个英文字母（不分大小写）占一个字节的空间，一个中文汉字占两个字节的空间
- UTF-8编码：一个英文字符等于一个字节，一个中文（含繁体）等于三个字节
常见数据类型讲解二
- 时间类型
- Date
- 日期类型，用两个字节存储，表示从 1970-01-01 (无符号) 到当前的日期值，支持字符串形式写入
- 上限是2106年，但最终完全支持的年份为2105
- DateTime
- 时间戳类型。用四个字节（无符号的）存储 Unix 时间戳，支持字符串形式写入
- 时间戳类型值精确到秒
- 值的范围: [1970-01-01 00:00:00, 2106-02-07 06:28:15]
- DateTime64
- 此类型允许以日期（date）加时间（time）的形式来存储一个时刻的时间值，具有定义的亚秒精度
- 值的范围: [1925-01-01 00:00:00, 2283-11-11 23:59:59.99999999] (注意: 最大值的精度是8)
- Date
- 枚举类型
- 包括
Enum8和Enum16类型，Enum保存'string'= integer的对应关系 - 在 ClickHouse 中，尽管用户使用的是字符串常量，但所有含有
Enum数据类型的操作都是按照包含整数的值来执行。这在性能方面比使用String数据类型更有效。Enum8用'String'= Int8对描述。Enum16用'String'= Int16对描述。
- 创建一个带有一个枚举
Enum8('home' = 1, 'detail' = 2, 'pay'=3)类型的列 -
CREATE TABLE t_enum ( page_code Enum8('home' = 1, 'detail' = 2,'pay'=3) ) ENGINE = TinyLog - 插入, page_code 这列只能存储类型定义中列出的值：'home'或`'detail' 或 'pay'。如果您尝试保存任何其他值，ClickHouse 抛出异常
- 包括
常见数据类型讲解三
- 数据库里面存储
- 不同CH版本存在不一样的数据类型，毕竟是火热项目，有些尽管新增但是也很少没用上
- case_insensitive 选项为1 表示大小写不敏感，字段类型不区分大小写
- 为0 表示大小写敏感，即字段类型需要严格区分大小写
- 里面很多数据类型，记住常用的即可
select * from system.data_type_families
- 布尔值
- 旧版以前没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1
- 新增里面新增了Bool
-
CREATE TABLE xdclass_shop.clickstream2 ( customer_id String, time_stamp Date, click_event_type String, page_code FixedString(20), source_id UInt64, money Decimal(2,1), is_new Bool ) ENGINE = MergeTree() ORDER BY (time_stamp) DESCRIBE xdclass_shop.clickstream2 INSERT INTO xdclass_shop.clickstream2 VALUES ('customer1', '2021-10-02', 'add_to_cart', 'home_enter', 568239, 3.8,1)
- 采用Mysql数据类型对比
| ClickHouse | Mysql | 说明 |
|---|---|---|
| UInt8 | UNSIGNED TINYINT | |
| Int8 | TINYINT | |
| UInt16 | UNSIGNED SMALLINT | |
| Int16 | SMALLINT | |
| UInt32 | UNSIGNED INT, UNSIGNED MEDIUMINT | |
| Int32 | INT, MEDIUMINT | |
| UInt64 | UNSIGNED BIGINT | |
| Int64 | BIGINT | |
| Float32 | FLOAT | |
| Float64 | DOUBLE | |
| Date | DATE | |
| DateTime | DATETIME, TIMESTAMP | |
| FixedString | BINARY |
常见SQL语法和注意事项
- 创建表
-
建表语句 CREATE TABLE xdclass_shop.clickstream3 ( customer_id String, time_stamp Date, click_event_type String, page_code FixedString(20), source_id UInt64, money Decimal(2,1), is_new Bool ) ENGINE = MergeTree() ORDER BY (time_stamp)
-
- 查看表结构
DESCRIBE xdclass_shop.clickstream3
- 查询
SELECT * FROM xdclass_shop.clickstream3
- 插入
INSERT INTO xdclass_shop.clickstream3 VALUES ('customer2', '2021-10-02', 'add_to_cart', 'home_enter', 568239,2.1, False )
- 更新和删除
- 在OLAP数据库中，可变数据（Mutable data）通常是不被欢迎的，早期ClickHouse是不支持，后来版本才有
- 不支持事务，建议批量操作，不要高频率小数据量更新删除
- 删除和更新是一个异步操作的过程，语句提交立刻返回，但不一定已经完成了
- 判断是否完成
SELECT database, table, command, create_time, is_done FROM system.mutations LIMIT 20
- 可以看到是否成功
- 判断是否完成
- 注意事项
- 每次更新或者删除，会废弃目标数据的原有分区，而重建新分区
- 例子
- 如果只更新一条数据，那么需要重建一个分区
- 如果更新100条数据，而这100条可能落在3个分区上，则需重建3个分区
- 相对来说一次更新一批数据的整体效率远高于一次更新一行
- 更新【不一样】
ALTER TABLE xdclass_shop.clickstream3 UPDATE click_event_type = 'pay' where customer_id = 'customer2';
- 删除【不一样】
ALTER TABLE xdclass_shop.clickstream3 delete where customer_id = 'customer2';
常见概念-分片-分区-副本讲解
- 常规的数据存储有几个概念是离不开的 分片、分区、副本
- 什么是ClickHouse的分区
- 分区是表的分区，把一张表的数据分成N多个区块，分区后的表还是一张表，数据处理还是由自己来完成
- PARTITION BY，指的是一个表按照某一列数据（比如日期）进行分区，不同分区的数据会写入不同的文件中
- 建表时加入partition概念，可以按照对应的分区字段，允许查询在指定了分区键的条件下，尽可能的少读取数据
-
create table xdclass_shop.order_merge_tree( id UInt32, sku_id String, out_trade_no String, total_amount Decimal(16,2), create_time Datetime ) engine =MergeTree() partition by toYYYYMMDD(create_time) order by (id,sku_id) primary key (id); - 注意
-
- 不是所有的表引擎都可以分区，合并树(MergeTree) 系列的表引擎才支持数据分区，Log系列引擎不支持
- 什么是ClickHouse的分片
- Shard 分片是把数据库横向扩展（Scale Out）到多个物理节点上的一种有效的方式
- 复用了数据库的分区概念，相当于在原有的分区下作为第二层分区，ClickHouse会将数据分为多个分片，并且分布到不同节点上，再通过 Distributed 表引擎把数据拼接起来一同使用
- Sharding机制使得ClickHouse可以横向线性拓展，构建大规模分布式集群，但需要避免数据倾斜问题
- 什么是ClickHouse的副本
- 两个相同数据的表, 作 用是为了数据备份与安全，保障数据的高可用性，
- 即使一台 ClickHouse 节点宕机，那么也可以从其他服务器获得相同的数据
- 类似Mysql主从架构，主节点宕机，从节点也能提供服务
- 总结
- 数据分区-允许查询在指定了分区键的条件下，尽可能的少读取数据
- 数据分片-允许多台机器/节点同并行执行查询，实现了分布式并行计算
本文作者为DBC，转载请注明。
