Concepts SQL pour les vues

Chaque fichier d'affichage de votre projet LookML définit une vue unique dans Looker, qui spécifie une table à interroger et les champs (dimensions et mesures) de cette table qui seront affichés dans l'interface utilisateur Looker. Une vue correspond à une seule table de votre base de données ou à une seule table dérivée.

Ce guide aborde les sujets suivants :

Pour en savoir plus sur l'utilisation de SQL pour définir et personnaliser des tables dérivées dans LookML, consultez Concepts SQL pour les tables dérivées.

La vue

Voici un exemple de fichier d'affichage appelé users.view, qui inclut des définitions pour la table de base de données à interroger, ainsi que plusieurs dimensions et mesures :

view: users {
  sql_table_name: thelook.users ;;

  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }

  dimension: age {
    type: number
    sql: ${TABLE}.age ;;
  }
   measure: average_age {
    type: average
    sql: ${age} ;;  }

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

  measure: count {
    type: count
  }
}

Le premier élément de la définition de la vue est le sql_table_name paramètre, qui spécifie la table de votre base de données qui sera interrogée par une vue. Cette valeur est le seul endroit de l'ensemble du modèle où le nom de la table est défini, car toutes les autres références à la vue utiliseront l'alias de table ${TABLE}. Si vous souhaitez modifier le nom de la table de base de données, vous n'avez qu'à le modifier dans le paramètre sql_table_name. Vous devez tenir compte de certains éléments lorsque vous faites référence à une table de base de données.

Looker utilise la valeur sql_table_name pour écrire sa clause SQL FROM, suivie du nom de la vue, qui devient l'alias de la table. L'équivalent SQL se présente comme suit :

FROM `thelook`.`users` AS `users`

Looker utilise les dimensions et les mesures définies de la vue pour générer sa clause SQL SELECT. Chaque dimension définit le type de dimension (par exemple, chaîne, nombre ou booléen) et un sql paramètre LookML qui fait référence à la dimension dans la vue, à l'aide de l'alias de table. Pour une dimension appelée age, consultez l'exemple suivant :

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

Lorsque Looker crée le code SQL à envoyer à votre base de données, il remplace l'alias de la vue par ${TABLE}. Pour la dimension age de l'exemple précédent, Looker génère une clause SELECT comme suit :

SELECT `users`.`age` AS `users.age`

Les mesures sont souvent des agrégations effectuées sur des dimensions. Vous spécifiez l'alias de dimension dans l'expression sql d'une mesure. Par exemple, une mesure qui calcule la moyenne de la dimension age peut comporter une expression sql avec l'alias ${age} en son sein, comme dans l'exemple suivant :

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

  measure: average_age {
    type: average
    sql: ${age} ;;
  }

Si vous renommez la dimension age, son nouvel alias est propagé à toutes ses références d'alias de dimension.

Personnaliser un fichier d'affichage

Vous pouvez personnaliser les expressions SQL de votre fichier d'affichage ou utiliser la logique LookML intégrée de Looker pour imiter la logique d'une expression SQL.

Utiliser une expression SQL

Supposons que vous souhaitiez diviser les données d'âge en quatre cohortes : les utilisateurs de moins de 18 ans sont définis comme "Jeunes", les utilisateurs âgés de 18 à 35 ans comme "Jeunes adultes", les utilisateurs âgés de 36 à 65 ans comme "Adultes plus âgés" et les utilisateurs de 65 ans et plus comme "Seniors". Pour effectuer cette division, vous devez définir une nouvelle dimension, par exemple dimension: age_cohort, avec une expression sql qui capture ces cohortes. La définition de dimension LookML suivante utilise une instruction CASE adaptée à une connexion de base de données MySQL :

dimension: age_cohort {
  type: string
  sql:
    CASE
      WHEN ${age} < 18 THEN 'Youth'
      WHEN ${age} < 35 THEN 'Young Adult'
      WHEN ${age} < 65 THEN 'Older Adult'
      ELSE 'Senior'
    END ;;
}

Maintenant que vous avez défini votre cohorte d'âge comme dimension, vous pouvez réutiliser la logique CASE en incluant la dimension de cohorte d'âge dans vos requêtes d'exploration.

Lorsque vous créez une requête d'exploration avec la dimension de cohorte d'âge, vous pouvez utiliser l'onglet SQL de l'exploration pour afficher le code SQL généré par Looker. Avec la dimension de cohorte d'âge, le code SQL se présente comme suit :

SELECT
CASE
  WHEN users.age < 18 THEN 'Youth'
  WHEN users.age < 35 THEN 'Young Adult'
  WHEN users.age < 65 THEN 'Older Adult'
  ELSE 'Senior'
END  AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 500

Utiliser la logique de cas intégrée de Looker

Vous pouvez obtenir le même effet qu'une instruction SQL CASE avec une expression indépendante de la base de données. Le paramètre LookML case vous permet de définir les compartiments de cohorte constitués d'instructions when qui utilisent des expressions sql pour capturer des conditions et des chaînes spécifiques afin de libeller les résultats.

Voici un exemple de la même nouvelle dimension age_cohort écrite avec le paramètre LookML case :

  dimension: age_cohort {
    case: {
      when: {
        sql: ${age} < 18 ;;
        label: "Youth"
      }
      when: {
        sql: ${age} < 35 ;;
        label: "Young Adult"
      }
      when: {
        sql: ${age} < 65 ;;
        label: "Middle-aged Adult"
      }
      else: "Older Adult"
    }
  }

Lors de l'exécution, Looker crée la syntaxe SQL CASE appropriée pour votre base de données. De plus, Looker crée une autre expression pour gérer le tri des groupes. Ainsi, les libellés obtenus ne seront pas simplement triés par ordre alphanumérique (sauf si vous définissez l'ordre de tri comme alphanumérique). Looker crée une requête SQL résultante semblable à la suivante :

SELECT
CASE
  WHEN users.age < 18  THEN '0'
  WHEN users.age < 35  THEN '1'
  WHEN users.age < 65  THEN '2'
  ELSE '3'
END AS `users.age_cohort__sort_`,
CASE
  WHEN users.age < 18  THEN 'Youth'
  WHEN users.age < 35  THEN 'Young Adult'
  WHEN users.age < 65  THEN 'Older Adult'
  ELSE 'Senior'
END AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1,
  2
ORDER BY
  1
LIMIT 500

Utiliser la logique de compartiment ou de niveau intégrée de Looker

Une autre méthode pour spécifier comment les valeurs numériques doivent être regroupées utilise les types de paramètres bin ou tier intégrés de Looker. type:bin est utilisé conjointement avec le paramètre bins, de même que type: tier est utilisé conjointement avec le paramètre tiers, pour séparer une dimension numérique en un ensemble de plages de nombres. L'inconvénient est que vous ne pouvez pas définir de libellés pour chaque compartiment.

L'exemple LookML suivant utilise le paramètre bins dans une dimension pour définir la valeur minimale de chaque ensemble :

  dimension: age_cohort {
    type: bin
    bins: [18,36,65]
    style: integer
    sql: ${age} ;;
  }

Vous pouvez utiliser le paramètre tiers dans une dimension exactement de la même manière. Exemple :

  dimension: age_cohort {
    type: tier
    tiers: [18,36,65]
    style: integer
    sql: ${age} ;;
  }

Looker génère ensuite une instruction SQL semblable à la suivante :

SELECT
CASE
  WHEN users.age  < 18 THEN '0'
  WHEN users.age  >= 18 AND users.age  < 36 THEN '1'
  WHEN users.age  >= 36 AND users.age  < 65 THEN '2'
  WHEN users.age  >= 65 THEN '3'
  ELSE '4'
END AS `users.age_cohort__sort_`,
CASE
  WHEN users.age  < 18 THEN 'Below 18'
  WHEN users.age  >= 18 AND users.age  < 36 THEN '18 to 35'
  WHEN users.age  >= 36 AND users.age  < 65 THEN '36 to 64'
  WHEN users.age  >= 65 THEN '65 or Above'
  ELSE 'Undefined'
END AS `users.age_cohort`,
AVG(`age`) AS `users.average_age`,
COUNT(*) AS `users.count`
FROM
  `thelook`.`users` AS `users`
GROUP BY
  1,
  2
ORDER BY
  1
LIMIT 500