Concepts SQL pour les vues

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

Dans ce guide, vous allez découvrir 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 la section Concepts SQL pour les tables dérivées.

Vue

Voici un exemple de fichier de vue nommé 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. Il spécifie la table de votre base de données qui sera interrogée par une vue. Cette valeur est le seul endroit 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 suffit de le faire dans le paramètre sql_table_name. Vous devez prendre en compte 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 mesures définies dans la vue pour générer sa clause SELECT SQL. Chaque dimension définit son type (par exemple, chaîne, nombre ou valeur booléenne) et un paramètre LookML sql qui 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 dans ${TABLE}. Pour la dimension age de l'exemple précédent, Looker générerait une clause SELECT semblable à celle-ci:

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 comportant 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é à tous 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, avec les utilisateurs de moins de 18 ans 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 "Senior". 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 à 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 tranche d'âge en tant que dimension, vous pouvez réutiliser la logique CASE en incluant la dimension "Tranche d'âge" dans vos requêtes d'exploration.

Lorsque vous créez une requête d'exploration avec la dimension "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 "Cohorte d'âge", le code SQL ressemblera à ceci:

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 CASE SQL 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 composés d'instructions when qui utilisent des expressions sql pour capturer des conditions et des chaînes spécifiques afin d'étiqueter 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"
    }
  }

Au moment de l'exécution, Looker crée la syntaxe SQL CASE correcte pour votre base de données. En outre, Looker crée une autre expression pour gérer le tri des groupes, de sorte que les étiquettes obtenues ne soient pas seulement triées de manière alphanumérique (sauf si vous définissez l'ordre de tri comme alphanumérique). Looker génère une requête SQL qui se présente comme 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 binning ou de niveau intégrée de Looker

Une autre méthode permettant de spécifier comment les valeurs numériques doivent être regroupées consiste à utiliser 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, type: tier est utilisé conjointement avec le paramètre tiers pour séparer une dimension numérique dans un ensemble de plages de nombres. En contrepartie, vous ne pouvez pas définir d'étiquettes pour chaque classe.

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