Föderierte MySQL-Abfragen ausführen

Als Datenanalyst können Sie relationale Daten in MySQL direkt über BigQuery in Echtzeit abfragen, ohne Daten kopieren oder verschieben zu müssen.

Die BigQuery-MySQL-Föderation verwendet die BigQuery Connection API, um eine Verbindung zu MySQL-Datenbank-Engines herzustellen. Anschließend können Sie die Funktion EXTERNAL_QUERY verwenden, um Ihre SQL-Abfrage direkt zur Ausführung an MySQL zu senden. Die Ergebnisse werden dann direkt zurückgestreamt.

Beschränkungen

Für föderierte MySQL-Abfragen gelten die folgenden Einschränkungen:

  • Sie können nur schreibgeschützte Abfragen über die Funktion EXTERNAL_QUERY ausführen. Anweisungen der Datendefinitionssprache (DDL) und der Datenbearbeitungssprache (DML) werden nicht unterstützt.
  • Der Zugriff auf die Föderation ist nur über die Funktion EXTERNAL_QUERY möglich. Das Erstellen von externen BigQuery-Tabellen oder permanenten Datasets, die direkt MySQL-Tabellen zugeordnet sind, wird nicht unterstützt.
  • Die Latenz föderierter Abfragen kann deutlich höher sein als die direkte Ausführung der Abfrage in MySQL.
  • Der zugrunde liegende Verbindungsdienst kann inaktive Verbindungen in den Ruhezustand versetzen. Die erste Abfrage, die nach einer Inaktivitätsphase eine MySQL-Verbindung verwendet, kann mehr als eine Minute dauern, während der Connector initialisiert wird.
  • DNS-Peering wird nicht unterstützt. Sie müssen eine Verbindung über eine IP-Adresse oder einen vollständig auflösbaren Hostnamen in Ihrem VPC-Netzwerk herstellen.
  • In der MySQL-SQL-Abfrage müssen explizite Spalten-Aliasse für alle Spalten angegeben werden, die Funktionsergebnisse oder Ausdrücke enthalten (z. B. SELECT COUNT(*) AS total_count). Ausdrücke ohne Alias erzeugen Spaltennamen mit Sonderzeichen, die in BigQuery-Tabellenschemadefinitionen ungültig sind.
  • Wenn die Nutzung der Compute Engine API im Abfrageprojekt durch VPC Service Controls eingeschränkt ist, schlägt die föderierte Abfrage fehl, wenn die Verbindung einen Netzwerkanhang verwendet. Achten Sie darauf, dass die entsprechenden Regeln für ein- und ausgehenden Traffic von VPC Service Controls konfiguriert sind.

  • Für MySQL werden keine zusätzlichen SQL-Push-downs unterstützt. Filter und Aggregationen in der äußeren BigQuery-Abfrage werden nicht in die innere Abfrage innerhalb der EXTERNAL_QUERY-Funktion übertragen. Sie müssen Prädikate und Spaltenprojektionen direkt in der inneren MySQL-SQL-Anweisung angeben, um die Zeilen und Spalten zu begrenzen, die über das Netzwerk übertragen werden.

  • Es wird nur die Authentifizierung mit MySQL-Datenbanknutzernamen und -passwort unterstützt. Externe Identitätsanbieter, Kerberos und die IAM-Authentifizierung von Cloud-Anbietern werden nicht unterstützt.

  • CREATE TABLE ... AS SELECT-Anweisungen (CTAS) schlagen mit Unsupported type INT32- oder Unsupported type FLOAT-Fehlern fehl, wenn die Abfrage 32-Bit-Ganzzahlspalten (TINYINT, SMALLINT und BOOLEAN / TINYINT(1)) oder Gleitkommaspalten mit einfacher Genauigkeit (FLOAT) auswählt. Wenn Sie Abfrageergebnisse in einer neuen BigQuery-Tabelle speichern möchten, wandeln Sie Ganzzahlspalten in Ihrer Abfrage explizit in INT64 und Gleitkommaspalten in FLOAT64 um:

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(tiny_col AS INT64) AS tiny_col,
CAST(small_col AS INT64) AS small_col,
...
FROM EXTERNAL_QUERY(...);

    Das Einfügen in eine vorhandene Tabelle mit INSERT INTO <table> SELECT ... ist ohne manuelle Umwandlungen erfolgreich, da BigQuery die Werte INT32 und FLOAT automatisch erweitert, damit sie den Spalten INT64 und FLOAT64 der Zieltabelle entsprechen.

  • Während die MySQL-Spalten DECIMAL und NUMERIC auf BIGNUMERIC(76, 38) abgebildet werden, erlaubt MySQL eine Genauigkeit von bis zu 65. Wenn ein Wert mehr als 38 Ganzzahlziffern enthält (Precision minus Scale ist größer als 38), überschreitet er den Ganzzahlbereich von BIGNUMERIC und die Abfrage schlägt zur Laufzeit fehl. Wenn Sie Werte mit mehr als 38 Ganzzahlstellen abfragen möchten, wandeln Sie sie in der EXTERNAL_QUERY-Anweisung in CHAR oder VARCHAR um.

  • Der Datentyp TIME schneidet Bruchteile von Sekunden (Mikrosekunden) auf ganze Sekunden ab (z. B. wird 12:34:56.789123 zu 12:34:56). Außerdem unterstützt MySQL TIME verstrichene Zeiträume von -838:59:59 bis 838:59:59, während BigQuery TIME nur Tageszeitwerte von 00:00:00 bis 23:59:59.999999 unterstützt. Werte außerhalb von 00:00:00 bis 23:59:59 führen zu Laufzeitabfragefehlern. Wenn Mikrosekundenpräzision oder Werte für die verstrichene Dauer erforderlich sind, wandeln Sie die Spalte in der EXTERNAL_QUERY-Anweisung in CHAR oder VARCHAR um.

  • Datums- und Zeitstempel vor dem 15. Oktober 1582 unterliegen aufgrund der historischen Umstellung auf den gregorianischen Kalender einer Verschiebung um 10 Tage. Wenn Sie genaue historische Daten vor 1582 beibehalten möchten, wandeln Sie sie in der EXTERNAL_QUERY-Anweisung in CHAR oder VARCHAR um.

Unterstützte MySQL-Umgebungen und -Versionen

Föderierte Abfragen unterstützen MySQL-Datenbanken in den folgenden Bereitstellungsumgebungen:

  • In der Cloud gehostete oder lokale MySQL-Datenbanken, die über öffentliche IP-Adressen oder Netzwerkanhänge (über Cloud VPN oder Cloud Interconnect) zugänglich sind
  • Selbst gehostete MySQL-Instanzen in Compute Engine oder einer anderen Infrastruktur
  • MySQL 8.4 LTS und 8.0

Hinweis

Achten Sie darauf, dass Ihr BigQuery-Administrator eine MySQL-Verbindung erstellt und für Sie freigegeben hat.

Erforderliche Rollen

Bitten Sie Ihren Administrator, Ihnen die IAM-Rolle BigQuery-Verbindungsnutzer (roles/bigquery.connectionUser) für das Projekt zuzuweisen, um die Berechtigungen zu erhalten, die Sie zum Abfragen von MySQL 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 MySQL zu senden.

Das folgende Beispiel ist eine föderierte Abfrage, die Kundendatensätze in BigQuery mit dem Namen mydataset.customers mit Transaktionsaufträgen in MySQL verknüpft:

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/mysql_prod_conn',
  '''SELECT CUSTOMER_ID, MIN(ORDER_DATE) AS first_order_date
     FROM ORDERS
     GROUP BY CUSTOMER_ID''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

MySQL-Tabellenschema ansehen

Sie können die MySQL-INFORMATION_SCHEMA-Ansichten über EXTERNAL_QUERY abfragen, um Datenbankmetadaten zu prüfen, Tabellen zu finden und Spaltenschemas zu überprüfen, bevor Sie Analysen ausführen.

Alle Datenbanken auflisten

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
     FROM INFORMATION_SCHEMA.SCHEMATA''');

Tabellen in einer Datenbank auflisten

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'DATABASE_NAME'""");

Spalten und Datentypen in einer Tabelle ansehen

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_TYPE
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = 'DATABASE_NAME' AND TABLE_NAME = 'TABLE_NAME'""");

Datentypzuordnung

Wenn Sie eine föderierte Abfrage ausführen, konvertiert BigQuery die Daten aus MySQL gemäß der folgenden Tabelle in GoogleSQL-Datentypen:

MySQL-Datentyp BigQuery-Datentyp
Numerische Typen
BOOLEAN, BOOL, TINYINT(1) INT64 (interaktive Abfragen; siehe Einschränkungen für CREATE TABLE ... AS SELECT)
TINYINT, SMALLINT INT64 (interaktive Abfragen; siehe Einschränkungen für CREATE TABLE ... AS SELECT)
MEDIUMINT, INT, INTEGER INT64
BIGINT INT64
BIGINT UNSIGNED, BIGINT ZEROFILL, SERIAL BIGNUMERIC(76, 38)
DECIMAL, DEC, NUMERIC, FIXED BIGNUMERIC(76, 38)
FLOAT FLOAT64 (interaktive Abfragen; siehe Einschränkungen für CREATE TABLE ... AS SELECT)
DOUBLE, DOUBLE PRECISION, REAL FLOAT64
Datums- und Uhrzeittypen
DATE DATE
DATETIME DATETIME
TIMESTAMP TIMESTAMP
TIME TIME
String- und Texttypen
CHAR STRING
VARCHAR STRING
BINARY BYTES
VARBINARY BYTES
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB BYTES
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT STRING
ENUM STRING
SET STRING

Preise

Die Kosten für die Ausführung einer föderierten Abfrage hängen von drei Faktoren ab:

  • Die Rechenkosten für die Ausführung der Abfrage in MySQL (werden von Ihrem MySQL-Datenbankanbieter oder Ihrer Hosting-Umgebung in Rechnung gestellt)
  • Die Bandbreitenkosten für die Übertragung der Abfrageergebnisse von MySQL zu BigQuery (Gebühren für ausgehenden Traffic von Ihrem Datenbankhostanbieter)
  • Die Rechenkosten für die Ausführung der Abfrage in BigQuery

Alle MySQL-Computing- und ausgehenden Traffic-Kosten werden direkt von Ihrem MySQL-Anbieter oder Infrastrukturhost in Rechnung gestellt, nicht von Google. Um die Bandbreitenkosten zu begrenzen, empfehlen wir, die Abfrage in der EXTERNAL_QUERY-Anweisung so zu schreiben, dass alle Spalten und Zeilen ausgeschlossen werden, die nicht für die Berechnung des Endergebnisses erforderlich sind.

Für die Ausführung föderierter Abfragen in BigQuery fallen keine zusätzlichen Kosten an. Weitere Informationen zu den Preisen von BigQuery finden Sie unter Preise.

Wenn Ihre Verbindung Netzwerkkomponenten von Google Cloud verwendet, können auch die folgenden Preise gelten:

Nächste Schritte