如何将含UUID、DateTime数组的pandas DataFrame写入ClickHouse表

解决步骤 #

第一步：DataFrame数据预处理 #

所有报错的核心原因是数据类型和ClickHouse表结构不匹配，先执行以下转换：

import numpy as np
from datetime import datetime

# 1. 转换UUID列为字符串，匹配ClickHouse UUID类型输入要求
df['UserID'] = df['UserID'].astype(str)

# 2. 转换Views数组为Python原生列表，同时移除时间的时区信息避免范围报错
df['Views'] = df['Views'].apply(
    lambda x: [
        dt.replace(tzinfo=None) if isinstance(dt, datetime) and dt.tzinfo else dt 
        for dt in x.tolist()
    ] if isinstance(x, np.ndarray) else x
)

# 3. 转换Events数组为Python原生列表
df['Events'] = df['Events'].apply(lambda x: x.tolist() if isinstance(x, np.ndarray) else x)

# 4. 转换RecDate为无时区的Python datetime类型
df['RecDate'] = df['RecDate'].apply(lambda x: x.to_pydatetime().replace(tzinfo=None))

第二步：两种可行插入方案 #

方案1：使用clickhouse_driver插入（推荐）

from clickhouse_driver import Client

# 初始化ClickHouse客户端，端口默认9000，按需修改配置
client = Client(
    host='localhost',
    port=9000,
    user='default',
    password='',
    database='default'
)

# 执行插入
client.insert_dataframe(
    'INSERT INTO rec_eval_data VALUES',
    df,
    settings={'use_numpy': True}
)

方案2：使用pandahouse插入

添加method='POST'参数即可解决只读请求问题：

import pandahouse as ph

connection = {
    'database': 'default',
    'host': 'http://localhost:8123',
    'user': 'default',
    'password': ''
}

ph.to_clickhouse(
    df,
    'rec_eval_data',
    index=False,
    chunksize=100000,
    connection=connection,
    method='POST'
)

过往方案报错原因说明 #

• 原生插入报错：数组列是numpy.ndarray格式，非Python原生列表，或时间列带时区导致范围溢出
• pandahouse报错：未指定POST请求方式，默认GET请求仅支持查询操作
• insert_dataframe报错：数组列未从numpy.ndarray转为Python原生列表
• 逐行插入报错：拼接SQL时未给UUID等字符串类型加单引号，ClickHouse将带横杠的UUID识别为减法运算，所以报找不到对应列的错误，该写法存在SQL注入风险，不推荐使用

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