使用Python+Pandas向BigQuery推送数据时遇类型转换错误

错误日志 #

object of type <class 'str'> cannot be converted to int
File "/**/bq.py", line 71, in post
job = self.client.load_table_from_dataframe(df,
File "/**/tobq.py", line 99, in <module>
bq.post(data, target_table="tablemane")
pyarrow.lib.ArrowTypeError: object of type <class 'str'> cannot be converted to int

问题背景 #

脚本原本运行正常，现在出现上述类型转换错误。检查后发现测试数据、df.dtypes输出与BigQuery表结构完全匹配，但仍无法定位问题。

代码片段 #

df = pd.DataFrame(data)
df = df.reset_index(drop=True)
df['crawl_date'] = pd.to_datetime(df['crawl_date']).dt.date
df['r_index'] = df['rank_index'].astype('float')
df['v_index']=df['visibility_index'].astype('float')
df['s_var']=df['serp_var'].astype('float')
df['kd']=df['kd'].astype('float')        
df['camp_id'] = df['camp_id'].astype('int64')        
print(df.head())
print(df.isnull().sum())

table_id = f"{os.getenv('GCP_DATASET_NAME')}.{table_name}"

print(df.dtypes)
df.to_gbq(destination_table=table_id, table_schema=schema_path, project_id=os.getenv('GCP_PROJECT_NAME'), if_exists='append')

测试数据 #

[{'crawl_date': '2021-03-22', 'domain': 'www.example.com', 'categ': 't1', 'position': 1, 'position_spread': 'TOP_5', 'position_change': 0, 'v_index': 100, 'r_index': 100, 'estimated_traffic': 101881, 'traffic_change': 0, 'max_traffic': 0, 'device': 'desktop', 'top_rank': 1, 's_var': 0, 'kwd': '****** pro', 'volume': 461000, 'kd': 0, 'camp_id': 2, 'camp_name': '******'}]

df.dtypes输出 #

crawl_date            object
domain                object
categ                 object
position               int64
position_spread       object
position_change        int64
v_index               float64
r_index               float64
estimated_traffic      int64
traffic_change         int64
max_traffic            int64
device                object
top_rank               int64
s_var                 float64
kwd                   object
volume                 int64
kd                    float64
camp_id                int64
camp_name             object

BigQuery表结构 #

[
{
   "name": "crawl_date",
   "mode": "NULLABLE",
   "type": "DATE",
   "description": null,
   "fields": []
 },
 {
    "name": "domain",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": null,
    "fields": []
 },
{
    "name": "categ",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": null,
   "fields": []
  },
 {
    "name": "position",
    "mode": "NULLABLE",
    "type": "INTEGER",
    "description": null,
    "fields": []
 },
 {
    "name": "position_spread",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": null,
    "fields": []
  },
{
    "name": "position_change",
    "mode": "NULLABLE",
    "type": "INTEGER",
    "description": null,
    "fields": []
 },
 {
    "name": "v_index",
    "mode": "NULLABLE",
    "type": "FLOAT",
    "description": null,
    "fields": []
 },
 {
    "name": "r_index",
    "mode": "NULLABLE",
    "type": "FLOAT",
    "description": null,
    "fields": []
  },
  {
    "name": "estimated_traffic",
    "mode": "NULLABLE",
    "type": "INTEGER",
    "description": null,
   "fields": []
  },
 {
    "name": "traffic_change",
    "mode": "NULLABLE",
    "type": "INTEGER",
    "description": null,
    "fields": []
  },
 {
    "name": "max_traffic",
    "mode": "NULLABLE",
    "type": "INTEGER",
    "description": null,
    "fields": []
  }, 
  {
    "name": "device",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": null,
   "fields": []
  },
  {
    "name": "top_rank",
    "mode": "NULLABLE",
    "type": "INTEGER",
    "description": null,
    "fields": []
  }, 
 {
    "name": "s_var",
    "mode": "NULLABLE",
    "type": "FLOAT",
    "description": null,
    "fields": []
  },
  {
    "name": "kwd",
    "mode": "NULLABLE",
    "type": "STRING",
    "description": null,
    "fields": []
  },
  {
    "name": "volume",
    "mode": "NULLABLE",
    "type": "INTEGER",
    "description": null,
    "fields": []
  },
  {
    "name": "kd",
    "mode": "NULLABLE",
    "type": "FLOAT",
    "description": null,
    "fields": []
   },
  { 
    "name": "camp_id",
    "mode": "NULLABLE",
    "type": "INTEGER",
    "description": null,
    "fields": []
  },
  { 
     "name": "camp_name",
     "mode": "NULLABLE",
     "type": "STRING",
     "description": null,
     "fields": []
  }
]

排查与解决方法 #

1. 检查批量数据中的混合类型 #

测试数据没问题，但批量数据中可能存在隐藏的字符串值（比如空字符串、"N/A"、带引号的数字）混入整数列。执行以下代码检查所有整数类型列的元素类型：

# 遍历所有int64类型列，检查元素类型
int_columns = df.select_dtypes(include=['int64']).columns
for col in int_columns:
    unique_types = df[col].apply(type).unique()
    print(f"列名: {col}, 元素类型: {unique_types}")

如果输出中包含<class 'str'>，说明该列存在混合类型，需要清理：

# 强制转换为数值类型，无效值转为NaN后填充0（根据业务调整填充值）
for col in int_columns:
    df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0).astype('int64')

2. 修正crawl_date的转换方式 #

当前代码将crawl_date转为Python date对象（存为object类型），pyarrow转换时可能出现兼容性问题。改为转成标准日期字符串：

df['crawl_date'] = pd.to_datetime(df['crawl_date']).dt.strftime('%Y-%m-%d')

3. 校验schema文件与BQ实际结构 #

确保schema_path指向的JSON文件与BigQuery表结构完全一致，尤其注意字段顺序、类型、是否允许为空。如果schema定义与实际表结构冲突，也会导致类型转换错误。

4. 排查pyarrow版本问题 #

如果以上方法无效，可能是pyarrow版本兼容性问题。尝试升级或降级pyarrow：

pip install --upgrade pyarrow
# 或指定版本
pip install pyarrow==14.0.0

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