Praca z parametrami zapytania

W tym artykule wyjaśniono, jak pracować z parametrami zapytania w edytorze SQL usługi Azure Databricks.

Parametry zapytania umożliwiają bardziej dynamiczne i elastyczne wykonywanie zapytań przez wstawianie wartości zmiennych w czasie wykonywania. Zamiast kodowania określonych wartości w zapytaniach można zdefiniować parametry do filtrowania danych lub modyfikowania danych wyjściowych na podstawie danych wejściowych użytkownika. Takie podejście poprawia ponowne użycie zapytań, zwiększa bezpieczeństwo, uniemożliwiając wstrzyknięcie kodu SQL i umożliwia wydajniejszą obsługę różnych scenariuszy danych.

Nazwana składnia znacznika parametrów

Nazwane znaczniki parametrów są typizowane zmienne zastępcze. Ta składnia służy do pisania zapytań w następujących częściach interfejsu użytkownika usługi Azure Databricks:

• Edytor SQL
• Notesy
• Edytor zestawu danych pulpitu nawigacyjnego usługi AI/BI
• Miejsca usługi AI/BI Genie (publiczna wersja zapoznawcza)

Wstaw parametry do zapytań SQL, wpisując dwukropek, po którym następuje nazwa parametru, na przykład :parameter_name. Po dołączeniu nazwanego znacznika parametru w zapytaniu w interfejsie użytkownika zostanie wyświetlony widżet. Możesz użyć widżetu, aby edytować typ parametru i nazwę.

Dodawanie nazwanego znacznika parametru do zapytania

W tym przykładzie dodano znacznik parametru do następującego zapytania:

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

To zapytanie zwraca zestaw danych, który zawiera tylko kwoty taryfy poniżej pięciu dolarów. Wykonaj poniższe kroki, aby edytować zapytanie, aby użyć parametru zamiast zakodowanej wartości (5).

1. Usuń numer 5 z zapytania.
2. Wpisz dwukropek (:), a następnie ciąg fare_parameter. Ostatni wiersz zaktualizowanego zapytania powinien mieć wartość fare_amount < :fare_parameter.
3. Kliknij ikonę koła zębatego w pobliżu widżetu parametru. W oknie dialogowym są wyświetlane następujące pola:
   • Słowo kluczowe: słowo kluczowe reprezentujące parametr w zapytaniu. Nie można edytować tego pola. Aby zmienić słowo kluczowe, edytuj znacznik w zapytaniu SQL.
   • Tytuł: tytuł wyświetlany nad widżetem. Domyślnie tytuł jest taki sam jak słowo kluczowe.
   • Typ: Obsługiwane typy to Tekst, Liczba, Lista rozwijana, Data, Data i Godzina oraz Data i godzina (z sekundami). Wartość domyślna to Text.
4. W oknie dialogowym zmień typ na Liczba.
5. Wprowadź liczbę w widżecie parametrów i kliknij przycisk Zastosuj zmiany.
6. Kliknij przycisk Zapisz, aby zapisać zapytanie.

Przykłady składni nazwanych parametrów

W poniższych przykładach przedstawiono niektóre typowe przypadki użycia parametrów.

Wstaw datę

Poniższy przykład zawiera parametr Date , który ogranicza wyniki zapytania do rekordów po określonej dacie.

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

Wstaw liczbę

Poniższy przykład zawiera parametr Liczba , który ogranicza wyniki do rekordów, w których o_total_price pole jest większe niż podana wartość parametru.

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

Wstaw nazwę pola

W poniższym przykładzie field_param parametr jest używany z funkcją IDENTIFIER w celu zapewnienia wartości progowej zapytania w czasie wykonywania. Wartość parametru powinna być nazwą kolumny z tabeli używanej w zapytaniu.

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

Wstawianie obiektów bazy danych

Poniższy przykład tworzy trzy parametry: catalog, schemai table.

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

Zobacz klauzulę IDENTIFIER.

Łączenie wielu parametrów

Parametry można uwzględnić w innych funkcjach SQL. W tym przykładzie można wybrać tytuł pracownika i identyfikator numeru. Zapytanie używa format_string funkcji do łączenia dwóch ciągów i filtrowania wierszy, które są zgodne. Zobacz format_string funkcja.

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

Praca z ciągami JSON

Możesz użyć parametrów, aby wyodrębnić atrybut z ciągu JSON. W poniższym przykładzie użyto from_json funkcji , aby przekonwertować ciąg JSON na wartość struktury. Podstawianie ciągu a jako wartości parametru (param) zwraca atrybut 1.

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

Tworzenie interwału

Typ INTERVAL reprezentuje przedział czasu i umożliwia wykonywanie arytmetycznych i operacji opartych na czasie. Poniższy przykład zawiera parametr wewnątrz format_string funkcji, który następnie jest rzutowy jako typ interwału. Wynikowa INTERVAL wartość może służyć do obliczeń opartych na czasie lub filtrowania w zapytaniu.

Zobacz TYP INTERWAŁu, aby uzyskać szczegółowe informacje i składnię.

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

Dodawanie zakresu dat

W poniższym przykładzie pokazano, jak dodać sparametryzowany zakres dat, aby wybrać rekordy w określonym przedziale czasu.

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

Parametryzowanie zestawień według dnia, miesiąca lub roku

W poniższym przykładzie zagregowane są dane przejazdów taksówek na poziomie sparametryzowanym stopnia szczegółowości. Funkcja DATE_TRUNC obcina tpep_pickup_datetime wartość na podstawie wartości parametru :date_granularity , takiej jak DAY, MONTHlub YEAR. Data obcięta jest aliasem jako date_rollup i używana w 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

Używanie wielu wartości w jednym zapytaniu

W poniższym przykładzie użyto ARRAY_CONTAINS funkcji do filtrowania listy wartości. Funkcje TRANSFORM, i SPLIT umożliwiają przekazywanie wielu rozdzielonych przecinkami wartości jako parametru ciągu.

Wartość :list_parameter przyjmuje listę wartości rozdzielonych przecinkami. Funkcja SPLIT analizuje tę listę, dzieląc wartości rozdzielane przecinkami na tablicę. Funkcja TRANSFORM przekształca każdy element w tablicy, usuwając wszelkie białe znaki. Funkcja ARRAY_CONTAINS sprawdza, czy dropoff_zip wartość z trips tabeli znajduje się w tablicy wartości przekazanych jako list_parameter.

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

Uwaga

Ten przykład działa w przypadku wartości ciągów. Aby zmodyfikować zapytanie dla innych typów danych, takich jak lista liczb całkowitych, opakuj TRANSFORM operację operacją CAST , aby przekonwertować wartości ciągu na żądany typ danych.

Zmiany składni

W poniższej tabeli przedstawiono typowe przypadki użycia parametrów, oryginalną składnię wąsów SQL usługi Databricks i równoważną składnię przy użyciu składni znacznika parametrów nazwanych.

Przypadek użycia parametru	Składnia parametrów wąsów	Nazwana składnia znacznika parametrów
Ładowanie tylko danych przed określoną datą	WHERE date_field < '{{date_param}}' Należy uwzględnić cudzysłowy dotyczące parametru daty i nawiasów klamrowych.	WHERE date_field < :date_param
Ładowanie tylko danych mniejszych niż określona wartość liczbowa	WHERE price < {{max_price}}	WHERE price < :max_price
Porównanie dwóch ciągów	WHERE region = {{region_param}}	WHERE region = :region_param
Określanie tabeli używanej w zapytaniu	SELECT * FROM {{table_name}}	SELECT * FROM IDENTIFIER(:table) Gdy użytkownik wprowadzi ten parametr, powinien użyć pełnej trójwymiarowej przestrzeni nazw, aby zidentyfikować tabelę.
Niezależnie określ wykaz, schemat i tabelę używaną w zapytaniu	SELECT * FROM {{catalog}}.{{schema}}.{{table}}	SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table)
Używanie parametrów jako szablonu w dłuższym, sformatowanym ciągu	"({{area_code}}) {{phone_number}}" Wartości parametrów są automatycznie łączone jako ciąg.	format_string("(%d)%d, :area_code, :p hone_number) Aby uzyskać pełny przykład, zobacz Łączenie wielu parametrów .
Tworzenie interwału	SELECT INTERVAL {{p}} MINUTE	SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Składnia parametrów wąsów

Ważne

Poniższe sekcje dotyczą składni zapytań, której można używać tylko w edytorze SQL. Oznacza to, że w przypadku kopiowania i wklejania zapytania przy użyciu tej składni do dowolnego innego interfejsu usługi Azure Databricks, takiego jak notes lub edytor zestawu danych pulpitu nawigacyjnego AI/BI, zapytanie musi zostać ręcznie dostosowane, aby używać nazwanych znaczników parametrów przed uruchomieniem bez błędu.

W edytorze SQL każdy ciąg między podwójnymi nawiasami klamrowymi {{ }} jest traktowany jako parametr zapytania. Widżet jest wyświetlany powyżej okienka wyników, w którym ustawiono wartość parametru. Chociaż usługa Azure Databricks zwykle zaleca używanie nazwanych znaczników parametrów, niektóre funkcje są obsługiwane tylko przy użyciu składni parametrów wąsowych.

Użyj składni parametrów wąsu dla następujących funkcji:

• Starsze filtry pulpitu nawigacyjnego
• Wiele parametrów listy rozwijanej wartości
• Parametry listy rozwijanej opartej na zapytaniach

Dodawanie parametru wąsu

1. Wpisz Cmd + I. Parametr zostanie wstawiony do daszka tekstowego, a zostanie wyświetlone okno dialogowe Dodawanie parametru.
   • Słowo kluczowe: słowo kluczowe reprezentujące parametr w zapytaniu.
   • Tytuł: tytuł wyświetlany nad widżetem. Domyślnie tytuł jest taki sam jak słowo kluczowe.
   • Typ: Obsługiwane typy to tekst, liczba, data, data i godzina, data i godzina (z sekundami), lista rozwijana i lista rozwijana oparta na zapytaniach. Wartość domyślna to Text.
2. Wprowadź słowo kluczowe, opcjonalnie przesłoń tytuł i wybierz typ parametru.
3. Kliknij pozycję Dodaj parametr.
4. W widżecie parametru ustaw wartość parametru.
5. Kliknij pozycję Zastosuj zmiany.
6. Kliknij przycisk Zapisz.

Alternatywnie wpisz podwójne nawiasy klamrowe {{ }} i kliknij ikonę koła zębatego w pobliżu widżetu parametru, aby edytować ustawienia.

Aby ponownie uruchomić zapytanie z inną wartością parametru, wprowadź wartość w widżecie i kliknij przycisk Zastosuj zmiany.

Edytowanie parametru zapytania

Aby edytować parametr, kliknij ikonę koła zębatego obok widżetu parametru. Aby uniemożliwić użytkownikom, którzy nie są właścicielami zapytania zmiany parametru, kliknij pozycję Pokaż tylko wyniki. Zostanie <Keyword> wyświetlone okno dialogowe parametru.

Usuwanie parametru zapytania

Aby usunąć parametr zapytania, usuń parametr z zapytania. Widżet parametru zniknie i możesz ponownie napisać zapytanie przy użyciu wartości statycznych.

Zmienianie kolejności parametrów

Aby zmienić kolejność wyświetlania parametrów, możesz kliknąć i przeciągnąć każdy parametr do żądanej pozycji.

Typy parametrów zapytania

• Tekst
• Liczba
• Lista rozwijana
• Lista rozwijana oparta na zapytaniach
• Data i godzina

Text

Przyjmuje ciąg jako dane wejściowe. Znaki ukośnika odwrotnego, pojedynczego i podwójnego cudzysłowu są ucieczki, a usługa Azure Databricks dodaje znaki cudzysłowu do tego parametru. Na przykład ciąg podobny mr's Li"s do ciągu jest przekształcany do 'mr\'s Li\"s' przykładu użycia tego elementu

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

Liczba

Przyjmuje liczbę jako dane wejściowe. Przykładem użycia tego elementu może być

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

Lista rozwijana

Aby ograniczyć zakres możliwych wartości parametrów podczas uruchamiania zapytania, użyj typu parametru Lista rozwijana. Przykładem może być SELECT * FROM users WHERE name='{{ dropdown_param }}'. Po wybraniu z panelu ustawień parametrów zostanie wyświetlone pole tekstowe, w którym wprowadzasz dozwolone wartości, każda wartość oddzielona nową linią. Listy rozwijane to parametry tekstowe. Aby użyć dat lub dat i godzin na liście rozwijanej, wprowadź je w formacie wymaganym przez źródło danych. Ciągi nie są usuwane. Możesz wybrać jedną wartość lub listę rozwijaną z wieloma wartościami.

• Pojedyncza wartość: wymagane są pojedyncze cudzysłowy wokół parametru.
• Wiele wartości: przełącz opcję Zezwalaj na wiele wartości . Na liście rozwijanej Cudzysłów wybierz, czy parametry mają pozostać wprowadzone (bez cudzysłowów) lub opakowować parametry za pomocą pojedynczych lub podwójnych cudzysłowów. Nie musisz dodawać cudzysłowów wokół parametru, jeśli wybierzesz znaki cudzysłowu.

WHERE Zmień klauzulę, aby użyć słowa kluczowego IN w zapytaniu.

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

Widżet wielokrotnego wyboru parametru umożliwia przekazanie wielu wartości do bazy danych. W przypadku wybrania opcji Podwójny znak cudzysłowu dla parametru Cudzysłowu zapytanie odzwierciedla następujący format: WHERE IN ("value1", "value2", "value3")

Lista rozwijana oparta na zapytaniach

Pobiera wynik zapytania jako dane wejściowe. Ma to samo zachowanie co parametr Lista rozwijana. Musisz zapisać zapytanie listy rozwijanej SQL usługi Databricks, aby użyć go jako danych wejściowych w innym zapytaniu.

1. Kliknij pozycję Lista rozwijana oparta na zapytaniach w obszarze Typ w panelu ustawień.
2. Kliknij pole Zapytanie i wybierz zapytanie. Jeśli zapytanie docelowe zwraca dużą liczbę rekordów, wydajność będzie spadać.

Jeśli zapytanie docelowe zwraca więcej niż jedną kolumnę, usługa SQL usługi Databricks używa pierwszej kolumny. Jeśli zapytanie docelowe zwraca name kolumny i value kolumny, usługa Sql usługi Databricks wypełnia widżet wyboru parametrów kolumną name , ale wykonuje zapytanie ze skojarzonym elementem value.

Załóżmy na przykład, że poniższe zapytanie zwraca dane w tabeli.

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

wartość	name
1001	John Smith
1002	Jane Doe
1003	Tabele Bobby

Gdy usługa Azure Databricks uruchamia zapytanie, wartość przekazana do bazy danych to 1001, 1002 lub 1003.

Data i godzina

Usługa Azure Databricks oferuje kilka opcji sparametryzowania wartości daty i znacznika czasu, w tym opcje upraszczające parametryzację zakresów czasu. Wybierz spośród trzech opcji o różnej precyzji:

Opcja	Dokładność	Typ
Data	dzień	DATE
Data i godzina	min.	TIMESTAMP
Data i godzina (z sekundami)	drugi	TIMESTAMP

Podczas wybierania opcji parametru Zakres należy utworzyć dwa parametry wyznaczone przez .start i .end sufiksy. Wszystkie opcje przekazują parametry do zapytania jako literały ciągu; Usługa Azure Databricks wymaga opakowowania wartości daty i godziny w pojedynczych cudzysłowach ('). Na przykład:

-- 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 daty używają interfejsu wybierania kalendarza i domyślnie do bieżącej daty i godziny.

Uwaga

Parametr Zakres dat zwraca tylko poprawne wyniki dla kolumn DATE typu. W przypadku TIMESTAMP kolumn użyj jednej z opcji Zakres dat i godzin.

Dynamiczne wartości daty i zakresu dat

Po dodaniu parametru daty lub zakresu dat do zapytania widżet wyboru wyświetla niebieską ikonę błyskawicy. Kliknij go, aby wyświetlić wartości dynamiczne, takie jak today, , yesterdaythis week, last week, last monthlub last year. Te wartości są aktualizowane dynamicznie.

Ważne

Dynamiczne daty i zakresy dat nie są zgodne z zaplanowanymi zapytaniami.

Używanie parametrów zapytania na pulpitach nawigacyjnych

Opcjonalnie zapytania mogą używać parametrów lub wartości statycznych. Gdy wizualizacja oparta na zapytaniu sparametryzowanym zostanie dodana do pulpitu nawigacyjnego, można skonfigurować wizualizację tak, aby korzystała z jednej z następujących opcji:

• Parametr widżetu

  Parametry widżetu są specyficzne dla pojedynczej wizualizacji na pulpicie nawigacyjnym, są wyświetlane w panelu wizualizacji, a określone wartości parametrów dotyczą tylko zapytania bazowego wizualizacji.

• Parametr pulpitu nawigacyjnego

  Parametry pulpitu nawigacyjnego mogą być stosowane do wielu wizualizacji. Po dodaniu wizualizacji na podstawie sparametryzowanego zapytania do pulpitu nawigacyjnego parametr zostanie domyślnie dodany jako parametr pulpitu nawigacyjnego. Parametry pulpitu nawigacyjnego są skonfigurowane dla co najmniej jednej wizualizacji na pulpicie nawigacyjnym i są wyświetlane w górnej części pulpitu nawigacyjnego. Wartości parametrów określone dla parametru pulpitu nawigacyjnego mają zastosowanie do wizualizacji ponownie korzystających z tego konkretnego parametru pulpitu nawigacyjnego. Pulpit nawigacyjny może mieć wiele parametrów, z których każdy może być stosowany do niektórych wizualizacji, a nie innych.

• Wartość statyczna

  Wartości statyczne są używane zamiast parametru, który reaguje na zmiany. Wartości statyczne umożliwiają kodowanie wartości zamiast parametru. Sprawiają one, że parametr "znikną" z pulpitu nawigacyjnego lub widżetu, w którym wcześniej był wyświetlany.

Po dodaniu wizualizacji zawierającej zapytanie sparametryzowane można wybrać tytuł i źródło parametru w zapytaniu wizualizacji, klikając odpowiednią ikonę ołówka. Możesz również wybrać słowo kluczowe i wartość domyślną. Zobacz Właściwości parametru.

Po dodaniu wizualizacji do pulpitu nawigacyjnego uzyskaj dostęp do interfejsu mapowania parametrów, klikając menu kebab w prawym górnym rogu widżetu pulpitu nawigacyjnego, a następnie klikając pozycję Zmień ustawienia widżetu.

Właściwości parametru

• Tytuł: nazwa wyświetlana wyświetlana wyświetlana obok selektora wartości na pulpicie nawigacyjnym. Domyślnie jest to słowo kluczowe parametru . Aby go edytować, kliknij ikonę ołówka . Tytuły nie są wyświetlane dla statycznych parametrów pulpitu nawigacyjnego, ponieważ selektor wartości jest ukryty. Jeśli wybierzesz wartość statyczną jako źródło wartości, pole Tytuł będzie wyszarane.

• Słowo kluczowe: literał ciągu dla tego parametru w zapytaniu źródłowym. Jest to przydatne do debugowania, jeśli pulpit nawigacyjny nie zwraca oczekiwanych wyników.

• Wartość domyślna: wartość używana, jeśli nie określono żadnej innego wartości. Aby zmienić to z ekranu zapytania, uruchom zapytanie z żądaną wartością parametru i kliknij przycisk Zapisz .

• Źródło wartości: źródło wartości parametru. Kliknij ikonę ołówka, aby wybrać źródło.

  • Nowy parametr pulpitu nawigacyjnego: utwórz nowy parametr na poziomie pulpitu nawigacyjnego. Dzięki temu można ustawić wartość parametru w jednym miejscu na pulpicie nawigacyjnym i zamapować ją na wiele wizualizacji.
  • Istniejący parametr pulpitu nawigacyjnego: mapuj parametr na istniejący parametr pulpitu nawigacyjnego. Musisz określić, który wcześniej istniejący parametr pulpitu nawigacyjnego.
  • Parametr widżetu: wyświetla selektor wartości wewnątrz widżetu pulpitu nawigacyjnego. Jest to przydatne w przypadku jednorazowych parametrów, które nie są udostępniane między widżetami.
  • Wartość statyczna: wybierz wartość statyczną dla widżetu, niezależnie od wartości używanych w innych widżetach. Statycznie mapowane wartości parametrów nie wyświetlają selektora wartości w dowolnym miejscu na pulpicie nawigacyjnym, co jest bardziej kompaktowe. Dzięki temu można korzystać z elastyczności parametrów zapytania bez zaśmiecania interfejsu użytkownika na pulpicie nawigacyjnym, gdy niektóre parametry nie powinny się często zmieniać.

  

Często zadawane pytania

• Czy można wielokrotnie używać tego samego parametru w jednym zapytaniu?
• Czy mogę użyć wielu parametrów w jednym zapytaniu?

Czy można wielokrotnie używać tego samego parametru w jednym zapytaniu?

Tak. Użyj tego samego identyfikatora w nawiasach klamrowych. W tym przykładzie użyto parametru {{org_id}} dwa razy.

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

Czy mogę użyć wielu parametrów w jednym zapytaniu?

Tak. Użyj unikatowej nazwy dla każdego parametru. W tym przykładzie użyto dwóch parametrów: {{org_id}} i {{start_date}}.

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