Se usó la API de Cloud Translation para traducir esta página.

Libros de recetas de Looker: Aprovecha al máximo las tablas derivadas en Looker

Las tablas derivadas abren un mundo de posibilidades analíticas avanzadas, pero pueden ser difíciles de abordar, implementar y solucionar. Este libro de recetas contiene los casos de uso más populares de las tablas derivadas en Looker.

Esta página contiene los siguientes ejemplos:

Cómo crear una tabla a las 3 a.m. todos los días
Cómo anexar datos nuevos a una tabla grande
Usa funciones analíticas de SQL
Cómo crear columnas derivadas para valores calculados
Estrategias de optimización
Cómo usar los PDT para probar las optimizaciones
UNION dos tablas
Cómo sumar una suma (dimensionalizar una medida)
Tablas de resumen con reconocimiento agregado

Recursos de tablas derivadas

En estos libros de recetas, se supone que tienes una comprensión introductoria de LookML y las tablas derivadas. Debes sentirte cómodo creando vistas y editando el archivo del modelo. Si deseas repasar alguno de estos temas, consulta los siguientes recursos:

Compilación de una tabla a las 3 a.m. todos los días

En este ejemplo, los datos llegan a las 2 a.m. todos los días. Los resultados de una consulta sobre estos datos serán los mismos, ya sea que se ejecute a las 3 a.m. o a las 9 p.m. Por lo tanto, tiene sentido compilar la tabla una vez al día y permitir que los usuarios extraigan los resultados de una caché.

Si incluyes tu grupo de datos en el archivo del modelo, podrás reutilizarlo con varias tablas y Exploraciones. Este grupo de datos contiene un parámetro sql_trigger_value que le indica al grupo de datos cuándo activar y volver a compilar la tabla derivada.

Para obtener más ejemplos de expresiones de activación, consulta la documentación de sql_trigger_value.


## in the model file

datagroup: standard_data_load {
  sql_trigger_value: SELECT FLOOR(((TIMESTAMP_DIFF(CURRENT_TIMESTAMP(),'1970-01-01 00:00:00',SECOND)) - 60*60*3)/(60*60*24)) ;;
  max_cache_age: "24 hours"
}

explore: orders {
…

Agrega el parámetro datagroup_trigger a la definición de derived_table en el archivo de vista y especifica el nombre del grupo de datos que deseas usar. En este ejemplo, el grupo de datos es standard_data_load.


view: orders {
 derived_table: {
  indexes: ["id"]
  datagroup_trigger: standard_data_load
    sql:
      SELECT
        user_id,
        id,
        created_at,
        status
      FROM
        demo_db.orders
      GROUP BY
        user_id ;;
    }

…
}

Cómo agregar datos nuevos a una tabla grande

Una PDT incremental es una tabla derivada persistente que Looker compila agregando datos recientes a la tabla, en lugar de recompilar la tabla en su totalidad.

En el siguiente ejemplo, se basa en el ejemplo de la tabla orders para mostrar cómo se compila la tabla de forma incremental. Los datos de pedidos nuevos llegan todos los días y se pueden agregar a la tabla existente cuando agregas un parámetro increment_key y un parámetro increment_offset.


view: orders {
 derived_table: {
    indexes: ["id"]
    increment_key: "created_at"
    increment_offset: 3
    datagroup_trigger: standard_data_load
    distribution_style: all
    sql:
      SELECT
        user_id,
        id,
        created_at,
        status
      FROM
        demo_db.orders
      GROUP BY
        user_id ;;
    }

  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;  }

…
}

El valor de increment_key se establece en created_at, que es el incremento de tiempo para el que se deben consultar y agregar datos nuevos al PDT en este ejemplo.

El valor de increment_offset se establece en 3 para especificar la cantidad de períodos anteriores (en la granularidad de la clave de incremento) que se vuelven a generar para tener en cuenta los datos que llegan tarde.

Uso de funciones analíticas de SQL

Algunos dialectos de bases de datos admiten funciones de ventana, en especial para crear números de secuencia, claves principales, totales acumulados y en ejecución, y otros cálculos útiles de varias filas. Después de que se ejecuta la consulta principal, todas las declaraciones derived_column se ejecutan en un paso separado.

Si tu dialecto de base de datos admite funciones analíticas, puedes usarlas en tu tabla derivada nativa. Crea un parámetro derived_column con un parámetro sql que contenga tu función analítica. Cuando hagas referencia a valores, debes usar el nombre de la columna tal como se define en tu tabla derivada nativa.

En el siguiente ejemplo, se muestra cómo crear una tabla derivada nativa que incluya las columnas user_id, order_id y created_time. Luego, usarías una columna derivada con una función analítica SQL ROW_NUMBER() para calcular una columna que contenga el número de secuencia del pedido de un cliente.

view: user_order_sequences {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: order_items.user_id
      }
      column: order_id {
        field: order_items.order_id
      }
      column: created_time {
        field: order_items.created_time
      }
      derived_column: user_sequence {
        sql: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_time) ;;
      }
    }
  }
  dimension: order_id {
    hidden: yes
  }
  dimension: user_sequence {
    type: number
  }
}

Cómo crear columnas derivadas para valores calculados

Puedes agregar parámetros derived_column para especificar columnas que no existen en la Exploración del parámetro explore_source. Cada parámetro derived_column tiene un parámetro sql que especifica cómo construir el valor.

Tu cálculo de sql puede usar cualquier columna que hayas especificado con los parámetros column. Las columnas derivadas no pueden incluir funciones de agregación, pero sí pueden incluir cálculos que se pueden realizar en una sola fila de la tabla.

En este ejemplo, se crea una columna average_customer_order, que se calcula a partir de las columnas lifetime_customer_value y lifetime_number_of_orders de la tabla derivada nativa.

view: user_order_facts {
  derived_table: {
    explore_source: order_items {
      column: user_id {
        field: users.id
      }
      column: lifetime_number_of_orders {
        field: order_items.count
      }
      column: lifetime_customer_value {
        field: order_items.total_profit
      }
      derived_column: average_customer_order {
        sql:  lifetime_customer_value / lifetime_number_of_orders ;;
      }
    }
  }

  dimension: user_id {
    hidden: yes
  }
  dimension: lifetime_number_of_orders {
    type: number
  }
  dimension: lifetime_customer_value {
    type: number
  }
  dimension: average_customer_order {
    type: number
  }
}

Estrategias de optimización

Dado que los PDT se almacenan en tu base de datos, debes optimizarlos con las siguientes estrategias, según lo admita tu dialecto:

Por ejemplo, para agregar persistencia, puedes configurar el PDT para que se vuelva a compilar cuando se active el grupo de datos orders_datagroup y, luego, puedes agregar índices en customer_id y first_order, como se muestra a continuación:

view: customer_order_summary {
  derived_table: {
    explore_source: orders {
      ...
    }
    datagroup_trigger: orders_datagroup
    indexes: ["customer_id", "first_order"]
  }
}

Si no agregas un índice (o un equivalente para tu dialecto), Looker te advertirá que debes hacerlo para mejorar el rendimiento de las consultas.

Cómo usar los PDT para probar las optimizaciones

Puedes usar los PDT para probar diferentes opciones de indexación, distribución y otras opciones de optimización sin necesidad de una gran cantidad de asistencia de tus desarrolladores de DBA o ETL.

Considera un caso en el que tienes una tabla, pero quieres probar diferentes índices. Tu LookML inicial para la vista puede verse de la siguiente manera:

view: customer {
  sql_table_name: warehouse.customer ;;
}

Para probar las estrategias de optimización, puedes usar el parámetro indexes para agregar índices a LookML, como se muestra a continuación:

view: customer {
  # sql_table_name: warehouse.customer
  derived_table: {
    sql: SELECT * FROM warehouse.customer ;;
    persist_for: "8 hours"
    indexes: [customer_id, customer_name, salesperson_id]
  }
}

Consulta la vista una vez para generar el PDT. Luego, ejecuta tus consultas de prueba y compara los resultados. Si los resultados son favorables, puedes pedirle a tu equipo de ETL o DBA que agregue los índices a la tabla original.

`UNION` dos tablas

Puedes ejecutar un operador UNION o UNION ALL de SQL en ambas tablas derivadas si tu dialecto de SQL lo admite. Los operadores UNION y UNION ALL combinan los conjuntos de resultados de dos consultas.

En este ejemplo, se muestra cómo se ve una tabla derivada basada en SQL con un UNION:

view: first_and_second_quarter_sales {
  derived_table: {
    sql:
       SELECT * AS sales_records
       FROM sales_records_first_quarter
       UNION
       SELECT * AS sales_records
       FROM sales_records_second_quarter ;;
   }
}

La instrucción UNION en el parámetro sql produce una tabla derivada que combina los resultados de ambas consultas.

La diferencia entre UNION y UNION ALL es que UNION ALL no quita las filas duplicadas. Hay consideraciones de rendimiento que se deben tener en cuenta cuando se usa UNION en lugar de UNION ALL, ya que el servidor de la base de datos debe realizar trabajo adicional para quitar las filas duplicadas.

Cómo sumar una suma (dimensionalizar una medida)

Como regla general en SQL (y, por extensión, en Looker), no puedes agrupar una consulta por los resultados de una función de agregación (representada en Looker como medidas). Solo puedes agrupar por campos sin agregar (representados en Looker como dimensiones).

Para agrupar por un agregado (por ejemplo, para obtener la suma de una suma), debes "dimensionalizar" una métrica. Una forma de hacerlo es usar una tabla derivada, que crea de manera eficaz una subconsulta del agregado.

A partir de una exploración, Looker puede generar LookML para toda o la mayoría de tu tabla derivada. Solo tienes que crear una exploración y seleccionar todos los campos que quieras incluir en tu tabla derivada. Luego, para generar el LookML de la tabla derivada nativa (o basada en SQL), sigue estos pasos:

Haz clic en el menú de ajustes de la Exploración y selecciona Obtener LookML.
Para ver el LookML para crear una tabla derivada nativa para el Explorar, haz clic en la pestaña Tabla derivada.
Copia el código de LookML.

Ahora que copiaste el código de LookML generado, pégalo en un archivo de vista siguiendo estos pasos:

En el modo de desarrollo, navega a los archivos del proyecto.
Haz clic en + en la parte superior de la lista de archivos del proyecto en el IDE de Looker y selecciona Crear vista. Como alternativa, para crear el archivo dentro de la carpeta, haz clic en el menú de una carpeta y selecciona Crear vista.
Configura el nombre de la vista con un nombre significativo.
Opcionalmente, cambia los nombres de las columnas, especifica las columnas derivadas y agrega filtros.

Tablas de resumen con reconocimiento agregado

En Looker, es posible que a menudo te encuentres con conjuntos de datos o tablas muy grandes que, para tener un buen rendimiento, requieren tablas de agregación o resúmenes.

Con el conocimiento de agregados de Looker, puedes crear previamente tablas de agregados en varios niveles de detalle, dimensionalidad y agregación, y puedes informar a Looker cómo usarlas en los Explorar existentes. Luego, las consultas usarán estas tablas de resumen cuando Looker lo considere apropiado, sin ninguna entrada del usuario. Esto reducirá el tamaño de las búsquedas, los tiempos de espera y mejorará la experiencia del usuario.

A continuación, se muestra una implementación muy simple en un modelo de Looker para demostrar lo liviana que puede ser la conciencia de agregación. Dada una tabla hipotética de vuelos en la base de datos con una fila para cada vuelo que se registra a través de la FAA, puedes modelar esta tabla en Looker con su propia vista y Explorar. A continuación, se muestra el código de LookML para una tabla conjunta que puedes definir para la exploración:

  explore: flights {
    aggregate_table: flights_by_week_and_carrier {
      query: {
        dimensions: [carrier, depart_week]
        measures: [cancelled_count, count]
      }

      materialization: {
        sql_trigger_value: SELECT CURRENT-DATE;;
      }
    }
  }

Con esta tabla agregada, un usuario puede consultar el flights Explorar, y Looker usará automáticamente la tabla agregada para responder las consultas. Para obtener una explicación más detallada sobre el conocimiento agregado, consulta el instructivo sobre el conocimiento agregado.

Libros de recetas de Looker: Aprovecha al máximo las tablas derivadas en Looker Organiza tus páginas con colecciones Guarda y categoriza el contenido según tus preferencias.