Las tablas derivadas abren un mundo de posibilidades analíticas avanzadas, pero abordarlas, implementarlas y solucionar problemas puede ser abrumador. 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:
- Construir una mesa todos los días a las 3 a.m.
- Agrega datos nuevos a una tabla grande
- Usa funciones analíticas de SQL
- Cómo crear columnas derivadas para valores calculados
- Estrategias de optimización
- Uso de 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 estas guías de cocina, se supone que tienes un conocimiento introductorio de LookML y de las tablas derivadas. Debes poder crear vistas y editar el archivo de modelo. Si quieres repasar 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
Crear una mesa todos los días a las 3 a.m.
En este ejemplo, los datos llegan todos los días a las 2 a.m. 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 crear 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 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 ;;
}
…
}
Agrega 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.
El siguiente ejemplo se basa en el ejemplo de la tabla orders para mostrar cómo la tabla se compila de forma incremental. Todos los días se reciben nuevos datos de orden 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 separado.
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 hagas referencia 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
}
}
Crea 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 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.
Imagina un caso en el que tienes una tabla, pero quieres probar diferentes índices. Es posible que el LookML inicial para la vista se vea 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 la PDT. Luego, ejecuta las consultas de prueba y compara los resultados. Si tus 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 del 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 debes tener en cuenta cuando uses UNION en comparación con UNION ALL, ya que el servidor de la base de datos debe realizar un trabajo adicional para quitar las filas duplicadas.
Tomar la suma de una suma (dimensionalizar una medición)
Como regla general en SQL (y, por extensión, Looker), no puedes agrupar una consulta por los resultados de una función agregada (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 con una tabla derivada, que crea efectivamente una subconsulta del agregado.
Comenzando por una exploración, Looker puede generar LookML para toda tu tabla derivada o la mayor parte de ella. Solo crea una exploración y selecciona 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 Get LookML.
Si quieres ver el LookML a fin de 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 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. De manera alternativa, para crear el archivo dentro de la carpeta, haz clic en el menú de una carpeta y selecciona Crear vista.
Asigna un nombre significativo a 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 tablas o conjuntos de datos 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. Luego, las consultas usarán estas tablas agrupadas 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 ligero que puede ser el reconocimiento agregado. 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 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 del reconocimiento agregado, visita el instructivo sobre el reconocimiento agregado.