Erstellen von Skripts in Standard-SQL

Erstellen von BigQuery-Skripts

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

In BigQuery besteht ein Skript aus einer Liste von SQL-Anweisungen, die nacheinander ausgeführt werden. Eine SQL-Anweisungsliste ist eine Liste mit gültigen BigQuery-Anweisungen, die durch Semikolons getrennt sind.

Beispiel:

-- Declare a variable to hold names as an array.
DECLARE top_names ARRAY<STRING>;
-- Build an array of the top 100 names from the year 2017.
SET top_names = (
  SELECT ARRAY_AGG(name ORDER BY number DESC LIMIT 100)
  FROM `bigquery-public-data`.usa_names.usa_1910_current
  WHERE year = 2017
);
-- Which names appear as words in Shakespeare's plays?
SELECT
  name AS shakespeare_name
FROM UNNEST(top_names) AS name
WHERE name IN (
  SELECT word
  FROM `bigquery-public-data`.samples.shakespeare
);

In BigQuery werden Skripts, ähnlich wie bei jeder anderen Anfrage, mit jobs.insert ausgeführt, wobei das Skript mit der Mehrfachanweisung als Abfragetext angegeben wird. Bei der Ausführung eines Skripts werden für jede enthaltene Anweisung zusätzliche Jobs erstellt, die als untergeordnete Jobs bezeichnet werden. Sie können die untergeordneten Jobs eines Skripts auflisten, indem Sie jobs.list aufrufen und die Job-ID des Skripts als parentJobId-Parameter übergeben.

Wenn jobs.getQueryResults für ein Skript aufgerufen wird, werden die Abfrageergebnisse für die letzte SELECT-, DML- oder DDL-Anweisung zurückgegeben, die im Skript ausgeführt werden soll. Wurde keine der obigen Anweisungen ausgeführt, werden auch keine Abfrageergebnisse ausgegeben. Wenn Sie die Ergebnisse aller Anweisungen im Skript abrufen möchten, listen Sie die untergeordneten Jobs auf und rufen Sie für jeden jobs.getQueryResults auf.

BigQuery interpretiert jede Anfrage mit mehreren Anweisungen als Skript, es sei denn, es handelt sich dabei um CREATE TEMP FUNCTION-Anweisungen mit einer einzigen abschließenden Abfrageanweisung. Beispielsweise würde Folgendes nicht als Skript betrachtet:

CREATE TEMP FUNCTION Add(x INT64, y INT64) AS (x + y);

SELECT Add(3, 4);

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.
• 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.
• 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

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.

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

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.

¹ 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.

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]"

END

Beendet einen von BEGIN initiierten Block. BEGIN/END darf nicht dynamisch als verschachteltes Element ausgeführt werden.

IF

Syntax

IF condition THEN [sql_statement_list]
[ELSEIF 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.

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;

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.

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 |
+----+

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.

BREAK

Beschreibung

Beendet die aktuelle Schleife.

BREAK darf nicht außerhalb einer Schleife verwendet werden.

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');

VERLASSEN

Synonym für BREAK.

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.

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.

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.

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	Job-ID des derzeit 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.
@@last_job_id	STRING	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	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	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	Gesamtzahl der bisher im aktuell ausgeführten Skriptjob in Rechnung gestellten Byte. Dieser Wert ist NULL, wenn er nicht in einem Skript enthalten ist.
@@script.bytes_processed	INT64	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	Erstellzeit des derzeit ausgeführten Skriptjobs. Dieser Wert ist NULL, wenn er nicht in einem Skript enthalten ist.
@@script.job_id	STRING	Job-ID des derzeit ausgeführten Skriptjobs. Dieser Wert ist NULL, wenn er nicht in einem Skript enthalten ist.
@@script.num_child_jobs	INT64	Anzahl der aktuell abgeschlossenen untergeordneten Jobs. Dieser Wert ist NULL, wenn er nicht in einem Skript enthalten ist.
@@script.slot_ms	INT64	Anzahl der vom Skript bisher verwendeten Slot-Millisekunden. Dieser Wert ist NULL, wenn er nicht in einem Skript enthalten ist.
@@time_zone	STRING	Standardzeitzone, die in zeitzonenabhängigen SQL-Funktionen verwendet wird, wenn keine explizite Zeitzone als Argument angegeben ist. Im Gegensatz zu anderen Systemvariablen kann @@time_zone mit einer SET-Anweisung zu einem gültigen Zeitzonennamen geändert werden. Am Anfang jedes Skripts beginnt @@time_zone mit "UTC".

Hinweis: Aus Gründen der Abwärtskompatibilität sind Ausdrücke, die in einer OPTIONS- oder FOR SYSTEM TIME AS OF-Klausel verwendet werden, standardmäßig auf die Zeitzone America/Los_Angeles festgelegt, während alle anderen Datums-/Uhrzeitausdrücke standardmäßig die Zeitzone UTC verwenden. Wenn zuvor im Skript @@time_zone festgelegt wurde, gilt die ausgewählte Zeitzone für alle Datums-/Uhrzeitausdrücke, auch in den Klauseln OPTIONS und FOR SYSTEM TIME AS OF.

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.

Permissions

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, 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