ClickHouse常见数据类型以及常用SQL语法

1.数值类型（整形，浮点数，定点数）

（1）整型

（2）固定长度的整型，包括有符号整型或无符号整型 IntX X是位的意思，1Byte字节=8bit位

有符号整型范围
Int8 — [-128 : 127]
Int16 — [-32768 : 32767]
Int32 — [-2147483648 : 2147483647]
Int64 — [-9223372036854775808 : 9223372036854775807]
Int128 — [-170141183460469231731687303715884105728 : 170141183460469231731687303715884105727]
Int256 — [-57896044618658097711785492504343953926634992332820282019728792003956564819968 : 57896044618658097711785492504343953926634992332820282019728792003956564819967]
无符号整型范围
UInt8 — [0 : 255]
UInt16 — [0 : 65535]
UInt32 — [0 : 4294967295]
UInt64 — [0 : 18446744073709551615]
UInt128 — [0 : 340282366920938463463374607431768211455]
UInt256 — [0 : 115792089237316195423570985008687907853269984665640564039457584007913129639935] 

（3）浮点型（存在精度损失问题）

• 建议尽可能以整型形式存储数据
• Float32 - mysql里面的float类型
• Float64 - mysql里面的double类型
• （4）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类型
(1)创建数据库
CREATE DATABASE test
(2)创建表
CREATE TABLE test.product (
  id UInt64,
  name String, 
  time_stamp Date,
    money Decimal(2,1)
)
ENGINE = MergeTree()
ORDER BY (time_stamp)

测试插入
INSERT INTO test.product
VALUES (568239, '商品1', '2021-10-02',2.11 ) 

2.字符串类型

（1）UUID

• 通用唯一标识符(UUID)是由一组32位数的16进制数字所构成，用于标识记录

61f0c404-5cb3-11e7-907b-a6006ad3dba0

• 要生成UUID值，ClickHouse提供了 generateuidv4 函数。如果在插入新记录时未指定UUID列的值，则UUID值将用零填充

00000000-0000-0000-0000-000000000000

测试
(1)创建表
create TABLE t_uuid (x String,y String) ENGINE = TinyLog
(2)插入数据
INSERT INTO test.t_uuid SELECT generateUUIDv4(), 'Example 1'

（2）FixedString固定字符串类型

• 类似MySQL的Char类型，属于定长字符，固定长度 N 的字符串（N 必须是严格的正自然数）
• 如果字符串包含的字节数少于`N’,将对字符串末尾进行空字节填充。

• 如果字符串包含的字节数大于N,将抛出Too large value for FixedString(N)异常。
• 当数据的长度恰好为N个字节时，FixedString类型是高效的，在其他情况下，这可能会降低效率

• 应用场景

• ip地址二进制表示的IP地址

• 语言代码（ru_RU, en_US … ）
• 货币代码（USD, RUB … ）

（3）String字符串类型

• 字符串可以任意长度的。它可以包含任意的字节集，包含空字节。
  
• 字符串类型可以代替其他 DBMSs中的 VARCHAR、BLOB、CLOB 等类型
  

• ClickHouse 没有编码的概念，字符串可以是任意的字节集，按它们原本的方式进行存储和输出
  

3.时间类型

• 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)

4.枚举类型

• 包括 Enum8 和 Enum16 类型，Enum 保存 'string'= integer 的对应关系
• 在 ClickHouse 中，尽管用户使用的是字符串常量，但所有含有 Enum 数据类型的操作都是按照包含整数的值来执行。这在性能方面比使用 String 数据类型更有效。
• Enum8 用 'String'= Int8 对描述。

• Enum16 用 'String'= Int16 对描述。

• 创建一个带有一个枚举 Enum8('ToBePaid' = 1, 'Paid' = 2) 类型的列

#插入成功
INSERT INTO test.pay_emun VALUES ('ToBePaid'), ('Paid')
#查询
SELECT * FROM test.pay_emun

5.布尔值

• 旧版以前没有单独的类型来存储布尔值。可以使用 UInt8 类型，取值限制为 0 或 1
• 新增里面新增了Bool

#创建表
CREATE TABLE test.t_boolean (
  time_stamp Date,
    is_new Bool
) 
ENGINE = MergeTree()
ORDER BY (time_stamp)
#插入，注意只能插入0或者1否则会报错，0表示false，1表示true
INSERT INTO test.t_boolean VALUES ('2021-10-02', 1) 
INSERT INTO test.t_boolean VALUES ('2021-10-02', 0) 
#查询
select * from test.t_boolean

6.其他数据类型

#数据库中存储的数据类型，当然不止下图折磨多，具体用的时候再去看
select * from system.data_type_families

• case_insensitive 选项为1 表示大小写不敏感，字段类型不区分大小写
• 为0 表示大小写敏感，即字段类型需要严格区分大小写

7.ClickHouse与Mysql数据类型对比

8.常见SQL语法和注意事项

（1）创建表

CREATE TABLE test.order (
    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)

（2）查看表结构

DESCRIBE test.order

（3）查询

SELECT * FROM test.order

（4）插入

INSERT INTO test.order
VALUES ('customer2', '2021-10-02', 'add_to_cart', 'home_enter', 568239,2.1, False ) 

（5）更新和删除

• 在OLAP数据库中，可变数据（Mutable data）通常是不被欢迎的，早期ClickHouse是不支持，后来版本才有
  
• 不支持事务，建议批量操作，不要高频率小数据量更新删除
  
• 删除和更新是一个异步操作的过程，语句提交立刻返回，但不一定已经完成了
  

SELECT database, table, command, create_time, is_done FROM system.mutations LIMIT 20

注意事项

• 每次更新或者删除，会废弃目标数据的原有分区，而重建新分区
• 例子

• 如果只更新一条数据，那么需要重建一个分区

• 如果更新100条数据，而这100条可能落在3个分区上，则需重建3个分区
• 相对来说一次更新一批数据的整体效率远高于一次更新一行

• 更新
  

ALTER TABLE test.order UPDATE  click_event_type = 'pay' where customer_id = 'customer2'; 

• 删除

ALTER TABLE test.order delete where customer_id = 'customer2';

区

- 如果更新100条数据，而这100条可能落在3个分区上，则需重建3个分区

- 相对来说一次更新一批数据的整体效率远高于一次更新一行

• 更新

ALTER TABLE test.order UPDATE  click_event_type = 'pay' where customer_id = 'customer2'; 

• 删除

ALTER TABLE test.order delete where customer_id = 'customer2';