使用autodetect将动态模式JSON文件加载到BigQuery表中

Question

这个问题与this one非常相似——但是使用python API，几年后——由于解决方案不完整，我认为最好打开一个新问题

---

我有一堆ndjson文件，每天在GCS上生成，我想把它们加载到BQ表中

来自file_1.json的几行示例：

{"a": 1, "b": 2, "c": [1,2,4], "d": "string"}
{"a": 1, "c": [2, 4], "d": "some_string"}
{"a": 1, "e": 4}

一个file_2.json的例子：

{"a": 4, "e": 6, "f": {"g": 7, "h": "str"}}
{"a": 1, "c": [2, 4], "f": {"g": 5}}

大多数字段都是可选的，每个文件中的每个文件记录理论上可以包含字段的任意组合（目前选择的字段约为50个，但将来会发生变化并增加）。同一命名字段的内容（将任何错误放在一边）应包含相同的数据类型

我想将一组文件（某一天的所有文件）加载到一个BQ表中，该表的模式由文件中所有字段的并集组成，其中一行没有对应于字段的键，该值为空

我希望这样做，而不需要维护模式

我目前的尝试：

job_config = bigquery.LoadJobConfig(
    write_disposition="WRITE_TRUNCATE",
    create_disposition="CREATE_IF_NEEDED",
    autodetect=True,
    ignore_unknown_values=True,
    schema_update_option="ALLOW_FIELD_ADDITION",
    source_format="NEWLINE_DELIMITED_JSON"
)
uri = "gs://my-bucket/test/*.json"

load_job = client.load_table_from_uri(
    uri,
    table_id,
    location="EU",
    job_config=job_config,
)

load_job.result() 

使用autodetect=True避免显式指定架构，但由于autodetect最多可以扫描单个文件中的500行，因此最初可能不会创建某些字段

我希望schema_update_option="ALLOW_FIELD_ADDITION"（ref here）能满足我的需要，但它不起作用

我尝试过的另一个选择是：

# Instead of wildcard, get blob list and convert to uris
blobs = storage_client.get_bucket(BUCKET).list_blobs(prefix=FOLDER)
uris = [f"gs://{BUCKET}/{blob.name}" for blob in blobs if blob.name.endswith(".json")]

job_config = bigquery.LoadJobConfig(
    write_disposition="WRITE_APPEND", #Will append file by file
    create_disposition="CREATE_IF_NEEDED",
    autodetect=True,
    schema_update_option="ALLOW_FIELD_ADDITION",
    source_format="NEWLINE_DELIMITED_JSON"
)

for uri in uris:
    load_job = client.load_table_from_uri(
        uri,
        table_id,
        location="EU",
        job_config=job_config,
    )

    load_job.result()

我可以想象，这是效率较低的，但无论如何，它也不起作用，会出现以下错误：

BadRequest: 400 Provided Schema does not match Table my-bucket:test.test_diff_schema. Cannot add fields (field: f)

同样，我认为schema_update_option="ALLOW_FIELD_ADDITION"可以避免这种情况，但它似乎只适用于预定义的模式，而不适用于自动检测

欢迎有任何想法，提前谢谢

Answer 1

在autodetect中，BigQuery读取一堆第一行数据并尝试检测模式。在第一次运行之后，将设置模式，并且在整个摄取过程中无法更改模式。您可以使用“忽略未知值”选项跳过与自动检测模式不匹配的值

但它不会如你所愿工作。我知道BigQuery团队致力于更好的JSON集成。目前，我只能建议您将JSON作为字符串接收，并使用BigQueryJSON函数浏览文档