Notez que vous consultez la documentation Looker. Pour accéder à la documentation sur Looker Studio, consultez la page https://support.google.com/looker-studio.

Comment Looker génère SQL

Si vous venez d'utiliser Looker après avoir appris le langage SQL, vous vous demandez probablement comment Looker génère le langage SQL. Fondamentalement, Looker est un outil qui génère des requêtes SQL et les soumet à une connexion de base de données. Looker formule des requêtes SQL basées sur un projet LookML qui décrit la relation entre les tables et les colonnes d'une 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 certains aspects de la génération de code SQL par Looker 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 en détail tous les éléments LookML. Le guide de référence rapide de LookML constitue un bon point de départ pour en savoir plus sur les paramètres LookML.

Afficher la requête

Dans une présentation enregistrée ou 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 Open in SQL Runner (Ouvrir dans l'exécuteur SQL) et Explain in SQL Runner au bas de l'onglet SQL pour afficher votre requête dans SQL Runner ou consulter 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 le post 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 formule ci-dessus. Les expressions de filtre sont spécifiées dans Looker par l'utilisateur pour façonner des requêtes ad hoc. Les expressions de filtre peuvent également être déclarées directement dans LookML pour être appliquées à 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: paramètres dimension et measure à inclure dans la requête, qui renseignent la clause SQL SELECT
filter: expressions de filtre Looker à appliquer à zéro ou plusieurs champs, qui renseignent les clauses SQL WHERE et HAVING.
Ordre de tri: champ utilisé pour le tri et ordre de tri qui remplit 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 de requêtes avec Looker: dans le code SQL généré, dans l'URL qui représente la requête, dans l'API Looker, etc.

Qu'en est-il des affichages spécifiés par les clauses LEFT JOIN ? Les clauses JOIN sont renseignées en fonction de la structure du modèle LookML, qui spécifie la façon dont les vues sont jointes aux explorations. Lors de la création de requêtes SQL, Looker n'inclut les clauses JOIN que si nécessaire. Lorsque les utilisateurs créent une requête dans Looker, ils n'ont pas à préciser comment les tables s'assemblent, car ces informations sont codées dans le modèle : l'un des avantages les plus importants de Looker pour les utilisateurs métier.

Exemple de requête et le code SQL obtenu

Créons une requête dans Looker pour montrer comment elle est générée selon le modèle précédent. Prenons l'exemple d'une boutique en ligne disposant d'une base de données avec deux tables (orders et users) 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 la date de création de la commande (ORDERS Created Date) dans une exploration Looker.

Un tableau de données Exploration affiche le nombre de commandes regroupées par état utilisateur pour les commandes passées au cours des 30 derniers jours.

Pour voir la requête SQL générée et exécutée par Looker, cliquez sur l'onglet SQL dans le 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 similarité avec la formule de requête canonique. Le langage SQL de Looker présente certaines caractéristiques du code généré automatiquement (par exemple, COALESCE(users.state,'') AS "_g1"), mais correspond toujours à la formule.

Faites des tests avec davantage de requêtes dans Looker pour vous prouver que la structure des requêtes reste la même.

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

Looker inclut une fonctionnalité appelée SQL Runner, qui vous permet d'exécuter le code SQL de votre choix 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 étudier ou jouer avec la requête.

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

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 étendue pour voir les composants fondamentaux d'une requête Looker. Commencez par sélectionner Partager dans le menu Outils de l'exploration pour ouvrir le menu Partager les URL.

L'URL étendue fournit suffisamment d'informations pour recréer la requête. Par exemple, cet exemple d'URL étendue 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

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

Comment Looker structure les clauses JOIN

Dans la requête SQL ci-dessus, 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, ce qui est expliqué plus en détail sur la page Utilisation de jointures dans LookML.

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

Les éléments d'une requête Looker ne sont pas tous 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 l'outil de modélisation des 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 de dimensions et de 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 commençant par sql_ attendent 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 sur LookML.

Exemples de blocs SQL de dimensions et de mesures

Vous trouverez ci-dessous quelques exemples de blocs SQL de dimensions et de mesures. L'opérateur de substitution LookML ($) donne l'impression que ces déclarations sql sont différentes de SQL. Cependant, une fois la substitution effectuée, la chaîne obtenue est 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 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 le cas présent). 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. 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 permettant de 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} permet de 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});;
}