Una tabla derivada es una consulta cuyos resultados se usan como si fuera una tabla física en la base de datos. Una tabla derivada nativa se basa en una consulta que defines con términos de LookML. Esto es diferente de una tabla derivada basada en SQL, que se basa en una consulta que defines con términos de SQL. En comparación con las tablas derivadas basadas en SQL, las tablas derivadas nativas son mucho más fáciles de leer y comprender a medida que modela sus datos. Consulta la sección Tablas derivadas nativas y tablas derivadas basadas en SQL de la página de documentación Tablas derivadas en Looker para obtener más información.
Las tablas derivadas nativas y basadas en SQL se definen en LookML con el parámetro derived_table a nivel de la vista. Sin embargo, con las tablas derivadas nativas, no es necesario crear una consulta de SQL. En su lugar, usa el parámetro explore_source para especificar la exploración en la que se basará la tabla derivada, las columnas deseadas y otras características deseadas.
También puede hacer que Looker cree la tabla derivada LookML a partir de una consulta de SQL Runner, como se describe en la página de documentación Cómo usar SQL Runner para crear tablas derivadas.
Usa Explorar para comenzar a definir tus tablas derivadas nativas
A partir de Explorar, Looker puede generar LookML para toda su tabla derivada o la mayor parte de ella. Solo cree una exploración y seleccione todos los campos que desea incluir en su tabla derivada. Luego, para generar la tabla nativa de deriva derivada LookML:
Haga clic en el menú de ajustes de Explorar y seleccione Obtener LookML.
Haga clic en la pestaña Tabla derivada a fin de ver el LookML para crear una tabla derivada nativa para Explorar.
Copie el LookML.
Ahora que copió el LookML generado, péguelo en un archivo de vista:
En modo de desarrollo, navega a los archivos de tu proyecto.
Haz clic en + en la parte superior de la lista de archivos del proyecto del IDE de Looker y selecciona Create View. También puedes hacer clic en el menú de una carpeta y seleccionar Crear vista desde el menú para crear el archivo dentro de la carpeta.
Establece el nombre de la vista en algo significativo.
De forma opcional, cambia los nombres de las columnas, especifica las columnas derivadas y agrega filtros.
Cuando usas una medida de
type: counten una exploración, la visualización etiqueta los valores resultantes con el nombre de la vista en lugar de la palabra Recuento. Para evitar confusiones, te recomendamos que pluralices el nombre de tu vista, selecciones Show Full Field Name en la sección Series de la configuración de visualización o utilices unview_labelcon una versión en plural de tu nombre de vista.
Define una tabla nativa derivada en LookML
Ya sea que uses tablas derivadas declaradas en SQL o LookML nativo, el resultado de una consulta derived_table's es una tabla con un conjunto de columnas. Cuando la tabla derivada se expresa en SQL, la consulta de SQL implica los nombres de las columnas de resultado. Por ejemplo, la siguiente consulta de SQL tendrá las columnas de resultado user_id, lifetime_number_of_orders y lifetime_customer_value:
SELECT
user_id
, COUNT(DISTINCT order_id) as lifetime_number_of_orders
, SUM(sale_price) as lifetime_customer_value
FROM order_items
GROUP BY 1
En Looker, una consulta se basa en Explorar, incluye campos de medición y dimensión, agrega los filtros aplicables y también puede especificar un orden de clasificación. Una tabla derivada nativa contiene todos estos elementos, además de los nombres de salida para las columnas.
En el siguiente ejemplo simple, se produce una tabla derivada con tres columnas: user_id, lifetime_customer_value y lifetime_number_of_orders. No es necesario que escribas la consulta de forma manual en SQL. En su lugar, Looker crea la consulta mediante el order_items de Explorar especificado y algunos de los campos de Explorar (order_items.user_id, order_items.total_revenue y order_items.order_count).
view: user_order_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
}
Usa declaraciones include para habilitar los campos de referencia
En el archivo de vista de la tabla derivada nativa, debes usar el parámetro explore_source para apuntar a Explorar y definir las columnas y otras características deseadas para la tabla derivada nativa. Debido a que apuntas a Explorar desde el archivo de vista de tabla derivada nativa, también debes incluir el archivo que contiene la definición de Explorar. Por lo general, las exploraciones se definen dentro de un archivo de modelo, pero en el caso de las tablas derivadas nativas, es más limpio crear un archivo separado para Explorar con la extensión de archivo .explore.lkml, como se describe en la documentación sobre Cómo crear archivos de Explorar. De esa manera, en su archivo de vista de tabla derivada nativa, puede incluir un solo archivo Explorar y no todo el archivo del modelo. En cuyo caso:
- El archivo de vista de la tabla derivada nativa debe incluir el archivo de Explorar. Por ejemplo:
include: "/explores/order_items.explore.lkml" - El archivo Explorar debe incluir los archivos de vista que necesita. Por ejemplo:
include: "/views/order_items.view.lkml"
include: "/views/users.view.lkml" - El modelo debe incluir el archivo Explorar. Por ejemplo:
include: "/explores/order_items.explore.lkml"
Los archivos de Explorar escucharán la conexión del modelo en el que se incluyen. Considera este hecho cuando incluyas archivos de Explorar en modelos configurados con una conexión diferente del modelo principal del archivo Explorar. Si el esquema de la conexión del modelo incluido difiere del esquema de la conexión del modelo principal, puede provocar errores de consulta.
Define columnas de tablas derivadas nativas
Como se muestra en el ejemplo anterior, debes usar column para especificar las columnas de resultado de la tabla derivada.
Especifica los nombres de las columnas
Para la columna user_id, el nombre de la columna coincide con el nombre del campo especificado en la exploración original.
Con frecuencia, querrás usar un nombre de columna diferente en la tabla de salida que el nombre de los campos en el Explorar original. En el ejemplo anterior, producimos un cálculo del valor del ciclo de vida del cliente por medio de la exploración de order_items. En la tabla de resultados, total_revenue es en realidad un lifetime_customer_value del cliente.
La declaración column permite declarar un nombre de salida diferente del campo de entrada. Por ejemplo, en el siguiente código, se indica "crear una columna de resultado llamada lifetime_value del campo order_items.total_revenue":
column: lifetime_value {
field: order_items.total_revenue
}
Nombres de columnas implícitas
Si el parámetro field no se incluye en la declaración de una columna, se supone que es <explore_name>.<field_name>. Por ejemplo, si especificaste explore_source: order_items,
column: user_id {
field: order_items.user_id
}
es equivalente a
column: user_id {}
Crea columnas derivadas para valores calculados
Puedes agregar parámetros derived_column para especificar las columnas que no existen en el 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 de 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 el siguiente ejemplo, se produce la misma tabla derivada que en el ejemplo anterior, excepto que se agrega una columna average_customer_order calculada, 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: order_items.user_id
}
column: lifetime_number_of_orders {
field: order_items.order_count
}
column: lifetime_customer_value {
field: order_items.total_revenue
}
derived_column: average_customer_order {
sql: lifetime_customer_value / lifetime_number_of_orders ;;
}
}
}
# Define the view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: lifetime_number_of_orders {
type: number
}
dimension: lifetime_customer_value {
type: number
}
dimension: average_customer_order {
type: number
}
}
Usa funciones de ventana de SQL
Algunos dialectos de base de datos admiten funciones analíticas, especialmente para crear números de secuencia, claves primarias, totales en ejecución y acumulativos, y otros cálculos útiles de varias filas. Después de ejecutar la consulta principal, las declaraciones derived_column se ejecutan en un pase independiente.
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 la función analítica deseada. Cuando hagas referencia a valores, debes usar el nombre de columna como se define en tu tabla derivada nativa.
En el siguiente ejemplo, se crea una tabla derivada nativa que incluye las columnas user_id, order_id y created_time. Luego, mediante una columna derivada con una función de ventana SQL ROW_NUMBER(), calcula una columna que contiene 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
}
}
Agrega filtros a una tabla derivada nativa
Supongamos que queremos crear una tabla derivada del valor de un cliente durante los últimos 90 días. Deseamos los mismos cálculos que hicimos antes, pero solo queremos incluir compras de los últimos 90 días.
Solo agregamos un filtro a la derived_table que filtra las transacciones en los últimos 90 días. El parámetro filters de una tabla derivada usa la misma sintaxis que usas para crear una medida filtrada.
view: user_90_day_facts {
derived_table: {
explore_source: order_items {
column: user_id {
field: order_items.user_id
}
column: number_of_orders_90_day {
field: order_items.order_count
}
column: customer_value_90_day {
field: order_items.total_revenue
}
filters: [order_items.created_date: "90 days"]
}
}
# Add define view's fields as desired
dimension: user_id {
hidden: yes
}
dimension: number_of_orders_90_day {
type: number
}
dimension: customer_value_90_day {
type: number
}
}
Se agregarán filtros a la cláusula WHERE cuando Looker escriba el SQL para la tabla derivada.
Además, puedes usar el subparámetro dev_filters de explore_source con una tabla derivada nativa. El parámetro dev_filters te permite especificar filtros que Looker aplica solo a las versiones de desarrollo de la tabla derivada, lo que significa que puedes compilar versiones más pequeñas y filtradas de la tabla para iterar y probar sin esperar a que la tabla completa se compile después de cada cambio.
El parámetro dev_filters actúa junto con el parámetro filters para que todos los filtros se apliquen a la versión de desarrollo de la tabla. Si dev_filters y filters especifican filtros para la misma columna, dev_filters tiene prioridad para la versión de desarrollo de la tabla.
Consulta Trabaja más rápido en modo de desarrollo para obtener más información.
Usa filtros de plantilla
Puedes usar bind_filters para incluir filtros con plantilla:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
Básicamente, es lo mismo que usar el siguiente código en un bloque sql:
{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}
to_field es el campo al que se aplica el filtro. to_field debe ser un campo del explore_source subyacente.
from_field especifica el campo desde el que se obtendrá el filtro, si hay un filtro en el entorno de ejecución.
En el ejemplo de bind_filters anterior, Looker tomará cualquier filtro aplicado al campo filtered_lookml_dt.filter_date y aplicará el filtro al campo users.created_date.
También puedes usar el subparámetro bind_all_filters de explore_source para pasar todos los filtros del entorno de ejecución de una exploración a una subconsulta de tabla derivada nativa. Consulta la página de documentación del parámetro explore_source para obtener más información.
Ordenar y limitar tablas derivadas nativas
También puedes ordenar y limitar las tablas derivadas, si lo deseas:
sorts: [order_items.count: desc]
limit: 10
Recuerda que Explorar puede mostrar las filas en un orden diferente al orden subyacente.
Convierte tablas nativas derivadas en diferentes zonas horarias
Puedes especificar la zona horaria para la tabla derivada nativa con el subparámetro timezone:
timezone: "America/Los_Angeles"
Cuando usas el subparámetro timezone, todos los datos basados en el tiempo de la tabla derivada nativa se convertirán a la zona horaria que especifiques. Consulta la página de documentación de valores timezone para obtener una lista de las zonas horarias compatibles.
Si no especifica una zona horaria en su definición de tabla derivada nativa, la tabla derivada nativa no realizará ninguna conversión de zona horaria en datos basados en hora, sino datos predeterminados según su zona horaria de base de datos.
Si la tabla derivada nativa no es persistente, puedes establecer el valor de zona horaria en "query_timezone" para usar automáticamente la zona horaria de la consulta en ejecución.