clickhouse的常用语法你知道吗

@TOC

一、背景

    最近工作中经常使用clickhouse数据库，总结了一些常用的语法，想着分享一下，大家肯定能够用到，内容有点难度，第一遍看不懂，可以收藏后面再看，欢迎收藏点赞。
   想了解其他数据库语法，请看

Mysql数据库常用命令总结
关于PostgreSQL数据增删改查的日常总结（主要jsonb类型）

二、语法

1、变更clickhouse表字段类型

alter table mytable.mytable modify column trans_proto String

2、重命名表名

rename table mytable.mytable to mytable.mytable_del

3、添加表字段

(1)找到mytable表，搜索最后一个字段，添加加新字段。

alter table mytable.mytable add column `uuid` String;

4、删除测试数据

ALTER TABLE  mytable.mytable delete  where uuid ='AAAAAAAAAAAAAAAA'

5、删除表结构

drop table [if exists] db.name

6、重置某一列的值

alter table tb_name clear column name;

7、创建物化视图

（1）这个物化视图每次插入数据后，会将数据写入relation_real表中。但是创建物化视图之前历史的数据不会写入。

CREATE MATERIALIZED VIEW mytable.test_relation TO mytable.relation_real (
 `src` String,
 `dstion` String,
 `srcPnt` Int64,
 `dstPnt` Int64,
 `state` Int64,
 `thisTime` DateTime64(3, 'Asia/Shanghai')
) AS
SELECT e.src AS src, e.dstion AS dstion, e.srcPnt AS srcPnt, e.dstPnt AS dstPnt, any(e.pktsToIt) AS state, max(thisTime) AS thisTime
FROM mytable.test AS e INNER JOIN (SELECT src, dstion, srcPnt, dstPnt, max(thisTime) AS time
FROM mytable.test
GROUP BY src, dstion, srcPnt, dstPnt) AS sub ON (e.thisTime = sub.time) AND (e.src = sub.src) AND (e.dstion = sub.dstion) AND (e.srcPnt = sub.srcPnt) AND (e.dstPnt = sub.dstPnt)
GROUP BY e.src, e.dstion, e.srcPnt, e.dstPnt

8、创建表

CREATE TABLE mytable.test (
 `thisTime` DateTime64(3, 'Asia/Shanghai'),
 `src` String,
 `srcPnt` Int64,
 `dstion` String,
 `dstPnt` Int64,
 `pktsToIt` Int64,
 `pktsToIt` Int64,
 `state` String
) 
ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(thisTime)
ORDER BY thisTime TTL toDateTime(thisTime) + toIntervalMonth(1)
SETTINGS index_granularity = 8192

9、获取分组后最新的记录

SELECT e.src, e.dstion, e.srcPnt, e.dstPnt,,max(e.thisTime) AS time, any(e.state) AS state
FROM mytable.mytable_book AS e INNER JOIN (SELECT src, dstion, srcPnt, dstPnt, max(thisTime) AS time
FROM mytable.mytable_book
GROUP BY src, dstion, srcPnt, dstPnt) AS sub ON (e.thisTime = sub.time) AND (e.src = sub.src) AND (e.dstion = sub.dstion) AND (e.srcPnt = sub.srcPnt) AND (e.dstPnt = sub.dstPnt)
GROUP BY e.src, e.dstion, e.srcPnt, e.dstPnt

10、获取分组后最新的数据，创建视图

CREATE VIEW mytable.asset_relation1 (
 `src` String,
 `dstion` String,
 `srcPnt` Int64,
 `dstPnt` Int64,
  `time` DateTime64(3, 'Asia/Shanghai'),
 `state` Int64
) AS 
SELECT e.src, e.dstion, e.srcPnt, e.dstPnt,max(e.thisTime) AS time,any(e.state) AS state
FROM mytable.mytable_book AS e INNER JOIN (SELECT src, dstion, srcPnt, dstPnt, max(thisTime) AS time
FROM mytable.mytable_book
GROUP BY src, dstion, srcPnt, dstPnt) AS sub ON (e.thisTime = sub.time) AND (e.src = sub.src) AND (e.dstion = sub.dstion) AND (e.srcPnt = sub.srcPnt) AND (e.dstPnt = sub.dstPnt)
GROUP BY e.src, e.dstion, e.srcPnt, e.dstPnt

11、从一张表插入到另一张表

INSERT INTO <new_table_name> SELECT * FROM <damaged_table_name>

12、clickhouse多个字段作为分区

PARTITION BY (toYYYYMMDD(thisTime),
 infoType)

13、clickhouse的数据备份还原

（1）备份某个分区的数据

ALTER TABLE mytable.mytabletest freeze PARTITION (20220107,2) 

（2）清除分区数据

ALTER TABLE mytable.mytabletest detach PARTITION (20220107,2) 

（3）还原分区数据

ALTER TABLE mytable.mytabletest attach PARTITION (20220107,2) 

14、插入物化视图带历史数据

因为我们使用了PARTITION 参数，所以会在创建视图之后，将历史表数据插入一遍。不使用的话就无法插入历史数据了，新写入的才会插入。

CREATE  MATERIALIZED VIEW mytable.mytabledailycountv3 
(
    `day` Date,
    `src` String,
    `dstion` String,
     `srcv6` String,
    `dstionv6` String,
    `ruleId` UInt64,
    `devIp` String,
    `infoType` UInt16,
    `count` SimpleAggregateFunction(sum, UInt64)
) 
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMMDD(day)
ORDER BY (day, src, dstion, ruleId, devIp, infoType)
TTL day + toIntervalMonth(12)
SETTINGS index_granularity = 8192
POPULATE AS
SELECT
    toDate(occurTime) AS day,
    src,
    dstion,
    any(srcv6) as srcv6,
    any(dstionv6) as dstionv6,
    ruleId,
    devIp,
    infoType,
    count() AS count
FROM mytable.mytable
GROUP BY day, src, dstion, ruleId, devIp, infoType;

三、总结

   以上就是就是关于clickhouse数据库常用语法，可以参考一下，觉得不错的话，欢迎微信搜索关注java基础笔记，后面会不断更新相关知识，大家一起进步。