2023年6月23日发(作者：)

【clickhouse实践】关于clickhouse对空值的处理总结1 背景在⼯作中，我们在使⽤spark dataset向clickhouse向表中批量插⼊数据时，经常遇到某个字段为NULL导致导数任务失败。⽽我们在clickhouse按照正常⽅式建表时，我们并不能保证每条插⼊的数据的每个字段都是⾮NULL值。基于这个背景我们建表、查询、关联三个⽅⾯沉淀下clickhouse中应该如何处理NULL给我们带来的困扰。2 建表空值处理2.1 问题描述下⾯我们正常按官⽹提供的建表语句，正常进⾏建表语句如下：CREATE TABLE _user_phone_detail( `user_id` String COMMENT '⽤户id', `user_name` String COMMENT '⽤户姓名', `phone` String COMMENT '⽤户电话', `create_time` DateTime COMMENT '数据⼊表时间', `update_time` DateTime COMMENT '数据修改时间')

ENGINE = MergeTree

PARTITION BY user_id

ORDER BY user_idSETTINGS index_granularity = 8192;这个时候，如果将包含空值的数据，插⼊到表中时，会报错。DB::Exception: Expression returns value NULL, that is out of range of type String, at: null)我们发现clickhouse告诉我们‘表达式返回值NULL，这超出了String类型的范围’。这个时候我们知道clickhouse在默认情况下数据类型中是不⽀持NULL这个值的。2.2 解决⽅案经过研究们发现clickhouse提供了为NULL单独提供了⼀中类型是Nullable。那么问题的解决⽆⾮就是两种思路：1. 从clickhouse本⾝出发，让clickhouse中的各个数据类型⽀持NULL值。2. 从数据⾓度出发，消除数据中的null值为数据设置默认值。2.2.1 clickhouse 本⾝解决从clickhouse本⾝解决⽆疑就是研究如何利⽤Nullable这种类型。我们先来看这种数据类型。什么是Nullable1. ⽤法：Nullable(TypeName),表⽰某类型可以⽀持存储NULL。2. 允许⽤特殊标记 (NULL) 表⽰缺失值，可以与 TypeName 的正常值存放⼀起。 例如，Nullable(Int8) 类型的列可以存储Int8 类型值，⽽没有值的⾏将存储 NULL。对于 TypeName，不能使⽤复合数据类型 阵列 和 元组。3. 复合数据类型可以包含 Nullable 类型值，例如Array(Nullable(Int8))。4. Nullable 类型字段不能包含在表索引中。 这就决定了在clickhouse表中主键决不能使⽤Nullable做修饰，因为我们知道clickhouse中主键⾃⾝是被添加索引的。5. 除⾮在 ClickHouse 服务器配置中另有说明，否则 NULL 是任何 Nullable 类型的默认值。应⽤Nullable：CREATE TABLE _user_phone_detail( `user_id` String COMMENT '⽤户id', `user_name` Nullable(String) COMMENT '⽤户姓名', `phone` Nullable(String) COMMENT '⽤户电话', `create_time` Nullable(DateTime) COMMENT '数据⼊表时间', `update_time` Nullable(DateTime) COMMENT '数据修改时间')

ENGINE = MergeTree

PARTITION BY user_id

ORDER BY user_idSETTINGS index_granularity = 8192;注： 需要特别注意的是表中的主键不可加Nullable修饰否则违背第四条定义。因此主键是不可以包含空值的，如果把主键加上Nullable建表时就会报错。2.2.2 数据⾓度解决相⽐从clickhouse⾓度考虑，从数据⾓度考虑消除空值显得不是那么⼀劳永逸，我们就需要不厌其烦的在数据⼊库时给每条数据的NULL设置默认值。其实不同关系型或者类关系型数据源都⽀持设置判断字段是否为NULL设置默认值的函数如：Mysql中IFNULL(expr1,expr2)，Hive中的Nvl(expr1,expr2),但是针对不同的数据源⼊库我们难以对所有数据源的设置的类似功能的函数做出⼀致要求这⾥我们推荐两种⽅法进⾏处理：函数，当然看个⼈喜好，这⾥推荐灵活度较⾼的1.

coalesce(expr1,expr2,expr3,....)该函数返回所有设置表达式中的第⼀个⾮空表达式的结果。2. if(expr1,value1,value2) 该函数的第⼀个参数是表达式完全可以进⾏⾃定义使⽤。关键字 ，当然CASE WHEN THEN ELSE END了。实例：SELECT if(user_id is not null,user_id,'@') AS user_id, if(user_name is not null,user_name,'@') AS user_name, if(phone is not null,phone,'@') AS phone, if(create_time is not null,date_format(create_time,'yyyy-MM-dd hh:mm:ss'),date_format(CURRENT_DATE,'yyyy-MM-dd hh:mm:ss')) AS create_time, if(update_time is not null,date_format(update_time,'yyyy-MM-dd hh:mm:ss'),date_format(CURRENT_DATE,'yyyy-MM-dd hh:mm:ss')) AS update_timeFROM _user_phone_detail注： 值得说的是这种⽅式，使⽤前提是在不影响数据使⽤和数理逻辑的情况下。3 查询空值处理3.1 问题分析上⾯说了建表的问题，接下来要实例⼀下，当我们表已经建好，且表数据已经有了，⼀列数据既包含NULL，⼜包含''这类空值，这个时候，如果不注意语法，会报错，如果包含这两类数据，不能使⽤coalesce，如下：select coalesce(user_name, 0) as user_name_a, count(distinct phone) as ph_cntfrom _user_phone_detail group by user_name_a报错如下：错误原因是user_name是String类型，不可以改成UInt8类型Code: 386, yText() = DB::Exception: There is no supertype for types String, UInt8 because some of them are String/FixedString and some of them

are not (version 19.17.6.36 (official build))3.2 解决⽅案这⾥有⼀个⼩的知识点：group by后⾯的名称，可以写select中的逻辑，也可以写as为别名，下⾯使⽤case when改写上⾯的内容--⽅式⼀select case when user_name is null or user_name = '' then 'null' else user_name end as user_name, count(distinct phone) as ph_cntfrom _user_phone_detailgroup by user_name

--⽅式⼆select case when user_name is null or paymentterm = '' then 'null' else user_name end as user_name, count(distinct phone) as ph_cntfrom _user_phone_detailgroup by case when user_name is null or paymentterm = '' then 'null' else user_name end

--⽅式三select coalesce(user_name,'null') as user_name, count(distinct phone) as ph_cntfrom _user_phone_detailgroup by coalesce(user_name,'null')这⼏种⽅式都是可以达到效果的 。4.关联问题如下场景，需要使⽤a表关联b表，把a和b都有的id剔除，在hive中我们⼀般这样实现：select a.*

from a

left join b

on =

where is null不过这种⽅式，在CK中是有问题的，要借⽤coalesce来完成select a.*

from a

left join b

on =

where coalesce(,0) = 05 总结对于clickhouse中的NULL，处理接简单总结到这⾥，对于建表和查询我们进⾏了⽐较详细的分析，对关联的处理我们简单的举了⼀个实例，这个还需要⼤家在实际实际场景进⾏灵活变通与应⽤。希望能够帮⼤家。