BigQuery admite registros anidados en las tablas. Los registros anidados pueden ser un solo registro o contener valores repetidos. En esta página, se proporciona una descripción general de cómo trabajar con datos anidados de BigQuery en Looker.
Las ventajas de los registros anidados
El uso de registros anidados tiene algunas ventajas cuando analizas un conjunto de datos distribuido:
- Los registros anidados no requieren combinaciones. Esto significa que los cálculos pueden ser más rápidos y analizar muchos menos datos que si tuvieras que volver a unir los datos adicionales cada vez que los consultas.
- Las estructuras anidadas son, en esencia, tablas unidas previamente. No se agrega ningún gasto a la consulta si no haces referencia a la columna anidada, ya que los datos de BigQuery se almacenan en columnas. Si haces referencia a la columna anidada, la lógica es idéntica a una unión con ubicación colocalizada.
- Las estructuras anidadas evitan repetir los datos que se tendrían que repetir en una tabla amplia desnormalizada. En otras palabras, para una persona que vivió en cinco ciudades, una tabla amplia con datos no normalizados contendría toda su información en cinco filas (una para cada una de las ciudades en las que vivió). En una estructura anidada, la información repetida solo ocupa una fila, ya que el array de cinco ciudades se puede contener en una sola fila y desanidar cuando sea necesario.
Cómo trabajar con registros anidados en LookML
La siguiente tabla de BigQuery, persons_living, muestra un esquema típico que almacena datos de usuario de ejemplo, incluidos fullName, age, phoneNumber y citiesLived, junto con el tipo de datos y el modo de cada columna. El esquema muestra que los valores de la columna citiesLived se repiten, lo que indica que algunos usuarios pueden haber vivido en varias ciudades:
El siguiente ejemplo es el código LookML para las exploraciones y vistas que puedes crear a partir del esquema anterior que se muestra. Hay tres vistas: persons, persons_cities_lived y persons_phone_number. La exploración parece idéntica a una que se escribió con tablas no anidadas.
Nota: Si bien en el siguiente ejemplo todos los componentes (vistas y Explorar) se escriben en un bloque de código, se recomienda colocar las vistas en archivos de vista individuales y las exploraciones y la especificación connection: en el archivo de modelo.
-- 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]
}
}
En las siguientes secciones, se analiza cada componente para trabajar con datos anidados en LookML con más detalle:
Vistas
Cada registro anidado se escribe como una vista. Por ejemplo, la vista phoneNumber simplemente declara las dimensiones que aparecen en el registro:
view: persons_phone_number {
dimension: areaCode {label: "Area Code"}
dimension: number {}
}
La vista persons_cities_lived es más compleja. Como se muestra en el ejemplo de LookML, defines las dimensiones que aparecen en el registro (numberOfYears y place), pero también puedes definir algunas medidas. Las medidas y drill_fields se definen como de costumbre, como si estos datos estuvieran en su propia tabla. La única diferencia real es que declaras id como un primary_key para que los agregados se calculen correctamente.
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]
}
}
Declaraciones de registro
En la vista que contiene los subregistros (en este caso, persons), debes declarar los registros. Se usarán cuando crees las uniones. Puedes ocultar estos campos de LookML con el parámetro hidden porque no los necesitarás cuando explores los datos.
view: persons {
...
dimension: citiesLived {
hidden:yes
}
dimension: phoneNumber {
hidden:yes
}
...
}
Uniones
Los registros anidados en BigQuery son arrays de elementos STRUCT. En lugar de unir con un parámetro sql_on, la relación de unión se incorpora en la tabla. En este caso, puedes usar el parámetro de unión sql: para usar el operador UNNEST. Aparte de esa diferencia, desagrupar un array de elementos STRUCT es exactamente igual que unir una tabla.
En el caso de los registros no repetidos, puedes usar STRUCT. Para convertirlo en un array de elementos STRUCT, colócalo entre corchetes. Si bien esto puede parecer extraño, parece que no hay penalización de rendimiento, lo que mantiene todo limpio y simple.
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
}
}
Combinaciones para arrays sin claves únicas para cada fila
Si bien es mejor tener claves naturales identificables en los datos o claves sustitutivas creadas en el proceso de ETL, esto no siempre es posible. Por ejemplo, podrías encontrar una situación en la que algunos arrays no tienen una clave única relativa para la fila. Aquí es donde WITH OFFSET puede resultar útil en la sintaxis de unión.
Por ejemplo, una columna que representa a una persona puede cargarse varias veces si esta vivió en varias ciudades (Chicago, Denver, San Francisco, etcétera). Puede ser difícil crear una clave primaria en la fila no anidada si no se proporciona una fecha o alguna otra clave natural identificable para distinguir la permanencia de la persona en cada ciudad. Aquí es donde WITH OFFSET puede proporcionar un número de fila relativo (0,1,2,3) para cada fila no anidada. Este enfoque garantiza una clave única en la fila no anidada:
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;;
}
}
Valores repetidos simples
Los datos anidados en BigQuery también pueden ser valores simples, como números enteros o cadenas. Para desanidar arrays de valores repetidos simples, puedes usar un enfoque similar al que se mostró anteriormente, con el operador UNNEST en una unión.
En el siguiente ejemplo, se desanida un array determinado de números enteros, "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} ;;
}
}
El parámetro sql para el array de números enteros, unresolved_skus, se representa como ${TABLE}. Esto hace referencia directamente a la tabla de valores, que luego se desanida en explore.