Verbindung zu PostgreSQL herstellen

Als BigQuery-Administrator können Sie eine Verbindung erstellen, um auf PostgreSQL-Daten zuzugreifen. Über diese Verbindung können Datenanalysten Daten in PostgreSQL abfragen.

Unterstützte PostgreSQL-Umgebungen und -Versionen

Die BigQuery-Föderation unterstützt die folgenden PostgreSQL-Bereitstellungsumgebungen:

  • Lokale PostgreSQL-Instanzen, die über Cloud VPN oder Cloud Interconnect mit Google Cloudverbunden sind
  • Selbstverwaltete PostgreSQL-Instanzen in Compute Engine
  • In der Cloud gehostete PostgreSQL-Instanzen, auf die über öffentliche IP-Adressen oder Netzwerkanhänge zugegriffen werden kann

Die BigQuery-Föderation unterstützt die folgenden PostgreSQL-Versionen:

  • PostgreSQL 16 (empfohlen)
  • PostgreSQL 15

Hinweis

Aktivieren Sie die erforderlichen APIs, konfigurieren Sie Ihre PostgreSQL-Datenbank und weisen Sie IAM-Rollen (Identity and Access Management) zu, die Nutzern die erforderlichen Berechtigungen zum Ausführen der einzelnen Aufgaben in diesem Dokument gewähren.

  1. BigQuery Connection API aktivieren.

    API aktivieren

  2. Wenn Sie Traffic über private Netzwerke mithilfe von Netzwerkanhängen weiterleiten möchten, aktivieren Sie auch die Compute Engine API.

    Compute Engine API aktivieren

  3. Erstellen Sie in Ihrer PostgreSQL-Datenbank einen dedizierten Datenbanknutzer mit SELECT-Berechtigungen für die Tabellen oder das Schema, die Sie abfragen möchten:

    CREATE USER USER_NAME WITH PASSWORD 'PASSWORD';
GRANT USAGE ON SCHEMA SCHEMA_NAME TO USER_NAME;
GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_NAME TO USER_NAME;

    Ersetzen Sie Folgendes:

    • USER_NAME: der Nutzername für den Datenbanknutzer.
    • PASSWORD: Das Passwort des Datenbanknutzers.
    • SCHEMA_NAME: das Schema der Tabellen, die Sie abfragen möchten.

  4. Konfigurieren Sie die Netzwerkverbindung zu Ihrer PostgreSQL-Datenbankinstanz. Gehen Sie dazu so vor:

    • Wenn Sie Traffic über private Netzwerke weiterleiten, erstellen Sie einen Netzwerkanhang in derselben Region wie Ihre BigQuery-Verbindung. DNS-Peering wird nicht unterstützt. Stellen Sie eine Verbindung über eine IP-Adresse oder einen Hostnamen her, der in Ihrem VPC-Netzwerk aufgelöst werden kann.
    • Wenn Sie eine Verbindung über das öffentliche Internet herstellen, fügen Sie den Firewallregeln Ihrer PostgreSQL-Datenbank alle Google-IP-Adressbereiche hinzu.

Erforderliche Rollen

Bitten Sie Ihren Administrator, Ihnen die folgenden IAM-Rollen zuzuweisen, um die Berechtigungen zu erhalten, die Sie zum Herstellen einer Verbindung zu 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.

BigQuery mit PostgreSQL verbinden

Sie können BigQuery mit PostgreSQL in derGoogle Cloud Console oder über das bq-Befehlszeilentool verbinden.

Console

  1. Rufen Sie in der Google Cloud Console die Seite BigQuery auf.

    BigQuery aufrufen

  2. Klicken Sie im Bereich Explorer auf Daten hinzufügen.

    Die Seite Daten hinzufügen wird geöffnet.

  3. Wählen Sie im Bereich Filtern nach im Abschnitt Datenquellentyp die Option Datenbanken aus.

    Alternativ können Sie im Feld Nach Datenquellen suchen den Wert postgresql eingeben.

  4. Klicken Sie im Abschnitt Empfohlene Datenquellen auf PostgreSQL.

  5. Klicken Sie im Abschnitt Direkt auf externe Daten zugreifen auf die Lösungskarte BigQuery-Föderation mit der Beschreibung Föderierte Abfragen an PostgreSQL (nicht auf die Karten für Cloud SQL oder AlloyDB for PostgreSQL).

    Der Bereich Externe Datenquelle wird geöffnet.

  6. Konfigurieren Sie im Bereich Externe Datenquelle Folgendes:

    • Geben Sie im Feld Verbindungs-ID eine eindeutige Verbindungs-ID ein, z. B. postgres_prod_conn.
    • Wählen Sie im Abschnitt Standorttyp die Option Region und dann die Region aus, in der sich Ihr BigQuery-Dataset befindet. Abfragen, die diese Verbindung verwenden, müssen in dieser Region ausgeführt werden.
    • Optional: Geben Sie im Feld Anzeigename einen aussagekräftigen Namen für die Verbindung ein.
    • Optional: Geben Sie im Feld Beschreibung eine Beschreibung für diese Verbindungsressource ein.
    • Wählen Sie im Abschnitt Verschlüsselung die Option Google-managed encryption key oder Cloud KMS-Schlüssel aus, um einen kundenverwalteten Verschlüsselungsschlüssel (CMEK) zu verwenden.
    • Geben Sie im Feld Host:Port den Host und den Port des PostgreSQL-Datenbankservers im Format HOST:PORT ein (z. B. 10.10.2.2:5432).
    • Geben Sie im Feld Nutzername den Datenbanknutzer ein, den Sie in den Voraussetzungen erstellt haben.
    • Geben Sie im Feld Passwort das Passwort des Datenbanknutzers ein.
    • Geben Sie im Feld Datenbank den Namen der PostgreSQL-Datenbank ein.
    • (Optional) Wenn Sie private Konnektivität verwenden, geben Sie im Feld Netzwerkanhang den Ressourcenpfad des Netzwerkanhangs im Format projects/PROJECT_ID/regions/NA_REGION/networkAttachments/ATTACHMENT_NAME ein.
    • Wählen Sie in der Liste TLS-Modus einen der unterstützten TLS-Modi aus (z. B. Daten verschlüsseln, Zertifizierungsstelle und Hostname überprüfen (empfohlen)).
    • Wählen Sie in der Liste Zertifizierungsstelle (CA) die Option Öffentliche Zertifizierungsstelle (Web-PKI) aus oder wählen Sie eine benutzerdefinierte Zertifizierungsstelle aus und fügen Sie das PEM-formatierte Zertifikat ein.

  7. Klicken Sie auf Verbindung erstellen.

bq

Führen Sie den Befehl bq mk mit dem Flag --connection aus:

bq mk --connection \
  --location=LOCATION \
  --project_id=PROJECT_ID \
  --connector_configuration '{
    "connector_id": "postgresql",
    "endpoint": {
      "host_port": "HOST_PORT"
    },
    "asset": {
      "database": "DATABASE"
    },
    "authentication": {
      "username_password": {
        "username": "USERNAME",
        "password": {
          "plaintext": "PASSWORD"
        }
      }
    },
    "tls": {
      "mode": "TLS_MODE"
    },
    "network": {
      "private_service_connect": {
        "network_attachment": "projects/PROJECT_ID/regions/NA_REGION/networkAttachments/ATTACHMENT_NAME"
      }
    }
  }' \
  CONNECTION_ID

Ersetzen Sie Folgendes:

  • LOCATION: die BigQuery-Region (z. B. us-central1), die dem Dataset entspricht, das Sie zusammen mit PostgreSQL abfragen möchten. Abfragen, die diese Verbindung verwenden, müssen in dieser Region ausgeführt werden.
  • PROJECT_ID: Projekt-ID in Google Cloud .
  • HOST_PORT: der Host und der Port Ihres PostgreSQL-Datenbankservers im Format HOST:PORT (z. B. 10.10.2.2:5432).
  • DATABASE: der Name der PostgreSQL-Datenbank.
  • USERNAME: der PostgreSQL-Datenbanknutzer.
  • PASSWORD: Das Passwort des Datenbanknutzers.
  • TLS_MODE: der TLS-Verschlüsselungsmodus (ENCRYPT_VERIFY_CA_AND_HOST, ENCRYPT_VERIFY_CA, ENCRYPT_VERIFY_NONE oder DISABLE). Wenn Sie ein benutzerdefiniertes CA-Zertifikat mit ENCRYPT_VERIFY_CA_AND_HOST oder ENCRYPT_VERIFY_CA verwenden, fügen Sie das Objekt "private_pki": { "trusted_certificates_pem": "CERT_CONTENT" } unter "tls" ein.
  • NA_REGION: die Region, in der sich der Netzwerkanhang befindet.
  • ATTACHMENT_NAME: der Name Ihres Netzwerkanhangs. Wenn Sie eine Verbindung über eine öffentliche IP-Adresse herstellen, lassen Sie das Objekt "network" weg.
  • CONNECTION_ID: die eindeutige Kennung für diese Verbindung.
  • (Optional) --kms_key_name=KMS_KEY_NAME: ein kundenverwalteter Verschlüsselungsschlüssel (CMEK). Wenn keine Angabe gemacht wird, werden die Anmeldedaten durch den Standard- Google-owned and Google-managed encryption keygeschützt.

TLS-Konfiguration (Transport Layer Security)

Der PostgreSQL-Connector unterstützt Transport Layer Security (TLS) zur Verschlüsselung des Abfragetraffic während der Übertragung zwischen BigQuery und Ihrer PostgreSQL-Datenbank.

Wenn Sie BigQuery mit PostgreSQL verbinden, wählen Sie einen der folgenden TLS-Modi aus:

  • Daten verschlüsseln und Zertifizierungsstelle sowie Hostnamen überprüfen (Modus ENCRYPT_VERIFY_CA_AND_HOST). Führt eine vollständige Servervalidierung mit TLS durch. Verschlüsselt alle Daten bei der Übertragung, prüft, ob das Zertifikat des Datenbankservers von einer vertrauenswürdigen Zertifizierungsstelle signiert ist, und prüft, ob der Hostname, mit dem Sie eine Verbindung herstellen, mit dem Common Name (CN) oder dem Subject Alternative Name (SAN) im Zertifikat übereinstimmt. Erforderlich für Verbindungen über öffentliche IP-Adressen.

  • Daten verschlüsseln und nur Zertifizierungsstelle überprüfen (ENCRYPT_VERIFY_CA-Modus). Verschlüsselt alle Daten bei der Übertragung und überprüft, ob das Zertifikat des Servers von einer vertrauenswürdigen Zertifizierungsstelle signiert wurde. Der Hostname des Servers wird jedoch nicht überprüft. Verwenden Sie diesen Modus, wenn das Serverzertifikat von einer vertrauenswürdigen Zertifizierungsstelle signiert ist, Sie aber eine Verbindung über eine IP-Adresse oder einen Hostnamen herstellen, der nicht mit dem CN oder SAN des Zertifikats übereinstimmt.

  • Nur Verschlüsselung (ENCRYPT_VERIFY_NONE-Modus). Verschlüsselt alle Daten, die zwischen BigQuery und dem Server übertragen werden. Führt keine Zertifikats- oder Hostnamenüberprüfung durch. Nur innerhalb privater VPCs empfohlen.

  • Keine Verschlüsselung oder Überprüfung (DISABLE-Modus). Daten werden nicht verschlüsselt und es wird keine Zertifikats- oder Hostnamenüberprüfung durchgeführt. Nur zulässig, wenn die Verbindung über private Netzwerkanhänge innerhalb geschützter VPC-Netzwerke hergestellt wird.

Vertrauenswürdige Serverzertifikate im PEM-Format

Wenn Sie entweder den Modus ENCRYPT_VERIFY_CA_AND_HOST oder den Modus ENCRYPT_VERIFY_CA verwenden, gehen Sie so vor:

  • Wenn das Zertifikat Ihres Datenbankservers von einer öffentlichen Zertifizierungsstelle signiert ist (z. B. von einer Zertifizierungsstelle, die im Mozilla Root CA Program enthalten ist, einschließlich Let's Encrypt, DigiCert oder Google Trust Services), müssen Sie kein Zertifikat angeben. Root-Zertifikate für öffentliche Zertifizierungsstellen sind standardmäßig vertrauenswürdig.
  • Wenn Sie ein Zertifikat verwenden, das von einer internen oder privaten Zertifizierungsstelle in Ihrer Organisation signiert wurde, oder ein selbst signiertes Zertifikat, müssen Sie die vollständige Zertifikatskette im PEM-Format (Privacy-Enhanced Mail) im Feld Vertrauenswürdiges PEM-Zertifikat während der Verbindungseinrichtung angeben.

Verbindungen für Nutzer freigeben

Sie können die folgenden Rollen zuweisen, damit Nutzer Daten abfragen und Verbindungen verwalten können:

  • roles/bigquery.connectionUser: Nutzer können Verbindungen nutzen, um sich mit externen Datenquellen zu verbinden und diese abzufragen.

  • roles/bigquery.connectionAdmin: Nutzer können Verbindungen verwalten.

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

Wählen Sie eine der folgenden Optionen aus:

Console

  1. Rufen Sie die Seite BigQuery auf.

    BigQuery aufrufen

    Verbindungen werden in Ihrem Projekt in einer Gruppe namens Verbindungen aufgeführt.

  2. Klicken Sie im linken Bereich auf Explorer:

    Hervorgehobener Button für den Explorer-Bereich.

    Wenn der linke Bereich nicht angezeigt wird, klicken Sie auf Linken Bereich maximieren, um ihn zu öffnen.

  3. Klicken Sie auf Ihr Projekt, dann auf Verbindungen und wählen Sie eine Verbindung aus.

  4. Klicken Sie im Bereich Details auf Freigeben, um eine Verbindung freizugeben. Führen Sie anschließend folgende Schritte aus:

    1. Geben Sie im Dialogfeld Verbindungsberechtigungen die Verbindung für andere Hauptkonten frei, indem Sie Hauptkonten hinzufügen oder bearbeiten.

    2. Klicken Sie auf Speichern.

bq

Sie können keine Verbindung mit dem bq-Befehlszeilentool freigeben. Verwenden Sie zum Freigeben einer Verbindung die Google Cloud Console oder die Methode der BigQuery Connections API.

API

Verwenden Sie die Methode projects.locations.connections.setIAM im Referenzabschnitt zur BigQuery Connections REST API und geben Sie eine Instanz der Ressource policy an.

Java

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

Richten Sie zur Authentifizierung bei BigQuery die Standardanmeldedaten für Anwendungen ein. Weitere Informationen finden Sie unter Authentifizierung für Clientbibliotheken einrichten. 

import com.google.api.resourcenames.ResourceName;
import com.google.cloud.bigquery.connection.v1.ConnectionName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import com.google.iam.v1.Binding;
import com.google.iam.v1.Policy;
import com.google.iam.v1.SetIamPolicyRequest;
import java.io.IOException;

// Sample to share connections
public class ShareConnection {

  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";
    shareConnection(projectId, location, connectionId);
  }

  static void shareConnection(String projectId, String location, String connectionId)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      ResourceName resource = ConnectionName.of(projectId, location, connectionId);
      Binding binding =
          Binding.newBuilder()
              .addMembers("group:example-analyst-group@google.com")
              .setRole("roles/bigquery.connectionUser")
              .build();
      Policy policy = Policy.newBuilder().addBindings(binding).build();
      SetIamPolicyRequest request =
          SetIamPolicyRequest.newBuilder()
              .setResource(resource.toString())
              .setPolicy(policy)
              .build();
      client.setIamPolicy(request);
      System.out.println("Connection shared successfully");
    }
  }
}

Preise

Für föderierte Abfragen und Verbindungen fallen die folgenden Gebühren an:

Nächste Schritte