Comment Looker génère le langage SQL

Si vous utilisez Looker en arrière-plan, vous souhaitez probablement savoir comment Looker génère le langage SQL. Fondamentalement, Looker est un outil qui génère des requêtes SQL et les envoie via une connexion à une base de données. Looker formule les requêtes SQL en fonction d'un projet LookML qui décrit la relation entre les tables et les colonnes de la base de données. En comprenant comment Looker génère des requêtes, vous pouvez mieux comprendre comment votre code LookML se traduit par des requêtes SQL efficaces.

Chaque paramètre LookML contrôle certains aspects de la façon dont Looker génère le langage 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 SQL par Looker, mais ne décrit pas tous les éléments LookML en détail. La documentation de référence sur LookML constitue un bon point de départ pour en savoir plus sur les paramètres LookML.

Afficher la requête

Dans un aperçu ou une exploration enregistrés, vous pouvez utiliser l'onglet SQL de la section Données pour voir ce que Looker envoie à la base de données pour obtenir les données. Vous pouvez également utiliser les liens en bas pour afficher votre requête dans l'exécuteur SQL ou consulter le plan d'explication de la base de données pour la requête. Pour en savoir plus sur l'exécuteur SQL, consultez la page de documentation de base sur l'exécuteur SQL. Pour en savoir plus sur l'optimisation d'une requête avec SQL Runner, consultez l'article Optimiser une requête SQL avec la campagne 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 formule ci-dessus. Les expressions de filtre sont spécifiées dans Looker par l'utilisateur pour façonner les requêtes ad hoc. Les expressions de filtre peuvent également être déclarées directement dans LookML pour s'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 la formule ci-dessus.

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 l'exploration à interroger, qui remplit la clause SQL FROM
  • Champs: les paramètres dimension et measure à inclure dans la requête, qui renseigne la clause SQL SELECT
  • filter: expressions de filtre Looker à appliquer à plusieurs champs, qui renseignent les clauses SQL WHERE et HAVING
  • Ordre de tri: champ permettant d'effectuer le tri et ordre de tri qui renseigne la clause SQL ORDER BY

Il s'agit précisément des é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 de requêtes avec Looker: en SQL généré, dans l'URL qui représente la requête, dans l'API Looker, etc.

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 la manière dont les vues sont associées aux explorations. Lors de la création de requêtes SQL, Looker n'inclut des clauses JOIN que si nécessaire. Lorsque les utilisateurs créent une requête dans Looker, ils n'ont pas besoin de spécifier la manière dont les tables sont associées, car ces informations sont encodées dans le modèle. C'est l'un des principaux avantages de Looker pour les utilisateurs professionnels.

Exemple de requête et requête SQL

Créons une requête dans Looker pour montrer comment elle est générée selon le modèle ci-dessus. Prenons l'exemple d'une boutique en ligne avec des tables pour suivre les utilisateurs et les commandes. Les champs de champs et les relations entre les tables sont indiqués ci-dessous.

Recherchez le nombre de commandes (nombre de commandes) regroupées par État (état USERS) et filtrez-les par date de création de la commande (date de création de la commande).

Vous trouverez ci-dessous le résultat de la requête sur la page "Explorer" de Looker.

Cliquez sur l'onglet SQL pour afficher le code SQL généré et exécuté par Looker.

Notez la similarité avec la formule canonique indiquée ci-dessus. Le langage SQL de Looker présente certaines caractéristiques de code généré automatiquement (par exemple, COALESCE(users.state,'') AS "_g1"), mais il correspond toujours à la formule.

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>

Testez davantage de requêtes dans Looker pour vérifier que la structure des requêtes est toujours la même.

Exécuter du code SQL brut dans l'exécuteur SQL de Looker

Looker inclut une fonctionnalité appelée exécuteur SQL qui vous permet d'exécuter le langage de votre choix sur les connexions à la 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 l'exécuteur SQL pour l'examiner ou y jouer.

Les requêtes SQL brutes exécutées dans l'exécuteur SQL génèrent le même ensemble de résultats

Si le code SQL contient des erreurs, l'exécuteur SQL indique l'emplacement de la première erreur dans la commande SQL et indique sa position dans le message d'erreur.

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

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

Si vous démarrez à partir d'un Look, cliquez sur le lien Explorer depuis ce site pour ouvrir la requête dans Explorer.

La fenêtre Partager les URL s'affiche. Elle contient l'URL étendue:

L'URL fournit suffisamment d'informations pour recréer la requête. Par exemple, pour une URL de partage développée comme suit:

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

Cette URL fournit les informations suivantes:

model e_thelook
exploration events
champs à interroger et à afficher fields=users.state,users.count
trier le champ et l'ordre sorts=users.count+desc
filtrer les champs et les valeurs f[users.created_year]=2020

Comment Looker structure les jointures

Dans le langage SQL de la requête ci-dessus, notez que l'exploration orders apparaît dans la clause principale FROM et que les vues associées 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. Dans LookML, les blocs SQL sont des extraits de code SQL fournis par le modèle 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. Il est utilisé dans les définitions des dimensions et des mesures. Le paramètre sql spécifie une clause SQL permettant de référencer une colonne sous-jacente ou d'exécuter une fonction d'agrégation. En général, tous les paramètres LookML qui commencent par sql_ attendent une expression SQL sous une forme ou une autre. 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 sur LookML.

Exemples de blocs SQL de dimensions et de mesures

Vous trouverez ci-dessous quelques exemples de blocs SQL pour les dimensions et les mesures. Contrairement à SQL, l'opérateur de substitution LookML ($) fait apparaître ces déclarations sql de façon trompeuse. Cependant, une fois la substitution effectuée, la chaîne qui en résulte est du code SQL pur, 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 indiqué dans les deux dernières dimensions ci-dessus, les blocs SQL peuvent utiliser des fonctions compatibles avec la base de données sous-jacente (telles que 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 qui les dérivée. En voici un exemple :

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