Usa campos anidados y repetidos
BigQuery se puede usar con muchos métodos diferentes de modelado de datos y, por lo general, proporciona un alto rendimiento en muchas metodologías de modelos de datos. A fin de ajustar aún más un modelo de datos para el rendimiento, podrías considerar la desnormalización de datos, lo que significa agregar columnas de datos a una sola tabla con el fin de reducir o quitar las uniones de tablas.
Práctica recomendada: Usa campos anidados y repetidos para desnormalizar el almacenamiento de datos y aumentar el rendimiento de las consultas.
La desnormalización es una estrategia común para aumentar el rendimiento de la lectura en conjuntos de datos relacionales que se normalizaron con anterioridad. A fin de desnormalizar los datos en BigQuery, se recomienda usar campos anidados y repetidos. Es mejor usar esta estrategia cuando las relaciones son jerárquicas y, con frecuencia, se consultan juntas como relaciones superiores y secundarias.
El ahorro de almacenamiento de datos normalizados tiene menos efecto en los sistemas modernos. Los aumentos en los costos de almacenamiento se compensan con lo que se gana en el rendimiento del uso de datos desnormalizados. Las uniones requieren coordinación de datos (ancho de banda de comunicación). La desnormalización localiza los datos en ranuras individuales para que la ejecución se pueda realizar en paralelo.
Para mantener las relaciones al desnormalizar tus datos, puedes usar campos anidados y repetidos, en lugar de compactar por completo tus datos. Cuando los datos relacionales se compactan por completo, la comunicación de red (redistribución) puede tener un impacto negativo en el rendimiento.
Por ejemplo, la desnormalización de un esquema de pedidos sin usar campos repetidos y anidados podría requerir la agrupación por campos como order_id
(cuando hay una relación de uno a varios). Debido a la redistribución involucrada, agrupar los datos es menos eficaz que desnormalizarlos mediante campos repetidos y anidados.
En algunas circunstancias, desnormalizar tus datos y usar campos anidados y repetidos no genera un mayor rendimiento. Por ejemplo, los esquemas en estrella suelen ser esquemas optimizados para las estadísticas y, como resultado, el rendimiento puede no ser muy diferente si intentas desnormalizar más.
Usa campos repetidos y anidados
BigQuery no requiere una desnormalización compacta por completo. Puedes usar campos repetidos y anidados para conservar relaciones.
Datos anidados (
STRUCT
)- Anidar datos te permite representar entidades externas intercaladas.
- La consulta de datos anidados usa la sintaxis de “punto” para hacer referencia a los campos de hoja, que es similar a la sintaxis que usa una unión.
- Los datos anidados se representan como un tipo de
STRUCT
en GoogleSQL.
Datos repetidos (
ARRAY
)- Crear un campo de tipo
RECORD
con el modo configurado comoREPEATED
te permite conservar una relación de uno a varios intercalada (siempre que la relación no sea de alta cardinalidad). - Con datos repetidos, la redistribución no es necesaria.
- Los datos repetidos se representan como un
ARRAY
. Puedes usar una funciónARRAY
en GoogleSQL cuando consultes datos repetidos.
- Crear un campo de tipo
Datos repetidos y anidados (
ARRAY
deSTRUCT
)- La anidación y la repetición se complementan.
- Por ejemplo, en una tabla de registros de transacciones, podrías incluir un arreglo de
STRUCT
de elementos de una sola línea.
Para obtener más información, consulta Especifica columnas anidadas y repetidas en esquemas de tablas.
Para obtener más información sobre cómo desnormalizar los datos, consulta Desnormalización.
Ejemplo
Considera una tabla Orders
con una fila para cada elemento de una sola línea vendido:
Order_Id | Item_Name |
---|---|
001 | A1 |
001 | B1 |
002 | A1 |
002 | C1 |
Si deseas analizar los datos de esta tabla, debes usar una cláusula GROUP BY
, similar a la siguiente:
SELECT COUNT (Item_Name) FROM Orders GROUP BY Order_Id;
La cláusula GROUP BY
implica una sobrecarga de procesamiento adicional, pero esto se puede evitar mediante la anidación de los datos repetidos. Para evitar el uso de una cláusula GROUP BY
, crea una tabla con un pedido por fila, en la que los elementos de una sola línea del pedido estén en un campo anidado:
Order_Id | Item_Name |
---|---|
001 |
A1 B1 |
002 |
A1 C1 |
En BigQuery, por lo general, debes especificar un esquema anidado como una ARRAY
de objetos STRUCT
. Usa el operador UNNEST
para compactar los datos anidados, como se muestra en la siguiente consulta:
SELECT * FROM UNNEST( [ STRUCT('001' AS Order_Id, ['A1', 'B1'] AS Item_Name), STRUCT('002' AS Order_Id, ['A1', 'C1'] AS Item_Name) ] );
Esta consulta genera resultados similares a los siguientes:
Si estos datos no se anidaron, es posible que tengas varias filas para cada pedido, una por cada artículo vendido en ese orden, lo que daría como resultado una tabla grande y una operación GROUP BY
costosa.
Ejercicio
Puedes ver la diferencia de rendimiento en las consultas que usan campos anidados en comparación con las que no lo hacen, si sigues los pasos de esta sección.
Crea una tabla basada en el conjunto de datos públicos
bigquery-public-data.stackoverflow.comments
:CREATE OR REPLACE TABLE `PROJECT.DATASET.stackoverflow` AS ( SELECT user_id, post_id, creation_date FROM `bigquery-public-data.stackoverflow.comments` );
Con la tabla
stackoverflow
, ejecuta la siguiente consulta a fin de ver el comentario más antiguo para cada usuario:SELECT user_id, ARRAY_AGG(STRUCT(post_id, creation_date AS earliest_comment) ORDER BY creation_date ASC LIMIT 1)[OFFSET(0)].* FROM `PROJECT.DATASET.stackoverflow` GROUP BY user_id ORDER BY user_id ASC;
Esta consulta tarda unos 25 segundos en ejecutarse y procesa 1.88 GB de datos.
Crea una segunda tabla con datos idénticos que cree un campo
comments
mediante un tipoSTRUCT
para almacenar los datospost_id
ycreation_date
, en lugar de dos campos individuales:CREATE OR REPLACE TABLE `PROJECT.DATASET.stackoverflow_nested` AS ( SELECT user_id, ARRAY_AGG(STRUCT(post_id, creation_date) ORDER BY creation_date ASC) AS comments FROM `bigquery-public-data.stackoverflow.comments` GROUP BY user_id );
Con la tabla
stackoverflow_nested
, ejecuta la siguiente consulta a fin de ver el comentario más antiguo para cada usuario:SELECT user_id, (SELECT AS STRUCT post_id, creation_date as earliest_comment FROM UNNEST(comments) ORDER BY creation_date ASC LIMIT 1).* FROM `PROJECT.DATASET.stackoverflow_nested` ORDER BY user_id ASC;
Esta consulta tarda unos 10 segundos en ejecutarse y procesa 1.28 GB de datos.
Borra las tablas
stackoverflow
ystackoverflow_nested
cuando termines de usarlas.