Las tablas derivadas ofrecen un mundo de posibilidades analíticas avanzadas, pero pueden ser abrumadoras a la hora de abordarlas, implementarlas y solucionar problemas. 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
- Agregar datos nuevos a una tabla grande
- Cómo usar 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- Suma de una suma (dimensionalización de una métrica)
- Tablas de resumen con conocimiento agregado
Recursos de tablas derivadas
En estos libros de recetas, se supone que tienes conocimientos básicos de LookML y tablas derivadas. Debes sentirte a gusto 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
- Cómo crear tablas derivadas nativas
- Referencia del parámetro
derived_table - Almacenamiento en caché de consultas y compilación de PDTs con grupos de datos
Cómo crear una tabla 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é.
Si incluyes tu grupo de datos en el archivo del modelo, podrás reutilizarlo con varias tablas y Explorar. Este grupo de datos contiene un parámetro sql_trigger_value que le indica cuándo 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 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 adjuntar datos nuevos a una tabla grande
Una PDT incremental es una tabla derivada persistente que Looker compila agregando datos nuevos 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 de 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 que llegan tarde.
Usa funciones de ventana 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 que se ejecuta la consulta principal, las declaraciones derived_column se ejecutan 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 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 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.
El 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í 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 en 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 indexaciones, distribuciones y otras opciones de optimización sin necesidad de una gran cantidad de asistencia de tus DBA o desarrolladores de 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 ;;
}
Para 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 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 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 base de datos debe realizar tareas adicionales para quitar las filas duplicadas.
Cómo sumar una suma (dimensionalizar una métrica)
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 un agregado (por ejemplo, para sumar una suma), debes "dimensionalizar" una medida. 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 tienes que crear una exploración y seleccionar todos los campos que deseas incluir en tu tabla derivada. Luego, para generar el modelo de LookML de tabla derivada 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 el Modo de desarrollo, navega a los archivos del proyecto.
Haz clic en el + en la parte superior de la lista de archivos del proyecto en el IDE de Looker y selecciona Create View. 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.
Tablas de resumen 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 conocimiento agregado de Looker, puedes preconstruir tablas agregadas en varios niveles de detalle, dimensionalidad y agregación, y puedes informarle a Looker cómo usarlas en 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 la consulta, 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 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 Explorar. A continuación, se muestra el código de LookML de 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 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.