Einführung in föderierte Abfragen

Auf dieser Seite wird die Verwendung von föderierten Abfragen erläutert. Außerdem erhalten Sie eine Anleitung zum Abfragen von Spanner-, AlloyDB- und Cloud SQL-Daten aus BigQuery.

Mit föderierten Abfragen können Sie eine Abfrageanweisung an AlloyDB-, Spanner- oder Cloud SQL-Datenbanken senden und das Ergebnis als temporäre Tabelle zurückgeben. Föderierte Abfragen verwenden die BigQuery Connection API, um eine Verbindung mit AlloyDB, Spanner oder Cloud SQL herzustellen. In der Abfrage verwenden Sie die Funktion EXTERNAL_QUERY, um eine Abfrageanweisung mit dem SQL-Dialekt dieser Datenbank an die externe Datenbank zu senden. Die Ergebnisse werden in GoogleSQL-Datentypen konvertiert.

Unterstützte Datenspeicher

Sie können föderierte Abfragen mit den folgenden Datenspeichern verwenden:

Workflow

  • Bestimmen Sie das Google Cloud-Projekt mit der Datenquelle, die Sie abfragen möchten.
  • Ein bigquery.admin-Nutzer erstellt eine Verbindungsressource in BigQuery.
  • Der Administrator erteilt Nutzer B die Berechtigung, die Verbindungsressource zu verwenden.
    • Wenn der Administrator und Nutzer B dieselbe Person sind, muss keine Berechtigung erteilt werden.
  • Nutzer B schreibt eine Abfrage mit der neuen SQL-Funktion EXTERNAL_QUERY in BigQuery.

Alternativen zu föderierten Abfragen: externe Tabellen und Datensätze

Eine weitere Möglichkeit, betriebliche Datenbanken wie Bigtable, Spanner, Cloud Storage, Google Drive und Salesforce Data Cloud abzufragen, besteht darin, externe Tabellen und Datensätze zu verwenden. Mit externen Datasets und Tabellen können Sie Tabellen und ihre Schemas aufrufen und abfragen, ohne eine EXTERNAL_QUERY-SQL-Funktion zu verwenden. Sie müssen die Daten nicht wieder in BigQuery hochladen und können die BigQuery-Syntax verwenden, anstatt SQL im spezifischen Datenbankdialekt zu schreiben.

Unterstützte Regionen

Föderierte Abfragen werden nur in Regionen unterstützt, die sowohl die externe Datenquelle als auch BigQuery unterstützen. Eine Liste der unterstützten Standorte finden Sie in den folgenden Abschnitten:

Sie können gemäß den folgenden Regeln eine Verbindung erstellen und eine föderierte Abfrage regionenübergreifend ausführen.

Einzelne Regionen

Eine einzelne BigQuery-Region kann nur eine Ressource in derselben Region abfragen.

Wenn Ihr Dataset beispielsweise us-east4 hat, können Sie Cloud SQL-Instanzen, AlloyDB-Instanzen oder Spanner-Datenbanken abfragen, die sich in us-east4 befinden. Der Standort für die Abfrageverarbeitung ist die einzelne BigQuery-Region.

Multiregionen

Eine BigQuery-Multiregion kann jede Region für Datenquellen im selben großen geografischen Gebiet (USA, EU) abfragen. Multiregionale Standorte sind für Cloud SQL-Instanzen nicht verfügbar, da sie nur für Sicherungen verwendet werden. Eine BigQuery-Multiregion kann auch eine Spanner-Instanz in derselben Multi-Region abfragen.

  • Eine Abfrage, die in der Multiregion BigQuery ausgeführt wird, kann jede einzelne Region im geografischen Gebiet der USA abfragen, z. B. us-central1, us-east4 oder us-west2.

  • Eine Abfrage, die in der Multiregion BigQuery EU ausgeführt wird, kann jede einzelne Region in den Mitgliedstaaten der Europäischen Union abfragen, z. B. europe-north1 oder europe-west3.

  • Der Standort, an dem die Abfrage ausgeführt wird, muss mit dem Standort der Verbindungsressource übereinstimmen. Beispielsweise müssen Abfragen, die vom multiregionalen Standort „US“ ausgeführt werden, eine Verbindung am multiregionalen Standort „US“ verwenden.

Die Abfrageleistung hängt von der Nähe zwischen dem Dataset und der externen Datenquelle ab. Beispielsweise ist eine föderierte Abfrage zwischen einem Dataset am multiregionalen Standort „US“ und einer Cloud SQL-Instanz in us-central1 schnell. Wenn Sie jedoch dieselbe Abfrage zwischen dem multiregionalen Standort „USA“ und einer Cloud SQL-Instanz in us-east4 ausführen, ist die Leistung möglicherweise niedriger.

Der Standort für die Abfrageverarbeitung ist der multiregionale Standort, entweder US oder EU.

Datentypzuordnungen

Wenn Sie eine föderierte Abfrage ausführen, werden die Daten aus der externen Datenquelle in GoogleSQL-Typen konvertiert. Weitere Informationen finden Sie unter Föderierte Cloud SQL-Abfragen.

Kontingente und Limits

  • Regionenübergreifende föderierte Abfragen Wenn sich der BigQuery-Standort zur Abfrageverarbeitung und der Standort der externen Datenquelle unterscheiden, ist dies eine regionenübergreifende Abfrage. Sie können pro Projekt und Tag regionenübergreifende Abfragen in einem Umfang von bis zu 1 TB ausführen. Das folgende Beispiel zeigt eine regionenübergreifende Abfrage.
    • Die Cloud SQL-Instanz befindet sich in us-west1, während sich die BigQuery-Verbindung in der Multiregion „USA“ befindet. Der BigQuery-Standort zur Abfrageverarbeitung ist US.
  • Kontingent. Nutzer sollten ihr Abfragekontingent in der externen Datenquelle wie Cloud SQL oder AlloyDB steuern. Es gibt keine zusätzliche Kontingenteinstellung für föderierte Abfragen. Wenn Sie eine Isolation von Arbeitslasten erreichen möchten, wird empfohlen, nur ein Lesereplikat der Datenbank abzufragen.
  • Maximal zulässige Anzahl an abgerechneten Byte Dieses Feld wird für föderierte Abfragen nicht unterstützt. Die Berechnung der abgerechneten Byte vor der tatsächlichen Ausführung der föderierten Abfragen ist nicht möglich.
  • Anzahl der Verbindungen. Eine föderierte Abfrage kann höchstens 10 eindeutige Verbindungen haben.
  • Es gelten Kontingente und Beschränkungen für Cloud SQL MySQL und PostgreSQL.

Beschränkungen

Föderierte Abfragen unterliegen den folgenden Einschränkungen:

  • Leistung: Eine föderierte Abfrage ist wahrscheinlich nicht so schnell wie das alleinige Abfragen des BigQuery-Speichers. BigQuery muss warten, bis die Quelldatenbank die externe Abfrage ausführt und Daten vorübergehend aus der externen Datenquelle in BigQuery verschiebt. Außerdem ist die Quelldatenbank möglicherweise nicht für komplexe analytische Abfragen optimiert.

    Die Abfrageleistung hängt außerdem von der Nähe zwischen dem Dataset und der externen Datenquelle ab. Weitere Informationen finden Sie unter Unterstützte Regionen.

  • Föderierte Abfragen sind schreibgeschützt. Die externe Abfrage, die in der Quelldatenbank ausgeführt wird, muss schreibgeschützt sein. Daher werden DML- und DDL-Anweisungen nicht unterstützt.

  • Nicht unterstützte Datentypen. Wenn Ihre externe Abfrage einen Datentyp enthält, der in BigQuery nicht unterstützt wird, schlägt die Abfrage sofort fehl. Sie können den nicht unterstützten Datentyp in einen anderen unterstützten Datentyp umwandeln.

  • Project. Sie müssen die Verbindungsressource im selben Projekt wie die Cloud SQL- oder AlloyDB-Instanz erstellen.

Preise

  • Wenn Sie das On-Demand-Preismodell verwenden, wird Ihnen bei der Ausführung föderierter Abfragen über BigQuery die Anzahl der von der externen Abfrage zurückgegebenen Byte in Rechnung gestellt. Weitere Informationen finden Sie unter On-Demand-Preise.

  • Bei der Verwendung von BigQuery-Editionen erfolgt die Abrechnung auf Grundlage der Anzahl der verwendeten Slots. Weitere Informationen finden Sie unter Preise: Kapazitätsberechnung.

SQL-Pushdowns

Föderierte Abfragen unterliegen der Optimierungstechnik, die als SQL-Pushdown bezeichnet wird. Sie verbessern die Leistung einer Abfrage, indem sie Vorgänge wie das Filtern an die externe Datenquelle delegieren, anstatt sie in BigQuery auszuführen. Wenn Sie die Menge der von der externen Datenquelle übertragenen Daten reduzieren, können Sie die Abfrageausführungszeit verkürzen und die Kosten senken. Zu den SQL-Pushdowns gehören die Spaltenbereinigung (SELECT-Klauseln) und Filter-Pushdowns (WHERE-Klauseln).

Wenn Sie die Funktion EXTERNAL_QUERY verwenden, werden SQL-Pushdowns durch Umschreiben der ursprünglichen Abfrage ausgeführt. Im folgenden Beispiel wird die Funktion EXTERNAL_QUERY verwendet, um mit einer Cloud SQL-Datenbank zu kommunizieren:

SELECT COUNT(*)
FROM (
  SELECT * FROM EXTERNAL_QUERY("<connection>", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

Ohne SQL-Pushdowns wird die folgende Abfrage an Cloud SQL gesendet:

SELECT *
FROM operations_table

Wenn diese Abfrage ausgeführt wird, wird die gesamte Tabelle an BigQuery zurückgesendet, auch wenn nur einige Zeilen und Spalten benötigt werden.

Bei SQL-Pushdowns wird die folgende Abfrage an Cloud SQL gesendet:

SELECT `a`, `b`
FROM (
  SELECT * FROM operations_table) t
WHERE ((`a` = 'Y') AND (NOT `b` IN ('COMPLETE', 'CANCELLED'))

Wenn diese Abfrage ausgeführt wird, werden nur zwei Spalten und die Zeilen zurück an BigQuery gesendet, die mit dem Filterprädikat übereinstimmen.

SQL-Pushdowns werden auch angewendet, wenn föderierte Abfragen mit externen Spanner-Datensätzen ausgeführt werden.

Im Abfrageplan können Sie sich die angewendeten Pushdowns ansehen (falls vorhanden).

Beschränkungen

SQL-Pushdowns haben verschiedene Einschränkungen, die je nach externer Datenquelle und Art der Abfrage variieren.

Einschränkungen bei der Abfrageföderierung bei Verwendung von EXTERNAL_QUERY

  • SQL-Push-downs werden nur auf föderierte Abfragen vom Typ SELECT * FROM T angewendet.
  • Es werden nur Spaltenbereinigung und Filterabwärtsverschiebung unterstützt. Insbesondere werden Pushdowns für Berechnungen, Joins, Begrenzungen, Sortierungen und Aggregationen nicht unterstützt.
  • Für Filter-Push-downs müssen Literale einen der folgenden Typen haben: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP. Literale, bei denen es sich um Strukturen handelt, werden nicht unterstützt.
  • Pushdowns von SQL-Funktionen werden nur für Funktionen angewendet, die sowohl von BigQuery als auch von einer Zieldatenbank unterstützt werden.
  • SQL-Push-downs werden nur für AlloyDB, Cloud SQL und Spanner unterstützt.
  • SQL-Push-downs werden für SAP Datasphere nicht unterstützt.

Einschränkungen für die Abfrageföderierung bei Verwendung externer Spanner-Datensätze

  • Pushdowns für die Spaltenbeschneidung, Filter, Berechnung und teilweise Aggregation werden unterstützt. Insbesondere werden Joins, Begrenzungen und die Sortierung nach Aggregation nicht unterstützt.
  • Für Filter-Push-downs müssen Literale einen der folgenden Typen haben: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP, BYTE oder Arrays. Literale, bei denen es sich um Strukturen handelt, werden nicht unterstützt.
  • SQL-Funktions-Pushdowns werden nur für Funktionen angewendet, die sowohl von BigQuery als auch von Spanner unterstützt werden.

Unterstützte Funktionen nach Datenquelle

Die folgenden SQL-Funktionen werden nach Datenquelle unterstützt: Für SAP Datasphere werden keine Funktionen unterstützt.

Cloud SQL – MySQL

  • Logische Operatoren: AND, OR, NOT.
  • Vergleichsoperatoren: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Arithmetische Operatoren: +, -, * (nur für INT64 und FLOAT64)

Cloud SQL PostgreSQL und AlloyDB

  • Logische Operatoren: AND, OR, NOT.
  • Vergleichsoperatoren: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Arithmetische Operatoren: +, -, *, / (nur für die Typen INT64, FLOAT64 und DATE, mit Ausnahme der Subtraktion DATE).

Spanner – PostgreSQL-Dialekt

  • Logische Operatoren: AND, OR, NOT.
  • Vergleichsoperatoren: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Arithmetische Operatoren: +, -, *, / (nur für INT64, FLOAT64, NUMERIC)

Spanner – GoogleSQL-Dialekt

  • Logische Operatoren: AND, OR, NOT.
  • Vergleichsoperatoren: =, >, >=, <, <=, <>, IN, BETWEEN, IS NULL.
  • Arithmetische Operatoren: +, -, *, / (nur für INT64, FLOAT64, NUMERIC)
  • Sichere arithmetische Operatoren: SAFE_ADD, SAFE_SUBTRACT, SAFE_MULTIPLY, SAFE_DIVIDE (nur für INT64, FLOAT64, NUMERIC)
  • Bei der Verwendung von externen Datensätzen gilt zusätzlich Folgendes:
    • Compute-Pushdown,
    • Pushdown von partiellen Aggregaten,
    • Stringfunktionen,
    • Mathematische Funktionen,
    • Cast-Funktionen,
    • Arrayfunktionen

Nächste Schritte