BigQuery半结构化数据存储对比

1. 一、 📊 BigQuery 结构不固定数据存储方式对比分析

存储方式	查询效率	存储效率	查询/计算费用	易用性/灵活性
JSON 字符串 (STRING)	差	中 (需额外转义)	高 (需全表扫描+昂贵的函数)	低 (需复杂的解析函数)
RECORD (STRUCT)	优 (对已知字段)	优 (高效列式存储)	低 (只扫描所需列)	差 (结构变化需修改表结构)
JSON 类型 (JSON)	优 (比 STRING 快)	中 (比 RECORD 差)	中/高 (需扫描整个 JSON 列)	优 (灵活，原生查询函数)

---

2. 二、详细评估与分析

2.1. 存储为 JSON 字符串 (STRING)

这种方式是将整个结构体数据序列化为一个巨大的 JSON 文本字符串，然后存储在一个 STRING 类型的列中。

• 查询效率 (差):
  • 查询内部字段时，必须使用昂贵的 UDF (用户定义函数) 或 JSON_EXTRACT 等字符串函数进行 运行时解析。
  • 每次查询都需要重新解析整个字符串，并且 BigQuery 无法利用列式存储的优势 来跳过不相关的部分。
  • 无法通过分区/聚簇 (Clustering) 来优化 字符串内部字段的查询。
• 存储效率 (中):
  • 由于是字符串，会产生额外的转义字符和冗余的键名存储，通常比优化的列式存储（如 RECORD）要大。
• 查询/计算费用 (高):
  • 由于解析操作昂贵且需要对包含 JSON 字符串的列进行 全扫描，计算费用（在使用按需计费时）会很高。
• 易用性/灵活性 (低):
  • 查询语法复杂，可读性差，难以维护。
  • 结构变化时，存储本身不需要修改，但所有依赖于该结构的查询都需要修改解析逻辑。

2.2. 存储为 RECORD (STRUCT)

这种方式是将结构体数据映射为 BigQuery 的 RECORD (或称 STRUCT) 类型，内部的每个字段都是一个独立的子列。

• 查询效率 (优):
  • BigQuery 的核心优势是列式存储。RECORD 字段会被高效地存储，查询时 只扫描所需的子列，速度极快。
  • 可以对 RECORD 内部的字段进行有效的 分区 和 聚簇 优化。
• 存储效率 (优):
  • 得益于列式存储的压缩和优化，通常是 存储效率最高 的方式。
• 查询/计算费用 (低):
  • 由于只扫描必要的子列，扫描数据量最小，因此按需计费的费用最低。
• 易用性/灵活性 (差):
  • 最大的缺点： 当结构不固定时，这种方式很痛苦。任何结构体内部字段的增删改，都需要修改 BigQuery 的表结构 (Schema)。这在大规模、快速迭代的场景下维护成本极高。

2.3. 存储为 JSON 类型 (JSON)

JSON 类型是 BigQuery 近年引入的新特性，用于原生支持半结构化数据。它将 JSON 数据以 优化的二进制格式 存储。

• 查询效率 (优于 STRING):
  • 虽然不如 RECORD，但由于 BigQuery 原生支持 JSON 类型，它在内部使用优化的格式（例如，可能类似 BSON 或其他内部结构）来存储，查询时 避免了 STRING 类型所需的昂贵文本解析。
  • 使用 JSON_VALUE 和 JSON_QUERY 等函数进行查询，性能远高于对 STRING 使用同样的函数。
• 存储效率 (中):
  • 比 STRING 略好，但因为需要存储键名和结构信息，仍不如 RECORD 的列式优化。
• 查询/计算费用 (中/高):
  • 查询时需要扫描整个 JSON 类型的列，无法像 RECORD 那样只扫描所需的内部子列。这意味着扫描的数据量通常大于 RECORD，费用也更高。
  • 不过，由于解析效率高，总的 CPU 消耗和查询时间会比 STRING 低。
• 易用性/灵活性 (优):
  • 最大的优势： 数据结构可以自由变化，不需要修改表结构。这是处理结构不固定数据的 最灵活 方式。
  • 查询语法相对简洁和原生化。

---

3. 三、总结与选择建议

对于您提到的 “结构不固定的结构体数据”，核心的权衡在于 灵活性 与 性能/费用：

1. 首选（最佳平衡）：JSON 类型 (JSON)

   • 适用场景： 数据结构经常变化（结构不固定 是主要矛盾），但您又希望在性能和查询便利性上优于传统的 JSON 字符串。
   • 优点： 兼顾了 RECORD 的原生查询能力和 STRING 的灵活性，是官方推荐的半结构化数据存储方案。

2. 次选（性能至上）：RECORD (STRUCT)

   • 适用场景： 结构变化频率极低或可预测，且您对查询性能和费用（只扫描所需列）有最高的要求。
   • 提示： 考虑使用 “部分 RECORD + 部分 JSON” 的混合模式，将经常查询且结构稳定的字段提升为 RECORD 列，将不常查询或结构经常变化的字段打包为一到多个 JSON 类型列。

3. 最不推荐（避免使用）：JSON 字符串 (STRING)

   • 适用场景： 除非您的数据源严格限制只能导出为扁平的 CSV/TSV，且您必须保持原样。但在所有涉及内部字段查询的场景中，它都是性能最差、费用最高的方式。

---

4. JSON Type 限制

• 如果您使用批量加载作业将 JSON 数据注入到表中，则源数据必须采用 CSV、Avro 或 JSON 格式。不支持其他批量加载格式。
• JSON 数据类型的嵌套上限为 500。
• 不能使用旧版 SQL 来查询包含 JSON 类型的表。
• 无法对 JSON 列应用行级访问权限政策。
• 您无法对表在 JSON 列上进行分区或聚簇，因为 JSON 类型上未定义等式和比较运算符。

---

5. 日期格式推断

BigQuery 会根据源数据的格式检测日期和时间值。

• DATE 列中的值必须采用以下格式：YYYY-MM-DD。
• TIME 列中的值必须采用以下格式：HH: MM: SS [.SSSSSS]（小数秒部分是可选的）。
• 对于 TIMESTAMP 列，BigQuery 会检测各种时间戳格式，包括但不限于：

YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSSSSS
YYYY/MM/DD HH:MM
时间戳还可包含世界协调时间 (UTC) 偏移量或世界协调时间 (UTC) 可用区指示符 (Z)。

以下是 BigQuery 将自动检测为时间戳值的一些值示例：

2018-08-19 12:11
2018-08-19 12:11:35.22
2018/08/19 12:11
2018-08-19 07:11:35.220 -05:00
如果未启用自动检测功能，并且您的值采用的格式不在上述示例中，则 BigQuery 只能以 STRING 数据类型加载相应列。您可以启用自动检测功能，让 BigQuery 将这些列识别为时间戳。例如，只有在启用自动检测功能后，BigQuery 才会加载 2025-06-16T16:55:22Z 作为时间戳。