Una tabla derivada es una consulta cuyos resultados se usan como si la tabla derivada 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 modelas tus datos. Para obtener más información, consulta la sección Tablas derivadas nativas y tablas derivadas basadas en SQL de la página de documentación Tablas derivadas en Looker.
Tanto las tablas derivadas nativas como las 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 que crees una consulta en SQL. En cambio, usas el parámetro explore_source
para especificar el Explore en el que se basará la tabla derivada, las columnas deseadas y otras características deseadas.
También puedes hacer que Looker cree el LookML de la tabla derivada 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.
Cómo usar Explorar para comenzar a definir tus tablas derivadas nativas
A partir de una exploración, Looker puede generar LookML para toda o la mayoría de tu tabla derivada. Solo crea una exploración y selecciona todos los campos que quieras incluir en tu tabla derivada. Luego, para generar el LookML de la tabla derivada nativa, sigue estos pasos:
Selecciona el menú de ajustes Explore Actions y, luego, Get LookML.
Haz clic en la pestaña Tabla derivada para ver el LookML para crear una tabla derivada nativa para el Explorar.
Copia el código de LookML.
Ahora que copiaste el código de LookML generado, pégalo en un archivo de vista:
En el modo de desarrollo, navega a los archivos del proyecto.
Haz clic en el botón + en la parte superior de la lista de archivos del proyecto en el IDE de Looker y selecciona Create View. Como alternativa, puedes hacer clic en el menú de una carpeta y seleccionar Crear vista en el menú para crear el archivo dentro de la carpeta.
Configura el nombre de la vista con un nombre significativo.
Opcionalmente, cambia los nombres de las columnas, especifica las columnas derivadas y agrega filtros.
Cuando usas una medida de
type: count
en Explorar, la visualización etiqueta los valores resultantes con el nombre de la vista en lugar de la palabra Recuento. Para evitar confusiones, usa el plural en el nombre de la vista. Para cambiar el nombre de la vista, selecciona Mostrar nombre completo del campo en Series en la configuración de la visualización o usa el parámetroview_label
con una versión pluralizada del nombre de la vista.
Cómo definir una tabla derivada nativa en LookML
Ya sea que uses tablas derivadas declaradas en SQL o LookML nativo, el resultado de una consulta de derived_table
es una tabla con un conjunto de columnas. Cuando la tabla derivada se expresa en SQL, los nombres de las columnas de salida se infieren a partir de la consulta en SQL. Por ejemplo, la siguiente consulta en SQL tendrá las columnas de salida 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 una exploración, incluye campos de dimensión y medició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 de 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 manualmente en SQL. En cambio, Looker la crea por ti con el Explorar order_items
especificado y algunos de los campos de ese 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 instrucciones include
para habilitar la referencia a campos
En el archivo de vista de la tabla derivada nativa, usas el parámetro explore_source
para apuntar a un Explore y definir las columnas y otras características de la tabla derivada nativa.
En el archivo de vista de la tabla derivada nativa, no es necesario que uses el parámetro include
para hacer referencia al archivo que contiene la definición de la Exploración. Si no tienes la instrucción include
, el IDE de Looker no autosuggest nombres de campos ni verificará tus referencias de campos a medida que compiles la tabla derivada nativa. En su lugar, puedes usar el Validador de LookML para verificar los campos a los que haces referencia en tu tabla derivada nativa.
Sin embargo, si deseas habilitar la función de autocompletar y la verificación inmediata de campos en el IDE de Looker, o si tienes un proyecto de LookML complejo que tiene varios Explorar con el mismo nombre o potencial de referencias circulares, puedes usar el parámetro include
para apuntar a la ubicación de la definición de Explorar.
Los Explorar suelen definirse dentro de un archivo de modelo, pero, en el caso de las tablas derivadas nativas, es mejor crear un archivo independiente para el Explorar. Los archivos de exploración de LookML tienen la extensión .explore.lkml
, como se describe en la documentación para Creación de archivos de exploración. De esa manera, en tu archivo de vista de tabla derivada nativa, puedes incluir un solo archivo de Explorar y no el archivo de modelo completo.
Si deseas crear un archivo de Explore independiente y usar el parámetro include
para que apunte al archivo de Explore en el archivo de vista de tu tabla derivada nativa, asegúrate de que tus archivos de LookML cumplan con los siguientes requisitos:
- El archivo de vista de la tabla derivada nativa debe incluir el archivo del Explorar. Por ejemplo:
include: "/explores/order_items.explore.lkml"
- El archivo de Explore 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 de la exploración. Por ejemplo:
include: "/explores/order_items.explore.lkml"
Cómo definir columnas de tablas derivadas nativas
Como se muestra en el ejemplo anterior, usas column
para especificar las columnas de salida de la tabla derivada.
Cómo especificar los nombres de las columnas
En la columna user_id
, el nombre de la columna coincide con el nombre del campo especificado en el Explorar original.
Con frecuencia, querrás que la tabla de salida tenga un nombre de columna diferente del nombre de los campos en el Explorar original. En el ejemplo anterior, se produjo un cálculo del valor del ciclo de vida del cliente por usuario con la exploración order_items
. En la tabla de salida, total_revenue
es, en realidad, el lifetime_customer_value
de un cliente.
La declaración column
admite la declaración de un nombre de salida diferente del campo de entrada. Por ejemplo, el siguiente código le indica a Looker que "cree una columna de salida llamada lifetime_value
a partir del campo order_items.total_revenue
":
column: lifetime_value {
field: order_items.total_revenue
}
Nombres de columna implícitos
Si el parámetro field
se omite en la declaración de una columna, se supone que es <explore_name>.<field_name>
. Por ejemplo, si especificaste explore_source: order_items
, entonces
column: user_id {
field: order_items.user_id
}
es equivalente a
column: user_id {}
Cómo crear 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 sí pueden incluir cálculos que se pueden realizar en una sola fila de la tabla.
En el siguiente ejemplo, se genera la misma tabla derivada que en el ejemplo anterior, con la diferencia de 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
de 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
}
}
Uso de funciones analíticas de SQL
Algunos dialectos de bases de datos admiten funciones de ventana, en especial para crear números de secuencia, claves principales, totales acumulados y en ejecución, y otros cálculos útiles de varias filas. Después de que se ejecuta la consulta principal, todas las declaraciones derived_column
se ejecutan en un paso separado.
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 la columna tal 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, con una columna derivada que incluye una función analítica 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
}
}
Cómo agregar filtros a una tabla derivada nativa
Supongamos que deseas crear una tabla derivada del valor de un cliente en los últimos 90 días. Deseas realizar los mismos cálculos que hiciste en el ejemplo anterior, pero solo quieres incluir las compras de los últimos 90 días.
Solo tendrías que agregar un filtro al derived_table
que filtre las transacciones de los últimos 90 días. El parámetro filters
para 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
}
}
Los filtros se agregarán a la cláusula WHERE
cuando Looker escriba el código SQL para la tabla derivada.
Además, puedes usar el parámetro secundario 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 realizar pruebas sin esperar a que se compile la tabla completa después de cada cambio.
El parámetro dev_filters
actúa en conjunto 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 Cómo trabajar más rápido en el modo de desarrollo para obtener más información.
Usa filtros basados en plantillas
Puedes usar bind_filters
para incluir filtros basados en plantillas:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
Esto es esencialmente lo mismo que usar el siguiente código en un bloque sql
:
{% condition filtered_lookml_dt.filter_date %} users.created_date {% endcondition %}
El to_field
es el campo al que se aplica el filtro. El to_field
debe ser un campo del explore_source
subyacente.
El parámetro from_field
especifica el campo del que se obtendrá el filtro, si hay un filtro en el tiempo de ejecución.
En el ejemplo anterior de bind_filters
, Looker tomará cualquier filtro aplicado al campo filtered_lookml_dt.filter_date
y lo aplicará al campo users.created_date
.
También puedes usar el parámetro secundario bind_all_filters
de explore_source
para pasar todos los filtros de tiempo 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.
Cómo 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 un Explore puede mostrar las filas en un orden diferente al de la clasificación subyacente.
Cómo convertir tablas derivadas nativas a diferentes zonas horarias
Puedes especificar la zona horaria de tu 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 los valores de timezone
para obtener una lista de las zonas horarias admitidas.
Si no especificas una zona horaria en la definición de tu tabla derivada nativa, esta no realizará ninguna conversión de zona horaria en los datos basados en el tiempo. En cambio, los datos basados en el tiempo se establecerán de forma predeterminada en la zona horaria de la base de datos.
Si la tabla derivada nativa no es persistente, puedes establecer el valor de la zona horaria en "query_timezone"
para usar automáticamente la zona horaria de la consulta que se está ejecutando.