Arbeta med förfrågan parameters
Den här artikeln beskriver hur du arbetar med parameters i Azure Databricks SQL-redigeraren.
Med fråga parameters kan du göra dina frågor mer dynamiska och flexibla genom att infoga variabeln values vid den tidpunkt då körning sker. I stället för att hårdkoda specifika values i dina frågor kan du definiera parameters för att filtrera data eller ändra utdata baserat på användarindata. Den här metoden förbättrar återanvändning av frågor, förbättrar säkerheten genom att förhindra SQL-inmatning och möjliggör effektivare hantering av olika datascenarier.
Namngiven parametermarkörsyntax
Namngivna parametermarkörer är typindelade platshållarvariabler. Använd den här syntaxen för att skriva frågor i följande delar av Azure Databricks-användargränssnittet:
- SQL-redigerare
- Notebook-filer
- AI/BI-instrumentpanelens datauppsättningsredigerare
- AI/BI Genie spaces (offentlig förhandsversion)
Insert
parameters i dina SQL-frågor genom att skriva ett kolon följt av ett parameternamn, till exempel :parameter_name. När du inkluderar en namngiven parametermarkör i en fråga visas en widget i användargränssnittet. Du kan använda widgeten för att redigera parametertypen och namnet.
Lägga till en namngiven parametermarkör i en fråga
I det här exemplet läggs en parametermarkör till i följande fråga:
SELECT
trip_distance,
fare_amount
FROM
samples.nyctaxi.trips
WHERE
fare_amount < 5
Den här frågan returnerar en datamängd som endast innehåller prisbelopp som är under fem dollar. Använd följande steg för att redigera frågan för att använda en parameter i stället för det hårdkodade värdet (5).
- Ta bort nummer 5 från frågan.
- Skriv ett kolon (:) följt av strängen
fare_parameter. Den sista raden i den uppdaterade frågan ska innehålla .fare_amount < :fare_parameter - Klicka på kugghjulsikonen
nära parameterwidgeten. Dialogrutan visar följande fält: - Nyckelord: Nyckelordet som representerar parametern i frågan. Fältet kan inte redigeras. Om du vill ändra nyckelordet redigerar du markören i SQL-frågan.
- Rubrik: Rubriken som visas över widgeten. Som standard är rubriken samma som nyckelordet.
- typ: Stödde typer är Text, Nummer, Listruta List, Datum, Datum och tid, samt Datum och tid (med sekunder). Standardvärdet är Text.
- I dialogrutan ändrar du Typen till Tal.
- Ange ett tal i parameterwidgeten och klicka på Tillämpa ändringar.
- Spara frågan genom att klicka på Spara.
Namngivna parametersyntaxexempel
Följande exempel visar några vanliga användningsfall för parameters.
Insert ett datum
I följande exempel finns en datumparameter som begränsar frågeresultat till poster efter ett visst 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
Insert ett tal
I följande exempel finns parametern Number som begränsar resultatet till poster where fältet o_total_price är större än det angivna parametervärdet.
SELECT
o_orderdate AS Date,
o_orderpriority AS Priority,
o_totalprice AS Price
FROM
samples.tpch.orders
WHERE
o_totalprice > :num_param
Insert ett fältnamn
I följande exempel field_param används med IDENTIFIER funktionen för att ange ett tröskelvärde för frågan vid körning. Parametervärdet ska vara ett column-namn från table som används i sökfrågan.
SELECT
*
FROM
samples.tpch.orders
WHERE
IDENTIFIER(:field_param) < 10000
Insert databasobjekt
I följande exempel skapas tre parameters: catalog, schemaoch table.
SELECT
*
FROM
IDENTIFIER(:catalog || '.' || :schema || '.' || :table)
Sammanfoga flera parameters
Du kan inkludera parameters i andra SQL-funktioner. I det här exemplet kan användaren select en anställds titel och ett ID-nummer. Frågan använder format_string funktionen för att sammanfoga de två strängarna och filtrera på de rader som matchar. Se funktionen format_string.
SELECT
o_orderkey,
o_clerk
FROM
samples.tpch.orders
WHERE
o_clerk LIKE format_string('%s%s', :title, :emp_number)
Arbeta med JSON-strängar
Du kan använda parameters för att extrahera ett attribut från en JSON-sträng. I följande exempel används from_json funktionen för att konvertera JSON-strängen till ett struct-värde. Om du ersätter strängen a som värde för parametern (param) returneras attributet 1.
SELECT
from_json('{"a": 1}', 'map<string, int>') [:param]
Skapa ett intervall
Typen INTERVAL representerar en tidsperiod och gör att du kan utföra tidsbaserad aritmetik och åtgärder. I följande exempel används en CAST-funktion för att omvandla parametern som intervalltyp. Det resulterande INTERVAL värdet kan användas för tidsbaserade beräkningar eller filtrering i frågan.
Se INTERVALLtyp för fullständig information och syntax.
SELECT CAST(:param AS INTERVAL MINUTE)
Lägga till ett datumintervall
I följande exempel visas hur du lägger till ett parameteriserat datumintervall till select-poster inom en viss tidsram.
SELECT * FROM samples.nyctaxi.trips
WHERE tpep_pickup_datetime
BETWEEN :start_date AND :end_date
Parametrisera sammanslagningar efter dag, månad eller år
I följande exempel aggregeras taxiresasdata på en parameteriserad kornighetsnivå. Funktionen DATE_TRUNC trunkerar tpep_pickup_datetime värdet baserat på :date_granularity parametervärdet, till exempel DAY, MONTHeller YEAR. Det trunkerade datumet aliaseras som date_rollup och används i GROUP BY -satsen.
SELECT DATE_TRUNC(:date_granularity, tpep_pickup_datetime) AS
date_rollup,
COUNT(*) AS total_trips
FROM samples.nyctaxi.trips
GROUP BY date_rollup
Använd flera values i en enda fråga
I följande exempel används funktionen ARRAY_CONTAINS för att filtrera en list av values. Funktionerna TRANSFORM, och SPLIT tillåter att flera kommaavgränsade values skickas in som en strängparameter.
Värdet :list_parameter tar ett list kommaseparerat values. Funktionen SPLIT analyserar listoch delar upp den komma-avgränsade values i en matris. Funktionen TRANSFORM transformerar varje element i matrisen genom att ta bort alla blanksteg. Funktionen ARRAY_CONTAINS kontrollerar om värdet för dropoff_zip från tripstable finns i matrisen för values som skickas som list_parameter.
SELECT * FROM samples.nyctaxi.trips WHERE
array_contains(
TRANSFORM(SPLIT(:list_parameter, ','), s -> TRIM(s)),
dropoff_zip
)
Kommentar
Det här exemplet fungerar för sträng values. Om du vill ändra frågan för andra datatyper, till exempel en list med heltal, omsluter du åtgärden TRANSFORM med en CAST åtgärd för att konvertera strängen values till önskad datatyp.
Syntaxändringar
Följande table visar vanliga användningsfall för parameters, den ursprungliga Databricks SQL-mustaschsyntaxen och motsvarande syntax vid användning av namngiven parametermarkörsyntax.
| Parameteranvändningsfall | Parametersyntax för mustasch | Namngiven parametermarkörsyntax |
|---|---|---|
| Läs endast in data före ett angivet datum | WHERE date_field < '{{date_param}}'Du måste inkludera citattecken runt datumparametern och klammerparenteser. |
WHERE date_field < :date_param |
| Läs endast in data som är mindre än ett angivet numeriskt värde | WHERE price < {{max_price}} |
WHERE price < :max_price |
| Jämför två strängar | WHERE region = {{region_param}} |
WHERE region = :region_param |
| Ange table som används i en fråga | SELECT * FROM {{table_name}} |
SELECT * FROM IDENTIFIER(:table)När en användare anger den här parametern bör de använda hela namnområdet på tre nivåer för att identifiera table. |
| Ange oberoende catalog, schemaoch table som används i en fråga | SELECT * FROM {{catalog}}.{{schema}}.{{table}} |
SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table) |
| Använd parameters som en mall i en längre, formaterad sträng | "({{area_code}}) {{phone_number}}" Parametern values sammanfogas automatiskt som en sträng. |
format_string("(%d)%d, :area_code, :p hone_number) Se för ett fullständigt exempel på hur flera parameters kan sammanfogas. |
| Skapa ett intervall | SELECT INTERVAL {{p}} MINUTE |
SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE) |
Parametersyntax för mustasch
Viktigt!
Följande avsnitt gäller för frågesyntax som du endast kan använda i SQL-redigeraren. Det innebär att om du kopierar och klistrar in en fråga med den här syntaxen i något annat Azure Databricks-gränssnitt, till exempel en notebook- eller AI/BI-instrumentpanelsdatauppsättningsredigerare, måste frågan justeras manuellt för att använda namngivna parametermarkörer innan den körs utan fel.
I SQL-redigeraren behandlas alla strängar mellan dubbla klammerparenteser {{ }} som en frågeparameter. En widget visas ovanför resultatfönstret where du set parametervärdet. Azure Databricks rekommenderar vanligtvis namngivna parametermarkörer, men vissa funktioner stöds endast med hjälp av mustaschparametersyntax.
Använd mustaschparametersyntax för följande funktioner:
Lägga till en mustaschparameter
- Skriv
Cmd + I. Parametern infogas i textomsorgen och dialogrutan Lägg till parameter visas.- Nyckelord: Nyckelordet som representerar parametern i frågan.
- Rubrik: Rubriken som visas över widgeten. Som standard är rubriken samma som nyckelordet.
- typ: Typer som stöds är Text, Tal, Datum, Datum och tid, Datum och tid (med sekunder), Listruta Listoch Frågebaserad listruta List. Standardvärdet är Text.
- Ange nyckelordet, åsidosätt rubriken om så önskas, och select för parametertypen.
- Klicka på Lägg till parameter.
- I parameterwidgeten ska du göra parametervärdet till set.
- Klicka på Tillämpa ändringar.
- Klicka på Spara.
Du kan också skriva dubbla klammerparenteser och klicka på kugghjulsikonen {{ }} nära parameterwidgeten för att redigera inställningarna.
Om du vill köra frågan igen med ett annat parametervärde anger du värdet i widgeten och klickar på Tillämpa ändringar.
Redigera en frågeparameter
Om du vill redigera en parameter klickar du på kugghjulsikonen bredvid parameterwidgeten. Om du vill förhindra att användare som inte äger frågan ändrar parametern klickar du på Visa endast resultat. Parameterdialogrutan <Keyword> visas.
Remove en frågeparameter
Om du vill remove en frågeparameter tar du bort parametern från din fråga. Parameterwidgeten försvinner och du kan skriva om frågan med statisk values.
Ändra ordningen på parameters
Om du vill ändra i vilken ordning parameters visas kan du klicka och dra varje parameter till önskad position.
Frågeparametertyper
Text
Tar en sträng som indata. Omvänt snedstreck, enkla och dubbla citattecken är undantagna och Azure Databricks lägger till citattecken i den här parametern. Till exempel omvandlas en sträng som mr's Li"s omvandlas till 'mr\'s Li\"s' Ett exempel på hur du använder detta kan vara
SELECT * FROM users WHERE name={{ text_param }}
Antal
Tar ett tal som indata. Ett exempel på hur du använder detta kan vara
SELECT * FROM users WHERE age={{ number_param }}
Listruta List
Om du vill begränsa omfånget för möjliga parametrar values när du kör en fråga använder du listrutan List parametertyp. Ett exempel skulle vara SELECT * FROM users WHERE name='{{ dropdown_param }}'. När du väljer från panelen för parameterinställningar visas en textruta where du anger din tillåtna values, varje värde avgränsat med en ny rad. Dropdown-listor är text vid parameters. Om du vill använda datum eller datum och tider i listrutan Listanger du dem i det format som datakällan kräver. Strängarna är inte undantagna. Du kan välja mellan en listruta med ett värde eller flera värden.
- Enskilt värde: Enkla citattecken runt parametern krävs.
- Flera värden: Aktivera/deaktivera alternativet Tillåt flera values. I listrutan Citat väljer du om du vill lämna parameters som angivet (inga citattecken) eller omsluta parameters med enkla eller dubbla citattecken. Du behöver inte lägga till citattecken runt parametern om du väljer citattecken.
WHERE Ändra satsen så att nyckelordet IN används i frågan.
SELECT ...
FROM ...
WHERE field IN ( {{ Multi Select Parameter }} )
Med parameterwidgeten för flera val kan du skicka flera values till databasen. Om du select alternativet dubbla citattecken för parametern Quotation återspeglar frågan följande format: WHERE IN ("value1", "value2", "value3")
Query-Based Listruta List
Tar resultatet av en fråga som indata. Den har samma beteende som parametern Dropdown List. Du måste spara Databricks SQL-fråga list i listrutan så att du kan använda den som indata i en annan fråga.
- Klicka på frågebaserad listruta list under Typ i inställningspanelen.
- Klicka på fältet Query och select en sökfråga. Om målfrågan returnerar ett stort antal poster försämras prestandan.
Om målfrågan returnerar mer än en columnanvänder Databricks SQL första en. Om målfrågan returnerar name och valuecolumnsfyller Databricks SQL i widgeten för parameterval med namecolumn men kör frågan med den associerade value.
Anta till exempel att följande fråga returnerar data i table.
SELECT user_uuid AS 'value', username AS 'name'
FROM users
| värde | name |
|---|---|
| 1001 | John Svensson |
| 1002 | Jane Doe |
| 1003 | Bobby Tables |
När Azure Databricks kör frågan blir värdet som skickas till databasen 1001, 1002 eller 1003.
Datum och tid
Azure Databricks har flera alternativ för att parametrisera datum och tidsstämpel values, inklusive alternativ för att förenkla parameteriseringen av tidsintervall. Select från tre alternativ med varierande precision:
| Alternativ | Precision | Typ |
|---|---|---|
| Datum | dag | DATE |
| Datum och tid | minut | TIMESTAMP |
| Datum och tid (med sekunder) | sekund | TIMESTAMP |
När du väljer parameteralternativet Range skapar du två parameters som anges av .start och .end suffix. Alla alternativ skickar parameters till din fråga som strängliteraler. Azure Databricks kräver att du omsluter datum och tid values inom enkla citattecken ('). Till exempel:
-- 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 }}'
Datum parameters använder ett kalenderplockningsgränssnitt, och standardvärdet är det aktuella datumet och tiden.
Kommentar
Parametern Datumintervall returnerar endast rätt resultat för columns av DATE typ. För TIMESTAMPcolumnsanvänder du något av alternativen Datum och Tidsintervall.
Dynamiskt datum- och datumintervall values
När du lägger till en datum- eller datumintervallparameter i din fråga visar markeringswidgeten en blå blixtikon. Klicka på den om du vill visa dynamiska values som today, yesterday, this week, last week, last montheller last year. Dessa valuesupdate dynamiskt.
Viktigt!
Dynamiska datum och datumintervall är inte kompatibla med schemalagda frågor.
Använda fråga parameters i instrumentpaneler
Frivilligt kan frågor använda parameters eller statiska values. När en visualisering baserad på en parametriserad fråga läggs till på en instrumentpanel kan visualiseringen konfigureras att använda antingen en:
Widgetparameter
Widget-parameters är specifika för en enda visualisering på en instrumentpanel, visas i visualiseringspanelen och parametern values anges gäller endast för frågan som ligger bakom visualiseringen.
Instrumentpanelsparameter
Instrumentpanel parameters kan tillämpas på flera visualiseringar. När du lägger till en visualisering baserat på en parametriserad fråga på en instrumentpanel läggs parametern som standard till som en instrumentpanelsparameter. Instrumentpanel parameters är konfigurerad för en eller flera visualiseringar i en instrumentpanel och visas högst upp på instrumentpanelen. Parametern values som anges för en instrumentpanelsparameter gäller för visualiseringar som återanvänder den specifika instrumentpanelsparametern. En instrumentpanel kan ha flera parameters, som var och en kan gälla för vissa visualiseringar men inte för andra.
Statiskt värde
Statiska values används i stället för en parameter som svarar på ändringar. Med statisk values kan du hårdkoda ett värde i stället för en parameter. De gör att parametern "försvinner" från instrumentpanelen eller widgeten where där den tidigare visades.
När du lägger till en visualisering som innehåller en parametriserad fråga kan du välja rubrik och källa för parametern i visualiseringsfrågan genom att klicka på lämplig pennikon. Du kan också select nyckelordet och ett standardvärde. Se Parameteregenskaper.
När du har lagt till en visualisering på en instrumentpanel får du åtkomst till parametermappningsgränssnittet genom att klicka på menyn för kebab längst upp till höger i en instrumentpanelswidget och sedan klicka på Ändra widgetinställningar.
Parameteregenskaper
Rubrik: Visningsnamnet som visas bredvid värdeväljaren på instrumentpanelen. Den är som standard parametern Nyckelord. Om du vill redigera den klickar du på pennikonen
. Rubriker visas inte för statiska instrumentpaneler parameters eftersom värdeväljaren är dold. Om du väljer selectStatic value som källa för värde, är fältet Rubrik nedtonat.Nyckelord: Strängliteralen för den här parametern i den underliggande frågan. Detta är användbart för felsökning om instrumentpanelen inte returnerar det förväntade resultatet.
Standardvärde: Det värde som används om inget annat värde har angetts. Om du vill ändra detta från frågeskärmen kör du frågan med önskat parametervärde och klickar på knappen Spara .
Värdekälla: Källan till parametervärdet. Klicka på pennikonen
för att välja en källa.- Ny instrumentpanelsparameter: Skapa en ny parameter på instrumentpanelsnivå. På så sätt kan du set ett parametervärde på en plats på instrumentpanelen och mappa det till flera visualiseringar.
- Befintlig instrumentpanelsparameter: Mappa parametern till en befintlig instrumentpanelsparameter. Du måste ange vilken befintlig instrumentpanelsparameter.
- Widgetparameter: Visar en värdeväljare i instrumentpanelswidgeten. Detta är användbart för separata parameters som inte delas mellan widgetar.
- Statiskt värde: Välj ett statiskt värde för widgeten, oavsett vilka values som används på andra widgetar. Statiskt mappad parameter values inte visar någon värdeväljare någonstans på instrumentpanelen, vilket är mer kompakt. På så sätt kan du dra nytta av flexibiliteten i sökfråga parameters utan att belasta användargränssnittet på en kontrollpanel då vissa parameters inte förväntas ändras ofta.
Vanliga frågor och svar (FAQ)
- Kan jag återanvända samma parameter flera gånger i en enda fråga?
- Kan jag använda flera parameters i en enda fråga?
Kan jag återanvända samma parameter flera gånger i en enda fråga?
Ja. Använd samma identifier inom klammerparenteserna. I det här exemplet används parametern {{org_id}} två gånger.
SELECT {{org_id}}, count(0)
FROM queries
WHERE org_id = {{org_id}}
Kan jag använda flera parameters i en enda fråga?
Ja. Använd ett unikt namn för varje parameter. I det här exemplet används två parameters: {{org_id}} och {{start_date}}.
SELECT count(0)
FROM queries
WHERE org_id = {{org_id}} AND created_at > '{{start_date}}'