Conceptos de SQL para tablas derivadas

Si bien las vistas de LookML suelen basarse en tablas existentes dentro 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 en la base de datos.

En esta guía, aprenderás sobre los siguientes temas:

Cómo Looker genera el SQL para las tablas derivadas

Cuando consultas tablas derivadas en LookML, Looker traduce tus consultas en instrucciones de SQL como expresiones de tablas comunes (CTE) o vistas intercaladas, según el dialecto. En otras palabras, las consultas de SQL generadas pueden parecerse a uno de los siguientes ejemplos.

En el siguiente ejemplo, se muestra cómo Looker podría generar una consulta en SQL que use 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 Looker podría generar una consulta de SQL que usa una vista intercalada 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, debes definir una consulta de SQL directamente en LookML con el parámetro sql dentro del parámetro derived_table de una vista. Esto te permite definir las columnas de la tabla derivada con SQL.

Por ejemplo, la siguiente muestra de LookML define una tabla derivada llamada top_100_users que identifica a los 100 usuarios principales con la inversión total más alta 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 de Explorar, Looker usa esta sentencia SELECT de SQL en el SQL generado como una CTE o una vista intercalada, según el dialecto.

El uso de SQL para definir tablas derivadas puede tener algunas limitaciones. En el ejemplo de la sección de tablas derivadas basadas en SQL, por ejemplo, se aplican las siguientes consideraciones:

  • Es probable que la relación entre las tablas orders y order_items ya esté definida en el modelo de LookML. Si los nombres de la tabla subyacente en tu base de datos cambian, los nombres de la tabla deben actualizarse en varios lugares, incluso en la definición de tu tabla derivada basada en SQL.
  • El SQL en la definición de tu tabla derivada tiene que estar en el dialecto correcto para la base de datos subyacente. Si los datos se transfieren a una base de datos diferente, se debe cambiar la definición de tu 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 usar tablas derivadas basadas en SQL, cuando defines una tabla derivada nativa, aprovechas tu modelo de LookML de las siguientes maneras:

  • Si el nombre de la tabla cambia en la base de datos, solo tendrás que actualizarlo una vez en el modelo de LookML. Debido a que tu tabla derivada nativa apunta al objeto LookML existente en el que se define la tabla de la base de datos, tu tabla derivada nativa hará referencia automáticamente a la tabla adecuada.
  • Del mismo modo, si cambias a un dialecto diferente, el código LookML de la tabla derivada nativa seguirá siendo válido porque Looker genera el código SQL adecuado para tu conexión a la base de datos.

Puedes definir LookML de forma manual para la tabla derivada nativa. Sin embargo, la forma más fácil de crear una tabla derivada nativa es pedirle a Looker que cree la tabla derivada a partir de una consulta de Explorar. En Explorar, selecciona los campos que deseas usar en tu tabla derivada y usa la opción Obtener LookML > Tabla derivada en el menú de ajustes de Explorar para obtener el código LookML. Looker genera el LookML que necesitas para crear una tabla derivada a partir de tu consulta de Explorar, incluidas las definiciones de campos relevantes de tu modelo de LookML que son necesarias para crear las columnas de tu tabla derivada. Puedes copiar LookML en un archivo de vista en tu proyecto para crear la tabla derivada.

El siguiente ejemplo muestra una tabla derivada nativa que identifica a los 100 usuarios principales con la mayor inversión total 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 una exploración, todos los detalles de la consulta de SQL para la tabla derivada nativa se generan automáticamente. 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, lo que elimina la necesidad de repetir ninguna definición.

Tablas derivadas persistentes (PDT)

En Looker, puedes crear tablas derivadas temporales y tablas derivadas persistentes. Una vez que crees una tabla derivada basada en SQL o una tabla derivada nativa, puedes agregar persistencia para que Looker escriba la tabla en un esquema en blanco en tu base de datos y la vuelva a generar en el programa que especifiques. Para obtener más información, consulta Tablas derivadas en Looker.