Lavorare con la query parameters

Questo articolo spiega come lavorare con la query parameters nell'editor SQL di Azure Databricks.

Le query parameters consentono di rendere le query più dinamiche e flessibili inserendo variabili values in fase di esecuzione. Invece di inserire direttamente values nelle query, è possibile definire parameters per filtrare i dati o modificare l'output in base all'input dell'utente. Questo approccio migliora il riutilizzo delle query e la sicurezza impedendo l'inserimento di SQL e consente una gestione più efficiente di diversi scenari di dati.

Sintassi dell'indicatore di parametro denominato

I marcatori di parametro denominati sono variabili segnaposto digitati. Usare questa sintassi per scrivere query nelle parti seguenti dell'interfaccia utente di Azure Databricks:

• Editor SQL
• Notebook
• Editor del set di dati del dashboard di IA/BI
• Spazi genie di IA/BI (anteprima pubblica)

Insert parameters nelle tue query SQL digitando due punti seguito da un nome di parametro, ad esempio :parameter_name. Quando si include un marcatore di parametro denominato in una query, nell'interfaccia utente viene visualizzato un widget. È possibile usare il widget per modificare il tipo di parametro e il nome.

Aggiungere un marcatore di parametro denominato a una query

In questo esempio viene aggiunto un marcatore di parametro alla query seguente:

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

Questa query restituisce un set di dati che include solo gli importi delle tariffe minori di cinque dollari. Usare la procedura seguente per modificare la query per usare un parametro anziché il valore hardcoded (5).

1. Eliminare il numero 5 dalla query.
2. Digitare due punti (:) seguito dalla stringa fare_parameter. L'ultima riga della query aggiornata deve indicare fare_amount < :fare_parameter.
3. Fare clic sull'icona a forma di ingranaggio accanto al widget del parametro. La finestra di dialogo mostra i campi seguenti:
   • Parola chiave: parola chiave che rappresenta il parametro nella query. Non è possibile modificare questo campo. Per modificare la parola chiave, modificare l'indicatore nella query SQL.
   • Titolo: titolo visualizzato sul widget. Per impostazione predefinita, il titolo corrisponde alla parola chiave .
   • Tipo: i tipi supportati sono Testo, Numero, Elenco a discesa List, Data, Data e Ora e Data e Ora (con Secondi). Il valore predefinito è testo.
4. Nella finestra di dialogo, modificare il Tipo in Numero.
5. Immettere un numero nel widget del parametro e fare clic su Applica modifiche.
6. Fare clic su Salva per salvare la query.

Esempi di sintassi dei parametri denominati

Negli esempi seguenti vengono illustrati alcuni casi d'uso comuni per parameters.

Insert una data

L'esempio seguente include un parametro Date che limita i risultati della query ai record dopo una data specifica.

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 un numero

L'esempio seguente include un parametro Number che limita i risultati ai record where campo o_total_price è maggiore del valore del parametro specificato.

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

Insert un nome di campo

Nell'esempio seguente, field_param viene usato con la funzione IDENTIFIER per fornire un valore soglia per la query in fase di esecuzione. Il valore del parametro deve essere un nome column del table usato nella query.

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

Insert oggetti di database

Nell'esempio seguente vengono creati tre parameters: catalog, schemae table.

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

Vedere IDENTIFIER clausola.

Concatenare più parameters

È possibile includere parameters in altre funzioni SQL. Questo esempio consente al visualizzatore di select una posizione lavorativa e un numero ID. La query usa la funzione format_string per concatenare le due stringhe e filtrare le righe corrispondenti. Vedere la funzione format_string.

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

Usare le stringe JSON

È possibile usare parameters per estrarre un attributo da una stringa JSON. Nell'esempio seguente viene utilizzata la funzione from_json per convertire una stringa JSON in un valore struct. La sostituzione della stringa a come valore per il parametro (param) restituisce l'attributo 1.

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

Creare un intervallo

Il INTERVAL tipo rappresenta un intervallo di tempo e consente di eseguire operazioni aritmetiche e basate sul tempo. Nell'esempio seguente viene usata una funzione CAST per eseguire il cast del parametro come tipo di intervallo. Il valore risultante INTERVAL può essere usato per i calcoli basati sul tempo o il filtro nella query.

Per informazioni dettagliate e sintassi complete, vedere TIPO INTERVAL.

SELECT CAST(:param AS INTERVAL MINUTE)

Aggiungere un intervallo di date

Nell'esempio seguente viene illustrato come aggiungere un intervallo di date con parametri ai record select in un intervallo di tempo specifico.

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

Parametrizza rollup per giorno, mese o anno

Nell'esempio seguente vengono aggregati i dati delle corse dei taxi a un livello di granularità con parametri. La DATE_TRUNC funzione tronca il tpep_pickup_datetime valore in base al valore del :date_granularity parametro, ad esempio DAY, MONTHo YEAR. La data troncata viene aliasata come date_rollup e usata nella GROUP BY clausola .

SELECT DATE_TRUNC(:date_granularity, tpep_pickup_datetime) AS
  date_rollup,
  COUNT(*) AS total_trips
FROM samples.nyctaxi.trips
GROUP BY date_rollup

Usare più values in una singola query

Nell'esempio seguente viene usata la funzione ARRAY_CONTAINS per filtrare un list di values. Le funzioni TRANSFORMe SPLIT consentono di passare più values delimitati da virgole come parametro stringa.

Il valore :list_parameter accetta una list di valuesdelimitati da virgole. La funzione SPLIT analizza list, suddividendo il values separato da virgole in una matrice. La TRANSFORM funzione trasforma ogni elemento nella matrice rimuovendo qualsiasi spazio vuoto. La funzione ARRAY_CONTAINS controlla se il valore dropoff_zip del tripstable è contenuto nella matrice di values passata come list_parameter.

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

Nota

Questo esempio funziona per la stringa values. Per modificare la query per altri tipi di dati, ad esempio un list di interi, eseguire il wrapping dell'operazione di TRANSFORM con un'operazione di CAST per convertire la stringa values nel tipo di dati desiderato.

Modifiche alla sintassi

Il seguente table illustra i casi d'uso comuni per parameters, la sintassi mustache originale di Databricks SQL, e la sintassi equivalente con il marcatore di parametri denominati.

Use case del parametro	Sintassi dei parametri mustache	Sintassi dell'indicatore di parametro denominato
Caricare solo i dati prima di una data specificata	WHERE date_field < '{{date_param}}' È necessario includere virgolette intorno al parametro date e parentesi graffe.	WHERE date_field < :date_param
Caricare solo dati minori di un valore numerico specificato	WHERE price < {{max_price}}	WHERE price < :max_price
Confronta due stringhe	WHERE region = {{region_param}}	WHERE region = :region_param
Specificare il table utilizzato in una query	SELECT * FROM {{table_name}}	SELECT * FROM IDENTIFIER(:table) Quando un utente immette questo parametro, deve usare lo spazio dei nomi completo a tre livelli per identificare il table.
Specificare in modo indipendente le catalog, le schemae le table usate in una query	SELECT * FROM {{catalog}}.{{schema}}.{{table}}	SELECT * FROM IDENTIFIER(:catalog \|\| '.' \|\| :schema \|\| '.' \|\| :table)
Usare parameters come modello in una stringa formattata più lunga	"({{area_code}}) {{phone_number}}" Il parametro values viene concatenato automaticamente come stringa.	format_string("(%d)%d, :area_code, :phone_number) Per un esempio completo, vedere Concatenare più parameters.
Creare un intervallo	SELECT INTERVAL {{p}} MINUTE	SELECT CAST(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Sintassi dei parametri mustache

Importante

Le sezioni seguenti si applicano alla sintassi di query che è possibile usare solo nell'editor SQL. Ciò significa che se si copia e incolla una query usando questa sintassi in qualsiasi altra interfaccia di Azure Databricks, ad esempio un notebook o un editor di set di dati del dashboard di intelligenza artificiale/BI, la query deve essere modificata manualmente per usare marcatori di parametri denominati prima di essere eseguiti senza errori.

Nell'editor SQL qualsiasi stringa tra parentesi graffe doppie {{ }} viene considerata come parametro di query. Un widget viene visualizzato sopra il riquadro dei risultati where si set il valore del parametro. Anche se Azure Databricks consiglia in genere di usare marcatori di parametro denominati, alcune funzionalità sono supportate solo usando la sintassi dei parametri mustache.

Usare la sintassi dei parametri mustache per le funzionalità seguenti:

• Filtri del dashboard legacy
• elenco a discesa con selezione multipla parameters
• elenco a discesa basato su query parameters

Aggiungere un parametro mustache

1. Digitare Cmd + I. Il parametro viene inserito nel cursore di testo e viene visualizzata la finestra di dialogo Aggiungi parametro.
   • Parola chiave: parola chiave che rappresenta il parametro nella query.
   • Titolo: titolo visualizzato sul widget. Per impostazione predefinita, il titolo corrisponde alla parola chiave .
   • Tipo: i tipi supportati sono Testo, Numero, Data, Data e Ora, Data e Ora (con secondi), Menu a tendina Liste Menu a tendina basato su query List. Il valore predefinito è testo.
2. Immettere la parola chiave , facoltativamente eseguire l'override del titolo e select il tipo di parametro.
3. Fare clic su Aggiungi parametro.
4. Nel widget del parametro, set visualizza il valore del parametro.
5. Fare clic su Applica modifiche.
6. Fare clic su Salva.

In alternativa, digitare parentesi graffe doppie {{ }} e fare clic sull'icona a forma di ingranaggio accanto al widget del parametro per modificare le impostazioni.

Per eseguire nuovamente la query con un valore di parametro diverso, immettere il valore nel widget e fare clic su Applica modifiche.

Modificare un parametro di query

Per modificare un parametro, fare clic sull'icona a forma di ingranaggio accanto al widget del parametro. Per impedire agli utenti che non possiedono la query di modificare il parametro, fare clic su Mostra solo risultati. Verrà visualizzata la finestra di dialogo dei parametri <Keyword>.

Remove un parametro di query

Per remove un parametro di query, eliminare il parametro dalla query. Il widget del parametro scompare ed è possibile riscrivere la query usando statico values.

Modificare l'ordine dei parameters

Per modificare l'ordine in cui vengono visualizzati parameters, è possibile fare clic e trascinare ogni parametro nella posizione desiderata.

Tipi di parametri di query

• Text
• Numero
• elenco a discesa List
• Query-Based menu a tendina List
• Data e ora

Testo

Accetta una stringa come input. La barra rovesciata e le virgolette singole e doppie sono precedute da caratteri di escape e Azure Databricks aggiunge virgolette a questo parametro. Ad esempio, una stringa come mr's Li"s viene trasformata in 'mr\'s Li\"s' Un esempio di utilizzo potrebbe essere

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

Numero

Accetta un numero come input. Un esempio di utilizzo di questo potrebbe essere

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

Elenco a discesa List

Per limitare l'ambito del possibile parametro values durante l'esecuzione di una query, usare il tipo di parametro List elenco a discesa. Un esempio sarebbe SELECT * FROM users WHERE name='{{ dropdown_param }}'. Se selezionata nel pannello delle impostazioni dei parametri, viene visualizzata una casella di testo where si immette il valuesconsentito, ogni valore separato da una nuova riga. Gli elenchi a discesa sono in formato testo parameters. Per inserire date o date e ore nell'elenco a discesa List, inseriteli nel formato richiesto dall'origine dati. Le stringhe non vengono precedute da escape. È possibile scegliere tra un elenco a discesa a valore singolo o multivalore.

• Valore singolo: sono necessarie virgolette singole intorno al parametro.
• multivalore: attiva/disattiva l'opzione Consenti valuespiù. Nell'elenco a discesa virgolette, scegliere se lasciare il parameters come immesso (senza virgolette) o racchiudere il parameters con virgolette singole o doppie. Non è necessario aggiungere virgolette intorno al parametro se si scelgono le virgolette.

Modificare la clausola WHERE per usare la parola chiave IN nella query.

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

Il widget di selezione multipla del parametro consente di passare più valori values al database. Se si l'opzione virgolette doppie per il parametro virgolette , la query riflette il formato seguente:

Query-Based elenco a discesa List

Accetta il risultato di una query come input. Ha lo stesso comportamento del parametro elenco a discesa List. È necessario salvare la query list del menu a discesa Databricks SQL per utilizzarla come input in un'altra query.

1. Fare clic su elenco a discesa basato su query list in Tipo nel pannello delle impostazioni.
2. Fare clic sul campo Query e select una query. Se la query di destinazione restituisce un numero elevato di record, le prestazioni risulteranno ridotte.

Se la query di destinazione restituisce più di un column, Databricks SQL usa prima uno. Se la query di destinazione restituisce name e valuecolumns, Databricks SQL popola il widget di selezione dei parametri con il namecolumn ma esegue la query con il valueassociato.

Si supponga, ad esempio, che la query seguente restituisca i dati nel table.

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

value	name
1001	Giorgio Cavaglieri
1002	Valeria Dal Monte
1003	Bobby Tables

Quando Azure Databricks esegue la query, il valore passato al database sarà 1001, 1002 o 1003.

Data e ora

Azure Databricks offre diverse opzioni per parametrizzare data e timestamp values, incluse le opzioni per semplificare la parametrizzazione degli intervalli di tempo. Select tra tre opzioni di precisione variabile:

Opzione	Precisione	Type
Data	Giorno	DATE
Data e ora	minuto	TIMESTAMP
Data e ora (con secondi)	second	TIMESTAMP

Quando si sceglie un'opzione di parametro intervallo, si creano due designati dai suffissi e . Tutte le opzioni passano parameters alla query come valori letterali stringa; Azure Databricks richiede il wrapping di data e ora values tra virgolette singole ('). Ad esempio:

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

Data parameters usare un'interfaccia di selezione del calendario e il valore predefinito è la data e l'ora correnti.

Nota

Il parametro Date Range restituisce solo i risultati corretti per columns di tipo DATE. Per TIMESTAMPcolumns, usare una delle opzioni Intervallo di data e ora.

Data dinamica e intervallo di date values

Quando si aggiunge un parametro di data o intervallo di date alla query, il widget di selezione mostra un'icona a forma di fulmine blu. Fare clic su di esso per visualizzare values dinamiche come today, yesterday, this week, last week, last montho last year. Questi vengono gestiti valuesupdate dinamicamente.

Importante

Le date dinamiche e gli intervalli di date non sono compatibili con le query pianificate.

Utilizzo della query parameters nei dashboard

Facoltativamente, le query possono usare parameters o valuesstatici. Quando una visualizzazione basata su una query con parametri viene aggiunta a un dashboard, la visualizzazione può essere configurata per l'uso di uno dei due elementi seguenti:

• Parametro widget

  I widget parameters sono specifici di una singola visualizzazione in un dashboard, appaiono nel pannello di visualizzazione e il parametro values specificato si applica solo alla query sottostante la visualizzazione.

• Parametro del dashboard

  Il dashboard parameters può essere applicato a più visualizzazioni. Quando si aggiunge a un dashboard una visualizzazione basata su una query con parametri, il parametro verrà aggiunto come parametro del dashboard per impostazione predefinita. I parameters dashboard sono configurati per una o più visualizzazioni in un dashboard e vengono visualizzati nella parte superiore del dashboard. Il parametro values specificato per un parametro del dashboard si applica alle visualizzazioni riutilizzando quel particolare parametro del dashboard. Un dashboard può avere più parameters, ognuno dei quali può essere applicato ad alcune visualizzazioni e non ad altri.

• Valore statico

  Le values statiche vengono usate al posto di un parametro che risponde alle modifiche. La values statica consente di impostare un valore fisso al posto di un parametro. Il parametro "scompare" dal dashboard o dal widget where visualizzato in precedenza.

Quando si aggiunge una visualizzazione contenente una query con parametri, è possibile scegliere il titolo e l'origine per il parametro nella query di visualizzazione facendo clic sull'icona a forma di matita appropriata. È anche possibile select la parola chiave e un valore predefinito. Vedere Proprietà parametri.

Dopo aver aggiunto una visualizzazione a un dashboard, accedere all'interfaccia di mapping dei parametri facendo clic sul menu kebab in alto a destra di un widget del dashboard e quindi scegliendo Modifica impostazioni widget.

Proprietà dei parametri

• Titolo: nome visualizzato accanto al selettore di valori nel dashboard. Per impostazione predefinita, viene usata la parola chiave del parametro. Fare clic sull'icona a matita per modificarlo. I titoli non vengono visualizzati per il dashboard statico parameters perché il selettore di valori è nascosto. Se usi valore statico comeorigine del valore , il campo titolo è disabilitato.

• Parola chiave: il valore letterale stringa per questo parametro nella query sottostante. Ciò è utile per il debug se il dashboard non restituisce i risultati previsti.

• Valore predefinito: il valore utilizzato se non ne viene specificato un altro. Per modificare questa operazione dalla schermata della query, eseguire la query con il valore del parametro desiderato e fare clic sul pulsante Salva.

• Origine valore: origine del valore del parametro. Fare clic sull'icona a forma di matita per scegliere un'origine.

  • Nuovo parametro del dashboard: creare un nuovo parametro a livello di dashboard. In questo modo è possibile set un valore di parametro in un'unica posizione nel dashboard ed eseguirne il mapping a più visualizzazioni.
  • Parametro dashboard esistente: eseguire il mapping del parametro a un parametro del dashboard esistente. È necessario specificare il parametro del dashboard preesistente.
  • Parametro del widget: mostra un selettore di valori all'interno del widget del dashboard. Ciò è utile per parameters una tantum che non sono condivise tra i widget.
  • valore statico: scegliere un valore statico per il widget, indipendentemente dal values usato in altri widget. Il parametro mappato in modo statico values non visualizza un selettore di valori in un punto qualsiasi del dashboard, che è più compatto. In questo modo è possibile sfruttare la flessibilità delle query parameters senza ingombrare l'interfaccia utente in un dashboard quando alcuni parameters non sono previsti cambiare frequentemente.

  

Domande frequenti

• È possibile riutilizzare più volte lo stesso parametro in una singola query?
• È possibile usare più parameters in una singola query?

È possibile riutilizzare più volte lo stesso parametro in una singola query?

Sì. Usare la stessa identifier nelle parentesi graffe. In questo esempio viene usato due volte il parametro {{org_id}}.

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

È possibile usare più parameters in una singola query?

Sì. Usare un nome univoco per ogni parametro. Questo esempio usa due parameters: {{org_id}} e {{start_date}}.

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