ClickHouse exception, code: 41 ClickHouse修改表字段类型后查询异常 遇到的坑

ClickHouse exception, code: 41

ClickHouse修改表字段类型后查询异常遇到的坑

由于把clickHouse表中的xxx字段类型string改为DateTime 出现如下错误

ClickHouse exception, code: 41, host: 192.168.0.123, port: 8123; Code: 41, e.displayText() = 
DB::ParsingException: Cannot parse datetime: Cannot parse DateTime from String: while executing 
'FUNCTION CAST(xxx :: 17, 'DateTime' :: 37) -> CAST(xxx, 'DateTime') DateTime : 36': (while reading from 
part /clickhouse/clickhouse/xxxxxx/): While executing MergeTree (version .... (official build))

1.修改字段类型 (修改后可能会出现查询异常)

ALTER TABLE db_name.table_name MODIFY COLUMN endTimestamp Int64;

条件允许的情况下（数据有备份 或者有方法再恢复） 把表删除，再重启创建表

-- 删除表
DROP table db.本地表

-- 然后再创建表 此时记得把类型改好
CREATE TABLE db.table(
`id` UInt64,
`name` String,
`deleted` UInt8,
`createdAt` DateTime,
`updatedAt` DateTime)ENGINE = ReplacingMergeTree(updatedAt) ORDER BY (id) PRIMARY KEY(id) SETTINGS index_granularity = 8192

2.其他相关命令

修改字段名称

ALTER TABLE db_name.table_name RENAME COLUMN regionId to region;

添加字段

ALTER TABEL db_name.table_name ADD COLUMN `$user_id` Nullable(String); 

删除字段

ALTER TABEL db_name.table_name DROP COLUMN `$user_id`;