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 Datasets

Eine weitere Möglichkeit, operative Datenbanken wie Bigtable, Spanner, Cloud Storage, Google Drive und Salesforce Data Cloud abzufragen, ist die Verwendung externer Tabellen und Datasets. Mit externen Datasets und Tabellen können Sie Tabellen und ihre Schemas ansehen und abfragen, ohne eine EXTERNAL_QUERY-SQL-Funktion verwenden zu müssen. Sie müssen keine Daten zurück in BigQuery übertragen und können die BigQuery-Syntax verwenden, anstatt im spezifischen SQL-Datenbankdialekt von SQL zu schreiben.

Unterstützte Regionen

Eine Liste der unterstützten Standorte finden Sie in den folgenden Abschnitten:

AlloyDB und Cloud SQL

Föderierte Abfragen werden nur in Regionen unterstützt, die sowohl die externe Datenquelle als auch BigQuery unterstützen.

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 sich Ihr Dataset beispielsweise in us-east4 befindet, können Sie Cloud SQL-Instanzen oder AlloyDB-Instanzen 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 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.

Spanner

Für Spanner werden sowohl regionale als auch multiregionale Konfigurationen unterstützt. Eine einzelne BigQuery-Region oder eine BigQuery-Multiregion kann eine Spanner-Instanz in einer beliebigen unterstützten Spanner-Region abfragen. Weitere Informationen finden Sie unter regionenübergreifende Abfragen.

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 Menge abgerechneter Byte Dieses Feld wird nicht für föderierte Abfragen 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.

  • Vom Kunden verwaltete Verschlüsselungsschlüssel (Customer Managed Encryption Keys, CMEK). CMEK wird separat für BigQuery und für externe Datenquellen konfiguriert. Wenn Sie die Quelldatenbank für die Verwendung von CMEK konfigurieren, aber nicht BigQuery, wird die temporäre Tabelle, die die Ergebnisse einer föderierten Abfrage enthält, mit einem Google-owned and Google-managed encryption keyverschlüsselt.

Preise

  • Wenn Sie das On-Demand-Preismodell verwenden, wird Ihnen die Anzahl der Byte berechnet, 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-Pushdowns

Föderierte Abfragen unterliegen der Optimierungstechnik „SQL Pushdown“. 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 aus der externen Datenquelle übertragenen Daten reduzieren, können Sie die Ausführungszeit von Abfragen verkürzen und die Kosten senken. SQL-Push-downs umfassen das Entfernen von Spalten (SELECT-Klauseln) und das Verschieben von Filtern (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_ID", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

Ersetzen Sie CONNECTION_ID durch die ID der BigQuery-Verbindung.

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

Wenn diese Abfrage ausgeführt wird, werden nur zwei Spalten und die Zeilen, die dem Filterprädikat entsprechen, an BigQuery zurückgesendet.

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

Angewendete Pushdowns (falls vorhanden) können Sie im Abfrageplan sehen.

Beschränkungen

SQL-Pushdowns unterliegen verschiedenen Einschränkungen, die je nach externer Datenquelle und Art der Datenabfrage variieren.

Einschränkungen für die Abfrageföderation bei Verwendung von EXTERNAL_QUERY

  • SQL-Push-downs werden nur auf föderierte Abfragen der Form SELECT * FROM T angewendet.
  • Es werden nur das Entfernen von Spalten und das Verschieben von Filtern unterstützt. Insbesondere werden keine Pushdowns für Berechnungen, Joins, Grenzwerte, Sortierungen und Aggregationen 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.
  • SQL-Funktions-Pushdowns 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öderation bei Verwendung externer Spanner-Datasets

  • Das Auslagern von Spaltenbeschneidung, Filtern, Berechnungen und teilweisen Aggregationen wird unterstützt. Insbesondere werden die Aggregationsfunktionen „join“, „limit“ und „order by“ 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-Push-downs 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)
  • Wenn Sie externe Datasets verwenden, gilt zusätzlich Folgendes:
    • Pushdown von Berechnungen
    • Partial Aggregate-Pushdown,
    • Stringfunktionen
    • Mathematische Funktionen
    • Umwandlungsfunktionen
    • Arrayfunktionen.

Mit Sortierungen in externen Datenquellen arbeiten

Für eine Spalte in einer externen Datenquelle kann eine Sortierung festgelegt sein, z. B. die Berücksichtigung der Groß-/Kleinschreibung. Wenn Sie eine föderierte Abfrage ausführen, berücksichtigt die Remote-Datenbank die konfigurierte Sortierung.

Sehen Sie sich das folgende Beispiel an, in dem Sie in der externen Datenquelle eine flag-Spalte mit einer Sortierung haben, bei der die Groß-/Kleinschreibung nicht berücksichtigt wird:

SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table where flag = 'Y'")

Ersetzen Sie CONNECTION_ID durch die ID der BigQuery-Verbindung.

Die vorhergehende Abfrage gibt Zeilen zurück, in denen flag gleich y oder Y ist, da die Abfrage für die externe Datenquelle ausgeführt wird.

Wenn Sie jedoch bei der Abfrageföderation mit Cloud SQL-, SAP Datasphere- oder AlloyDB-Datenquellen einen Filter in Ihre Hauptabfrage einfügen, wird die Abfrage mit der Standardsortierung auf der BigQuery-Seite ausgeführt. Hier ein Beispiel:

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE flag = 'Y'

Aufgrund der standardmäßigen Sortierung in BigQuery, bei der die Groß-/Kleinschreibung beachtet wird, werden bei der vorherigen Abfrage nur Zeilen zurückgegeben, in denen das Flag Y ist. Zeilen, in denen das Flag y ist, werden herausgefiltert. Wenn Sie die WHERE-Klausel unabhängig von der Groß-/Kleinschreibung verwenden möchten, geben Sie die Sortierung in der Abfrage an:

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE COLLATE(flag, 'und:ci') = 'Y'

Nächste Schritte