Arbeta med frågeparametrar
Den här artikeln beskriver hur du arbetar med frågeparametrar i Azure Databricks SQL-redigeraren.
Med frågeparametrar kan du göra dina frågor mer dynamiska och flexibla genom att infoga variabelvärden vid körning. I stället för att hårdkoda specifika värden i dina frågor kan du definiera parametrar 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)
Infoga parametrar 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: Typer som stöds är Text, Tal, Listruta, 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 parametrar.
Infoga 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
Infoga ett tal
I följande exempel finns en talparameter som begränsar resultatet till poster där o_total_price fältet ä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
Infoga 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 kolumnnamn från den tabell som används i frågan.
SELECT
*
FROM
samples.tpch.orders
WHERE
IDENTIFIER(:field_param) < 10000
Infoga databasobjekt
I följande exempel skapas tre parametrar: catalog, schemaoch table.
SELECT
*
FROM
IDENTIFIER(:catalog || '.' || :schema || '.' || :table)
Se IDR-sats.
Sammanfoga flera parametrar
Du kan inkludera parametrar i andra SQL-funktioner. I det här exemplet kan visningsprogrammet välja en anställds titel och ett nummer-ID. 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 parametrar 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 ingår parametern i en format_string funktion som sedan gjuts som en 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(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)
Lägga till ett datumintervall
I följande exempel visas hur du lägger till ett parameteriserat datumintervall för att välja 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ända flera värden i en enskild fråga
I följande exempel används ARRAY_CONTAINS funktionen för att filtrera en lista med värden. Funktionerna TRANSFORM, och SPLIT tillåter att flera kommaavgränsade värden skickas in som en strängparameter.
Värdet :list_parameter tar en lista över kommaavgränsade värden. Funktionen SPLIT parsar listan och delar upp kommaavgränsade värden i en matris. Funktionen TRANSFORM transformerar varje element i matrisen genom att ta bort alla blanksteg. Funktionen ARRAY_CONTAINS kontrollerar om dropoff_zip värdet från trips tabellen finns i matrisen med värden som list_parameterskickas som .
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ängvärden. Om du vill ändra frågan för andra datatyper, till exempel en lista över heltal, omsluter TRANSFORM du åtgärden med en CAST åtgärd för att konvertera strängvärdena till önskad datatyp.
Syntaxändringar
I följande tabell visas vanliga användningsfall för parametrar, den ursprungliga Databricks SQL-mustaschsyntaxen och motsvarande syntax med hjälp 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 tabellen 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 tabellen. |
| Oberoende ange katalogen, schemat och tabellen som används i en fråga | SELECT * FROM {{catalog}}.{{schema}}.{{table}} |
SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table) |
| Använda parametrar som en mall i en längre, formaterad sträng | "({{area_code}}) {{phone_number}}" Parametervärden sammanfogas automatiskt som en sträng. |
format_string("(%d)%d, :area_code, :p hone_number) Se Sammanfoga flera parametrar för ett fullständigt exempel. |
| 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 där du anger 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 och frågebaserad listruta. Standardvärdet är Text.
- Ange nyckelordet, om du vill åsidosätta rubriken och välj parametertypen.
- Klicka på Lägg till parameter.
- I parameterwidgeten anger du parametervärdet.
- 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.
Ta bort en frågeparameter
Ta bort en frågeparameter genom att ta bort parametern från din fråga. Parameterwidgeten försvinner och du kan skriva om frågan med hjälp av statiska värden.
Ändra ordningen på parametrar
Om du vill ändra i vilken ordning parametrarna 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
Om du vill begränsa omfånget för möjliga parametervärden när du kör en fråga använder du parametertypen Listruta . 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 där du anger dina tillåtna värden, varje värde avgränsat med en ny rad. Listrutor är textparametrar. Om du vill använda datum eller datum och tider i listrutan anger 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.
- Flervärde: Växla alternativet Tillåt flera värden . I listrutan Citat väljer du om du vill lämna parametrarna som angivna (inga citattecken) eller omsluta parametrarna 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 värden till databasen. Om du väljer alternativet Dubbel citattecken för parametern Quotation visar frågan följande format: WHERE IN ("value1", "value2", "value3")
Frågebaserad listruta
Tar resultatet av en fråga som indata. Den har samma beteende som parametern Listruta . Du måste spara listrutan Databricks SQL för att kunna använda den som indata i en annan fråga.
- Klicka på Listrutan Frågebaserad under Typ i inställningspanelen.
- Klicka på fältet Fråga och välj en fråga. Om målfrågan returnerar ett stort antal poster försämras prestandan.
Om målfrågan returnerar mer än en kolumn använder Databricks SQL den första . Om målfrågan returnerar name och value kolumner fyller Databricks SQL i parametervalswidgeten name med kolumnen men kör frågan med tillhörande value.
Anta till exempel att följande fråga returnerar data i tabellen.
SELECT user_uuid AS 'value', username AS 'name'
FROM users
| värde | name |
|---|---|
| 1001 | John Svensson |
| 1002 | Jane Doe |
| 1003 | Bobby-tabeller |
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ämpelvärden, inklusive alternativ för att förenkla parameteriseringen av tidsintervall. Välj bland 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 alternativet Intervallparameter skapar du två parametrar som anges av .start och .end suffix. Alla alternativ skickar parametrar till din fråga som strängliteraler. Azure Databricks kräver att du omsluter datum- och tidsvärden 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 }}'
Datumparametrar använder ett kalenderplockningsgränssnitt och standardvärdet för aktuellt datum och tid.
Kommentar
Parametern Datumintervall returnerar endast rätt resultat för kolumner av DATE typen. För TIMESTAMP kolumner använder du något av alternativen Datum och Tidsintervall.
Värden för dynamiskt datum och datumintervall
När du lägger till en datum- eller datumintervallparameter i din fråga visar markeringswidgeten en blå blixtikon. Klicka på den för att visa dynamiska värden som today, yesterday, this week, last week, last montheller last year. Dessa värden uppdateras dynamiskt.
Viktigt!
Dynamiska datum och datumintervall är inte kompatibla med schemalagda frågor.
Använda frågeparametrar i instrumentpaneler
Du kan också använda parametrar eller statiska värden för frågor. 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
Widgetparametrar är specifika för en enda visualisering på en instrumentpanel, visas i visualiseringspanelen och de angivna parametervärdena gäller endast för frågan som ligger till grund för visualiseringen.
Instrumentpanelsparameter
Instrumentpanelsparametrar 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. Instrumentpanelsparametrar konfigureras för en eller flera visualiseringar på en instrumentpanel och visas överst på instrumentpanelen. Parametervärdena som anges för en instrumentpanelsparameter gäller för visualiseringar som återanvänder den specifika instrumentpanelsparametern. En instrumentpanel kan ha flera parametrar som var och en kan gälla för vissa visualiseringar och inte andra.
Statiskt värde
Statiska värden används i stället för en parameter som svarar på ändringar. Med statiska värden 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 där den visades tidigare.
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å välja 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 instrumentpanelsparametrar eftersom värdeväljaren är dold. Om du väljer Statiskt värde som värdekälla ä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 ange 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 engångsparametrar som inte delas mellan widgetar.
- Statiskt värde: Välj ett statiskt värde för widgeten, oavsett vilka värden som används på andra widgetar. Statiskt mappade parametervärden visar inte någon värdeväljare någonstans på instrumentpanelen, vilket är mer kompakt. På så sätt kan du dra nytta av flexibiliteten hos frågeparametrar utan att röra användargränssnittet på en instrumentpanel när vissa parametrar 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 parametrar i en enda fråga?
Kan jag återanvända samma parameter flera gånger i en enda fråga?
Ja. Använd samma identifierare 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 parametrar i en enda fråga?
Ja. Använd ett unikt namn för varje parameter. I det här exemplet används två parametrar: {{org_id}} och {{start_date}}.
SELECT count(0)
FROM queries
WHERE org_id = {{org_id}} AND created_at > '{{start_date}}'