Utiliser les paramètres de requête

Le présent article décrit 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 de variables au moment de l’exécution. Au lieu de procéder au codage 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 leur sécurité en empêchant les injections SQL. De plus, elle permet un traitement plus efficace de divers scénarios de données.

Syntaxe de marqueur de paramètre nommé

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 (IU) Azure Databricks :

• Éditeur SQL
• Blocs-notes
• éditeur de jeu de données de tableau de bord IA/BI
• espaces IA/BI Genie (préversion publique)

Insérez des paramètres dans vos requêtes SQL en utilisant les 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’IU. Vous pouvez utiliser le widget pour modifier le type de paramètre et le nom.

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

L’exemple décrit ici 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 jeu de données qui inclut uniquement des montants tarifaires inférieurs à cinq dollars. Procédez comme suit pour modifier la requête afin d’utiliser un paramètre au lieu de la valeur codée en dur (5).

1. Supprimez le nombre 5 de la requête.
2. Saisissez les deux points (:) suivis de la chaîne de caractères fare_parameter. La dernière ligne de votre requête mise à jour doit indiquer fare_amount < :fare_parameter.
3. Cliquez sur l’icône d’engrenage près du widget de paramètre. La boîte de dialogue affiche les champs suivants :
   • 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 sur le widget. Par défaut, le titre est le même que le mot clé.
   • Type : les types pris en charge sont les suivants : texte, nombre, liste déroulante, date, date et heure, et date et heure (y compris les secondes). La valeur par défaut est Text.
4. Dans la boîte de dialogue, changez le Type en Nombre.
5. Saisissez 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 pour les paramètres nommés

Les exemples suivants illustrent quelques cas d’utilisation courants pour les 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 dont le champ o_total_price est supérieur à la valeur du paramètre fournie.

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, la fonction field_param est utilisé avec la fonction IDENTIFIER pour fournir une valeur de seuil pour la requête au moment 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)

Voir la clause IDENTIFIER.

Concaténer plusieurs paramètres

Vous pouvez inclure des paramètres dans d’autres fonctions SQL. Cet exemple permet à l’utilisateur de sélectionner un titre d’employé et un numéro d’ID. 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)

Utiliser des 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 struct. La substitution de la chaîne a comme valeur du paramètre (param) retourne l’attribut 1.

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

Créer un intervalle

Le INTERVAL type représente un intervalle de temps et vous permet d’effectuer des opérations et des arithmétiques basées sur le temps. L’exemple suivant inclut le paramètre à l’intérieur d’une format_string fonction qui obtient ensuite la conversion en tant que type d’intervalle. La valeur résultante INTERVAL peut être utilisée pour les calculs basés sur le temps ou le filtrage dans la requête.

Consultez le type INTERVAL pour obtenir des détails et une syntaxe complets.

SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Ajouter une plage de dates

L’exemple suivant montre comment ajouter une plage de dates paramétrable pour sélectionner des enregistrements dans un intervalle de temps spécifique.

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

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

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

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 ARRAY_CONTAINS fonction pour filtrer une liste de valeurs. Les TRANSFORMfonctions et SPLIT les fonctions permettent à plusieurs valeurs séparées par des virgules d’être transmises en tant que paramètre de chaîne.

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

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

Remarque

Cet exemple fonctionne pour les valeurs de chaîne. Pour modifier la requête pour d’autres types de données, comme une liste d’entiers, encapsulez l’opération TRANSFORM avec une CAST opération pour convertir les valeurs de chaîne en type de données souhaité.

Modifications de syntaxe

La table suivante présente les cas d’usage courants des paramètres, la syntaxe Databricks SQL de la boîte à moustache d’origine et la syntaxe équivalente basée sur la syntaxe des marqueurs de paramètres nommés.

Cas d’usage des paramètres	Syntaxe des paramètres de la boîte à moustache	Syntaxe de marqueur de paramètre nommé
Charger uniquement des données avant une date spécifiée	WHERE date_field < '{{date_param}}' Vous devez ajouter des guillemets autour du paramètre de date et des accolades.	WHERE date_field < :date_param
Charger uniquement des données inférieures à une valeur numérique spécifiée	WHERE price < {{max_price}}	WHERE price < :max_price
Comparer deux chaînes	WHERE region = {{region_param}}	WHERE region = :region_param
Spécifier la table utilisée dans une requête	SELECT * FROM {{table_name}}	SELECT * FROM IDENTIFIER(:table) Quand un utilisateur entre ce paramètre, il doit utiliser l’espace de noms complet à trois niveaux pour identifier la table.
Spécifier indépendamment le catalogue, le schéma et la table utilisés dans une requête	SELECT * FROM {{catalog}}.{{schema}}.{{table}}	SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table)
Utiliser des paramètres comme modèle dans une chaîne plus longue et mise en forme	“({{area_code}}) {{phone_number}}” Les valeurs des paramètres sont automatiquement concaténées sous forme de chaîne.	format_string(“(%d)%d, :area_code, :phone_number) Pour obtenir un exemple complet, consultez Concaténer plusieurs paramètres.
Créer un intervalle	SELECT INTERVAL {{p}} MINUTE	SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Syntaxe des paramètres de la boîte à moustache

Important

Les sections suivantes s’appliquent uniquement à la syntaxe de requête que vous pouvez utiliser dans l’éditeur SQL. En d’autres termes si vous copiez et collez une requête à l’aide de cette syntaxe dans une autre interface Azure Databricks, telle qu’un Notebook ou un éditeur de jeu de données de tableau de bord IA/BI, la requête doit être ajustée manuellement pour utiliser des marqueurs de paramètres nommés avant de s’exécuter sans erreur.

Dans l’éditeur SQL, toute chaîne de caractères située 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 sont uniquement prises en charge à l’aide de la syntaxe des paramètres de la boîte à moustache.

Utilisez la syntaxe des paramètres de la boîte à moustache pour les fonctionnalités suivantes :

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

Ajouter un paramètre de la boîte à 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.
   • Mot clé : mot clé qui représente le paramètre dans la requête.
   • Titre : titre qui apparaît sur le widget. Par défaut, le titre est le même que le 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 taper des accolades doubles {{ }} et cliquer sur l’icône d’engrenage près 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 sont pas propriétaires de 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 annuler un paramètre de requête, supprimez le paramètre de votre requête. Le widget du 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

• Text
• Nombre
• Liste déroulante
• Liste déroulante en fonction de la requête
• Date et heure

Texte

Accepte une chaîne comme entrée. La barre oblique inverse et les guillemets simples et doubles sont placés dans une séquence d’échappement, 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 }}

Number

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ètres 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 }}'. Lorsqu’elle est sélectionnée dans le panneau des paramètres, une zone de texte s’affiche dans laquelle vous entrez vos valeurs autorisées, chaque valeur séparée par une nouvelle ligne. Les listes déroulantes sont des paramètres de texte. Pour utiliser des dates ou des dates et des heures dans votre liste déroulante, entrez-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 les valeurs multiples. Dans la liste déroulante Guillemets, choisissez de laisser les paramètres tels qu’ils sont entrés (pas de guillemets) ou d’encadrer les paramètres de guillemets simples ou doubles. Vous n’avez pas besoin d’ajouter de guillemets autour du paramètre si vous choisissez les 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 Guillemet double pour le paramètre Guillemet, votre requête reflète le format suivant : WHERE IN ("value1", "value2", "value3")

Liste déroulante en fonction de la requête

Accepte le résultat d’une requête comme entrée. Elle présente le même comportement que le 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 se dégraderont.

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 retourne les données de la table.

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

value	name
1001	John Smith
1002	Jane Doe
1003	Bobby Tables

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

Date et heure

Azure Databricks présente plusieurs options pour paramétrer les valeurs de date et d’heure, y compris celles permettant de simplifier le paramétrage des intervalles de temps. Sélectionnez parmi trois options de précision variable :

Option	Precision	Type
Date	day	DATE
Date et heure	minute	TIMESTAMP
Date et heure (avec secondes)	second	TIMESTAMP

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 les valeurs de date et d’heure soient placées 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 sont définis par défaut sur la date et l’heure actuelles.

Remarque

Le paramètre de plage de dates ne renvoie des résultats corrects que pour les colonnes de type DATE. Pour les colonnes TIMESTAMP, utilisez l’une des options de plage de dates et d’heures.

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.

Important

Les dates et plages de dates dynamiques ne sont pas compatibles avec les requêtes planifiées.

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

Si vous le souhaitez, les requêtes peuvent utiliser les paramètres ou des valeurs statiques. Lorsqu’une visualisation basée sur une requête paramétrable est ajoutée à un tableau de bord, la visualisation peut être configurée pour utiliser un ou une :

• Paramètre de widget

  Les paramètres de widget sont spécifiques à une visualisation unique dans un tableau de bord, ils 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étrable à un tableau de bord, le paramètre est ajouté en tant que paramètre de tableau de bord par défaut. Les paramètres de 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 avoir plusieurs paramètres, chacun pouvant s’appliquer à certaines visualisations et non à d’autres.

• Valeur statique

  Les valeurs statiques sont utilisées à la place d’un paramètre qui répond 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 précédemment.

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

Une fois que vous avez ajouté une visualisation à un tableau de bord, accédez à l’interface de mappage des paramètres en cliquant sur le menu kebab dans le coin supérieur droit 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 votre tableau de bord. Par défaut, il s’agit du paramètre Mot clé. Pour le modifier, cliquez sur l’icône en forme de 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 pour ce paramètre dans la requête sous-jacente. Cela est utile pour le débogage si votre tableau de bord ne retourne 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 valeur : source de la valeur du paramètre. Cliquez sur l’icône en forme 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 : associez 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 même 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 même 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}}'