将JSON数据加载到Google BigQuery的模式

Question

我对我们正在进行的项目有一个问题...

我尝试将此JSON提取到Google Big Query中，但无法从JSON输入中获取JSON投票对象字段。 我已尝试在模式中使用“record”和“string”类型。

{
    "votes": {
        "funny": 10,
        "useful": 10,
        "cool": 10
    },
    "user_id": "OlMjqqzWZUv2-62CSqKq_A",
    "review_id": "LMy8UOKOeh0b9qrz-s1fQA",
    "stars": 4,
    "date": "2008-07-02",
    "text": "This is what this 4-star bar is all about.",
    "type": "review",
    "business_id": "81IjU5L-t-QQwsE38C63hQ"
}

我无法从以下JSON中填充类别和街区JSON数组的表格。这些输入的模式应该是什么？不幸的是，文档在这种情况下没有提供太多帮助，或者可能我没有看对地方。

{
    "business_id": "Iu-oeVzv8ZgP18NIB0UMqg",
    "full_address": "3320 S Hill St\nSouth East LA\nLos Angeles, CA 90007",
    "schools": [
        "University of Southern California"
    ],
    "open": true,
    "categories": [
        "Medical Centers",
        "Health and Medical"
    ],
    "neighborhoods": [
        "South East LA"
    ]
}

我能够获取常规字段，但只有这些... 非常感谢您的帮助！

- Snow Monkey

---

输入JSON: {"business_id": "Iu-oeVzv8ZgP18NIB0UMqg", "full_address": "3320 S Hill St\nSouth East LA\nLos Angeles, CA 90007", "schools": ["University of Southern California"], "open": true}解析所给模式：business_id:string,full_address:string,schools:string,open:boolean错误字段：schools，非重复字段指定了数组 - Snow Monkey

另一个输入：{"votes": {"funny": 0, "useful": 1, "cool": 0}, "name": "LiiLii C."}给定模式：votes:string，name:string错误：字段：投票，为非记录字段指定了JSON映射 - Snow Monkey

Answer 1

对于“商业（business）”，似乎您希望学校成为一个重复字段。您的模式应该是：

"schema": {
    "fields": [
        {
            "name": "business_id",
            "type": "string"
        }.
        {
            "name": "full_address",
            "type": "string"
        },
        {
            "name": "schools",
            "type": "string",
            "mode": "repeated"
        },
        {
            "name": "open",
            "type": "boolean"
        }
    ]
}

对于 votes，似乎您想要记录。您的模式应该是：

"schema": {
    "fields": [
        {
            "name": "name",
            "type": "string"
        }.
        {
            "name": "votes",
            "type": "record",
            "fields": [
                {
                    "name": "funny",
                    "type": "integer",
                },
                {
                    "name": "useful",
                    "type": "integer"
                },
                {
                    "name": "cool",
                    "type": "integer"
                }
            ]
        },
    ]
}

Source

- Tjorriemorrie

Answer 2

我也曾经卡在这个问题上，但是我遇到的问题是因为必须记得将模式标记为“重复”以便记录source

另外请注意，这些不能有空值source

- Gustav Hartz

---

你应该将问题标记为重复，而不是回答问题链接。 - Greedo