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 términos de LookML. 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 entender al modelar los 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.
Las tablas derivadas nativas y las basadas en SQL se definen en LookML mediante el parámetro derived_table a nivel de vista. Sin embargo, con las tablas derivadas nativas, no es necesario crear una consulta SQL. En su lugar, se 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.
También puedes hacer que Looker cree el LookML de la tabla derivada a partir de una consulta de SQL Runner, tal como se describe en la página de documentación Usar SQL Runner para crear tablas derivadas.
Usar un Explore para empezar a definir tus tablas derivadas nativas
A partir de una Exploración, Looker puede generar LookML para toda o la mayor parte de tu tabla derivada. Solo tienes que crear una exploración y seleccionar todos los campos que quieras incluir en la tabla derivada. A continuación, para generar el LookML de la tabla derivada nativa, sigue estos pasos:
Selecciona el menú de ajustes Explorar acciones y, a continuación, Obtener LookML.
Haga clic en la pestaña Tabla derivada para ver el LookML que se usa para crear una tabla derivada nativa en la exploración.
Copia el LookML.
Ahora que has copiado el LookML generado, pégalo en un archivo de vista:
En el modo Desarrollo, ve a los archivos del proyecto.
Haz clic en + en la parte superior de la lista de archivos de proyecto del IDE de Looker y selecciona Crear vista. También puedes hacer clic en el menú de una carpeta y seleccionar Crear vista para crear el archivo en esa carpeta.
Asigna un nombre descriptivo a la vista.
También puedes cambiar los nombres de las columnas, especificar columnas derivadas y añadir 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 con la palabra Recuento. Para evitar confusiones, pon el nombre de la vista en plural. Para cambiar el nombre de la vista, puedes seleccionar Mostrar nombre de campo completo en Serie, en la configuración de la visualización, o bien usar el parámetroview_labelcon una versión en plural del nombre de la vista.
Definir una tabla derivada nativa en LookML
Tanto si usas tablas derivadas declaradas en SQL como en 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 implican en la consulta SQL. Por ejemplo, la siguiente consulta de 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 medida y de dimensión, añade 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 sencillo se crea 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 su lugar, Looker crea la consulta por ti usando la exploración 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
}
}
Usar declaraciones include para habilitar la referencia a campos
En el archivo de vista de la tabla derivada nativa, se usa el parámetro explore_source para hacer referencia 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 dirigir al archivo que contiene la definición del Explore. Si no tienes la instrucción include, el IDE de Looker no autosuggest nombres de campos ni verificará tus referencias de campos mientras creas la tabla derivada nativa. En su lugar, puede usar el validador de LookML para verificar los campos a los que hace referencia en su tabla derivada nativa.
Sin embargo, si quieres habilitar las sugerencias automáticas y la verificación inmediata de campos en el IDE de Looker, o si tienes un proyecto de LookML complejo con varios Exploraciones que tienen el mismo nombre o que pueden tener referencias circulares, puedes usar el parámetro include para indicar la ubicación de la definición de Exploración.
Los Explorar se suelen definir en un archivo de modelo, pero, en el caso de las tablas derivadas nativas, es más sencillo crear un archivo independiente para el Explorar. Los archivos Explorar de LookML tienen la extensión .explore.lkml, tal como se describe en la documentación sobre creación de archivos Explorar. De esta forma, en el archivo de vista de tabla derivada nativa, puede incluir un solo archivo Explore y no todo el archivo de modelo.
Si quieres crear un archivo Explorar independiente y usar el parámetro include para dirigir al archivo Explorar en el archivo de vista de tu tabla derivada nativa, asegúrate de que tus archivos LookML cumplan los siguientes requisitos:
- El archivo de vista de la tabla derivada nativa debe incluir el archivo del Explore. Por ejemplo:
include: "/explores/order_items.explore.lkml"
- El archivo de Exploración debe incluir los archivos de vista que necesite. 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"
Definir columnas de tablas derivadas nativas
Como se muestra en el ejemplo anterior, se usa column para especificar las columnas de salida de la tabla derivada.
Especificar los nombres de las columnas
En la columna user_id, el nombre de la columna coincide con el nombre del campo especificado en la exploración original.
A menudo, querrá que la tabla de salida tenga un nombre de columna diferente al de los campos de la Exploración original. En el ejemplo anterior, se ha calculado el valor del tiempo de vida por usuario mediante la exploración order_items. En la tabla de resultados, total_revenue es en realidad el lifetime_customer_value de un cliente.
La declaración column permite declarar un nombre de salida que sea diferente del campo de entrada. Por ejemplo, el siguiente código 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 no se incluye en la declaración de una columna, se supone que es <explore_name>.<field_name>. Por ejemplo, si has especificado explore_source: order_items,
column: user_id {
field: order_items.user_id
}
es equivalente a
column: user_id {}
Crear columnas derivadas para valores calculados
Puedes añadir parámetros derived_column para especificar columnas que no existan en la función Explorar del parámetro explore_source. Cada parámetro derived_column tiene un parámetro sql que especifica cómo se debe crear el valor.
El cálculo de sql puede usar cualquier columna que haya especificado con parámetros column. Las columnas derivadas no pueden incluir funciones de agregación, pero sí cálculos que se pueden realizar en una sola fila de la tabla.
El siguiente ejemplo genera la misma tabla derivada que el ejemplo anterior, pero añade una columna average_customer_order calculada 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 de ventana de SQL
Algunos dialectos de bases de datos admiten funciones de ventana, sobre todo para crear números de secuencia, claves principales, totales acumulados y otros cálculos útiles de varias filas. Una vez que se ha ejecutado la consulta principal, las declaraciones derived_column se ejecutan en una pasada independiente.
Si el dialecto de tu base de datos admite funciones de ventana, puedes usarlas en tu tabla derivada nativa. Crea un parámetro derived_column con un parámetro sql que contenga la función de ventana que quieras. 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. A continuación, 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
}
}
Añadir filtros a una tabla derivada nativa
Supongamos que quiere crear una tabla derivada del valor de un cliente en los últimos 90 días. Quieres hacer los mismos cálculos que en el ejemplo anterior, pero solo quieres incluir las compras de los últimos 90 días.
Solo tienes que añadir un filtro a la derived_table para que se muestren las transacciones de los últimos 90 días. El parámetro filters de una tabla derivada usa la misma sintaxis que se utiliza para crear una métrica 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 añadirán a la cláusula WHERE cuando Looker escriba el SQL de 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 crear versiones más pequeñas y filtradas de la tabla para iterar y probar sin tener que esperar a que se cree la tabla completa después de cada cambio.
El parámetro dev_filters funciona 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 en la versión de desarrollo de la tabla.
Consulta Trabajar más rápido en el modo Desarrollo para obtener más información.
Usar 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
}
Es 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 from_field especifica el campo del que se va a obtener el filtro, si hay un filtro en el tiempo de ejecución.
En el bind_filtersejemplo anterior, Looker tomará cualquier filtro aplicado al campo filtered_lookml_dt.filter_date y lo aplicará al campo users.created_date.
También puede usar el subparámetro bind_all_filters de explore_source para transferir todos los filtros de tiempo de ejecución de una exploración a una subconsulta de tabla derivada nativa. Para obtener más información, consulta la página de documentación del parámetro explore_source.
Ordenar y limitar tablas derivadas nativas
También puedes ordenar y limitar las tablas derivadas, si quieres:
sorts: [order_items.count: desc]
limit: 10
Recuerda que una exploración puede mostrar las filas en un orden diferente al de la ordenación subyacente.
Convertir tablas derivadas nativas a otras zonas horarias
Puede especificar la zona horaria de su tabla derivada nativa mediante el subparámetro timezone:
timezone: "America/Los_Angeles"
Si usa el subparámetro timezone, todos los datos basados en el tiempo de la tabla derivada nativa se convertirán a la zona horaria que especifique. Consulta la página de documentación de los timezone valores para ver una lista de las zonas horarias admitidas.
Si no especifica una zona horaria en la definición de la tabla derivada nativa, esta no realizará ninguna conversión de zona horaria en los datos basados en el tiempo. En su lugar, los datos basados en el tiempo se definirán de forma predeterminada en la zona horaria de la base de datos.
Si la tabla derivada nativa no es persistente, puedes asignar el valor "query_timezone" a la zona horaria para usar automáticamente la zona horaria de la consulta que se esté ejecutando.