Usar campos anidados y repetidos

BigQuery se puede usar con muchos métodos de modelado de datos diferentes y, por lo general, ofrece un alto rendimiento en muchas metodologías de modelado de datos. Para optimizar aún más un modelo de datos, puedes desnormalizar los datos, es decir, añadir columnas de datos a una sola tabla para reducir o eliminar las combinaciones 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 habitual para aumentar el rendimiento de la lectura de los conjuntos de datos relacionales que se han normalizado anteriormente. La forma recomendada de desnormalizar datos en BigQuery es usar campos anidados y repetidos. Se recomienda usar esta estrategia cuando las relaciones son jerárquicas y se suelen consultar juntas, como en las relaciones de elementos superiores y secundarios.

El ahorro de almacenamiento que se consigue al usar datos normalizados tiene menos efecto en los sistemas modernos. El aumento de los costes de almacenamiento merece la pena por las mejoras de rendimiento que se obtienen al usar datos desnormalizados. Las combinaciones requieren coordinación de datos (ancho de banda de comunicación). La desnormalización localiza los datos en slots individuales para que la ejecución se pueda realizar en paralelo.

Para mantener las relaciones al desnormalizar los datos, puedes usar campos anidados y repetidos en lugar de acoplar los datos por completo. Cuando los datos relacionales se aplanan por completo, la comunicación de red (barajado) puede afectar negativamente al rendimiento de las consultas.

Por ejemplo, desnormalizar un esquema de pedidos sin usar campos anidados y repetidos puede requerir que agrupes los datos por un campo como order_id (cuando hay una relación de uno a muchos). Debido a la aleatorización que implica, agrupar los datos es menos eficaz que desnormalizarlos mediante campos anidados y repetidos.

En algunas circunstancias, desnormalizar los datos y usar campos anidados y repetidos no aumenta el rendimiento. Por ejemplo, los esquemas de estrella suelen ser esquemas optimizados para analíticas y, por lo tanto, el rendimiento no puede variar significativamente si intentas desnormalizar más.

Usar campos anidados y repetidos

BigQuery no requiere una desnormalización completamente plana. Puedes usar campos anidados y repetidos para mantener las relaciones.

  • Anidado de datos (STRUCT)

    • Los datos anidados te permiten representar entidades externas de forma insertada.
    • Para consultar datos anidados, se usa la sintaxis de "punto" para hacer referencia a los campos hoja, que es similar a la sintaxis que usa una combinación.
    • Los datos anidados se representan como un tipo STRUCT en GoogleSQL.

  • Datos repetidos (ARRAY)

    • Si creas un campo de tipo RECORD con el modo REPEATED, puedes conservar una relación de uno a muchos en línea (siempre que la relación no sea de alta cardinalidad).
    • Si los datos se repiten, no es necesario aleatorizarlos.
    • Los datos repetidos se representan como un ARRAY. Puedes usar una ARRAYfunción de GoogleSQL cuando consultes los datos repetidos.

  • Datos anidados y repetidos (ARRAY de STRUCTs)

    • La anidación y la repetición se complementan entre sí.
    • Por ejemplo, en una tabla de registros de transacciones, puede incluir una matriz de STRUCTs de partidas.

Para obtener más información, consulta Especificar columnas anidadas y repetidas en esquemas de tablas.

Para obtener más información sobre la desnormalización de datos, consulta Desnormalización.

Ejemplo

Supongamos que tiene una tabla Orders con una fila por cada línea de pedido vendida:

Order_Id Item_Name
001 A1
001 B1
002 A1
002 C1

Si quisiera analizar los datos de esta tabla, tendría que usar una cláusula GROUP BY, como la siguiente:

SELECT COUNT (Item_Name)
FROM Orders
GROUP BY Order_Id;

La cláusula GROUP BY implica una sobrecarga de cálculo adicional, pero se puede evitar anidando datos repetidos. Puedes evitar usar una cláusula GROUP BY creando una tabla con un pedido por fila, donde las líneas de pedido se encuentren en un campo anidado:

Order_Id Item_Name
001 A1

B1
002 A1

C1

En BigQuery, normalmente se especifica un esquema anidado como un ARRAY de objetos STRUCT. Puedes usar el operador UNNEST para desanidar 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:

Resultados de consultas con datos sin anidar

Si estos datos no estuvieran anidados, podría haber varias filas por cada pedido, una por cada artículo vendido en ese pedido, lo que daría lugar a una tabla grande y a una operación GROUP BY costosa.

Ejercicio

Para ver la diferencia de rendimiento entre las consultas que usan campos anidados y las que no, siga los pasos que se indican en esta sección.

  1. Crea una tabla basada en el bigquery-public-data.stackoverflow.comments conjunto de datos público:

    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 para ver el comentario más antiguo de 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 con 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 para ver el comentario más antiguo de 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. Elimina las tablas stackoverflow y stackoverflow_nested cuando hayas terminado de usarlas.