Cómo Looker genera SQL

Si llega a Looker desde un punto de vista SQL, es probable que desee saber cómo Looker genera SQL. En esencia, Looker es una herramienta que genera consultas de SQL y las envía en una conexión de base de datos. Looker formula consultas de SQL en función de un proyecto de LookML que describe la relación entre tablas y columnas en la base de datos. Si comprendes cómo Looker genera consultas, comprenderás mejor cómo tu código de LookML se traduce en consultas de SQL eficientes.

Cada parámetro de LookML controla algún aspecto de cómo Looker genera SQL mediante la modificación de 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 abarca todos los elementos de LookML en detalle. Consulta la página de documentación de referencia de LookML para obtener detalles completos.

Visualiza la consulta

En una apariencia guardada o Explorar, puede usar la pestaña SQL en la sección Datos para ver lo que Looker envía a la base de datos a fin de obtener los datos. También puedes usar los vínculos de la parte inferior para ver tu consulta en SQL Runner o 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 SQL Runner. Para obtener más información sobre cómo optimizar una consulta mediante SQL Runner, consulte el artículo del Centro de ayuda Cómo optimizar SQL con EXPLAIN.

Forma canónica de una consulta de Looker

Las consultas de SQL de Looker siempre adoptan 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 LookML define todas las dimensiones, medidas, exploraciones y vistas a las que se hace referencia en la fórmula anterior. El usuario especifica las expresiones de filtro en Looker para dar forma a consultas ad hoc. Las expresiones de filtro también se pueden declarar directamente en LookML para aplicarse 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 fórmula anterior.

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

• model: Es el nombre del modelo de LookML que se orientará, el cual especifica la base de datos de destino
• explore: Es el nombre de Explorar para 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 propagarán 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 ordenará, y el orden de clasificación, que propaga la cláusula ORDER BY de SQL

Estos parámetros son, precisamente, los elementos que especifica un usuario cuando compila 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 según la estructura del modelo de LookML, que especifica cómo las vistas se unen a Explorar. Cuando se construyen consultas de SQL, Looker incluye cláusulas JOIN solo cuando es necesario. Cuando los usuarios crean una consulta en Looker, no necesitan especificar cómo se unen las tablas, ya que esta información está codificada en el modelo, uno de los beneficios más importantes 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 según el patrón anterior. Considere una tienda de comercio electrónico con tablas para realizar un seguimiento de los usuarios y los pedidos. Los campos y las relaciones de tabla se muestran a continuación.

Busquemos la cantidad de pedidos (Recuento de ORDERS) agrupados por estado (Estado de USERS) y los filtramos por fecha de creación del pedido (Fecha de creación de ORDERS).

A continuación, se muestra el resultado de la consulta en la página Explorar de Looker.

Haz clic en la pestaña SQL para ver el SQL generado y ejecutado por Looker.

Ten en cuenta la similitud con la fórmula canónica que se indicó anteriormente. El SQL de Looker muestra algunas características del código generado por una máquina (p.ej., COALESCE(users.state,'') AS "_g1"), pero siempre se ajusta a la fórmula.

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>,...
ORDER BY <dimension> | <measure>
HAVING <measure_filter_expression> AND <measure_filter_expression> AND ...
LIMIT <limit>

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

Ejecuta SQL sin procesar en el ejecutor de SQL de Looker

Looker incluye una función llamada SQL Runner, en la que puede ejecutar cualquier SQL que desee en las conexiones de base de datos que configuró en Looker.

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

Las consultas SQL sin procesar ejecutadas en SQL Runner producen el mismo conjunto de resultados

Si el SQL contiene errores, 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 la configuración de conexión especifica una zona horaria de la base de datos, Looker emitirá una SET TIMEZONE en la conexión, lo que puede afectar los resultados.

Examina los componentes de la consulta en la URL

Después de ejecutar una consulta en Looker, puede examinar la URL de uso compartido expandida para ver los componentes fundamentales de una consulta de Looker. Primero, selecciona Compartir en el menú de ajustes de Explorar:

Si comienzas desde Look, haz clic en el vínculo Explorar desde aquí para abrir la consulta en Explorar.

Desde allí, aparece la ventana Compartir URL, que muestra la URL expandida:

Por ejemplo, la consulta anterior produce la siguiente URL de uso compartido expandida:

https://docsexamples.dev.looker.com/explore/e_thelook/events?fields=users.state,users.count
&f[users.created_year]=2020&sorts=users.count+desc&limit=500

La URL proporciona información suficiente para recrear la consulta:

modelo	e_thelook
explorar	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 el SQL de consulta 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 se generan automáticamente. 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, que Looker usa para sintetizar expresiones SQL completas.

El parámetro de bloque de SQL más común es sql, que se usa en las definiciones de dimensiones y mediciones. El parámetro sql especifica una cláusula SQL para hacer referencia a una columna subyacente o realizar una función agregada. 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. La Referencia de LookML proporciona detalles sobre cada parámetro.

Ejemplo de bloques 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 LookML ($) hace que estas declaraciones sql parezcan engañosas a diferencia de SQL. Sin embargo, después de la sustitución, la string resultante es SQL puro, que Looker inyecta 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 anteriores, los bloques de SQL pueden usar funciones compatibles con la base de datos subyacente (como CONCAT y DATEDIFF en este caso). El código que uses en bloques de SQL debe coincidir con el dialecto de 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 SQL para filtrar una exploración

Los parámetros sql_always_where y sql_always_having de LookML 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 LookML ${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});;
}