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 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 moteur SQL puissant 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 tirer parti de 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'un tableau revient essentiellement à comprendre ce qu'est la table et ce qu'on peut 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 comporter 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. L'objectif du paramètre relationship est d'indiquer à Looker s'il faut 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
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 la clé primaire customer_id:
| customer_id | first_name | last_name | consultations |
|---|---|---|---|
| 1 | Amélia | 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 | Amélia | Earhart | 2 |
| 2 | 50,00 $ | 1 | 1 | Amélia | 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 numéro client est représenté plusieurs fois (dans ce cas, il s'agit du client associé à l'ID 1, qui est présent 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 avoir de nombreuses correspondances pour une seule valeur de la table customers. Si customer_id n'est pas unique sur chaque ligne de la table 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, 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 augmente la durée d'exécution, il est préférable d'essayer de remplacer l'un des côtés ou les deux 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. Si ce n'est pas le cas, 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.) - Examinez ensuite 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.
Il est recommandé d'écrire votre expression sql_on en commençant par le tableau de gauche, qui est représenté à gauche du signe égal, et le tableau de droite, qui se trouve du côté droit. 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'ordonner 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 façon dont le paramètre relationship est lu de gauche à droite. Ordonner les champs de cette façon peut également vous aider à distinguer en un coup d'œil la table existante de l'exploration à laquelle vous associez 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 lors de la jointure. Ceci est particulièrement important car les jointures externes gauches 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 cette opération n'est pas effectuée correctement, 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 est associée à des valeurs manquantes 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 | Amélia | 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 | Amélia | Earhart | 2 |
| 2 | 50,00 $ | 1 | 1 | Amélia | 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 tables clés primaires est many_to_one. Toutefois, l'enregistrement nul ajouté nécessite également des agrégations symétriques dans le tableau de gauche. Vous devez donc remplacer le paramètre relationship par many_to_many, car l'exécution de cette jointure perturbe le décompte 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 serait toujours 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, 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 ci-dessus, cela signifie qu'une table serait jointe à la table "client". Dans ce cas, il est préférable de ne regarder que la jointure individuelle en cours d'écriture lors de l'évaluation du paramètre relationship. Looker comprend quand une distribution ramifiée en aval affecte une requête, même si la vue concernée ne se trouve pas dans la jointure qui a réellement créé la distribution ramifiée.
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 (il n'existe toutefois aucune erreur de validateur LookML à ce sujet).
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. Si ce n'est pas le cas, il doit généralement rester many." Cela est vrai, sauf si votre table contient plusieurs colonnes qui ne contiennent 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 primary_key: yes.
Il peut être utile de s'assurer qu'il existe une sorte de règle logicielle qui garantit que l'instruction du paragraphe précédent restera toujours vraie 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 utilisateurs puissent la consulter ultérieurement (avec le lien SQL Runner pour le prouver). Sachez toutefois que Looker confirmera la vérité de l'unicité implicite lorsqu'un champ est désigné comme clé primaire, mais qu'il en va de même pour les autres champs. Il n'appellera tout simplement pas l'algorithme d'agrégation symétrique.