Conéctate a PostgreSQL

Como administrador de BigQuery, puedes crear una conexión para acceder a los datos de PostgreSQL. Esta conexión permite que los analistas de datos consulten datos en PostgreSQL.

Entornos y versiones de PostgreSQL compatibles

La federación de BigQuery admite los siguientes entornos de implementación de PostgreSQL:

  • Instancias de PostgreSQL locales conectadas a Google Cloud a través de Cloud VPN o Cloud Interconnect
  • Instancias de PostgreSQL autoadministradas en Compute Engine
  • Instancias de PostgreSQL alojadas en la nube a las que se puede acceder a través de direcciones IP públicas o adjuntos de red

La federación de BigQuery admite las siguientes versiones de PostgreSQL:

  • PostgreSQL 16 (recomendado)
  • PostgreSQL 15

Antes de comenzar

Habilita las APIs necesarias, configura tu base de datos de PostgreSQL y otorga roles de Identity and Access Management que les brinden a los usuarios los permisos necesarios para realizar cada tarea de este documento.

  1. Habilita la API de conexión de BigQuery.

    Habilitar la API

  2. Si planeas enrutar el tráfico a través de redes privadas con adjuntos de red, también habilita la API de Compute Engine.

    Habilitar la API de Compute Engine

  3. En tu base de datos de PostgreSQL, crea un usuario de base de datos dedicado con privilegios SELECT en las tablas o el esquema que quieras consultar:

    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;

    Reemplaza lo siguiente:

    • USER_NAME: Es el nombre de usuario de la base de datos.
    • PASSWORD: Es la contraseña del usuario de la base de datos.
    • SCHEMA_NAME: el esquema de las tablas que deseas consultar.

  4. Configura la conectividad de red a tu instancia de base de datos de PostgreSQL con una de las siguientes opciones:

    • Si enrutas el tráfico a través de redes privadas, crea un adjunto de red en la misma región que tu conexión de BigQuery. No se admite el intercambio de tráfico de DNS. Conéctate con una dirección IP o un nombre de host que se pueda resolver en tu red de VPC.
    • Si te conectas a través de la Internet pública, agrega todos los rangos de direcciones IP de Google a las reglas de firewall de tu base de datos de PostgreSQL.

Roles obligatorios

Para obtener los permisos que necesitas para conectarte a PostgreSQL, pídele a tu administrador que te otorgue los siguientes roles de IAM:

Para obtener más información sobre cómo otorgar roles, consulta Administra el acceso a proyectos, carpetas y organizaciones.

También puedes obtener los permisos necesarios a través de roles personalizados o cualquier otro rol predefinido.

Conecta BigQuery a PostgreSQL

Puedes conectar BigQuery a PostgreSQL en la consola deGoogle Cloud o la herramienta de línea de comandos de bq.

Console

  1. En la consola de Google Cloud , ve a la página BigQuery.

    Ir a BigQuery

  2. En el panel Explorador, haz clic en Agregar datos.

    Se abrirá la página Agregar datos.

  3. En el panel Filtrar por, en la sección Tipo de fuente de datos, selecciona Bases de datos.

    Como alternativa, en el campo Buscar fuentes de datos, ingresa postgresql.

  4. En la sección Fuentes de datos destacadas, haz clic en PostgreSQL.

  5. En la sección Accede a los datos externos implementados, haz clic en la tarjeta de solución Federación de BigQuery con la descripción Consultas federadas a PostgreSQL (no las tarjetas de Cloud SQL ni AlloyDB para PostgreSQL).

    Se abrirá el panel Fuente de datos externa.

  6. En el panel Fuente de datos externa, configura lo siguiente:

    • En el campo ID de conexión, ingresa un ID de conexión único (por ejemplo, postgres_prod_conn).
    • En la sección Tipo de ubicación, selecciona Región y, luego, la región en la que reside tu conjunto de datos de BigQuery. Las consultas que usan esta conexión deben ejecutarse desde esta región.
    • En el campo Nombre descriptivo, ingresa un nombre descriptivo para la conexión (opcional).
    • En el campo Descripción, ingresa una descripción para este recurso de conexión (opcional).
    • En la sección Encriptación, selecciona Google-managed encryption key o Clave de Cloud KMS para usar una clave de encriptación administrada por el cliente (CMEK).
    • En el campo Host:puerto, ingresa el host y el puerto del servidor de la base de datos de PostgreSQL en formato HOST:PORT (por ejemplo, 10.10.2.2:5432).
    • En el campo Nombre de usuario, ingresa el usuario de la base de datos que creaste en los requisitos previos.
    • En el campo Contraseña, ingresa la contraseña del usuario de la base de datos.
    • En el campo Base de datos, ingresa el nombre de la base de datos de PostgreSQL.
    • (Opcional) En el campo Adjunto de red, si usas la conectividad privada, ingresa la ruta de acceso del recurso del adjunto de red en el formato projects/PROJECT_ID/regions/NA_REGION/networkAttachments/ATTACHMENT_NAME.
    • En la lista Modo TLS, selecciona uno de los modos TLS admitidos (por ejemplo, Encriptar datos, verificar la AC y el nombre de host (recomendado)).
    • En la lista Autoridad certificadora (AC), selecciona AC pública (PKI web) o selecciona una AC personalizada y pega el certificado con formato PEM.

  7. Haz clic en Crear conexión.

bq

Usa el comando bq mk con la marca --connection:

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

Reemplaza lo siguiente:

  • LOCATION: Es la región de BigQuery (por ejemplo, us-central1) que coincide con el conjunto de datos que deseas consultar junto con PostgreSQL. Las consultas que usan esta conexión deben ejecutarse desde esta región.
  • PROJECT_ID: Es el ID del proyecto de Google Cloud .
  • HOST_PORT: Es el host y el puerto de tu servidor de base de datos de PostgreSQL en el formato HOST:PORT (por ejemplo, 10.10.2.2:5432).
  • DATABASE: Es el nombre de la base de datos de PostgreSQL.
  • USERNAME: Es el usuario de la base de datos de PostgreSQL.
  • PASSWORD: Es la contraseña del usuario de la base de datos.
  • TLS_MODE: Es el modo de encriptación TLS (ENCRYPT_VERIFY_CA_AND_HOST, ENCRYPT_VERIFY_CA, ENCRYPT_VERIFY_NONE o DISABLE). Si usas un certificado de la AC personalizado con ENCRYPT_VERIFY_CA_AND_HOST o ENCRYPT_VERIFY_CA, incluye el objeto "private_pki": { "trusted_certificates_pem": "CERT_CONTENT" } en "tls".
  • NA_REGION: Es la región en la que se encuentra el adjunto de red.
  • ATTACHMENT_NAME: Es el nombre de tu adjunto de red. Si te conectas a través de una IP pública, omite el objeto "network".
  • CONNECTION_ID: Es el identificador único de esta conexión.
  • --kms_key_name=KMS_KEY_NAME (opcional): Es una clave de encriptación administrada por el cliente (CMEK). Si se omite, las credenciales se protegen con la Google-owned and Google-managed encryption keypredeterminada.

Configuración de la seguridad de la capa de transporte (TLS)

El conector de PostgreSQL admite la seguridad de la capa de transporte (TLS) para encriptar el tráfico de consultas en tránsito entre BigQuery y tu base de datos de PostgreSQL.

Cuando conectes BigQuery a PostgreSQL, selecciona uno de los siguientes modos de TLS:

  • Encripta los datos y verifica la autoridad certificadora (AC) y el nombre de host (modo ENCRYPT_VERIFY_CA_AND_HOST). Realiza una validación completa del servidor con TLS. Encripta todos los datos en tránsito, verifica que el certificado del servidor de la base de datos esté firmado por una AC de confianza y comprueba que el nombre de host al que te conectas coincida con el nombre común (CN) o el nombre alternativo del sujeto (SAN) en el certificado. Es obligatorio para las conexiones a través de IPs públicas.

  • Encriptar datos y verificar solo la AC (modo ENCRYPT_VERIFY_CA). Encripta todos los datos en tránsito y verifica que el certificado del servidor esté firmado por una AC de confianza, pero no verifica el nombre de host del servidor. Usa este modo si el certificado del servidor está firmado por una AC de confianza, pero te conectas con una dirección IP o un nombre de host que no coincide con el CN o el SAN del certificado.

  • Solo encriptación (modo ENCRYPT_VERIFY_NONE) Encripta todos los datos transferidos entre BigQuery y el servidor. No realiza la verificación del certificado ni del nombre de host. Se recomienda solo dentro de VPC privadas.

  • Sin encriptación ni verificación (modo DISABLE) No encripta los datos ni realiza la verificación del certificado o del nombre de host. Solo se permite cuando se conecta a través de adjuntos de red privada dentro de redes de VPC protegidas.

Certificados de servidor de confianza en formato PEM

Si usas el modo ENCRYPT_VERIFY_CA_AND_HOST o el modo ENCRYPT_VERIFY_CA, haz lo siguiente:

  • Si el certificado de tu servidor de base de datos está firmado por una CA pública (como las incluidas en el programa de CA raíz de Mozilla, como Let's Encrypt, DigiCert o Google Trust Services), no es necesario que proporciones un certificado. Los certificados raíz de las AC públicas son de confianza de forma predeterminada.
  • Si usas un certificado firmado por una CA interna o privada dentro de tu organización, o un certificado autofirmado, debes proporcionar la cadena de certificados completa en formato de correo con privacidad mejorada (PEM) en el campo Certificado PEM de confianza durante la configuración de la conexión.

Comparte conexiones con usuarios

Puedes otorgar los siguientes roles para permitir que los usuarios consulten datos y administren conexiones:

  • roles/bigquery.connectionUser: Permite que los usuarios usen conexiones para conectarse con fuentes de datos externas y ejecutar consultas en ellas.

  • roles/bigquery.connectionAdmin: Permite que los usuarios administren conexiones.

Para obtener más información sobre los roles y los permisos de IAM en BigQuery, consulta Roles y permisos predefinidos.

Selecciona una de las opciones siguientes:

Console

  1. Ve a la página de BigQuery.

    Ir a BigQuery

    Las conexiones se enumeran en tu proyecto, en un grupo llamado Conexiones.

  2. En el panel de la izquierda, haz clic en Explorar.

    Botón destacado del panel Explorador.

    Si no ves el panel izquierdo, haz clic en Expandir panel izquierdo para abrirlo.

  3. Haz clic en tu proyecto, en Conexiones y, luego, selecciona una conexión.

  4. En el panel Detalles, haz clic en Compartir para compartir una conexión. A continuación, sigue estos pasos:

    1. En el cuadro de diálogo Permisos de conexión, agrega o edita las principales para compartir la conexión con otros principales.

    2. Haz clic en Guardar.

bq

No puedes compartir una conexión con la herramienta de línea de comandos de bq. Para compartir una conexión, usa la consola de Google Cloud o el método de la API de BigQuery Connections.

API

Consulta el método projects.locations.connections.setIAM en la sección de referencia de la API de REST de las conexiones de BigQuery y proporciona una instancia del recurso policy.

Java

Antes de probar este ejemplo, sigue las instrucciones de configuración para Java incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas cliente. 

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

Precios

Las consultas y conexiones federadas están sujetas a los siguientes cargos:

¿Qué sigue?