Carga datos de PostgreSQL en BigQuery

Puedes cargar datos de PostgreSQL a BigQuery con el conector del Servicio de transferencia de datos de BigQuery para PostgreSQL. El conector admite instancias de PostgreSQL alojadas en tu entorno local, Cloud SQL y otros proveedores de servicios en la nube pública, como Amazon Web Services (AWS) y Microsoft Azure. Con el Servicio de transferencia de datos de BigQuery, puedes programar trabajos de transferencia recurrentes que agreguen tus datos más recientes de PostgreSQL a BigQuery.

Limitaciones

Las transferencias de datos de PostgreSQL están sujetas a las siguientes limitaciones:

• La cantidad máxima de ejecuciones de transferencias simultáneas a una sola base de datos de PostgreSQL está determinada por la cantidad máxima de conexiones simultáneas que admite la base de datos de PostgreSQL. La cantidad de trabajos de transferencia simultáneos debe limitarse a un valor inferior a la cantidad máxima de conexiones simultáneas que admite la base de datos de PostgreSQL.

• Una sola configuración de transferencia solo puede admitir una ejecución de transferencia de datos en un momento determinado. Cuando se programa una segunda transferencia de datos para que se ejecute antes de que se complete la primera, solo se completa la primera transferencia de datos, mientras que se omiten las demás transferencias de datos que se superpongan con la primera.

  Para evitar que se omitan transferencias dentro de una sola configuración de transferencia, te recomendamos que aumentes la duración del tiempo entre las transferencias de datos grandes configurando la frecuencia de repetición.

• Durante una transferencia de datos, el conector de PostgreSQL identifica las columnas de clave indexadas y particionadas para transferir tus datos en lotes paralelos. Por este motivo, te recomendamos que especifiques columnas de clave primaria o que uses columnas indexadas en tu tabla para mejorar el rendimiento y reducir la tasa de errores en tus transferencias de datos. Ten en cuenta lo siguiente:

  • Si tienes restricciones de clave primaria o indexada, solo se admiten los siguientes tipos de columnas para crear lotes paralelos:
    • INTEGER
    • TINYINT
    • SMALLINT
    • FLOAT
    • REAL
    • DOUBLE
    • NUMERIC
    • BIGINT
    • DECIMAL
    • DATE
  • Las transferencias de datos de PostgreSQL que no usan clave primaria ni columnas indexadas no pueden admitir más de 2,000,000 de registros por tabla.

Limitaciones de la transferencia incremental

• Solo puedes elegir TIMESTAMP columnas como columnas de marca de agua.
• La transferencia incremental solo se admite para los recursos con columnas de marcas de agua válidas.
• Los valores de una columna de marca de agua deben aumentar de forma monótona.
• Las transferencias incrementales no pueden sincronizar las operaciones de eliminación en la tabla de origen.
• Una sola configuración de transferencia solo puede admitir la transferencia incremental o completa.
• No puedes actualizar los objetos de la lista asset después de la primera ejecución de la incorporación incremental.
• No puedes cambiar el modo de escritura en una configuración de transferencia después de la primera ejecución de la transferencia incremental.
• No puedes cambiar la columna de marca de agua ni la clave primaria después de la primera ejecución de la transferencia incremental.
• La tabla de BigQuery de destino se agrupa con la clave primaria proporcionada y está sujeta a las limitaciones de las tablas agrupadas.

• Cuando actualizas una configuración de transferencia existente al modo de transferencia incremental por primera vez, la primera transferencia de datos después de esa actualización transfiere todos los datos disponibles de tu fuente de datos. Todas las transferencias de datos incrementales posteriores solo transferirán las filas nuevas y actualizadas de tu fuente de datos.

• Te recomendamos que crees índices en la columna de marcas de agua. Este conector usa columnas de marcas de agua para los filtros en las transferencias incrementales, por lo que indexar estas columnas puede mejorar el rendimiento.

• Cuando realices una transferencia incremental, debes usar la asignación de tipos de datos actualizada.

Opciones de transferencia de datos

En las siguientes secciones, se proporciona información sobre las opciones de transferencia de datos cuando configuras una transferencia de datos de PostgreSQL.

Configuración de TLS

El conector de PostgreSQL admite la configuración de seguridad a nivel de transporte (TLS) para encriptar tus transferencias de datos a BigQuery. El conector de PostgreSQL admite los siguientes parámetros de configuración de TLS:

• El modo Encriptar datos y verificar la CA y el nombre de host En este modo, se realiza una validación completa del servidor con TLS a través del protocolo TCPS. Encripta todos los datos en tránsito y verifica que el certificado del servidor de la base de datos esté firmado por una autoridad certificadora (AC) de confianza. Este modo también verifica que el nombre de host al que te conectas coincida exactamente con el nombre común (CN) o con un nombre alternativo del sujeto (SAN) en el certificado del servidor. Este modo evita que los atacantes usen un certificado válido para un dominio diferente y se hagan pasar por tu servidor de base de datos.

  Si tu nombre de host no coincide con el CN o el SAN del certificado, la conexión fallará. Debes configurar una resolución de DNS para que coincida con el certificado o usar un modo de seguridad diferente. Usa este modo para obtener la opción más segura y evitar ataques de intermediario (PITM).

• El modo Encrypt data, and verify CA only En este modo, se encriptan todos los datos con TLS a través del protocolo TCPS y se verifica que el certificado del servidor esté firmado por una CA en la que confía el cliente. Sin embargo, este modo no verifica el nombre de host del servidor. Este modo se conecta correctamente siempre que el certificado sea válido y lo haya emitido una CA de confianza, independientemente de si el nombre de host del certificado coincide con el nombre de host al que te conectas.

  Usa este modo si deseas asegurarte de conectarte a un servidor cuyo certificado esté firmado por una CA de confianza, pero el nombre de host no se puede verificar o no tienes control sobre la configuración del nombre de host.

• El modo Solo encriptación Este modo encripta todos los datos transferidos entre el cliente y el servidor. No realiza ninguna validación de certificado o nombre de host.

  Este modo proporciona cierto nivel de seguridad, ya que protege los datos en tránsito, pero puede ser vulnerable a los ataques de PITM.

  Usa este modo si necesitas asegurarte de que todos los datos estén encriptados, pero no puedes o no quieres verificar la identidad del servidor. Te recomendamos que uses este modo cuando trabajes con VPC privadas.

• El modo Sin encriptación ni verificación Este modo no encripta ningún dato y no realiza ninguna verificación de certificados ni de nombres de host. Todos los datos se envían como texto sin formato.

  No recomendamos usar este modo en un entorno en el que se manejan datos sensibles. Solo recomendamos usar este modo para realizar pruebas en una red aislada en la que la seguridad no sea un problema.

Certificado del servidor de confianza (PEM)

Si usas el modo Encrypt data, and verify CA and hostname o el modo Encrypt data, and verify CA, también puedes proporcionar uno o más certificados codificados en PEM. Estos certificados son necesarios en algunos casos en los que el Servicio de transferencia de datos de BigQuery necesita verificar la identidad del servidor de la base de datos durante la conexión TLS:

• Si usas un certificado firmado por una CA privada dentro de tu organización o un certificado autofirmado, debes proporcionar la cadena de certificados completa o el certificado autofirmado único. Esto es necesario para los certificados emitidos por las AC internas de los servicios administrados del proveedor de servicios en la nube, como el Servicio de Base de datos Relacionales de Amazon (RDS).
• Si tu certificado de servidor de base de datos está firmado por una CA pública (por ejemplo, Let's Encrypt, DigiCert o GlobalSign), no es necesario que proporciones un certificado. Los certificados raíz de estas AC públicas están preinstalados y el Servicio de transferencia de datos de BigQuery los considera confiables.

Puedes especificar certificados con codificación PEM en el campo Certificado PEM de confianza de la configuración de transferencia, con los siguientes requisitos:

• El certificado debe ser una cadena de certificados válida con codificación PEM.
• El certificado debe ser completamente correcto. Si falta algún certificado en la cadena o el contenido es incorrecto, la conexión TLS fallará.
• Para un solo certificado, puedes proporcionar un solo certificado autofirmado del servidor de la base de datos.
• En el caso de una cadena de certificados completa emitida por una CA privada, debes proporcionar la cadena de confianza completa. Esto incluye el certificado del servidor de la base de datos y los certificados de CA intermedios y raíz.

Transferencias completas o incrementales

Para especificar cómo se cargan los datos en BigQuery, selecciona la preferencia de escritura Completa o Incremental en la configuración de transferencia cuando configures una transferencia de PostgreSQL. Las transferencias incrementales son compatibles con la versión preliminar.

Como alternativa, puedes configurar una transferencia de datos incremental (versión preliminar) para transferir solo los datos que se modificaron desde la última transferencia de datos, en lugar de cargar el conjunto de datos completo con cada transferencia de datos. Si configuraste una transferencia de datos incremental, debes especificar los modos de escritura append o upsert para definir cómo se escriben los datos en BigQuery durante una transferencia de datos incremental. En las siguientes secciones, se describen los modos de escritura disponibles.

Modo de escritura de anexión

El modo de escritura de anexar solo inserta filas nuevas en tu tabla de destino. Esta opción agrega estrictamente los datos transferidos sin verificar si existen registros, por lo que este modo puede provocar la duplicación de datos en la tabla de destino.

Cuando seleccionas el modo de anexar, debes seleccionar una columna de marca de agua. Se requiere una columna de marca de agua para que el conector de PostgreSQL haga un seguimiento de los cambios en la tabla de origen.

Modo de escritura Upsert

El modo de escritura upsert actualiza una fila o inserta una nueva en la tabla de destino verificando si hay una clave primaria. Puedes especificar una clave primaria para permitir que el conector de PostgreSQL determine qué cambios son necesarios para mantener tu tabla de destino actualizada con tu tabla de origen. Si la clave primaria especificada está presente en la tabla de BigQuery de destino durante una transferencia de datos, el conector de PostgreSQL actualiza esa fila con los datos nuevos de la tabla de origen. Si no hay una clave primaria durante una transferencia de datos, el conector de PostgreSQL inserta una fila nueva.

Cuando seleccionas el modo de upsert, debes seleccionar una columna de marca de agua y una clave primaria:

• Se requiere una columna de marca de agua para que el conector de PostgreSQL haga un seguimiento de los cambios en la tabla de origen.

  Selecciona una columna de marca de agua que se actualice cada vez que se modifique una fila. Recomendamos columnas similares a la columna UPDATED_AT o LAST_MODIFIED.

• La clave primaria puede ser una o más columnas de tu tabla que son necesarias para que el conector de PostgreSQL determine si necesita insertar o actualizar una fila.

  Selecciona las columnas que contengan valores no nulos que sean únicos en todas las filas de la tabla. Te recomendamos que incluyas columnas que contengan identificadores generados por el sistema, códigos de referencia únicos (por ejemplo, IDs de incremento automático) o IDs de secuencia inmutables basados en el tiempo.

  Para evitar la posible pérdida o corrupción de datos, las columnas de clave primaria que selecciones deben tener valores únicos. Si tienes dudas sobre la singularidad de la columna de clave primaria que elegiste, te recomendamos que uses el modo de escritura de anexar.

Comportamiento de la transferencia incremental

Cuando realizas cambios en el esquema de la tabla en tu fuente de datos, las transferencias de datos incrementales de esas tablas se reflejan en BigQuery de las siguientes maneras:

Antes de comenzar

• Crea un usuario en la base de datos de PostgreSQL.
• Verifica si completaste todas las acciones necesarias para habilitar el Servicio de transferencia de datos de BigQuery.
• Crea un conjunto de datos de BigQuery para almacenar tus datos.
• Asegúrate de tener los roles necesarios para completar las tareas de este documento.

Roles obligatorios

Si quieres configurar las notificaciones de ejecución de transferencias para Pub/Sub, asegúrate de tener el permiso pubsub.topics.setIamPolicy de Identity and Access Management (IAM). Los permisos de Pub/Sub no son necesarios si solo configuras las notificaciones por correo electrónico. Para obtener más información, consulta la sección sobre notificaciones de ejecución del Servicio de transferencia de datos de BigQuery.

Para obtener los permisos que necesitas para crear una transferencia de datos de Servicio de transferencia de datos de BigQuery, pídele a tu administrador que te otorgue el rol de IAM de administrador de BigQuery (roles/bigquery.admin) en tu proyecto. Para obtener más información sobre cómo otorgar roles, consulta Administra el acceso a proyectos, carpetas y organizaciones.

Este rol predefinido contiene los permisos necesarios para crear una transferencia de datos del Servicio de transferencia de datos de BigQuery. Para ver los permisos exactos que son necesarios, expande la sección Permisos requeridos:

También puedes obtener estos permisos con roles personalizados o con otros roles predefinidos.

Para obtener más información, consulta Cómo otorgar acceso a bigquery.admin.

Conexiones de red

Si no hay una dirección IP pública disponible para la conexión de la base de datos de PostgreSQL, debes configurar un adjunto de red.

Para obtener instrucciones detalladas sobre la configuración de red necesaria, consulta los siguientes documentos:

• Si realizas la transferencia desde Cloud SQL, consulta Cómo configurar el acceso a la instancia de Cloud SQL.
• Si realizas la transferencia desde AWS, consulta Configura la VPN de AWS-Cloud de Confiance y la conexión de red.
• Si realizas la transferencia desde Azure, consulta Configura la VPN de Azure-Cloud de Confiance y la conexión de red.

Configura una transferencia de datos de PostgreSQL

Para agregar datos de PostgreSQL a BigQuery, configura una transferencia con una de las siguientes opciones:

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"],
  

Para ejecutar manualmente una transferencia de datos fuera de tu programa habitual, puedes iniciar una ejecución de reabastecimiento.

Asignación de tipos de datos

En la siguiente tabla, se asignan los tipos de datos de PostgreSQL a los tipos de datos de BigQuery correspondientes.

Solucionar problemas

Si tienes problemas para configurar tu transferencia de datos, consulta Problemas de transferencia de PostgreSQL.

Precios

Para obtener información sobre los precios de las transferencias de PostgreSQL, consulta Precios del Servicio de transferencia de datos.

¿Qué sigue?

• Lee una descripción general del Servicio de transferencia de datos de BigQuery.
• Obtén información para administrar transferencias, lo que incluye obtener información sobre una configuración de transferencia, mostrar distintas configuraciones o visualizar su historial de ejecuciones.
• Obtén más información sobre cómo cargar datos con operaciones entre nubes.