Föderierte Cloud SQL-Abfragen

Auf dieser Seite wird erläutert, wie Sie Daten in BigQuery und Cloud SQL mit einer föderierten Abfrage abfragen.

Übersicht

Daten sind oft an vielen Orten verstreut. Möglicherweise speichern Sie eine Kundentabelle in BigQuery und eine Verkaufstabelle in Cloud SQL und möchten die beiden Tabellen in einer einzigen Abfrage verknüpfen.

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. Sie unterstützt sowohl MySQL- (2. Generation) als auch PostgreSQL-Instanzen in Cloud SQL.

Nach der ersten einmaligen Einrichtung können Sie eine Abfrage mit der neuen SQL-Funktion EXTERNAL_QUERY() schreiben.

Workflow

Wählen Sie ein Google Cloud-Projekt aus, das die abzufragende Cloud SQL-Instanz enthält.
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 Funktion EXTERNAL_QUERY() in BigQuery.

Syntax für föderierte Abfragen

Bei föderierten Abfragen wird eine neue Funktion eingeführt: EXTERNAL_QUERY.

Syntax

    SELECT * FROM EXTERNAL_QUERY(connection_id, external_database_query[, options]);

connection_id (String): Der Name der Datenbankverbindungsressource die Sie in der Cloud Console, im bq-Befehlszeilentool oder in der BigQuery API erstellen.

Beispiel für Verbindungs-ID: bigquery-federation-test.us.test-mysql
external_database_query (String): Eine schreibgeschützte Abfrage im SQL-Dialekt der externen Datenbank (MySQL oder PostgreSQL). Die Abfrage wird in der externen Datenbank in Cloud SQL ausgeführt.

Optionen (String): Ein optionaler String einer JSON-Formatzuordnung mit Schlüssel/Wert-Paaren von Optionsnamen und Wert. Bei beiden wird die Groß- und Kleinschreibung berücksichtigt.

Beispieloptionen {"default_type_for_decimal_columns":"numeric"}

Unterstütze Optionen:

Option	Beschreibung
"default_type_for_decimal_columns"	Kann „float64”, „numeric”, „bignumeric” oder „string” sein. Bei dieser Option wird der MySQL-Dezimaltyp oder PostgreSQL vom numerischen Typ dem angegebenen BigQuery-Typ zugeordnet. Wenn diese Option nicht angegeben ist, wird der MySQL-Dezimaltyp oder PostgreSQL vom numerischen Typ dem numerischen Typ von BigQuery zugeordnet.

Beschreibung

EXTERNAL_QUERY führt die Abfrage in Cloud SQL aus und gibt die Ergebnisse als temporäre Tabelle zurück. Der Datentyp der Quelldatenbank (MySQL oder PostgreSQL) wird in der temporären Ergebnistabelle mit der folgenden Datentypzuordnung in den BigQuery-Datentyp konvertiert. Die Funktion EXTERNAL_QUERY wird normalerweise in einer FROM-Klausel verwendet. Diese Funktion ist nur in BigQuery-Standard-SQL verfügbar.

Rückgabetyp

Eine BigQuery-Tabelle.

Beispielabfrage

Nehmen wir an, Sie benötigen das Datum der ersten Bestellung jedes Ihrer Kunden, um es in den Bericht aufzunehmen, den wir in der Übersicht erwähnt haben. Diese Daten befinden sich derzeit nicht in BigQuery, sind jedoch in Ihrer operativen PostgreSQL-Datenbank in Cloud SQL verfügbar. Das folgende Beispiel für föderierte Abfragen ruft diese Daten ab.

SELECT c.customer_id, c.name, SUM(t.amount) AS total_revenue,
rq.first_order_date
FROM customers AS c
INNER JOIN transaction_fact AS t ON c.customer_id = t.customer_id
LEFT OUTER JOIN EXTERNAL_QUERY(
  '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:

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.
Die Ergebnistabelle der externen Abfrage wird mit der Kundentabelle in BigQuery nach customer_id verknüpft.
Die Kundendaten und das Datum der ersten Bestellung werden ausgewählt.

Reihenfolge wird nicht beibehalten

EXTERNAL_QUERY() berücksichtigt nicht die Reihenfolge im Ergebnis externer Abfragen, selbst wenn die externe Abfrage ORDER BY enthält. Die folgende Beispielabfrage sortiert Zeilen in Cloud SQL zwar nach der Kunden-ID, BigQuery gibt die Ergebniszeilen jedoch nicht in dieser Reihenfolge aus.

SELECT * FROM EXTERNAL_QUERY(
'connection_id',
'''SELECT * FROM customers AS c ORDER BY c.customer_id'');

Vorbereitung

BigQuery-Verbindungsdienst aktivieren

Öffnen Sie in der API-Bibliothek die Seite BigQuery Connection API.
Wählen Sie im Drop-down-Menü das Projekt aus, das Ihre Cloud SQL-Instanz enthält.
Klicken Sie auf die Schaltfläche AKTIVIEREN.

BigQuery Connection API

Dienstkonto

BigQuery verwendet ein Dienstkonto, um eine Verbindung zu Ihrer Cloud SQL-Instanz herzustellen. Wenn Sie die BigQuery Connection API aktivieren, wird automatisch ein von Google Cloud verwaltetes IAM-Dienstkonto (Identitäts- und Zugriffsverwaltung) für Sie erstellt. Das Dienstkonto hat die folgenden Rollen:

Rolle	Beschreibung
`cloudsql.client`	Verbindung mit einer Cloud SQL-Instanz herstellen
`logging.logWriter`	In Stackdriver Logging schreiben
`metrics.metricWriter`	In Stackdriver Monitoring schreiben

Berechtigungen

Der Nutzer muss die vordefinierte IAM-Rolle bigquery.admin haben, um eine Verbindungsressource zu erstellen und zu verwalten.
Die Rolle bigquery.admin umfasst die folgenden Berechtigungen für den BigQuery-Verbindungsdienst:
- bigquery.connections.create
- bigquery.connections.get
- bigquery.connections.list
- bigquery.connections.update
- bigquery.connections.use
- bigquery.connections.delete

Wenn Sie einem anderen Nutzer Berechtigungen erteilen möchten, um die Verbindungsressource für Cloud SQL-Abfragen zu verwenden, finden Sie weitere Informationen unter Verbindungsressource freigeben.

`bigquery.admin`-Zugriff gewähren

So weisen Sie die Rolle bigquery.admin zu:

Console

Öffnen Sie in der Cloud Console die Seite „IAM“.
Zur Seite „IAM“
Klicken Sie auf Select a project (Projekt auswählen).
Wählen Sie ein Projekt aus und klicken Sie auf Open (Öffnen).
Klicken Sie auf Add (Hinzufügen), um dem Projekt neue Mitglieder hinzuzufügen, und legen Sie dann ihre Berechtigungen fest.
Gehen Sie im Dialogfeld Add members (Mitglieder hinzufügen) so vor:
- Geben Sie unter Members (Mitglieder) die E-Mail-Adresse des Nutzers oder der Gruppe ein.
- Klicken Sie im Drop-down-Menü Select a role (Rolle wählen) auf BigQuery > BigQuery Admin (BigQuery > BigQuery-Administrator).
- Klicken Sie auf Add (Hinzufügen).

gcloud

Sie können das gcloud-Befehlszeilentool verwenden, um einem Nutzer oder einer Gruppe die Rolle bigquery.admin zu gewähren.

Geben Sie den nachstehenden Befehl ein, um der IAM-Richtlinie Ihres Projekts eine einzelne Bindung hinzuzufügen. Wenn Sie einen Nutzer hinzufügen möchten, geben Sie das Flag --member im Format user:user@example.com an. Wenn Sie eine Gruppe hinzufügen möchten, geben Sie das Flag --member im Format group:group@example.com an.

gcloud projects add-iam-policy-binding project_id \
--member group/user:address \
--role roles/bigquery.admin

Wobei:

project_id ist die Projekt-ID.
group/user ist entweder group oder user.
address die E-Mail-Adresse des Nutzers oder der Gruppe ist.

Beispiel:

gcloud projects add-iam-policy-binding myproject \
--member group:group@example.com \
--role roles/bigquery.admin

Der Befehl gibt die aktualisierte Richtlinie aus:

    bindings:
    - members:
      - group:group@example.com
        role: roles/bigquery.admin

Weitere Informationen zu IAM-Rollen in BigQuery finden Sie unter Vordefinierte Rollen und Berechtigungen.

Öffentliche IP-Adresse

Die Föderation von BigQuery und Cloud SQL unterstützt nur Cloud SQL-Instanzen mit einer öffentlichen IP-Verbindung. Konfigurieren Sie die öffentliche IP-Verbindung für Ihre Cloud SQL-Instanz.

Cloud SQL-Datenbankverbindungen einrichten

Erstellen Sie zuerst eine Datenbankverbindungsressource pro Datenbank in BigQuery und verweisen Sie dann auf die Verbindungsressource in Ihrer föderierten Abfrage. So vermeiden Sie, dass in einer föderierten Abfrage Datenbankanmeldedaten als Klartext enthalten sind.

Die Verbindungsressource verfügt über eine Reihe von IAM-Berechtigungen, die Sie anderen Nutzern erteilen können. Die Verbindungsressource wird verschlüsselt und sicher im BigQuery-Verbindungsdienst gespeichert. Sie kann nur für föderierte Abfragen verwendet werden.

Weitere Informationen zu IAM-Rollen und Berechtigungen in BigQuery finden Sie unter Vordefinierte Rollen und Berechtigungen.

Verbindungsressource erstellen

Console

Rufen Sie zum Erstellen einer Verbindungsressource die BigQuery-Seite in der Cloud Console auf.

Zur Seite "BigQuery"
Wählen Sie im Menü Daten hinzufügen die Option Externe Datenquelle aus.
Geben Sie im Bereich Externe Datenquelle die folgenden Informationen ein:
- Wählen Sie als Verbindungstyp MySQL oder PostgreSQL aus.
- Geben Sie als Connection ID (Verbindungs-ID) eine Kennzeichnung für die Verbindungsressource ein. Buchstaben, Ziffern und Unterstriche sind zulässig.
- Wählen Sie unter Verbindungsstandort einen BigQuery-Standort (oder eine Region) aus, der mit Ihrer Cloud SQL-Instanzregion kompatibel ist.
- Optional: Geben Sie unter Anzeigename einen nutzerfreundlichen Namen für die Verbindung ein, z. B. My connection resource. Der Anzeigename kann ein beliebiger Wert sein, mit dem Sie die Verbindungsressource identifizieren können, wenn Sie sie später ändern müssen.
- Optional: Geben Sie unter Beschreibung eine Beschreibung für diese Verbindungsressource ein.
- Geben Sie als ID für Cloud SQL-Instanz den vollständigen Namen der Cloud SQL-Instanz ein, in der Regel im Format project-id:location-id:instance-id. Sie finden die Instanz-ID auf der Detailseite der Cloud SQL-Instanz, die Sie abfragen möchten.
- Geben Sie unter Datenbankname den Namen der Datenbank ein.
- Geben Sie unter Nutzername den Nutzernamen für die Datenbank ein.
- Geben Sie unter Passwort das Passwort für die Datenbank ein.
  - Optional: Klicken Sie auf das Kästchen Passwort anzeigen, um das Passwort zu sehen.
  Hinweis: Wenn dieselben Nutzeranmeldedaten für andere Datenbanken in der Cloud SQL-Instanz gültig sind, kann dieser Nutzer diese Datenbanken über dieselbe Verbindungsressource abfragen.
Klicken Sie auf Verbindung erstellen.

bq

Geben Sie den Befehl bq mk ein und geben Sie das Verbindungs-Flag --connection an. Die folgenden Flags sind ebenfalls erforderlich:

--connection_type
- --connection_type ist immer CLOUD_SQL.
--properties
--connection_credential
--project_id
--location

Ein Name für die Verbindung. Geben Sie einen Namen ein, um die Verbindung zu identifizieren. Verwenden Sie nur Buchstaben, Ziffern und Unterstriche.

bq mk --connection --connection_type='CLOUD_SQL' --properties=PROPERTIES --connection_credential=CREDENTIALS --project_id=PROJECT_ID --location=LOCATION new_connection

Dabei gilt:

PROPERTIES: die Parameter für die erstellte Verbindung im JSON-Format Beispiel: --properties='{"param":"param_value"}'. Zum Erstellen einer Verbindungsressource müssen Sie die Parameter instanceID, database und type angeben. Die Parameter friendly_name und description sind optional.
CREDENTIALS: die Parameter username und password
PROJECT_ID: Ihre Projekt-ID.
LOCATION: die Region, in der sich Ihre Cloud SQL-Instanz befindet

Mit dem folgenden Befehl wird beispielsweise eine neue Verbindungsressource mit dem Namen "my_new_connection" (Anzeigename: "My new connection") in einem Projekt mit der ID federation-test erstellt.

bq mk --connection --connection_type='CLOUD_SQL' --properties='{"instanceId":"federation-test:us-central1:mytestsql","database":"mydatabase","type":"MYSQL"}' --connection_credential='{"username":"myusername", "password":"mypassword"}' --project_id=federation-test --location=us my_new_connection

API

Innerhalb der BigQuery Connection API können Sie CreateConnection innerhalb von ConnectionService aufrufen, um eine Verbindung zu instanziieren. Weitere Informationen finden Sie auf der Seite der Clientbibliotheken.

Java

Bevor Sie dieses Beispiel anwenden, folgen Sie den Schritten zur Einrichtung von Java in der BigQuery-Kurzanleitung: Clientbibliotheken verwenden. Weitere Informationen finden Sie in der Referenzdokumentation zur BigQuery Java API.

Auf GitHub ansehen Feedback

import com.google.cloud.bigquery.connection.v1.CloudSqlCredential;
import com.google.cloud.bigquery.connection.v1.CloudSqlProperties;
import com.google.cloud.bigquery.connection.v1.Connection;
import com.google.cloud.bigquery.connection.v1.CreateConnectionRequest;
import com.google.cloud.bigquery.connection.v1.LocationName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import java.io.IOException;

// Sample to create a connection with cloud MySql database
public class CreateConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    String database = "MY_DATABASE";
    String instance = "MY_INSTANCE";
    String instanceLocation = "MY_INSTANCE_LOCATION";
    String username = "MY_USERNAME";
    String password = "MY_PASSWORD";
    String instanceId = String.format("%s:%s:%s", projectId, instanceLocation, instance);
    CloudSqlCredential cloudSqlCredential =
        CloudSqlCredential.newBuilder().setUsername(username).setPassword(password).build();
    CloudSqlProperties cloudSqlProperties =
        CloudSqlProperties.newBuilder()
            .setType(CloudSqlProperties.DatabaseType.MYSQL)
            .setDatabase(database)
            .setInstanceId(instanceId)
            .setCredential(cloudSqlCredential)
            .build();
    Connection connection = Connection.newBuilder().setCloudSql(cloudSqlProperties).build();
    createConnection(projectId, location, connectionId, connection);
  }

  public static void createConnection(
      String projectId, String location, String connectionId, Connection connection)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      LocationName parent = LocationName.of(projectId, location);
      CreateConnectionRequest request =
          CreateConnectionRequest.newBuilder()
              .setParent(parent.toString())
              .setConnection(connection)
              .setConnectionId(connectionId)
              .build();
      Connection response = client.createConnection(request);
      System.out.println("Connection created successfully :" + response.getName());
    }
  }
}

Fehlerbehebung

Dieser Abschnitt hilft Ihnen beim Beheben von Problemen, die beim Einrichten einer neuen Verbindung auftreten können. Es werden jedoch nicht alle möglichen Fehlermeldungen oder Probleme behandelt.

Prüfen Sie zum Diagnostizieren allgemeiner Verbindungsfehler Folgendes:

Sie haben alle Schritte im Abschnitt Vorbereitung abgeschlossen.
Die Attribute der Verbindungskonfiguration sind korrekt.
Sie haben die erforderlichen Berechtigungen zum Erstellen einer Verbindung.

Wenn Ihre Verbindungsattribute korrekt sind und die entsprechenden Berechtigungen erteilt wurden, sehen Sie sich die folgenden Lösungen für gängige Probleme an.

Problem: BigQuery und Cloud SQL haben nicht denselben Standort.: Lösung: Föderierte Cloud SQL-Abfragen werden nur in Regionen unterstützt, die sowohl Cloud SQL als auch BigQuery unterstützen. Das BigQuery-Dataset und die Cloud SQL-Instanz müssen sich in derselben Region befinden, oder an einem multiregionalen Standort wie US und EU im selben geografischen Gebiet, das mit der Cloud SQL-Region kompatibel ist. Weitere Informationen zu Regionen und Regionskompatibilität finden Sie unter Unterstützte Regionen.
Problem: Eine neu erstellte Verbindung wird im Projekt nicht angezeigt.: Lösung: Es kann zu einer leichten Verzögerung kommen, bevor neue Verbindungen in der Cloud Console angezeigt werden. Wechseln Sie zu einem anderen Projekt, warten Sie bis zu 30 Sekunden und wechseln Sie dann zurück. Die neue Verbindung sollte jetzt angezeigt werden.
Problem: Die Geschwindigkeit ist langsamer als erwartet.: Lösung: Die Geschwindigkeit von föderierten Abfragen ist nicht so hoch wie das Abfragen von in BigQuery gespeicherten Daten. Dies liegt daran, dass bei föderierten Abfragen Cloud SQL extern abgefragt wird, die Daten an eine temporäre BigQuery-Tabelle zurückgegeben sowie den BigQuery-Datentypen zugeordnet werden und die Abfrage dann in BigQuery ausgeführt wird. Obwohl die Abfragegeschwindigkeit nicht so hoch ist, müssen die Daten nicht noch einmal kopiert, verschoben oder gespeichert werden.
Problem: Wie formatiere ich den Verbindungsnamen?: Lösung: Der Verbindungsname sollte das Projekt, den Standort und die Verbindungs-ID enthalten. Die Verbindungs-ID sollte dem folgenden Muster entsprechen: project_id.location_id.connection_id, z. B. federation-test.us.my_new_connection.

Verbindungsressourcen verwalten

Informationen zum Ansehen, Auflisten, Freigeben, Aktualisieren und Löschen von Verbindungsressourcen finden Sie unter Mit Verbindungen arbeiten.

Audit-Logging

Informationen zum Audit-Logging von Verbindungsressourcen finden Sie in der Übersicht zu BigQuery-Audit-Logs.

Unterstützte Regionen

Föderierte Cloud SQL-Abfragen werden nur in Regionen unterstützt, die sowohl Cloud SQL als auch BigQuery unterstützen.

Standorte oder Regionstypen

Es gibt zwei Arten von Standorten:

Eine Region ist ein bestimmter geografischer Ort wie London.
Eine Multiregion ist ein großes geografisches Gebiet (beispielsweise die USA), das mindestens zwei geografische Orte enthält.

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

Multiregionen

Eine BigQuery-Multiregion kann jede Cloud SQL-Region im selben großen geografischen Gebiet (USA, EU) abfragen. Beispiel:

Die BigQuery-Multiregion US kann jede einzelne Cloud SQL-Region im geografischen Gebiet der USA abfragen, z. B. us-central1, us-east4, us-west2 usw.
Die BigQuery-Multiregion EU kann jede einzelne Cloud SQL-Region in den Mitgliedsstaaten der Europäischen Union abfragen, z. B. europe-north1, europe-west3 usw.
Die in der Abfrage verwendete Verbindung muss sich am selben Standort wie der Abfragespeicherort befinden. Beispielsweise müssen Abfragen, die von der Multiregion US ausgeführt werden, auf eine Verbindung verweisen, die sich in der Multiregion US befindet.

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

Weitere Informationen zu Regionen und Multiregionen finden Sie auf der Seite Dataset-Standorte.

Einzelne Regionen

Eine einzelne BigQuery-Region kann nur Cloud SQL in derselben Region abfragen. Beispiel:

Die einzelne BigQuery-Region us-east4 kann nur Cloud SQL in us-east4 abfragen.

In diesem Beispiel ist der Standort für die Abfrageverarbeitung die einzelne BigQuery-Region.

Der folgenden Übersicht können Sie für das jeweilige Produkt genau entnehmen, welche Regionen unterstützt werden.

Regionale Standorte

	Beschreibung der Region	Cloud SQL-Region	Kompatible BigQuery-Region	Kompatible BigQuery-Multiregion
Amerika
	Iowa	`us-central`	Nicht unterstützt: Diese Region der Cloud SQL-Instanz ist V1. Föderierte Abfragen unterstützen nur V2-Instanzen von Cloud SQL.
	Iowa	`us-central1`	`us-central1`	`US`
	Las Vegas	`us-west4`	`us-west4`	`US`
	Los Angeles	`us-west2`	`us-west2`	`US`
	Montreal	`northamerica-northeast1`	`northamerica-northeast1`	`US`
	Nord-Virginia	`us-east4`	`us-east4`	`US`
	Oregon	`us-west1`	`us-west1`	`US`
	Salt Lake City	`us-west3`	`us-west3`	`US`
	São Paulo	`southamerica-east1`	`southamerica-east1`
	South Carolina	`us-east1`	`us-east1`	`US`
Europa
	Belgien	`europe-west1`	`europe-west1`	`EU`
	Finnland	`europe-north1`	`europe-north1`	`EU`
	Frankfurt	`europe-west3`	`europe-west3`	`EU`
	London	`europe-west2`	`europe-west2`	`EU`
	Netherlands	`europe-west4`	`europe-west4`	`EU`
	Warschau	`europe-central2`	`europe-central2`	`EU`
	Zürich	`europe-west6`	`europe-west6`	`EU`
Asiatisch-pazifischer Raum
	Hongkong	`asia-east2`	`asia-east2`
	Jakarta	`asia-southeast2`	`asia-southeast2`
	Mumbai	`asia-south1`	`asia-south1`
	Osaka	`asia-northeast2`	`asia-northeast2`
	Seoul	`asia-northeast3`	`asia-northeast3`
	Singapur	`asia-southeast1`	`asia-southeast1`
	Sydney	`australia-southeast1`	`australia-southeast1`
	Taiwan	`asia-east1`	`asia-east1`
	Tokio	`asia-northeast1`	`asia-northeast1`

Multiregionale Standorte

Multiregionale Standorte sind für Cloud SQL-Instanzen nicht verfügbar. Cloud SQL-Multiregionen können nicht für föderierte Abfragen verwendet werden.

Daten in der Multiregion EU werden nicht in den Rechenzentren europe-west2 (London) oder europe-west6 (Zürich) gespeichert.

Beschränkungen

Eingeschränkte Cloud SQL-Instanzen

Föderierte Abfragen werden nur von der Cloud SQL-V2-Instanz mit öffentlicher IP-Adresse (im Gegensatz zu einer privaten IP-Adresse) unterstützt.

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 von Cloud SQL nach BigQuery verschiebt. Quelldatenbanken wie MySQL oder PostgreSQL sind normalerweise nicht für komplexe analytische Abfragen optimiert.

Externe 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

Nicht unterstützter MySQL-Datentyp
- Fehlermeldung: Invalid table-valued function external_query Found unsupported MySQL type in BigQuery at [1:15]
- Nicht unterstützter Typ: GEOMETRY, BIT
- Lösung: Wandeln Sie den nicht unterstützten Datentyp in STRING um.
- Beispiel: SELECT ST_AsText(ST_GeomFromText('POINT(1 1)'));. Dieser Befehl wandelt den nicht unterstützten Datentyp GEOMETRY in STRING um.
Nicht unterstützter PostgreSQL-Datentyp
- Fehlermeldung: Invalid table-valued function external_query Postgres type (OID = 790) is not supported now at [1:15]
- Nicht unterstützter Typ: money, time with time zone, inet, path, pg_lsn, point, polygon, tsquery, tsvector, txid_snapshot, uuid, box, cidr, circle, interval, jsonb, line, lseg, macaddr, macaddr8
- Lösung: Wandeln Sie den nicht unterstützten Datentyp in STRING um.
- Beispiel: SELECT CAST('12.34'::float8::numeric::money AS varchar(30));. Dieser Befehl wandelt den nicht unterstützten Datentyp money in STRING um.

Kontingente und Limits

Regionenübergreifende föderierte Abfragen: Wenn sich der BigQuery-Standort zur Abfrageverarbeitung und der Cloud SQL-Instanzstandort unterscheiden, handelt es sich um eine regionenübergreifende Abfrage. Sie können pro Projekt und Tag regionenübergreifende Abfragen in einem Umfang von bis zu 1 TB ausführen.
- Hier ist ein Beispiel für eine regionenübergreifende Abfrage:
  - Die Cloud SQL-Instanz befindet sich in us-west1, während sich die BigQuery-Verbindung in der Multiregion US befindet. Der BigQuery-Standort zur Abfrageverarbeitung ist US.
Kontingent: Nutzer sollten ihr Abfragekontingent in Cloud SQL prüfen. 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 derzeit nicht für föderierte Abfragen unterstützt. Die Berechnung der abgerechneten Byte vor der tatsächlichen Ausführung der föderierten Abfragen ist derzeit nicht möglich.
Es gelten Kontingente und Beschränkungen für Cloud SQL MySQL und PostgreSQL.
Anzahl der Verbindungen: Eine föderierte Abfrage kann höchstens 10 eindeutige Verbindungen haben.

Preise

Bei der Abfrage von Cloud SQL über BigQuery wird Ihnen die Anzahl der von der externen Abfrage zurückgegebenen Byte in Rechnung gestellt. Weitere Informationen finden Sie unter On-Demand-Preise.

Referenz

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

Details zur Verbindungsressource

Name des Attributs	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.

Details zu Anmeldedaten für die Verbindungsressource

Name des Attributs	Wert	Beschreibung
`username`	String	Nutzername der Datenbank.
`password`	String	Passwort der Datenbank.

Datentypzuordnungen

Wenn Sie eine föderierte Cloud SQL-Abfrage ausführen, werden die Daten aus Cloud SQL (als MySQL- oder PostgreSQL-Datentypen) in BigQuery-Standard-SQL-Typen konvertiert. Die folgenden Datentypzuordnungen stammen von MySQL zu BigQuery und von PostgreSQL zu BigQuery.

Wissenswertes über Zuordnungen:

Die meisten MySQL-Datentypen können mit einigen Ausnahmen wie etwa decimal, timestamp und time demselben BigQuery-Datentyp zugeordnet werden.
PostgreSQL unterstützt viele nicht standardmäßige Datentypen, die in BigQuery nicht unterstützt werden, zum Beispiel money, path, uuid, boxer und andere.
Die numerischen Datentypen in MySQL und PostgreSQL werden standardmäßig dem BigQuery-Wert NUMERIC zugeordnet. Der NUMERIC-Wertebereich ist in BigQuery kleiner als in MySQL und PostgreSQL. Sie kann auch mit "default_type_for_decimal_columns" in EXTERNAL_QUERY-Optionen mit BIGNUMERIC, FLOAT64 oder STRING verknüpft werden.

Fehlerbehandlung

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 MySQL/PostgreSQL-Datentyp umwandeln. Weitere Informationen zur Umwandlung finden Sie unter Nicht unterstützte Datentypen im Abschnitt Fehlerbehebung.

Typzuordnung von MySQL zu BigQuery

MySQL-Typ	MySQL-Beschreibung	BigQuery-Typ	Unterschied zwischen Typen
Ganzzahl
INT	4 Byte, 2^32 - 1	INT64
TINYINT	1 Byte, 2^8 - 1	INT64
SMALLINT	2 Byte, 2^16 - 1	INT64
MEDIUMINT	3 Byte, 2^24 - 1	INT64
BIGINT	8 Byte, 2^64 - 1	INT64
UNSIGNED BIGINT	8 Byte, 2^64 - 1	NUMERIC
Genaue numerische Werte
DECIMAL (M,D)	Eine Dezimalzahl wird durch (M,D) dargestellt, wobei M die Gesamtzahl der Stellen und D die Anzahl der Dezimalstellen ist. M <= 65	NUMERIC, BIGNUMERIC, FLOAT64 oder STRING	DECIMAL (M,D) wird standardmäßig NUMERIC zugeordnet oder kann BIGNUMERIC, FLOAT64 oder STRING mit default_type_for_decimal_columns zugeordnet werden.
Ungefähre numerische Werte
FLOAT (M,D)	4 Byte, M <= 23	FLOAT64
DOUBLE (M,D)	8 Byte, M <= 53	FLOAT64
Datum und Uhrzeit
TIMESTAMP	"1970-01-01 00:00:01" UTC bis "2038-01-19 03:14:07" UTC	TIMESTAMP	MySQL TIMESTAMP wird als UTC-Zeitzone abgerufen, unabhängig davon, von wo aus Sie BigQuery aufrufen.
DATETIME	"1000-01-01 00:00:00" bis "9999-12-31 23:59:59".	DATETIME
DATE	"1000-01-01" bis "9999-12-31"	DATE
TIME	Uhrzeit im Format "HH:MM:SS" "-838:59:59" bis "838:59:59"	TIME	Der TIME-Bereich in BigQuery ist kleiner, von 00:00:00 bis 23:59:59
YEAR		INT64
Zeichen und Strings
ENUM	Stringobjekt mit einem Wert aus einer Liste zulässiger Werte	STRING
CHAR (M)	Ein String mit fester Länge zwischen 1 und 255 Zeichen	STRING
VARCHAR (M)	Ein String mit variabler Länge zwischen 1 und 255 Zeichen	STRING
TEXT	Ein Feld mit einer maximalen Länge von 65.535 Zeichen	STRING
TINYTEXT	Eine TEXT-Spalte mit einer maximalen Länge von 255 Zeichen	STRING
MEDIUMTEXT	Eine TEXT-Spalte mit einer maximalen Länge von 16.777.215 Zeichen	STRING
LONGTEXT	Eine TEXT-Spalte mit einer maximalen Länge von 4.294.967.295 Zeichen	STRING
Binär
BLOB	Ein Binary Large Object mit einer maximalen Länge von 65.535 Zeichen	BYTES
MEDIUM_BLOB	Ein BLOB mit einer maximalen Länge von 16.777.215 Zeichen	BYTES
LONG_BLOB	Ein BLOB mit einer maximalen Länge von 4.294.967.295 Zeichen	BYTES
TINY_BLOB	Ein BLOB mit einer maximalen Länge von 255 Zeichen	BYTES
BINARY	Ein String mit fester Länge zwischen 1 und 255 Zeichen	BYTES
VARBINARY	Ein String mit variabler Länge zwischen 1 und 255 Zeichen	BYTES
Andere
SET	Geben Sie beim Deklarieren der SET-Spalte einige Werte vor. Verwenden Sie dann INSERT, um dieser Spalte einen beliebigen Satz vordefinierter Werte hinzuzufügen.	STRING
GEOMETRY		GEOGRAPHY	NOCH NICHT UNTERSTÜTZT
BIT		INT64	NOCH NICHT UNTERSTÜTZT

Typzuordnung von PostgreSQL zu BigQuery

Name	Beschreibung	BigQuery-Typ	Unterschied zwischen Typen
Ganzzahl
smallint	2 Byte, -32.768 bis +32.767	INT64
smallserial	Siehe smallint	INT64
integer	4 Byte, -2.147.483.648 bis +2.147.483.647	INT64
serial	Siehe integer	INT64
bigint	8 Byte, -9.223.372.036.854.775.808 bis 9.223.372.036.854.775.807	INT64
bigserial	Siehe bigint	INT64
Genaue numerische Werte
numeric [ (p, s) ]	Bis zu 1.000 Stellen	NUMERIC, BIGNUMERIC, FLOAT64 oder STRING	Numerisch [ (p, s) ] wird standardmäßig NUMERIC zugeordnet oder kann BIGNUMERIC, FLOAT64 oder STRING mit default_type_for_decimal_columns zugeordnet werden.
Decimal [ (p, s) ]	Siehe numeric	NUMERIC	Siehe numeric
money	8 Byte, zwei Dezimalstellen, -92.233.720.368.547.758,08 bis +92.233.720.368.547.758,07	NICHT UNTERSTÜTZT
Ungefähre numerische Werte
real	4 Byte, Gleitkommazahl mit einfacher Genauigkeit	FLOAT64
double precision	8 Byte, Gleitkommazahl mit doppelter Genauigkeit	FLOAT64
Datum und Uhrzeit
date	Kalenderdatum (Jahr, Monat, Tag)	DATE
time [ (p) ] [ ohne Zeitzone ]	Uhrzeit (ohne Zeitzone)	TIME
time [ (p) ] mit Zeitzone	Uhrzeit, mit Zeitzone	NICHT UNTERSTÜTZT
timestamp [ (p) ] [ ohne Zeitzone ]	Datum und Uhrzeit (ohne Zeitzone)	DATETIME
timestamp [ (p) ] mit Zeitzone	Datum und Uhrzeit, mit Zeitzone	TIMESTAMP	PostgreSQL TIMESTAMP wird als UTC-Zeitzone abgerufen, unabhängig davon, von wo aus Sie BigQuery aufrufen.
interval	Eine Zeitdauer	NICHT UNTERSTÜTZT
Zeichen und Strings
character [ (n) ]	Zeichenstring mit fester Länge	STRING
character varying [ (n) ]	Zeichenstring mit variabler Länge	STRING
text	Zeichenstring mit variabler Länge	STRING
Binär
bytea	Binärdaten ("Bytearray")	BYTES
bit [ (n) ]	Bitfolge mit fester Länge	BYTES
bit varying [ (n) ]	Bitfolge mit variabler Länge	BYTES
Andere
boolean	Logischer boolescher Wert (wahr/falsch)	BOOL
inet	IPv4- oder IPv6-Hostadresse	NICHT UNTERSTÜTZT
path	Geometrischer Pfad auf einer Ebene	NICHT UNTERSTÜTZT
pg_lsn	PostgreSQL-Logsequenznummer	NICHT UNTERSTÜTZT
point	Geometrischer Punkt auf einer Ebene	NICHT UNTERSTÜTZT
polygon	Geschlossener geometrischer Pfad auf einer Ebene	NICHT UNTERSTÜTZT
tsquery	Textsuchabfrage	NICHT UNTERSTÜTZT
tsvector	Textsuchdokument	NICHT UNTERSTÜTZT
txid_snapshot	Snapshot der Transaktions-ID auf Nutzerebene	NICHT UNTERSTÜTZT
uuid	Universell eindeutige Kennzeichnung	NICHT UNTERSTÜTZT
xml	XML-Daten.	STRING
box	Rechteckiges Feld auf einer Ebene	NICHT UNTERSTÜTZT
cidr	IPv4- oder IPv6-Netzwerkadresse	NICHT UNTERSTÜTZT
Kreis	Kreis auf einer Ebene	NICHT UNTERSTÜTZT
interval [ Felder ] [ (p) ]	Zeitspanne	NICHT UNTERSTÜTZT
json	JSON-Textdaten	STRING
jsonb	JSON-Binärdaten, zerlegt	NICHT UNTERSTÜTZT
Linie	Unendliche Linie auf einer Ebene	NICHT UNTERSTÜTZT
lseg	Liniensegment auf einer Ebene	NICHT UNTERSTÜTZT
macaddr	MAC-Adresse (Media Access Control)	NICHT UNTERSTÜTZT
macaddr8	MAC-Adresse (Media Access Control) (EUI-64-Format)	NICHT UNTERSTÜTZT

Föderierte Cloud SQL-Abfragen

Übersicht

Workflow

Syntax für föderierte Abfragen

Syntax

Beschreibung

Rückgabetyp

Beispielabfrage

Reihenfolge wird nicht beibehalten

Vorbereitung

BigQuery-Verbindungsdienst aktivieren

Dienstkonto

Berechtigungen

bigquery.admin-Zugriff gewähren

Console

gcloud

Öffentliche IP-Adresse

Cloud SQL-Datenbankverbindungen einrichten

Verbindungsressource erstellen

Console

bq

API

Java

Fehlerbehebung

Verbindungsressourcen verwalten

Audit-Logging

Unterstützte Regionen

Standorte oder Regionstypen

Regionale Standorte

Multiregionale Standorte

Beschränkungen

Eingeschränkte Cloud SQL-Instanzen

Leistung

Externe Abfragen sind schreibgeschützt

Nicht unterstützte Datentypen

Kontingente und Limits

Preise

Referenz

Cloud SQL-Tabellenschema ansehen

Details zur Verbindungsressource

Details zu Anmeldedaten für die Verbindungsressource

Datentypzuordnungen

`bigquery.admin`-Zugriff gewähren