目录
基本上来说传统关系型数据库(以 MySQL 为例)的 SQL 语句,ClickHouse 基本都支持, 这里不会从头讲解 SQL 语法只介绍 ClickHouse 与标准 SQL(MySQL)不一致的地方。
# 创建单节点本地表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)使用此参数可以使数据均匀散列到各个节点中,防止数据集中命中集群中一个节点上。
同 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 语句分两步执行,同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候,才会删除旧数据释放磁盘空间,一般不会开放这样的功能给用户,由管理员完成。
基本与标准 SQL(MySQL)基本一致
insert into [table_name] values(...),(....)
insert into [table_name] select a,b,c from [table_name_2]
ClickHouse 基本上与标准 SQL 差别不大
(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;
clickhouse-client --query "select * from t_order_mt wherecreate_time='2020-06-01 12:00:00'" --format CSVWithNames>/opt/module/data/rs1.csv