Skriptanweisungen

Mit Skripts können Sie in einer Anfrage mehrere Anweisungen an BigQuery senden. Skripts können Variablen verwenden und Ablaufanweisungen wie IF und WHILE steuern. Damit haben Sie beispielsweise die Möglichkeit, eine Variable zu deklarieren, dieser einen Wert zuzuweisen und darauf in einer weiteren Anweisung zu verweisen.

Weitere Informationen zu Variablen und temporären Tabellenregeln sowie zu Debugging- und Schreibverfahren für Skripts finden Sie unter Skripts und gespeicherte Prozeduren.

DECLARE

DECLARE variable_name[, ...] [variable_type] [DEFAULT expression];

variable_name muss eine gültige Kennzeichnung und variable_type kann ein beliebiger BigQuery-Typ sein.

Beschreibung

Deklariert eine Variable des angegebenen Typs. Wenn die Klausel DEFAULT angegeben ist, wird die Variable mit dem Wert des Ausdrucks initialisiert. Wenn keine DEFAULT-Klausel vorhanden ist, wird die Variable mit dem Wert NULL initialisiert.

Wenn [variable_type] weggelassen wird, muss eine DEFAULT-Klausel angegeben werden. Der Typ der Variablen wird dann vom Typ des Ausdrucks in der DEFAULT-Klausel abgeleitet.

Variablendeklarationen müssen am Anfang eines Skripts vor allen anderen Anweisungen oder am Anfang eines mit BEGIN deklarierten Blocks stehen. Bei Variablennamen wird nicht zwischen Groß- und Kleinschreibung unterschieden.

In einer DECLARE-Anweisung können mehrere Variablennamen enthalten sein, aber jeweils nur ein Wert für variable_type und expression.

Eine Variable darf nicht mit dem gleichen Namen deklariert werden wie eine zuvor im aktuellen Block oder oder in einem enthaltenden Block deklarierte Variable.

Wenn die DEFAULT-Klausel vorhanden ist, muss der Wert des Ausdrucks zwingend dem angegebenen Typ entsprechen. Der Ausdruck kann auf andere Variablen verweisen, die zuvor innerhalb desselben Blocks oder in einem enthaltenden Block deklariert wurden.

Die maximale Größe einer Variablen beträgt 1 MB. Die Größe aller in einem Skript verwendeten Variablen darf 10 MB nicht übersteigen.

Beispiele

Im folgenden Beispiel wird die Variable x als INT64-Typ mit dem Wert NULL initialisiert.

DECLARE x INT64;

Im folgenden Beispiel wird die Variable d als DATE-Typ mit dem Wert des aktuellen Datums initialisiert.

DECLARE d DATE DEFAULT CURRENT_DATE();

Im folgenden Beispiel werden die Variablen x, y und z als INT64-Typ mit dem Wert 0 initialisiert.

DECLARE x, y, z INT64 DEFAULT 0;

Hier ein Beispiel, in dem eine Variable namens item deklariert wird, die einem beliebigen Element in der Tabelle dataset1.products entspricht: Der Typ von item wird aus dem Tabellenschema abgeleitet.

DECLARE item DEFAULT (SELECT item FROM dataset1.products LIMIT 1);

SET

Syntax

SET name = expression;
SET (variable_name_1, variable_name_2, …, variable_name_n) =
  (expression_1, expression_2, …, expression_n);

Beschreibung

Legt fest, dass eine Variable den Wert des angegebenen Ausdrucks haben soll, oder legt auf der Grundlage des Ergebnisses mehrerer Ausdrücke mehrere Variablen gleichzeitig fest.

Die Anweisung SET kann an einer beliebigen Stelle im Text eines Skripts stehen.

Beispiele

Im folgenden Beispiel wird für die Variable x der Wert 5 festgelegt.

SET x = 5;

Im folgenden Beispiel wird für die Variable a der Wert 4, für b der Wert "foo" und für die Variable c der Wert false festgelegt.

SET (a, b, c) = (1 + 3, 'foo', false);

Im folgenden Beispiel wird das Ergebnis einer Abfrage mehreren Variablen zugewiesen. Zuerst werden zwei Variablen deklariert: target_word und corpus_count. Als Nächstes werden dann den beiden Variablen die Ergebnisse einer SELECT AS STRUCT-Abfrage zugewiesen. Das Ergebnis der Abfrage ist eine einzelne Zeile, die ein STRUCT mit zwei Feldern enthält. Das erste Element wird dabei der ersten und das zweite der zweiten Variablen zugewiesen.

DECLARE target_word STRING DEFAULT 'methinks';
DECLARE corpus_count, word_count INT64;

SET (corpus_count, word_count) = (
  SELECT AS STRUCT COUNT(DISTINCT corpus), SUM(word_count)
  FROM `bigquery-public-data`.samples.shakespeare
  WHERE LOWER(word) = target_word
);

SELECT
  FORMAT('Found %d occurrences of "%s" across %d Shakespeare works',
         word_count, target_word, corpus_count) AS result;

Diese Anweisungsliste gibt den folgenden String aus:

Found 151 occurrences of "methinks" across 38 Shakespeare works

EXECUTE IMMEDIATE

Syntax

EXECUTE IMMEDIATE sql_expression [ INTO variable[, ...] ] [ USING identifier[, ...] ];

sql_expression:
  { "query_statement" | expression("query_statement") }

identifier:
  { variable | value } [ AS alias ]

Beschreibung

Führt eine dynamische SQL-Anweisung aus.

  • sql_expression: Steht für eine Abfrageanweisung, einen Ausdruck, den Sie für eine Abfrageanweisung verwenden können, eine einzelne DDL-Anweisung oder eine einzelne DML-Anweisung. Kann keine Kontrollanweisung wie IF sein.
  • expression: Kann eine Funktion, ein bedingter Ausdruck oder eine Ausdruck-Unterabfrage sein.
  • query_statement: Steht für eine gültige eigenständige SQL-Anweisung dar, die ausgeführt werden soll. Wenn dabei ein Wert zurückgegeben wird, muss die INTO-Klausel Werte desselben Typs enthalten. Sie können sowohl auf Systemvariablen als auch auf Werte in der USING-Klausel zugreifen. Alle anderen lokalen Variablen und Abfrageparameter werden nicht für die Abfrageanweisung bereitgestellt.
  • INTO-Klausel: Nachdem der SQL-Ausdruck ausgeführt wurde, können Sie die Ergebnisse mithilfe der INTO-Klausel in einer oder mehreren Variablen speichern.
  • USING-Klausel: Bevor Sie den SQL-Ausdruck ausführen, können Sie eine oder mehrere Kennzeichnungen aus der USING-Klausel an den SQL-Ausdruck übergeben. Diese Kennzeichnungen funktionieren ähnlich wie Abfrageparameter; sie stellen Werte für die Abfrageanweisung bereit. Eine Kennzeichnung kann eine Variable oder ein Wert sein.

Sie können diese Platzhalter in das query_statement für Kennzeichnungen aufnehmen, auf die in der USING-Klausel verwiesen wird:

  • ?: Der Wert für diesen Platzhalter ist über den Index an eine Kennzeichnung in der USING-Klausel gebunden.

    -- y = 1 * (3 + 2) = 5
    EXECUTE IMMEDIATE "SELECT ? * (? + 2)" INTO y USING 1, 3;
    
  • @identifier: Der Wert für diesen Platzhalter ist an eine Kennzeichnung in der USING-Klausel gebunden. Diese Syntax ist mit der Syntax der Abfrageparameter identisch.

    -- y = 1 * (3 + 2) = 5
    EXECUTE IMMEDIATE "SELECT @a * (@b + 2)" INTO y USING 1 as a, 3 as b;
    

Im Folgenden finden Sie einige zusätzliche Hinweise zum Verhalten der EXECUTE IMMEDIATE-Anweisung:

  • EXECUTE IMMEDIATE darf nicht dynamisch als verschachteltes Element ausgeführt werden. Das bedeutet, dass EXECUTE IMMEDIATE nicht in einer anderen EXECUTE IMMEDIATE-Anweisung verschachtelt werden darf.
  • Wenn eine EXECUTE IMMEDIATE-Anweisung Ergebnisse zurückgibt, werden diese Ergebnisse zum Ergebnis der gesamten Anweisung und alle entsprechenden Systemvariablen werden aktualisiert.
  • In den Klauseln INTO und USING kann dieselbe Variable vorkommen.
  • query_statement kann eine einzelne geparste Anweisung enthalten, in der sich andere Anweisungen befinden (z. B. BEGIN ... END).
  • Wenn von query_statement null Zeilen zurückgegeben werden (auch aus Wertetabellen mit null Zeilen), werden alle Variablen in der INTO-Klausel auf NULL gesetzt.
  • Wenn von query_statement genau eine Zeile zurückgegeben wird (auch aus Wertetabellen mit null Zeilen), werden die Werte nach der Position und nicht nach dem Variablennamen zugewiesen.
  • Wenn eine INTO-Klausel vorhanden ist, wird ein Fehler ausgegeben, wenn Sie versuchen, mehr als eine Zeile aus query_statement zurückzugeben.

Beispiele

In diesem Beispiel erstellen wir eine Tabelle mit Büchern und füllen sie mit Daten. Beachten Sie die verschiedenen Möglichkeiten, auf Variablen zu verweisen, Werte in Variablen zu speichern und Ausdrücke zu verwenden.

-- create some variables
DECLARE book_name STRING DEFAULT 'Ulysses';
DECLARE book_year INT64 DEFAULT 1922;
DECLARE first_date INT64;

-- Create a temporary table called Books.
EXECUTE IMMEDIATE
  "CREATE TEMP TABLE Books (title STRING, publish_date INT64)";

-- Add a row for Hamlet (less secure)
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES('Hamlet', 1599)";

-- add a row for Ulysses, using the variables declared at the top of this
-- script and the ? placeholder
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(?, ?)"
  USING book_name, book_year;

-- add a row for Emma, using the identifier placeholder
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(@name, @year)"
  USING 1815 as year, "Emma" as name;

-- add a row for Middlemarch, using an expression
EXECUTE IMMEDIATE
  CONCAT("INSERT INTO Books (title, publish_date)", "VALUES('Middlemarch', 1871)");

-- save the publish date of the first book, Hamlet, to a variable called first_date
EXECUTE IMMEDIATE "SELECT publish_date FROM Books LIMIT 1" INTO first_date;

+------------------+------------------+
| title            | publish_date     |
+------------------+------------------+
| Hamlet           | 1599             |
| Ulysses          | 1922             |
| Emma             | 1815             |
| Middlemarch      | 1871             |
+------------------+------------------+

BEGIN...END

Syntax

BEGIN
  sql_statement_list
END;

Beschreibung

BEGIN initialisiert einen Block von Anweisungen, wobei deklarierte Variablen nur bis zur entsprechenden END-Anweisung vorhanden sind. sql_statement_list ist eine Liste von null oder mehr SQL-Anweisungen, die mit Semikolons abgeschlossen sind.

Variablendeklarationen müssen vor anderen Arten von Anweisungen am Anfang des Blocks stehen. Auf in einem Block deklarierte Variablen darf nur innerhalb dieses Blocks und in verschachtelten Blöcken verwiesen werden. Eine Variable darf nicht mit dem gleichen Namen deklariert werden wie eine zuvor im gleichen Block oder oder in einem äußeren Block deklarierte Variable.

Blocks sowie bedingte Anweisungen wie BEGIN/END, IF/ELSE/END IF und WHILE/END WHILE können auf maximal 50 Ebenen verschachtelt werden.

BEGIN/END darf nicht dynamisch als verschachteltes Element ausgeführt werden.

Mit dieser Anweisung können Sie ein Label verwenden. Weitere Informationen finden Sie unter Labels.

Beispiele

Im folgenden Beispiel wird die Variable x mit dem Standardwert 10 deklariert. Dann wird ein Block initiiert, in dem der Variable y der Wert x zugewiesen wird, also 10, und dieser Wert zurückgegeben wird. Als Nächstes beendet die Anweisung END den Block sowie den Bereich der Variablen y. Schließlich wird der Wert x zurückgegeben.

DECLARE x INT64 DEFAULT 10;
BEGIN
  DECLARE y INT64;
  SET y = x;
  SELECT y;
END;
SELECT x;

BEGIN...EXCEPTION...END

Syntax

BEGIN
  sql_statement_list
EXCEPTION WHEN ERROR THEN
  sql_statement_list
END;

Beschreibung

BEGIN...EXCEPTION führt einen Block von Anweisungen aus. Wenn bei einer der Anweisungen ein Fehler auftritt, überspringt das Skript den Rest des Blocks und führt die Anweisungen in der EXCEPTION-Klausel aus.

Innerhalb der EXCEPTION-Klausel können Sie mit den folgenden EXCEPTION-Systemvariablen auf Details zum Fehler zugreifen:

Name Typ Beschreibung
@@error.formatted_stack_trace STRING Der Inhalt von @@error.stack_trace wird als Klartextstring angegeben. Dieser Wert ist für die Anzeige vorgesehen und kann ohne Vorankündigung geändert werden. Beim programmatischen Zugriff auf den Stacktrace eines Fehlers sollte stattdessen @@error.stack_trace verwendet werden.
@@error.message STRING Gibt eine Fehlermeldung im Klartext an.
@@error.stack_trace Siehe 1. Jedes Element des Arrays entspricht einer Anweisung oder einem Prozeduraufruf, der zum Zeitpunkt des Fehlers ausgeführt wird, wobei der aktuell ausgeführte Stapelframe zuerst angezeigt wird. Die Bedeutung der einzelnen Felder wird so definiert:
  • line/column: Gibt die Zeilen- und Spaltennummer des Stapelframes an, beginnend mit 1. Wenn der Frame innerhalb eines Prozedurtexts auftritt, entspricht line 1 column 1 dem Schlüsselwort BEGIN am Anfang des Prozedurtexts.
  • location: Wenn der Frame innerhalb eines Prozedurtexts auftritt, wird der vollständige Name der Prozedur im Format [project_name].[dataset_name].[procedure_name] angegeben. Wenn sich der Frame auf einen Ort in einem Skript auf oberster Ebene bezieht, lautet das Feld NULL.
  • filename: Reserviert für zukünftige Verwendung. Immer NULL.
@@error.statement_text STRING Gibt den Text der Anweisung an, die den Fehler verursacht hat.

1 Der Typ von @@error.stack_trace ist ARRAY<STRUCT<line INT64, column INT64, filename STRING, location STRING>>.

Da BigQuery sich das Recht vorbehält, Fehlermeldungen jederzeit zu überarbeiten, sollten sich Nutzer von @@error.message nicht darauf verlassen, dass Fehlermeldungen gleich bleiben oder einem bestimmten Muster folgen. Extrahieren Sie keinen Text aus der Fehlermeldung, um Informationen zum Fehlerort zu erhalten. Verwenden Sie stattdessen @@error.stack_trace und @@error.statement_text.

Bei Ausnahmen, die von einem Ausnahme-Handler selbst ausgelöst (aber nicht verarbeitet) werden, müssen Sie den Block in einem äußeren Block mit einem separaten Ausnahme-Handler zusammenfassen.

Im folgenden Beispiel wird gezeigt, wie ein äußerer Block mit einem separaten Ausnahme-Handler verwendet wird:

BEGIN
  BEGIN
    ...
  EXCEPTION WHEN ERROR THEN
    SELECT 1/0;
  END;
EXCEPTION WHEN ERROR THEN
  -- The exception thrown from the inner exception handler lands here.
END;

Blöcke vom Typ BEGIN...EXCEPTION unterstützen auch DECLARE-Anweisungen, wie jeder andere BEGIN-Block. In einem BEGIN-Block deklarierte Variablen sind nur im Abschnitt BEGIN gültig und dürfen nicht im Ausnahme-Handler des Blocks verwendet werden.

Mit dieser Anweisung können Sie ein Label verwenden. Weitere Informationen finden Sie unter Labels.

Beispiele

CREATE OR REPLACE PROCEDURE dataset1.proc1() BEGIN
  SELECT 1/0;
END;

CREATE OR REPLACE PROCEDURE dataset1.proc2() BEGIN
  CALL dataset1.proc1();
END;

BEGIN
  CALL dataset1.proc2();
EXCEPTION WHEN ERROR THEN
  SELECT
    @@error.message,
    @@error.stack_trace,
    @@error.statement_text,
    @@error.formatted_stack_trace;
END;

In diesem Beispiel, in dem ein Fehler wegen Division durch null auftritt, beendet BigQuery nicht das gesamte Skript, sondern dataset1.proc1() und dataset1.proc2() und führt die SELECT-Anweisung im Ausnahme-Handler aus. Wenn der Ausnahme-Handler ausgeführt wird, haben die Variablen folgende Werte:

Variable Wert
@@error.message "Query error: division by zero: 1 / 0 at <project>.dataset1.proc1:2:3]"
@@error.stack_trace [
STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.dataset1.proc1:2:3" AS location),
STRUCT(2 AS line, 3 AS column, NULL AS filename, "<project>.dataset1.proc2:2:3" AS location),
STRUCT(10 AS line, 3 AS column, NULL AS filename, NULL AS location),
]
@@error.statement_text "SELECT 1/0"
@@error.formatted_stack_trace "At <project>.dataset1.proc1[2:3]\nAt <project>.dataset1.proc2[2:3]\nAt [10:3]"

CASE

Syntax

CASE
  WHEN boolean_expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

Beschreibung

Führt die erste sql_statement_list aus, für die der boolesche Ausdruck zutrifft, oder die optionale ELSE sql_statement_list, wenn keine der Bedingungen erfüllt ist.

CASE kann maximal 50 verschachtelte Ebenen haben.

CASE darf nicht dynamisch als verschachteltes Element ausgeführt werden. Das bedeutet, dass CASE nicht in einer EXECUTE IMMEDIATE-Anweisung verschachtelt werden darf.

Beispiele

Das folgende Skript sucht nach target_product_ID in der Tabelle products_a. Wenn die ID dort nicht gefunden wird, sucht das Skript nach der ID in der Tabelle products_b. Wenn die ID dort nicht gefunden wird, wird die Anweisung im Block ELSE ausgeführt.

DECLARE target_product_id INT64 DEFAULT 103;
CASE
  WHEN
    EXISTS(SELECT 1 FROM dataset.products_a WHERE product_id = target_product_id)
    THEN SELECT 'found product in products_a table';
  WHEN
    EXISTS(SELECT 1 FROM dataset.products_b WHERE product_id = target_product_id)
    THEN SELECT 'found product in products_b table';
  ELSE
    SELECT 'did not find product';
END CASE;

CASE search_expression

Syntax

CASE search_expression
  WHEN expression THEN sql_statement_list
  [...]
  [ELSE sql_statement_list]
END CASE;

Beschreibung

Führt den ersten sql_statement_list aus, bei dem der Suchausdruck mit einem WHEN-Ausdruck übereinstimmt. Der search_expression wird einmal ausgewertet und dann auf jeden WHEN-Ausdruck auf Gleichheit hin überprüft, bis eine Übereinstimmung gefunden wird. Wenn keine Übereinstimmung gefunden wird, wird der optionale ELSE sql_statement_list ausgeführt.

CASE kann maximal 50 verschachtelte Ebenen haben.

CASE darf nicht dynamisch als verschachteltes Element ausgeführt werden. Das bedeutet, dass CASE nicht in einer EXECUTE IMMEDIATE-Anweisung verschachtelt werden darf.

Beispiele

Im folgenden Beispiel wird die Produkt-ID als Suchausdruck verwendet. Wenn die ID 1 lautet, wird 'Product one' zurückgegeben. Wenn die ID 2 lautet, wird 'Product two' zurückgegeben. Wenn es sich um eine andere ID handelt, wird Invalid product zurückgegeben.

DECLARE product_id INT64 DEFAULT 1;
CASE product_id
  WHEN 1 THEN
    SELECT CONCAT('Product one');
  WHEN 2 THEN
    SELECT CONCAT('Product two');
  ELSE
    SELECT CONCAT('Invalid product');
END CASE;

IF

Syntax

IF condition THEN [sql_statement_list]
  [ELSEIF condition THEN sql_statement_list]
  [...]
  [ELSE sql_statement_list]
END IF;

Beschreibung

Führt die erste sql_statement_list aus, für die die Bedingung zutrifft, oder die optionale ELSE sql_statement_list, wenn keine der Bedingungen erfüllt ist.

Blocks sowie bedingte Anweisungen wie BEGIN/END, IF/ELSE/END IF und WHILE/END WHILE können auf maximal 50 Ebenen verschachtelt werden.

IF darf nicht dynamisch als verschachteltes Element ausgeführt werden. Das bedeutet, dass IF nicht in einer EXECUTE IMMEDIATE-Anweisung verschachtelt werden darf.

Beispiele

Im folgenden Beispiel wird eine INT64-Variable target_product_id mit dem Standardwert 103 deklariert. Anschließend wird geprüft, ob die Tabelle dataset.products eine Zeile mit der product_id-Spalte enthält, deren Wert dem Wert von target_product_id entspricht. Ist dies der Fall, wird ein String ausgegeben, der angibt, dass das Produkt gefunden wurde, zusammen mit dem Wert von default_product_id. Ist dies nicht der Fall, wird in einem String angegeben, dass das Produkt nicht gefunden wurde, ebenfalls mit dem Wert von default_product_id.

DECLARE target_product_id INT64 DEFAULT 103;
IF EXISTS(SELECT 1 FROM dataset.products
           WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product ', CAST(target_product_id AS STRING));
  ELSEIF EXISTS(SELECT 1 FROM dataset.more_products
           WHERE product_id = target_product_id) THEN
  SELECT CONCAT('found product from more_products table',
  CAST(target_product_id AS STRING));
ELSE
  SELECT CONCAT('did not find product ', CAST(target_product_id AS STRING));
END IF;

Label

Syntax

label_name: BEGIN
  block_statement_list
END [label_name];
label_name: LOOP
  loop_statement_list
END LOOP [label_name];
label_name: WHILE condition DO
  loop_statement_list
END WHILE [label_name];
label_name: FOR variable IN query DO
  loop_statement_list
END FOR [label_name];
label_name: REPEAT
  loop_statement_list
  UNTIL boolean_condition
END REPEAT [label_name];
block_statement_list:
  { statement | break_statement_with_label }[, ...]

loop_statement_list:
  { statement | break_continue_statement_with_label }[, ...]

break_statement_with_label:
  { BREAK | LEAVE } label_name;

break_continue_statement_with_label:
  { BREAK | LEAVE | CONTINUE | ITERATE } label_name;

Beschreibung

Eine BREAK- oder CONTINUE-Anweisung mit einem Label stellt einen bedingungsfreien Sprung zum Ende des Blocks oder der Schleife bereit, die mit diesem Label verknüpft ist. Wenn Sie ein Label mit einem Block oder einer Schleife verwenden möchten, muss das Label am Anfang des Blocks oder der Schleife stehen und optional auch am Ende.

  • Ein Labelname kann aus einer beliebigen BigQuery-ID bestehen, einschließlich der Verwendung von Graviszeichen, um reservierte Zeichen oder Suchbegriffe einzubeziehen.
  • Mehrteilige Pfadnamen können verwendet werden, aber nur als Kennungen in Anführungszeichen.

    `foo.bar`: BEGIN ... END -- Works
    foo.bar: BEGIN ... END -- Does not work
    
  • Bei Labelnamen wird nicht zwischen Groß- und Kleinschreibung unterschieden.

  • Jedes gespeicherte Verfahren hat einen unabhängigen Speicher für Labelnamen. Ein Verfahren kann beispielsweise ein Label neu definieren, das bereits in einem Aufrufverfahren verwendet wird.

  • Eine Schleife oder ein Block darf nicht den Labelnamen wiederholen, der in einer einschließenden Schleife oder einem Block verwendet wird.

  • Wiederholte Labelnamen in nicht überlappenden Teilen eines Skripts sind zulässig.

  • Ein Label und eine Variable mit demselben Namen sind zulässig.

  • Wenn die Anweisung BREAK, LEAVE, CONTINUE oder ITERATE ein Label angibt, wird die Schleife beendet oder fortgesetzt, die dem Labelnamen entspricht, anstatt immer die innerste Schleife zu wählen.

Beispiele

Sie können nur auf einen Block oder eine Schleife verweisen, während Sie sich darin befinden.

label_1: BEGIN
  SELECT 1;
  BREAK label_1;
  SELECT 2; -- Unreached
END;
label_1: LOOP
  BREAK label_1;
END LOOP label_1;

WHILE x < 1 DO
  CONTINUE label_1; -- Error
END WHILE;

Wiederholte Labelnamen in nicht überlappenden Teilen des Skripts sind zulässig. Dies funktioniert:

label_1: BEGIN
  BREAK label_1;
END;

label_2: BEGIN
  BREAK label_2;
END;

label_1: BEGIN
  BREAK label_1;
END;

Eine Schleife oder ein Block darf nicht den Labelnamen wiederholen, der in einer einschließenden Schleife oder einem Block verwendet wird. Dies gibt einen Fehler aus:

label_1: BEGIN
   label_1: BEGIN -- Error
     BREAK label_1;
   END;
END;

Ein Label und eine Variable können denselben Namen haben. Dies funktioniert:

label_1: BEGIN
   DECLARE label_1 INT64;
   BREAK label_1;
END;

Das Keyword END, das einen Block oder eine Schleife beendet, kann einen Labelnamen angeben. Dies ist jedoch optional. Diese funktionieren:

label_1: BEGIN
  BREAK label_1;
END label_1;
label_1: BEGIN
  BREAK label_1;
END;

Am Ende eines Blocks oder einer Schleife darf kein Label stehen, wenn es kein Label am Anfang eines Blocks oder einer Schleife gibt. Dies gibt einen Fehler aus:

BEGIN
  BREAK label_1;
END label_1;

In diesem Beispiel sind die Anweisungen BREAK und CONTINUE auf die äußere label_1: LOOP- und nicht auf die innere WHILE x < 1 DO-Schleife ausgerichtet:

label_1: LOOP
  WHILE x < 1 DO
    IF y < 1 THEN
      CONTINUE label_1;
    ELSE
      BREAK label_1;
  END WHILE;
END LOOP label_1

Eine BREAK-, LEAVE-, CONTINUE- oder ITERATE-Anweisung, die ein nicht vorhandenes Label angibt, gibt einen Fehler aus:

WHILE x < 1 DO
  BREAK label_1; -- Error
END WHILE;

Das Beenden eines Blocks aus dem Ausnahme-Handler-Abschnitt heraus ist zulässig:

label_1: BEGIN
  SELECT 1;
  EXCEPTION WHEN ERROR THEN
    BREAK label_1;
    SELECT 2; -- Unreached
END;

CONTINUE kann nicht mit einem Blocklabel verwendet werden. Dies gibt einen Fehler aus:

label_1: BEGIN
  SELECT 1;
  CONTINUE label_1; -- Error
  SELECT 2;
END;

Schleifen

LOOP

Syntax

LOOP
  sql_statement_list
END LOOP;

Beschreibung

Führt sql_statement_list aus, bis eine BREAK- oder LEAVE-Anweisung die Schleife beendet. sql_statement_list ist eine Liste von null oder mehr SQL-Anweisungen, die mit Semikolons abgeschlossen sind.

LOOP darf nicht dynamisch als verschachteltes Element ausgeführt werden. Das bedeutet, dass LOOP nicht in einer EXECUTE IMMEDIATE-Anweisung verschachtelt werden darf.

Mit dieser Anweisung können Sie ein Label verwenden. Weitere Informationen finden Sie unter Labels.

Beispiele

Im folgenden Beispiel wird die Variable x mit dem Standardwert 0 deklariert. Anschließend wird mit der Anweisung LOOP eine Schleife erstellt, die ausgeführt wird, bis die Variable x größer oder gleich 10 ist. Nachdem die Schleife beendet wurde, gibt das Beispiel den Wert von x zurück.

DECLARE x INT64 DEFAULT 0;
LOOP
  SET x = x + 1;
  IF x >= 10 THEN
    LEAVE;
  END IF;
END LOOP;
SELECT x;

In diesem Beispiel wird Folgendes ausgegeben:

+----+
| x  |
+----+
| 10 |
+----+

REPEAT

Syntax

REPEAT
  sql_statement_list
  UNTIL boolean_condition
END REPEAT;

Beschreibung

Führt eine Liste mit null oder mehreren SQL-Anweisungen wiederholt aus, bis die boolesche Bedingung am Ende der Liste TRUE lautet. Die boolesche Bedingung muss ein Ausdruck sein. Sie können diese Schleife frühzeitig mit der Anweisung BREAK oder LEAVE beenden.

REPEAT darf nicht dynamisch als verschachteltes Element ausgeführt werden. Das bedeutet, dass REPEAT nicht in einer EXECUTE IMMEDIATE-Anweisung verschachtelt werden darf.

Mit dieser Anweisung können Sie ein Label verwenden. Weitere Informationen finden Sie unter Labels.

Beispiele

Im folgenden Beispiel wird die Variable x mit dem Standardwert 0 deklariert. Anschließend wird mit der Anweisung REPEAT eine Schleife erstellt, die ausgeführt wird, bis die Variable x größer oder gleich 3 ist.

DECLARE x INT64 DEFAULT 0;

REPEAT
  SET x = x + 1;
  SELECT x;
  UNTIL x >= 3
END REPEAT;

In diesem Beispiel wird Folgendes ausgegeben:

+---+
| x |
+---+
| 1 |
+---+

+---+
| x |
+---+
| 2 |
+---+

+---+
| x |
+---+
| 3 |
+---+

WHILE

Syntax

WHILE boolean_expression DO
  sql_statement_list
END WHILE;

Blocks sowie bedingte Anweisungen wie BEGIN/END, IF/ELSE/END IF und WHILE/END WHILE können auf maximal 50 Ebenen verschachtelt werden.

Beschreibung

Wenn boolean_expression "true" (wahr) ist, wird sql_statement_list ausgeführt. boolean_expression wird für jede Iteration der Schleife ausgewertet.

WHILE darf nicht dynamisch als verschachteltes Element ausgeführt werden. Das bedeutet, dass WHILE nicht in einer EXECUTE IMMEDIATE-Anweisung verschachtelt werden darf.

Mit dieser Anweisung können Sie ein Label verwenden. Weitere Informationen finden Sie unter Labels.

BREAK

Syntax

BREAK;

Beschreibung

Beendet die aktuelle Schleife.

BREAK darf nicht außerhalb einer Schleife verwendet werden.

Mit dieser Anweisung können Sie ein Label verwenden. Weitere Informationen finden Sie unter Labels.

Beispiele

Im folgenden Beispiel werden zuerst zwei Variablen, heads und heads_count, deklariert. Anschließend wird eine Schleife initiiert, die heads einen zufälligen booleschen Wert zuweist und dann prüft, ob heads "true" (wahr) ist. Wenn ja, wird "Heads!" (Kopf) ausgegeben und heads_count erhöht. Ist dies nicht der Fall, wird "Tails!" (Zahl) ausgegeben und die Schleife beendet. Abschließend wird ein String ausgegeben, der angibt, wie oft die Zufallszuweisung zu "Heads" geführt hat.

DECLARE heads BOOL;
DECLARE heads_count INT64 DEFAULT 0;
LOOP
  SET heads = RAND() < 0.5;
  IF heads THEN
    SELECT 'Heads!';
    SET heads_count = heads_count + 1;
  ELSE
    SELECT 'Tails!';
    BREAK;
  END IF;
END LOOP;
SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');

LEAVE

Synonym für BREAK.

WEITER

Syntax

CONTINUE;

Beschreibung

Überspringt die folgenden Anweisungen in der aktuellen Schleife und kehrt zum Anfang der Schleife zurück.

CONTINUE darf nicht außerhalb einer Schleife verwendet werden.

Mit dieser Anweisung können Sie ein Label verwenden. Weitere Informationen finden Sie unter Labels.

Beispiele

Im folgenden Beispiel werden zuerst zwei Variablen, heads und heads_count, deklariert. Anschließend wird eine Schleife initiiert, die heads einen zufälligen booleschen Wert zuweist und dann prüft, ob heads "true" (wahr) ist. Wenn ja, wird "Heads!" (Kopf) ausgegeben, heads_count erhöht und die Schleife bei Überspringen der übrigen Anweisungen neu durchlaufen. Ist dies nicht der Fall, wird "Tails!" (Zahl) ausgegeben und die Schleife beendet. Abschließend wird ein String ausgegeben, der angibt, wie oft die Zufallszuweisung zu "Heads" geführt hat.

DECLARE heads BOOL;
DECLARE heads_count INT64 DEFAULT 0;
LOOP
  SET heads = RAND() < 0.5;
  IF heads THEN
    SELECT 'Heads!';
    SET heads_count = heads_count + 1;
    CONTINUE;
  END IF;
  SELECT 'Tails!';
  BREAK;
END LOOP;
SELECT CONCAT(CAST(heads_count AS STRING), ' heads in a row');

ITERATE

Synonym für CONTINUE.

FOR...IN

Syntax

FOR loop_variable_name IN (table_expression)
DO
  sql_expression_list
END FOR;

Beschreibung

Führt eine Schleife für jede Zeile in table_expression aus und weist die Zeile loop_variable_name zu. In jeder Schleife werden die SQL-Anweisungen in sql_expression_list mit dem aktuellen Wert von loop_variable_name ausgeführt.

Der Wert von table_expression wird zu Beginn der Schleife einmal ausgewertet. Bei jeder Iteration ist der Wert von loop_variable_name ein STRUCT, der die Spalten der obersten Ebene des Tabellenausdrucks als Felder enthält. Die Reihenfolge, in der die Werte loop_variable_name zugewiesen werden, ist nicht definiert, es sei denn, der Tabellenausdruck hat eine übergeordnete ORDER BY-Klausel oder einen UNNEST-Arrayoperator.

Der Bereich von loop_variable_name ist der Text der Schleife. Der Name von loop_variable_name darf nicht mit anderen Variablen im selben Bereich in Konflikt stehen.

Mit dieser Anweisung können Sie ein Label verwenden. Weitere Informationen finden Sie unter Labels.

Beispiel

FOR record IN
  (SELECT word, word_count
   FROM bigquery-public-data.samples.shakespeare
   LIMIT 5)
DO
  SELECT record.word, record.word_count;
END FOR;

Transaktionen

BEGIN TRANSACTION

Syntax

BEGIN [TRANSACTION];

Beschreibung

Startet eine Transaktion.

Die Transaktion endet, wenn eine COMMIT TRANSACTION- oder ROLLBACK TRANSACTION-Anweisung erreicht wird. Wenn das Skript endet, bevor eine dieser Anweisungen erreicht wurde, erfolgt ein automatisches Rollback.

Weitere Informationen zu Transaktionen in BigQuery finden Sie unter Transaktionen mit mehreren Anweisungen.

Beispiel

Im folgenden Beispiel wird eine Transaktion ausgeführt, die Zeilen aus einer vorhandenen Tabelle in eine temporäre Tabelle auswählt, diese Zeilen aus der ursprünglichen Tabelle löscht und die temporäre Tabelle in einer anderen Tabelle zusammenführt.

BEGIN TRANSACTION;

-- Create a temporary table of new arrivals from warehouse #1
CREATE TEMP TABLE tmp AS
SELECT * FROM mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Delete the matching records from the original table.
DELETE mydataset.NewArrivals WHERE warehouse = 'warehouse #1';

-- Merge the matching records into the Inventory table.
MERGE mydataset.Inventory AS I
USING tmp AS T
ON I.product = T.product
WHEN NOT MATCHED THEN
 INSERT(product, quantity, supply_constrained)
 VALUES(product, quantity, false)
WHEN MATCHED THEN
 UPDATE SET quantity = I.quantity + T.quantity;

DROP TABLE tmp;

COMMIT TRANSACTION;

COMMIT TRANSACTION

Syntax

COMMIT [TRANSACTION];

Beschreibung

Führt ein Commit einer offenen Transaktion durch. Wenn keine offene Transaktion läuft, schlägt die Anweisung fehl.

Weitere Informationen zu Transaktionen in BigQuery finden Sie unter Transaktionen mit mehreren Anweisungen.

Beispiel

BEGIN TRANSACTION;

-- SQL statements for the transaction go here.

COMMIT TRANSACTION;

ROLLBACK TRANSACTION

Syntax

ROLLBACK [TRANSACTION];

Beschreibung

Führt ein Rollback einer offenen Transaktion durch. Wenn keine offene Transaktion ausgeführt wird, schlägt die Anweisung fehl.

Weitere Informationen zu Transaktionen in BigQuery finden Sie unter Transaktionen mit mehreren Anweisungen.

Beispiel

Im folgenden Beispiel wird eine Transaktion rückgängig gemacht, wenn während der Transaktion ein Fehler auftritt. Zur Veranschaulichung der Logik wird im Beispiel der Fehler "Split by null" ausgelöst, nachdem eine Zeile in eine Tabelle eingefügt wurde. Nachdem diese Anweisungen ausgeführt wurden, ist die Tabelle wieder unverändert.

BEGIN

  BEGIN TRANSACTION;
  INSERT INTO mydataset.NewArrivals
    VALUES ('top load washer', 100, 'warehouse #1');
  -- Trigger an error.
  SELECT 1/0;
  COMMIT TRANSACTION;

EXCEPTION WHEN ERROR THEN
  -- Roll back the transaction inside the exception handler.
  SELECT @@error.message;
  ROLLBACK TRANSACTION;
END;

RAISE

Syntax

RAISE [USING MESSAGE = message];

Beschreibung

Löst einen Fehler aus, wobei optional die angegebene Fehlermeldung verwendet wird, wenn USING MESSAGE = message angegeben wurde.

Wenn USING MESSAGE nicht angegeben ist

Die Anweisung RAISE darf nur innerhalb einer EXCEPTION-Klausel verwendet werden. Die Anweisung RAISE löst die Ausnahme noch einmal aus und behält den ursprünglichen Stacktrace bei.

Wenn USING MESSAGE angegeben ist

Wenn die RAISE-Anweisung im Abschnitt BEGIN eines BEGIN...EXCEPTION-Blocks enthalten ist:

  • Der Handler wird aufgerufen.
  • Der Wert von @@error.message stimmt genau mit dem angegebenen String message überein. Dieser kann NULL sein, wenn message gleich NULL ist.

  • Der Stacktrace wird auf die Anweisung RAISE gesetzt.

Wenn die Anweisung RAISE nicht im Abschnitt BEGIN eines BEGIN...EXCEPTION-Blocks enthalten ist, beendet die Anweisung RAISE die Ausführung des Skripts mit der angegebenen Fehlermeldung.

RETURN

In einem BigQuery-Skript beendet RETURN die Ausführung des aktuellen Skripts.

CALL

Syntax

CALL procedure_name (procedure_argument[, …])

Beschreibung

Ruft eine Prozedur mit einer Argumentliste auf. procedure_argument kann eine Variable oder ein Ausdruck sein. Bei OUT- oder INOUT-Argumenten muss eine als Argument übergebene Variable den richtigen BigQuery-Typ haben.

Eine Variable kann nicht mehrfach als OUT- oder INOUT-Argument in der Argumentliste der Prozedur angegeben werden.

Die maximale Tiefe von Prozeduraufrufen beträgt 50 Ebenen.

CALL darf nicht dynamisch als verschachteltes Element ausgeführt werden. Das bedeutet, dass CALL nicht in einer EXECUTE IMMEDIATE-Anweisung verschachtelt werden darf.

Beispiele

Im folgenden Beispiel wird die Variable retCode deklariert. Anschließend wird die Prozedur updateSomeTables im Dataset myDataset aufgerufen und es werden die Argumente 'someAccountId' und retCode übergeben. Abschließend wird der Wert retCode zurückgegeben.

DECLARE retCode INT64;
-- Procedure signature: (IN account_id STRING, OUT retCode INT64)
CALL myDataset.UpdateSomeTables('someAccountId', retCode);
SELECT retCode;

Systemvariablen

Sie können Systemvariablen verwenden, um während der Ausführung eines Skripts Informationen zu prüfen.

Name Typ Beschreibung
@@current_job_id STRING (Schreibgeschützt) Job-ID des aktuell ausgeführten Jobs. Im Kontext eines Skripts gibt dies den Job zurück, der für die aktuelle Anweisung verantwortlich ist, nicht das gesamte Skript.
@@dataset_id STRING ID des Standard-Datasets im aktuellen Projekt. Sie wird verwendet, wenn in der Abfrage kein Dataset für ein Projekt angegeben ist. Mit der Anweisung SET können Sie @@dataset_id einer anderen Dataset-ID im aktuellen Projekt zuweisen.
@@last_job_id STRING (Schreibgeschützt) Job-ID des letzten Jobs, der im aktuellen Skript ausgeführt wurde, ohne den aktuellen Job. Wenn das Skript CALL-Anweisungen enthält, stammt dieser Job möglicherweise aus einer anderen Prozedur.
@@project_id STRING (Schreibgeschützt) ID des Projekts, das zum Ausführen der aktuellen Abfrage verwendet wurde. Im Kontext einer Prozedur bezieht sich @@project_id auf das Projekt, in dem das Skript ausgeführt wird, und nicht auf das Projekt, zu dem die Prozedur gehört.
@@row_count INT64 (Schreibgeschützt) Bei Verwendung in einem Skript und wenn die vorherige Skriptanweisung DML ist, wird die Anzahl der als Ergebnis dieser DML-Anweisung geänderten, eingefügten oder gelöschten Zeilen angegeben. Wenn die vorherige Anweisung eine MERGE-Anweisung ist, gibt @@row_count die kombinierte Gesamtzahl der eingefügten, entfernten und gelöschten Zeilen an. Dieser Wert ist NULL, wenn er nicht in einem Skript enthalten ist.
@@script.bytes_billed INT64 (Schreibgeschützt) Gesamtzahl der Byte, die bisher im derzeit ausgeführten Skriptjob in Rechnung gestellt wurden. Dieser Wert ist NULL, wenn er nicht in einem Skript enthalten ist.
@@script.bytes_processed INT64 (Schreibgeschützt) Gesamtzahl der Byte, die bisher im derzeit ausgeführten Skriptjob verarbeitet wurden. Dieser Wert ist NULL, wenn er nicht in einem Skript enthalten ist.
@@script.creation_time TIMESTAMP (Schreibgeschützt) Erstellungszeit des derzeit ausgeführten Skriptjobs. Dieser Wert ist NULL, wenn er nicht in einem Skript enthalten ist.
@@script.job_id STRING (Schreibgeschützt) Job-ID des derzeit ausgeführten Skriptjobs. Dieser Wert ist NULL, wenn er nicht in einem Skript enthalten ist.
@@script.num_child_jobs INT64 (Schreibgeschützt) Anzahl der derzeit abgeschlossenen untergeordneten Jobs. Dieser Wert ist NULL, wenn er nicht in einem Skript enthalten ist.
@@script.slot_ms INT64 (Schreibgeschützt) Anzahl der vom Skript bisher verwendeten Slot-Millisekunden. Dieser Wert ist NULL, wenn er nicht in einem Skript enthalten ist.
@@time_zone STRING Die Standardzeitzone, die in zeitzonenabhängigen SQL-Funktionen verwendet wird, wenn keine Zeitzone als Argument angegeben ist. @@time_zone kann mit einer SET-Anweisung in einen beliebigen gültigen Zeitzonennamen geändert werden. Am Anfang jedes Skripts beginnt @@time_zone mit "UTC".

Zusätzlich zu den oben gezeigten Systemvariablen können Sie während der Ausführung eines Skripts die Systemvariablen EXCEPTION verwenden. EXCEPTION-Systemvariablen sind NULL, wenn sie nicht in einem Ausnahme-Handler verwendet werden. Die folgenden sind EXCEPTION-Systemvariablen:

  • @@error.formatted_stack_trace
  • @@error.message
  • @@error.stack_trace
  • @@error.statement_text

Weitere Informationen zu den EXCEPTION-Systemvariablen finden Sie unter BEGIN...EXCEPTION.

Systemprozeduren

BigQuery unterstützt die folgenden Systemprozeduren.

BQ.JOBS.CANCEL

Syntax

CALL BQ.JOBS.CANCEL(job);

Beschreibung

Bricht einen laufenden Job ab

Geben Sie den Job als String im Format '[project_id.]job_id' an. Wenn Sie dieses Systemverfahren von einem anderen Projekt als dem Job aus ausführen, müssen Sie die Projekt-ID angeben. Die Prozedur muss in derselben Region wie der Job ausgeführt werden.

Weitere Informationen erhalten Sie auch unter Job abbrechen.

BQ.REFRESH_MATERIALIZED_VIEW

Syntax

CALL BQ.REFRESH_MATERIALIZED_VIEW(view_name);

Beschreibung

Aktualisiert eine materialisierte Ansicht.

Geben Sie den Namen der materialisierten Ansicht als String im Format '[project_id.]dataset.table' an. Wenn Sie dieses Systemverfahren von einem anderen Projekt als der materialisierten Ansicht ausführen, müssen Sie die Projekt-ID angeben.

Weitere Informationen finden Sie unter Manuelle Aktualisierung.

Berechtigungen

Die Berechtigung zum Zugriff auf eine Tabelle, ein Modell oder eine andere Ressource wird bei der Ausführung geprüft. Wenn eine Anweisung nicht ausgeführt oder ein Ausdruck nicht ausgewertet wird, prüft BigQuery nicht, ob der Nutzer, der das Skript ausführt, Zugriff auf darin referenzierte Ressourcen hat.

Innerhalb eines Skripts werden die Berechtigungen für jeden Ausdruck und jede Anweisung separat validiert. Ein Beispiel ist das folgende Skript:

SELECT * FROM dataset_with_access.table1;
SELECT * FROM dataset_without_access.table2;

Wenn der Nutzer, der das Skript ausführt, Zugriff auf table1, aber nicht auf table2 hat, ist die erste Abfrage erfolgreich und die zweite Abfrage schlägt fehl. Der Skriptjob selbst schlägt ebenfalls fehl.

Sicherheitsbeschränkungen

Dynamisches SQL ist praktisch, bietet aber auch neue Möglichkeiten für Missbrauch. Das Ausführen der folgenden Abfrage stellt beispielsweise eine potenzielle Sicherheitsbedrohung dar, da der Tabellenparameter möglicherweise nicht ordnungsgemäß gefiltert wird und so Zugriff auf nicht beabsichtigte Tabellen und die Ausführung darin ermöglicht wird.

EXECUTE IMMEDIATE CONCAT(‘SELECT * FROM ’, @employee_table);

Um zu verhindern, dass vertrauliche Daten in einer Tabelle offengelegt oder Befehle wie DROP TABLE zum Löschen von Daten in einer Tabelle ausgeführt werden, unterstützen BigQuery-Skripts mit dynamischem SQL verschiedene Sicherheitsmaßnahmen, um die Anfälligkeit für SQL-Injection-Angriffe zu reduzieren. Beispiele:

  • Sie können nicht mehrere SQL-Anweisungen ausführen, die in an dynamisches SQL übergebene Parameter eingebettet sind.
  • Die folgenden Befehle können nicht dynamisch ausgeführt werden: BEGIN/END, CALL, CASE, IF, LOOP, WHILE und EXECUTE IMMEDIATE.

Einschränkungen des Konfigurationsfelds

Die folgenden Abfragekonfigurationsfelder können nicht für Skripts festgelegt werden:

  • clustering
  • create_disposition
  • destination_table
  • destination_encryption_configuration
  • range_partitioning
  • schema_update_options
  • time_partitioning
  • user_defined_function_resources
  • write_disposition