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 dieINTO
-Klausel Werte desselben Typs enthalten. Sie können sowohl auf Systemvariablen als auch auf Werte in derUSING
-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 derINTO
-Klausel in einer oder mehreren Variablen speichern.USING
-Klausel: Bevor Sie den SQL-Ausdruck ausführen, können Sie eine oder mehrere Kennzeichnungen aus derUSING
-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 derUSING
-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 derUSING
-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
undUSING
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 derINTO
-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 ausquery_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:
|
@@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.
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 Stringmessage
überein. Dieser kannNULL
sein, wennmessage
gleichNULL
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". |
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
undEXECUTE 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