Pandas向Clickhouse插入数据时空值如何触发列定义的默认值

问题1解决方案（无需拆分批次插入） #

有两个更简洁的实现方案，性能都远高于拆分两次插入：

方案1：调整driver配置适配空值转换 #

clickhouse_driver 关闭use_numpy模式时会自动将Python的None识别为SQL层面的NULL，配合ClickHouse的input_format_null_as_default参数，即可让空值自动触发列定义的默认值，代码示例：

import pandas as pd
from clickhouse_driver import Client

# 把NaT统一替换为None
df['day'] = df['day'].replace({pd.NaT: None})

with Client(host="", port="", password="",
            user="", settings={
                "use_numpy": False,
                "input_format_null_as_default": True
            }) as client:
    client.insert_dataframe(
        'INSERT INTO test_table (name, day) VALUES',
        df
    )

如果需要保留use_numpy的高性能插入能力，可以用下面的方案。

方案2：插入时通过SQL逻辑替换零值 #

不需要修改Python侧的空值处理逻辑，直接在插入SQL中做零值判断，自动替换为列默认值，性能损耗可以忽略：

from clickhouse_driver import Client

with Client(host="", port="", password="",
            user="", settings={"use_numpy": True}) as client:
    client.command(
        '''
        INSERT INTO test_table 
        SELECT name, if(day = toDateTime64('1970-01-01 00:00:00', 3), DEFAULT, day) 
        FROM input('name String, day DateTime64(3)')
        ''',
        data=df.to_records(index=False).tolist()
    )

问题2原理说明 #

这不是ClickHouse DateTime类型的缺陷，是两类设计逻辑共同作用的结果：

• ClickHouse的隐式类型转换规则设计：所有无法解析为合法时间的字符串（包括空字符串）、超出时间范围的数值，都会被转换为DateTime类型的零值1970-01-01 00:00:00，这个规则是通用的强类型转换逻辑，并非针对DateTime的特殊处理。
• clickhouse_driver在开启use_numpy模式时，为了适配numpy数组的连续存储结构，会直接将NaT转换为datetime64类型的零值，而非SQL层面的NULL，所以不会触发自定义默认值。关闭use_numpy模式后，driver会将None/NaT识别为SQL的NULL，配合input_format_null_as_default参数即可正常触发自定义默认值。

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