clickhouse⽇期函数⽆效报错问题处理
clickhouse建表语句：
CREATE TABLE test.user_action_log
(
`event_time`DateTime,
`action` String,
`user_id` String,
`school_id` String,
`uuid` String,
`app_id` String,
`student_id` String,
`date_key` String
)
ENGINE= MergeTree()
PARTITION BY date_key
PRIMARY KEY user_id
ORDER BY(user_id, student_id, school_id, app_id)
unknown怎么处理
SETTINGS index_granularity =8192;
问题：
现象：
当我们使⽤指定⽇期查询时发现可以查询出结果：
localhost :)select count(1)from test.user_action_log where date_key ='2020-12-04';
SELECT count(1)
FROM test.user_action_log
WHERE date_key ='2020-12-04'
Query id: cab1314f-25e2-4328-b865-17ede5f0c2fc
┌──count(1)─┐
│105338324│
└───────────┘
1rows in set. Elapsed: 0.002 sec.
但是我们发现在使⽤⽇期加减函数时却发⽣报错：
localhost :)select count(1)from test.user_action_log where date_key = addsDays(today(),6);
SELECT count(1)
FROM test.user_action_log
WHERE date_key = addsDays(today(),-6)
Query id: 8d757fd0-6cbc-4b95-8b32-f87f78850db6
Received exception from server (version 20.11.3):
Code: 46. DB::Exception: Received from10.12.80.130:9000. DB::Exception: Unknown function addsDays. Maybe you meant: ['addDays','addYears']: While  processing SELECT count(1)FROM test.user_action_log WHERE date_key = addsDays(today(),-6).
0rows in set. Elapsed: 0.006 sec.
解决思路：
经过排查我们发现建表时字段使⽤了String类型，⽽addsDays(today(), 6)⽣成的数据类型是Date类型。这样我们就有了两种解决的思路。
⼀种是将WHERE条件中的⽇期字段强转为Date:
localhost :)select count(1)from test.user_action_log where cast(date_key AS date)= addDays(today(),-6);
SELECT count(1)
FROM test.user_action_log
WHERE CAST(date_key,'date')= addDays(today(),-6)
Query id: d0fb99a6-023f-403a-832b-629ce6011b53
┌──count(1)─┐
│105338324│
└───────────┘
1rows in set. Elapsed: 0.003 sec.
另⼀种是将WHERE条件中的⽇期字段强转为Date:
localhost :)select count(1)from test.user_action_log where date_key = cast(addDays(today(),-6)AS String);
SELECT count(1)
FROM test.user_action_log
WHERE date_key = CAST(addDays(today(),-6),'String')
Query id: 20cf4d53-07b9-47cd-9b66-51356f6d625a
┌──count(1)─┐
│105338324│
└───────────┘
1rows in set. Elapsed: 0.003 sec.
两种⽅式相⽐差别就是在where条件中在操作符前边添加运算会导致索引失效，也就是WHERE CAST(date_key, 'date') = addDays(today(), -6)会导致date_key索引失效，也就是该任务在执⾏时不在
⾛索引。
总结：
我们发现引起这种问题的根本原因是建表时⽇期字段使⽤错误，使⽤为String类型⽽不是Date类型导致，这就要求我们在建表⼀定要慎重使⽤字段类型，尤其是⽇期类型。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。