Conceptos de SQL para tablas derivadas

Aunque las vistas de LookML suelen basarse en tablas de tu base de datos, también puedes crear vistas basadas en instrucciones SELECT de SQL. En Looker, este tipo de vista se denomina tabla derivada. Las tablas derivadas son consultas cuyos resultados se usan como si fueran tablas reales de la base de datos.

En esta guía, se tratan los siguientes temas:

Cómo genera Looker el código SQL de las tablas derivadas

Cuando consultas tablas derivadas en LookML, Looker traduce tus consultas en instrucciones SQL como expresiones de tabla comunes (CTEs) o vistas insertadas, según el dialecto. Es decir, las consultas de SQL generadas pueden parecerse a uno de los siguientes ejemplos.

En el siguiente ejemplo se muestra cómo Looker puede generar una consulta SQL que utiliza una CTE para consultar una tabla derivada:

WITH (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users
SELECT ...
FROM users AS u
INNER JOIN top_100_users ON u.id == top_100_users.id
WHERE ...

En el siguiente ejemplo se muestra cómo podría generar Looker una consulta de SQL que utilice una vista insertada para consultar una tabla derivada:

SELECT ...
FROM users AS u
INNER JOIN (
    SELECT o.user_id as id
    FROM orders AS o
    INNER JOIN order_items AS oi ON o.id == oi.order_id
    ORDER BY SUM(oi.total_sale_price) DESC
    GROUP BY o.customer_id
    LIMIT 100
    ) AS top_100_users ON u.id == top_100_users.id
WHERE ...

Tablas derivadas basadas en SQL

Para crear una tabla derivada basada en SQL, define una consulta de SQL directamente en LookML mediante el parámetro sql dentro del parámetro derived_table de una vista. Esto le permite definir las columnas de la tabla derivada mediante SQL.

Por ejemplo, el siguiente LookML de ejemplo define una tabla derivada llamada top_100_users que identifica a los 100 usuarios con el gasto total más alto en todos sus pedidos:

view: top_100_users {
  derived_table: {
    sql: SELECT o.user_id as id
         FROM orders AS o
         INNER JOIN order_items AS oi ON o.id == oi.order_id
         ORDER BY SUM(oi.total_sale_price) DESC
         GROUP BY o.customer_id
         LIMIT 100 ;;
  }

  dimension: id {
    type: number
    sql: ${TABLE}.id ;;
  }
}

Cuando se hace referencia a la vista top_100_users en una consulta Exploración, Looker usa esta instrucción SQL SELECT en el SQL generado como CTE o como vista insertada, en función de tu dialecto.

Usar SQL para definir tablas derivadas puede tener algunas limitaciones. Por ejemplo, en el ejemplo de la sección Tablas derivadas basadas en SQL, se aplican las siguientes consideraciones:

  • La relación entre las tablas orders y order_items probablemente ya esté definida en el modelo de LookML. Si cambian los nombres de las tablas subyacentes de tu base de datos, deberás actualizar los nombres de las tablas en varios lugares, incluida la definición de la tabla derivada basada en SQL.
  • El SQL de la definición de la tabla derivada debe estar en el dialecto correcto de la base de datos subyacente. Si los datos se mueven a otra base de datos, deberá cambiar la definición de la tabla derivada basada en SQL.

Por estos motivos, las tablas derivadas nativas suelen ser una mejor opción.

Tablas derivadas nativas

En Looker, las tablas derivadas nativas se definen con LookML. A diferencia de las tablas derivadas basadas en SQL, cuando defines una tabla derivada nativa, aprovechas tu modelo de LookML de las siguientes formas:

  • Si el nombre de la tabla cambia en la base de datos, solo tienes que actualizarlo una vez en el modelo de LookML. Como tu tabla derivada nativa apunta al objeto de LookML donde se define la tabla de la base de datos, se referirá automáticamente a la tabla adecuada.
  • Del mismo modo, si cambias a otro dialecto, el LookML de la tabla derivada nativa seguirá siendo válido porque Looker genera el SQL adecuado para tu conexión de base de datos.

Puedes definir manualmente el LookML de la tabla derivada nativa. Sin embargo, la forma más sencilla de crear una tabla derivada nativa es dejar que Looker cree la tabla derivada a partir de una consulta Explorar. En Explorar, seleccione los campos que quiera usar en la tabla derivada y, en el menú de ajustes de Explorar, elija Obtener LookML > Tabla derivada para obtener el LookML. Looker genera el LookML que necesitas para crear una tabla derivada a partir de tu consulta Exploración, incluidas las definiciones de campo relevantes de tu modelo de LookML que son necesarias para crear las columnas de tu tabla derivada. Puedes copiar el LookML en un archivo de vista de tu proyecto para crear la tabla derivada.

En el siguiente ejemplo se muestra una tabla derivada nativa que identifica a los 100 usuarios con el gasto total más alto en todos sus pedidos:

view: top_100_users {
  derived_table: {
    explore_source:  orders {
      column: id {
        field: orders.customer_id
      }
      sorts: [order_items.sum_total_sale_price desc]
      limit: 100
    }
  }
}

Cuando consultas una tabla derivada nativa en un Explore, se generan automáticamente todos los detalles de la consulta SQL de la tabla derivada nativa. Las definiciones de las columnas de una vista de LookML con una tabla derivada nativa se infieren de las definiciones de la Exploración subyacente, por lo que no es necesario repetir ninguna definición.

Tablas derivadas persistentes (PDTs)

En Looker, puedes crear tablas derivadas temporales y tablas derivadas persistentes. Una vez que hayas creado una tabla derivada basada en SQL o una tabla derivada nativa, puedes añadir persistencia para que Looker escriba la tabla en un esquema provisional de tu base de datos y la regenere según la programación que especifiques. Para obtener más información, consulta el artículo Tablas derivadas en Looker.