一对多关系

我们假设每种农产品都可以在不同的农场生产,每个农场都有许多不同的员工。

在BigQuery中,拥有3个表以及它们之间的关系没有什么错——但是您也可能希望利用BigQuery的嵌套和重复列支持。

对于这个组成的示例,我们可以按照以下方式对其进行建模:

SELECT 'tomato' produce, STRUCT<farm ARRAY<STRUCT<farm_id string, employee ARRAY<STRUCT<name string>>>>>(
  [
    STRUCT('farm1' AS farm_id, [STRUCT('employee1' AS name), STRUCT('employee2')] AS employee ) 
     , ('farm2', [STRUCT('employee3' AS name), STRUCT('employee4')])
     , ('farm3', [STRUCT('employee5' AS name), STRUCT('employee6')])
  ]) AS farms
UNION ALL
SELECT 'lettuce', STRUCT<ARRAY<STRUCT<farm_id string, employee ARRAY<STRUCT<name string>>>>>(
  [
    STRUCT('farm4' AS farm_id, [STRUCT('employee7' AS name), STRUCT('employee8')] AS employee ) 
     , ('farm5', [STRUCT('employee9' AS name)])
  ]) AS farms

问:像这样做有意义吗?

A:视情况而定。

正如劳埃德所说:

在扫描分布式数据集时,嵌套记录有两个优点。首先,它们不需要联接。这意味着,与每次使用额外数据时都必须重新连接的情况相比,计算速度更快,扫描的数据更少。

嵌套结构本质上是预联接的表。而且,由于数据是按列存储的,因此如果不引用嵌套列,则不会向查询增加任何开销。如果确实引用嵌套列,则逻辑与并置联接相同。

嵌套结构带来的另一个好处是,它们避免了必须在一个宽的非规范化表中重复的重复数据。换言之,对于居住在五个城市的人,一个宽的非规范化表将包含他们所有的信息,分为五行(每个城市一个)。在嵌套结构中,重复的信息只需要一行,因为五个城市的数组可以包含在一行中,并且只在需要时才不被列出。

• https://discourse.looker.com/t/nested-data-in-bigquery-repeated-records/4174

同时,对于不用于处理嵌套数据的用户和工具来说,查询将更加困难。