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 del trabajo con datos anidados de BigQuery en Looker.
Las ventajas de los registros anidados
Existen algunas ventajas de usar registros anidados 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 escanear 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 de colocación.
- Las estructuras anidadas evitan la repetición de datos que tendrían que repetirse en una tabla desnormalizada ancha. En otras palabras, para una persona que ha vivido en cinco ciudades, una tabla ancha 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. En el esquema, se muestra que los valores de 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]
}
}
Cada componente para trabajar con datos anidados en LookML se analiza con más detalle en las siguientes secciones:
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, tú defines las dimensiones que aparecen en el registro (numberOfYears y place), pero también puedes definir algunas medidas. Las mediciones 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 registros
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 poder usar el operador UNNEST. Aparte de esa diferencia, desanidar 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 se penaliza el rendimiento, y esto hace que todo sea más claro 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
Aunque es mejor tener claves naturales identificables en los datos o claves sustitutas creadas en el proceso 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 podría cargarse varias veces si esta vivió en distintas ciudades (Chicago, Denver, San Francisco, etc. Puede resultar 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 antigüedad 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.