Mit Sammlungen den Überblick behalten Sie können Inhalte basierend auf Ihren Einstellungen speichern und kategorisieren.

Mit Abfragen mit mehreren Anweisungen arbeiten

In diesem Dokument wird die Verwendung von Abfragen mit mehreren Anweisungen in BigQuery beschrieben, z. B. wie Sie Abfragen mit mehreren Anweisungen schreiben, wie Sie temporäre Tabellen in Abfragen mit mehreren Anweisungen verwenden, wie Sie auf Variablen in Abfragen mit mehreren Anweisungen verweisen und wie Sie Abfragen mit mehreren Anweisungen debuggen.

Eine Abfrage mit mehreren Anweisungen ist eine Sammlung von SQL-Anweisungen, die Sie in einer einzigen Anfrage ausführen können. Mit Abfragen mit mehreren Anweisungen können Sie mehrere Anweisungen in einer Sequenz mit gemeinsamem Status ausführen. Abfragen mit mehreren Anweisungen können Nebenwirkungen haben, z. B. das Hinzufügen oder Ändern von Tabellendaten.

Abfragen mit mehreren Anweisungen werden häufig in gespeicherten Prozeduren verwendet und unterstützen prozedurale Sprachanweisungen, mit denen Sie beispielsweise Variablen definieren und die Ablaufsteuerung implementieren können.

Abfragen mit mehreren Anweisungen schreiben, ausführen und speichern

Eine Abfrage mit mehreren Anweisungen besteht aus einer oder mehreren SQL-Anweisungen, die durch Semikolons getrennt sind. Jede gültige SQL-Anweisung kann in einer Abfrage mit mehreren Anweisungen verwendet werden. Abfragen mit mehreren Anweisungen können auch prozedurale Sprachanweisungen enthalten, bei denen Sie mit Ihren SQL-Anweisungen Variablen verwenden oder eine Ablaufsteuerung implementieren können.

Abfrage mit mehreren Anweisungen schreiben

Sie können eine Abfrage mit mehreren Anweisungen in BigQuery schreiben. Mit der folgenden Abfrage mit mehreren Abfragen wird eine Variable deklariert und in einer IF-Anweisung verwendet:

DECLARE day INT64;
SET day = (SELECT EXTRACT(DAYOFWEEK from CURRENT_DATE));
if day = 1 or day = 7 THEN
  SELECT 'Weekend';
ELSE
  SELECT 'Weekday';
END IF

BigQuery interpretiert jede Anfrage mit mehreren Anweisungen als Abfrage mit mehreren Anweisungen, es sei denn, die Anweisungen bestehen vollständig aus CREATE TEMP FUNCTION-Anweisungen gefolgt von einer einzigen SELECT-Anweisung. Beispielsweise wird Folgendes nicht als Abfrage mit mehreren Anweisungen betrachtet:

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

Abfrage mit mehreren Anweisungen ausführen

Sie können eine Abfrage mit mehreren Anweisungen auf die gleiche Weise wie jede andere Abfrage ausführen, z. B. in der Google Cloud Console oder mithilfe des bq-Befehlszeilentools.

Abfrage mit mehreren Anweisungen speichern

Informationen zum Speichern einer Abfrage mit mehreren Anweisungen finden Sie unter Abfragen speichern und freigeben.

Variablen in einer Abfrage mit mehreren Anweisungen verwenden

Eine Abfrage mit mehreren Anweisungen kann von Nutzern erstellte Variablen und Systemvariablen enthalten.

  • Sie können von Nutzern erstellte Variablen deklarieren, ihnen Werte zuweisen und sie in der gesamten Abfrage referenzieren.

  • Sie können in einer Abfrage auf Systemvariablen verweisen und einigen von ihnen Werte zuweisen. Im Gegensatz zu benutzerdefinierten Variablen werden sie jedoch nicht deklariert. Systemvariablen sind in BigQuery eingebunden.

Vom Nutzer erstellte Variable deklarieren

Sie müssen von Nutzern erstellte Variablen entweder am Anfang der Abfrage mit mehreren Anweisungen oder am Anfang eines BEGIN-Blocks deklarieren. Zu Beginn der Abfrage mit mehreren Anweisungen deklarierte Variablen gelten für die gesamte Abfrage. Variablen, die in einem BEGIN-Block deklariert sind, gelten nur für den Block. Nach der entsprechenden END-Anweisung werden sie nicht mehr berücksichtigt. Die maximale Größe einer Variablen beträgt 1 MB. Die maximale Größe aller in einer Abfrage mit mehreren Anweisungen verwendeten Variablen beträgt 10 MB.

Sie können eine Variable mit der prozeduralen Anweisung DECLARE so deklarieren:

DECLARE x INT64;

BEGIN
DECLARE y INT64;
-- Here you can reference x and y
END;

-- Here you can reference x, but not y

Vom Nutzer erstellte Variable festlegen

Nachdem Sie eine vom Nutzer erstellte Variable deklariert haben, können Sie ihr mit der prozeduralen Anweisung SET einen Wert zuweisen:

DECLARE x INT64 DEFAULT 0;
SET x = 10;

Systemvariable festlegen

Sie erstellen keine Systemvariablen, aber Sie können den Standardwert für einige davon so überschreiben:

SET @@dataset_project_id = 'MyProject';

Sie können auch eine Systemvariable in einer Abfrage mit mehreren Anweisungen festlegen und implizit verwenden. In der folgenden Abfrage müssen Sie beispielsweise das Projekt jedes Mal einfügen, wenn Sie eine neue Tabelle erstellen möchten:

BEGIN
  CREATE TABLE MyProject.MyDataset.MyTempTableA (id STRING);
  CREATE TABLE MyProject.MyDataset.MyTempTableB (id STRING);
END;

Wenn Sie das Projekt nicht mehrfach zu Tabellenpfaden hinzufügen möchten, können Sie die Dataset-Projekt-ID MyProject der Systemvariable @@dataset_project_id in der Abfrage mit mehreren Anweisungen zuweisen. Durch diese Zuweisung wird MyProject zum Standardprojekt für den Rest der Abfrage.

SET @@dataset_project_id = 'MyProject';

BEGIN
  CREATE TABLE MyDataset.MyTempTableA (id STRING);
  CREATE TABLE MyDataset.MyTempTableB (id STRING);
END;

Ebenso können Sie die Systemvariable @@dataset_id festlegen, um der Abfrage ein Standard-Dataset zuzuweisen. Beispiel:

SET @@dataset_project_id = 'MyProject';
SET @@dataset_id = 'MyDataset';

BEGIN
  CREATE TABLE MyTempTableA (id STRING);
  CREATE TABLE MyTempTableB (id STRING);
END;

Sie können auch in vielen Teilen einer Abfrage mit mehreren Anweisungen explizit auf Systemvariablen wie @@dataset_id verweisen. Weitere Informationen finden Sie unter Auf eine Systemvariable verweisen.

Auf eine vom Nutzer erstellte Variable verweisen

Nachdem Sie eine vom Nutzer erstellte Variable deklariert und festgelegt haben, können Sie in einer Abfrage mit mehreren Anweisungen darauf verweisen. Wenn eine Variable und eine Spalte denselben Namen haben, hat die Spalte Vorrang.

Dadurch wird column x + column x zurückgegeben:

DECLARE x INT64 DEFAULT 0;
SET x = 10;

WITH Numbers AS (SELECT 50 AS x)
SELECT (x+x) AS result FROM Numbers;
+--------+
| result |
+--------+
| 100    |
+--------+

Dadurch wird column y + variable x zurückgegeben:

DECLARE x INT64 DEFAULT 0;
SET x = 10;

WITH Numbers AS (SELECT 50 AS y)
SELECT (y+x) AS result FROM Numbers;
+--------+
| result |
+--------+
| 60     |
+--------+

Auf eine Systemvariable verweisen

Sie können in einer Abfrage mit mehreren Anweisungen auf eine Systemvariable verweisen.

Die folgende Abfrage gibt die Standardzeitzone zurück:

BEGIN
  SELECT @@time_zone AS default_time_zone;
END;
+-------------------+
| default_time_zone |
+-------------------+
| UTC               |
+-------------------+

Sie können Systemvariablen mit DDL- und DML-Abfragen verwenden. Im Folgenden finden Sie einige Möglichkeiten, wie Sie die Systemvariable @@time_zone beim Erstellen und Aktualisieren einer Tabelle verwenden können:

BEGIN
  CREATE TEMP TABLE MyTempTable
  AS SELECT @@time_zone AS default_time_zone;
END;
BEGIN
  CREATE OR REPLACE TABLE MyDataset.MyTable(default_time_zone STRING)
  OPTIONS (description = @@time_zone);
END;
BEGIN
  UPDATE MyDataset.MyTable
  SET default_time_zone = @@time_zone
  WHERE TRUE;
END;

Es gibt einige Stellen, an denen Systemvariablen nicht in DDL- und DML-Abfragen verwendet werden können. Systemvariablen können beispielsweise nicht als Projektname, Dataset oder Tabellenname verwendet werden. Dies erzeugt einen Fehler, wenn Sie versuchen, die Systemvariable @@dataset_id in einen Tabellenpfad aufzunehmen:

BEGIN
  CREATE TEMP TABLE @@dataset_id.MyTempTable (id STRING);
END;

Temporäre Tabellen in einer Abfrage mit mehreren Anweisungen verwenden

Mit temporären Tabellen können Sie Zwischenergebnisse in einer Tabelle speichern. Diese temporären Tabellen sind auf Sitzungsebene vorhanden, sodass Sie sie nicht in einem Dataset speichern oder verwalten müssen.

Sie können eine temporäre Tabelle in einer Abfrage mit mehreren Anweisungen erstellen und referenzieren. Wenn Sie mit der temporären Tabelle fertig sind, können Sie sie manuell löschen oder warten, bis BigQuery sie nach 24 Stunden löscht.

Vorläufige Tabelle erstellen

Sie können eine temporäre Tabelle für eine Abfrage mit mehreren Anweisungen mit der CREATE TABLE-Anweisung erstellen. Im folgenden Beispiel wird eine temporäre Tabelle erstellt, um die Ergebnisse einer Abfrage zu speichern, und die temporäre Tabelle wird in einer Unterabfrage verwendet:

-- Find the top 100 names from the year 2017.
CREATE TEMP TABLE top_names(name STRING)
AS
 SELECT name
 FROM `bigquery-public-data`.usa_names.usa_1910_current
 WHERE year = 2017
 ORDER BY number DESC LIMIT 100
;
-- Which names appear as words in Shakespeare's plays?
SELECT
 name AS shakespeare_name
FROM top_names
WHERE name IN (
 SELECT word
 FROM `bigquery-public-data`.samples.shakespeare
);

Abgesehen von der Verwendung von TEMP bzw. TEMPORARY ist die Syntax identisch mit jener von CREATE TABLE.

Verwenden Sie beim Erstellen einer temporären Tabelle keinen Projekt- oder Dataset-Qualifier im Tabellennamen. Die Tabelle wird automatisch in einem speziellen Dataset erstellt.

Für das Speichern temporärer Tabellen fallen keine Kosten an.

Auf eine temporäre Tabelle verweisen

Für die Dauer der aktuellen Abfrage mit mehreren Anweisungen können Sie auf eine temporäre Tabelle anhand des Namens verweisen. Dies gilt auch für temporäre Tabellen, die von einer Prozedur in der Abfrage mit mehreren Anweisungen erstellt werden. Temporäre Tabellen können nicht freigegeben werden. Sie können auch nicht mit der standardmäßigen Auflistungsmethode oder anderen Methoden zur Tabellenbearbeitung sichtbar gemacht werden.

Temporäre Tabellen löschen

Sie können eine temporäre Tabelle explizit mit der DROP TABLE-Anweisung löschen, bevor die Abfrage mit mehreren Anweisungen abgeschlossen ist:

CREATE TEMP TABLE table1(x INT64);
SELECT * FROM table1;  -- Succeeds
DROP TABLE table1;
SELECT * FROM table1;  -- Results in an error

Nach Abschluss einer Abfrage mit mehreren Anweisungen ist die temporäre Tabelle bis zu 24 Stunden vorhanden.

Daten einer temporären Tabelle ansehen

Nachdem Sie eine temporäre Tabelle erstellt haben, können Sie die Struktur der Tabelle und der darin enthaltenen Daten aufrufen. So rufen Sie die Tabellenstruktur und die Daten auf:

  1. Rufen Sie in der Google Cloud Console die Seite Big Query Explorer auf.

    Zum Explorer

  2. Klicken Sie auf Abfrageverlauf.

  3. Wählen Sie die Abfrage aus, mit der die temporäre Tabelle erstellt wurde.

  4. Klicken Sie dann in der Zeile Zieltabelle auf Temporäre Tabelle.

Temporäre Tabellen mit _SESSION qualifizieren

Wenn temporäre Tabellen zusammen mit einem Standard-Dataset verwendet werden, beziehen sich nicht qualifizierte Tabellennamen auf eine temporäre Tabelle, sofern vorhanden, oder auf eine Tabelle im Standard-Dataset. Eine Ausnahme sind CREATE TABLE-Anweisungen, bei denen die Zieltabelle nur dann als temporäre Tabelle gilt, wenn das Schlüsselwort TEMP oder TEMPORARY vorhanden ist.

Betrachten Sie beispielsweise die folgende Abfrage mit mehreren Anweisungen:

-- Create table t1 in the default dataset
CREATE TABLE t1 (x INT64);

-- Create temporary table t1.
CREATE TEMP TABLE t1 (x INT64);

-- This statement selects from the temporary table.
SELECT * FROM t1;

-- Drop the temporary table
DROP TABLE t1;

-- Now that the temporary table is dropped, this statement selects from the
-- table in the default dataset.
SELECT * FROM t1;

Sie können explizit angeben, dass es sich um eine temporäre Tabelle handelt. Dazu qualifizieren Sie den Tabellennamen mit _SESSION:

-- Create a temp table
CREATE TEMP TABLE t1 (x INT64);

-- Create a temp table using the `_SESSION` qualifier
CREATE TEMP TABLE _SESSION.t2 (x INT64);

-- Select from a temporary table using the `_SESSION` qualifier
SELECT * FROM _SESSION.t1;

Wenn Sie den Qualifier _SESSION zum Abfragen einer temporären Tabelle verwenden, die nicht vorhanden ist, gibt die Abfrage mit mehreren Anweisungen einen Fehler aus, der angibt, dass die Tabelle nicht vorhanden ist. Wenn beispielsweise keine temporäre Tabelle namens t3 vorhanden ist, gibt die Abfrage mit mehreren Anweisungen einen Fehler aus, auch wenn eine Tabelle mit dem Namen t3 im Standard-Dataset vorhanden ist.

Sie können _SESSION nicht zum Erstellen einer nicht temporären Tabelle verwenden:

CREATE TABLE _SESSION.t4 (x INT64);  -- Fails

Informationen zu einem Abfragejob mit mehreren Anweisungen erfassen

Ein Abfragejob mit mehreren Anweisungen enthält Informationen zu einer ausgeführten Abfrage mit mehreren Anweisungen. Einige häufige Aufgaben, die Sie mit Jobdaten ausführen können, umfassen die Rückgabe der letzten mit der Abfrage mit mehreren Anweisungen ausgeführten Anweisung oder die Rückgabe aller Anweisungen, die mit der Abfrage mit mehreren Anweisungen ausgeführt wurden.

Zuletzt ausgeführte Anweisung zurückgeben

Die Methode jobs.getQueryResults gibt die Abfrageergebnisse für die letzte Anweisung zurück, die in der Abfrage mit mehreren Anweisungen ausgeführt wurde. Wenn keine Anweisung ausgeführt wurde, werden keine Ergebnisse zurückgegeben.

Alle ausgeführten Anweisungen zurückgeben

Wenn Sie die Ergebnisse aller Anweisungen in der Abfrage mit mehreren Anweisungen abrufen möchten, listen Sie die untergeordneten Jobs auf und rufen Sie für jeden Job jobs.getQueryResults auf.

Untergeordnete Jobs auflisten

Abfragen mit mehreren Anweisungen werden in BigQuery wie jede andere Abfrage mit jobs.insert ausgeführt. Dabei werden die Abfragen mit mehreren Anweisungen als Abfragetext angegeben. Beim Ausführen einer Abfrage mit mehreren Anweisungen werden für jede Anweisung in der Abfrage mit mehreren Anweisungen zusätzliche Jobs erstellt, die als untergeordnete Jobs bezeichnet werden. Sie können die untergeordneten Jobs einer Abfrage mit mehreren Anweisungen aufzählen, indem Sie jobs.list aufrufen und die Abfragejob-ID mit mehreren Anweisungen als parentJobId-Parameter übergeben.

Abfrage mit mehreren Anweisungen debuggen

Hier finden Sie einige Tipps zum Debuggen von Abfragen mit mehreren Anweisungen:

  • Mit der Anweisung ASSERT können Sie bestätigen, dass eine boolesche Bedingung erfüllt ist.

  • Mit BEGIN...EXCEPTION...END können Sie Fehler erkennen und Fehlermeldung sowie Stacktrace anzeigen lassen.

  • Verwenden Sie SELECT FORMAT("...."), um Zwischenergebnisse anzuzeigen.

  • Wenn Sie eine Abfrage mit mehreren Anweisungen in der Google Cloud Console ausführen, können Sie sich die Ausgabe jeder Anweisung in der Abfrage mit mehreren Anweisungen ansehen. Mit dem Befehl `bq query` des bq-Befehlszeilentools werden auch die Ergebnisse der einzelnen Schritte angezeigt, wenn Sie eine Abfrage mit mehreren Anweisungen ausführen.

  • Wählen Sie in der Google Cloud Console eine einzelne Anweisung im Abfrageeditor und führen Sie diese aus.

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 die Abfrage mit mehreren Anweisungen ausführt, Zugriff auf darin referenzierte Ressourcen hat.

Innerhalb einer Abfrage mit mehreren Anweisungen werden die Berechtigungen für jeden Ausdruck und jede Anweisung separat validiert. Beispiel:

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

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

Sicherheitsbeschränkungen

In Abfragen mit mehreren Anweisungen können Sie dynamisches SQL verwenden, um SQL-Anweisungen zur Laufzeit zu erstellen. Dies ist praktisch, bietet aber auch neue Möglichkeiten für Missbrauch. Das Ausführen der folgenden Abfrage stellt beispielsweise eine potenzielle Sicherheitsbedrohung in Form von SQL-Injection 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.

-- Risky query vulnerable to SQL injection attack.
EXECUTE IMMEDIATE CONCAT('SELECT * FROM SensitiveTable WHERE id = ', @id);

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 dynamische prozedurale Anweisungen von BigQuery verschiedene Sicherheitsmaßnahmen, um die Anfälligkeit für SQL-Injection-Angriffe zu reduzieren. Beispiele:

  • In der mit Abfrageparametern und Variablen erweiterten EXECUTE IMMEDIATE-Anweisung dürfen nicht mehrere SQL-Anweisungen eingebettet sein.
  • 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 für die Jobkonfiguration können für eine Abfrage mit mehreren Anweisungen nicht festgelegt werden:

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

Preise

Wenn Sie On-Demand-Abrechnung verwenden, erfolgt die Abrechnung von BigQuery entsprechend der Anzahl der Byte, die während der Ausführung der Abfragen mit mehreren Anweisungen verarbeitet werden.

Weitere Informationen finden Sie unter Abfragegrößenberechnung.

Kontingente

Weitere Informationen zu den Kontingenten für Abfragen mit mehreren Anweisungen finden Sie unter Kontingente und Limits.