BigQuery 中的嵌套数据（重复记录）

BigQuery 支持表中的嵌套记录。嵌套记录可以是单个记录，也可以是包含重复值。本页面简要介绍了如何在 Looker 中处理 BigQuery 嵌套数据。

嵌套记录的优势

在扫描分布式数据集时，使用嵌套记录有几个好处：

• 嵌套记录不需要联接。这意味着，与每次查询额外数据时必须重新联接额外数据相比，计算速度更快，扫描的数据也更少。
• 嵌套结构本质上是预联接表。如果您不引用嵌套列，则不会增加查询费用，因为 BigQuery 数据存储在列中。如果您确实引用了嵌套列，则逻辑与共置联接相同。
• 嵌套结构可以避免在广泛的反规范化表中出现重复数据。换句话说，对于住在五个城市的人，一个宽广的反规范化表会将他们的所有信息分到五行（他们所居住的城市各占一行）。在嵌套结构中，重复信息只占一行，因为由五个城市组成的数组可以包含在一行中，并根据需要解除嵌套。

在 LookML 中处理嵌套记录

以下 BigQuery 表 persons_living 显示了存储示例用户数据（包括 fullName、age、phoneNumber 和 citiesLived）的典型架构，以及每列的数据类型和 mode。此架构显示 citiesLived 列中的值重复了，这表明某些用户可能居住在多个城市：

以下示例是您可以从所示架构中创建的探索和视图的 LookML。有三个视图：persons、persons_cities_lived 和 persons_phone_number。该探索看起来与使用非嵌套表编写的探索完全相同。

注意：在下例中，虽然所有组件（视图和探索）都是在一个代码块中编写的，但最佳做法是将视图放在各个视图文件中，并将探索和 connection: 规范放在模型文件中。

-- model file

connection: "bigquery_publicdata_standard_sql"

explore: persons {

  # Repeated nested object
  join: persons_cities_lived {
    view_label: "Persons: Cities Lived:"
    sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived ;;
    relationship: one_to_many
  }

  # Non repeated nested object
  join: persons_phone_number {
    view_label: "Persons: Phone:"
    sql: LEFT JOIN UNNEST([${persons.phoneNumber}]) as persons_phone_number ;;
    relationship: one_to_one
  }

}

-- view files

view: persons {

 sql_table_name: bigquery-samples.nested.persons_living ;;

  dimension: id {
    primary_key: yes
    sql: ${TABLE}.fullName ;;
  }

  dimension: fullName {label: "Full Name"}

  dimension: kind {}

  dimension: age {type:number}

  dimension: citiesLived {hidden:yes}

  dimension: phoneNumber {hidden:yes}

  measure: average_age {
    type: average
    sql: ${age} ;;
    drill_fields: [fullName,age]
  }

  measure: count {
    type: count
    drill_fields: [fullName, cities_lived.place_count, age]
  }
}

view: persons_phone_number {

  dimension: areaCode {label: "Area Code"}

  dimension: number {}
}

view: persons_cities_lived {

  dimension: id {
    primary_key: yes
    sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${place} AS STRING)) ;;
  }

  dimension: place {}

  dimension: numberOfYears {
    label: "Number Of Years"
    type: number
  }

  measure: place_count {
    type: count
    drill_fields: [place, persons.count]
  }

  measure: total_years {
    type: sum
    sql: ${numberOfYears} ;;
    drill_fields: [persons.fullName, persons.age, place, numberOfYears]
  }

}

以下各部分更详细地讨论了用于处理 LookML 中嵌套数据的每个组件：

• 视图
• 记录声明
• 联接
• 简单的重复值

视图

每个嵌套记录均写入一个视图。例如，phoneNumber 视图只会声明记录中显示的维度：

view: persons_phone_number {

  dimension: areaCode {label: "Area Code"}

  dimension: number {}

}

persons_cities_lived 视图更为复杂。如 LookML 示例所示，您可以定义记录中显示的维度（numberOfYears 和 place），但您也可以定义一些测量。测量和 drill_fields 会照常定义，就好像此数据位于自己的表中一样。唯一的实际区别是，您将 id 声明为 primary_key，以便正确计算汇总数据。

view: persons_cities_lived {

  dimension: id {
    primary_key: yes
    sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${place} AS STRING)) ;;
  }

  dimension: place {}

  dimension: numberOfYears {
    label: "Number Of Years"
    type: number
  }

  measure: place_count {
    type: count
    drill_fields: [place, persons.count]
  }

  measure: total_years {
    type: sum
    sql: ${numberOfYears} ;;
    drill_fields: [persons.fullName, persons.age, place, numberOfYears]
  }

}

记录声明

在包含子记录的视图（在本例中为 persons）中，您需要声明记录。创建联接时将使用这些变量。您可以使用 hidden 参数隐藏这些 LookML 字段，因为浏览数据时不需要这些字段。

view: persons {

  ...
  dimension: citiesLived {
    hidden:yes
    }

  dimension: phoneNumber {
    hidden:yes
    }
  ...

}

联接数

BigQuery 中的嵌套记录是 STRUCT 元素的数组。联接关系内置于表中，而不是使用 sql_on 参数进行联接。在这种情况下，您可以使用 sql: 联接参数，以便使用 UNNEST 运算符。除此之外，解除 STRUCT 元素数组的嵌套与联接表完全相同。

对于非重复记录，只需使用 STRUCT；您可以将其放在方括号中，以将其转换为 STRUCT 元素的数组。虽然这可能看起来很奇怪，但似乎并没有性能下降，这使得一切保持简洁明了。

explore: persons {

  # Repeated nested object
  join: persons_cities_lived {
    view_label: "Persons: Cities Lived:"
    sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived ;;
    relationship: one_to_many
  }

  # Non repeated nested object
  join: persons_phone_number {
    view_label: "Persons: Phone:"
    sql: LEFT JOIN UNNEST([${persons.phoneNumber}]) as persons_phone_number ;;
    relationship: one_to_one
  }

}

每行没有唯一键的数组的联接

虽然最好在数据中使用可识别的自然键，或在 ETL 流程中创建的代理键，但这并非总能实现。例如，您可能会遇到某些数组没有相应行的相对唯一键的情况。这就是 WITH OFFSET 在联接语法中的用武之地。

例如，如果某个人曾居住在多个城市（芝加哥、丹佛、旧金山等），则代表某人的列可能会加载多次。如果没有提供日期或其他可识别的自然键来区分此人在每个城市的任期，则很难在未嵌套的行上创建主键。这时，WITH OFFSET 可以为每个未嵌套的行提供相对行号 (0,1,2,3)。此方法可保证未嵌套行上的唯一键：

explore: persons {

  # Repeated nested Object
  join: persons_cities_lived {
    view_label: "Persons: Cities Lived:"
    sql: LEFT JOIN UNNEST(persons.citiesLived) as persons_cities_lived WITH OFFSET as person_cities_lived_offset;;
    relationship: one_to_many
  }

}

view: persons_cities_lived {

  dimension: id {
    primary_key: yes
    sql: CONCAT(CAST(${persons.fullName} AS STRING),'|', CAST(${offset} AS STRING)) ;;
  }

  dimension: offset {
    type: number
    sql: person_cities_lived_offset;;
  }

}

简单的重复值

BigQuery 中的嵌套数据也可以是简单值，例如整数或字符串。如需解除简单重复值数组的嵌套，可以使用与上文所述类似的方法，在联接中使用 UNNEST 运算符。

以下示例会解除给定的整数数组“unresolved_skus”的嵌套：

explore: impressions {
  join: impressions_unresolved_sku {
    sql: LEFT JOIN UNNEST(unresolved_skus) AS impressions_unresolved_sku ;;
    relationship: one_to_many
  }

}

view: impressions_unresolved_sku {

  dimension: sku {
    type: string
    sql: ${TABLE} ;;
  }

}

整数数组的 sql 参数 unresolved_skus 表示为 ${TABLE}。这会直接引用值表本身，而该值随后会在 explore 中解除嵌套。