Utiliser les paramètres de requête

Cette page explique comment utiliser des paramètres de requête dans l’éditeur SQL Azure Databricks.

Les paramètres de requête vous permettent de rendre vos requêtes plus dynamiques et flexibles en insérant des valeurs variables lors de l’exécution. Au lieu de coder en dur des valeurs spécifiques dans vos requêtes, vous pouvez définir des paramètres pour filtrer les données ou modifier la sortie en fonction de l’entrée utilisateur Cette approche améliore la réutilisation des requêtes, renforce la sécurité en empêchant l’injection SQL et permet une gestion plus efficace de divers scénarios de données.

Syntaxe des marqueurs de paramètres nommés

Les marqueurs de paramètres nommés sont des variables d’espace réservé typées. Utilisez cette syntaxe pour écrire des requêtes dans les parties suivantes de l’interface utilisateur Azure Databricks :

• Éditeur SQL (nouveau et hérité)
• Notebooks
• Éditeur de jeu de données du tableau de bord AI/BI
• AI/BI Genie environnements

Insérez des paramètres dans vos requêtes SQL en tapant deux points suivis d’un nom de paramètre, tel que :parameter_name. Lorsque vous incluez un marqueur de paramètre nommé dans une requête, un widget apparaît dans l’interface utilisateur. Vous pouvez utiliser le widget pour modifier le type et le nom du paramètre.

Ajouter un marqueur de paramètre nommé à une requête

Cet exemple ajoute un marqueur de paramètre à la requête suivante :

SELECT
  trip_distance,
  fare_amount
FROM
  samples.nyctaxi.trips
WHERE
  fare_amount < 5

Cette requête renvoie un ensemble de données qui inclut uniquement les montants de tarifs inférieurs à cinq dollars. Procédez comme suit pour modifier la requête afin d’utiliser un paramètre à la place de la valeur codée en dur (5).

1. Supprimez le chiffre 5 de la requête.
2. Tapez deux points (:) suivis de la chaîne fare_parameter. La dernière ligne de votre requête mise à jour doit afficher fare_amount < :fare_parameter.
3. Cliquez sur près du widget de paramètre. La boîte de dialogue affiche les champs suivants :
   • Keyword (Mot clé) : mot clé qui représente le paramètre dans la requête. Vous ne pouvez pas modifier ce champ. Pour modifier le mot-clé, modifiez le marqueur dans la requête SQL.
   • Titre : titre qui apparaît au-dessus du widget. Par défaut, le titre est identique au mot-clé.
   • Type : les types pris en charge sont Texte, Nombre, Liste déroulante, Date, Date et heure, et Date et heure (avec secondes). La valeur par défaut est Text.
4. Dans la boîte de dialogue, remplacez Type par Nombre.
5. Entrez un nombre dans le widget de paramètre, puis cliquez sur Appliquer les modifications.
6. Cliquez sur Enregistrer pour enregistrer la requête.

Exemples de syntaxe de paramètres nommés

Les exemples suivants illustrent quelques cas d’utilisation courants des paramètres.

Insérer une date

L’exemple suivant inclut un paramètre Date qui limite les résultats de la requête aux enregistrements postérieurs à une date spécifique.

SELECT
  o_orderdate AS Date,
  o_orderpriority AS Priority,
  sum(o_totalprice) AS `Total Price`
FROM
  samples.tpch.orders
WHERE
  o_orderdate > :date_param
GROUP BY
  1,
  2

Insérer un nombre

L’exemple suivant inclut un paramètre Nombre qui limite les résultats aux enregistrements dans lesquels le champ o_total_price est supérieur à la valeur du paramètre fourni.

SELECT
  o_orderdate AS Date,
  o_orderpriority AS Priority,
  o_totalprice AS Price
FROM
  samples.tpch.orders
WHERE
  o_totalprice > :num_param

Insérer un nom de champ

Dans l’exemple suivant, field_param est utilisé avec la fonction IDENTIFIER pour fournir une valeur seuil pour la requête lors de l’exécution. La valeur du paramètre doit être un nom de colonne de la table utilisée dans la requête.

SELECT
  *
FROM
  samples.tpch.orders
WHERE
  IDENTIFIER(:field_param) < 10000

Insérer des objets de base de données

L’exemple suivant crée trois paramètres : catalog, schema et table.

SELECT
  *
FROM
  IDENTIFIER(:catalog || '.' || :schema || '.' || :table)

Consultez la IDENTIFIER clause.

Concaténer plusieurs paramètres

Vous pouvez inclure des paramètres dans d’autres fonctions SQL. Cet exemple permet au visualiseur de sélectionner un titre d’employé et un ID numérique. La requête utilise la fonction format_string pour concaténer les deux chaînes et filtrer les lignes qui correspondent. Consultez Fonction format_string.

SELECT
  o_orderkey,
  o_clerk
FROM
  samples.tpch.orders
WHERE
  o_clerk LIKE format_string('%s%s', :title, :emp_number)

Utilisation de chaînes JSON

Vous pouvez utiliser des paramètres pour extraire un attribut d’une chaîne JSON. L’exemple suivant utilise la fonction from_json pour convertir la chaîne JSON en une valeur de structure. Le remplacement de la chaîne a par la valeur du paramètre (param) renvoie l’attribut 1.

SELECT
  from_json('{"a": 1}', 'map<string, int>') [:param]

Création d’un intervalle

Le type INTERVAL représente une étendue de temps et vous permet d’effectuer des opérations arithmétiques basées sur le temps. L’exemple suivant utilise une fonction CAST pour convertir le paramètre en tant que type d’intervalle. La valeur INTERVAL résultante peut être utilisée pour des calculs basés sur le temps ou pour filtrer dans la requête.

Pour plus d’informations et la syntaxe complète, consultez Type INTERVAL.

SELECT CAST(:param AS INTERVAL MINUTE)

Ajouter une plage de dates

Les exemples suivants montrent comment filtrer les enregistrements dans une plage de dates. Les paramètres de date peuvent utiliser .min et .max attributs pour spécifier une plage. Vous pouvez également spécifier une plage de dates en utilisant deux paramètres de date distincts.

Utiliser des attributs min et max

1. Choisissez un nom de paramètre. Permet .min d’accéder à la valeur de plage minimale et .max d’accéder à la valeur de plage maximale, comme dans la requête suivante :

   SELECT * FROM samples.nyctaxi.trips
   WHERE tpep_pickup_datetime
   BETWEEN :date_range.min AND :date_range.max
   

2. Définissez le type de paramètre sur Date.

3. Définissez le type de widget sur Range.

Utiliser deux paramètres de date

L’exemple suivant utilise deux paramètres et start_dateend_date, pour définir une plage de dates.

SELECT * FROM samples.nyctaxi.trips
WHERE tpep_pickup_datetime
BETWEEN :start_date AND :end_date

Paramétrer les rollups par jour, mois ou année

L’exemple suivant agrège les données de courses en taxi à un niveau de granularité paramétré. La fonction DATE_TRUNC tronque la valeur tpep_pickup_datetime en fonction de la valeur du paramètre :date_granularity, telle que DAY, MONTH ou YEAR. La date tronquée est aliasée sous le nom date_rollup et utilisée dans la clause GROUP BY.

SELECT DATE_TRUNC(:date_granularity, tpep_pickup_datetime) AS
  date_rollup,
  COUNT(*) AS total_trips
FROM samples.nyctaxi.trips
GROUP BY date_rollup

Utiliser plusieurs valeurs dans une seule requête

L’exemple suivant utilise la fonction ARRAY_CONTAINS pour filtrer une liste de valeurs. Les fonctions TRANSFORM et SPLIT permettent de passer plusieurs valeurs séparées par des virgules en tant que paramètre de chaîne.

La valeur :list_parameter prend une liste de valeurs séparées par des virgules. La fonction SPLIT analyse cette liste et divise les valeurs séparées par des virgules en un tableau. La fonction TRANSFORM transforme chaque élément du tableau en supprimant tous les espaces. La fonction ARRAY_CONTAINS vérifie si la valeur dropoff_zip de la table trips est contenue dans le tableau de valeurs transmis en tant que list_parameter.

SELECT * FROM samples.nyctaxi.trips WHERE
  array_contains(
    TRANSFORM(SPLIT(:list_parameter, ','), s -> TRIM(s)),
    dropoff_zip
  )

Modifications de syntaxe

Le tableau suivant présente les cas d’utilisation courants des paramètres, la syntaxe SQL Mustache Databricks d’origine et la syntaxe équivalente utilisant la syntaxe des marqueurs de paramètres nommés.

Syntaxe des paramètres Mustache

Dans l’éditeur SQL, toute chaîne entre deux accolades {{ }} est traitée comme un paramètre de requête. Un widget apparaît au-dessus du volet de résultats, où vous définissez la valeur du paramètre. Bien qu’Azure Databricks recommande généralement d’utiliser des marqueurs de paramètres nommés, certaines fonctionnalités ne sont prises en charge qu’avec la syntaxe de paramètres moustache.

Utilisez la syntaxe de paramètres moustache pour les fonctionnalités suivantes :

• Filtres de tableau de bord hérités
• Paramètres de liste déroulante à valeurs multiples
• Paramètres de liste déroulante basés sur une requête

Ajouter un paramètre moustache

1. Tapez Cmd + I. Le paramètre est inséré au niveau du curseur de texte et la boîte de dialogue Ajouter un paramètre s’affiche.
   • Keyword (Mot clé) : mot clé qui représente le paramètre dans la requête.
   • Titre : titre qui apparaît au-dessus du widget. Par défaut, le titre est identique au mot-clé.
   • Type : les types pris en charge sont Texte, Nombre, Date, Date et heure, Date et heure (avec secondes), Liste déroulante et Liste déroulante basée sur une requête. La valeur par défaut est Text.
2. Entrez le mot clé, remplacez le titre si vous le souhaitez et sélectionnez le type de paramètre.
3. Cliquez sur Ajouter un paramètre.
4. Dans le widget du paramètre, définissez la valeur du paramètre.
5. Cliquez sur Appliquer les modifications.
6. Cliquez sur Enregistrer.

Vous pouvez également saisir deux accolades {{ }} et cliquer sur l’icône en forme de roue dentée à côté du widget de paramètre pour modifier les paramètres.

Pour réexécuter la requête avec une valeur de paramètre différente, entrez la valeur dans le widget, puis cliquez sur Appliquer les modifications.

Modifier un paramètre de requête

Pour modifier un paramètre, cliquez sur l’icône en forme d’engrenage située à côté du widget de paramètre. Pour empêcher les utilisateurs qui ne possèdent pas la requête de modifier le paramètre, cliquez sur Afficher les résultats uniquement. La boîte de dialogue du paramètre <Keyword> s’affiche.

Supprimer un paramètre de requête

Pour supprimer un paramètre de requête, supprimez le paramètre de votre requête. Le widget de paramètre disparaît et vous pouvez réécrire votre requête à l’aide de valeurs statiques.

Modifier l’ordre des paramètres

Pour modifier l’ordre dans lequel les paramètres sont affichés, vous pouvez cliquer sur chaque paramètre et le faire glisser jusqu’à la position de votre choix.

Types de paramètres de requête

• Texte
• Nombre
• Liste déroulante
• Liste déroulante basée sur une requête
• Date et heure

Text

Accepte une chaîne comme entrée. Les barres obliques inversées, les guillemets simples et doubles sont échappés, et Azure Databricks ajoute des guillemets à ce paramètre. Par exemple, une chaîne comme mr's Li"s est transformée en 'mr\'s Li\"s'. Voici un exemple d’utilisation :

SELECT * FROM users WHERE name={{ text_param }}

Numéro

Accepte un nombre comme entrée. Voici un exemple d’utilisation :

SELECT * FROM users WHERE age={{ number_param }}

Liste déroulante

Pour limiter l’étendue des valeurs de paramètre possibles lors de l’exécution d’une requête, utilisez le type de paramètre Liste déroulante. Par exemple, SELECT * FROM users WHERE name='{{ dropdown_param }}'. Lorsque vous sélectionnez ce type dans le panneau des paramètres, une zone de texte apparaît dans laquelle vous pouvez saisir les valeurs autorisées, séparées par une nouvelle ligne. Les listes déroulantes sont des paramètres de type texte. Pour utiliser des dates ou des dates et heures dans votre liste déroulante, saisissez-les au format requis par votre source de données. Les chaînes ne sont pas placées dans une séquence d’échappement. Vous pouvez choisir entre une liste déroulante à valeur unique ou à valeurs multiples.

• Valeur unique : le paramètre doit être entouré de guillemets simples.
• Valeurs multiples : activez l’option Autoriser plusieurs valeurs. Dans la liste déroulante Guillemets, choisissez de laisser les paramètres tels qu’ils ont été saisis (sans guillemets) ou de les entourer de guillemets simples ou doubles. Vous n’avez pas besoin d’ajouter de guillemets autour du paramètre si vous choisissez des guillemets.

Modifiez votre clause WHERE pour utiliser le mot-clé IN dans votre requête.

SELECT ...
FROM   ...
WHERE field IN ( {{ Multi Select Parameter }} )

Le widget de paramètre à sélection multiple vous permet de transmettre plusieurs valeurs à la base de données. Si vous sélectionnez l’option Double guillemet pour le paramètre Guillemet, votre requête reflète le format suivant : WHERE IN ("value1", "value2", "value3")

Liste déroulante basée sur une requête

Accepte le résultat d’une requête comme entrée. Son comportement est identique à celui du paramètre Liste déroulante. Vous devez enregistrer la requête de liste déroulante Databricks SQL pour l’utiliser comme entrée dans une autre requête.

1. Cliquez sur Liste déroulante basée sur une requête sous Type dans le panneau des paramètres.
2. Cliquez sur le champ Requête et sélectionnez une requête. Si votre requête cible renvoie un grand nombre d’enregistrements, les performances seront réduites.

Si votre requête cible renvoie plus d’une colonne, Databricks SQL utilise la première colonne. Si votre requête cible renvoie des colonnes name et value, Databricks SQL renseigne le widget de sélection du paramètre avec la colonne name, mais exécute la requête avec la colonne value associée.

Par exemple, supposons que la requête suivante renvoie les données du tableau.

SELECT user_uuid AS 'value', username AS 'name'
FROM users

Lorsque Azure Databricks exécute la requête, la valeur transmise à la base de données serait 1001, 1002 ou 1003.

Date et heure

Azure Databricks propose plusieurs options pour paramétrer les valeurs de date et d’horodatage, notamment des options permettant de simplifier le paramétrage des plages de temps. Sélectionnez l’une des trois options de précision :

Lorsque vous choisissez une option de paramètre de plage, vous créez deux paramètres désignés par les suffixes .start et .end. Toutes les options transmettent les paramètres à votre requête sous forme de littéraux de chaîne ; Azure Databricks exige que vous placiez les valeurs de date et d’heure entre guillemets simples ('). Par exemple :

-- Date parameter
SELECT *
FROM usage_logs
WHERE date = '{{ date_param }}'

-- Date and Time Range parameter
SELECT *
FROM usage_logs
WHERE modified_time > '{{ date_range.start }}' and modified_time < '{{ date_range.end }}'

Les paramètres de date utilisent une interface de sélection de calendrier et prennent par défaut la date et l’heure actuelles.

Valeurs dynamiques de date et de plage de dates

Lorsque vous ajoutez un paramètre de date ou de plage de dates à votre requête, le widget de sélection affiche une icône en forme d’éclair bleu. Cliquez dessus pour afficher des valeurs dynamiques telles que today, yesterday, this week, last week, last month ou last year. Ces valeurs sont mises à jour dynamiquement.

Utilisation de paramètres de requête dans des tableaux de bord

Les requêtes peuvent éventuellement utiliser des paramètres ou des valeurs statiques. Lorsqu’une visualisation basée sur une requête paramétrée est ajoutée à un tableau de bord, la visualisation peut être configurée pour utiliser soit :

• Paramètre de widget

  Les paramètres de widget sont spécifiques à une seule visualisation dans un tableau de bord, apparaissent dans le panneau de visualisation et les valeurs de paramètre spécifiées s’appliquent uniquement à la requête sous-jacente à la visualisation.

• Paramètre de tableau de bord

  Les paramètres du tableau de bord peuvent s’appliquer à plusieurs visualisations. Lorsque vous ajoutez une visualisation basée sur une requête paramétrée à un tableau de bord, le paramètre est ajouté par défaut en tant que paramètre du tableau de bord. Les paramètres du tableau de bord sont configurés pour une ou plusieurs visualisations dans un tableau de bord et apparaissent en haut du tableau de bord. Les valeurs de paramètre spécifiées pour un paramètre de tableau de bord s’appliquent aux visualisations réutilisant ce paramètre de tableau de bord particulier. Un tableau de bord peut comporter plusieurs paramètres, chacun pouvant s’appliquer à certaines visualisations et pas à d’autres.

• Valeur statique

  Les valeurs statiques sont utilisées à la place d’un paramètre qui réagit aux modifications. Les valeurs statiques vous permettent de coder en dur une valeur à la place d’un paramètre. Elles font « disparaître » le paramètre du tableau de bord ou du widget où il apparaissait auparavant.

Lorsque vous ajoutez une visualisation contenant une requête paramétrée, vous pouvez choisir le titre et la source du paramètre dans la requête de visualisation en cliquant sur l’icône crayon appropriée. Vous pouvez également sélectionner le mot clé et une valeur par défaut. Voir Propriétés de paramètre.

Après avoir ajouté une visualisation à un tableau de bord, accédez à l’interface de mappage des paramètres en cliquant sur le menu kebab en haut à droite d’un widget de tableau de bord, puis en cliquant sur Modifier les paramètres du widget.

Propriétés du paramètre

• Titre : nom d’affichage qui apparaît à côté du sélecteur de valeurs sur le tableau de bord. Par défaut, il s’agit du paramètre Mot clé. Pour le modifier, cliquez sur l’icône crayon . Les titres ne sont pas affichés pour les paramètres statiques du tableau de bord, car le sélecteur de valeur est masqué. Si vous sélectionnez Valeur statique comme source de valeur, le champ Titre est grisé.

• Mot clé : littéral de chaîne de ce paramètre dans la requête sous-jacente. Cette information est utile pour le débogage si le tableau de bord ne renvoie pas les résultats attendus.

• Valeur par défaut : valeur utilisée si aucune autre valeur n’est spécifiée. Pour modifier cette valeur à partir de l’écran de requête, exécutez la requête avec la valeur de paramètre souhaitée, puis cliquez sur le bouton Enregistrer.

• Source de la valeur : source de la valeur du paramètre. Cliquez sur l’icône en ’orme de crayon pour choisir une source.

  • Nouveau paramètre de tableau de bord : créez un paramètre au niveau du tableau de bord. Cela vous permet de définir une valeur de paramètre à un seul endroit sur votre tableau de bord et de l’associer à plusieurs visualisations.
  • Paramètre de tableau de bord existant : mappez le paramètre à un paramètre de tableau de bord existant. Vous devez spécifier le paramètre de tableau de bord préexistant.
  • Paramètre de widget : affiche un sélecteur de valeur dans le widget de votre tableau de bord. Ceci est utile pour les paramètres uniques qui ne sont pas partagés entre les widgets.
  • Valeur statique : choisissez une valeur statique pour le widget, quelles que soient les valeurs utilisées sur les autres widgets. Les valeurs de paramètres statiques n’affichent pas de sélecteur de valeur sur le tableau de bord, ce qui est plus compact. Cela vous permet de profiter de la flexibilité des paramètres de requête sans encombrer l’interface utilisateur sur un tableau de bord lorsque certains paramètres ne sont pas censés changer fréquemment.

  

Forums Aux Questions (FAQ)

• Puis-je réutiliser le même paramètre plusieurs fois dans une seule requête ?
• Puis-je utiliser plusieurs paramètres dans une seule requête ?

Puis-je réutiliser le même paramètre plusieurs fois dans une seule requête ?

Oui. Utilisez le même identificateur dans les accolades. Cet exemple utilise deux fois le paramètre {{org_id}}.

SELECT {{org_id}}, count(0)
FROM queries
WHERE org_id = {{org_id}}

Puis-je utiliser plusieurs paramètres dans une seule requête ?

Oui. Utilisez un nom unique pour chaque paramètre. Cet exemple utilise deux paramètres : {{org_id}} et {{start_date}}.

SELECT count(0)
FROM queries
WHERE org_id = {{org_id}} AND created_at > '{{start_date}}'