Cómo genera Looker el código SQL

Si tienes experiencia con SQL y empiezas a usar Looker, seguramente te interese saber cómo genera Looker el código SQL. Básicamente, Looker es una herramienta que genera consultas de SQL y las envía a través de una conexión de base de datos. Looker formula consultas SQL basadas en un proyecto de LookML que describe la relación entre las tablas y las columnas de la base de datos. Si sabes cómo genera consultas Looker, entenderás mejor cómo se traduce tu código LookML en consultas SQL eficientes.

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

Ver la consulta

En un Look guardado o en un Exploración, puedes usar la pestaña SQL del panel Datos para ver lo que Looker envía a la base de datos para obtener los datos. También puede usar los enlaces Abrir en SQL Runner y Explicar en SQL Runner de la parte inferior de la pestaña SQL para ver su consulta en SQL Runner o para ver el plan de explicación de la base de datos para la consulta.

Para obtener más información sobre SQL Runner, consulta la página de documentación Conceptos básicos de SQL Runner. Para obtener más información sobre cómo optimizar una consulta con SQL Runner, consulta la publicación de la comunidad Cómo optimizar SQL con EXPLAIN.

Forma canónica de una consulta de Looker

Las consultas SQL de Looker siempre tienen el siguiente formato.

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, los Exploraciones y las vistas a los que se hace referencia en la consulta de 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 aplicarlas a todas las consultas.

Componentes fundamentales de una consulta de Looker

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

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

• model: el nombre del modelo de LookML al que se dirige, que especifica la base de datos de destino.
• explore: el nombre del Explore que se va a consultar, que rellena la cláusula FROM de SQL.
• Campos: los parámetros dimension y measure que se incluirán en la consulta y que rellenarán la cláusula SELECT de SQL.
• filter: expresiones de filtro de Looker que se aplican a uno o varios campos, que rellenan las cláusulas WHERE y HAVING de SQL.
• Orden: el campo por el que se va a ordenar y el orden, que rellena la cláusula ORDER BY de SQL.

Estos parámetros son precisamente los elementos que un usuario especifica al crear una consulta en la página Explorar de Looker. Estos mismos elementos aparecen en todos los modos de ejecutar consultas con Looker, como en el SQL generado, en la URL que representa la consulta y en la API de Looker.

¿Qué ocurre con las vistas especificadas por las cláusulas LEFT JOIN? Las cláusulas JOIN se rellenan en función de la estructura del modelo de LookML, que especifica cómo se unen las vistas a las Exploraciones. Cuando se crean consultas de 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, una de las ventajas más potentes de Looker para los usuarios empresariales.

Ejemplo de consulta y el código SQL resultante

Vamos a crear una consulta en Looker para mostrar cómo se genera la consulta según el patrón anterior. Imagina una tienda de comercio electrónico que tiene una base de datos con dos tablas, orders (pedidos) y users (usuarios), para monitorizar a los usuarios y los 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)

Vamos a buscar el número de pedidos (ORDERS Count) agrupados por estado (USERS State) y filtrados por la fecha de creación del pedido (ORDERS Created Date) en una exploración de Looker.

Para ver la consulta de SQL que genera y ejecuta Looker, haga 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

Observa la similitud con la fórmula de consulta canónica. El código SQL de Looker muestra algunas características del código generado por una máquina (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.

Ejecutar SQL sin formato en el Ejecutor de SQL de Looker

Looker incluye una función llamada SQL Runner, que te permite ejecutar cualquier SQL que quieras en las conexiones de bases de datos que hayas configurado en Looker.

Como cada consulta generada por Looker da como resultado un comando SQL completo y funcional, puedes usar SQL Runner para investigar o probar la consulta.

Las consultas de SQL sin formato que se ejecutan en SQL Runner producen el mismo conjunto de resultados. Si el SQL contiene algún error, SQL Runner 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 en la configuración de la conexión se especifica una zona horaria de la base de datos, Looker emitirá un SET TIMEZONE al conectarse, lo que puede afectar a los resultados.

Examinar los componentes de consulta de la URL expandida

Después de ejecutar una consulta en Looker, puedes examinar la URL ampliada para ver los componentes fundamentales de una consulta de Looker. Para empezar, selecciona Compartir en el menú de la rueda dentada de Explorar para abrir el menú Compartir URLs.

Nota: Si empiezas desde un Look, haz clic en el enlace Explorar desde aquí del Look para abrir la consulta en un Explorar.

La URL expandida proporciona suficiente información para recrear la consulta. Por ejemplo, esta URL ampliada 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

modelo	e_thelook
explorar	events
campos que se van a consultar y mostrar	fields=users.state,users.count
campo y orden de clasificación	sorts=users.count+desc
campos y valores de filtro	f[users.created_year]=2020

Cómo estructura Looker las combinaciones

En la consulta de ejemplo anterior, observe que orders Explorar aparece en la cláusula FROM principal y las vistas combinadas aparecen en las cláusulas LEFT JOIN. Las combinaciones de Looker se pueden escribir de muchas formas diferentes, lo que se explica con más detalle en la página Trabajar con combinaciones en LookML.

Los bloques SQL especifican cláusulas SQL personalizadas

No todos los elementos de una consulta de Looker se generan automáticamente. En algún momento, el modelo de datos debe proporcionar información específica para que Looker pueda acceder a las tablas subyacentes y calcular los valores derivados. En LookML, los bloques SQL son fragmentos de código SQL proporcionados por el modelador de datos que Looker usa para sintetizar expresiones SQL completas.

El parámetro de bloque SQL más habitual es sql, que se usa en las definiciones de dimensiones y medidas. El parámetro sql especifica una cláusula SQL para hacer referencia a una columna subyacente o para realizar una función de agregación. Por lo general, todos los parámetros de LookML que empiezan por sql_ esperan una expresión SQL de algún tipo. 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 métricas

En el siguiente código de ejemplo se muestran algunos ejemplos de bloques de SQL para dimensiones y medidas. El operador de sustitución de LookML ($) hace que estas declaraciones de sql parezcan engañosamente diferentes de SQL. Sin embargo, una vez que se ha producido la sustitución, la cadena 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 CONCAT y DATEDIFF de MySQL en este caso). El código que uses en los bloques de SQL debe coincidir con el dialecto SQL que use la base de datos.

Bloque de SQL de ejemplo para tablas derivadas

Las tablas derivadas también usan un bloque de SQL para especificar la consulta que deriva la tabla. Este es un ejemplo de tabla derivada basada en SQL:

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
  }
}

Bloque de SQL de ejemplo 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 insertando un bloque de SQL en las cláusulas SQL WHERE o HAVING. En este ejemplo, se usa el operador de sustitución de LookML ${view_name.SQL_TABLE_NAME} 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});;
}