Bindungsvariablen¶

Überblick über die Bindungsvariablen¶

Mit Bindungsvariablen ersetzen Sie in SQL-Anweisungen Literale durch Platzhalter. Die folgende SQL-Anweisung verwendet beispielsweise Literale für die eingefügten Werte:

INSERT INTO t (c1, c2) VALUES (1, 'Test string');

Die folgende SQL-Anweisung verwendet Platzhalter für die eingefügten Werte:

INSERT INTO t (c1, c2) VALUES (?, ?);

Ihr Anwendungscode bindet mit jedem Platzhalter Daten in der SQL-Anweisung. Die jeweilige Technik zum Binden von Daten mit Platzhaltern hängt von der Programmiersprache ab. Auch die Syntax des Platzhalters variiert je nach Programmiersprache. Es ist entweder ?, :varname oder %varname.

Bindungsvariablen in gespeicherten Prozeduren in JavaScript verwenden¶

Sie können JavaScript verwenden, um gespeicherte Prozeduren zu erstellen, die SQL ausführen.

Um Bindungsvariablen in JavaScript anzugeben, verwenden Sie ? Platzhalter. Die folgende INSERT-Anweisung gibt zum Beispiel Bindungsvariablen für die in eine Tabellenzeile eingefügten Werte an:

INSERT INTO t (col1, col2) VALUES (?, ?)

In JavaScript können Sie Bindungsvariablen für die Werte in den meisten SQL-Anweisungen verwenden. Informationen über Einschränkungen finden Sie unter Einschränkungen für Bindungsvariablen.

Weitere Informationen zur Verwendung von Bindungsvariablen in JavaScript finden Sie unter Binden von Variablen.

Verwenden von Bindungsvariablen mit Snowflake Scripting¶

Sie können Snowflake Scripting verwenden, um prozeduralen Code zu erstellen, der SQL ausführt, wie Codeblöcke und gespeicherte Prozeduren. Um Bindungsvariablen in Snowflake Scripting-Code anzugeben, stellen Sie dem Variablennamen einen Doppelpunkt voran. Die folgende INSERT-Anweisung gibt zum Beispiel eine Bindungsvariable mit dem Namen variable1 an:

INSERT INTO t (c1) VALUES (:variable1)

Wenn Sie SQL in einem EXECUTE IMMEDIATE-Befehl oder einem OPEN-Befehl für einen Cursor ausführen, können Sie Variablen mit der USING-Klausel binden.

Dieses Beispiel bindet Variablen in einem EXECUTE IMMEDIATE-Befehl mit einer USING-Klausel:

EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price);

Das vollständige Beispiel, das diesen Code enthält, finden Sie unter Ausführen einer Anweisung, die Bindungsvariablen enthält.

Wenn Sie einen Cursor deklarieren, können Sie Bindungsparameter (?-Zeichen) in einer SELECT-Anweisung angeben. Sie können diese Parameter dann an Variablen in der USING-Klausel binden, wenn Sie den Cursor öffnen.

Im folgenden Beispiel wird ein Cursor deklariert, und es werden Bindungsparameter angegeben. Anschließend wird der Cursor mit der USING-Klausel geöffnet:

LET c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
OPEN c1 USING (minimum_price, maximum_price);

Snowflake Scripting unterstützt auch die Nummerierung von Bindungsvariablen nach Position und die Wiederverwendung einer Bindungsvariable in einer SQL-Anweisung. Bei nummerierten Bindungsvariablen wird jeder Variablendeklaration ein Index zugewiesen, und Sie können mit :n auf die n-te deklarierte Variable verweisen. Der folgende Snowflake Scripting-Block gibt zum Beispiel die Bindungsvariable :1 für die Variable i und :2 für die Variable v an und verwendet die Bindungsvariable :1 in einer SQL-Anweisung erneut:

EXECUTE IMMEDIATE $$
DECLARE
  i INTEGER DEFAULT 1;
  v VARCHAR DEFAULT 'SnowFlake';
  r RESULTSET;
BEGIN
  CREATE OR REPLACE TABLE snowflake_scripting_bind_demo (id INTEGER, value VARCHAR);
  EXECUTE IMMEDIATE 'INSERT INTO snowflake_scripting_bind_demo (id, value)
    SELECT :1, (:2 || :1)' USING (i, v);
  r := (SELECT * FROM snowflake_scripting_bind_demo);
  RETURN TABLE(r);
END;
$$
;

+----+------------+
| ID | VALUE      |
|----+------------|
|  1 | SnowFlake1 |
+----+------------+

In Snowflake Scripting Code können Sie Bindungsvariablen für die Werte in den meisten SQL-Anweisungen verwenden. Informationen über Einschränkungen finden Sie unter Einschränkungen für Bindungsvariablen.

Weitere Informationen zur Verwendung von Bindungsvariablen in Snowflake Scripting finden Sie unter Verwenden einer Variablen in einer SQL-Anweisung (Bindung) und Verwenden eines Arguments in einer SQL-Anweisung (Bindung).

Verwenden von Bindungsvariablen mit der SQL-API¶

Sie können die Snowflake-SQL-API verwenden, um auf Daten in einer Snowflake-Datenbank zuzugreifen und diese Daten zu aktualisieren. Sie können Anwendungen erstellen, die die SQL-API verwenden, um SQL-Anweisungen zu übermitteln und Bereitstellungen zu verwalten.

Wenn Sie eine Anforderung übermitteln, die eine SQL-Anweisung ausführt, können Sie für Werte in der Anweisung Bindungsvariablen verwenden. Weitere Informationen dazu finden Sie unter Verwenden von Bindungsvariablen in einer Anweisung.

Verwenden von Bindungsvariablen mit Treibern¶

Mit Snowflake-Treibern können Sie Anwendungen schreiben, die Operationen in Snowflake ausführen. Die Treiber unterstützen Programmiersprachen wie Go, Java und Python. Weitere Informationen zur Verwendung von Bindungsvariablen in einer Anwendung für einen bestimmten Treiber finden Sie unter dem Link des jeweiligen Treibers:

• Go

• JDBC

• .NET

• Node.js

• ODBC

• Python

Verwenden von Bindungsvariablen mit Arrays von Werten¶

Sie können ein Array von Werten an Variablen in SQL-Anweisungen binden. Mit dieser Technik können Sie die Performance verbessern, indem Sie mehrere Zeilen in einen einzigen Batch einfügen, wodurch Netzwerk-Roundtrips und Kompilierungen vermieden werden. Die Verwendung einer Array-Bindung wird auch als „Masseneinfügen“ (Bulk Insert) oder „Batcheinfügung“ (Batch Insert) bezeichnet.

Im Folgenden finden Sie ein Beispiel für eine Array-Bindung in Python-Code:

conn = snowflake.connector.connect( ... )
rows_to_insert = [('milk', 2), ('apple', 3), ('egg', 2)]
conn.cursor().executemany(
            "insert into grocery (item, quantity) values (?, ?)",
            rows_to_insert)

Im diesem Beispiel wird die folgende Bindungsliste angegeben: [('milk', 2), ('apple', 3), ('egg', 2)]. Die Art und Weise, wie eine Anwendung eine Bindungsliste angibt, hängt von der Programmiersprache ab.

Dieser Code fügt drei Zeilen in die Tabelle ein:

+-------+----+
| C1    | C2 |
|-------+----|
| milk  |  2 |
| apple |  3 |
| egg   |  2 |
+-------+----+

Weitere Informationen zur Verwendung von Array-Bindungen in einer Anwendung für einen bestimmten Treiber finden Sie unter dem Link des jeweiligen Treibers:

• Go

• JDBC

• .NET

• Node.js

• ODBC

• Python

INSERT INTO t VALUES (?,?), (?,?);

Verwenden von Bindungsvariablen mit semistrukturierten Daten¶

Um Variablen mit semistrukturierten Daten zu binden, binden Sie die Variable als Zeichenfolgetyp (String) und verwenden Funktionen wie PARSE_JSON oder ARRAY_CONSTRUCT.

Im folgenden Beispiel wird eine Tabelle mit einer VARIANT-Spalte erstellt und dann die Funktion PARSE_JSON aufgerufen, um semistrukturierte Daten mit einer Bindungsvariablen in die Tabelle einzufügen:

CREATE TABLE t (a VARIANT);
-- Code that supplies a bind value for ? of '{'a': 'abc', 'x': 'xyz'}'
INSERT INTO t SELECT PARSE_JSON(a) FROM VALUES (?);

Im folgenden Beispiel wird die Tabelle abgefragt:

SELECT * FROM t;

Die Abfrage gibt die folgende Ausgabe zurück:

+---------------+
| A             |
|---------------|
| {             |
|   "a": "abc", |
|   "x": "xyz"  |
| }             |
+---------------+

Die folgende Anweisung ruft die Funktion ARRAY_CONSTRUCT auf, um ein Array von semistrukturierten Daten mittels einer Bindungsvariablen in eine VARIANT-Spalte einzufügen:

INSERT INTO t SELECT ARRAY_CONSTRUCT(column1) FROM VALUES (?);

In beiden Beispiele kann eine einzelne Zeile eingefügt werden, oder es kann eine Array-Bindung verwendet werden, um mehrere Zeilen im Batch einzufügen. Sie können diese Technik verwenden, um jede Art von semistrukturierten Daten einzufügen, die in einer VARIANT-Spalte gültig ist.

Abrufen der Werte der Bindungsvariablen¶

Um die Werte von Bindungsvariablen in einer ausgeführten Abfrage abzurufen, können Sie die Tabellenfunktion BIND_VALUES im Schema INFORMATION_SCHEMA verwenden. Mit dieser Funktion können Sie die Werte von Bindungsvariablen aus jedem Code abrufen, der Bindungsvariablen unterstützt, einschließlich Javascript- und Snowflake Scripting-Code.

Sie können auf diese Bindungsvariablenwerte auch über die bind_values-Spalte in der Ausgabe für die QUERY_HISTORY Account Usage-Ansicht, die QUERY_HISTORY Organization Usage-Ansicht oder die QUERY_HISTORY-Funktion zugreifen.

Um Werte für Bindungsvariablen mit einer dieser Methoden abrufen , müssen Sie den ALLOW_BIND_VALUES_ACCESS-Parameter auf Kontoebene auf TRUE setzen.

Sie können die Werte von Bindungsvariablen für die folgenden Fälle abrufen:

• Problembehandlung bei Abfragen – Wenn Sie die genauen, in Abfragen verwendeten Bindungswerte kennen, ist es einfacher, die Abfragen zu optimieren und die folgenden Arten von Problemen zu debuggen:

  • Eine Abfrage kann nicht ausgeführt werden.

  • Die Performance einer Abfrage ist schlecht.

  • Eine Abfrage verwendet keine Caches oder erwarteten Ausführungspläne.

• Abfragen zu Testzwecken neu erstellen – Entwicklungsteams und DBAs können benutzergenerierte Abfragen mit Werten von Bindungsvariablen neu erstellen, um Probleme zu replizieren und Stresstests durchzuführen.

• Audits und Compliance – Aus Sicherheits- und Compliance-Gründen müssen Organisationen die Daten prüfen, auf die Benutzende zugreifen. Sie können die Werte von Bindungsvariablen verwenden, um die genauen Daten zu ermitteln, die von Benutzenden abgerufen werden.

SELECT * FROM TABLE(
  INFORMATION_SCHEMA.BIND_VALUES('<query_id_value>'));

SELECT bind_values
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE query_id = '<query_id_value>';

DECLARE
  name STRING;
  temperature FLOAT;
  res RESULTSET;
BEGIN
  name := 'Snowman';
  temperature := -20.14;
  res := (
    SELECT
      CONCAT('Hello ', :NAME, '!') as greeting,
      CONCAT('It is ', :TEMPERATURE, 'deg C today.') as weather
  );
  RETURN LAST_QUERY_ID();
END;

EXECUTE IMMEDIATE
$$
DECLARE
  name STRING;
  temperature FLOAT;
  res RESULTSET;
BEGIN
  name := 'Snowman';
  temperature := -20.14;
  res := (
    SELECT
      CONCAT('Hello ', :NAME, '!') as greeting,
      CONCAT('It is ', :TEMPERATURE, 'deg C today.') as weather
  );
  RETURN LAST_QUERY_ID();
END;
$$
;

+--------------------------------------+
| anonymous block                      |
|--------------------------------------|
| 01bbe3d6-0109-0863-0000-a99502ffa062 |
+--------------------------------------+

SELECT * FROM TABLE(
  INFORMATION_SCHEMA.BIND_VALUES('01bbe3d6-0109-0863-0000-a99502ffa062'));

+--------------------------------------+----------+-------------+------+---------+
| QUERY_ID                             | POSITION | NAME        | TYPE | VALUE   |
|--------------------------------------+----------+-------------+------+---------|
| 01bbe3d6-0109-0863-0000-a99502ffa062 |     NULL | TEMPERATURE | REAL | -20.14  |
| 01bbe3d6-0109-0863-0000-a99502ffa062 |     NULL | NAME        | TEXT | Snowman |
+--------------------------------------+----------+-------------+------+---------+

cursor = conn.cursor()
print(cursor.execute(
          """
          SELECT
              CONCAT('Hello ', ?, '!') as greeting,
              CONCAT('It is ', ?, 'deg C today.') as weather
          """,
          params=["Snowman", -20.14],
      ).fetch_pandas_all())

query_id = cursor.sfqid
print(f"Bind values for query {query_id} are:")
print(cursor.execute("SELECT * FROM TABLE(INFORMATION_SCHEMA.BIND_VALUES(?))", params=[query_id]).fetch_pandas_all())

        GREETING                   WEATHER
0  Hello Snowman!  It is -20.14deg C today.

Bind values for query 01bbe918-0200-0001-0000-000000101145 are:

                               QUERY_ID POSITION  NAME  TYPE    VALUE
0  01bbe918-0200-0001-0000-000000101145        1  None  TEXT  Snowman
1  01bbe918-0200-0001-0000-000000101145        2  None  REAL   -20.14

Einschränkungen für Bindungsvariablen¶

Die folgenden Einschränkungen gelten für Bindungsvariablen:

• Einschränkungen für SELECT-Anweisungen:

  • Bindungsvariablen können keine Zahlen ersetzen, die Teil einer Datentypdefinition (z. B. NUMBER(?)) oder Sortierungsspezifikation (z. B. COLLATE ?) sind.

  • Bindungsvariablen können nicht für die Quelle in einer SELECT-Anweisung verwendet werden, die Dateien in einem Stagingbereich abfragt.

• Einschränkungen für DDL-Befehle:

  • Bindungsvariablen können in den folgenden DDL-Befehlen nicht verwendet werden:

    • CREATE/ALTER INTEGRATION

    • CREATE/ALTER REPLICATION GROUP

    • CREATE/ALTER PIPE

    • CREATE TABLE … USING TEMPLATE

  • Bindungsvariablen können in den folgenden Klauseln nicht verwendet werden:

    • ALTER COLUMN

    • COMMENT ON CONSTRAINT

  • In CREATE/ALTER-Befehlen können Bindungsvariablen nicht für die Werte der folgenden Parameter verwendet werden:

    • CREDENTIALS

    • DIRECTORY

    • ENCRYPTION

    • IMPORTS

    • PACKAGES

    • REFRESH

    • TAG

    • Parameter, die spezifisch für externe Tabellen sind

  • Bindungsvariablen können nicht für Eigenschaften verwendet werden, die Teil eines FILE FORMAT-Wertes sind.

• In COPY INTO-Befehlen können Bindungsvariablen nicht für die Werte der folgenden Parameter verwendet werden:

  • CREDENTIALS

  • ENCRYPTION

  • FILE_FORMAT

• In SHOW-Befehlen können Bindungsvariablen nicht im Parameter STARTS WITH verwendet werden.

• Bindungsvariablen können nicht in einem EXECUTE IMMEDIATE FROM-Befehl verwendet werden.

• Die Werte von Bindungsvariablen können nicht automatisch von einem Datentyp in einen anderen konvertiert werden, wenn Bindungsvariablen verwendet werden in:

  • Snowflake Scripting-Code, der den Datentyp explizit angibt

  • DDL-Anweisungen

  • Namen von Stagingbereichen

Sicherheitserwägungen für Bindungsvariablen¶

Bindungsvariablen maskieren nicht in allen Fällen sensible Daten. Die Werte von Bindungsvariablen können zum Beispiel in Fehlermeldungen und anderen Artefakten erscheinen.

Bindungsvariablen können helfen, Angriffe durch Einschleusen von SQL-Befehlen zu verhindern, wenn Sie SQL-Anweisungen anhand von Benutzereingaben konstruieren. Bindungsvariablen können jedoch potenzielle Sicherheitsrisiken darstellen. Wenn Eingaben für SQL-Anweisungen aus externen Quellen stammen, stellen Sie sicher, dass diese validiert sind. Weitere Informationen dazu finden Sie unter Einschleusung von SQL-Befehlen.