Mit Abfrageparametern arbeiten

Auf dieser Seite wird erläutert, wie Sie mit Abfrageparametern im SQL-Editor von Azure Databricks arbeiten.

Mit Abfrageparametern können Sie Ihre Abfragen dynamischer und flexibler gestalten, indem Sie zur Runtime variable Werte einfügen. Anstatt bestimmte Werte fest in Ihre Abfragen zu codieren, können Sie Parameter definieren, um Daten zu filtern oder die Ausgabe auf der Grundlage von Benutzereingaben zu ändern. Dieser Ansatz verbessert die Wiederverwendung von Abfragen, erhöht die Sicherheit durch die Verhinderung von SQL-Injektionen und ermöglicht eine effizientere Handhabung verschiedener Datenszenarien.

Syntax der Markierung für benannte Parameter

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 (neu und legacy)
• Notebooks
• AI/BI Dashboard Datensatz-Editor
• AI/BI Genie-Bereiche

Fügen Sie Parameter in Ihre SQL-Abfragen ein, indem Sie einen Doppelpunkt gefolgt von einem Parameternamen eingeben, z. B. :parameter_name. Wenn Sie eine benannte Parametermarkierung in eine Abfrage einfügen, erscheint ein Widget in der Benutzeroberfläche. Sie können das Widget verwenden, um den Parametertyp und den Namen zu bearbeiten.

Eine benannte Parametermarkierung zu einer Abfrage hinzufügen

Dieses Beispiel fügt der folgenden Abfrage eine Parametermarkierung hinzu:

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

Diese Abfrage gibt einen Datensatz zurück, der nur Beträge unter fünf Dollar enthält. Führen Sie die folgenden Schritte aus, um die Abfrage so zu bearbeiten, dass ein Parameter anstelle des fest codierten Wertes (5) 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 Ihrer aktualisierten Abfrage sollte lauten fare_amount < :fare_parameter.
3. Klicken Sie auf das Zahnradsymbol neben dem Parameter-Widget. Das Dialogfeld enthält die folgenden Bereiche:
   • Schlüsselwort: Das Schlüsselwort, das den Parameter in der Abfrage darstellt. Sie können dieses Feld nicht bearbeiten. Um das Schlüsselwort zu ändern, bearbeiten Sie die Markierung in der SQL-Abfrage.
   • Titel: Der Titel, der über dem Widget angezeigt wird. Standardmäßig entspricht der Titel dem Schlüsselwort.
   • Typ: Unterstützte Typen sind Text, Zahl, Dropdown-Liste, Datum, Datum und Uhrzeit sowie Datum und Uhrzeit (mit Sekunden). Der Standardwert ist „Text“.
4. Ändern Sie im Dialogfeld den Typ in Nummer.
5. Geben Sie eine Zahl in das Parameter-Widget ein und klicken Sie auf Änderungen übernehmen.
6. Klicken Sie auf Speichern , um die Abfrage zu speichern.

Beispiele für die Syntax benannter 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 einen Zahlenparameter, der die Ergebnisse auf Datensätze beschränkt, in denen das o_total_price-Feld größer ist als der angegebene Parameterwert.

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)

Siehe 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-Strings

Sie können Parameter verwenden, um ein Attribut aus einem JSON-String zu extrahieren. Das folgende Beispiel verwendet die from_json Funktion, um den JSON-String in einen struct-Wert zu konvertieren. Wenn Sie die Zeichenfolge a als Wert für den Parameter (param) eingeben, erhalten Sie das Attribut 1.

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

Ein Intervall erstellen

Der Typ INTERVAL stellt eine Zeitspanne dar und ermöglicht es Ihnen, zeitbasierte Arithmetik und Operationen durchzuführen. Im folgenden Beispiel wird eine CAST Funktion verwendet, um den Parameter als Intervalltyp zu umwandeln. Der resultierende INTERVAL-Wert kann für zeitbasierte Berechnungen oder zum Filtern in der Abfrage verwendet werden.

Siehe INTERVAL-Typ für vollständige Details und Syntax.

SELECT CAST(:param AS INTERVAL MINUTE)

Einen Datumsbereich hinzufügen

Die folgenden Beispiele veranschaulichen das Filtern nach Datensätzen innerhalb eines Datumsbereichs. Datumsparameter können die Attribute .min und .max verwenden, um einen Bereich anzugeben. Sie können einen Bereich auch mit zwei verschiedenen Datumsparametern angeben.

Verwenden von Min- und Max-Attributen

1. Wählen Sie einen Parameternamen aus. Verwenden Sie .min, um auf den Minimalbereichswert zuzugreifen, und .max, um auf den Maximalbereichswert zuzugreifen, wie in der folgenden Abfrage:

   SELECT * FROM samples.nyctaxi.trips
   WHERE tpep_pickup_datetime
   BETWEEN :date_range.min AND :date_range.max
   

2. Legen Sie den Parametertyp auf Date.

3. Legen Sie den Widget-Typ auf Bereich fest.

Verwenden von zwei Datumsparametern

Im folgenden Beispiel werden zwei Parameter start_date und end_datezum Festlegen eines Datumsbereichs verwendet.

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

Rollups nach Tag, Monat oder Jahr parametrisieren

Das folgende Beispiel aggregiert Taxifahrtdaten auf einer parametrisierten Granularitätsebene. Die Funktion DATE_TRUNC reduziert den Wert tpep_pickup_datetime auf der Grundlage des Parameterwerts :date_granularity, wie z. B. DAY, MONTH oder YEAR. Das verkürzte Datum wird als date_rollup aliasiert und in der 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

Mehrere Werte in einer einzigen Abfrage verwenden

Das folgende Beispiel verwendet die Funktion ARRAY_CONTAINS, um eine Liste von Werten zu filtern. Die Funktionen TRANSFORM und SPLIT erlauben die Übergabe mehrerer, durch Komma getrennter Werte als String-Parameter.

Der Wert :list_parameter nimmt eine Liste von durch Komma getrennten Werten an. Die Funktion SPLIT analysiert diese Liste und teilt die durch Kommas getrennten Werte in ein Array auf. Die Funktion TRANSFORM wandelt jedes Element des Arrays um, indem es Leerzeichen entfernt. Die Funktion ARRAY_CONTAINS überprüft, ob der Wert dropoff_zip aus der Tabelle trips in dem als list_parameter übergebenen Array von Werten enthalten ist.

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

Änderungen der Syntax

Die folgende Tabelle zeigt häufige Anwendungsfälle für Parameter, die ursprüngliche Databricks-SQL-Mustache-Syntax und die entsprechende Syntax unter Verwendung der benannten Parametermarker-Syntax.

Syntax der Mustache-Parameter

Im SQL-Editor wird jede Zeichenkette 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 generell die Verwendung von benannten Parametermarkern, aber einige Funktionen werden nur mit der Mustache-Parametersyntax unterstützt.

Verwenden Sie die Mustache-Parametersyntax für die folgenden Funktionen:

• Veraltete Dashboard-Filter
• Dropdown-Parameter mit mehreren Werten
• 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 nicht der Besitzer der Abfrage sind, 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
• Zahl
• Dropdown-Liste
• Abfragebasierte Dropdownliste
• 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 }}

Zahl

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. Sie müssen keine Anführungszeichen um den Parameter hinzufügen, wenn Sie Anführungszeichen auswählen.

Ä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. Zum 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 es Ihnen, einen Wert anstelle eines Parameters fest zu codieren. Sie sorgen dafür, dass der Parameter aus dem Dashboard oder Widget verschwindet, 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 Stiftsymbol 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}}'