PostgreSQL-Daten in BigQuery laden

Mit dem BigQuery Data Transfer Service-Connector für PostgreSQL können Sie Daten aus PostgreSQL in BigQuery laden. Der Connector unterstützt PostgreSQL-Instanzen, die in Ihrer lokalen Umgebung, in Cloud SQL und bei anderen öffentlichen Cloud-Anbietern wie Amazon Web Services (AWS) und Microsoft Azure gehostet werden. Mit dem BigQuery Data Transfer Service können Sie wiederkehrende Übertragungsjobs planen, mit denen Ihre neuesten Daten aus PostgreSQL in BigQuery eingefügt werden.

Beschränkungen

Für PostgreSQL-Datenübertragungen gelten die folgenden Einschränkungen:

• Die maximale Anzahl gleichzeitiger Übertragungen zu einer einzelnen PostgreSQL-Datenbank wird durch die maximale Anzahl gleichzeitiger Verbindungen bestimmt, die von der PostgreSQL-Datenbank unterstützt werden. Die Anzahl der gleichzeitigen Übertragungsjobs sollte auf einen Wert begrenzt werden, der kleiner als die maximale Anzahl gleichzeitiger Verbindungen ist, die von der PostgreSQL-Datenbank unterstützt werden.

• Für eine einzelne Übertragungskonfiguration kann jeweils nur eine Datenübertragung ausgeführt werden. Wenn eine zweite Datenübertragung geplant ist, bevor die erste abgeschlossen ist, wird nur die erste Datenübertragung abgeschlossen. Alle anderen Datenübertragungen, die sich mit der ersten überschneiden, werden übersprungen.

  Um übersprungene Übertragungen innerhalb einer einzelnen Übertragungskonfiguration zu vermeiden, empfehlen wir, die Zeit zwischen großen Datenübertragungen durch Konfigurieren der Wiederholungshäufigkeit zu verlängern.

• Während einer Datenübertragung werden mit dem PostgreSQL-Connector indexierte und partitionierte Schlüsselspalten identifiziert, um Ihre Daten in parallelen Batches zu übertragen. Aus diesem Grund empfehlen wir, Primärschlüsselspalten anzugeben oder indexierte Spalten in Ihrer Tabelle zu verwenden, um die Leistung zu verbessern und die Fehlerrate bei Ihren Datenübertragungen zu reduzieren. Berücksichtige Folgendes:

  • Wenn Sie Index- oder Primärschlüsselbeschränkungen haben, werden nur die folgenden Spaltentypen für das Erstellen paralleler Batches unterstützt:
    • INTEGER
    • TINYINT
    • SMALLINT
    • FLOAT
    • REAL
    • DOUBLE
    • NUMERIC
    • BIGINT
    • DECIMAL
    • DATE
  • PostgreSQL-Datenübertragungen,bei denen keine Primärschlüssel oder indexierten Spalten verwendet werden,können nicht mehr als 2.000.000 Datensätze pro Tabelle unterstützen.

Einschränkungen bei inkrementellen Übertragungen

• Sie können nur TIMESTAMP Spalten als Wasserzeichenspalten auswählen.
• Die inkrementelle Aufnahme wird nur für Assets mit gültigen Wasserzeichenspalten unterstützt.
• Die Werte in einer Wasserzeichenspalte müssen monoton ansteigen.
• Bei inkrementellen Übertragungen können keine Löschvorgänge in der Quelltabelle synchronisiert werden.
• Eine einzelne Übertragungskonfiguration kann nur die inkrementelle oder die vollständige Aufnahme unterstützen.
• Sie können Objekte in der Liste asset nach dem ersten inkrementellen Import nicht mehr aktualisieren.
• Sie können den Schreibmodus in einer Übertragungskonfiguration nach dem ersten inkrementellen Erfassungslauf nicht mehr ändern.
• Nach dem ersten inkrementellen Import können Sie die Spalte für das Wasserzeichen oder den Primärschlüssel nicht mehr ändern.
• Die BigQuery-Zieltabelle wird mit dem angegebenen Primärschlüssel geclustert und unterliegt den Beschränkungen für geclusterte Tabellen.
• Wenn Sie eine vorhandene Übertragungskonfiguration zum ersten Mal auf den inkrementellen Erfassungsmodus umstellen, werden bei der ersten Datenübertragung nach dieser Umstellung alle verfügbaren Daten aus Ihrer Datenquelle übertragen. Bei allen nachfolgenden inkrementellen Datenübertragungen werden nur die neuen und aktualisierten Zeilen aus Ihrer Datenquelle übertragen.
• Wir empfehlen, Indexe für die Wasserzeichenspalte zu erstellen. Bei diesem Connector werden Wasserzeichenspalten für Filter bei inkrementellen Übertragungen verwendet. Das Indexieren dieser Spalten kann die Leistung verbessern.

Optionen für die Datenaufnahme

In den folgenden Abschnitten finden Sie Informationen zu den Optionen für die Datenaufnahme, wenn Sie einen PostgreSQL-Datenübertrag einrichten.

TLS-Konfiguration

Der PostgreSQL-Connector unterstützt die Konfiguration für die Sicherheit auf Transportebene (Transport Layer Security, TLS), um Ihre Datenübertragungen nach BigQuery zu verschlüsseln. Der PostgreSQL-Connector unterstützt die folgenden TLS-Konfigurationen:

• Der Modus Daten verschlüsseln und Zertifizierungsstelle und Hostname bestätigen. In diesem Modus wird eine vollständige Validierung des Servers mit TLS über das TCPS-Protokoll durchgeführt. Sie verschlüsselt alle übertragenen Daten und prüft, ob das Zertifikat des Datenbankservers von einer vertrauenswürdigen Zertifizierungsstelle signiert wurde. In diesem Modus wird auch geprüft, ob der Hostname, zu dem Sie eine Verbindung herstellen, genau mit dem allgemeinen Namen (Common Name, CN) oder einem alternativen Antragstellernamen (Subject Alternative Name, SAN) im Zertifikat des Servers übereinstimmt. In diesem Modus wird verhindert, dass Angreifer ein gültiges Zertifikat für eine andere Domain verwenden, um sich als Ihr Datenbankserver auszugeben.

  Wenn Ihr Hostname nicht mit dem CN oder SAN des Zertifikats übereinstimmt, schlägt die Verbindung fehl. Sie müssen eine DNS-Auflösung konfigurieren, die dem Zertifikat entspricht, oder einen anderen Sicherheitsmodus verwenden. Verwenden Sie diesen Modus, um PITM-Angriffe (Person-in-the-Middle) zu verhindern.

• Der Modus Daten verschlüsseln und nur Zertifizierungsstelle bestätigen. In diesem Modus werden alle Daten mit TLS über das TCPS-Protokoll verschlüsselt und es wird geprüft, ob das Zertifikat des Servers von einer Zertifizierungsstelle signiert ist, der der Client vertraut. In diesem Modus wird der Hostname des Servers jedoch nicht überprüft. In diesem Modus wird eine Verbindung hergestellt, solange das Zertifikat gültig ist und von einer vertrauenswürdigen Zertifizierungsstelle ausgestellt wurde. Dabei spielt es keine Rolle, ob der Hostname im Zertifikat mit dem Hostnamen übereinstimmt, zu dem Sie eine Verbindung herstellen.

  Verwenden Sie diesen Modus, wenn Sie sicherstellen möchten, dass Sie eine Verbindung zu einem Server herstellen, dessen Zertifikat von einer vertrauenswürdigen Zertifizierungsstelle signiert ist, der Hostname jedoch nicht überprüft werden kann oder Sie keine Kontrolle über die Hostname-Konfiguration haben.

• Der Modus Nur Verschlüsselung. In diesem Modus werden alle Daten verschlüsselt, die zwischen dem Client und dem Server übertragen werden. Es wird keine Zertifikats- oder Hostname-Validierung durchgeführt.

  Dieser Modus bietet ein gewisses Maß an Sicherheit, da Daten während der Übertragung geschützt werden. Er kann jedoch anfällig für PITM-Angriffe sein.

  Verwenden Sie diesen Modus, wenn Sie sicherstellen müssen, dass alle Daten verschlüsselt sind, die Identität des Servers aber nicht überprüfen können oder möchten. Wir empfehlen, diesen Modus zu verwenden, wenn Sie mit privaten VPCs arbeiten.

• Der Modus Keine Verschlüsselung oder Bestätigung. In diesem Modus werden keine Daten verschlüsselt und es werden keine Zertifikats- oder Hostnamenprüfungen durchgeführt. Alle Daten werden als Nur-Text gesendet.

  Wir empfehlen, diesen Modus nicht in einer Umgebung zu verwenden, in der vertrauliche Daten verarbeitet werden. Wir empfehlen, diesen Modus nur zu Testzwecken in einem isolierten Netzwerk zu verwenden, in dem Sicherheit kein Problem darstellt.

Vertrauenswürdiges Serverzertifikat (PEM)

Wenn Sie den Modus Daten verschlüsseln und CA und Hostname bestätigen oder Daten verschlüsseln und CA bestätigen verwenden, können Sie auch ein oder mehrere PEM-codierte Zertifikate angeben. Diese Zertifikate sind in einigen Szenarien erforderlich, in denen der BigQuery Data Transfer Service die Identität Ihres Datenbankservers während der TLS-Verbindung bestätigen muss:

• Wenn Sie ein Zertifikat verwenden, das von einer privaten Zertifizierungsstelle in Ihrer Organisation signiert wurde, oder ein selbst signiertes Zertifikat, müssen Sie die vollständige Zertifikatskette oder das einzelne selbst signierte Zertifikat angeben. Dies ist für Zertifikate erforderlich, die von internen Zertifizierungsstellen verwalteter Cloud-Anbieterdienste wie Amazon Relational Database Service (RDS) ausgestellt werden.
• Wenn das Zertifikat Ihres Datenbankservers von einer öffentlichen Zertifizierungsstelle signiert wurde (z. B. Let's Encrypt, DigiCert oder GlobalSign), müssen Sie kein Zertifikat angeben. Die Stammzertifikate für diese öffentlichen Zertifizierungsstellen sind vorinstalliert und werden vom BigQuery Data Transfer Service als vertrauenswürdig eingestuft.

Sie können PEM-codierte Zertifikate im Feld Trusted PEM Certificate (Vertrauenswürdiges PEM-Zertifikat) in der Übertragungskonfiguration angeben. Dabei gelten die folgenden Anforderungen:

• Das Zertifikat muss eine gültige PEM-codierte Zertifikatskette sein.
• Das Zertifikat muss vollständig korrekt sein. Fehlende Zertifikate in der Kette oder falsche Inhalte führen dazu, dass die TLS-Verbindung fehlschlägt.
• Für ein einzelnes Zertifikat können Sie ein einzelnes, selbst signiertes Zertifikat vom Datenbankserver bereitstellen.
• Für eine vollständige Zertifikatskette, die von einer privaten Zertifizierungsstelle ausgestellt wurde, müssen Sie die vollständige Vertrauenskette angeben. Dazu gehören das Zertifikat des Datenbankservers sowie alle Zwischen- und Stammzertifikate der Zertifizierungsstelle.

Vollständige oder inkrementelle Übertragungen

Sie können angeben, wie Daten in BigQuery geladen werden, indem Sie beim Einrichten einer PostgreSQL-Übertragung in der Übertragungskonfiguration die Schreibeinstellung Vollständig oder Inkrementell auswählen. Inkrementelle Übertragungen werden in der Vorschau unterstützt.

Alternativ können Sie eine inkrementelle Datenübertragung (Vorabversion) konfigurieren, um nur Daten zu übertragen, die sich seit der letzten Datenübertragung geändert haben. So muss nicht bei jeder Datenübertragung das gesamte Dataset geladen werden. Wenn Sie eine inkrementelle Datenübertragung konfiguriert haben, müssen Sie entweder den Schreibmodus append oder upsert angeben, um festzulegen, wie Daten während einer inkrementellen Datenübertragung in BigQuery geschrieben werden. In den folgenden Abschnitten werden die verfügbaren Schreibmodi beschrieben.

Anhängemodus

Im Anhängen-Schreibmodus werden nur neue Zeilen in die Zieltabelle eingefügt. Bei dieser Option werden übertragene Daten strikt angehängt, ohne dass nach vorhandenen Datensätzen gesucht wird. In der Zieltabelle kann es daher zu Datenduplizierungen kommen.

Wenn Sie den Modus „Append“ auswählen, müssen Sie eine Watermark-Spalte auswählen. Für den PostgreSQL-Connector ist eine Wasserzeichenspalte erforderlich, um Änderungen in der Quelltabelle zu erfassen.

Upsert-Schreibmodus

Im Upsert-Schreibmodus wird entweder eine Zeile aktualisiert oder eine neue Zeile in die Zieltabelle eingefügt, indem nach einem Primärschlüssel gesucht wird. Sie können einen Primärschlüssel angeben, damit der PostgreSQL-Connector ermitteln kann, welche Änderungen erforderlich sind, um die Zieltabelle mit der Quelltabelle auf dem neuesten Stand zu halten. Wenn der angegebene Primärschlüssel während einer Datenübertragung in der BigQuery-Zieltabelle vorhanden ist, aktualisiert der PostgreSQL-Connector diese Zeile mit neuen Daten aus der Quelltabelle. Wenn bei einer Datenübertragung kein Primärschlüssel vorhanden ist, fügt der PostgreSQL-Connector eine neue Zeile ein.

Wenn Sie den Upsert-Modus auswählen, müssen Sie eine Watermark-Spalte und einen Primärschlüssel auswählen:

• Für den PostgreSQL-Connector ist eine Watermark-Spalte erforderlich, um Änderungen in der Quelltabelle zu verfolgen.

  Wählen Sie eine Wasserzeichenspalte aus, die jedes Mal aktualisiert wird, wenn eine Zeile geändert wird. Wir empfehlen Spalten, die der Spalte UPDATED_AT oder LAST_MODIFIED ähneln.

• Der Primärschlüssel kann aus einer oder mehreren Spalten in Ihrer Tabelle bestehen, die der PostgreSQL-Connector benötigt, um zu ermitteln, ob eine Zeile eingefügt oder aktualisiert werden muss.

  Wählen Sie Spalten mit Nicht-Null-Werten aus, die in allen Zeilen der Tabelle eindeutig sind. Wir empfehlen Spalten mit systemgenerierten Kennungen, eindeutigen Referenzcodes (z. B. automatisch inkrementierenden IDs) oder unveränderlichen zeitbasierten Sequenz-IDs.

  Um potenziellen Datenverlust oder Datenbeschädigung zu vermeiden, müssen die ausgewählten Primärschlüsselspalten eindeutige Werte enthalten. Wenn Sie Zweifel an der Eindeutigkeit der ausgewählten Primärschlüsselspalte haben, empfehlen wir, stattdessen den Schreibmodus „Append“ zu verwenden.

Inkrementelles Aufnahmeverhalten

Wenn Sie Änderungen am Tabellenschema in Ihrer Datenquelle vornehmen, werden inkrementelle Datenübertragungen aus diesen Tabellen in BigQuery auf folgende Weise berücksichtigt:

Hinweis

• Erstellen Sie einen Nutzer in der PostgreSQL-Datenbank.
• Überprüfen Sie, ob Sie alle erforderlichen Aktionen ausgeführt haben, damit Sie den BigQuery Data Transfer Service aktivieren können.
• Erstellen Sie ein BigQuery-Dataset zum Speichern Ihrer Daten.
• Prüfen Sie, ob Sie die erforderlichen Rollen haben, um die Aufgaben in diesem Dokument auszuführen.

Erforderliche Rollen

Wenn Sie Benachrichtigungen zur Übertragungsausführung für Pub/Sub einrichten möchten, benötigen Sie die IAM-Berechtigung (Identity and Access Management) pubsub.topics.setIamPolicy. Pub/Sub-Berechtigungen sind nicht erforderlich, wenn Sie nur E-Mail-Benachrichtigungen einrichten. Weitere Informationen finden Sie unter Ausführungsbenachrichtigungen im BigQuery Data Transfer Service.

Bitten Sie Ihren Administrator, Ihnen die IAM-Rolle BigQuery-Administrator (roles/bigquery.admin) für Ihr Projekt zuzuweisen, um die Berechtigungen zu erhalten, die Sie zum Erstellen einer BigQuery Data Transfer Service-Datenübertragung benötigen. Weitere Informationen zum Zuweisen von Rollen finden Sie unter Zugriff auf Projekte, Ordner und Organisationen verwalten.

Diese vordefinierte Rolle enthält die Berechtigungen, die zum Erstellen einer BigQuery Data Transfer Service-Datenübertragung erforderlich sind. Maximieren Sie den Abschnitt Erforderliche Berechtigungen, um die notwendigen Berechtigungen anzuzeigen:

Sie können diese Berechtigungen auch mit benutzerdefinierten Rollen oder anderen vordefinierten Rollen erhalten.

Weitere Informationen finden Sie unter Zugriff auf bigquery.admin gewähren.

Netzwerkverbindungen

Wenn für die PostgreSQL-Datenbankverbindung keine öffentliche IP-Adresse verfügbar ist, müssen Sie einen Netzwerkanhang einrichten.

Eine ausführliche Anleitung zur erforderlichen Netzwerkeinrichtung finden Sie in den folgenden Dokumenten:

• Wenn Sie Daten aus Cloud SQL übertragen, lesen Sie den Abschnitt Zugriff auf Cloud SQL-Instanz konfigurieren.
• Wenn Sie Daten von AWS übertragen, lesen Sie den Abschnitt AWS-Cloud de Confiance VPN und Netzwerkanhang einrichten.
• Wenn Sie Daten von Azure übertragen, lesen Sie den Abschnitt Azure-Cloud de Confiance VPN und Netzwerkverbindung einrichten.

PostgreSQL-Datenübertragung einrichten

Sie können PostgreSQL-Daten in BigQuery einfügen, indem Sie eine Übertragungskonfiguration mit einer der folgenden Optionen einrichten:

bq mk
    --transfer_config
    --project_id=PROJECT_ID
    --data_source=DATA_SOURCE
    --display_name=DISPLAY_NAME
    --target_dataset=DATASET
    --params='PARAMETERS'

bq mk
    --transfer_config
    --target_dataset=mydataset
    --data_source=postgresql
    --display_name='My Transfer'
    --params='{"assets":["DB1/PUBLIC/DEPARTMENT","DB1/PUBLIC/EMPLOYEES"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.database":"DB1",
        "connector.endpoint.host":"192.168.0.1",
        "connector.endpoint.port":5432,
        "ingestionType":"incremental",
        "writeMode":"WRITE_MODE_APPEND",
        "watermarkColumns":["createdAt","createdAt"],
        "primaryKeys":[['dep_id'], ['report_by','report_title']],
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

      "primaryKeys":[['dep_id'], ['report_by','report_title']],
      "assets":["DB1/USER1/DEPARTMENT","DB1/USER1/EMPLOYEES"],
  

Wenn Sie eine Datenübertragung manuell außerhalb des regulären Zeitplans ausführen möchten, können Sie einen Backfill-Vorgang starten.

Datentypzuordnung

In der folgenden Tabelle werden PostgreSQL-Datentypen den entsprechenden BigQuery-Datentypen zugeordnet.

Fehlerbehebung

Unterstützung bei Problemen mit der Einrichtung von Datenübertragungen finden Sie unter PostgreSQL-Übertragungsprobleme.

Preise

Preisinformationen zu PostgreSQL-Übertragungen finden Sie unter Data Transfer Service – Preise.

Nächste Schritte

• Übersicht über den BigQuery Data Transfer Service
• Weitere Informationen zum Verwalten von Übertragungen, einschließlich des Abrufs von Informationen zu einer Übertragungskonfiguration, des Auflistens von Übertragungskonfigurationen und des Aufrufs des Ausführungsverlaufs der Übertragung
• Daten mit cloudübergreifenden Vorgängen laden