Cómo Looker genera SQL

Si llegas a Looker desde que tienes experiencia en SQL, es probable que te interese saber cómo Looker genera SQL. En esencia, Looker es una herramienta que genera consultas en SQL y las envía a una conexión de base de datos. Looker formula consultas en SQL en función de un proyecto de LookML que describe la relación entre las tablas y las columnas de la base de datos. Comprender cómo Looker genera consultas te permitirá comprender mejor cómo tu código de LookML se traduce en consultas en SQL eficientes.

Cada parámetro de LookML controla algún aspecto de la forma en que Looker genera SQL modificando la estructura, el contenido o el comportamiento de la consulta. En esta página, se describen los principios de cómo Looker genera SQL, pero no se abarcan todos los elementos de LookML en detalle. La página de documentación de referencia rápida de LookML es un buen punto de partida para obtener información sobre los parámetros de LookML.

Visualiza la consulta

En una vista guardada o una Explorar, puedes usar la pestaña SQL del panel Datos para ver qué envía Looker a la base de datos para obtener los datos. También puedes usar los vínculos Abrir en el Ejecutor de SQL y Explicar en el Ejecutor de SQL que se encuentran en la parte inferior de la pestaña SQL para ver tu consulta en el Ejecutor de SQL o para ver el plan de explicación de la base de datos para la consulta.

Para obtener más información sobre el Ejecutor de SQL, consulta la página de documentación de los conceptos básicos del Ejecutor de SQL. Para obtener más información sobre cómo optimizar una consulta con el Ejecutor de SQL, lee la publicación de Comunidad Cómo optimizar SQL con EXPLAIN.

Formato canónico de una consulta de Looker

Las consultas en SQL de Looker siempre toman la siguiente forma.

SELECT
   <dimension>, <dimension>, ...
   <measure>, <measure>, ...
FROM <explore>
LEFT JOIN <view> ON ...
LEFT JOIN <view> ON ...
WHERE (<dimension_filter_expression>) AND (<dimension_filter_expression>) AND ...
GROUP BY <dimension>, <dimension>, <dimension>, ...
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
ORDER BY <dimension> | <measure>
LIMIT <limit>

El proyecto de LookML define todas las dimensiones, las medidas, las exploraciones y las vistas a las que se hace referencia en la consulta en SQL. El usuario especifica las expresiones de filtro en Looker para dar forma a las consultas ad hoc. Las expresiones de filtro también se pueden declarar directamente en LookML para que se apliquen a todas las consultas.

Componentes fundamentales de una consulta de Looker

Todas las consultas de Looker se representan con estos parámetros fundamentales aplicados a un proyecto de LookML, como se ve en la consulta de ejemplo anterior.

Looker usa los siguientes parámetros para generar una consulta en SQL completa:

• model: Es el nombre del modelo de LookML al que se orientará, que especifica la base de datos de destino.
• explore: Es el nombre de la exploración que se debe consultar, que propaga la cláusula FROM de SQL.
• Campos: Los parámetros dimension y measure que se incluirán en la consulta, los cuales propagan la cláusula SELECT de SQL
• filter: Expresiones de filtro de Looker para aplicar a cero o más campos, que propagan las cláusulas WHERE y HAVING de SQL
• Orden de clasificación: El campo por el que se debe ordenar y el orden de clasificación, que propaga la cláusula ORDER BY de SQL

Estos parámetros son precisamente los elementos que un usuario especifica cuando crea una consulta en la página Explorar de Looker. Estos mismos elementos aparecen en todos los modos de ejecución de consultas con Looker: en el SQL generado, en la URL que representa la consulta, en la API de Looker, etcétera.

¿Qué ocurre con las vistas especificadas por las cláusulas LEFT JOIN? Las cláusulas JOIN se propagan en función de la estructura del modelo de LookML, que especifica cómo se unen las vistas con las exploraciones. Cuando se crean consultas en SQL, Looker incluye cláusulas JOIN solo cuando es necesario. Cuando los usuarios crean una consulta en Looker, no tienen que especificar cómo se unen las tablas, ya que esta información está codificada en el modelo, uno de los beneficios más potentes de Looker para los usuarios empresariales.

Una consulta de ejemplo y el SQL resultante

Creemos una consulta en Looker para demostrar cómo se genera la consulta de acuerdo con el patrón anterior. Piensa en una tienda de comercio electrónico que tiene una base de datos con dos tablas, pedidos y usuarios, para hacer un seguimiento de usuarios y pedidos.

orders
id INT
created_at DATETIME
users_id INT
status VARCHAR(255)
traffic_source VARCHAR(15)

users
id INT
email VARCHAR(255)
first_name VARCHAR(255)
last_name VARCHAR(255)
created_at DATETIME
zip INT
country VARCHAR(255)
state VARCHAR(255)
city VARCHAR(255)
age INT
traffic_source VARCHAR(15)

Busquemos la cantidad de pedidos (Recuento de PEDIDOS) agrupados por estado (Estado de los USUARIOS) y filtrados por la fecha de creación del pedido (Fecha de creación de los PEDIDOS) en una exploración de Looker.

Para ver la consulta en SQL que genera y ejecuta Looker, haz clic en la pestaña SQL del panel Datos.

SELECT COALESCE(users.state, ' ') AS "_g1",
   users.state AS 'users.state',
   COUNT(DISTINCT orders.id) AS 'orders.count'
FROM orders
LEFT JOIN users ON orders.user_id = users.id

WHERE
  orders.created_at BETWEEN (CONVERT_TZ(DATE_ADD(CURDATE(), INTERVAL -29 day), 'America/Los_Angeles', 'UTC',)) AND (CONVERT_TZ(DATE_ADD(DATE_ADD(DATE_ADD(CURDATE(), INTERVAL -29 day), INTERVAL 30 day), INTERVAL -1 second), 'America/Los_Angeles', 'UTC'))
GROUP BY 1
ORDER BY COUNT(DISTINCT orders.id) DESC
LIMIT 500

Ten en cuenta la similitud con la fórmula de la consulta canónica. Looker SQL muestra algunas características del código generado por máquinas (por ejemplo, COALESCE(users.state,'') AS "_g1"), pero siempre se ajusta a la fórmula.

Experimenta con más consultas en Looker para comprobar que la estructura de las consultas es siempre la misma.

Ejecuta SQL sin procesar en el ejecutor de SQL de Looker

Looker incluye una función llamada Ejecutor de SQL, en la que puedes ejecutar cualquier SQL que desees en las conexiones de base de datos que configuraste en Looker.

Como cada consulta que genera Looker genera un comando de SQL completo y funcional, puedes usar el ejecutor de SQL para investigar o probar la consulta.

Las consultas SQL sin procesar que se ejecutan en el Ejecutor de SQL producen el mismo conjunto de resultados. Si el SQL contiene algún error, el Ejecutor de SQL destacará la ubicación del primer error en el comando SQL e incluirá la posición del error en el mensaje de error.

Nota: Si la configuración de conexión especifica una zona horaria de la base de datos, Looker emitirá un SET TIMEZONE cuando se establezca la conexión, lo que puede afectar los resultados.

Examina los componentes de la consulta en la URL expandida

Después de ejecutar una consulta en Looker, puedes examinar la URL expandida para ver los componentes fundamentales de una consulta de Looker. En primer lugar, selecciona Compartir en el menú de ajustes de la exploración para abrir el menú Compartir URL.

Nota: Si estás comenzando desde una vista, haz clic en el vínculo Explorar desde aquí de la vista para abrir la búsqueda en una exploración.

La URL expandida proporciona suficiente información para volver a crear la consulta. Por ejemplo, este ejemplo de URL expandida proporciona la siguiente información:

https://<Looker instance URL>.cloud.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500

model	e_thelook
exploración	events
campos para consultar y mostrar	fields=users.state,users.count
ordenar campo y ordenar	sorts=users.count+desc
filtrar campos y valores	f[users.created_year]=2020

Cómo Looker estructura las JOIN

En la consulta de ejemplo anterior, observa que la exploración orders aparece en la cláusula FROM principal y las vistas unidas aparecen en las cláusulas LEFT JOIN. Las uniones de Looker se pueden escribir de muchas maneras diferentes, lo que se explica con más detalle en la página Trabaja con uniones en LookML.

Los bloques de SQL especifican cláusulas de SQL personalizadas

No todos los elementos de una consulta de Looker son generados por máquinas. En algún momento, el modelo de datos debe proporcionar detalles específicos para que Looker acceda a las tablas subyacentes y calcule los valores derivados. En LookML, los bloques de SQL son fragmentos de código SQL que proporciona el modelador de datos y que Looker usa para sintetizar expresiones de SQL completas.

El parámetro de bloque de SQL más común es sql, que se usa en las definiciones de dimensión y medición. El parámetro sql especifica una cláusula de SQL para hacer referencia a una columna subyacente o realizar una función de agregación. En general, todos los parámetros de LookML que comienzan con sql_ esperan una expresión SQL de alguna forma. Por ejemplo: sql_always_where, sql_on y sql_table_name. Consulta la referencia de LookML para obtener más información sobre cada parámetro.

Ejemplos de bloques de SQL para dimensiones y medidas

A continuación, se muestran algunos ejemplos de bloques de SQL para dimensiones y medidas. El operador de sustitución de Looker ($) hace que estas declaraciones sql parezcan engañosas, a diferencia de SQL. Sin embargo, después de que se produce la sustitución, la string resultante es SQL puro, que Looker inserta en la cláusula SELECT de la consulta.

dimension: id {
  primary_key: yes
  sql: ${TABLE}.id ;;  # Specify the primary key, id
}
measure: average_cost {
  type: average
  value_format: "0.00"
  sql: ${cost} ;;      # Specify the field that you want to average
                       # The field 'cost' is declared elsewhere
}
dimension: name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
  type: number
  sql: DATEDIFF(${sold_date}, ${created_date}) ;;
}

Como se muestra en las dos últimas dimensiones de este ejemplo, los bloques de SQL pueden usar funciones compatibles con la base de datos subyacente (como las funciones de MySQL CONCAT y DATEDIFF en este caso). El código que usas en los bloques de SQL debe coincidir con el dialecto SQL que usa la base de datos.

Ejemplo de bloque de SQL para tablas derivadas

Las tablas derivadas también usan un bloque de SQL para especificar la consulta que deriva la tabla. A continuación, se muestra un ejemplo:

view: user_order_facts {
  derived_table: {
    sql:
      SELECT
        user_id
        , COUNT(*) as lifetime_orders
      FROM orders
      GROUP BY 1 ;;
  }

  # later, dimension declarations reference the derived column(s)…
  dimension: lifetime_orders {
    type: number
  }
}

Ejemplo de bloque de SQL para filtrar una exploración

Los parámetros de LookML sql_always_where y sql_always_having te permiten restringir los datos disponibles para una consulta mediante la inserción de un bloque de SQL en las cláusulas WHERE o HAVING de SQL. En este ejemplo, el operador de sustitución de Looker ${view_name.SQL_TABLE_NAME} se usa para hacer referencia a una tabla derivada:

explore: trips {
  view_label: "Long Trips"
  # This will ensure that we only see trips that are longer than average!
  sql_always_where: ${trips.trip_duration}>=(SELECT tripduration FROM ${average_trip_duration.SQL_TABLE_NAME});;
}