Clickhouse开发常用内部函数及调优

最新推荐文章于 2024-09-24 18:00:37 发布

司马灰

最新推荐文章于 2024-09-24 18:00:37 发布

阅读量6.4k

点赞数

文章标签： clickhouse

本文链接：https://blog.csdn.net/weixin_44223047/article/details/101422502

版权

Clickhouse开发常用内部函数

目前公司正在使用Clickhouse，记录一下常用内部函数和CK特性，调优相关。
待补充。。。

一、日期和时间类

1、时间取整

toYear¶/toMonth¶

将Date或DateTime转换为包含年份、月份编号（AD）的UInt16类型的数字。

toHour¶/toMinute/toSecond

将DateTime转换为包含24小时制（0-23）小时/分钟/秒数的UInt8数字。

注：返回值类型为UInt，例toMonth(2018-09-01) ，返回9。

toStartOfMonth¶

将Date或DateTime向前取整到本月的第一天。返回Date类型。

注：返回值类型为：2018-09-01。可用于按月分组

toStartOfDay/toStartOfHour/toStartOfMinute

将DateTime向前取整到当前天、小时、的开始。

2、日期、时间戳相互转化

toUnixTimestamp¶

将DateTime转换为unix时间戳。(常用)

toDate/toDateTime 可接受两种类型的参数

将unix时间戳转化为Date、DateTime。

将字符串类型转化为Date、DateTime。

3、当前、昨天、明天

now¶

返回当前时间（DateTime）。尽量少使用，可在程序内部添加系统时间。例：2019-09-24 11:09:06

today/yesterday

返回一个当前、昨天的（Date）。例：2019-09-24

注：可以使用 now()+60 表示当前时间+60秒

**today()+1 表示明天。**其他同理

4、时间加减

除了可以用 + / - 来进行时间的加减以外，还可以使用函数进行时间加减的操作。

addYears, addMonths, addWeeks, addDays, addHours, addMinutes, addSeconds, addQuarters¶

函数将一段时间间隔添加到Date/DateTime，然后返回Date/DateTime。例如：

WITH
    toDate('2018-01-01') AS date,
    toDateTime('2018-01-01 00:00:00') AS date_time
SELECT
    addYears(date, 1) AS add_years_with_date,
    addYears(date_time, 1) AS add_years_with_date_time

┌─add_years_with_date─┬─add_years_with_date_time─┐
│          2019-01-01 │      2019-01-01 00:00:00 │
└─────────────────────┴──────────────────────────┘

subtractYears, subtractMonths, subtractWeeks, subtractDays, subtractHours, subtractMinutes, subtractSeconds, subtractQuarters¶

函数将Date/DateTime减去一段时间间隔，然后返回Date/DateTime。例如：

WITH
    toDate('2019-01-01') AS date,
    toDateTime('2019-01-01 00:00:00') AS date_time
SELECT
    subtractYears(date, 1) AS subtract_years_with_date,
    subtractYears(date_time, 1) AS subtract_years_with_date_time

┌─subtract_years_with_date─┬─subtract_years_with_date_time─┐
│               2018-01-01 │           2018-01-01 00:00:00 │
└──────────────────────────┴─────────────────────────

二、字符串函数

1、判断是否为空 or 不为空

empty/notEmpty

返回值：1 true ， 0 false。

2、拼接字符串

concat(s1,s2,…) 简单拼接字符串

3、截取字符串

substring(s,offset,length)

返回以‘offset’位置为开头，长度为‘length’的子串。‘offset’从1开始

4、判断字符串开头、结尾

startsWith(s,prefix)

endsWith(s,suffix)

5、字符串搜索、替换

使用正则、子串查找和替换。

正常开发中使用较少，需要可查询文档

https://clickhouse.yandex/docs/zh/query_language/functions/string_search_functions/

6、将字符串类型转化数值类型

toInt32OrZero()

toFloat32OrZero()

注：其他类型同理，无法转换的返回0.

7、字符串分割

splitByChar() 以字符分割

splitByString() 以字符串分割

使用同Split算子，结果返回一个数组。

三、数学函数

1、取整函数

floor(x[,N]) 地板函数

返回小于或等于x的最大舍入数。

ceil(x[,N]) 顶板函数

返回大于或等于’x’的最小舍入数。

round(x[,N]) 四舍五入取整

注：以上三种函数对Float64有数值精度问题，但目前项目均为Float32，因此可以正常使用。

四、聚合函数

1、数组聚合

groupArray()

groupArray(max_size)()

按某个条件分组，将一个字段聚合成为一个数组。

max_size可以设置，数组的最大值。

2、分位数

quantile(0.9)()

示例：90%分位数，求气象数据分位数会用到。

3、聚合函数组合子

If 聚合函数条件判断组合子

示例：avgIf(SO2Value,SO2Value > 0)

Array 针对数组操作的组合子

示例：sumArray([1,2,3,4]) 返回值 10

输入参数为一个数组，返回一个聚合结果10

ForEach 对一个字段为数组的列进行对位计算。Such as

sumForEach for the arrays [1, 2], [3, 4, 5]and[6, 7]returns the result [10, 13, 5]

多个组合子可以一起使用，如需要这样用时，把Array放首位，If放之后。具体使用时可以进行测试。

其他聚合函数及其组合子相关见文档

https://clickhouse.yandex/docs/zh/query_language/agg_functions/combinators/

五、URL操作函数

URL 操作、IP操作，UUID操作函数，可查阅文档

https://clickhouse.yandex/docs/zh/query_language/functions/url_functions/

Clickhouse开发调优

一、利用数组

示例：使用array join 和 group Array 实现分组求TopN。例如求每个区县排名AQI最大的两个站点

SELECT
	CountyID,
	PointID,
	num,
	AQI
FROM
	(SELECT
	CountyID,
	groupArray(2)(AQI) as aqi_list,
	groupArray(2)(PointID) as point_list,
	arrayEnumerate(aqi_list) as index_list
	FROM
		(SELECT
		CountyID,PointID,AQI
		FROM aqi_test.point_daily WHERE DataDate = '2018-12-01' ORDER BY AQI DESC)
	GROUP BY CountyID) array join aqi_list as AQI,point_list as PointID,index_list as num
ORDER BY CountyID

返回值示例：
区县   站点  排名  AQI
区县A，站点ID，1，198
区县A，站点ID，2，182
区县B，站点ID，1，178
区县B，站点ID，2，134
区县C，站点ID，1，163
区县C，站点ID，2，114

二、创建物化视图

示例：创建一个跟踪point_hourly的AggregatingMergeTree物化视图，可以随着point_hourly数据的增加。

AggregatingMergeTree 聚合表引擎,通过聚合表引擎处理增量数据。

保存聚合的中间状态，避免每次从头开始聚合计算。

MATERIALIZED 表示物化视图。

POPULATE 表示将在创建时将该表的数据插入到物化视图中，否则将创建一个空表，仅追加新增数据。

avgStateIf, 其中State表示在聚合表引擎中保存字段的聚合状态。

CREATE MATERIALIZED VIEW aqi_test.viewTest
ENGINE=AggregatingMergeTree()  PARTITION BY toYYYYMM(DataDate) ORDER BY PointID 
POPULATE as SELECT
PointID,DataDate,avgStateIf(SO2Value,SO2Value>0)
FROM aqi_test.point_hourly
GROUP BY PointID,DataDate

查询时，使用group by ，聚合函数使用avgMerge() 表示聚合。

SELECT
PointID,DataDate,avgMerge(`avgSo2`)
FROM aqi_test.viewTest where PointID = ? and DataDate = ''
GROUP By PointID,DataDate

查询效率提升效果，需要实际应用场景测试。

Clickhouse 运维相关

一、查询系统、配置情况

1、查询磁盘容量信息，磁盘剩余空间信息

filesystemCapacity() 磁盘容量（字节）

filesystemAvailable() 磁盘剩余空间（字节）

二、分区操作

可以对分区执行以下操作：

DETACH PARTITION –将分区移动到detached目录，相当于回收站。

ALTER TABLE test.point_daily_test DETACH  PARTITION 20190201

DROP PARTITION –删除分区。(该数据将在所有副本上被删除)

ALTER TABLE test.point_daily_test DROP  PARTITION 20190201

ATTACH PART | PARTITION –将detached目录中的一部分或分区添加到表中。

ALTER TABLE test.city_daily_new ATTACH PARTITION 20190201;

REPLACE PARTITION-将数据分区从一个表复制到另一个表。

ALTER TABLE test.city_daily_new REPLACE PARTITION 20190301 FROM aqi_test.city_daily_test

FETCH PARTITION –从另一台服务器下载分区。

ALTER  TABLE  table_name  FETCH  PARTITION  partition_expr  From 'path-in-zookeeper'

这个仅适用于使用同一套zookeeper的Clickhouse 服务器之间，如果不是，可以使用remote函数。

如下方式，插入其他远程服务器的数据。注意Clickhouse插入数据有分区数限制，默认限制最大为100

由于我们的表都是按日期分区，因此一次最多插入100天的数据。此配置项可修改。

INSERT INTO TABLENAME
SELECT  * FROM remote('iP',DATABASE.TABLENAME,'username','password')

三、系统表

关于分区的进一步细粒度的操作，一个分区中还会继续划分多个文件块，每一个文件块有一个name。

系统表system.parts 中可以查询目前所有分区，他们的分区ID，分区下的文件块的name，文件块挂载于

磁盘上的位置，写入的时间等详细信息。对于大部分分区操作，可以针对具体的某一个文件块（part）进行操作。

系统表system.detached_parts，相当于回收站表。

可以查询到所有被放入detached区域的所有分区/文件块信息。

四、数据备份

1、clickhouse-copier

司马灰

关注

0
点赞
踩
13

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫