Práce s parametry dotazu

Tento článek vysvětluje, jak pracovat s parametry dotazu v editoru SQL Azure Databricks.

Parametry dotazu umožňují, aby byly dotazy dynamičtější a flexibilnější vložením hodnot proměnných za běhu. Místo pevně zakódovaných hodnot do dotazů můžete definovat parametry pro filtrování dat nebo úpravu výstupu na základě uživatelského vstupu. Tento přístup zlepšuje opakované použití dotazů, vylepšuje zabezpečení tím, že brání injektáži SQL a umožňuje efektivnější zpracování různorodých datových scénářů.

Syntaxe pojmenovaných značek parametrů

Pojmenované značky parametrů jsou zástupné proměnné typu. Pomocí této syntaxe můžete psát dotazy v následujících částech uživatelského rozhraní Azure Databricks:

• Editor SQL
• Poznámkové bloky
• Editor datových sad řídicích panelů AI/BI
• Prostory AI/BI Genie (Public Preview)

Do dotazů SQL vložte parametry zadáním dvojtečky následované názvem parametru, například :parameter_name. Když do dotazu zahrnete pojmenovanou značku parametru, zobrazí se v uživatelském rozhraní widget. Widget můžete použít k úpravě typu a názvu parametru.

Přidání pojmenované značky parametru do dotazu

Tento příklad přidá značku parametru do následujícího dotazu:

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

Tento dotaz vrátí datovou sadu, která obsahuje pouze částky jízdného, které jsou pod pěti dolary. Pomocí následujících kroků upravte dotaz tak, aby používal parametr místo pevně zakódované hodnoty (5).

1. Odstraňte číslo 5 z dotazu.
2. Zadejte dvojtečku (:) následovanou řetězcem fare_parameter. Poslední řádek aktualizovaného dotazu by měl být .fare_amount < :fare_parameter
3. Klikněte na ikonu ozubeného kola u widgetu parametrů. V dialogovém okně jsou zobrazena následující pole:
   • Klíčové slovo: Klíčové slovo, které představuje parametr v dotazu. Toto pole nelze upravit. Pokud chcete změnit klíčové slovo, upravte značku v dotazu SQL.
   • Název: Název, který se zobrazí nad widgetem. Ve výchozím nastavení je název stejný jako klíčové slovo.
   • Typ: Podporované typy jsou text, číslo, rozevírací seznam, datum, datum a čas a datum a čas (s sekundami). Výchozí hodnota je Text.
4. V dialogovém okně změňte typ na číslo.
5. Zadejte číslo do widgetu parametrů a klikněte na Použít změny.
6. Kliknutím na Uložit dotaz uložte.

Příklady syntaxe pojmenovaných parametrů

Následující příklady ukazují některé běžné případy použití parametrů.

Vložení data

Následující příklad obsahuje parametr Date , který omezuje výsledky dotazu na záznamy po určitém datu.

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

Vložení čísla

Následující příklad obsahuje parametr Číslo , který omezuje výsledky na záznamy, kde o_total_price je pole větší než zadaná hodnota parametru.

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

Vložení názvu pole

V následujícím příkladu field_param IDENTIFIER se funkce používá k poskytnutí prahové hodnoty dotazu za běhu. Hodnota parametru by měla být název sloupce z tabulky použité v dotazu.

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

Vložení databázových objektů

Následující příklad vytvoří tři parametry: catalog, schemaa table.

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

Viz klauzule IDENTIFIER.

Zřetězení více parametrů

Parametry můžete zahrnout do jiných funkcí SQL. Tento příklad umožňuje prohlížeči vybrat jméno zaměstnance a ID čísla. Dotaz použije format_string funkci ke zřetězení dvou řetězců a filtrování podle odpovídajících řádků. Viz format_string funkce.

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

Práce s řetězci JSON

Pomocí parametrů můžete extrahovat atribut z řetězce JSON. Následující příklad používá funkci k převodu from_json řetězce JSON na hodnotu struktury. Nahrazení řetězce a jako hodnoty parametru (param) vrátí atribut 1.

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

Vytvoření intervalu

Tento INTERVAL typ představuje časové období a umožňuje provádět aritmetické aritmetické operace založené na čase. Následující příklad obsahuje parametr uvnitř format_string funkce, která pak získá přetypování jako typ intervalu. Výslednou INTERVAL hodnotu lze použít pro výpočty založené na čase nebo filtrování v dotazu.

Podrobnosti a syntaxe najdete v části Typ INTERVALu.

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

Přidání rozsahu dat

Následující příklad ukazuje, jak přidat parametrizovaný rozsah kalendářních dat pro výběr záznamů v určitém časovém rámci.

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

Parametrizace souhrnů podle dne, měsíce nebo roku

Následující příklad agreguje data o jízdě taxíkem na parametrizované úrovni členitosti. Funkce DATE_TRUNC zkrátí tpep_pickup_datetime hodnotu na základě hodnoty parametru :date_granularity , například DAY, MONTHnebo YEAR. Zkrácené datum se aliasuje jako date_rollup a používá se v klauzuli 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

Použití více hodnot v jednom dotazu

Následující příklad používá ARRAY_CONTAINS funkci k filtrování seznamu hodnot. Funkce TRANSFORMa SPLIT funkce umožňují předání více hodnot oddělených čárkami jako řetězcový parametr.

Tato :list_parameter hodnota přebírá seznam hodnot oddělených čárkami. Funkce SPLIT analyzuje tento seznam a rozdělí hodnoty oddělené čárkami do pole. Funkce TRANSFORM transformuje každý prvek v poli odebráním všech prázdných znaků. Funkce ARRAY_CONTAINS zkontroluje, zda dropoff_zip je hodnota z trips tabulky obsažena v matici hodnot předaných jako list_parameter.

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

Poznámka:

Tento příklad funguje pro řetězcové hodnoty. Chcete-li upravit dotaz pro jiné datové typy, například seznam celých čísel, zabalte TRANSFORM operaci operací CAST , která převede řetězcové hodnoty na požadovaný datový typ.

Změny syntaxe

V následující tabulce jsou uvedeny běžné případy použití parametrů, původní syntaxe knírek SQL Databricks a ekvivalentní syntaxe používající syntaxi značek pojmenovaných parametrů.

Případ použití parametru	Syntaxe parametru Mustache	Syntaxe pojmenovaných značek parametrů
Načtení pouze dat před zadaným datem	WHERE date_field < '{{date_param}}' Je nutné zahrnout uvozovky kolem parametru data a složených závorek.	WHERE date_field < :date_param
Načtení pouze dat menší než zadaná číselná hodnota	WHERE price < {{max_price}}	WHERE price < :max_price
Porovnání dvou řetězců	WHERE region = {{region_param}}	WHERE region = :region_param
Zadání tabulky použité v dotazu	SELECT * FROM {{table_name}}	SELECT * FROM IDENTIFIER(:table) Když uživatel zadá tento parametr, měl by k identifikaci tabulky použít úplný tříúrovňový obor názvů.
Nezávisle zadejte katalog, schéma a tabulku použitou v dotazu.	SELECT * FROM {{catalog}}.{{schema}}.{{table}}	SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table)
Použití parametrů jako šablony v delším formátovaném řetězci	"({{area_code}}) {{phone_number}}" Hodnoty parametrů se automaticky zřetězeny jako řetězec.	format_string("(%d)%d; :area_code; :p hone_number) Úplný příklad najdete v tématu Concatenate více parametrů .
Vytvoření intervalu	SELECT INTERVAL {{p}} MINUTE	SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Syntaxe parametru Mustache

Důležité

Následující části platí pro syntaxi dotazů, kterou můžete použít pouze v editoru SQL. To znamená, že pokud zkopírujete a vložíte dotaz pomocí této syntaxe do jakéhokoli jiného rozhraní Azure Databricks, jako je poznámkový blok nebo editor datových sad řídicích panelů AI/BI, je nutné dotaz ručně upravit tak, aby používal značky pojmenovaných parametrů, než se spustí bez chyby.

V editoru SQL se libovolný řetězec mezi dvojitými složenými závorkami {{ }} považuje za parametr dotazu. Widget se zobrazí nad podoknem výsledků, kde nastavíte hodnotu parametru. Přestože Azure Databricks obecně doporučuje používat pojmenované značky parametrů, některé funkce se podporují jenom pomocí syntaxe parametrů mustache.

Pro následující funkce použijte syntaxi parametru mustache:

• Starší filtry řídicího panelu
• Parametry rozevíracího seznamu s více hodnotami
• Parametry rozevíracího seznamu založené na dotazech

Přidání parametru mustache

1. Zadejte Cmd + I. Parametr se vloží do textového stříšku a zobrazí se dialogové okno Přidat parametr .
   • Klíčové slovo: Klíčové slovo, které představuje parametr v dotazu.
   • Název: Název, který se zobrazí nad widgetem. Ve výchozím nastavení je název stejný jako klíčové slovo.
   • Typ: Podporované typy jsou text, číslo, datum, datum a čas, datum a čas (s sekundami), rozevírací seznam a rozevírací seznam založený na dotazech. Výchozí hodnota je Text.
2. Zadejte klíčové slovo, volitelně přepište název a vyberte typ parametru.
3. Klikněte na Přidat parametr.
4. Ve widgetu parametrů nastavte hodnotu parametru.
5. Klikněte na Použít změny.
6. Klikněte na Uložit.

Případně zadejte dvojité složené závorky {{ }} a kliknutím na ikonu ozubeného kola u widgetu parametrů upravte nastavení.

Pokud chcete dotaz znovu spustit s jinou hodnotou parametru, zadejte hodnotu ve widgetu a klikněte na Použít změny.

Úprava parametru dotazu

Pokud chcete upravit parametr, klikněte na ikonu ozubeného kola vedle widgetu parametrů. Pokud chcete uživatelům, kteří dotaz nevlastní, zabránit změně parametru, klikněte na Zobrazit pouze výsledky. Zobrazí se dialogové okno parametru <Keyword> .

Odebrání parametru dotazu

Pokud chcete parametr dotazu odebrat, odstraňte ho z dotazu. Widget parametrů zmizí a dotaz můžete přepsat pomocí statických hodnot.

Změna pořadí parametrů

Pokud chcete změnit pořadí, ve kterém se zobrazují parametry, můžete kliknout a přetáhnout jednotlivé parametry na požadované místo.

Typy parametrů dotazu

• Text
• Počet
• Rozevírací seznam
• Rozevírací seznam založený na dotazech
• Datum a čas

Text

Přebírá řetězec jako vstup. Zpětné lomítko, jednoduché a dvojité uvozovky jsou uvozovky uvozovky a Azure Databricks přidá do tohoto parametru uvozovky. Například řetězec, jako mr's Li"s je transformován na 'mr\'s Li\"s' příklad použití, může být

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

Počet

Přebírá jako vstup číslo. Příkladem použití může být

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

Rozevírací seznam

Pokud chcete omezit rozsah možných hodnot parametrů při spuštění dotazu, použijte typ parametru Rozevírací seznam . Příkladem by bylo SELECT * FROM users WHERE name='{{ dropdown_param }}'. Když vyberete z panelu nastavení parametrů, zobrazí se textové pole, do kterého zadáte povolené hodnoty, přičemž každá hodnota je oddělená novým řádkem. Rozevírací seznamy jsou textové parametry. Pokud chcete v rozevíracím seznamu použít kalendářní data nebo data a časy, zadejte je ve formátu, který zdroj dat vyžaduje. Řetězce nejsou uchvácené. Můžete si vybrat mezi rozevíracím seznamem s jednou nebo více hodnotami.

• Jedna hodnota: Jsou vyžadovány jednoduché uvozovky kolem parametru.
• Více hodnot: Přepněte možnost Povolit více hodnot . V rozevíracím seznamu uvozovek zvolte, jestli chcete parametry ponechat zadané (bez uvozovek) nebo zabalit parametry jednoduchými nebo dvojitými uvozovkami. Pokud zvolíte uvozovky, nemusíte kolem parametru přidávat uvozovky.

Změňte klauzuli WHERE tak, aby používala IN klíčové slovo v dotazu.

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

Widget s vícenásobným výběrem parametru umožňuje předat do databáze více hodnot. Pokud u parametru uvozovek vyberete možnost Dvojité uvozovky, bude váš dotaz odpovídat následujícímu formátu:WHERE IN ("value1", "value2", "value3")

Rozevírací seznam založený na dotazech

Vezme výsledek dotazu jako vstup. Má stejné chování jako parametr Rozevírací seznam . Pokud ho chcete použít jako vstup v jiném dotazu, musíte uložit dotaz rozevíracího seznamu SQL Databricks.

1. V části Typ na panelu nastavení klikněte na rozevírací seznam Založený na dotazu.
2. Klikněte na pole Dotaz a vyberte dotaz. Pokud cílový dotaz vrátí velký počet záznamů, výkon se sníží.

Pokud váš cílový dotaz vrátí více než jeden sloupec, databricks SQL použije první sloupec. Pokud cílový dotaz vrátí name sloupce a value sloupce, sql Databricks naplní widget pro výběr parametrů sloupcem name , ale spustí dotaz přidruženým value.

Předpokládejme například, že následující dotaz vrátí data v tabulce.

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

hodnota	name
1001	John Smith
1 002	Neznámá žena
1003	Bobby Tables

Když Azure Databricks spustí dotaz, hodnota předaná do databáze by byla 1001, 1002 nebo 1003.

Datum a čas

Azure Databricks nabízí několik možností parametrizace hodnot data a časového razítka, včetně možností pro zjednodušení parametrizace časových rozsahů. Vyberte ze tří možností různé přesnosti:

Možnost	Počet deset. míst	Typ
Date	den	DATE
Datum a čas	minuta	TIMESTAMP
Datum a čas (s sekundami)	sekunda	TIMESTAMP

Při výběru možnosti parametru Rozsah vytvoříte dva parametry určené příponou .start a .end příponami. Všechny možnosti předávají vašemu dotazu jako řetězcové literály; Azure Databricks vyžaduje, abyste hodnoty data a času zabalily do jednoduchých uvozovek ('). Příklad:

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

Parametry data používají rozhraní pro výběr kalendáře a výchozí nastavení aktuálního data a času.

Poznámka:

Parametr Rozsah dat vrátí pouze správné výsledky pro sloupce typu DATE . U TIMESTAMP sloupců použijte jednu z možností data a časového rozsahu.

Dynamické hodnoty data a rozsahu kalendářních dat

Když do dotazu přidáte parametr data nebo rozsahu kalendářních dat, widget výběru zobrazí modrou ikonu blesku. Kliknutím na něj zobrazíte dynamické hodnoty, jako je today, yesterday, last weekthis week, , last monthnebo last year. Tyto hodnoty se dynamicky aktualizují.

Důležité

Dynamická data a rozsahy kalendářních dat nejsou kompatibilní s naplánovanými dotazy.

Použití parametrů dotazu na řídicích panelech

Volitelně můžou dotazy používat parametry nebo statické hodnoty. Když se vizualizace založená na parametrizovaném dotazu přidá na řídicí panel, můžete vizualizaci nakonfigurovat tak, aby používala jednu z těchto hodnot:

• Parametr widgetu

  Parametry widgetu jsou specifické pro jednu vizualizaci na řídicím panelu, zobrazí se na panelu vizualizace a zadané hodnoty parametrů se vztahují pouze na dotaz podkladové vizualizace.

• Parametr řídicího panelu

  Parametry řídicího panelu se můžou vztahovat na více vizualizací. Když na řídicí panel přidáte vizualizaci založenou na parametrizovaném dotazu, parametr se ve výchozím nastavení přidá jako parametr řídicího panelu. Parametry řídicího panelu se konfigurují pro jednu nebo více vizualizací na řídicím panelu a zobrazí se v horní části řídicího panelu. Hodnoty parametrů zadané pro parametr řídicího panelu se vztahují na vizualizace, které znovu používají tento konkrétní parametr řídicího panelu. Řídicí panel může mít více parametrů, z nichž každý se může vztahovat na některé vizualizace, a ne na jiné.

• Statická hodnota

  Statické hodnoty se používají místo parametru, který reaguje na změny. Statické hodnoty umožňují pevně zakódovat hodnotu místo parametru. Parametr "zmizí" z řídicího panelu nebo widgetu, kde se dříve objevil.

Když přidáte vizualizaci obsahující parametrizovaný dotaz, můžete zvolit název a zdroj parametru v dotazu vizualizace kliknutím na příslušnou ikonu tužky. Můžete také vybrat klíčové slovo a výchozí hodnotu. Viz vlastnosti parametru.

Po přidání vizualizace na řídicí panel přejděte do rozhraní mapování parametrů kliknutím na nabídku kebab v pravém horním rohu widgetu řídicího panelu a následným kliknutím na změnit nastavení widgetu.

Vlastnosti parametru

• Název: Zobrazovaný název, který se zobrazí vedle selektoru hodnot na řídicím panelu. Výchozí hodnota je klíčové slovo parametru. Pokud ho chcete upravit, klikněte na ikonu tužky . Názvy se nezobrazují pro parametry statického řídicího panelu, protože selektor hodnot je skrytý. Pokud jako zdroj hodnot vyberete statickou hodnotu, pole Název se zobrazí šedě.

• Klíčové slovo: Řetězcový literál pro tento parametr v podkladovém dotazu. To je užitečné pro ladění, pokud řídicí panel nevrátí očekávané výsledky.

• Výchozí hodnota: Hodnota použitá, pokud není zadána žádná jiná hodnota. Pokud to chcete změnit na obrazovce dotazu, spusťte dotaz s požadovanou hodnotou parametru a klikněte na tlačítko Uložit .

• Zdroj hodnoty: Zdroj hodnoty parametru. Kliknutím na ikonu tužky vyberte zdroj.

  • Nový parametr řídicího panelu: Vytvořte nový parametr na úrovni řídicího panelu. To vám umožní nastavit hodnotu parametru na jednom místě na řídicím panelu a namapovat ji na více vizualizací.
  • Existující parametr řídicího panelu: Namapovat parametr na existující parametr řídicího panelu Musíte zadat, který parametr řídicího panelu už existuje.
  • Parametr widgetu: Zobrazí selektor hodnot uvnitř widgetu řídicího panelu. To je užitečné pro jednorázové parametry, které nejsou sdíleny mezi widgety.
  • Statická hodnota: Zvolte pro widget statickou hodnotu bez ohledu na hodnoty použité v jiných widgetech. Staticky mapované hodnoty parametrů nezobrazují selektor hodnot kdekoli na řídicím panelu, což je kompaktnější. To vám umožní využít flexibilitu parametrů dotazu bez nepotřebného uživatelského rozhraní na řídicím panelu, když se určité parametry nebudou často měnit.

  

Nejčastější dotazy

• Můžu stejný parametr opakovaně použít v jednom dotazu?
• Můžu v jednom dotazu použít více parametrů?

Můžu stejný parametr opakovaně použít v jednom dotazu?

Ano. Použijte stejný identifikátor ve složených závorkách. Tento příklad používá {{org_id}} parametr dvakrát.

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

Můžu v jednom dotazu použít více parametrů?

Ano. Pro každý parametr použijte jedinečný název. Tento příklad používá dva parametry: {{org_id}} a {{start_date}}.

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