观远 BI

高性能数据集&ClickHouse语法注意事项

创建于 2022-11-01 / 最近更新于 2023-10-27 / 2349
字体: [默认] [大] [更大]

1)关于窗口函数

BI版本号5.9之前默认不支持窗口函数。如需使用,需要将bi系统升级到5.9及之后的版本进行体验(另外,高性能数据集的ck版本要求22以上)。

2)大小写敏感
a. 函数名称:
ClickHouse的某些函数对大小写敏感,比如toDate()函数写成todate()就会报错;但对某些函数又大小写不敏感,比如length()、Length()都可以返回字符串长度,String函数注意大写,cast ([字段] as String),其中S要大写。具体使用的时候需注意。
b.
表名&字段名称:ClickHouse对于字段名称的大小写也是敏感的,比如ddl定义了一张名为orders的表,其中有一个字段叫做Itemnum,那么要查询此表的这个字段,必须写作select Itemnum from orders其他的不管把字段名写作ItemNum或者表名写作Orders都会报错。

3)函数差异
a.
ClickHouse中常用函数的名称、语法可能与常规数据库不同。比如字符串转换成日期,在ClickHouse中是toDate(),而不是to_date()或str_to_date()。关于toDate()函数官方文档未做详细说明,个人测试此函数似乎只支持数字,比如toDate(11111),或者 'yyyy-MM-dd', 'yyyy/MM/dd', 'yyyy MM dd'格式及相应带时分秒格式的字符串,输入其他格式的日期字符串则会报错。如有解析'yyyyMMdd'等其他格式的日期字符串,可以先对字符串做处理,或者使用parseDateTimeBestEffort()函数,此函数可自动支持各种格式的日期字符串,具体可参考官方文档。
b. ClickHouse中很多类型转换函数都有OrNull,OrZero的后缀,这是用于处理不符合相应函数处理范围的数据或NULL的。比如toInt8('12.34'),12.34不是整型数字的字符串,不可被toInt8解析成功,会报错;如果使用toInt8OrZero('12.34')则会返回0,或toInt8OrNull('12.34')则会返回null。

4)数据类型差异

a. ClickHouse中基本的数据类型与其他数据库大差不差,部分类型名称可能存在差异,比如整型字段在其他数据库中是int,smallint,bigint等,而在ClickHouse中是Int8,Int16,Int32等。

b. 此处要介绍的是ClickHouse中比较独特的一种数据类型Nullable:
个人认为Nullable严格来说甚至不能算是一种数据类型,而是对具体数据类型的一种特殊标识、限制。带Nullable()的字段,允许其中的缺失值默认存储为NULL,比如Nullable(Int8)类型的字段,可以存储Int8类型的值,且没有具体数值的行对应的值将被存储为NULL。
之所以特别强调Nullable()是因为ClickHouse对于数据类型限制极为严格,在上面的场景中,如果字段类型为Int8,传入的值存在NULL,则会报错。

5)单引号和双引号的使用

spark里习惯使用英文双引号来引用字符串,单引号也是兼容的,但是ClickHouse里需要严格使用英文状态下的单引号(''),不然会报错。

6)dateDiff函数的使用

datediff函数在spark里只需要两个参数【终止日期字段】和【起始日期字段】,如datediff([离职日期], [入职日期]),返回同一行中"离职日期"至"入职日期"的间隔天数。dateDiff函数在ClickHhouse里需要3个或者4个参数。

dateDiff('unit', startdate, enddate, [timezone])
/*--
支持的时间单位: second, minute, hour, day, week, month, quarter, year.
[timezone]这个参数是可选参数,可根据实际场景填写。
--*/ 
dateDiff( 'day',[开始日期],[结束日期])
/*-------计算两个日期之间天数的差----------*/

7)date_sub函数和subtractDays函数

date_sub函数是spark的语法。date_sub([销售日期],1),返回销售日期前一天的日期;
subtractDays函数是ClickHouse的语法,写法是一样的:subtractDays([销售日期],1),返回销售日期前一天的日期。

8)clickhouse要求年份必须在1970~2139年之间,否则会报错

ClickHouse exception , code :321, host :xxx.xx.x.xx, port :xxxx; Code :xxx, e .displayText0= DB : Exception : Input value 2932783 of a column "UInt16” is greater than max alowed Date value , which is 49710( version 20.4.5.36( official build ))


18 人点赞过