Delen via

Werken met queryparameters

  • Artikel

In dit artikel wordt uitgelegd hoe u kunt werken met queryparameters in de Azure Databricks SQL-editor.

Met queryparameters kunt u uw query's dynamischer en flexibeler maken door variabele waarden in te voegen tijdens runtime. In plaats van specifieke waarden in uw query's vast te coderen, kunt u parameters definiëren om gegevens te filteren of uitvoer te wijzigen op basis van gebruikersinvoer. Deze aanpak verbetert het hergebruik van query's, verbetert de beveiliging door SQL-injectie te voorkomen en maakt efficiëntere verwerking van diverse gegevensscenario's mogelijk.

Benoemde parametermarkeringssyntaxis

Benoemde parametermarkeringen zijn tijdelijke aanduidingen voor variabelen getypt. Gebruik deze syntaxis om query's te schrijven in de volgende onderdelen van de Azure Databricks-gebruikersinterface:

  • SQL-editor
  • Notebooks
  • Editor voor AI/BI-dashboardgegevensset
  • AI/BI Genie-ruimten (openbare preview)

Voeg parameters in uw SQL-query's in door een dubbele punt te typen, gevolgd door een parameternaam, zoals :parameter_name. Wanneer u een benoemde parametermarkering in een query opneemt, wordt er een widget weergegeven in de gebruikersinterface. U kunt de widget gebruiken om het parametertype en de naam te bewerken.

Er wordt een benoemde parameter toegevoegd aan een SQL-query. Er wordt een widget weergegeven onder de SQL-editor

Een benoemde parametermarkering toevoegen aan een query

In dit voorbeeld wordt een parametermarkering toegevoegd aan de volgende query:


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

Deze query retourneert een gegevensset die alleen tariefbedragen bevat die minder dan vijf dollar bedragen. Gebruik de volgende stappen om de query te bewerken om een parameter te gebruiken in plaats van de in code vastgelegde waarde (5).

  1. Verwijder het getal 5 uit de query.
  2. Typ een dubbele punt (:) gevolgd door de tekenreeks fare_parameter. De laatste regel van de bijgewerkte query moet zeggen fare_amount < :fare_parameter.
  3. Klik op het Tandwielpictogram tandwielpictogram in de buurt van de parameterwidget. In het dialoogvenster ziet u de volgende velden:
    • Trefwoord: het trefwoord dat de parameter in de query vertegenwoordigt. U kunt dit veld niet bewerken. Als u het trefwoord wilt wijzigen, bewerkt u de markering in de SQL-query.
    • Titel: De titel die over de widget wordt weergegeven. De titel is standaard hetzelfde als het trefwoord.
    • Type: Ondersteunde typen zijn tekst, getal, vervolgkeuzelijst, datum, datum en tijd en datum en tijd (met seconden). De standaardwaarde is de tekst.
  4. Wijzig in het dialoogvenster het type in Getal.
  5. Voer een getal in de parameterwidget in en klik op Wijzigingen toepassen.
  6. Klik op Save om de query op te slaan.

Voorbeelden van benoemde parametersyntaxis

In de volgende voorbeelden ziet u enkele veelvoorkomende use cases voor parameters.

Een datum invoegen

Het volgende voorbeeld bevat een datumparameter waarmee queryresultaten worden beperkt tot records na een specifieke datum.


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

Een getal invoegen

Het volgende voorbeeld bevat een getalparameter waarmee de resultaten worden beperkt tot records waarin het o_total_price veld groter is dan de opgegeven parameterwaarde.


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

Een veldnaam invoegen

In het volgende voorbeeld wordt de field_param IDENTIFIER functie gebruikt voor het opgeven van een drempelwaarde voor de query tijdens runtime. De parameterwaarde moet een kolomnaam zijn uit de tabel die in de query wordt gebruikt.


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

Databaseobjecten invoegen

In het volgende voorbeeld worden drie parameters gemaakt: catalog, schemaen table.


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

Zie de ID-component.

Meerdere parameters samenvoegen

U kunt parameters opnemen in andere SQL-functies. In dit voorbeeld kan de kijker een werknemertitel en een nummer-id selecteren. De query gebruikt de format_string functie om de twee tekenreeksen samen te voegen en te filteren op de rijen die overeenkomen. Zie format_string functie.


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

Werken met JSON-tekenreeksen

U kunt parameters gebruiken om een kenmerk uit een JSON-tekenreeks te extraheren. In het volgende voorbeeld wordt de from_json functie gebruikt om de JSON-tekenreeks te converteren naar een struct-waarde. Als u de tekenreeks a vervangt door de waarde voor de parameter (param) wordt het kenmerk 1 geretourneerd.

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

Een interval maken

Het INTERVAL type vertegenwoordigt een tijdsduur en stelt u in staat om rekenkundige bewerkingen en bewerkingen op basis van tijd uit te voeren. Het volgende voorbeeld bevat de parameter in een format_string functie die vervolgens wordt omgezet als intervaltype. De resulterende INTERVAL waarde kan worden gebruikt voor berekeningen op basis van tijd of filteren in de query.

Zie intervaltype voor volledige details en syntaxis.

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

Een datumbereik toevoegen

In het volgende voorbeeld ziet u hoe u een geparameteriseerd datumbereik toevoegt om records in een bepaald tijdsbestek te selecteren.

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

Samengetelde parameters per dag, maand of jaar

In het volgende voorbeeld worden taxiritgegevens geaggregeerd op een geparameteriseerd granulariteitsniveau. De DATE_TRUNC functie kapt de tpep_pickup_datetime waarde af op basis van de :date_granularity parameterwaarde, zoals DAY, MONTHof YEAR. De afgekapte datum wordt als alias gebruikt date_rollup in de GROUP BY component.

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

Meerdere waarden in één query gebruiken

In het volgende voorbeeld wordt de ARRAY_CONTAINS functie gebruikt om een lijst met waarden te filteren. SPLIT Met de TRANSFORMfuncties kunnen meerdere door komma's gescheiden waarden worden doorgegeven als een tekenreeksparameter.

De :list_parameter waarde gebruikt een lijst met door komma's gescheiden waarden. De SPLIT functie parseert die lijst en splitst de door komma's gescheiden waarden in een matrix. Met TRANSFORM de functie wordt elk element in de matrix getransformeerd door een witruimte te verwijderen. De ARRAY_CONTAINS functie controleert of de dropoff_zip waarde uit de trips tabel is opgenomen in de matrix met waarden die zijn doorgegeven als de list_parameter.


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

Notitie

Dit voorbeeld werkt voor tekenreekswaarden. Als u de query voor andere gegevenstypen, zoals een lijst met gehele getallen, wilt wijzigen, verpakt u de TRANSFORM bewerking met een CAST bewerking om de tekenreekswaarden te converteren naar het gewenste gegevenstype.

Syntaxiswijzigingen

De volgende tabel bevat veelvoorkomende use cases voor parameters, de oorspronkelijke Databricks SQL mustache syntaxis en de equivalente syntaxis met behulp van de benoemde parametermarkeringssyntaxis.

Use-case voor parameters Parametersyntaxis van mustache Benoemde parametermarkeringssyntaxis
Alleen gegevens laden vóór een opgegeven datum WHERE date_field < '{{date_param}}'

U moet aanhalingstekens rond de datumparameter en accolades opnemen.		 WHERE date_field < :date_param
Alleen gegevens laden die kleiner zijn dan een opgegeven numerieke waarde WHERE price < {{max_price}} WHERE price < :max_price
Twee tekenreeksen vergelijken WHERE region = {{region_param}} WHERE region = :region_param
De tabel opgeven die in een query wordt gebruikt SELECT * FROM {{table_name}} SELECT * FROM IDENTIFIER(:table)

Wanneer een gebruiker deze parameter invoert, moet deze de volledige naamruimte op drie niveaus gebruiken om de tabel te identificeren.
Geef onafhankelijk de catalogus, het schema en de tabel op die in een query worden gebruikt SELECT * FROM {{catalog}}.{{schema}}.{{table}} SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table)
Parameters gebruiken als sjabloon in een langere, opgemaakte tekenreeks "({{area_code}}) {{phone_number}}

Parameterwaarden worden automatisch samengevoegd als een tekenreeks.		 format_string("(%d)%d, :area_code, :p hone_number)

Zie Meerdere parameters samenvoegen voor een volledig voorbeeld.
Een interval maken SELECT INTERVAL {{p}} MINUTE SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Parametersyntaxis van mustache

Belangrijk

De volgende secties zijn van toepassing op querysyntaxis die u alleen in de SQL-editor kunt gebruiken. Dit betekent dat als u een query kopieert en plakt met deze syntaxis in een andere Azure Databricks-interface, zoals een notebook- of AI/BI-dashboardgegevensseteditor, de query handmatig moet worden aangepast om benoemde parametermarkeringen te gebruiken voordat deze zonder fouten wordt uitgevoerd.

In de SQL-editor wordt elke tekenreeks tussen dubbele accolades {{ }} behandeld als een queryparameter. Er wordt een widget weergegeven boven het resultatenvenster waar u de parameterwaarde instelt. Hoewel Azure Databricks over het algemeen het gebruik van benoemde parametermarkeringen aanbeveelt, wordt sommige functionaliteit alleen ondersteund met behulp van de syntaxis van de mustache-parameter.

Gebruik de syntaxis van de mustache-parameter voor de volgende functionaliteit:

Een mustache-parameter toevoegen

  1. Typ Cmd + I. De parameter wordt ingevoegd in de tekst caret en het dialoogvenster Parameter toevoegen wordt weergegeven.
    • Trefwoord: het trefwoord dat de parameter in de query vertegenwoordigt.
    • Titel: De titel die over de widget wordt weergegeven. De titel is standaard hetzelfde als het trefwoord.
    • Type: Ondersteunde typen zijn tekst, getal, datum, datum en tijd, datum en tijd (met seconden), vervolgkeuzelijst en vervolgkeuzelijst op basis van query's. De standaardwaarde is de tekst.
  2. Voer het trefwoord in, overschrijf eventueel de titel en selecteer het parametertype.
  3. Klik op Parameter toevoegen.
  4. Stel in de parameterwidget de parameterwaarde in.
  5. Klik op Wijzigingen toepassen.
  6. Klik op Opslaan.

U kunt ook dubbele accolades {{ }} typen en op het tandwielpictogram in de buurt van de parameterwidget klikken om de instellingen te bewerken.

Als u de query opnieuw wilt uitvoeren met een andere parameterwaarde, voert u de waarde in de widget in en klikt u op Wijzigingen toepassen.

Een queryparameter bewerken

Als u een parameter wilt bewerken, klikt u op het tandwielpictogram naast de parameterwidget. Als u wilt voorkomen dat gebruikers die geen eigenaar zijn van de query de parameter wijzigen, klikt u op Alleen resultaten weergeven. Het <Keyword> parameterdialoogvenster wordt weergegeven.

Een queryparameter verwijderen

Als u een queryparameter wilt verwijderen, verwijdert u de parameter uit uw query. De parameterwidget verdwijnt en u kunt uw query opnieuw schrijven met behulp van statische waarden.

De volgorde van parameters wijzigen

Als u de volgorde wilt wijzigen waarin parameters worden weergegeven, kunt u op elke parameter klikken en naar de gewenste positie slepen.

Queryparametertypen

Sms verzenden

Neemt een tekenreeks als invoer. Backslash-, enkele en dubbele aanhalingstekens worden ontsnapt en Azure Databricks voegt aanhalingstekens toe aan deze parameter. Een tekenreeks zoals mr's Li"s deze wordt omgezet in 'mr\'s Li\"s' een voorbeeld van het gebruik hiervan kan bijvoorbeeld zijn

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

Aantal

Neemt een getal als invoer. Een voorbeeld van het gebruik hiervan kan zijn

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

Als u het bereik van mogelijke parameterwaarden wilt beperken bij het uitvoeren van een query, gebruikt u het parametertype Vervolgkeuzelijst . Een voorbeeld hiervan is SELECT * FROM users WHERE name='{{ dropdown_param }}'. Wanneer u deze optie selecteert in het deelvenster parameterinstellingen, wordt er een tekstvak weergegeven waarin u de toegestane waarden invoert, elke waarde gescheiden door een nieuwe regel. Vervolgkeuzelijsten zijn tekstparameters. Als u datums of datums en tijden in de vervolgkeuzelijst wilt gebruiken, voert u deze in de indeling in die uw gegevensbron nodig heeft. De tekenreeksen worden niet ontsnapt. U kunt kiezen tussen een vervolgkeuzelijst met één waarde of meerdere waarden.

  • Enkele waarde: enkele aanhalingstekens rond de parameter zijn vereist.
  • Meerdere waarden: schakel de optie Meerdere waarden toestaan in. Kies in de vervolgkeuzelijst Offerte of u de parameters wilt laten staan zoals opgegeven (geen aanhalingstekens) of de parameters tussen enkele of dubbele aanhalingstekens laten staan. U hoeft geen aanhalingstekens toe te voegen rond de parameter als u aanhalingstekens kiest.

Wijzig uw WHERE component om het IN trefwoord in uw query te gebruiken.

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

Met de parameterwidget voor meerdere selecties kunt u meerdere waarden doorgeven aan de database. Als u de optie Dubbele aanhalingstekens voor de parameter Aanhalingsteken selecteert, geeft uw query de volgende indeling weer: WHERE IN ("value1", "value2", "value3")

Vervolgkeuzelijst op basis van query's

Neemt het resultaat van een query als invoer. Het heeft hetzelfde gedrag als de parameter vervolgkeuzelijst . U moet de Databricks SQL-vervolgkeuzelijstquery opslaan om deze als invoer in een andere query te kunnen gebruiken.

  1. Klik op vervolgkeuzelijst op basis van query's onder Type in het deelvenster Instellingen.
  2. Klik op het veld Query en selecteer een query. Als uw doelquery een groot aantal records retourneert, worden de prestaties verslechterd.

Als uw doelquery meer dan één kolom retourneert, gebruikt Databricks SQL de eerste . Als uw doelquery retourneert en kolommen retourneertname, vult Databricks SQL de widget parameterselectie in met de name kolom, maar voert u de query uit met de bijbehorendevalue.value

Stel dat de volgende query de gegevens in de tabel retourneert.

SELECT user_uuid AS 'value', username AS 'name'
FROM users
waarde naam
1001 John Smith
1002 Jane Doe
1003 Bobby Tables

Wanneer Azure Databricks de query uitvoert, is de waarde die wordt doorgegeven aan de database 1001, 1002 of 1003.

Datum en tijd

Azure Databricks heeft verschillende opties voor het parameteriseren van datum- en tijdstempelwaarden, waaronder opties om de parameterisering van tijdsbereiken te vereenvoudigen. U kunt kiezen uit drie opties met verschillende precisie:

Optie Precisie Type
Datum dagen DATE
Datum en tijd minuten TIMESTAMP
Datum en tijd (met seconden) seconde TIMESTAMP

Wanneer u een bereikparameteroptie kiest, maakt u twee parameters die zijn aangewezen door .start en .end achtervoegsels. Alle opties geven parameters door aan uw query als letterlijke tekenreeksen; Voor Azure Databricks moeten datum- en tijdwaarden tussen enkele aanhalingstekens (') worden verpakt. Voorbeeld:

-- 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 }}'

Datumparameters gebruiken een interface voor het kiezen van agenda's en gebruiken standaard de huidige datum en tijd.

Notitie

De parameter Datumbereik retourneert alleen de juiste resultaten voor kolommen van het DATE type. Gebruik TIMESTAMP voor kolommen een van de opties datum- en tijdsbereik.

Dynamische datum- en datumbereikwaarden

Wanneer u een datum- of datumbereikparameter aan uw query toevoegt, wordt in de selectiewidget een blauw bliksemschichtpictogram weergegeven. Klik erop om dynamische waarden weer te geven, zoals , , , , of last yearlast month. last weekthis weekyesterdaytoday Deze waarden worden dynamisch bijgewerkt.

Belangrijk

Dynamische datums en datumbereiken zijn niet compatibel met geplande query's.

Queryparameters gebruiken in dashboards

Optioneel kunnen query's parameters of statische waarden gebruiken. Wanneer een visualisatie op basis van een geparameteriseerde query wordt toegevoegd aan een dashboard, kan de visualisatie worden geconfigureerd voor het gebruik van een van de volgende opties:

  • Widgetparameter

    Widgetparameters zijn specifiek voor één visualisatie in een dashboard, worden weergegeven in het visualisatievenster en de opgegeven parameterwaarden zijn alleen van toepassing op de query die onder de visualisatie wordt uitgevoerd.

  • Dashboardparameter

    Dashboardparameters kunnen worden toegepast op meerdere visualisaties. Wanneer u een visualisatie toevoegt op basis van een geparameteriseerde query aan een dashboard, wordt de parameter standaard toegevoegd als een dashboardparameter. Dashboardparameters zijn geconfigureerd voor een of meer visualisaties in een dashboard en worden boven aan het dashboard weergegeven. De parameterwaarden die zijn opgegeven voor een dashboardparameter zijn van toepassing op visualisaties die die specifieke dashboardparameter hergebruiken. Een dashboard kan meerdere parameters bevatten, die elk kunnen worden toegepast op sommige visualisaties en niet op andere.

  • Statische waarde

    Statische waarden worden gebruikt in plaats van een parameter die reageert op wijzigingen. Met statische waarden kunt u een waarde hard coden in plaats van een parameter. Ze zorgen ervoor dat de parameter 'verdwijnt' uit het dashboard of de widget waar deze eerder werd weergegeven.

Wanneer u een visualisatie met een geparameteriseerde query toevoegt, kunt u de titel en de bron voor de parameter in de visualisatiequery kiezen door op het juiste potloodpictogram te klikken. U kunt ook het trefwoord en een standaardwaarde selecteren. Zie parametereigenschappen.

Nadat u een visualisatie aan een dashboard hebt toegevoegd, opent u de interface voor parametertoewijzing door in de rechterbovenhoek van een dashboardwidget te klikken op het menu Voor de parametertoewijzing en vervolgens op Widgetinstellingen wijzigen te klikken.

Parametereigenschappen

  • Titel: De weergavenaam die naast de waardekiezer op uw dashboard wordt weergegeven. Deze wordt standaard ingesteld op het parameterwoord. Als u het wilt bewerken, klikt u op het potloodpictogram Potloodpictogram. Titels worden niet weergegeven voor statische dashboardparameters omdat de waardekiezer verborgen is. Als u statische waarde als waardebron selecteert, wordt het veld Titel grijs weergegeven.

  • Trefwoord: de letterlijke tekenreeks voor deze parameter in de onderliggende query. Dit is handig voor foutopsporing als uw dashboard niet de verwachte resultaten retourneert.

  • Standaardwaarde: de waarde die wordt gebruikt als er geen andere waarde is opgegeven. Als u dit wilt wijzigen vanuit het queryscherm, voert u de query uit met de gewenste parameterwaarde en klikt u op de knop Opslaan .

  • Waardebron: de bron van de parameterwaarde. Klik op het potloodpictogram Potloodpictogram om een bron te kiezen.

    • Nieuwe dashboardparameter: maak een nieuwe parameter op dashboardniveau. Hiermee kunt u een parameterwaarde instellen op één plaats op uw dashboard en deze toewijzen aan meerdere visualisaties.
    • Bestaande dashboardparameter: Wijs de parameter toe aan een bestaande dashboardparameter. U moet opgeven welke bestaande dashboardparameter al bestaat.
    • Widgetparameter: Geeft een waardekiezer weer in de dashboardwidget. Dit is handig voor eenmalige parameters die niet worden gedeeld tussen widgets.
    • Statische waarde: Kies een statische waarde voor de widget, ongeacht de waarden die op andere widgets worden gebruikt. Statisch toegewezen parameterwaarden geven nergens een waardekiezer weer op het dashboard, wat compacter is. Hiermee kunt u profiteren van de flexibiliteit van queryparameters zonder dat u de gebruikersinterface op een dashboard onoverzichtelijk maakt wanneer bepaalde parameters naar verwachting niet regelmatig worden gewijzigd.

    Parametertoewijzing wijzigen

Veelgestelde vragen (FAQ)

Kan ik dezelfde parameter meerdere keren opnieuw gebruiken in één query?

Ja. Gebruik dezelfde id in de accolades. In dit voorbeeld wordt de {{org_id}} parameter twee keer gebruikt.

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

Kan ik meerdere parameters in één query gebruiken?

Ja. Gebruik een unieke naam voor elke parameter. In dit voorbeeld worden twee parameters gebruikt: {{org_id}} en {{start_date}}.

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