BigQuery插入REPEATED RECORD遇'repeated values...'错误排查与解决

问题现象 #

在BigQuery中插入REPEATED模式的RECORD字段时，触发错误提示repeated values added outside of an array。已确认传入的是数组格式，且同结构的config_onboarding字段可正常插入，最初误以为是BigQuery的bug。

表结构Schema #

[
  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "id",
        "type": "STRING"
      },
      {
        "mode": "NULLABLE",
        "name": "date",
        "type": "DATETIME"
      }
    ],
    "mode": "REPEATED",
    "name": "config_onboarding",
    "type": "RECORD"
  },
  {
    "mode": "NULLABLE",
    "name": "date_insert",
    "type": "DATETIME"
  },
  {
    "fields": [
      {
        "mode": "NULLABLE",
        "name": "key",
        "type": "STRING"
      },
      {
        "mode": "NULLABLE",
        "name": "value",
        "type": "STRING"
      }
    ],
    "mode": "REPEATED",
    "name": "features",
    "type": "RECORD"
  }
]

成功插入的代码示例 #

仅插入config_onboarding字段时执行成功，返回空数组表示无错误，数据可正常查询：

self._client.insert_rows(
    table=table,
    rows=[
        {
            'config_onboarding': [
                {'id': 'template-install', 'date': '2021-07-01 18:43:44.810000'}, 
                {'id': 'run-workflow', 'date': '2021-07-01 18:43:44.810000'}, 
                {'id': 'template-workflow-view', 'date': '2021-07-01 18:43:44.810000'}, 
                {'id': 'automate-workflow-wrapper', 'date': '2021-07-01 19:50:00.901000'}, 
                {'id': 'share-workflow', 'date': '2021-07-01 19:50:00.901000'}
            ],
            'date_insert': datetime.now()
        }
    ],
    selected_fields=self._schema,
    ignore_unknown_values=True,
    skip_invalid_rows=True
)

失败的代码示例 #

添加同结构的features字段后执行失败，返回包含"repeated values added outside of an array"的错误数组：

self._client.insert_rows(
    table=table,
    rows=[
        {
            'config_onboarding': [
                {'id': 'template-install', 'date': '2021-07-01 18:43:44.810000'}, 
                {'id': 'run-workflow', 'date': '2021-07-01 18:43:44.810000'}, 
                {'id': 'template-workflow-view', 'date': '2021-07-01 18:43:44.810000'}, 
                {'id': 'automate-workflow-wrapper', 'date': '2021-07-01 19:50:00.901000'}, 
                {'id': 'share-workflow', 'date': '2021-07-01 19:50:00.901000'}
            ], 
            'features': [
                {'key': 'foo', 'value': 'bar'}, 
                {'key': 'hello', 'value': 'World'}
            ], 
            'date_insert': datetime.now()
        }
    ],
    selected_fields=self._schema,
    ignore_unknown_values=True,
    skip_invalid_rows=True
)

问题根源与解决 #

经排查，错误的核心原因是代码中selected_fields参数对应的self._schema里缺少features字段的定义。当指定selected_fields时，BigQuery会严格按照传入的Schema解析数据，此时如果传入的数据包含Schema未定义的REPEATED RECORD字段，就会误判为“在数组外添加重复值”，触发报错。

解决方法：更新self._schema，将features字段的完整定义补充进去，确保selected_fields与表结构、传入的数据字段完全匹配即可解决问题。

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