Comment Looker génère le langage SQL

Si vous utilisez Looker et que vous avez des connaissances en SQL, vous vous demandez peut-être comment Looker génère du code SQL. Fondamentalement, Looker est un outil qui génère des requêtes SQL et les envoie à une connexion de base de données. Looker génère des requêtes SQL à partir d'un projet LookML qui décrit les relations entre les tables et les colonnes de la base de données. En comprenant comment Looker génère des requêtes, vous comprendrez mieux comment votre code LookML se traduit en requêtes SQL efficaces.

Chaque paramètre LookML contrôle un aspect de la façon dont Looker génère du code SQL, en modifiant la structure, le contenu ou le comportement de la requête. Cette page décrit les principes de génération de code SQL par Looker, mais ne couvre pas tous les éléments LookML en détail. La page de documentation Référence rapide LookML est un bon point de départ pour obtenir des informations sur les paramètres LookML.

Afficher la requête

Dans un requête enregistrée ou dans une exploration, vous pouvez utiliser l'onglet SQL du panneau Données pour voir ce que Looker envoie à la base de données pour obtenir les données. Vous pouvez également utiliser les liens Ouvrir dans SQL Runner et Expliquer dans SQL Runner en bas de l'onglet SQL pour afficher votre requête dans SQL Runner ou pour afficher le plan d'explication de la base de données pour la requête.

Pour en savoir plus sur SQL Runner, consultez la page de documentation Principes de base de SQL Runner. Pour en savoir plus sur l'optimisation d'une requête à l'aide de SQL Runner, consultez l'article de la communauté Comment optimiser SQL avec EXPLAIN.

Forme canonique d'une requête Looker

Les requêtes SQL de Looker prennent toujours la forme suivante.

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>

Le projet LookML définit toutes les dimensions, mesures, explorations et vues référencées dans la requête SQL. Les expressions de filtre sont spécifiées dans Looker par l'utilisateur pour façonner des requêtes ad hoc. Vous pouvez également déclarer des expressions de filtre directement dans le code LookML pour les appliquer à toutes les requêtes.

Composants fondamentaux d'une requête Looker

Toutes les requêtes Looker sont représentées par ces paramètres fondamentaux appliqués à un projet LookML, comme illustré dans l'exemple de requête précédent.

Looker utilise les paramètres suivants pour générer une requête SQL complète:

• model: nom du modèle LookML à cibler, qui spécifie la base de données cible
• explore: nom de la requête "Explorer vers", qui renseigne la clause SQL FROM
• Champs: paramètres dimension et measure à inclure dans la requête, qui renseignent la clause SELECT SQL
• filter: expressions de filtre Looker à appliquer à un ou plusieurs champs, qui remplissent les clauses SQL WHERE et HAVING
• Ordre de tri: champ à trier et ordre de tri, qui renseigne la clause SQL ORDER BY

Ces paramètres correspondent précisément aux éléments qu'un utilisateur spécifie lorsqu'il crée une requête sur la page Explorer de Looker. Ces mêmes éléments apparaissent dans tous les modes d'exécution des requêtes avec Looker, par exemple dans le code SQL généré, dans l'URL représentant la requête et dans l'API Looker.

Qu'en est-il des vues spécifiées par les clauses LEFT JOIN ? Les clauses JOIN sont renseignées en fonction de la structure du modèle LookML, qui spécifie comment les vues sont jointes aux explorations. Lors de la création de requêtes SQL, Looker n'inclut des clauses JOIN que lorsque cela est nécessaire. Lorsque les utilisateurs créent une requête dans Looker, ils n'ont pas besoin de spécifier comment les tables sont jointes, car ces informations sont encodées dans le modèle. Il s'agit là de l'un des avantages les plus intéressants de Looker pour les utilisateurs professionnels.

Exemple de requête et code SQL généré

Créons une requête dans Looker pour montrer comment elle est générée selon le modèle précédent. Imaginons une boutique d'e-commerce qui dispose d'une base de données avec deux tables, orders (Commandes) et users (Utilisateurs), pour suivre les utilisateurs et les commandes.

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)

Trouvons le nombre de commandes (ORDERS Count) regroupées par état (USERS State) et filtrées par date de création de la commande (ORDERS Created Date) dans une exploration Looker.

Pour afficher la requête SQL générée et exécutée par Looker, cliquez sur l'onglet SQL du panneau Données.

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

Notez la similitude avec la formule de requête canonique. Le code SQL Looker présente certaines caractéristiques du code généré par machine (par exemple, COALESCE(users.state,'') AS "_g1"), mais s'adapte toujours à la formule.

Testez d'autres requêtes dans Looker pour vous assurer que la structure des requêtes est toujours la même.

Exécuter du code SQL brut dans SQL Runner de Looker

Looker inclut une fonctionnalité appelée SQL Runner, qui vous permet d'exécuter n'importe quel code SQL sur les connexions de base de données que vous avez configurées dans Looker.

Étant donné que chaque requête générée par Looker génère une commande SQL complète et fonctionnelle, vous pouvez utiliser SQL Runner pour examiner ou manipuler la requête.

Les requêtes SQL brutes exécutées dans SQL Runner produisent le même ensemble de résultats. Si le code SQL contient des erreurs, SQL Runner met en surbrillance l'emplacement de la première erreur dans la commande SQL et inclut la position de l'erreur dans le message d'erreur.

Remarque :Si vos paramètres de connexion spécifient un fuseau horaire de base de données, Looker émet un SET TIMEZONE lors de la connexion, ce qui peut avoir une incidence sur les résultats.

Examiner les composants de la requête dans l'URL étendue

Après avoir exécuté une requête dans Looker, vous pouvez examiner l'URL développée pour voir les composants fondamentaux d'une requête Looker. Commencez par sélectionner Partager dans le menu en forme de roue dentée d'Explorer pour ouvrir le menu Partager les URL.

Remarque :Si vous commencez à partir d'un look, cliquez sur le lien Explorer à partir d'ici du look pour ouvrir la requête dans une exploration.

L'URL développée fournit suffisamment d'informations pour recréer la requête. Par exemple, cette URL développée fournit les informations suivantes:

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

modèle	e_thelook
exploration	events
champs à interroger et à afficher	fields=users.state,users.count
champ et ordre de tri	sorts=users.count+desc
champs et valeurs de filtrage	f[users.created_year]=2020

Comment Looker structure les JOIN

Dans l'exemple de requête précédent, notez que l'exploration orders apparaît dans la clause FROM principale et que les vues jointes apparaissent dans les clauses LEFT JOIN. Les jointures Looker peuvent être écrites de différentes manières, comme expliqué plus en détail sur la page Utiliser des jointures dans LookML.

Les blocs SQL spécifient des clauses SQL personnalisées

Tous les éléments d'une requête Looker ne sont pas générés automatiquement. À un moment donné, le modèle de données doit fournir des détails spécifiques pour que Looker puisse accéder aux tables sous-jacentes et calculer les valeurs dérivées. En LookML, les blocs SQL sont des extraits de code SQL fournis par le modélisateur de données, que Looker utilise pour synthétiser des expressions SQL complètes.

Le paramètre de bloc SQL le plus courant est sql, utilisé dans les définitions de dimension et de mesure. Le paramètre sql spécifie une clause SQL pour faire référence à une colonne sous-jacente ou pour effectuer une fonction d'agrégation. En général, tous les paramètres LookML commençant par sql_ nécessitent une expression SQL, qu'elle qu'en soit la forme. Par exemple: sql_always_where, sql_on et sql_table_name. Pour en savoir plus sur chaque paramètre, consultez la documentation de référence LookML.

Exemples de blocs SQL de dimensions et de mesures

L'exemple de code suivant fournit quelques exemples de blocs SQL pour les dimensions et les mesures. L'opérateur de substitution LookML ($) fait que ces déclarations sql ne ressemblent pas vraiment à du code SQL. Toutefois, une fois la substitution effectuée, la chaîne obtenue est un code purement SQL, que Looker injecte dans la clause SELECT de la requête.

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

Comme illustré dans les deux dimensions de cet exemple, les blocs SQL peuvent utiliser des fonctions prises en charge par la base de données sous-jacente (comme les fonctions MySQL CONCAT et DATEDIFF dans ce cas). Le code que vous utilisez dans les blocs SQL doit correspondre au dialecte SQL utilisé par la base de données.

Exemple de bloc SQL destiné à des tables dérivées

Les tables dérivées utilisent également un bloc SQL pour spécifier la requête déduite de la table. Voici un exemple de table dérivée basée sur 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
  }
}

Exemple de bloc SQL pour filtrer une exploration

Les paramètres LookML sql_always_where et sql_always_having vous permettent de limiter les données disponibles pour une requête en injectant un bloc SQL dans les clauses SQL WHERE ou HAVING. Dans cet exemple, l'opérateur de substitution LookML ${view_name.SQL_TABLE_NAME} est utilisé pour référencer une table dérivée:

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