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 le langage SQL, en particulier si vous comprenez la différence entre les jointures internes et externes. Pour 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 dans LookML permet aux équipes de données et IT de créer des règles générales qui sont toujours vraies, ce qui permet aux analystes métier de créer des requêtes qui sont toujours correctes, même si l'équipe de données n'a jamais anticipé le besoin de ces requêtes. 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, deux éléments doivent être correctement effectués pour exploiter l'algorithme: 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'une table revient essentiellement à comprendre ce qu'est la table et ce que vous pouvez en faire. La seule condition requise est que la colonne (ou l'ensemble de colonnes concaténées) que vous choisissez comme clé primaire ne doit pas contenir de valeurs répétées.
Paramètre relationship
Maintenant que vous avez vérifié vos clés primaires, vous pouvez déterminer la valeur correcte pour le paramètre relationship de la jointure. Le paramètre relationship permet d'indiquer à Looker s'il doit appeler des agrégations symétriques lorsque la jointure est écrite dans une requête SQL. Une approche possible consiste à demander à Looker de toujours les appeler, ce qui produira 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 avec parcimonie.
Le processus de détermination de la valeur correcte est légèrement différent entre les jointures internes et externes.
Jointures internes
Imaginons par exemple que vous disposiez d'une table de commandes avec une clé primaire 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 customer_id:
| customer_id | first_name | last_name | consultations |
|---|---|---|---|
| 1 | Amelia | Earhart | 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 seule table jointe, comme suit:
| order_id | amount | customer_id | customer_id | first_name | last_name | consultations |
|---|---|---|---|---|---|---|
| 1 | 25,00 $ | 1 | 1 | Amelia | Earhart | 2 |
| 2 | 50,00 $ | 1 | 1 | Amelia | Earhart | 2 |
| 3 | 75,00 $ | 2 | 2 | Bessie | Coleman | 2 |
| 4 | 35,00 $ | 3 | 3 | Wilbur | Wright | 4 |
La relation many_to_one fait ici 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 apparaît sur plusieurs lignes).
Dans la table customers (table de droite), chaque numéro client n'est représenté qu'une seule fois, car customer_id est la clé primaire de cette table. Par conséquent, les enregistrements de la table orders peuvent présenter de nombreuses correspondances pour une seule valeur dans la table customers. Si customer_id n'était pas unique dans chaque ligne du tableau customers, la relation serait many_to_many.
Pour déterminer de manière programmatique la valeur de relation correcte en vérifiant les clés primaires, procédez comme suit:
- Commencez par indiquer
many_to_manycomme relation. Tant que vos clés primaires sont correctes, vous obtiendrez 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 préférable d'essayer de remplacer l'un ou les deux côtés paroneau lieu demany. - Examinez le ou les champs de votre clause
sql_ondans le tableau de gauche. Si le ou les champs constituent la clé primaire de la table de gauche, vous pouvez remplacer le côté gauche du paramètrerelationshipparone. Sinon, il doit généralement restermany. (Pour en savoir plus sur un cas particulier, consultez la section À prendre en compte plus loin sur cette page.) - Ensuite, examinez le ou les champs représentant votre table de droite dans la clause
sql_on. Si le ou les champs constituent la clé primaire du tableau de droite, vous pouvez remplacer le côté droit parone.
Nous vous recommandons d'écrire votre phrase sql_on en commençant par la table de gauche, qui se trouve à gauche du signe égal, et la table de droite, qui se trouve à droite. L'ordre des conditions dans le 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 d'organiser les champs de cette manière, vous pouvez déterminer la relation en disposant les conditions sql_on de sorte que les côtés gauche et droit du signe égal correspondent à la façon dont le paramètre relationship est lu de gauche à droite. Cette organisation des champs peut également vous aider à identifier plus facilement, d'un 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 ramification peut se produire lorsque des enregistrements nuls sont ajoutés lors de la jointure. Cela est particulièrement important, car les jointures externes à gauche sont utilisées par défaut dans Looker. Bien que les enregistrements nuls n'affectent pas les sommes ni les moyennes, ils ont un impact sur la façon dont Looker exécute une mesure de type: count. Si vous ne le faites pas correctement, les enregistrements nuls seront comptabilisés (ce qui est indésirable).
Dans une jointure externe complète, des enregistrements NULL peuvent être ajoutés à l'une ou l'autre des tables si des valeurs manquent dans sa clé de jointure qui existent dans l'autre table. Vous en trouverez l'illustration 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 |
Imaginons que vous disposiez également de la table customers suivante:
| customer_id | first_name | last_name | consultations |
|---|---|---|---|
| 1 | Amelia | Earhart | 2 |
| 2 | Bessie | Coleman | 2 |
| 3 | Wilbur | Wright | 4 |
| 4 | Charles | 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 | consultations |
|---|---|---|---|---|---|---|
| 1 | 25,00 $ | 1 | 1 | Amelia | Earhart | 2 |
| 2 | 50,00 $ | 1 | 1 | Amelia | Earhart | 2 |
| 3 | 75,00 $ | 2 | 2 | Bessie | Coleman | 2 |
| 4 | 35,00 $ | 3 | 3 | Wilbur | Wright | 4 |
| null | null | null | 4 | Charles | Yeager | 3 |
Tout comme dans une jointure interne, la relation entre les clés primaires des tables est many_to_one. Toutefois, l'enregistrement nul ajouté nécessite également des agrégations symétriques dans le tableau de gauche. Par conséquent, vous devez remplacer le paramètre relationship par many_to_many, car effectuer cette jointure perturbe les totaux du tableau de gauche.
S'il s'agissait d'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 reste many_to_one. Il s'agit de la valeur par défaut de Looker, car on suppose que la table de base définit l'analyse. Dans ce cas, vous analysez les commandes, et non les clients. Si le tableau des clients se trouvait à gauche, la situation serait différente.
Jointures multiniveaux
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 ci-dessus, cela signifie qu'une table serait jointe à la table "client". Dans ce cas, il est préférable de n'examiner que la jointure individuelle écrite lors de l'évaluation du paramètre relationship. Looker comprendra quand un fanage en aval affecte une requête, même si la vue concernée ne figure pas dans la jointure qui a créé le fanage.
En quoi Looker peut-il m'aider ?
Looker dispose de mécanismes pour vous aider à vous assurer que la valeur de la relation est correcte. L'une d'elles consiste à vérifier l'unicité de la clé primaire. Chaque fois qu'il y a un fanage et que des agrégations symétriques sont nécessaires pour calculer une mesure, Looker vérifie l'unicité de la clé primaire utilisée. S'il n'est pas unique, une erreur s'affiche au moment de l'exécution de la requête (mais aucune erreur de l'outil de validation LookML n'est générée).
De plus, si Looker ne peut pas gérer un fanage (généralement parce qu'aucune clé primaire n'est indiquée), aucune mesure n'apparaît dans l'exploration à partir de cette vue. Pour résoudre ce problème, il vous suffit de désigner un champ comme clé primaire pour permettre à vos mesures d'accéder à 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 prise en charge des agrégations symétriques sur la page de documentation symmetric_aggregates.
Cas particulier
La section Jointure interne plus haut sur cette page indique que, pour déterminer la valeur de relation correcte, vous devez examiner le ou les champs de la clause sql_on de la table de gauche : "Si le ou les champs constituent la clé primaire de la table de gauche, vous pouvez remplacer le côté gauche du paramètre relationship par one. Sinon, il doit généralement rester un many." Cela est vrai, sauf si votre table contient plusieurs colonnes sans enregistrements en double. Dans ce cas, vous pouvez traiter toute colonne de ce type comme s'il s'agissait d'une clé primaire lorsque vous formulez votre relation, même si ce n'est pas la colonne désignée par primary_key: yes.
Il peut être utile de s'assurer qu'une sorte de règle logicielle est en place pour garantir que l'énoncé du paragraphe précédent restera toujours vrai pour la colonne que vous désignez. Si c'est le cas, traitez-la comme telle et notez sa propriété spéciale dans le fichier de vue pour que d'autres puissent s'y référer plus tard (avec un lien vers SQL Runner pour le prouver). Notez toutefois que Looker confirme la validité de l'unicité implicite lorsqu'un champ est désigné comme clé primaire, mais qu'il ne fait pas de même pour les autres champs. Il n'appellera tout simplement pas l'algorithme d'agrégation symétrique.