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 como REPEATED 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ón ARRAY en GoogleSQL cuando consultes datos repetidos.

  • Datos repetidos y anidados (ARRAY de STRUCT)

    • 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:

Resultado de la consulta con datos no anidados

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.

  1. 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`
);

  2. 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.

  3. Crea una segunda tabla con datos idénticos que cree un campo comments mediante un tipo STRUCT para almacenar los datos post_id y creation_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
);

  4. 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.

  5. Borra las tablas stackoverflow y stackoverflow_nested cuando termines de usarlas.