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.

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 nur 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 externe Standort der 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 das 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.
  • Zulässige maximale Menge abgerechneter 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 die Anzahl der Byte in Rechnung gestellt, die von der externen Abfrage zurückgegeben werden, wenn Sie föderierte Abfragen über BigQuery ausführen. 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-Push-downs

Föderierte Abfragen unterliegen der Optimierungsmethode, die als SQL-Push-down bezeichnet wird. Sie verbessern die Leistung einer Abfrage, indem Vorgänge wie das Filtern an die externe Datenquelle delegiert werden, anstatt sie in BigQuery auszuführen. Wenn Sie die von der externen Datenquelle übertragene Datenmenge reduzieren, können Sie die Ausführungszeit der Abfrage und die Kosten senken. SQL-Push-downs umfassen Spaltenbereinigung (SELECT-Klauseln) und Filter-Push-downs (WHERE-Klauseln).

Wenn Sie die Funktion EXTERNAL_QUERY verwenden, wird bei SQL-Push-downs die ursprüngliche Abfrage umgeschrieben. Im folgenden Beispiel wird die Funktion EXTERNAL_QUERY für die Kommunikation mit einer Cloud SQL-Datenbank verwendet:

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

Ohne SQL-Push-downs 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, obwohl nur einige Zeilen und Spalten benötigt werden.

Bei SQL-Push-downs 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'))

Bei dieser Abfrage werden nur zwei Spalten und die Zeilen, die mit dem Filterprädikat übereinstimmen, an BigQuery zurückgesendet.

Sie können angewendete Push-downs (falls vorhanden) im Abfrageplan prüfen.

Beschränkungen

  • SQL-Push-downs werden nur auf föderierte Abfragen der Form SELECT * FROM T angewendet.
  • Es werden nur Spaltenbereinigungen und Filter-Push-downs unterstützt. Computing-, Join- und Aggregations-Push-downs werden 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 oder Arrays handelt, werden nicht unterstützt.
  • 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.

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

Von Cloud SQL PostgreSQL und AlloyDB unterstützte Funktionen

  • 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

Der GoogleSQL-Dialekt unterstützt dieselben Funktionen wie der PostgreSQL-Dialekt und zusätzlich:

  • Sichere arithmetische Operatoren: SAFE_ADD, SAFE_SUBTRACT, SAFE_MULTIPLY, SAFE_DIVIDE (nur für INT64, FLOAT64, NUMERIC)

Nächste Schritte