Arbeiten mit Abfrageparametern

In diesem Artikel wird erläutert, wie Sie mit Abfrageparametern im SQL-Editor von Azure Databricks arbeiten.

Mit Abfrageparametern können Sie Abfragen dynamischer und flexibler gestalten, indem Sie Variablenwerte zur Laufzeit einfügen. Anstatt bestimmte Werte in Abfragen hart zu codieren, können Sie Parameter definieren, um Daten zu filtern oder die Ausgabe auf Grundlage der Benutzereingabe zu ändern. Dieser Ansatz verbessert die Wiederverwendung von Abfragen und die Sicherheit, indem die Einschleusung von SQL-Befehlen verhindert wird. Außerdem ermöglicht er eine effizientere Handhabung verschiedener Datenszenarien.

Syntax für benannte Parametermarkierungen

Benannte Parametermarkierungen sind typbehaftete Platzhaltervariablen. Verwenden Sie diese Syntax, um Abfragen in den folgenden Teilen der Benutzeroberfläche von Azure Databricks zu schreiben:

• SQL-Editor

• Notebooks

• DataSet-Editor eines KI/BI-Dashboards

• KI/BI Genie-Bereiche (Public Preview)

Fügen Sie Parameter in SQL-Abfragen ein, indem Sie einen Doppelpunkt eingeben, gefolgt von einem Parameternamen wie etwa :parameter_name. Wenn Sie eine benannte Parametermarkierung in eine Abfrage einbeziehen, wird ein Widget in der Benutzeroberfläche angezeigt. Mit dem Widget können Sie den Typ und den Namen des Parameters bearbeiten.

Hinzufügen einer benannten Parametermarkierung zu einer Abfrage

In diesem Beispiel wird der folgenden Abfrage eine Parametermarkierung hinzugefügt:

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

Diese Abfrage gibt ein DataSet zurück, das nur Fahrpreise von unter fünf Dollar enthält. Gehen Sie wie folgt vor, um die Abfrage so zu bearbeiten, dass anstelle des hartcodierten Werts (5) ein Parameter verwendet wird.

1. Löschen Sie die Zahl 5 aus der Abfrage.
2. Geben Sie einen Doppelpunkt ein (:), gefolgt von der Zeichenfolge fare_parameter. Die letzte Zeile der aktualisierten Abfrage sollte fare_amount < :fare_parameter lauten.
3. Klicken Sie auf das Zahnradsymbol in der Nähe des Parameterwidgets. Das Dialogfeld zeigt die folgenden Felder:
   • Schlüsselwort: Das Schlüsselwort, das den Parameter in der Abfrage darstellt. Sie können dieses Feld nicht bearbeiten. Bearbeiten Sie die Markierung in der SQL-Abfrage, um das Schlüsselwort zu ändern.
   • Titel: Der Titel, der über dem Widget angezeigt wird. Standardmäßig entspricht der Titel dem Schlüsselwort.
   • Typ: Unterstützte Typen sind „Text“, „Zahl“, „Dropdownliste“, „Datum“, „Datum und Uhrzeit“ und „Datum und Uhrzeit (mit Sekunden)“. Der Standardwert ist „Text“.
4. Ändern Sie im Dialogfeld den Typ in Zahl.
5. Geben Sie eine Zahl in das Parameterwidget ein, und klicken Sie auf Änderungen übernehmen.
6. Klicken Sie auf Speichern, um die Abfrage zu speichern.

Beispiele für die Syntax für benannte Parameter

Die folgenden Beispiele veranschaulichen einige häufige Anwendungsfälle für Parameter.

Einfügen eines Datums

Das folgende Beispiel enthält den Parameter Date, der Abfrageergebnisse auf Datensätze nach einem bestimmten Datum beschränkt.

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

Einfügen einer Zahl

Das folgende Beispiel enthält den Parameter Zahl, der Ergebnisse auf Datensätze beschränkt, bei denen das Feld o_total_price größer als der angegebene Parameterwert ist.

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

Einfügen eines Feldnamens

Im folgenden Beispiel wird field_param mit der Funktion IDENTIFIER verwendet, um einen Schwellenwert für die Abfrage zur Laufzeit bereitzustellen. Der Parameterwert sollte ein Spaltenname aus der Tabelle sein, die in der Abfrage verwendet wird.

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

Einfügen von Datenbankobjekten

Im folgenden Beispiel werden drei Parameter erstellt: catalog, schema, und table.

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

Weitere Informationen finden Sie unter IDENTIFIER-Klausel.

Verketten mehrerer Parameter

Sie können Parameter in andere SQL-Funktionen einschließen. In diesem Beispiel kann der Viewer einen Mitarbeitertitel und eine Nummern-ID auswählen. Die Abfrage verwendet die Funktion format_string, um die beiden Zeichenfolgen zu verketten und nach den Zeilen zu filtern, die übereinstimmen. Siehe format_string-Funktion.

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

Arbeiten mit JSON-Zeichenketten

Sie können anhand von Parametern ein Attribut aus einer JSON-Zeichenkette extrahieren. Im folgenden Beispiel wird die Funktion from_json verwendet, um die JSON-Zeichenkette in einen Strukturwert zu konvertieren. Durch das Ersetzen der Zeichenkette a als Wert für den Parameter (param) wird das Attribut 1 zurückgegeben.

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

Erstellen eines Intervalls

Der INTERVAL Typ stellt eine Zeitspanne dar und ermöglicht es Ihnen, zeitbasierte Arithmetik und Vorgänge auszuführen. Das folgende Beispiel enthält den Parameter in einer format_string Funktion, die dann als Intervalltyp umgegossen wird. Der resultierende INTERVAL Wert kann für zeitbasierte Berechnungen oder Filterungen in der Abfrage verwendet werden.

Ausführliche Informationen und Syntax finden Sie unter INTERVAL-Typ .

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

Hinzufügen eines Datumsbereichs

Das folgende Beispiel zeigt, wie Sie einen parametrisierten Datumsbereich hinzufügen, um Datensätze in einem bestimmten Zeitrahmen auszuwählen.

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

Parametrisieren von Rollups nach Tag, Monat oder Jahr

Im folgenden Beispiel werden Taxitripdaten auf einer parametrisierten Granularitätsebene aggregiert. Die DATE_TRUNC Funktion schneidet den tpep_pickup_datetime Wert basierend auf dem :date_granularity Parameterwert ab, z DAY. B. , , MONTHoder YEAR. Das abgeschnittene Datum wird als date_rollup Alias für die GROUP BY Klausel verwendet.

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

Verwenden mehrerer Werte in einer einzelnen Abfrage

Im folgenden Beispiel wird die ARRAY_CONTAINS Funktion verwendet, um eine Liste von Werten zu filtern. Mit TRANSFORMden Funktionen SPLIT können mehrere durch Kommas getrennte Werte als Zeichenfolgenparameter übergeben werden.

Der :list_parameter Wert akzeptiert eine Liste von durch Kommas getrennten Werten. Die SPLIT Funktion analysiert diese Liste und teilt die durch Trennzeichen getrennten Werte in ein Array auf. Die TRANSFORM Funktion transformiert jedes Element im Array, indem alle Leerzeichen entfernt werden. Die ARRAY_CONTAINS Funktion überprüft, ob der dropoff_zip Wert aus der trips Tabelle im Array der als list_parameter

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

Syntaxänderungen

In der folgenden Tabelle sind häufige Anwendungsfälle für Parameter, die ursprüngliche SQL-Mustache-Syntax für Databricks und die entsprechende Syntax mit der Syntax für benannte Parametermarkierungen aufgeführt.

Syntax für Mustache-Parameter

Im SQL-Editor werden Zeichenfolgen zwischen doppelten geschweiften Klammern {{ }} als Abfrageparameter behandelt. Oberhalb des Ergebnisbereichs wird ein Widget angezeigt, in dem Sie den Parameterwert festlegen können. Azure Databricks empfiehlt zwar in der Regel die Verwendung benannter Parametermarkierungen, einige Funktionalitäten werden jedoch nur mithilfe der Syntax für Mustache-Parameter unterstützt.

Nutzen Sie die Syntax für Mustache-Parameter für die folgende Funktionalität:

• Filter von Vorversion-Dashboards
• Dropdown-Parameter mit mehreren Werten
• Datumsbereiche
• Abfragebasierte Dropdown-Parameter

Hinzufügen eines Mustache-Parameters

1. Geben Sie Cmd + Iein. Der Parameter wird am Caretzeichen des Texts eingefügt und das Dialogfeld Parameter hinzufügen wird angezeigt.
   • Schlüsselwort: Das Schlüsselwort, das den Parameter in der Abfrage darstellt.
   • Titel: Der Titel, der über dem Widget angezeigt wird. Standardmäßig entspricht der Titel dem Schlüsselwort.
   • Typ: Unterstützte Typen sind „Text“, „Zahl“, „Datum“, „Datum und Uhrzeit“, „Datum und Uhrzeit (mit Sekunden)“, „Dropdownliste“ und „Abfragebasierte Dropdownliste“. Der Standardwert ist „Text“.
2. Geben Sie das Schlüsselwort ein, überschreiben Sie optional den Titel und wählen Sie den Parametertyp aus.
3. Klicken Sie auf Parameter hinzufügen.
4. Legen Sie im Parameter-Widget den Parameterwert fest.
5. Klicken Sie auf Apply Changes.
6. Klicken Sie auf Speichern.

Alternativ können Sie doppelte geschweifte Klammern ({{ }}) eingeben und auf das Zahnradsymbol neben dem Parameterwidget klicken, um die Einstellungen zu bearbeiten.

Um die Abfrage mit einem anderen Parameterwert erneut auszuführen, geben Sie den Wert im Widget ein und klicken Sie auf Änderungen übernehmen.

Einen Abfrageparameter bearbeiten

Um einen Parameter zu bearbeiten, klicken Sie auf das Zahnradsymbol neben dem Parameter-Widget. Um zu verhindern, dass Benutzer, die die Abfrage nicht besitzen, den Parameter ändern, klicken Sie auf Nur Ergebnisse anzeigen. Das <Keyword>-Parameterdialogfeld wird angezeigt.

Entfernen eines Abfrageparameters

Um einen Abfrageparameter zu entfernen, löschen Sie den Parameter aus ihrer Abfrage. Das Parameterwidget verschwindet, und Sie können Ihre Abfrage mithilfe statischer Werte neu schreiben.

Ändern der Reihenfolge der Parameter

Wenn Sie die Reihenfolge ändern möchten, in der Parameter angezeigt werden, können Sie auf die einzelnen Parameter klicken und sie an die gewünschte Position ziehen.

Abfrage-Parametertypen

• Text
• Number
• Dropdownliste
• Abfragebasierte Auswahlliste
• Datum und Uhrzeit

Text

Verwendet Zeichenfolge als Eingabe. Umgekehrte Schrägstriche, einfache Anführungszeichen und doppelte Anführungszeichen werden mit Escapezeichen versehen und Azure Databricks fügt diesem Parameter Anführungszeichen hinzu. Beispielsweise wird eine Zeichenfolge wie mr's Li"s in 'mr\'s Li\"s' transformiert. Ein Anwendungsbeispiel hierfür könnte wie folgt aussehen:

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

Number

Verwendet eine Zahl als Eingabe. Ein Anwendungsbeispiel hierfür könnte wie folgt aussehen:

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

Dropdownliste

Um den Bereich möglicher Parameterwerte beim Ausführen einer Abfrage einzuschränken, verwenden Sie den Parametertyp Dropdownliste. Ein Beispiel wäre SELECT * FROM users WHERE name='{{ dropdown_param }}'. Wenn dies im Bereich „Parametereinstellungen“ ausgewählt ist, wird ein Textfeld angezeigt, in dem Sie Ihre zulässigen Werte eingeben, wobei jeder Wert durch eine neue Zeile getrennt wird. Auswahllisten sind Textparameter. Um „Datum“ oder „Datum und Uhrzeit“ in Ihrer Dropdownliste zu verwenden, geben Sie sie in dem Format ein, das Ihre Datenquelle benötigt. Die Zeichenfolgen werden nicht mit Escapezeichen versehen. Sie können zwischen einem einzelnen Wert oder einer Dropdownliste mit mehreren Werten wählen.

• Einzelner Wert: Um den Parameter sind einfache Anführungszeichen erforderlich.
• Mehrere Werte: Schalten Sie die Option Mehrere Werte zulassen um. Wählen Sie in der Auswahlliste Anführungszeichen aus, ob die Parameter wie eingegeben (ohne Anführungszeichen) übernommen oder ob die Parameter mit einfachen oder doppelten Anführungszeichen umschlossen werden sollen. Wenn Sie Anführungszeichen auswählen, müssen Sie keine Anführungszeichen um den Parameter hinzufügen.

Ändern Sie die WHERE-Klausel, sodass diese das IN-Schlüsselwort in Ihrer Abfrage verwendet.

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

Mit dem Parameter-Widget für die Mehrfachauswahl können Sie mehrere Werte an die Datenbank übergeben. Wenn Sie die Option Doppeltes Anführungszeichen für den Zitat-Parameter auswählen, weist Ihre Abfrage das folgende Format auf: WHERE IN ("value1", "value2", "value3").

Abfragebasierte Auswahlliste

Verwendet das Ergebnis einer Abfrage als Eingabe. Dies verhält sich ebenso wie der Parameter Auswahlliste. Sie müssen die Databricks SQL-Auswahllistenabfrage speichern, um sie als Eingabe in einer anderen Abfrage verwenden zu können.

1. Klicken Sie im Einstellungsfenster unter Typ auf Abfragebasierte Dropdownliste.
2. Klicken Sie auf das Feld Abfrage und wählen Sie eine Abfrage aus. Wenn die Zielabfrage eine große Anzahl von Datensätzen zurückgibt, wird die Leistung beeinträchtigt.

Wenn Ihre Zielabfrage mehrere Spalten zurückgibt, verwendet Databricks SQL die erste Spalte. Wenn ihre Zielabfrage name- und value-Spalten zurückgibt, füllt Databricks SQL das Widget für die Parameterauswahl mit der name-Spalte auf, führt jedoch die Abfrage mit dem zugeordneten value aus.

Angenommen, die folgende Abfrage gibt die Daten in der Tabelle zurück.

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

Wenn Azure Databricks die Abfrage ausführt, lautet der an die Datenbank übergebene Wert 1001, 1002 oder 1003.

Datum und Uhrzeit

Azure Databricks bietet verschiedene Optionen zur Parametrisierung von Datums- und Zeitstempelwerten, einschließlich Optionen zur Vereinfachung der Parametrisierung von Zeitbereichen. Wählen Sie aus drei Optionen mit unterschiedlicher Genauigkeit aus:

Beim Auswählen einer Range-Parameteroption erstellen Sie zwei Parameter, die durch .start- und .end-Suffixe bestimmt sind. Alle Optionen übergeben Parameter als Zeichenfolgenliterale an Ihre Abfrage. Azure Databricks verlangt, dass Sie Datums- und Zeitwerte in einfache Anführungszeichen (') einschließen. Beispiel:

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

Datumsparameter verwenden eine Kalenderauswahloberfläche und standardmäßig das aktuelle Datum und die aktuelle Uhrzeit.

Dynamische Datums- und Datumsbereichswerte

Wenn Sie Ihrer Abfrage einen Datums- oder Datumsbereichsparameter hinzufügen, zeigt das Auswahlwidget ein blaues Blitzsymbol an. Klicken Sie darauf, um dynamische Werte wie today, yesterday, this week, last week, last month oder last year anzuzeigen. Diese Werte werden dynamisch aktualisiert.

Verwenden von Abfrageparametern in Dashboards

Optional können Abfragen Parameter oder statische Werte nutzen. Wenn eine Visualisierung, die auf einer parametrisierten Abfrage basiert, einem Dashboard hinzugefügt wird, kann die Visualisierung für die Nutzung folgender Elemente konfiguriert werden:

• Widgetparameter

  Widgetparameter sind für eine einzelne Visualisierung auf einem Dashboards spezifisch und werden im Visualisierungsbereich angezeigt. Die angegebenen Parameterwerte gelten nur für die der Visualisierung zugrunde liegende Abfrage.

• Dashboardparameter

  Dashboardparameter können auf mehrere Visualisierungen angewendet werden. Wenn Sie einem Dashboard eine Visualisierung basierend auf einer parametrisierten Abfrage hinzufügen, wird der Parameter standardmäßig als Dashboardparameter hinzugefügt. Dashboardparameter werden für eine oder mehrere Visualisierungen auf einem Dashboards konfiguriert und am oberen Rand des Dashboards angezeigt. Die für einen Dashboardparameter angegebenen Parameterwerte gelten für Visualisierungen, die diesen bestimmten Dashboardparameter wiederverwenden. Ein Dashboard kann mehrere Parameter aufweisen, von denen jeder für einige Visualisierungen und nicht für andere gilt.

• Statischer Wert

  Statische Werte werden anstelle eines Parameters verwendet, der auf Änderungen reagiert. Statische Werte ermöglichen das Hartcodieren eines Werts anstelle eines Parameters. Sie lassen den Parameter aus dem Dashboard oder Widget „verschwinden“, in dem er zuvor angezeigt wurde.

Wenn Sie eine Visualisierung hinzufügen, die eine parametrisierte Abfrage enthält, können Sie den Titel und die Quelle für den Parameter in der Visualisierungsabfrage auswählen, indem Sie auf das entsprechende Bleistiftsymbol klicken. Sie können auch das Schlüsselwort und einen Standardwert auswählen. Weitere Informationen finden Sie unter Parametereigenschaften.

Nachdem Sie eine Visualisierung zu einem Dashboard hinzugefügt haben, können Sie auf die Schnittstelle für die Parameterzuordnung zugreifen, indem Sie auf das Kebab-Menü oben rechts in einem Dashboard-Widget und dann auf Widget-Einstellungen ändern klicken.

Parametereigenschaften

• Titel: Der Anzeigename, der neben der Wertauswahl auf Ihrem Dashboard angezeigt wird. Standardmäßig wird der Parameter Schlüsselwort verwendet. Um dies zu bearbeiten, klicken Sie auf das Bleistiftsymbol . Titel werden für statische Dashboardparameter nicht angezeigt, da die Wertauswahl ausgeblendet ist. Wenn Sie Statischer Wert als Wertquelle auswählen, wird das Feld Titel abgeblendet dargestellt.

• Schlüsselwort: Das Zeichenfolgenliteral für diesen Parameter in der zugrunde liegenden Abfrage. Dies ist nützlich für das Debuggen, wenn Ihr Dashboard nicht die erwarteten Ergebnisse liefert.

• Standardwert: Der Wert, der verwendet wird, wenn kein anderer Wert angegeben wird. Um dies auf dem Abfragebildschirm zu ändern, führen Sie die Abfrage mit dem gewünschten Parameterwert aus, und klicken Sie auf die Schaltfläche Speichern.

• Wertquelle: Die Quelle des Parameterwerts. Um eine Quelle auszuwählen, klicken Sie auf das Bleistiftsymbol .

  • Neuer Dashboard-Parameter: Erstellt einen neuen Parameter auf Dashboard-Ebene. So können Sie einen Parameterwert an einer Stelle Ihres Dashboards festlegen und ihn mehreren Visualisierungen zuordnen.
  • Vorhandener Dashboardparameter: Ordnen Sie den Parameter einem vorhandenen Dashboardparameter zu. Sie müssen den bereits vorhandenen Dashboardparameter angeben.
  • Widgetparameter: Zeigt eine Wertauswahl in Ihrem Dashboard-Widget an. Dies ist nützlich für einmalige Parameter, die nicht von mehreren Widgets gemeinsam genutzt werden.
  • Statischer Wert: Wählen Sie unabhängig von den Werten, die in anderen Widgets verwendet werden, einen statischen Wert für das Widget aus. Statisch zugeordnete Parameterwerte zeigen keine Wertauswahl auf dem Dashboard an und sind somit kompakter. So können Sie die Flexibilität von Abfrageparametern nutzen, ohne die Benutzeroberfläche eines Dashboards zu überladen, wenn nicht zu erwarten ist, dass sich bestimmte Parameter häufig ändern.

  

Häufig gestellte Fragen (FAQ)

• Kann ich denselben Parameter mehrmals in einer einzelnen Abfrage wiederverwenden?
• Kann ich mehrere Parameter in einer einzelnen Abfrage verwenden?

Kann ich denselben Parameter mehrmals in einer einzelnen Abfrage wiederverwenden?

Ja. Verwenden Sie den gleichen Bezeichner in den geschweiften Klammern. In diesem Beispiel wird der {{org_id}}-Parameter zweimal verwendet.

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

Kann ich mehrere Parameter in einer einzelnen Abfrage verwenden?

Ja. Verwenden Sie für jeden Parameter einen eindeutigen Namen. In diesem Beispiel werden zwei Parameter verwendet: {{org_id}} und {{start_date}}.

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