加载到 Google Big Query 时出现 JSON 格式错误

Question

我正在尝试使用内置的数据流模板从 PUBSUB 的 Big Query 中加载以下数据:

{
    "current_speed": "19.09",
    "_east": "-87.654561",
    "_last_updt": "2018-07-17 15:50:54.0",
    "_region_id": "1",
    "_north": "42.026444",
    "_south": "41.997946",
    "region": "Rogers Park - West Ridge",
    "_west": "-87.709645",
    "_description": "North of Devon. Kedzie to Lake Shore"
}

但我不断收到此错误:

"Error while reading data, error message: Failed to parse JSON: Unexpected end of string; Unexpected end of string; Expected key"

我实际上需要加载更大的数据集，如下所示:

 [{
    "current_speed": "19.09",
    "_east": "-87.654561",
    "_last_updt": "2018-07-17 15:50:54.0",
    "_region_id": "1",
    "_north": "42.026444",
    "_south": "41.997946",
    "region": "Rogers Park - West Ridge",
    "_west": "-87.709645",
    "_description": "North of Devon. Kedzie to Lake Shore"
}, {
    "current_speed": "25.23",
    "_east": "-87.747456",
    "_last_updt": "2018-07-17 15:50:54.0",
    "_region_id": "2",
    "_north": "42.0190998",
    "_south": "41.960669",
    "region": "Far North West",
    "_west": "-87.84621",
    "_description": "North of Montrose. East River to Cicero"
}

]

但是我收到了这个错误:

Error while reading data, error message: Failed to parse JSON: No object found when new array is started.; BeginArray returned false; Parser terminated before end of string

我在这里做错了什么？

Answer 1

是的，BigQuery 只接受换行分隔的 JSON，这意味着每行一个完整的 JSON 对象。在将对象合并到一行之前，BigQuery 读取“{”，它是对象的开始，并期望读取一个键，但该行已结束，因此您会看到错误消息“预期键”。

对于多个 JSON 对象，只需在每一行中放置一个。不要将它们包含在数组中。 BigQuery 期望每一行都以一个对象“{”开头。如果您将“[”作为第一个字符，您将看到第二条错误消息，这意味着 BigQuery 读取一个数组但不在对象内部。