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

Intégration de SQL et référence aux objets LookML

Pour écrire un code LookML efficace, vous devez pouvoir faire référence à des dimensions, mesures, vues ou tables dérivées existantes, même si elles sont en dehors de la portée actuelle. Vous devez également référencer des colonnes dans la table sous-jacente et utiliser les appels de fonction du dialecte de la base de données pour manipuler ces valeurs.

Opérateur de substitution ($)

L'opérateur de substitution $ rend le code LookML plus réutilisable et modulaire. Vous pouvez ainsi référencer d'autres vues et tables dérivées, colonnes dans une table SQL, ou dimensions et mesures LookML. Cette méthode présente deux avantages : Tout d'abord, vous avez peut-être déjà dû définir une dimension ou une mesure très complexe, et vous n'aurez pas besoin d'écrire à nouveau la complexité. Deuxièmement, si vous modifiez une dimension ou une mesure, vous pouvez reporter ces changements sur tous les éléments reposant sur cette dimension ou mesure.

Vous pouvez vous servir de l'opérateur de substitution de plusieurs façons :

${TABLE}.column_name fait référence à une colonne de la table associée à la vue sur laquelle vous travaillez. Exemple :

dimension: customer_id {
  type: number
  sql: ${TABLE}.customer_id ;;
}

${field_name} fait référence à une dimension ou à une mesure dans la vue sur laquelle vous travaillez. Exemple :

measure: total_population {
  type: sum
  sql: ${population} ;;
}

${view_name.field_name} fait référence à une dimension ou à une mesure à partir d'une autre vue. Exemple :

dimension: lifetime_orders {
  type: number
  sql: ${user_order_facts.lifetime_orders} ;;
}

${view_name.SQL_TABLE_NAME} fait référence à une autre vue ou à une table dérivée. Notez que dans cette référence, SQL_TABLE_NAME est une chaîne littérale. Vous n'avez pas besoin de la remplacer par quoi que ce soit. Exemple :

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});;
}

${view_name.SQL_TABLE_NAME} ne fonctionne pas avec le paramètre sql_trigger utilisé avec les groupes de données.

Portée et appellation

Vous pouvez nommer des explorations, des vues, des champs et des ensembles, ces identifiants Looker étant écrits sans guillemets.

Les champs et ensembles LookML ont des noms complets et des noms courts:

Les noms complets sont au format <view>.<field-name | set-name>. La partie gauche indique la portée, c'est-à-dire la vue contenant le champ ou l'ensemble. La partie droite correspond au nom du champ ou de l'ensemble à proprement parler.
Les noms courts se présentent simplement au format <field-name | set-name>, sans point de séparation. Looker développe les noms abrégés en noms complets en utilisant la portée dans laquelle ils sont employés.

Voici un exemple illustrant plusieurs formes de nom et de portée. Ce groupe de champs, quoique peu réaliste, est représentatif des différentes expressions de portée possibles.

view: orders {                   # "orders" becomes the containing scope
  measure: count {               # short name, equivalent to orders.count
    type: count
  }
  dimension: customer_id {       # short name, equivalent to orders.customer_id
    type: number
    sql: ${TABLE}.customer_id ;;
  }
  dimension: customer_address {  # short name, equivalent to orders.customer_address
    sql: ${customer.address} ;;  # full name, references a field defined in the "customer" view
  }
  set: drill_fields {            # short name, equivalent to orders.drill_fields
    fields: [
      count,                     # short name, equivalent to orders.count
      customer.id                # full name, references a field defined in the "customer" view
    ]
  }
}

Dans la déclaration dimension: customer_address ci-dessus, notez que la vue sous-jacente du bloc SQL (customer) est différente de la vue englobante (orders). Cela peut s'avérer utile lorsque vous devez comparer des champs entre deux vues différentes.

Lorsqu'une vue (vue "A") fait référence à un champ défini dans une autre vue (vue "B"), vous devez tenir compte des points suivants:

Le fichier de vue B doit être inclus dans le même modèle que la vue A, à l'aide du paramètre include.
La vue B doit être jointe à la vue A dans une ou plusieurs explorations. Consultez la page Utiliser des jointures dans LookML pour en savoir plus sur les jointures.

Dialecte SQL

Looker est compatible avec de nombreux types de bases de données tels que MySQL, Postgres, Redshift, BigQuery, etc. Chaque base de données accepte un ensemble de fonctionnalités légèrement différent avec des noms de fonction différents, appelés dialecte SQL.

LookML est compatible avec tous les dialectes SQL, sans distinction. Toutefois, vous devez inclure des expressions de code SQL (appelées blocs SQL) dans certains paramètres LookML. En effet, avec ces paramètres, Looker transmet directement l'expression SQL à la base de données, de sorte que vous devez utiliser le dialecte SQL correspondant à cette dernière. Par exemple, si vous utilisez une fonction SQL, il doit s'agir d'une fonction prise en charge par votre base de données.

Blocs SQL

Certains paramètres LookML imposent de fournir des expressions SQL brutes afin que Looker puisse comprendre comment extraire des données de la base.

Les paramètres LookML qui commencent par sql_ s'attendent à une expression SQL. Par exemple: sql_always_where, sql_on et sql_table_name. Le paramètre LookML le plus courant pour les blocs SQL est sql. Il est utilisé dans les définitions des champs de dimensions et de mesures pour spécifier l'expression SQL qui définit la dimension ou la mesure.

Le code spécifié dans un bloc SQL peut être un simple nom de champ ou une instruction de sous-sélection corrélée sophistiquée. Le contenu peut être relativement complexe et répondre à pratiquement tous vos besoins pour exprimer une logique de requête personnalisée en SQL brut. Notez que le code que vous utilisez dans les blocs SQL doit correspondre au dialecte SQL utilisé par la base de données.

Exemples de blocs SQL de dimensions et de mesures

Vous trouverez ci-dessous plusieurs exemples de blocs SQL de dimensions et de mesures. Contrairement à SQL, l'opérateur de substitution LookML ($) peut faire apparaître ces déclarations sql de manière trompeuse. Cependant, une fois la substitution effectuée, la chaîne qui en résulte est du code 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: ${order_items.cost} ;;   # Specify the field that you want to average
}
dimension: name {
  sql: CONCAT(${first_name}, ' ', ${last_name}) ;;
}
dimension: days_in_inventory {
  type: int
  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 cet exemple).

Exemple de bloc SQL contenant une sous-sélection corrélée

Vous pouvez placer n'importe quelle instruction SQL dans le bloc SQL d'un champ, y compris dans une sous-sélection corrélée. En voici un exemple :

view: customers {
  dimension: id {
    primary_key: yes
    sql: ${TABLE}.id ;;
  }
  dimension: first_order_id {
    sql: (SELECT MIN(id) FROM orders o WHERE o.customer_id=customers.id) ;;
         # correlated subselect to derive the value for "first_order_id"
  }
}

Exemple de bloc SQL destiné à des tables dérivées

Les tables dérivées utilisent le bloc SQL pour spécifier la requête déduite de la table. En voici un exemple :

view: user_order_facts {
  derived_table: {
    sql:            # Get the number of orders for each user
      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
  }
}

Références des types de champs LookML

Lorsque vous référencez un champ LookML existant dans un autre champ, vous pouvez demander à Looker de traiter le champ référencé comme un type de données spécifique à l'aide du double point (::), suivi du type souhaité. Par exemple, si vous référencez la dimension orders.created_date dans un autre champ, vous pouvez utiliser la syntaxe ${orders.created_date::date} pour que le champ created_date soit traité comme un champ de date dans le langage SQL généré par Looker, au lieu d'être converti en chaîne.

Le type de données que vous pouvez utiliser dans une référence dépend de celui utilisé dans le champ d'origine que vous référencez actuellement. Par exemple, si vous faites référence à un champ de chaîne, le seul type de données que vous pouvez spécifier est ::string. Voici la liste complète des références de types de champs autorisés et que vous pouvez utiliser pour chaque type de champ :

Dans une référence à un champ de chaîne, vous pouvez utiliser ::string.
Dans une référence à un champ numérique, vous pouvez utiliser ::string et ::number.
Dans une référence à un champ de date ou d'heure, vous pouvez utiliser ::string, ::date et ::datetime.
Les références utilisant ::string et ::date renvoient des données dans le fuseau horaire de la requête, tandis que les références utilisant ::datetime renvoient des données dans le fuseau horaire de la base de données.
Dans une référence à un champ "yesno", vous pouvez utiliser ::string, ::number et ::boolean.
Les références de champs utilisant le type ::boolean ne sont pas disponibles pour les dialectes de base de données qui ne sont pas compatibles avec le type de données booléen.
Dans une référence à un champ de zone géographique, vous pouvez utiliser ::latitude et ::longitude.

Utilisation des références des types de champs LookML avec les champs de date

Par exemple, supposons que vous ayez une dimension enrollment_month et une dimension graduation_month, qui ont toutes les deux été créées dans des groupes de dimensions de type: time. Dans cet exemple, la dimension enrollment_month est produite par le groupe de dimensions suivant (type: time) :


dimension_group: enrollment {
  type: time
  timeframes: [time, date, week, month, year, raw]
  sql: ${TABLE}.enrollment_date ;;
}

De même, la dimension graduation_month est créée par le groupe de dimensions type: time suivant:


dimension_group: graduation {
  type: time
  timeframes: [time, date, week, month, year, raw]
  sql: ${TABLE}.graduation_date ;;
}

Les dimensions enrollment_month et graduation_month vous permettent de calculer le nombre de mois ou d'années entre l'inscription de l'élève et l'obtention du diplôme en créant un groupe de dimensions type: duration. Toutefois, étant donné que certains champs de date sont castés en chaînes SQL dans Looker, la définition des dimensions enrollment_month et graduation_month en tant que valeurs pour sql_start et sql_end peut entraîner une erreur.

Pour éviter une erreur résultant du cast de ces champs de temps en tant que chaînes, vous pouvez créer un groupe de dimensions type: duration, en référençant les délais raw à partir des groupes de dimensions enrollment et graduation dans les paramètres sql_start et sql_end:


dimension_group: enrolled {
  type: duration
  intervals: [month, year]
  sql_start: ${enrollment_raw} ;;
  sql_end: ${graduation_raw} ;;
}

Dans l'interface utilisateur de l'onglet "Explorer", vous générez un groupe de dimensions appelé Durée d'inscription, avec les dimensions individuelles Mois d'inscription et Années d'enregistrement.

Une alternative plus simple à l'utilisation de la période raw dans un groupe de dimensions type: duration consiste à spécifier le type de référence ::date ou ::datetime pour les champs référencés dans les paramètres sql_start et sql_end.


dimension_group: enrolled {
  type: duration
  intervals: [month, year]
  sql_start: ${enrollment_month::date} ;;
  sql_end: ${graduation_month::date} ;;
}

Dans cet exemple, le LookML crée également un groupe de dimensions Durée d'inscription, mais la référence ::date permet d'utiliser les dimensions enrollment_month et graduation_month sans utiliser de période raw ni les caster en tant que chaînes avec SQL.

Pour voir un autre exemple d'utilisation des références de type LookML pour créer des groupes de dimensions personnalisées de type: duration, consultez la page de documentation sur les paramètres dimension_group.

Cette syntaxe n'est pas disponible avec les mesures de type: list, qui ne peuvent pas être référencées à partir de Looker 6.8.

Constantes LookML

Le paramètre constant vous permet de spécifier une constante que vous pouvez utiliser dans un projet LookML. Avec les constantes LookML, vous pouvez définir une valeur une fois et la référencer à n'importe quel endroit de votre projet qui accepte les chaînes et ainsi, limiter les répétitions dans votre code LookML.

Les constantes doivent être déclarées dans un manifeste de projet et la valeur de la constante doit être une chaîne. Par exemple, vous pouvez définir une constante city avec la valeur "Okayama" comme suit:

constant: city {
  value: "Okayama"
}

La constante city peut ensuite être référencée dans l'ensemble du projet à l'aide de la syntaxe @{city}. Par exemple, vous pouvez utiliser la constante city avec le paramètre label dans users:


explore: users {
  label: "@{city} Users"
}

Looker affiche ensuite Okayama Users (Utilisateurs d'Okayama) dans le menu Explore (Explorer) et dans le titre de l'exploration, plutôt que l'option par défaut Users (Utilisateurs).

Pour en savoir plus et voir des exemples d'utilisation des constantes LookML pour écrire du code réutilisable, consultez la page de documentation sur les paramètres constant.