Si vous utilisez Looker avec une formation SQL, vous vous demandez probablement comment Looker génère ce langage. Fondamentalement, Looker est un outil qui génère des requêtes SQL et les soumet à une connexion à une 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 de la base de données. En comprenant comment Looker génère les requêtes, vous comprendrez mieux comment votre code LookML se traduit par des requêtes SQL efficaces.
Chaque paramètre LookML contrôle un aspect de la manière dont Looker génère le code SQL, en modifiant la structure, le contenu ou le comportement de la requête. Cette page décrit les principes de la génération de code SQL par Looker, mais elle ne couvre pas en détail tous les éléments LookML. La page de référence rapide de LookML pour en savoir plus sur les paramètres LookML constitue un bon point de départ.
Afficher la requête
Dans une présentation ou une exploration enregistrée, 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 consulter le plan d'explication de la base de données.
Pour en savoir plus sur l'exécuteur SQL, consultez la page de documentation Principes de base de l'exécuteur SQL. Pour en savoir plus sur l'optimisation d'une requête à l'aide de SQL Runner, consultez le post destiné à la communauté Comment optimiser SQL avec EXPLAIN.
Forme canonique d'une requête Looker
Les requêtes SQL de Looker se présentent toujours sous 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. Les expressions de filtre peuvent également être déclarées 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 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 cibleexplore
: nom de l'exploration à interroger, qui renseigne la clause SQLFROM
.- Champs: paramètres
dimension
etmeasure
à inclure dans la requête, qui renseignent la clause SQLSELECT
filter
: expressions de filtre Looker à appliquer à zéro ou plusieurs champs, qui renseignent les clauses SQLWHERE
etHAVING
.- Ordre de tri: champ à utiliser comme critère de tri et ordre de tri, qui renseigne la clause SQL
ORDER BY
Ces paramètres sont précisément les é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, comme dans le code SQL généré, dans l'URL qui représente 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 inclut les clauses JOIN
uniquement lorsque cela est nécessaire. Lorsque les utilisateurs créent une requête dans Looker, ils n'ont pas besoin de spécifier la façon 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 le résultat SQL
Créons une requête dans Looker pour montrer comment elle est générée en fonction du modèle précédent. Prenons l'exemple d'un magasin en ligne dont la base de données comporte 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) |
Retrouvons le nombre de commandes (Nombre de commandes) regroupées par état (État UTILISATEUR) et filtrées par date de création de la commande (Date de création des Commandes) 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 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 similitude 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.
Testez davantage de requêtes dans Looker pour vous prouver à vous-même 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 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 l'exécuteur SQL pour étudier la requête ou jouer avec elle.
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.
Examiner les composants de 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 représenté par une roue dentée pour ouvrir le menu Partager des URL.
L'URL étendue fournit suffisamment d'informations pour recréer la requête. 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
modèle | e_thelook |
exploration | events |
à interroger et à afficher | fields=users.state,users.count |
champ de tri et ordre | sorts=users.count+desc |
champs et valeurs de filtre | f[users.created_year]=2020 |
Comment Looker structure les JOINTURES
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, qui sont expliquées plus en détail sur la page Utiliser des jointures dans LookML.
Les blocs SQL spécifient les 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 le créateur de modèles de données, que Looker utilise pour synthétiser des expressions SQL complètes.
sql
est le paramètre de bloc SQL le plus courant. Il est utilisé dans les définitions de dimensions et de mesures. Le paramètre sql
spécifie une clause SQL pour référencer une colonne sous-jacente ou pour exécuter une fonction d'agrégation. En général, tous les paramètres LookML commençant par sql_
attendent une expression SQL d'une certaine forme. Par exemple: sql_always_where
, sql_on
et sql_table_name
. Consultez la documentation de référence LookML pour en savoir plus sur chaque paramètre.
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 apparaître ces déclarations sql
de façon trompeuse par rapport à SQL. Cependant, une fois la substitution effectuée, la chaîne obtenue est en 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 le montrent les deux dernières dimensions de cet exemple, 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 qui extrait 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 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}
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});;
}