clickhouse中的sql操作

DDL操作

create操作

create table t_order_mt2(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time  Datetime,
    INDEX a total_amount TYPE minmax GRANULARITY 5
) engine =MergeTree
partition by toYYYYMMDD(create_time)
order by (id, sku_id)
primary key (id)
index granularity=8192
;

primary key主键(可选)

clickhouse中的主键，和其他数据库不太一样，它只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同primary key的数据的。

主键的设定主要依据是查询语句中的 where 条件。根据条件通过对主键进行某种形式的二分查找，能够定位到对应的index granularity,避免了全表扫描。

index granularity稀疏索引(可选)

 直接翻译的话就是索引粒度，指在稀疏索引中两个相邻索引对应数据的间隔。clickhouse中的MergeTree默认是8192。官方不建议修改这个值，除非该列存在大量重复值，比如在一个分区中几万行才有一个不同数据。

稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索引粒度的第一行，然后再进行进行一点扫描。

order by(必选) 

order by 设定了分区内的数据按照哪些字段顺序进行有序保存。order by是MergeTree中唯一一个必填项，甚至比primary key 还重要，因为当用户不设置主键的情况，很多处理会依照order by的字段进行处理（比如去重和汇总）。

多个排序字段的顺序，可以遵循两个原则：

• WHERE 子句中出现频次高的字段放到频率低字段的前面，增加查询命中索引的概率；
• 维度基数大的字段放到维度基数小字段的后面，降低查询扫描范围。

什么是字段基数？

以性别为例，gender 有两种情况，即 gender=0 或 gender=1，那么 gender 的基数为 2。

注意：主键必须是order by字段的前缀字段。如order by 字段是 (id,sku_id) 那么主键必须是id 或者(id,sku_id)

二级索引（可选）

在clickhouse的官网上二级索引的功能是被标注为实验性的。所以使用二级索引前需要增加设置。

set allow_experimental_data_skipping_indices=1;

其中GRANULARITY N 是设定二级索引对于一级索引粒度的粒度。在一级索引上加个二级索引。

数据TTL（可选）

TTL即Time To Live，MergeTree提供了可以管理数据或者列的生命周期的功能。

(1)列级别TTL

  create table t_order_mt3(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2)  TTL create_time+interval 10 SECOND,
    create_time  Datetime 
 ) engine =MergeTree
 partition by toYYYYMMDD(create_time)
   primary key (id)
   order by (id, sku_id)

(2)表级TTL

  create table t_order_mt3(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time  Datetime 
 ) engine =MergeTree
 partition by toYYYYMMDD(create_time)
   primary key (id)
   order by (id, sku_id)
TTL ct + INTERVAL 1  MINUTE DELETE ;   -- 超过时间一分钟后会将数据删除

注意:当列中的值过期时, ClickHouse会将它们替换成该列数据类型的默认值。如果数据片段中列的所有值均已过期，则ClickHouse 会从文件系统中的数据片段中此列。

alter操作

--新增字段
alter table tableName  add column  newcolname  String after col1

--修改字段类型
alter table tableName  modify column  newcolname  String    ；

--删除字段
alter table tableName  drop column  newcolname   ;

DML操作

insert操作

插入几行数据到表中

nsert 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 id =102;

Mutation语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间。

查询操作

• 支持各种JOIN， 但是JOIN操作无法使用缓存，所以即使是两次相同的JOIN语句，Clickhouse也会视为两条新SQL。
• 不支持窗口函数。
• 不支持自定义函数。
• GROUP BY 操作增加了 with rollup\with cube\with total 用来计算小计和总计。

查看分区状态

SELECT    partition,name,part_type,active
FROM system.parts
WHERE table = 'partition_directory_merge'

• partition 列存储分区的名称。此示例中有1个分区：1
• name 列为分区目录的名称。
• part_type 数据存储格式。Wide: 每一列都单独存储一个数据文件；Compact: 所有列都存储一个数据文件。
• active 列为片段状态。1 代表激活状态；0 代表非激活状态。非激活片段是那些在合并到较大片段之后剩余的源数据片段。损坏的数据片段也表示为非活动状态。