Filtres basés sur un modèle et paramètres Liquid

Cette rubrique avancée suppose une bonne connaissance des langages SQL et LookML.

Looker permet automatiquement aux utilisateurs de manipuler leurs requêtes en créant des filtres, qui sont basés sur des dimensions et des mesures. Cette méthode simple répond à de nombreux cas d'utilisation, mais ne répond pas à tous les besoins analytiques. Les filtres basés sur un modèle et les paramètres Liquid étendent considérablement les possibilités qui s'offrent à vous.

D'un point de vue SQL, les dimensions et les mesures ne peuvent modifier que les clauses WHERE ou HAVING les plus externes dans votre requête. Vous pouvez néanmoins autoriser les utilisateurs à manipuler d'autres parties d'une requête SQL. L'ajustement d'une partie d'une table dérivée, l'ajustement de la table de base de données interrogée, ou la création de dimensions et de filtres polyvalents sont quelques-unes des fonctionnalités que vous pouvez activer grâce aux modèles de filtres et aux paramètres Liquid.

Les filtres basés sur un modèle et les paramètres Liquid font appel au langage de création de modèles Liquid pour insérer les valeurs saisies par l'utilisateur dans des requêtes SQL. Vous commencez par utiliser un paramètre LookML pour créer un champ avec lequel les utilisateurs peuvent interagir. Ensuite, vous utilisez une variable Liquid pour injecter les entrées utilisateur dans des requêtes SQL.

Examples

Étudions quelques exemples pour illustrer la valeur des filtres modélisés et des paramètres Liquid.

Création d'une table dérivée dynamique avec un filtre basé sur un modèle

Prenons l'exemple d'une table dérivée qui calcule les dépenses totales d'un client dans la région nord-est:

view: customer_facts {
  derived_table: {
    sql:
      SELECT
        customer_id,                        -- Can be made a dimension
        SUM(sale_price) AS lifetime_spend   -- Can be made a dimension
      FROM
        order
      WHERE
        region = 'northeast'                -- Can NOT be made a dimension
      GROUP BY 1
    ;;
  }
}

Dans cette requête, vous pouvez créer des dimensions à partir de customer_id et de lifetime_spend. Toutefois, supposons que vous souhaitiez que l'utilisateur puisse spécifier le region au lieu de l'incorporer au nord-est. L'élément region ne peut pas être exposé en tant que dimension, et l'utilisateur ne peut donc pas le filtrer normalement.

L'une des solutions serait d'utiliser un filtre basé sur un modèle, qui ressemblerait à ceci :

view: customer_facts {
  derived_table: {
    sql:
      SELECT
        customer_id,
        SUM(sale_price) AS lifetime_spend
      FROM
        order
      WHERE
        {% condition order_region %} order.region {% endcondition %}
      GROUP BY 1
    ;;
  }

  filter: order_region {
    type: string
  }
}

Vous trouverez des instructions détaillées ci-dessous.

Si une table dérivée utilise un filtre modélisé, vous ne pouvez pas la rendre persistante.

Création d'une mesure dynamique avec un paramètre Liquid

Prenons l'exemple d'une mesure filtrée calculant la somme des pantalons (« pants ») vendus :

measure: pants_count {
  filters: [category: "pants"]
}

Le principe est simple, mais en présence de dizaines de catégories, il serait fastidieux de créer une mesure pour chacune d'elles. En outre, l'utilisation des explorations n'en serait que plus confuse.

Une autre solution consisterait à créer une mesure dynamique comparable à celle-ci :

measure: category_count {
  type: sum
  sql:
    CASE
      WHEN ${category} = '{% parameter category_to_count %}'
      THEN 1
      ELSE 0
    END
  ;;
}

parameter: category_to_count {
  type: string
}

Vous trouverez des instructions détaillées ci-dessous.

Utilisation de base

Étape 1: Créez un contenu avec lequel l'utilisateur peut interagir

• Pour les filtres modélisés, ajoutez un filter.
• Pour les paramètres Liquid, ajoutez un parameter.

Dans les deux cas, l'utilisateur peut voir ces champs dans la section Champs uniquement filtrés de l'outil de sélection de champs:

Les champs filter et parameter acceptent tous deux une série de paramètres enfants, ce qui vous permet de personnaliser leur fonctionnement. Pour obtenir la liste complète, consultez la page de documentation sur les paramètres des champs. Deux options sont spéciales pour les champs parameter.

Tout d'abord, les champs parameter peuvent avoir un type spécial appelé sans guillemets:

parameter: table_name {
  type: unquoted
}

Ce type de champ permet d'insérer des valeurs dans une requête SQL sans les mettre entre guillemets — comme c'est le cas des chaînes —, ce qui peut être utile pour insérer des valeurs SQL telles que des noms de tables.

Ensuite, les champs parameter comportent une option appelée valeurs autorisées qui vous permet d'associer un nom convivial à la valeur que vous souhaitez insérer. Exemple :

  parameter: sale_price_metric_picker {
    description: "Use with the Sale Price Metric measure"
    type: unquoted
    allowed_value: {
      label: "Total Sale Price"
      value: "SUM"
    }
    allowed_value: {
      label: "Average Sale Price"
      value: "AVG"
    }
    allowed_value: {
      label: "Maximum Sale Price"
      value: "MAX"
    }
    allowed_value: {
      label: "Minimum Sale Price"
      value: "MIN"
    }
  }

Étape 2: Appliquez les entrées utilisateur

La deuxième étape consiste à utiliser Liquid pour ajouter le filtre modélisé ou le paramètre Liquid si vous le souhaitez.

Filtres basés sur un modèle

La syntaxe des filtres basés sur un modèle se décompose de la manière suivante :

{% condition filter_name %} sql_or_lookml_reference {% endcondition %}

• Les mots condition et endcondition ne changent jamais.
• Remplacez filter_name par le nom du filtre que vous avez créé à la première étape. Si vous n'avez pas créé de champ réservé au filtrage, vous pouvez également utiliser une dimension.
• Remplacez sql_or_lookml_reference par le code SQL ou LookML qui doit être défini sur"égal"sur l'entrée utilisateur (plus d'informations ci-dessous). Si vous utilisez LookML, utilisez la syntaxe LookML ${view_name.field_name}.

Dans l'exemple ci-dessus, nous avons utilisé:

{% condition order_region %} order.region {% endcondition %}

Il est important de bien comprendre l'interaction entre le code SQL et les balises Liquid qui l'entourent. Les balises d'un filtre basé sur un modèle sont systématiquement converties en expression logique. Par exemple, si l'utilisateur saisit "Northeast" dans le filtre order_region, Looker transforme ces tags en: order.region = 'Northeast'. Autrement dit, Looker comprend l'entrée utilisateur et génère l'expression logique appropriée.

Ce point est souvent une source de confusion pour les développeurs Looker. Les filtres basés sur un modèle se soldent toujours par une expression logique d'un genre ou d'un autre, et non par la valeur saisie par un utilisateur.

Étant donné que les filtres modélisés renvoient une expression logique, vous pouvez les utiliser avec d'autres opérateurs logiques et expressions logiques valides dans l'instruction SQL WHERE. Dans l'exemple ci-dessus, si vous souhaitez renvoyer toutes les valeurs, sauf la région sélectionnée par l'utilisateur, vous pouvez utiliser l'instruction suivante dans l'instruction WHERE:

NOT ({% condition order_region %} order.region {% endcondition %})

Il est également possible d'utiliser un champ LookML comme condition de filtre. Tous les filtres appliqués directement au champ LookML détermineront la valeur de l'instruction WHERE:

view: customer_facts {
  derived_table: {
    sql:
      SELECT
        customer_id,
        SUM(sale_price) AS lifetime_spend
      FROM
        order
      WHERE
        {% condition region %} order.region {% endcondition %}
      GROUP BY 1
    ;;
  }

  dimension: region {
    type: string
    sql: ${TABLE}.region ;;
}

Paramètres Liquid

La syntaxe des paramètres Liquid se décompose de la manière suivante :

{% parameter parameter_name %}

• Le mot parameter ne change jamais.
• Remplacez parameter_name par le nom parameter que vous avez créé à la première étape.

Par exemple, pour appliquer l'entrée du champ parameter à l'étape 1 ci-dessus, vous pouvez créer une mesure comme suit:

  measure: sale_price_metric {
    description: "Use with the Sale Price Metric Picker filter-only field"
    type: number
    label_from_parameter: sale_price_metric_picker
    sql: {% parameter sale_price_metric_picker %}(${sale_price}) ;;
    value_format_name: usd
  }

Choix entre les filtres basés sur un modèle et les paramètres Liquid

Malgré leurs similitudes, les filtres basés sur un modèle et les paramètres Liquid présentent une différence de taille :

• Les paramètres liquides permettent d'insérer directement une entrée utilisateur (ou d'utiliser les valeurs que vous définissez avec des valeurs autorisées).
• Les filtres basés sur un modèle insèrent des valeurs en tant qu'instructions logiques, comme décrit ci-dessus.

Lorsque vous voulez proposer aux utilisateurs un mode de saisie plus souple (différents types de plages de dates ou modes de recherche de chaînes, par exemple), privilégiez si possible les filtres basés sur un modèle. Looker peut interpréter l'entrée utilisateur et écrire le code SQL approprié en arrière-plan, ce qui vous évite de prévoir tous les types d'entrées utilisateur possibles.

Dans les cas où une instruction logique ne peut pas être insérée ou lorsque vous connaissez un ensemble limité d'options que l'utilisateur peut saisir, utilisez les paramètres Liquid.