Una tabla derivada es una consulta cuyos resultados se usan como si la tabla derivada fuera una tabla física de la base de datos. Una tabla derivada nativa se basa en una consulta que defines con los 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. 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 mediante el parámetro derived_table en el nivel de la vista. Sin embargo, con las tablas derivadas nativas, no necesitas crear una consulta en SQL. En su lugar, usa el parámetro explore_source para especificar la exploración en la que se basa la tabla derivada, las columnas deseadas y otras características deseadas.
También puedes hacer que Looker cree la tabla derivada LookML a partir de una consulta del ejecutor de SQL, como se describe en la página de documentación Cómo usar el ejecutor de SQL para crear tablas derivadas.
Cómo usar una exploración para comenzar a definir tus tablas derivadas nativas
Comenzando con una función Explorar, Looker puede generar LookML para toda o la mayoría de tus tablas derivadas. Solo debes crear una exploración y seleccionar todos los campos que desees incluir en tu tabla derivada. Luego, para generar la tabla derivada nativa de LookML, sigue estos pasos:
Selecciona el menú de ajustes Explorar acciones y selecciona Obtener LookML.
Haz clic en la pestaña Tabla derivada para consultar LookML con el objetivo de crear una tabla derivada nativa para Explorar.
Copia LookML.
Ahora que copiaste el LookML generado, pégalo en un archivo de vista:
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. También puedes hacer clic en el menú de una carpeta y seleccionar Create View en el menú para crear el archivo dentro de la carpeta.
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.
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 Count. Para evitar confusiones, coloca el nombre de la vista en plural. Para cambiar el nombre de la vista, selecciona Mostrar nombre completo del campo en Serie en la configuración de visualización o utiliza el parámetroview_labelcon una versión en plural del nombre de la vista.
Define una tabla derivada nativa en LookML
Ya sea que uses tablas derivadas declaradas en SQL o LookML nativo, el resultado de la consulta de derived_table es una tabla con un conjunto de columnas. Cuando la tabla derivada se expresa en SQL, la consulta en SQL implica los nombres de las columnas de resultado. Por ejemplo, la siguiente consulta en 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 una exploración, 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 más 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 en SQL de forma manual. En su lugar, Looker crea la consulta por ti con la opción Explorar order_items especificada y algunos de los campos de esa exploración (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 la referencia a campos
En el archivo de vista de la tabla derivada nativa, usa el parámetro explore_source para apuntar a una exploración 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 señalar el archivo que contiene la definición de la exploración. Si no tienes la sentencia include, el IDE de Looker no sugerirá automáticamente nombres de campo ni verificará las referencias de tus campos mientras compilas la tabla derivada nativa. En su lugar, puedes usar el Validador de Looker para verificar los campos a los que haces referencia en tu tabla derivada nativa.
Sin embargo, si quieres habilitar las sugerencias automáticas y la verificación de campo inmediata en el IDE de Looker, o si tienes un proyecto complejo de LookML que tiene varias exploraciones con el mismo nombre o posibles referencias circulares, puedes usar el parámetro include para señalar la ubicación de la definición de la exploración.
Las exploraciones a menudo 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 la exploración. Los archivos de exploración de LookML tienen la extensión de archivo .explore.lkml, como se describe en la documentación sobre cómo crear archivos de exploración. De esa manera, en tu archivo de vista de tabla derivada nativa, puedes incluir un solo archivo de exploración y no el archivo de modelo completo.
Si quieres crear un archivo de exploración independiente y usar el parámetro include para apuntar al archivo de exploración en el archivo de vista de tu tabla derivada nativa, asegúrate de que los archivos de LookML cumplan con los siguientes requisitos:
- El archivo de vista de la tabla derivada nativa debe incluir el archivo de exploración. Por ejemplo:
include: "/explores/order_items.explore.lkml"
- El archivo de exploración 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 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, usa 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.
A menudo, querrás un nombre de columna diferente en la tabla de salida que el nombre de los campos en la exploración original. En el ejemplo anterior, se produjo un cálculo del valor del ciclo de vida del cliente por parte del usuario mediante la exploración de order_items. En la tabla de salida, total_revenue es realmente el lifetime_customer_value de un cliente.
La declaración column admite la declaración de un nombre de salida que sea diferente del campo de entrada. Por ejemplo, el siguiente código le indica a Looker que “cree una columna de salida llamada lifetime_value desde el campo order_items.total_revenue”:
column: lifetime_value {
field: order_items.total_revenue
}
Nombres de columnas implícitos
Si el parámetro field se excluye de una declaración de 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 {}
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 el siguiente ejemplo, se produce la misma tabla derivada que en el ejemplo anterior, excepto que se agrega una columna calculada 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: 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
}
}
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 deseada. 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 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 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
}
}
Agrega filtros a una tabla derivada nativa
Supongamos que deseas crear una tabla derivada del valor de un cliente en los últimos 90 días. Quieres los mismos cálculos que realizaste en el ejemplo anterior, pero solo quieres incluir las compras de los últimos 90 días.
Solo debes agregar un filtro a la derived_table que filtre 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
}
}
Los filtros se agregarán 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 realizar pruebas sin esperar a que se compile la tabla completa 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 tanto dev_filters como 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 Modo de desarrollo para obtener más información.
Usa filtros con plantillas
Puedes usar bind_filters para incluir filtros con plantilla:
bind_filters: {
to_field: users.created_date
from_field: filtered_lookml_dt.filter_date
}
En esencia, 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 obtiene el filtro si hay uno 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 lo aplicará 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.
Ordena y limita tablas derivadas nativas
También puedes ordenar y limitar las tablas derivadas, si lo deseas:
sorts: [order_items.count: desc]
limit: 10
Recuerda que una exploración puede mostrar las filas en un orden diferente que el orden subyacente.
Cómo convertir tablas derivadas nativas en 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 horario en 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 admitidas.
Si no especificas una zona horaria en la definición de tu tabla derivada nativa, la tabla derivada nativa no realizará ninguna conversión de zona horaria en los datos basados en tiempo y, en su lugar, se establecerá la zona horaria de tu base de datos de forma predeterminada.
Si la tabla derivada nativa no es persistente, puedes configurar el valor de la zona horaria en "query_timezone" para usar automáticamente la zona horaria de la consulta que se está ejecutando.