Las tablas derivadas ofrecen un mundo de posibilidades analíticas avanzadas, pero pueden ser abrumadoras a la hora de abordarlas, implementarlas y solucionar problemas. Esta guía de soluciones contiene los casos de uso más populares de las tablas derivadas en Looker.
En esta página, se incluyen los siguientes ejemplos:
- Cómo crear una tabla a las 3 a.m. todos los días
- Agregar 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 optimizaciones
UNIONdos tablas- Tomar una suma de una suma (dimensionar una medida)
- Tablas agrupadas con reconocimiento total
Recursos de tabla derivados
En estos libros de recetas, se supone que tienes conocimientos básicos de LookML y tablas derivadas. Debes sentirte cómodo creando vistas y editando el archivo del modelo. Si quieres hacer un repaso sobre alguno de estos temas, consulta los siguientes recursos:
- Tablas derivadas
- Términos y conceptos de LookML
- Crea tablas derivadas nativas
- Referencia del parámetro
derived_table - Almacena consultas en caché y vuelve a compilar PDT con grupos de datos
Armar una mesa a las 3 a.m. todos los días
Los datos de este ejemplo 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 resultados de una caché.
Incluir tu grupo de datos en el archivo de modelo te permite volver a utilizarlo 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 debe activar y volver a compilar la tabla derivada.
Para obtener más ejemplos de expresiones de activador, 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 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 adjuntar datos nuevos a una tabla grande
Una PDT incremental es una tabla derivada persistente que Looker compila agregando datos actualizados a la tabla, en lugar de volver a compilarla por completo.
En el siguiente ejemplo, se basa en el ejemplo de la tabla orders para mostrar cómo se compila 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 los datos actualizados y agregarlos al PDT en este ejemplo.
El valor increment_offset se establece en 3 para especificar la cantidad de períodos anteriores (en el nivel de detalle de la clave de incremento) que se vuelven a compilar para tener en cuenta los datos tardíos.
Usa funciones analíticas de SQL
Algunos dialectos de bases de datos admiten funciones de ventana, en especial para crear números de secuencia, claves primarias, totales acumulados y en ejecución, y otros cálculos útiles de varias filas. Después de ejecutar la consulta principal, cualquier declaración derived_column se ejecuta en un pase independiente.
Si el dialecto de tu 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 te refieras a valores, debes usar el nombre de la columna 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 de ventana 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 función Explorar 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 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
Debido a que las PDT se almacenan en tu base de datos, debes optimizarlas con las siguientes estrategias, siempre que sean compatibles con tu dialecto:
Por ejemplo, para agregar persistencia, puedes configurar la 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 PDT para probar optimizaciones
Puedes usar PDT para probar diferentes opciones de indexación, distribución y otras opciones de optimización sin necesidad de una gran asistencia por parte de tus desarrolladores de DBA o ETL.
Considera un caso en el que tienes una tabla, pero quieres probar diferentes índices. Tu código LookML inicial para la vista podría verse de la siguiente manera:
view: customer {
sql_table_name: warehouse.customer ;;
}
Si quieres probar 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 DBA o ETL que agregue los índices a la tabla original.
UNION dos tablas
Puedes ejecutar un operador SQL UNION o UNION ALL en ambas tablas derivadas si tu dialecto 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 sentencia 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 tareas adicionales para quitar las filas duplicadas.
Tomar la suma de una suma (dimensionalizar una medición)
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 no agregados (representados en Looker como dimensiones).
Para agrupar por agregado (para tomar la suma de una suma, por ejemplo), debes “dimensionalizar” una medición. Una forma de hacerlo es usar una tabla derivada, que crea una subconsulta del agregado.
A partir de una exploración, Looker puede generar LookML para toda o la mayor parte de tu tabla derivada. Solo crea una exploración y selecciona todos los campos que deseas incluir en tu tabla derivada. Luego, para generar LookML de tabla nativa (o basada en SQL), sigue estos pasos:
Haz clic en el menú de ajustes de Explorar y selecciona Obtener LookML.
Para ver el código LookML para crear una tabla derivada nativa para 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 Modo de desarrollo, navega a los archivos del proyecto.
Haz clic en el signo + 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.
Establece un nombre significativo para la vista.
De manera opcional, cambia los nombres de las columnas, especifica las columnas derivadas y agrega filtros.
Combinar tablas con reconocimiento agregado
En Looker, es posible que encuentres conjuntos de datos o tablas muy grandes que, para tener un buen rendimiento, requieren tablas de agregación o resúmenes.
Con el reconocimiento agregado de Looker, puedes preconstruir tablas conjuntas con varios niveles de detalle, dimensionalidad y agregación. y puedes informar a Looker cómo usarlas en las exploraciones existentes. 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, reducirá 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 ligera que puede ser la información agregada. Si tienes 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 exploración. A continuación, se muestra el LookML de una tabla conjunta que puedes definir para Explorar:
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 flights Explorar, y Looker usará automáticamente la tabla agregada para responder las consultas. Para obtener una explicación más detallada de la información agregada, consulta el instructivo sobre la información agregada.