Utilizzare i parametri di query

Questo articolo illustra come usare i parametri di query nell'editor SQL di Azure Databricks.

I parametri di query consentono di rendere le query più dinamiche e flessibili inserendo valori di variabile in fase di esecuzione. Anziché impostare valori specifici con codifica fissa nelle query, è possibile definire parametri 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)

Inserire i parametri nelle query SQL digitando i due punti seguiti 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, 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

Gli esempi seguenti illustrano alcuni use case comuni per i parametri:

Inserire 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

Inserire un numero

Nell'esempio seguente è incluso un parametro Number che limita i risultati ai record in cui il 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

Inserire un nome 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 di colonna della tabella usata nella query.

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

Inserire oggetti di database

Nell'esempio seguente vengono creati tre parametri: catalog, schema e table.

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

Vedere clausola IDENTIFIER.

Concatenare più parametri

È possibile includere parametri in altre funzioni SQL. Questo esempio consente al visualizzatore di selezionare un titolo dipendente e un ID numero. 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 i parametri 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. L'esempio seguente include il parametro all'interno di una format_string funzione che ottiene quindi il cast 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(format_string("INTERVAL '%s' MINUTE", :param) AS INTERVAL MINUTE)

Aggiungere un intervallo di date

Nell'esempio seguente viene illustrato come aggiungere un intervallo di date con parametri per selezionare i record 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ù valori in una singola query

Nell'esempio seguente viene usata la ARRAY_CONTAINS funzione per filtrare un elenco di valori. Le TRANSFORMfunzioni , e SPLIT consentono di passare più valori delimitati da virgole come parametro stringa.

Il :list_parameter valore accetta un elenco di valori delimitati da virgole. La SPLIT funzione analizza tale elenco, suddividendo i valori delimitati da virgole in una matrice. La TRANSFORM funzione trasforma ogni elemento nella matrice rimuovendo qualsiasi spazio vuoto. La ARRAY_CONTAINS funzione controlla se il dropoff_zip valore della trips tabella è contenuto nella matrice di valori passati come list_parameter.

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

Modifiche alla sintassi

La tabella seguente illustra gli use case comuni per i parametri, la sintassi mustache di Databricks SQL originale e la sintassi equivalente usando la sintassi del marcatore di parametri denominata.

Sintassi dei parametri mustache

Nell'editor SQL qualsiasi stringa tra parentesi graffe doppie {{ }} viene considerata come parametro di query. Un widget viene visualizzato sopra il riquadro dei risultati in cui si imposta 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
• Parametri a discesa di più valori
• Parametri a discesa basati su query

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), Elenco a discesa ed Elenco a discesa basato su query. Il valore predefinito è testo.
2. Immettere la parola chiave, facoltativamente eseguire l'override del titolo e selezionare il tipo di parametro.
3. Fare clic su Aggiungi parametro.
4. Nel widget del parametro impostare 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>.

Rimuovere un parametro di query

Per rimuovere un parametro di query, eliminare il parametro dalla query. Il widget del parametro scompare ed è possibile riscrivere la query usando valori statici.

Modificare l'ordine dei parametri

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

Tipi di parametri di query

• Text
• Numero
• Elenco a discesa
• Elenco a discesa basato su query
• 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

Per limitare l'ambito dei valori dei parametri possibili durante l'esecuzione di una query, usare il tipo di parametro 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 in cui si immettono i valori consentiti, ogni valore separato da una nuova riga. Gli elenchi a discesa sono parametri di testo. Per usare date o date e ore nell'elenco a discesa, immetterli 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: attivare o disattivare l'opzione Consenti più valori. Nell'elenco a discesa Virgolette, scegliere se lasciare i parametri come immessi (senza virgolette) o eseguire il wrapping dei parametri 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 al database. Se si seleziona l'opzione Virgolette doppie per il parametro Virgolette, la query riflette il formato seguente: WHERE IN ("value1", "value2", "value3")

Elenco a discesa basato su query

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

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

Se la query di destinazione restituisce più colonne, Databricks SQL usa la prima colonna. Se la query di destinazione restituisce le colonne name e value, Databricks SQL popola il widget di selezione dei parametri con la colonna name ma esegue la query con l'oggetto associato value.

Ad esempio, supponiamo che la query seguente restituisca i dati nella tabella.

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

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 i valori data e ora, incluse le opzioni per semplificare la parametrizzazione degli intervalli di tempo. Selezionare una delle tre opzioni con precisione variabile:

Quando si sceglie un'opzione parametro Intervallo, si creano due parametri designati dai suffissi .start e .end. Tutte le opzioni passano parametri alla query come valori letterali stringa; Azure Databricks richiede il wrapping dei valori di data e ora 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 }}'

I parametri data usano un'interfaccia di selezione del calendario e il valore predefinito è la data e l'ora correnti.

Valori di intervallo di date e date dinamiche

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 valori dinamici come today, yesterday, this week, last week, last month o last year. Questi valori vengono aggiornati in modo dinamico.

Uso dei parametri di query nei dashboard

Facoltativamente, le query possono usare parametri o valori statici. 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 parametri del widget sono specifici di una singola visualizzazione in un dashboard, vengono visualizzati nel pannello di visualizzazione e i valori dei parametri specificati si applicano solo alla query sottostante alla visualizzazione.

• Parametro del dashboard

  I parametri del dashboard possono essere applicati 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 parametri del dashboard sono configurati per una o più visualizzazioni in un dashboard e vengono visualizzati nella parte superiore del dashboard. I valori dei parametri specificati per un parametro del dashboard si applicano alle visualizzazioni riutilizzando quel particolare parametro del dashboard. Un dashboard può avere più parametri, ognuno dei quali può essere applicato ad alcune visualizzazioni e non ad altri.

• Valore statico

  I valori statici vengono usati al posto di un parametro che risponde alle modifiche. I valori statici consentono di impostare come hardcoded un valore al posto di un parametro. Il parametro "scompare" dal dashboard o dal widget in cui è apparso 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 selezionare 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 i parametri del dashboard statico perché il selettore di valori è nascosto. Se si seleziona Valore statico come origine valore, il campo Titolo è disattivato.

• 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 impostare 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 i parametri occasionali non condivisi tra i widget.
  • Valore statico: scegliere un valore statico per il widget, indipendentemente dai valori usati in altri widget. I valori dei parametri mappati in modo statico non mostrano un selettore di valori in qualsiasi punto del dashboard, che è più compatto. In questo modo è possibile sfruttare la flessibilità dei parametri di query senza ingrombrare l'interfaccia utente in un dashboard quando alcuni parametri non sono destinati a cambiare frequentemente.

  

Domande frequenti

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

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

Sì. Usare lo stesso identificatore tra 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ù parametri in una singola query?

Sì. Usare un nome univoco per ogni parametro. In questo esempio vengono usati due parametri: {{org_id}} e {{start_date}}.

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