Las tablas derivadas abren un mundo de posibilidades analíticas avanzadas, pero el enfoque, las implementaciones y la solución de problemas pueden ser abrumadores. 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:
- Armar una mesa todos los días a las 3 a.m.
- Cómo 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 PDT para probar optimizaciones
UNIONdos tablas- Cómo calcular una suma de una suma (dimensionar una medida)
- Tablas de datos integrados con reconocimiento agregado
Recursos de tabla derivados
En estas guías de recetas, se supone que tienes una comprensión introductoria de LookML y de las tablas derivadas. Debes sentirte cómodo creando vistas y editando el archivo de modelo. Si deseas repasar 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 las PDT con grupos de datos
Crea una mesa todos los días a las 3 a.m.
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 crear la tabla una vez al día y permitir que los usuarios extraigan los resultados de una caché.
Incluir tu grupo de datos en el archivo del 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 activar y volver a compilar la tabla derivada.
Para ver 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 ;;
}
…
}
Agrega datos nuevos a una tabla grande
Una PDT incremental es una tabla derivada persistente que Looker crea agregando datos nuevos a la tabla, en lugar de volver a compilarla por completo.
El siguiente ejemplo se basa en el de la tabla orders para mostrar cómo la tabla se compila de forma incremental. Todos los días ingresan nuevos datos de pedidos 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 durante el cual se deben consultar y agregar datos nuevos a la PDT en este ejemplo.
El valor increment_offset se establece en 3 para especificar la cantidad de períodos anteriores (con el nivel de detalle de la clave de incremento) que se vuelven a compilar para tener en cuenta los datos tardíos.
Usar funciones analíticas de SQL
Algunos dialectos de las bases de datos admiten funciones analíticas, en especial para crear números de secuencia, claves primarias, totales acumulativos y en ejecución, y otros cálculos útiles de varias filas. Después de que se ejecuta la consulta principal, cualquier declaración de 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 la función analítica. Cuando hagas referencia a los 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, según lo admita tu dialecto:
Por ejemplo, para agregar persistencia, puedes configurar la PDT para que vuelva a compilar cuando el grupo de datos orders_datagroup se active y, luego, podrías agregar índices tanto en customer_id como en 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 las PDT para probar diferentes indexación, distribuciones y otras opciones de optimización sin necesidad de contar con mucha asistencia por parte de los desarrolladores de DBA o ETL.
Considera un caso en el que tienes una tabla, pero quieres probar índices diferentes. Tu LookML inicial para la vista puede tener el siguiente aspecto:
view: customer {
sql_table_name: warehouse.customer ;;
}
Si quieres 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 la PDT. Luego, ejecuta las consultas de prueba y compara los resultados. Si los resultados son favorables, puedes pedirle al 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 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 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 un trabajo adicional para quitar las filas duplicadas.
Tomar una suma de una suma (dimensionar 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 no agregados (representados en Looker como dimensiones).
Para agrupar por una agregación (para tomar la suma de una suma, por ejemplo), debes "dimensionar" una medida. Una forma de hacerlo es usar una tabla derivada, que crea de forma efectiva una subconsulta del agregado.
Comenzando con una función Explorar, Looker puede generar LookML para toda o la mayoría de tus tablas derivadas. Solo crea una exploración y selecciona todos los campos que deseas incluir en tu tabla derivada. Luego, para generar la tabla derivada nativa (o basada en SQL) de LookML, sigue estos pasos:
Haz clic en el menú de ajustes de Explorar y selecciona Obtener LookML.
Si deseas ver LookML para crear una tabla derivada nativa para Explorar, haz clic en la pestaña Tabla derivada.
Copia LookML.
Ahora que copiaste el LookML generado, pégalo en un archivo de vista siguiendo estos pasos:
En Modo de desarrollo, navega a tus archivos de 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.
Establece un nombre significativo para el nombre de la vista.
De manera opcional, cambia los nombres de las columnas, especifica las columnas derivadas y agrega filtros.
Agrupa 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 de datos integrados.
Gracias al reconocimiento agregado de Looker, puedes construir previamente las tablas conjuntas con varios niveles de detalle, dimensionalidad y agregación; y también puedes informar a Looker cómo usarlas en las exploraciones existentes. Luego, las consultas utilizarán estas tablas de datos integrados cuando Looker 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 ligero que puede ser el reconocimiento agregado. Dada una tabla de vuelos hipotéticos 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 LookML para una tabla conjunta que puedes definir para la función 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 conjunta, un usuario puede consultar la exploración de flights, y Looker usará automáticamente la tabla conjunta para responder consultas. Para obtener una explicación más detallada sobre este tema, visita el Instructivo de reconocimiento agregado.