Föderierte Cloud SQL-Abfragen

Als Datenanalyst können Sie Daten in Cloud SQL mithilfe von föderierten Abfragen über BigQuery abfragen.

Mit der Föderation von BigQuery und Cloud SQL kann BigQuery Daten in Cloud SQL in Echtzeit abfragen, ohne Daten kopieren oder verschieben zu müssen. Föderierte Abfragen unterstützen sowohl MySQL- (2. Generation) als auch 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

Daten abfragen

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

Angenommen Sie speichern eine Kundentabelle in BigQuery und eine Verkaufstabelle 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.

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;

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 PostgreSQL-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-Tabellen abfragen, um auf Datenbankmetadaten zuzugreifen, z. B. um eine Liste aller Tabellen in der Datenbank oder das Tabellenschema abzurufen. 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';");

Verbindungsdetails

Die folgende Tabelle zeigt die Cloud SQL-Verbindungsattribute:

Eigenschaft Wert Beschreibung
name String Name der Verbindungsressource im Format project_id.location_id.connection_id.
location String Standort der Verbindung. Er entspricht dem Standort der Cloud SQL-Instanz mit den folgenden Ausnahmen: Cloud SQL us-central1 wird BigQuery US zugeordnet, Cloud SQL europe-west1 wird BigQuery EU zugeordnet.
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, in der Regel im folgenden Format:

Project-id:location-id:instance-id

Sie finden die Instanz-ID auf der Detailseite der Cloud SQL-Instanz.
cloudSql.database String Die Cloud SQL-Datenbank, zu der Sie eine Verbindung herstellen möchten.
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 der Datenbank.
password String Passwort für Datenbank

Föderierte BigQuery-Abfragen verfolgen

Wenn Sie eine föderierte Abfrage für Cloud SQL ausführen, annotiert BigQuery die Abfrage mit einem Kommentar ähnlich dem folgenden:

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

Wenn Sie Logs für die Abfragenutzung in einer MySQL- oder PostgreSQL-Datenbank überwachen, können Sie mit der folgenden Annotation Abfragen identifizieren, die aus BigQuery stammen.

  1. Rufen Sie die Seite Log-Explorer auf.

    Zu „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 für föderierte BigQuery-Abfragen Datensätze verfügbar sind, wird unter Abfrageergebnisse eine Liste mit Datensätzen ähnlich der folgenden angezeigt:

    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
*/

Fehlerbehebung

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

Problem: 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 alternativ 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, ob gültige Anmeldedaten verwendet wurden und alle Voraussetzungen erfüllt sind, um die Verbindung für Cloud SQL zu erstellen. Prüfen Sie, ob das automatisch erstellte Dienstkonto beim Erstellen einer Verbindung zu Cloud SQL die Rolle „Cloud SQL-Client“ (roles/cloudsql.client) hat. Dieses Dienstkonto hat das folgende Format: service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com. Eine ausführliche Anleitung finden Sie unter Zugriff auf das Dienstkonto gewähren.

Nächste Schritte