Lekérdezési paraméterek használata

Ez a cikk bemutatja, hogyan dolgozhat lekérdezési paraméterekkel az Azure Databricks SQL-szerkesztőben.

A lekérdezési paraméterek lehetővé teszik a lekérdezések dinamikusabbá és rugalmasabbá tétele változó értékek futásidejű beszúrásával. Ahelyett, hogy konkrét értékeket kódolt a lekérdezésekbe, paramétereket határozhat meg az adatok szűréséhez vagy a kimenet felhasználói bemeneten alapuló módosításához. Ez a megközelítés javítja a lekérdezések újrafelhasználását, javítja a biztonságot az SQL-injektálás megelőzésével, és lehetővé teszi a különböző adatforgatókönyvek hatékonyabb kezelését.

Elnevezett paraméterjelölő szintaxisa

Az elnevezett paraméterjelölők beírt helyőrző változók. A szintaxis használatával lekérdezéseket írhat az Azure Databricks felhasználói felületének következő részeibe:

• SQL-szerkesztő
• Jegyzetfüzetek
• AI/BI-irányítópult adatkészlet-szerkesztője
• AI/BI Genie spaces (nyilvános előzetes verzió)

Paraméterek beszúrása az SQL-lekérdezésekbe kettőspont beírásával, majd egy paraméternév, például :parameter_name. Ha elnevezett paraméterjelölőt ad meg egy lekérdezésben, megjelenik egy widget a felhasználói felületen. A vezérlővel szerkesztheti a paraméter típusát és nevét.

Elnevezett paraméterjelölő hozzáadása egy lekérdezéshez

Ez a példa egy paraméterjelölőt ad hozzá a következő lekérdezéshez:

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

Ez a lekérdezés egy olyan adathalmazt ad vissza, amely csak öt dollár alatti viteldíjösszegeket tartalmaz. A következő lépésekkel szerkessze a lekérdezést úgy, hogy a rögzített érték (5) helyett egy paramétert használjon.

1. Törölje az 5-ös számot a lekérdezésből.
2. Írjon be egy kettőspontot (:) majd a sztringet fare_parameter. A frissített lekérdezés utolsó sorának a következőt kell mondania fare_amount < :fare_parameter: .
3. Kattintson a fogaskerék ikonra a paraméter widget közelében. A párbeszédpanel a következő mezőket jeleníti meg:
   • Kulcsszó: A lekérdezés paraméterét képviselő kulcsszó. Ez a mező nem szerkeszthető. A kulcsszó módosításához szerkessze a jelölőt az SQL-lekérdezésben.
   • Cím: A widget fölött megjelenő cím. Alapértelmezés szerint a cím megegyezik a kulcsszóval.
   • Típus: Támogatott típusok: Szöveg, Szám, Legördülő lista, Dátum, Dátum és idő, Dátum és idő, Valamint Dátum és idő (másodpercekkel). Az alapértelmezett szöveg.
4. A párbeszédpanelen módosítsa a típust számra.
5. Adjon meg egy számot a paraméter widgetbe, és kattintson a Módosítások alkalmazása gombra.
6. Kattintson a Mentés gombra a lekérdezés mentéséhez.

Elnevezett paraméterszintaxis-példák

Az alábbi példák a paraméterek gyakori használati eseteit mutatják be.

Dátum beszúrása

Az alábbi példa egy Date paramétert tartalmaz, amely egy adott dátum utáni rekordokra korlátozza a lekérdezési eredményeket.

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

Szám beszúrása

Az alábbi példa egy Szám paramétert tartalmaz, amely az eredményeket olyan rekordokra korlátozza, ahol a o_total_price mező nagyobb a megadott paraméterértéknél.

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

Mezőnév beszúrása

Az alábbi példában a field_param függvény a IDENTIFIER lekérdezés futásidejű küszöbértékének megadására szolgál. A paraméterértéknek a lekérdezésben használt táblából származó oszlopnévnek kell lennie.

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

Adatbázis-objektumok beszúrása

Az alábbi példa három paramétert hoz létre: catalog, schemaés table.

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

Lásd: IDENTIFIER záradék.

Több paraméter összefűzve

Paramétereket más SQL-függvényekbe is felvehet. Ez a példa lehetővé teszi, hogy a megtekintő kiválasztjon egy alkalmazotti címet és egy számazonosítót. A lekérdezés a format_string függvény használatával összefűzi a két sztringet, és szűri az egyező sorokat. Lásd format_string függvényt.

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

JSON-sztringek működése

Paraméterekkel kinyerhet egy attribútumot egy JSON-sztringből. Az alábbi példa a függvényt használja a from_json JSON-sztring strukturált értékké alakításához. Ha a sztringet a a paraméter (param) értékeként helyettesíti, az 1 attribútumot adja vissza.

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

Intervallum létrehozása

A INTERVAL típus egy időtartományt jelöl, és lehetővé teszi az időalapú aritmetika és műveletek végrehajtását. Az alábbi példa egy függvényen belüli paramétert format_string tartalmaz, amely ezután intervallumtípusként lesz leadva. Az eredményként kapott INTERVAL érték felhasználható időalapú számításokhoz vagy szűréshez a lekérdezésben.

A részletekért és a szintaxisért tekintse meg az INTERVAL típust .

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

Dátumtartomány hozzáadása

Az alábbi példa bemutatja, hogyan adhat hozzá paraméteres dátumtartományt egy adott időkeret rekordjainak kiválasztásához.

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

Összesítések paraméterezése nap, hónap vagy év szerint

Az alábbi példa a taxiút adatait egy paraméteres részletességi szinten összesíti. A DATE_TRUNC függvény csonkolja az értéket a tpep_pickup_datetime :date_granularity paraméter értéke alapján, például DAY: , MONTHvagy YEAR. A csonkolt dátumot a záradék aliasolja date_rollup és használja 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

Több érték használata egyetlen lekérdezésben

Az alábbi példa a függvény használatával ARRAY_CONTAINS szűri az értékek listáját. A TRANSFORM, és SPLIT függvények lehetővé teszik, hogy több, vesszővel elválasztott értéket adjon át sztringparaméterként.

Az :list_parameter érték a vesszővel elválasztott értékek listáját veszi fel. A SPLIT függvény elemzi a listát, és felosztja a vesszővel tagolt értékeket egy tömbre. A TRANSFORM függvény a szóközök eltávolításával átalakítja a tömb minden elemét. A ARRAY_CONTAINS függvény ellenőrzi, hogy a dropoff_zip trips táblából származó érték szerepel-e a megadott értékek tömbjében.list_parameter

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

Feljegyzés

Ez a példa sztringértékekhez használható. Ha módosítani szeretné a lekérdezést más adattípusokhoz, például az egész számok listájához, a műveletet egy művelettel tördelve TRANSFORM konvertálja a sztringértékeket CAST a kívánt adattípusba.

Szintaxisváltozások

Az alábbi táblázat a paraméterek gyakori használati eseteit, az eredeti Databricks SQL bajuszszintaxisát és az azzal egyenértékű, elnevezett paraméterjelölő szintaxist használó szintaxist mutatja be.

Paraméterhasználati eset	Bajuszparaméter szintaxisa	Elnevezett paraméterjelölő szintaxisa
Csak adatok betöltése megadott dátum előtt	WHERE date_field < '{{date_param}}' Idézőjeleket kell tartalmaznia a dátumparaméter és a kapcsos zárójelek köré.	WHERE date_field < :date_param
Csak a megadott numerikus értéknél kisebb adatok betöltése	WHERE price < {{max_price}}	WHERE price < :max_price
Két sztring összehasonlítása	WHERE region = {{region_param}}	WHERE region = :region_param
A lekérdezésben használt tábla megadása	SELECT * FROM {{table_name}}	SELECT * FROM IDENTIFIER(:table) Amikor egy felhasználó megadja ezt a paramétert, a teljes háromszintű névteret kell használnia a tábla azonosításához.
Függetlenül adja meg a lekérdezésben használt katalógust, sémát és táblát	SELECT * FROM {{catalog}}.{{schema}}.{{table}}	SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table)
Paraméterek használata sablonként egy hosszabb, formázott sztringben	"({{area_code}}) {{phone_number}}" A paraméterértékek automatikusan sztringként vannak összefűzve.	format_string("(%d)%d, :area_code, :p hone_number) A teljes példához lásd : Több paraméter összefűzve.
Intervallum létrehozása	SELECT INTERVAL {{p}} MINUTE	SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Bajuszparaméter szintaxisa

Fontos

A következő szakaszok csak az SQL-szerkesztőben használható lekérdezési szintaxisra vonatkoznak. Ez azt jelenti, hogy ha ezzel a szintaxissal másol és illeszt be egy lekérdezést bármely más Azure Databricks-felületbe, például egy jegyzetfüzetbe vagy egy AI/BI-irányítópult adatkészlet-szerkesztőjébe, a lekérdezést manuálisan kell módosítani a névvel ellátott paraméterjelölők használatához, mielőtt hiba nélkül fut.

Az SQL-szerkesztőben a kettős kapcsos zárójelek {{ }} közötti karakterláncok lekérdezési paraméterként lesznek kezelve. Megjelenik egy widget az eredménypanel felett, ahol beállítja a paraméter értékét. Bár az Azure Databricks általában névvel ellátott paraméterjelölők használatát javasolja, egyes funkciók csak bajuszparaméter-szintaxis használatával támogatottak.

Használja a bajuszparaméter szintaxisát a következő funkciókhoz:

• Örökölt irányítópult-szűrők
• Több érték legördülő paramétere
• Lekérdezésalapú legördülő paraméterek

Bajuszparaméter hozzáadása

1. Gépelje be: Cmd + I. A paraméter be lesz szúrva a szövegrészletbe, és megjelenik a Paraméter hozzáadása párbeszédpanel.
   • Kulcsszó: A lekérdezés paraméterét képviselő kulcsszó.
   • Cím: A widget fölött megjelenő cím. Alapértelmezés szerint a cím megegyezik a kulcsszóval.
   • Típus: Támogatott típusok: Szöveg, Szám, Dátum, Dátum és idő, Dátum és idő (másodpercekkel), Legördülő lista és Lekérdezésalapú legördülő lista. Az alapértelmezett szöveg.
2. Adja meg a kulcsszót, felülbírálhatja a címet, és kiválaszthatja a paraméter típusát.
3. Kattintson a Paraméter hozzáadása elemre.
4. A paraméter widgetben állítsa be a paraméter értékét.
5. Kattintson a Módosítások alkalmazása gombra.
6. Kattintson a Mentés gombra.

Másik lehetőségként írja be a dupla kapcsos zárójeleket {{ }} , és kattintson a paraméter widget melletti fogaskerék ikonra a beállítások szerkesztéséhez.

Ha egy másik paraméterértékkel szeretné újra futtatni a lekérdezést, írja be az értéket a widgetbe, és kattintson a Módosítások alkalmazása gombra.

Lekérdezési paraméter szerkesztése

Paraméter szerkesztéséhez kattintson a paraméter widget melletti fogaskerék ikonra. Ha meg szeretné akadályozni, hogy a lekérdezést nem birtokba vevő felhasználók módosítják a paramétert, kattintson a Csak eredmények megjelenítése gombra. Megjelenik a <Keyword> paraméter párbeszédpanel.

Lekérdezési paraméter eltávolítása

A lekérdezési paraméter eltávolításához törölje a paramétert a lekérdezésből. A paraméter widget eltűnik, és statikus értékekkel újraírhatja a lekérdezést.

Paraméterek sorrendjének módosítása

A paraméterek megjelenési sorrendjének módosításához kattintson és húzza az egyes paramétereket a kívánt helyre.

Lekérdezési paramétertípusok

• Szöveg
• Szám
• Legördülő lista
• Lekérdezésalapú legördülő lista
• Dátum és idő

Szöveg

Bemenetként egy sztringet vesz fel. A fordított perjelek, az egy- és a dupla idézőjelek feloldódnak, és az Azure Databricks idézőjeleket ad hozzá ehhez a paraméterhez. Ilyen sztring mr's Li"s 'mr\'s Li\"s' például egy példa erre

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

Szám

Bemenetként egy számot vesz fel. Erre példa lehet a

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

Legördülő lista

A lekérdezés futtatásakor a lehetséges paraméterértékek hatókörének korlátozásához használja a legördülő lista paramétertípusát. Ilyen például a .SELECT * FROM users WHERE name='{{ dropdown_param }}' Ha a paraméterbeállítások panelen van kiválasztva, megjelenik egy szövegmező, ahol megadhatja az engedélyezett értékeket, mindegyik értéket egy új sor választja el egymástól. A legördülő listák szöveges paraméterek. Ha dátumokat vagy dátumokat és időpontokat szeretne használni a legördülő listában, adja meg őket az adatforrás által igényelt formátumban. A sztringek nem szöktek meg. Választhat egy vagy többértékű legördülő listából.

• Egyetlen érték: A paraméter körül egyetlen idézőjelre van szükség.
• Többértékű: A Több érték engedélyezése beállítás váltása. Az Árajánlat legördülő listában válassza ki, hogy a paramétereket a megadott módon kívánja-e hagyni (idézőjelek nélkül), vagy a paramétereket szimpla vagy dupla idézőjelekkel burkolja. Ha idézőjeleket választ, nem kell idézőjeleket hozzáadnia a paraméterhez.

Módosítsa a záradékot WHERE úgy, hogy a kulcsszót IN használja a lekérdezésben.

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

A többkijelöléses paraméter widgettel több értéket is átadhat az adatbázisnak. Ha a Kettős idézőjel lehetőséget választja az Idézőjel paraméterhez, a lekérdezés a következő formátumot tükrözi: WHERE IN ("value1", "value2", "value3")

Lekérdezésalapú legördülő lista

A lekérdezés eredményét adja meg bemenetként. Ugyanaz a viselkedése, mint a Legördülő lista paraméternek. A Databricks SQL legördülő lista lekérdezését mentenie kell ahhoz, hogy egy másik lekérdezés bemeneteként használhassa.

1. Kattintson a Lekérdezésalapú legördülő listára a Beállítások panel Típus területén.
2. Kattintson a Lekérdezés mezőre, és válasszon ki egy lekérdezést. Ha a cél lekérdezés nagy számú rekordot ad vissza, a teljesítmény csökkenni fog.

Ha a cél lekérdezés egynél több oszlopot ad vissza, a Databricks SQL az elsőt használja. Ha a cél lekérdezés visszaadja és value oszlopokat name ad vissza, a Databricks SQL feltölti a paraméterválasztó widgetet az name oszlopmal, de végrehajtja a lekérdezést a társítottvalvalue.

Tegyük fel például, hogy a következő lekérdezés a táblában szereplő adatokat adja vissza.

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

Érték	név
1001	John Smith
1002	Jane Doe
1003	Bobby Tables

Amikor az Azure Databricks futtatja a lekérdezést, az adatbázisnak átadott érték 1001, 1002 vagy 1003 lesz.

Dátum és idő

Az Azure Databricks számos lehetőséget kínál a dátum- és időbélyeg-értékek paraméterezésére, beleértve az időtartományok paraméterezésének egyszerűsítésére vonatkozó lehetőségeket is. Három különböző pontosságú lehetőség közül választhat:

Lehetőség	Pontosság	Típus
Dátum	nap	DATE
Dátum és idő	perc	TIMESTAMP
Dátum és idő (másodpercekkel)	másodperc	TIMESTAMP

Tartományparaméter-beállítás kiválasztásakor két, utótagok által .start .end kijelölt paramétert kell létrehoznia. Minden beállítás sztringkonstansként adja át a paramétereket a lekérdezésnek; Az Azure Databricks megköveteli, hogy a dátum- és időértékeket egyetlen idézőjelbe (') csomagolja. Példa:

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

A dátumparaméterek naptárválasztási felületet használnak, és alapértelmezés szerint az aktuális dátumot és időpontot használják.

Feljegyzés

A Dátumtartomány paraméter csak a megfelelő eredményeket adja vissza a típusoszlopokhoz DATE . Oszlopok esetén TIMESTAMP használja a Dátum és az Időtartomány lehetőséget.

Dinamikus dátum- és dátumtartomány-értékek

Amikor dátum- vagy dátumtartomány-paramétert ad hozzá a lekérdezéshez, a kijelölési widget egy kék villámgyors ikont jelenít meg. Kattintson rá az olyan dinamikus értékek megjelenítéséhez, mint a today, yesterday, this week, last weeklast monthvagy last year. Ezek az értékek dinamikusan frissülnek.

Fontos

A dinamikus dátumok és dátumtartományok nem kompatibilisek az ütemezett lekérdezésekkel.

Lekérdezési paraméterek használata az irányítópultokon

A lekérdezések paramétereket vagy statikus értékeket is használhatnak. Ha egy paraméteres lekérdezésen alapuló vizualizációt ad hozzá egy irányítópulthoz, a vizualizáció konfigurálható úgy, hogy a következőt használja:

• Widget paraméter

  A widgetparaméterek egy irányítópult egyetlen vizualizációjára vonatkoznak, a vizualizáció panelen jelennek meg, és a megadott paraméterértékek csak a vizualizáció alapjául szolgáló lekérdezésre vonatkoznak.

• Irányítópult paramétere

  Az irányítópult paraméterei több vizualizációra is alkalmazhatók. Ha paraméteres lekérdezésen alapuló vizualizációt ad hozzá egy irányítópulthoz, a paraméter alapértelmezés szerint irányítópult-paraméterként lesz hozzáadva. Az irányítópult paraméterei egy vagy több vizualizációhoz vannak konfigurálva az irányítópulton, és az irányítópult tetején jelennek meg. Az irányítópult-paraméterhez megadott paraméterértékek az adott irányítópult-paramétert újrahasználó vizualizációkra vonatkoznak. Az irányítópultok több paraméterrel is rendelkezhetnek, amelyek mindegyike egyes vizualizációkra vonatkozhat, másokra nem.

• Statikus érték

  A statikus értékek olyan paraméter helyett használatosak, amely reagál a változásokra. A statikus értékek lehetővé teszik egy érték paraméter helyett történő kódolását. A paramétert "eltűntetik" az irányítópultról vagy widgetről, ahol korábban megjelent.

Ha paraméteres lekérdezést tartalmazó vizualizációt ad hozzá, a megfelelő ceruzaikonra kattintva kiválaszthatja a paraméter címét és forrását a vizualizációs lekérdezésben. Kiválaszthatja a kulcsszót és az alapértelmezett értéket is. Lásd: Paramétertulajdonságok.

Miután vizualizációt adott hozzá egy irányítópulthoz, az irányítópult jobb felső sarkában található kebab menüre kattintva, majd a Widget beállításainak módosítása parancsra kattintva érheti el a paraméterleképezési felületet.

Paramétertulajdonságok

• Cím: Az irányítópult értékválasztója mellett megjelenő megjelenítendő név. Alapértelmezés szerint a Kulcsszó paramétert használja. A szerkesztéshez kattintson a ceruza ikonra . A címek nem jelennek meg a statikus irányítópult paramétereinél, mert az értékválasztó rejtett. Ha a Statikus értéket választja értékforrásként, a Cím mező kiszürkítve jelenik meg.

• Kulcsszó: A paraméter sztringkonstansa az alapul szolgáló lekérdezésben. Ez akkor hasznos a hibakereséshez, ha az irányítópult nem a várt eredményeket adja vissza.

• Alapértelmezett érték: A használt érték, ha nincs megadva más érték. A lekérdezés képernyőjének módosításához futtassa a lekérdezést a kívánt paraméterértékkel, és kattintson a Mentés gombra.

• Értékforrás: A paraméter értékének forrása. Kattintson a ceruza ikonra a forrás kiválasztásához.

  • Új irányítópult-paraméter: Hozzon létre egy új irányítópultszintű paramétert. Ez lehetővé teszi, hogy egy paraméterértéket egy helyen állítson be az irányítópulton, és több vizualizációra képezhesse le.
  • Meglévő irányítópult-paraméter: Paraméter leképezése meglévő irányítópult-paraméterre. Meg kell adnia, hogy melyik már meglévő irányítópult-paraméter.
  • Widget paraméter: Megjelenít egy értékválasztót az irányítópult widgetén belül. Ez olyan egyszeri paraméterek esetén hasznos, amelyeket nem osztanak meg a widgetek között.
  • Statikus érték: Válasszon statikus értéket a widgethez, függetlenül a többi widgeten használt értékektől. A statikusan leképezett paraméterértékek az irányítópulton sehol nem jelennek meg értékválasztóként, ami kompaktabb. Ez lehetővé teszi, hogy kihasználja a lekérdezési paraméterek rugalmasságát anélkül, hogy az irányítópulton zsúfolttá teszi a felhasználói felületet, ha bizonyos paraméterek várhatóan nem változnak gyakran.

  

Gyakori kérdések (GYIK)

• Újra felhasználhatom ugyanazt a paramétert többször egyetlen lekérdezésben?
• Használhatok több paramétert egyetlen lekérdezésben?

Újra felhasználhatom ugyanazt a paramétert többször egyetlen lekérdezésben?

Igen. Használja ugyanazt az azonosítót a kapcsos zárójelekben. Ez a példa kétszer használja a paramétert {{org_id}} .

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

Használhatok több paramétert egyetlen lekérdezésben?

Igen. Minden paraméterhez használjon egyedi nevet. Ez a példa két paramétert használ: {{org_id}} és {{start_date}}.

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