clickhouse String to DataTime 报错

clickhouse 2022-06-04 6113 字 114 浏览 点赞

起步

昨天我强迫症犯了,心血来潮要规范生产环境 clickhouse 表中列的类型,简单说就是一个列从 String 类型修改为 DateTime 类型,这里假设列名是 res_time。没有在开发环境做测试,直接冲,结果导致只要执行 select res_time from ... 就报错。

我还是 clickhouse 新手,有太多不懂,为解决这个问题连着中午觉没睡前后花了三小时,终于是搞定了。处理问题期间其实慌得不行,一想到有 2000w+ 的数据要“死”在我手里,我人应该就没了……

解决以后复盘,发现问题不难,是我缺少排查 clickhouse 的思路。

操作

为讲明白这个过程,我将现实场景简化如下。


业务上,我们使用的是分布式表。先创建本地表,再创建全局表:

-- 1. 创建本地表
create table guan.test_01_local on cluster guan_01_cluster( 
  id String,
  ch_createtime DateTime default now()
) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/01/test_01_local', '{replica}', ch_createtime)
partition by toYYYYMM(ch_createtime)
order by id

-- 2. 创建分布式表
create table guan.test_01_all on cluster guan_01_cluster as guan.test_01_local
    ENGINE = Distributed(guan_01_cluster, guan, test_01_local, murmurHash3_64(`id`))

规范列类型之前,表中有 2000w+ 的数据。先插两条数据伪装“2000w”,然后添加 String 类型列。

-- 1. 插入数据
insert into guan.test_01_all(id) values(1), (2)

-- 2. 本地表 添加 res_time String 列
alter table guan.test_01_local on cluster guan_01_cluster add column res_time String default ''

-- 3. 全局表 添加 res_time String 列
alter table guan.test_01_all on cluster guan_01_cluster add column res_time String default ''

这时候查看之前插入的数据,可以看到 res_time 以空字符串的形式展示出来的(这也是我后来排查错方向的原因)。

:) select * from guan.test_01_all format CSV;

"1","2022-06-04 06:05:38",""
"2","2022-06-04 06:05:38",""

修改列类型之前,res_time 以 String 类型运行了一天多。我们伪装这“一天多”写入的数据。

-- 1. 插入 res_time 为时间格式正常的数据
insert into guan.test_01_all(id, res_time) values(3, now()), (4, now())

-- 2. 插入 res_time 为空字符串的数据
insert into guan.test_01_all(id, res_time) values(5, ''), (6, '') 

修改列 res_time 类型为 DateTime:

-- 1. 本地表 修改 res_time 为 DateTime
alter table guan.test_01_local on cluster guan_01_cluster modify column res_time DateTime default '2000-01-01 00:00:01'

-- 2. 全局表 修改 res_time 为 DateTime
alter table guan.test_01_all on cluster guan_01_cluster modify column res_time DateTime default '2000-01-01 00:00:01'

现象

执行完 modify 之后,我 show create table 了一下,确认类型修改成功。

CREATE TABLE guan.test_01_local
(
`id` String,
`ch_createtime` DateTime DEFAULT now(),
`res_time` DateTime DEFAULT '2000-01-01 00:00:01'
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/01/test_01_local', '{replica}', ch_createtime)
PARTITION BY toYYYYMM(ch_createtime)
ORDER BY id
SETTINGS index_granularity = 8192

很自然 select 一下,看到“最早”(add column 操作之前插入)的数据可以正常查询。

:) select * from guan.test_01_all where id in ('1', '2') format CSV;

"1","2022-06-04 06:05:38","2000-01-01 00:00:01"
"2","2022-06-04 06:05:38","2000-01-01 00:00:01"

事实上,线上操作我也很谨慎,modify 之前把数据处理速度从单次 5000 调整为 1。观察进程日志,insert 没有报错,数据正常写入。其实我们的模拟环境也能正常写入数据。

insert into guan.test_01_all(id, res_time) values(7, now()), (8, now())

然而,过一段时间后,我发现执行 SQL 会报错: Cannot parse datetime: Cannot parse DateTime from String: while executing 'FUNCTION _CAST(res_time :: 2, 'DateTime' :: 3) -> _CAST(res_time, 'DateTime') DateTime : 1'

select * from guan.test_01_all order by ch_createtime desc limit 1;

经过几次测试,得出只要 select 中没有 res_time 就可以正常执行。可以确定:是我 modify 搞出问题了

“发版出问题立刻回滚”是互联网基操。于是 modify 后 30 分钟,我决定把 DateTime 改回 String。于是执行:

-- 本地表 修改 res_time 类型为 String
alter table guan.test_01_local on cluster guan_01_cluster modify column res_time String default '2000-01-01 00:00:01';

-- 全局表
alter table guan.test_01_all on cluster guan_01_cluster modify column res_time String default '2000-01-01 00:00:01';

执行 SQL 阻塞,直到超时。

Received exception from server:

Code: 159. DB::Exception: Watching task /clickhouse/task_queue/ddl/query-0000000042 is executing longer than distributed_ddl_task_timeout (=180) seconds. There are 4 unfi

nished hosts (0 of them are currently active), they are going to execute the query in background. (TIMEOUT_EXCEEDED) (version 21.12.4.1 (official build))

几次尝试无果后,我打算删除 res_time 列,删除之后再以 DateTime 类型 add 回来,感觉不会有问题。搞!首先删除全局表的 res_time,业务进程还在跑,避免 insert 报错。

alter table guan.test_01_all on cluster guan_01_cluster drop column res_time;

还是执行 SQL 阻塞,直到超时


转而我以为是 modify 没执行完,查了 zk 中的 mutations、log 节点 —— 看不懂,也觉得没啥问题。

解决

最后观察到 system.errors 日志,发现有 CANNOT_PARSE_DATETIME 错误,对应的 last_error_time 时间一直在更新。

select name, last_error_time, last_error_message from system.errors order by last_error_time desc limit 3

这里有个天坑:CANNOT_PARSE_DATETIME 并不发生一次就在 errors 表里记录一次,而是修改 last_error_time 值。所以最开始我看到这个异常的时候,还以为是自己之前手动执行 select res_time 的报错。没放心上,往别的方向排查。

等我观察到 last_error_time 一直在更新,我才明白,是最开始的 modify 事件根本就没有执行完,失败之后又自动重试,重试失败就报错,再等下一次重试。

查询 system.mutations 表进行确认:

select mutation_id, table, is_done, command from system.mutations

  • is_done 表示没执行完。
  • latest_fail_reason 说明了失败原因,截图没有展示出来。

OK,只要终止 modify column DateTime 就可以了。从文档里查到 kill mutation 语法。

kill mutation on cluster guan_01_cluster where database='guan' and table='test_01_local';

观察 errors 表,没有错误更新。到这里大概知道是因为空字符串转 DateTime 失败,导致 modify 一直重试。所以解决方案:

-- 1. 把不能转 DateTime 的字符串都更新
alter table guan.test_01_local on cluster guan_01_cluster update res_time='2000-01-01 00:00:01' where toDateTimeOrNull(res_time) is null

-- 2. 再把 String 转 DateTime
alter table guan.test_01_local on cluster guan_01_cluster modify column res_time DateTime default '2000-01-01 00:00:01'

总结

事实上,执行 modify DateTime 的时候 clickhouse 有错误反馈。然而我是在 redash 可视化界面上执行的 SQL,即便 modify 成功,redash 也会报错,界面上看不出来。直到我复盘改用终端才知道。

此番最大教训就是:线上改动一定要先自测。属实吓死我了……



本文由 Guan 创作,采用 知识共享署名 3.0,可自由转载、引用,但需署名作者且注明文章出处。

还不快抢沙发

添加新评论