pandas.to_gbq()追加数据到BigQuery报ArrowTypeError日期错误

问题现象 #

调用pandas.to_gbq()向BigQuery表以追加模式写入DataFrame时持续报错，已提前核对确认待写入DataFrame的schema、字段数据类型与目标BigQuery表完全匹配。

复现代码 #

df.to_gbq(destination_table = PROCESSED_DATA_TABLE_NAME,
          project_id = PROJECT_NAME,
          if_exists = 'append')

报错信息 #

File ~\Documents\DartsModel\update_processed_visit_data\main_dev.py:152 in <module>
    df.to_gbq(destination_table = PROCESSED_DATA_TABLE_NAME,

  File ~\Anaconda3\envs\darts_model\lib\site-packages\pandas\core\frame.py:2054 in to_gbq
    gbq.to_gbq(

  File ~\Anaconda3\envs\darts_model\lib\site-packages\pandas\io\gbq.py:212 in to_gbq
    pandas_gbq.to_gbq(

  File ~\Anaconda3\envs\darts_model\lib\site-packages\pandas_gbq\gbq.py:1198 in to_gbq
    connector.load_data(

  File ~\Anaconda3\envs\darts_model\lib\site-packages\pandas_gbq\gbq.py:591 in load_data
    chunks = load.load_chunks(

  File ~\Anaconda3\envs\darts_model\lib\site-packages\pandas_gbq\load.py:238 in load_chunks
    load_parquet(

  File ~\Anaconda3\envs\darts_model\lib\site-packages\pandas_gbq\load.py:130 in load_parquet
    client.load_table_from_dataframe(

  File ~\Anaconda3\envs\darts_model\lib\site-packages\google\cloud\bigquery\client.py:2628 in load_table_from_dataframe
    _pandas_helpers.dataframe_to_parquet(

  File ~\Anaconda3\envs\darts_model\lib\site-packages\google\cloud\bigquery\_pandas_helpers.py:672 in dataframe_to_parquet
    arrow_table = dataframe_to_arrow(dataframe, bq_schema)

  File ~\Anaconda3\envs\darts_model\lib\site-packages\google\cloud\bigquery\_pandas_helpers.py:617 in dataframe_to_arrow
    bq_to_arrow_array(get_column_or_index(dataframe, bq_field.name), bq_field)

  File ~\Anaconda3\envs\darts_model\lib\site-packages\google\cloud\bigquery\_pandas_helpers.py:342 in bq_to_arrow_array
    return pyarrow.Array.from_pandas(series, type=arrow_type)

  File pyarrow\array.pxi:1033 in pyarrow.lib.Array.from_pandas

  File pyarrow\array.pxi:312 in pyarrow.lib.array

  File pyarrow\array.pxi:83 in pyarrow.lib._ndarray_to_array

  File pyarrow\error.pxi:123 in pyarrow.lib.check_status

ArrowTypeError: Expected bytes, got a 'datetime.date' object

当前环境依赖版本 #

python==3.9.12
pandas==1.4.2
pandas-gbq==0.17.6
arrow==1.2.2
google-cloud-bigquery==3.2.0
google-cloud-bigquery-storage==2.13.2

根因说明 #

报错出现在Parquet序列化环节，当前版本组合下的google-cloud-bigquery类型转换逻辑存在兼容bug：当DataFrame中存在Python原生datetime.date类型的列（而非pandas内置的datetime64[ns]类型）时，pyarrow无法正确识别类型，直接抛出类型不匹配错误。

解决方法 #

按优先级从高到低可选择以下方案：

• 类型转换修复：写入前将所有原生date类型的列转为pandas datetime类型，可批量处理所有日期列：

import datetime
import pandas as pd

# 遍历所有列，识别并转换原生date类型
for col in df.columns:
    col_non_empty = df[col].dropna()
    if col_non_empty.empty:
        continue
    sample_val = col_non_empty.iloc[0]
    if isinstance(sample_val, datetime.date):
        df[col] = pd.to_datetime(df[col])

转换完成后再调用to_gbq()即可正常写入。

• 依赖版本修复：将google-cloud-bigquery升级至3.4.0及以上版本，官方已在该版本修复原生date对象的Arrow类型转换逻辑；如果不想升级大版本，也可以固定pyarrow<8.0.0，避开该兼容问题。
• 绕开Parquet写入路径：给to_gbq()传入use_parquet=False参数，改用CSV序列化方式写入，可直接绕开Arrow转换环节，缺点是大文件写入性能会低于Parquet模式。

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