Concepts SQL pour les vues

Chaque fichier de vue de votre projet LookML définit une seule vue dans Looker. Il 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 table unique de votre base de données ou à une table dérivée unique.

Dans ce guide, vous allez aborder 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 de vue appelé users.view, qui inclut des définitions pour la table de base de données qui sera interrogée, 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 paramètre sql_table_name, 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, il vous suffit de le modifier dans le paramètre sql_table_name. Il y a quelques points à prendre en compte lorsque vous référencez 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 dans la vue pour générer sa clause SQL SELECT. Chaque dimension définit le type de dimension (chaîne, nombre ou booléen, par exemple) et un paramètre LookML sql qui fait référence à la dimension dans la vue, à l'aide de l'alias de table. Pour une dimension appelée age, voici un exemple :

  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érerait 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 contenir une expression sql avec l'alias ${age}, 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 de vue

Vous pouvez personnaliser les expressions SQL de votre fichier de vue 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 ("Jeunes"), ceux âgés de 18 à 35 ans ("Jeunes adultes"), ceux âgés de 36 à 65 ans ("Adultes") et ceux de 65 ans et plus ("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 qui convient à une connexion à une 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 Explorer avec la dimension "Tranche d'âge", vous pouvez utiliser l'onglet "SQL" de l'outil Explorer pour afficher le code SQL généré par Looker. Avec la dimension "Cohorte d'âge", le code SQL se présentera 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 buckets de cohortes qui sont constitués d'instructions when utilisant des expressions sql pour capturer des conditions et des chaînes spécifiques pour étiqueter les résultats.

Voici un exemple de la même dimension age_cohort qui est é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. Les libellés obtenus ne seront donc 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 qui ressemble à ce qui suit :

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 bin ou de niveau intégrée à 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 à Looker. type:bin est utilisé conjointement avec le paramètre bins, et 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 bin.

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 à celle-ci :

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