Cette page s'adresse à toute personne qui tente d'utiliser LookML pour créer une exploration dans Looker. La page sera plus facile à comprendre si vous maîtrisez SQL, en particulier si vous comprenez la différence entre les jointures internes et externes. Pour obtenir une explication concise des différences entre les jointures internes et externes, consultez cet article de w3schools sur les jointures SQL.
Looker peut être un puissant moteur SQL pour votre entreprise. La modélisation abstraite en LookML permet aux équipes informatiques et aux données d'élaborer des règles générales qui sont toujours vraies, ce qui permet aux analystes commerciaux d'élaborer des requêtes dans la nature qui sont toujours correctes, même si l'équipe chargée des données n'avait jamais prévu d'en avoir besoin. Le principal moteur de cette fonctionnalité est l'algorithme d'agrégation symétrique, qui résout un problème à l'échelle du secteur lié aux jointures SQL. Toutefois, pour tirer parti de l'algorithme, deux opérations doivent être effectuées correctement: les clés primaires doivent être exactes dans chaque vue contenant une mesure (généralement toutes), et les paramètres relationship doivent être corrects dans chaque jointure.
Clés primaires
À bien des égards, comprendre la clé primaire d'un tableau revient essentiellement à comprendre ce qu'est le tableau et ce qui pourrait en être fait. La seule chose à faire est que la colonne (ou l'ensemble de colonnes concaténées) que vous choisissez comme clé primaire ne doit pas comporter de valeurs répétées.
Paramètre relationship
Maintenant que vous avez validé vos clés primaires, vous pouvez déterminer la valeur correcte pour le paramètre relationship de la jointure. L'objectif du paramètre relationship est d'indiquer à Looker si les agrégations symétriques doivent être appelées lorsque la jointure est écrite dans une requête SQL. Une approche possible serait de dire à Looker de toujours les appeler, ce qui donnerait toujours des résultats précis. Cependant, cela a un coût en termes de performances. Il est donc préférable d'utiliser les agrégations symétriques judicieusement.
Le processus permettant de déterminer la valeur correcte diffère légèrement entre les jointures internes et externes.
Jointures internes
Par exemple, supposons que vous ayez une table de commandes avec une clé primaire de order_id:
| order_id | amount | customer_id |
|---|---|---|
| 1 | $25.00 | 1 |
| 2 | $50.00 | 1 |
| 3 | $75.00 | 2 |
| 4 | $35.00 | 3 |
Supposons que vous disposiez également d'une table de clients avec une clé primaire de customer_id:
| customer_id | first_name | last_name | visites |
|---|---|---|---|
| 1 | Amélie | Casque audio | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
Vous pouvez joindre ces tables sur le champ customer_id, qui est présent dans les deux tables. Cette jointure serait représentée dans LookML comme suit:
explore: orders {
join: customers {
type: inner
sql_on: ${orders.customer_id} = ${customers.customer_id} ;;
relationship: many_to_one
}
}
Le résultat de cette jointure LookML peut être représenté sous la forme d'une table jointe unique, comme suit:
| order_id | amount | customer_id | customer_id | first_name | last_name | visites |
|---|---|---|---|---|---|---|
| 1 | $25.00 | 1 | 1 | Amélie | Casque audio | 2 |
| 2 | $50.00 | 1 | 1 | Amélie | Casque audio | 2 |
| 3 | $75.00 | 2 | 2 | Bessie | Coleman | 2 |
| 4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
Ici, la relation many_to_one fait référence au nombre de fois où une valeur du champ de jointure (customer_id) est représentée dans chaque table. Dans le tableau orders (tableau de gauche), un seul ID client est représenté plusieurs fois (dans ce cas, il s'agit du client avec l'ID 1, qui figure sur plusieurs lignes).
Dans la table customers (tableau de droite), chaque ID client n'est représenté qu'une seule fois, car customer_id est la clé primaire de ce tableau. Par conséquent, les enregistrements de la table orders peuvent avoir plusieurs correspondances pour une même valeur dans la table customers. Si customer_id n'est pas unique sur chaque ligne de la table customers, la relation est many_to_many.
Vous pouvez suivre ces étapes pour déterminer par programmation la valeur de relation correcte en vérifiant les clés primaires:
- Commencez par écrire
many_to_manycomme relation. Tant que vos clés primaires sont correctes, cela produira toujours des résultats précis, car Looker déclenchera toujours l'algorithme d'agrégation symétrique et appliquera la précision. Toutefois, comme l'algorithme complique les requêtes et ajoute du temps d'exécution, il est utile d'essayer de remplacer l'un des deux côtés (ou les deux) paroneau lieu demany. - Examinez le ou les champs qui se trouvent dans votre clause
sql_ondans la table de gauche. Si le ou les champs forment la clé primaire de la table de gauche, vous pouvez remplacer le côté gauche du paramètrerelationshipparone. Si ce n'est pas le cas, il doit généralement restermany. Pour en savoir plus sur un cas particulier, consultez la section Éléments à prendre en compte plus loin sur cette page. - Examinez ensuite le ou les champs représentant la table de droite dans la clause
sql_on. Si le ou les champs forment la clé primaire de la table de droite, vous pouvez remplacer le côté droit parone.
Il est recommandé d'écrire votre expression sql_on en commençant par le tableau de gauche, représenté à gauche du signe égal, et par le tableau de droite, qui se trouve à droite. L'ordre des conditions du paramètre sql_on n'a pas d'importance, sauf si l'ordre est pertinent pour le dialecte SQL de votre base de données. Même si le paramètre sql_on ne nécessite pas que vous triiez les champs de cette manière, vous pouvez déterminer la relation en organisant les conditions sql_on de sorte que les côtés gauche et droit du signe égal correspondent à la lecture du paramètre relationship de gauche à droite. En ordonnant les champs de cette manière, vous pouvez également repérer plus facilement d'un simple coup d'œil la table existante de l'exploration à laquelle vous joignez la nouvelle table.
Jointures externes
Pour les jointures externes, vous devez également tenir compte du fait qu'une distribution ramifiée peut se produire lorsque des enregistrements nuls sont ajoutés au cours de la jointure. C'est d'autant plus important que les jointures externes gauches sont définies par défaut dans Looker. Bien que les enregistrements nuls n'affectent pas les sommes ni les moyennes, ils affectent la façon dont Looker exécute une mesure de type: count. Si cette opération est incorrecte, les enregistrements nuls seront comptés (ce qui n'est pas souhaitable).
Dans une jointure externe complète, des enregistrements nuls peuvent être ajoutés à l'une ou l'autre des tables si sa clé de jointure ne contient pas toutes les valeurs présentes dans l'autre table. Ce processus est illustré dans l'exemple suivant, qui implique une table orders:
| order_id | amount | customer_id |
|---|---|---|
| 1 | $25.00 | 1 |
| 2 | $50.00 | 1 |
| 3 | $75.00 | 2 |
| 4 | $35.00 | 3 |
Dans cet exemple, supposons que vous ayez également la table customers suivante:
| customer_id | first_name | last_name | visites |
|---|---|---|---|
| 1 | Amélie | Casque audio | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
| 4 | Charles | K. Yeager | 3 |
Une fois ces tables jointes, la table jointe peut être représentée comme suit:
| order_id | amount | customer_id | customer_id | first_name | last_name | visites |
|---|---|---|---|---|---|---|
| 1 | $25.00 | 1 | 1 | Amélie | Casque audio | 2 |
| 2 | $50.00 | 1 | 1 | Amélie | Casque audio | 2 |
| 3 | $75.00 | 2 | 2 | Bessie | Coleman | 2 |
| 4 | $35.00 | 3 | 3 | Wilbur | Wright | 4 |
| null | null | null | 4 | Charles | K. Yeager | 3 |
Tout comme dans une jointure interne, la relation entre les clés primaires des tables est many_to_one. Cependant, l'enregistrement nul ajouté force le besoin d'agrégations symétriques également dans la table de gauche. Vous devez donc remplacer le paramètre relationship par many_to_many, car l'exécution de cette jointure perturbe les décomptes dans la table de gauche.
Si cet exemple avait été une jointure externe gauche, la ligne nulle n'aurait pas été ajoutée et l'enregistrement client supplémentaire aurait été supprimé. Dans ce cas, la relation est toujours many_to_one. Il s'agit de la valeur par défaut de Looker, car nous partons du principe que la table de base définit l'analyse. Dans ce cas, vous analysez les commandes, pas les clients. Si le tableau des clients se trouvait à gauche, la situation serait différente.
Jointures à plusieurs niveaux
Dans certaines explorations, la table de base est jointe à une ou plusieurs vues qui, à leur tour, doivent être jointes à une ou plusieurs vues supplémentaires. Dans l'exemple ici, cela signifie qu'une table serait jointe à la table customer. Dans ce cas, il est préférable de n'examiner que la jointure individuelle en cours d'écriture lors de l'évaluation du paramètre relationship. Looker comprend quand une sortie ramifiée en aval affecte une requête, même si la vue concernée ne fait pas partie de la jointure à l'origine de la sortie.
Comment Looker m'aide-t-elle ?
Looker propose des mécanismes qui permettent de s'assurer que la valeur de la relation est correcte. L’une est la vérification de l’unicité de la clé primaire. En cas de sortie ramifiée et d'agrégations symétriques nécessaires pour calculer une mesure, Looker vérifie l'unicité de la clé primaire exploitée. S'il n'est pas unique, une erreur s'affiche au moment de l'exécution de la requête (toutefois, il n'y a pas d'erreur de validation LookML pour cela).
De plus, si Looker n'a aucun moyen de gérer une sortie ramifiée (généralement parce qu'aucune clé primaire n'est indiquée), aucune mesure n'apparaîtra dans l'exploration depuis cette vue. Pour corriger cela, il vous suffit de désigner un champ comme clé primaire pour permettre à vos mesures d'entrer dans l'exploration.
Éléments à prendre en compte
Prise en charge des dialectes pour les agrégations symétriques
Looker peut se connecter à certains dialectes qui ne prennent pas en charge les agrégations symétriques. Vous pouvez consulter la liste des dialectes et leur compatibilité avec les agrégations symétriques sur la page de documentation de symmetric_aggregates.
Cas particulier
La section Jointure interne mentionnée précédemment sur cette page indique que, pour déterminer la valeur de relation correcte, vous devez examiner le ou les champs qui se trouvent dans votre clause sql_on à partir du tableau de gauche : "Si le ou les champs forment la clé primaire de la table de gauche, vous pouvez remplacer le côté gauche du paramètre relationship par one. Si ce n'est pas le cas, il doit généralement rester au format many." C'est vrai, sauf si votre table contient plusieurs colonnes ne comportant aucun enregistrement répété. Dans ce cas, vous pouvez traiter n'importe quelle colonne de ce type comme s'il s'agissait d'une clé primaire lors de la formulation de votre relation, même s'il ne s'agit pas de la colonne désignée comme primary_key: yes.
Il peut être utile de s'assurer qu'il existe une sorte de règle logicielle en place qui garantit que l'énoncé du paragraphe précédent restera toujours vrai pour la colonne que vous désignez. Si tel est le cas, traitez-la comme telle et notez sa propriété spéciale dans le fichier de vue pour que d'autres utilisateurs puissent la consulter à l'avenir (avec le lien SQL Runner pour le prouver). Sachez toutefois que Looker confirmera la vérité concernant l'unicité implicite lorsqu'un champ est désigné comme clé primaire, mais pas pour les autres champs. Elle n'appellera tout simplement pas l'algorithme d'agrégation symétrique.