介绍

基本上来说传统关系型数据库(以 MySQL 为例)的 SQL 语句，ClickHouse 基本都支持， 这里不会从头讲解 SQL 语法只介绍 ClickHouse 与标准 SQL(MySQL)不一致的地方。

1 CREATE创建表(包含副本，分布式表创建)

# 创建单节点本地表
create table t_order_mt2 (
	id Uint32,
	sku_id String,
	total_amount Decimal(16,2) # TTL create_time+interval 10 second,//过期时间，也可以写在创建表末位，对表进行过期时间，非必选（秒、分、时、天、月、年等都可以）
	create_time Datetime,
	index a total_amount type minmax granularitys
) engine = mergeTree/ReplacingMergeTree(根据入参字段做去重依据，如果没有参数以最后一条数据为准)/SummingMergeTree(依据参数字段进行聚合，可多个)
parition by toYYYYMMDD(create_time)
primary by (id,sku_id)
order by (id,sku_id) #必选，最优包含三个字段
settings index_granulrity = 8192
# TTL create_time+interval 10 second
;

# 在集群中创建本地表（保存原数据到集群中每个节点中）
CREATE TABLE iov.t_fault12311 on cluster cluster_2s_2r(
    fault_time DateTime DEFAULT '1970-01-01 00:00:00' COMMENT '故障时间',
	fault_type Int8 DEFAULT 0 COMMENT '故障类型，详情请看字典',
	device_type Int8 DEFAULT 0 COMMENT '设备类型 100-DVR 200-OBD',
	vehicle_id Int64 DEFAULT 0 COMMENT '车辆ID',
	device_code String DEFAULT '' COMMENT '设备号',
	create_time DateTime DEFAULT now() COMMENT '创建时间'
) ENGINE = ReplicatedMergeTree('/clickhouse/tables/{database}/{table}/{shard}', '{replica}')
PARTITION BY toYYYYMMDD(fault_time)
ORDER BY (vehicle_id,device_code,fault_time,fault_type)
SETTINGS index_granularity = 8192;

# 创建分布式表
CREATE TABLE iov.dt_fault ON CLUSTER cluster_2s_2r as iov.t_fault ENGINE = Distributed(cluster_2s_2r, iov, t_fault, rand()或者javaHash(colName));

rand()或者javaHash(colName)使用此参数可以使数据均匀散列到各个节点中，防止数据集中命中集群中一个节点上。

2 ALTER修改表操作

同 MySQL 的修改字段基本一致

• 新增字段

# 新增单节点本地表
alter table tableName add column newcolname String after col1;

# 新增集群中所有本地表
alter table tableName ON CLUSTER cluster_2s_2r add column newcolname String after col1;
# 新增集群中分布式表
alter table 分布式表名字 add column newcolname String after col1;

• 修改字段类型

# 修改单节点本地表
alter table tableName modify column newcolname String;

# 修改集群中所有本地表
alter table tableName ON CLUSTER cluster_2s_2r modify column newcolname String;
# 修改集群中分布式表
alter table 分布式表名字 add column newcolname String after col1;

• 删除字段

# 删除单节点本地表
alter table tableName drop column newcolname;

# 删除集群中所有本地表
alter table tableName ON CLUSTER cluster_2s_2r drop column newcolname;
# 删除集群中分布式表
alter table 分布式表名字 drop column newcolname;

注意：ClickHouse原则上是不允许对库进行修改删除，可以查询和插入；

但是修改和删除也是支持的，ClickHouse 提供了 Delete 和 Update 的能力，这类操作被称为 Mutation |/mjuː'teɪʃ(ə)n/| (突变)查询，它可以看做 Alter 的一种。

所以说对ClickHouse进行修改和删除数据，就是对表进行操作，所以需要使用到alter关键字。虽然可以实现修改和删除，但是和一般的 OLTP 数据库不一样，Mutation 语句是一种很“重”的操作，而且不支持事务。“重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。所以尽量做批量的变更，不要进行频繁小数据的操作。

• 删除数据

# 删除单节点本地表的数据
ALTER TABLE db.tableName DELETE WHERE colName = 1;
# 或者使用TRUNCATE删除表数据（全表）
TRUNCATE TABLE db.tableName;

# 删除集群中所有本地表的数据
alter table db.tableName ON CLUSTER cluster_2s_2r DELETE WHERE colName = 1;
# 或者使用TRUNCATE删除表数据（全表）
TRUNCATE TABLE db.tableName ON CLUSTER cluster_2s_2r;

• 修改数据

# 修改单节点本地表的数据
ALTER TABLE db.tableName  update colName = 1 WHERE colName = 0;

# 修改集群中所有本地表的数据
alter table db.tableName ON CLUSTER cluster_2s_2r update colName = 1 WHERE colName = 0;

由于操作比较“重”，所以 Mutation 语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间，一般不会开放这样的功能给用户，由管理员完成。

3 INSERT插入数据

基本与标准 SQL(MySQL)基本一致

• 标准语法

insert into [table_name] values(...),(....)

• 从表到表的插入

insert into [table_name] select a,b,c from [table_name_2]

4 查询操作

ClickHouse 基本上与标准 SQL 差别不大

• ➢ 支持子查询

• ➢ 支持 CTE(Common Table Expression 公用表表达式 with 子句)

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

  A join B 将B表加载到缓存中，然后再和A表一一匹配

• ➢ 窗口函数

• ➢ 不支持自定义函数

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

(1)插入数据

alter table t_order_mt delete where 1=1;
 insert into t_order_mt values (101,'sku_001',1000.00,'2020-06-01 12:00:00'), 
(101,'sku_002',2000.00,'2020-06-01 12:00:00'),
 (103,'sku_004',2500.00,'2020-06-01 12:00:00'), 
 (104,'sku_002',2000.00,'2020-06-01 12:00:00'), 
 (105,'sku_003',600.00,'2020-06-02 12:00:00'), 
 (106,'sku_001',1000.00,'2020-06-04 12:00:00'), 
 (107,'sku_002',2000.00,'2020-06-04 12:00:00'), 
 (108,'sku_004',2500.00,'2020-06-04 12:00:00'), 
 (109,'sku_002',2000.00,'2020-06-04 12:00:00'), 
 (110,'sku_003',600.00,'2020-06-01 12:00:00');

(2)with rollup:从右至左去掉维度进行小计

select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with rollup;

(3)with cube : 从右至左去掉维度进行小计，再从左至右去掉维度进行小计

select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with cube;

(4)with totals: 只计算合计

select id , sku_id,sum(total_amount) from t_order_mt group by id,sku_id with totals;

5 导出数据

clickhouse-client --query "select * from t_order_mt where
create_time='2020-06-01 12:00:00'" --format CSVWithNames>
/opt/module/data/rs1.csv