使用clickhouse-driver的insert_dataframe方法遇TypeError: unhashable type: 'dict'

数据老陈

2026-7-31

问题

在数据管道中使用clickhouse-driver的Client调用insert_dataframe方法时，反复遇到错误：

TypeError("unhashable type: 'dict'")

相关代码如下：

cl = Client(**pl.config.click_credents.get('client_local')) # 连接客户端
chunk = data_building(f_name) # 读取并预处理数据
cl.insert_dataframe("INSERT INTO {} (列名) VALUES".format(tabname), chunk, settings={'use_numpy':True})

已确认DataFrame的列名不是字典类型，但仍报错，完整报错栈：

Traceback (most recent call last):
  File "/PATH/.ve/lib/python3.11/site-packages/pandas/core/arrays/categorical.py", line 441, in __init__
    codes, categories = factorize(values, sort=True)
                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "PATH/.ve/lib/python3.11/site-packages/pandas/core/algorithms.py", line 818, in factorize
    codes, uniques = factorize_array(
                     ^^^^^^^^^^^^^^^^
  File "PATH/.ve/lib/python3.11/site-packages/pandas/core/algorithms.py", line 574, in factorize_array
    uniques, codes = table.factorize(
                     ^^^^^^^^^^^^^^^^
  File "pandas/_libs/hashtable_class_helper.pxi", line 5943, in pandas._libs.hashtable.PyObjectHashTable.factorize
  File "pandas/_libs/hashtable_class_helper.pxi", line 5857, in pandas._libs.hashtable.PyObjectHashTable._unique
TypeError: unhashable type: 'dict'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "PATH/new_tools.py", line 98, in <module>
    chunk_to_click(take_1, 'TABNAME')
  File "PATH/new_tools.py", line 69, in chunk_to_click
    cl.insert_dataframe(
  File "PATH/.ve/lib/python3.11/site-packages/clickhouse_driver/client.py", line 456, in insert_dataframe
    rv = self.send_data(sample_block, data, columnar=True)
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "PATH/.ve/lib/python3.11/site-packages/clickhouse_driver/client.py", line 571, in send_data
    self.connection.send_data(block)
  File "PATH/.ve/lib/python3.11/site-packages/clickhouse_driver/connection.py", line 587, in send_data
    self.block_out.write(block)
  File "/PATH/.ve/lib/python3.11/site-packages/clickhouse_driver/streams/native.py", line 38, in write
    write_column(self.context, col_name, col_type, items,
  File "PATH/.ve/lib/python3.11/site-packages/clickhouse_driver/columns/service.py", line 157, in write_column
    column.write_data(items, buf)
  File "PATH/.ve/lib/python3.11/site-packages/clickhouse_driver/columns/base.py", line 87, in write_data
    self._write_data(items, buf)
  File "/PATH/.ve/lib/python3.11/site-packages/clickhouse_driver/columns/numpy/lowcardinalitycolumn.py", line 37, in _write_data
    c = pd.Categorical(items)
        ^^^^^^^^^^^^^^^^^^^^^
  File "PATH/.ve/lib/python3.11/site-packages/pandas/core/arrays/categorical.py", line 443, in __init__
    codes, categories = factorize(values, sort=False)
                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "PATH/.ve/lib/python3.11/site-packages/pandas/core/algorithms.py", line 818, in factorize
    codes, uniques = factorize_array(
                     ^^^^^^^^^^^^^^^^
  File "PATH/.ve/lib/python3.11/site-packages/pandas/core/algorithms.py", line 574, in factorize_array
    uniques, codes = table.factorize(
                     ^^^^^^^^^^^^^^^^
  File "pandas/_libs/hashtable_class_helper.pxi", line 5943, in pandas._libs.hashtable.PyObjectHashTable.factorize
  File "pandas/_libs/hashtable_class_helper.pxi", line 5857, in pandas._libs.hashtable.PyObjectHashTable._unique
TypeError: unhashable type: 'dict'

DataFrame的数据类型信息：

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 7304 entries, 0 to 7303
Data columns (total 18 columns):
 #   Column              Non-Null Count  Dtype         
---  ------              --------------  -----         
 0   amplitude_id        7304 non-null   uint32        
 1   user_properties     7304 non-null   object        
 2   app                 7304 non-null   uint16        
 3   city                7087 non-null   object        
 4   country             7304 non-null   object        
 5   device_type         6100 non-null   object        
 6   device_id           7304 non-null   object        
 7   event_type          7304 non-null   object        
 8   event_properties    7304 non-null   object        
 9   event_time          7304 non-null   datetime64[ns]
 10  user_creation_time  0 non-null      datetime64[ns]
 11  ip_address          7304 non-null   object        
 12  platform            7304 non-null   object        
 13  session_id          7304 non-null   uint32        
 14  user_id             6808 non-null   float32       
 15  version_name_1      7304 non-null   float32       
 16  version_name_2      7304 non-null   float32       
 17  role_type           7304 non-null   object        
dtypes: datetime64[ns](2), float32(3), object(10), uint16(1), uint32(2)
memory usage: 841.8+ KB

解决思路

排查Object列的单元格值：报错栈显示生成pd.Categorical时失败，说明某列的单元格内容是字典，而非列名是字典。重点检查user_properties和event_properties这两列，执行以下代码确认：
```
print(chunk['user_properties'].apply(lambda x: isinstance(x, dict)).any())
print(chunk['event_properties'].apply(lambda x: isinstance(x, dict)).any())
```

处理嵌套字典列：如果确认是这两列存了字典，转成ClickHouse支持的格式：

序列化为JSON字符串（对应ClickHouse列类型设为String或JSON）：

import json
chunk['user_properties'] = chunk['user_properties'].apply(json.dumps)
chunk['event_properties'] = chunk['event_properties'].apply(json.dumps)

展开字典为多列（若字典键固定）：

user_props_df = chunk['user_properties'].apply(pd.Series)
chunk = pd.concat([chunk, user_props_df], axis=1).drop('user_properties', axis=1)

调整LowCardinality相关设置：报错来自lowcardinalitycolumn.py，若ClickHouse表对应列用了LowCardinality类型，可尝试关闭use_numpy参数，或修改表结构去掉LowCardinality，或提前把列转成字符串再插入。
检查预处理逻辑：确认data_building函数在读取数据时，没有把嵌套字典保留在DataFrame单元格中，比如读取JSON文件时是否错误保留了嵌套结构。

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

使用clickhouse-driver的insert_dataframe方法遇TypeError: unhashable type: 'dict'

问题 #

解决思路 #

问题

解决思路