Föderierte Cloud SQL-Abfragen

Mit föderierten Abfragen in BigQuery können Sie Daten in Cloud SQL in Echtzeit abfragen, ohne Daten kopieren oder verschieben zu müssen. Föderierte Abfragen unterstützen sowohl Cloud SQL for MySQL- als auch Cloud SQL for PostgreSQL-Instanzen in Cloud SQL.

Zum Replizieren von Daten nach BigQuery können Sie auch Cloud Data Fusion oder Datastream verwenden. Weitere Informationen zur Verwendung von Cloud Data Fusion finden Sie unter Daten von MySQL in BigQuery replizieren.

Hinweis

So führen Sie föderierte Abfragen für Ihre Daten in Cloud SQL aus:

  1. Achten Sie darauf, dass Ihr BigQuery-Administrator eine Cloud SQL-Verbindung erstellt und die Verbindung für Sie freigegeben hat.
  2. Prüfen Sie, ob dem BigQuery Connection-Dienst-Agent die Rolle „Cloud SQL-Client“ (roles/cloudsql.client) zugewiesen wurde. Weitere Informationen finden Sie unter Zugriff auf den Dienst-Agent gewähren.
  3. Wenn Ihre Cloud SQL-Instanz eine private IP-Adresse verwendet, muss beim Erstellen der Instanz ein privater Pfad aktiviert worden sein. Weitere Informationen finden Sie unter Verbindung zu Cloud SQL herstellen und Private IP-Adresse konfigurieren.

Erforderliche Rollen

Bitten Sie Ihren Administrator, Ihnen die IAM-Rolle BigQuery-Verbindungsnutzer (roles/bigquery.connectionUser) für Ihr Projekt zuzuweisen, um die Berechtigungen zu erhalten, die Sie zum Abfragen einer Cloud SQL-Instanz benötigen. Weitere Informationen zum Zuweisen von Rollen finden Sie unter Zugriff auf Projekte, Ordner und Organisationen verwalten.

Sie können die erforderlichen Berechtigungen auch über benutzerdefinierte Rollen oder andere vordefinierte Rollen erhalten.

Daten abfragen

Verwenden Sie die Funktion EXTERNAL_QUERY, um eine föderierte Abfrage aus einer GoogleSQL-Abfrage an Cloud SQL zu senden.

Format der Verbindungskennung

Das erste Argument der Funktion EXTERNAL_QUERY ist die Verbindungskennung. Sie können die Verbindungskennung in einem der folgenden Formate angeben:

  • Kurzformat: [LOCATION.]CONNECTION_ID (z. B. 'us.my-connection' oder 'my-connection')
  • Voll qualifiziertes Format: projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID
  • Benutzerfreundliches Pfadformat: PROJECT_ID.LOCATION.CONNECTION_ID (z. B. 'my-project.us.my-connection')

Wenn Sie eine Abfrage in einer freigegebenen Ansicht über mehrere Projekte hinweg ausführen, verwenden Sie immer die vollständig qualifizierte Verbindungs-ID, um eine Verbindung zum falschen Projekt zu vermeiden.

Föderierte Abfrage ausführen

Angenommen, Sie speichern eine Kundentabelle in BigQuery und eine Bestelltabelle in Cloud SQL und möchten die beiden Tabellen in einer einzigen Abfrage verknüpfen. Im folgenden Beispiel wird eine föderierte Abfrage an eine Cloud SQL-Tabelle namens orders gesendet und die Ergebnisse werden mit einer BigQuery-Tabelle namens mydataset.customers verknüpft.

Wählen Sie eine der folgenden Optionen aus, um die föderierte Abfrage auszuführen:

Console

  1. Rufen Sie in der Google Cloud Console die Seite BigQuery auf.

    BigQuery aufrufen

  2. Geben Sie im Abfrageeditor die folgende Abfrage ein:

    SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.CONNECTION_ID',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

    Ersetzen Sie CONNECTION_ID durch die ID Ihrer BigQuery-Verbindung.

  3. Klicken Sie auf Ausführen.

    Die Abfrageergebnisse werden im Bereich Abfrageergebnisse angezeigt.

bq

  2. Aktivieren Sie Cloud Shell in der Google Cloud Console.

    Cloud Shell aktivieren

    Unten in der Google Cloud Console wird eine Cloud Shell-Sitzung gestartet und eine Eingabeaufforderung angezeigt. Cloud Shell ist eine Shell-Umgebung, in der das Google Cloud CLI bereits installiert ist und Werte für Ihr aktuelles Projekt bereits festgelegt sind. Das Initialisieren der Sitzung kann einige Sekunden dauern.

  3. Führen Sie den Befehl bq query mit dem Flag --use_legacy_sql=false aus:

    bq query \
    --use_legacy_sql=false \
    'SELECT c.customer_id, c.name, rq.first_order_date
    FROM mydataset.customers AS c
    LEFT OUTER JOIN EXTERNAL_QUERY(
      "us.CONNECTION_ID",
      """SELECT customer_id, MIN(order_date) AS first_order_date
      FROM orders
      GROUP BY customer_id""") AS rq ON rq.customer_id = c.customer_id
    GROUP BY c.customer_id, c.name, rq.first_order_date;'

    Ersetzen Sie CONNECTION_ID durch die ID Ihrer BigQuery-Verbindung.

Die Beispielabfrage besteht aus 3 Teilen:

  1. Die externe Abfrage SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id wird in der operativen Cloud SQL-Datenbank ausgeführt, um das erste Bestelldatum für jeden Kunden über die Funktion EXTERNAL_QUERY() abzurufen.
  2. Verknüpfen Sie die Ergebnistabelle der externen Abfrage mit der Kundentabelle in BigQuery anhand von customer_id.
  3. Die Kundendaten und das Datum der ersten Bestellung werden ausgewählt.

Cloud SQL-Tabellenschema ansehen

Mit der Funktion EXTERNAL_QUERY() können Sie INFORMATION_SCHEMA-Ansichten abfragen, um auf Datenbankmetadaten zuzugreifen, z. B. um alle Tabellen in der Datenbank aufzulisten oder Tabellenschemas anzuzeigen. Die folgenden INFORMATION_SCHEMA-Beispielabfragen funktionieren sowohl in MySQL als auch in PostgreSQL. Weitere Informationen finden Sie in den Artikeln zu information_schema-Tabellen in MySQL bzw. information_schema-Tabellen in PostgreSQL.

-- List all tables in a database.
SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID",
"SELECT * FROM information_schema.tables;");

-- List all columns in a table.
SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID",
"SELECT * FROM information_schema.columns WHERE table_name='x';");

Ersetzen Sie CONNECTION_ID durch die ID Ihrer BigQuery-Verbindung.

Föderierte BigQuery-Abfragen verfolgen

Wenn Sie eine föderierte Abfrage für Cloud SQL ausführen, fügt BigQuery der Abfrage einen Kommentar wie den folgenden hinzu:

/* Federated query from BigQuery. Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID. */

Wenn Sie Logs zur Abfragenutzung in einer MySQL- oder PostgreSQL-Datenbank überwachen, können Sie mithilfe der Annotation Abfragen identifizieren, die von BigQuery stammen.

  1. Rufen Sie in der Google Cloud Console die Seite Log-Explorer auf.

    Zum Log-Explorer

  2. Geben Sie auf dem Tab Abfrage die folgende Abfrage ein:

    resource.type="cloudsql_database"
textPayload=~"Federated query from BigQuery"

  3. Klicken Sie auf Abfrage ausführen.

    Wenn Datensätze für föderierte BigQuery-Abfragen verfügbar sind, wird unter Abfrageergebnisse eine Liste mit Datensätzen angezeigt, die in etwa so aussieht:

    YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT "company_id", "company type_id" FROM
(SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

Verbindungsdetails

Die folgende Tabelle zeigt die Cloud SQL-Verbindungsattribute:

Eigenschaft Wert Beschreibung
name String Name der Verbindungsressource im Format: projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID.
location String Standort der Verbindung, der entweder mit dem Standort der Cloud SQL-Instanz übereinstimmen oder ein multiregionaler Standort der entsprechenden Gerichtsbarkeit sein muss. Beispiel: Eine Cloud SQL-Instanz in us-east4 kann US verwenden, während eine Cloud SQL-Instanz in europe-north1 EU verwenden kann. Nur BigQuery-Abfragen, die an diesem Standort ausgeführt werden, können diese Verbindung verwenden.
friendlyName String Ein nutzerfreundlicher Anzeigename für die Verbindung.
description String Beschreibung der Verbindung.
cloudSql.type String Kann POSTGRES oder MYSQL sein.
cloudSql.instanceId String Name der Cloud SQL-Instanz, normalerweise im Format PROJECT_ID:LOCATION:INSTANCE_ID. Sie finden die Instanz-ID auf der Seite Cloud SQL-Instanzen.
cloudSql.database String Die Cloud SQL-Datenbank, zu der eine Verbindung hergestellt werden soll.
cloudSql.serviceAccountId String Das Dienstkonto, das für den Zugriff auf die Cloud SQL-Datenbank konfiguriert ist.

In der folgenden Tabelle sind die Attribute der Anmeldedaten der Cloud SQL-Instanz aufgeführt:

Eigenschaft Wert Beschreibung
username String Nutzername für Datenbank.
password String Datenbankpasswort.

Abfrageleistung und Best Practices

Beachten Sie die folgenden Best Practices, um die Leistung föderierter Abfragen zu optimieren und eine unnötige Belastung Ihrer Cloud SQL-Instanz zu vermeiden:

  • Früh in der externen Abfrage filtern: Wenden Sie WHERE-Filter und GROUP BY-Aggregationen innerhalb der EXTERNAL_QUERY-SQL-Anweisung an, anstatt in der äußeren BigQuery-Abfrage zu filtern. Durch das Ausführen von Filtern in Cloud SQL wird die Menge der über das Netzwerk an BigQuery übertragenen Daten reduziert.
  • Spalten bereinigen: Wählen Sie nur die spezifischen Spalten aus, die in der EXTERNAL_QUERY-Anweisung benötigt werden, anstatt SELECT * zu verwenden. Dadurch werden das Netzwerkübertragungsvolumen und die Abfrageausführungszeit reduziert.
  • Lesereplikate verwenden: Wenn Sie analytische Abfragearbeitslasten von Ihrer primären Produktionsdatenbank isolieren möchten, erstellen Sie ein Cloud SQL-Lesereplikat und konfigurieren Sie Ihre BigQuery-Verbindung so, dass das Replikat abgefragt wird.
  • Datasets und Instanzen am selben Standort platzieren: Für die geringste Latenz sollten Sie Ihr BigQuery-Dataset und Ihre Verbindung in derselben Region wie Ihre Cloud SQL-Instanz oder in der entsprechenden Multiregion (z. B. US oder EU) platzieren. Weitere Informationen finden Sie unter Unterstützte Regionen.

Fehlerbehebung

Dieser Abschnitt hilft Ihnen beim Beheben von Problemen, die beim Senden einer föderierten Abfrage an Cloud SQL auftreten können.

Verbindung zum Datenbankserver konnte nicht hergestellt werden

Wenn Sie eine MySQL-Datenbank abfragen, kann der folgende Fehler auftreten:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.

Wenn Sie eine PostgreSQL-Datenbank abfragen, kann der folgende Fehler auftreten:

Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

Lösung

Prüfen Sie die folgenden Voraussetzungen, um dieses Problem zu beheben:

  1. Prüfen Sie, ob gültige Anmeldedaten verwendet wurden und alle Einrichtungsschritte beim Erstellen der Verbindung für Cloud SQL ausgeführt wurden.

  2. Prüfen Sie, ob dem BigQuery Connection-Dienst-Agent die Rolle „Cloud SQL-Client“ (roles/cloudsql.client) zugewiesen wurde. Die E-Mail-Adresse des Dienst-Agents hat das folgende Format:

    service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.

    Eine ausführliche Anleitung finden Sie unter Zugriff auf den Dienst-Agent gewähren.

  3. Wenn Ihre Cloud SQL-Instanz eine private IP-Adresse verwendet, müssen Sie beim Erstellen der Cloud SQL-Instanz einen privaten Pfad aktiviert haben. Wenn Sie einen privaten Pfad aktivieren, kann BigQuery auf Daten in Cloud SQL zugreifen und Abfragen über eine private Verbindung ausführen.

Verbindung zur MySQL-Datenbank aufgrund des Authentifizierungs-Plug-ins fehlgeschlagen

Wenn Sie eine föderierte Abfrage für eine MySQL-Instanz ausführen, kann der folgende Fehler auftreten:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2059): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at [1:15]

Lösung

Dieser Fehler tritt bei Datenbanknutzerkonten auf, die mit der Authentifizierung „caching_sha2_password“ konfiguriert sind. Diese wird für föderierte Abfragen nicht unterstützt.

Wenn auf Ihrer Cloud SQL-Instanz MySQL Version 8.0 oder früher ausgeführt wird, können Sie das Authentifizierungs-Plug-in für das von BigQuery verwendete Nutzerkonto ändern. Führen Sie den folgenden Befehl auf der Cloud SQL-Instanz aus:

ALTER USER 'USERNAME'@'%' IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

Ersetzen Sie Folgendes:

  • USERNAME: Das Datenbanknutzerkonto, das BigQuery verwendet, um eine Verbindung zu Ihrer Cloud SQL for MySQL-Instanz herzustellen.
  • PASSWORD: Das Passwort für das Datenbanknutzerkonto.

Wenn Ihre Cloud SQL-Instanz MySQL Version 8.4 ausführt, ist das Plug-in mysql_native_password veraltet. Wir raten davon ab, eine vorhandene Datenbank für Produktionsarbeitslasten von 8.4 auf 8.0 herabzustufen.

Das Upgrade einer Instanz von einer früheren Version auf MySQL 8.4 hat keine Auswirkungen auf vorhandene Datenbankverbindungen, sofern Sie das Nutzerauthentifizierungs-Plug-in vor dem Upgrade nicht ändern. Da nur mit mysql_native_password erstellte Nutzer für föderierte Abfragen verwendet werden können, müssen Abfragen, die an MySQL 8.4 gesendet werden, ein Nutzerkonto verwenden, das vor dem Upgrade erstellt wurde.

Beschränkungen

Föderierte Abfragen in Cloud SQL unterliegen den folgenden Einschränkungen:

  • Lesezugriff-Abfragen: Föderierte Abfragen sind schreibgeschützt. Externe Abfragen müssen SELECT-Anweisungen sein. DDL-Anweisungen (Datendefinitionssprache) und DML-Anweisungen (Datenbearbeitungssprache) werden nicht unterstützt.
  • Abfrageleistung: Föderierte Abfragen sind nicht so schnell wie Abfragen im BigQuery-Speicher. BigQuery muss warten, bis Cloud SQL die externe Abfrage ausgeführt und die Ergebnismenge über das Netzwerk übertragen hat.
  • Regionale Einschränkungen: Der Standort für die BigQuery-Abfrageverarbeitung muss mit dem Standort der Cloud SQL-Instanz übereinstimmen oder kompatibel sein. Weitere Informationen finden Sie unter Unterstützte Regionen.
  • Nicht unterstützte Datentypen: Wenn eine externe Abfrage einen Datentyp zurückgibt, der von BigQuery nicht unterstützt wird, schlägt die Abfrage fehl. Sie können den nicht unterstützten Datentyp in der externen Abfrage in einen unterstützten Datentyp umwandeln. Weitere Informationen finden Sie unter Datentypzuordnungen.
  • Maximale Anzahl von Verbindungen: Eine föderierte Abfrage kann auf maximal 10 eindeutige Verbindungen verweisen.
  • Einschränkung des Authentifizierungs-Plug-ins: Das MySQL-Plug-in caching_sha2_password wird für föderierte Abfragen nicht unterstützt. Weitere Informationen finden Sie unter Fehlerbehebung.

Nächste Schritte