如何基于JSON数据自动创建并填充BigQuery表？

完全可以用BigQuery原生功能实现，下面是几种实用方案：

方案一：Web UI手动导入（适合快速操作） #

BigQuery支持自动推断JSON数据的架构，但需要先把你的JSON数组转换成**换行分隔JSON（NDJSON）**格式——也就是每个JSON对象单独占一行，格式如下：

{"id": "0001", "customer": {"name": "John", "age": 30, "cars": ["Ford", "BMW", "Fiat"]}}
{"id": "0002", "customer": {"name": "Mary", "age": 22, "cars": ["Ford", "Fiat"]}}

操作步骤：

• 打开BigQuery控制台，进入目标数据集，点击「创建表」
• 数据源选「上传」，选择刚才保存的NDJSON文件
• 目标表填写名称，架构选择「自动检测」，BigQuery会自动识别出嵌套结构：id为字符串，customer为记录类型（包含name字符串、age整数、cars字符串数组）
• 点击「创建表」，完成后就能直接查看导入的数据

方案二：bq命令行工具（适合自动化场景） #

同样先把JSON转成NDJSON格式保存为data.json，然后执行以下命令：

bq load --source_format=NEWLINE_DELIMITED_JSON --autodetect 你的项目ID.你的数据集ID.目标表名 ./data.json

--autodetect参数会让BigQuery自动推断架构，执行完成后表和数据就都准备好了。

方案三：SQL直接创建并填充（适合临时测试/字符串形式的JSON） #

如果你的JSON数组是以字符串形式存在的，可以直接用SQL解析并创建表：

CREATE OR REPLACE TABLE 你的项目ID.你的数据集ID.目标表名
AS
WITH json_data AS (
  SELECT '[
    {"id": "0001", "customer": {"name": "John", "age": 30, "cars": ["Ford", "BMW", "Fiat"]}},
    {"id": "0002", "customer": {"name": "Mary", "age": 22, "cars": ["Ford", "Fiat"]}}
  ]' AS raw_json
)
SELECT
  JSON_EXTRACT_SCALAR(item, '$.id') AS id,
  STRUCT(
    JSON_EXTRACT_SCALAR(item, '$.customer.name') AS name,
    CAST(JSON_EXTRACT(item, '$.customer.age') AS INT64) AS age,
    JSON_EXTRACT_ARRAY(item, '$.customer.cars') AS cars
  ) AS customer
FROM json_data, UNNEST(JSON_EXTRACT_ARRAY(raw_json)) AS item

执行后会生成符合要求的表，架构和自动推断的一致，数据也会直接填充进去。

最终生成的表架构如下：

id: STRING
customer: RECORD
  name: STRING
  age: INTEGER
  cars: ARRAY<STRING>

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