ClickHouse写入异常：数值列存0、日期列存1970-01-01求助

常见原因及排查步骤 #

• 字段名不匹配：ClickHouse默认大小写敏感，JSON字段名与表列名（包括Kafka表、ReplacingMergeTree基表）的拼写/大小写不一致时，会用类型默认值填充（数值0、日期1970-01-01）。直接对比JSON字段和表列名，确保完全一致。
• Kafka表格式配置错误：如果Kafka表未指定正确的JSON解析格式（如kafka_format = 'JSONEachRow'），ClickHouse无法正确解析JSON数据，会填充默认值。检查Kafka表的SETTINGS部分，确认格式设置正确。
• 数据类型不兼容或格式错误：
  • 数值列：若JSON中数值是字符串类型（如"123"）且包含非数字字符，或表列类型与JSON数值类型不匹配（如JSON是Float但表列是Int），可能解析失败为0。
  • 日期列：若JSON中日期是字符串（如"2023-08-01"）但格式与表列DateTime要求不符，或为时间戳字符串未转换，会被解析为 epoch 时间（1970-01-01）。
• 物化视图字段映射错误：物化视图的SELECT语句中，字段未正确从Kafka表映射到基表，比如漏写字段、别名错误，导致写入默认值。检查物化视图的SELECT逻辑，确认字段一一对应。

验证与解决示例 #

1. 检查Kafka表解析结果：直接查询Kafka表，确认原始数据是否已被正确解析：

SELECT * FROM kafka_table LIMIT 10;

如果查询结果已经是0和1970，问题出在Kafka表的解析环节；若结果正确，则排查物化视图的写入逻辑。

2. 修正Kafka表格式配置：确保Kafka表指定JSON解析格式：

ALTER TABLE kafka_table MODIFY SETTING kafka_format = 'JSONEachRow';

3. 处理日期时间转换：若JSON中日期是时间戳字符串，在物化视图中显式转换：

CREATE OR REPLACE MATERIALIZED VIEW mv TO base_table AS
SELECT
    id,
    toDateTime(create_time) AS create_time, -- 将时间戳转为DateTime类型
    amount
FROM kafka_table;

4. 修复字段名映射：若JSON与表列名大小写/拼写不同，在物化视图中显式映射：

CREATE OR REPLACE MATERIALIZED VIEW mv TO base_table AS
SELECT
    UserID AS user_id, -- JSON字段UserID映射到表列user_id
    create_time,
    amount
FROM kafka_table;

内容的提问来源于stack exchange，提问作者Shoxrux