Föderierte PostgreSQL-Abfragen ausführen

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

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

Beschränkungen

Föderierte PostgreSQL-Abfragen unterliegen den 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 PostgreSQL-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 PostgreSQL.
  • Der zugrunde liegende Verbindungsdienst kann inaktive Verbindungen in den Ruhezustand versetzen. Die erste Abfrage, die nach einer gewissen Zeit der Inaktivität eine PostgreSQL-Verbindung verwendet, kann während der Initialisierung des Connectors mehr als eine Minute dauern.
  • 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 PostgreSQL-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 PostgreSQL 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 PostgreSQL-SQL-Anweisung angeben, um die Zeilen und Spalten zu begrenzen, die über das Netzwerk übertragen werden.

  • Es wird nur die Authentifizierung mit Nutzername und Passwort für PostgreSQL-Datenbanken 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 16-Bit-Ganzzahlspalten (int2, smallint, smallserial) oder Gleitkommaspalten mit einfacher Genauigkeit (float4, real) 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(small_col AS INT64) AS small_col,
CAST(real_col AS FLOAT64) AS real_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.

  • Sowohl time [ without time zone ] als auch time with time zone (timetz) kürzen Sekundenbruchteile (Mikrosekunden) auf ganze Sekunden. Wenn eine Genauigkeit im Mikrosekundenbereich erforderlich ist, formatieren oder wandeln Sie die Spalte in der EXTERNAL_QUERY-Anweisung in text oder varchar um.

  • Alle PostgreSQL-Spalten vom Typ numeric und decimal werden BIGNUMERIC(76, 38) zugeordnet. Spalten mit einer Skalierung von mehr als 38 werden auf 38 Dezimalstellen gerundet. Werte, die den BIGNUMERIC-Bereich überschreiten (mehr als 38 ganze Ganzzahlstellen), schlagen mit einem Fehler „Außerhalb des zulässigen Bereichs“ fehl. Außerdem werden die speziellen Werte NaN, Infinity und -Infinity nicht unterstützt und schlagen zur Laufzeit fehl. Wenn Sie Zahlen mit größeren ganzzahligen Teilen, höherer Genauigkeit bei Nachkommastellen oder speziellen Werten abfragen möchten, wandeln Sie die Spalte in der EXTERNAL_QUERY-Anweisung in text um.

  • Bei character(n)- / char(n)-Werten (bpchar) mit fester Länge werden nachgestellte Leerzeichen bis zur Länge n beibehalten, wenn sie dem BigQuery-Datentyp STRING zugeordnet werden.

  • Datums- und Zeitstempel vor dem 15. Oktober 1582 unterliegen aufgrund der historischen Umstellung auf den gregorianischen Kalender einer Kalenderverschiebung von 10 Tagen. Wandeln Sie historische Datumsangaben in der EXTERNAL_QUERY-Anweisung in text um, um die genauen Datumsangaben beizubehalten.

Unterstützte PostgreSQL-Umgebungen und -Versionen

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

  • In der Cloud gehostete oder lokale PostgreSQL-Datenbanken, auf die über öffentliche IP-Adressen oder Netzwerkanhänge (über Cloud VPN oder Cloud Interconnect) zugegriffen werden kann
  • Selbst gehostete PostgreSQL-Instanzen in Compute Engine oder einer anderen Infrastruktur
  • PostgreSQL 16 (empfohlen) und PostgreSQL 15

Hinweis

Achten Sie darauf, dass Ihr BigQuery-Administrator eine PostgreSQL-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 PostgreSQL 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 PostgreSQL zu senden.

Das folgende Beispiel ist eine föderierte Abfrage, die Kundendatensätze in BigQuery mit dem Namen mydataset.customers mit transaktionalen Kundenaufträgen in PostgreSQL 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/postgres_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;

PostgreSQL-Tabellenschema ansehen

Sie können die PostgreSQL-information_schema-Tabellen mit EXTERNAL_QUERY abfragen, um Datenbankmetadaten abzurufen:

Alle Tabellen in einem Schema auflisten

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT table_schema, table_name, table_type
     FROM information_schema.tables
     WHERE table_schema = 'SCHEMA_NAME'
     ORDER BY table_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
     FROM information_schema.columns
     WHERE table_schema = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
     ORDER BY ordinal_position''');

Datentypzuordnung

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

PostgreSQL-Datentyp BigQuery-Datentyp
Boolesche Typen
bool, boolean BOOL
Numerische Typen
int2, smallint, smallserial INT64 (interaktive Abfragen; siehe Einschränkungen für CREATE TABLE ... AS SELECT)
int4, integer, serial INT64
int8, bigint, bigserial INT64
numeric, decimal BIGNUMERIC(76, 38)
float4, real FLOAT64 (interaktive Abfragen; siehe Einschränkungen für CREATE TABLE ... AS SELECT)
float8, double precision FLOAT64
Datums- und Uhrzeittypen
date DATE
timestamp [ without time zone ] DATETIME
timestamp with time zone, timestamptz TIMESTAMP
time [ without time zone ] TIME
time with time zone, timetz TIME
Zeichen- und Texttypen
character(n), char(n), bpchar STRING
character varying(n), varchar(n) STRING
text 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 PostgreSQL (werden von Ihrem PostgreSQL-Datenbankanbieter oder Ihrer Hosting-Umgebung in Rechnung gestellt)
  • Die Bandbreitenkosten für die Übertragung der Abfrageergebnisse von PostgreSQL zu BigQuery (Gebühren für ausgehenden Traffic von Ihrem Datenbankhostanbieter)
  • Die Rechenkosten für die Ausführung der Abfrage in BigQuery

Alle PostgreSQL-Computing- und ausgehenden Traffickosten werden direkt von Ihrem PostgreSQL-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