BigQuery es compatible con los registros anidados en las tablas. Los registros anidados pueden ser un registro único 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 uniones. 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 generan gastos adicionales por 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 conjunta.
- 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 ha vivido en cinco ciudades, una tabla amplia desnormalizada contendrá toda su información en cinco filas (una para cada una de las ciudades en las que ha vivido). En una estructura anidada, la información repetida solo toma una fila, ya que el array de cinco ciudades puede estar contenido en una sola fila y desecharse cuando sea necesario.
Trabaja 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 mode de cada columna. El esquema muestra que los valores en la columna citiesLived están repetidos, lo que indica que algunos usuarios pudieron haber vivido en varias ciudades:
El siguiente ejemplo es el LookML de 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 es idéntica a la exploración escrita con tablas no anidadas.
Nota: Si bien todos los componentes (vistas y Exploración) están escritos en un bloque de código en el siguiente ejemplo, se recomienda colocar las vistas en archivos de vistas individuales y colocar las especificaciones connection: y Exploraciones en el archivo del 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 primary_key para que los agregados se calculen de forma correcta.
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 declararlos. Estas 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 realizar la unión con un parámetro sql_on, la relación de unión se compila 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 registros no repetidos, simplemente puedes usar STRUCT; Puedes convertirlo en un array de elementos STRUCT si lo colocas entre corchetes. Si bien esto puede parecer extraño, no parece haber una 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
}
}
Uniones 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 tengan 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ó antes, 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.