Carregar dados do PostgreSQL no BigQuery

É possível carregar dados do PostgreSQL para o BigQuery usando o conector do serviço de transferência de dados do BigQuery para PostgreSQL. O conector é compatível com instâncias do PostgreSQL hospedadas no seu ambiente local, no Cloud SQL e em outros provedores de nuvem pública, como Amazon Web Services (AWS) e Microsoft Azure. Com o serviço de transferência de dados do BigQuery, é possível programar jobs de transferência recorrentes que adicionam seus dados mais recentes do PostgreSQL ao BigQuery.

Limitações

As transferências de dados do PostgreSQL estão sujeitas às seguintes limitações:

• O número máximo de execuções de transferência simultâneas para um único banco de dados PostgreSQL é determinado pelo número máximo de conexões simultâneas compatíveis com o banco de dados PostgreSQL. O número de jobs de transferência simultâneos precisa ser limitado a um valor menor que o número máximo de conexões simultâneas aceitas pelo banco de dados PostgreSQL.

• Uma única configuração de transferência só pode oferecer suporte a uma execução de transferência de dados por vez. Quando uma segunda transferência de dados é programada para ser executada antes da conclusão da primeira, apenas a primeira é concluída. As outras que se sobrepõem a ela são ignoradas.

  Para evitar transferências ignoradas em uma única configuração, recomendamos aumentar a duração entre transferências de dados grandes configurando a frequência de repetição.

• Durante uma transferência de dados, o conector do PostgreSQL identifica colunas de chave indexadas e particionadas para transferir seus dados em lotes paralelos. Por isso, recomendamos que você especifique colunas de chave primária ou use colunas indexadas na tabela para melhorar o desempenho e reduzir a taxa de erros nas transferências de dados. Considere o seguinte:

  • Se você tiver restrições de índice ou de chave primária, apenas os seguintes tipos de coluna serão compatíveis para a criação de lotes paralelos:
    • INTEGER
    • TINYINT
    • SMALLINT
    • FLOAT
    • REAL
    • DOUBLE
    • NUMERIC
    • BIGINT
    • DECIMAL
    • DATE
  • As transferências de dados do PostgreSQL que não usam chave primária ou colunas indexadas não podem ter mais de 2 milhões de registros por tabela.

Limitações da transferência incremental

• Só é possível escolher TIMESTAMP colunas como colunas de marca-d'água.
• A ingestão incremental só é compatível com recursos que têm colunas de marca d'água válidas.
• Os valores em uma coluna de marca-d'água precisam aumentar monotonicamente.
• As transferências incrementais não podem sincronizar operações de exclusão na tabela de origem.
• Uma única configuração de transferência só pode oferecer suporte à ingestão incremental ou completa.
• Não é possível atualizar objetos na lista asset após a primeira execução de ingestão incremental.
• Não é possível mudar o modo de gravação em uma configuração de transferência após a primeira execução de ingestão incremental.
• Não é possível mudar a coluna de marca-d'água ou a chave primária após a primeira execução de ingestão incremental.
• A tabela de destino do BigQuery é agrupada usando a chave primária fornecida e está sujeita às limitações de tabelas em cluster.
• Quando você atualiza uma configuração de transferência para o modo de ingestão incremental pela primeira vez, a primeira transferência de dados após essa atualização transfere todos os dados disponíveis da sua fonte. As transferências incrementais de dados subsequentes vão transferir apenas as linhas novas e atualizadas da sua fonte de dados.
• Recomendamos que você crie índices na coluna de marca-d'água. Esse conector usa colunas de marca-d'água para filtros em transferências incrementais. Portanto, indexar essas colunas pode melhorar o desempenho.

Opções de ingestão de dados

As seções a seguir fornecem informações sobre as opções de ingestão de dados ao configurar uma transferência de dados do PostgreSQL.

Configuração de TLS

O conector PostgreSQL oferece suporte à configuração de segurança no nível de transporte (TLS) para criptografar suas transferências de dados para o BigQuery. O conector PostgreSQL é compatível com as seguintes configurações de TLS:

• O modo Criptografar dados e verificar CA e nome do host. Esse modo realiza uma validação completa do servidor usando TLS sobre o protocolo TCPS. Ele criptografa todos os dados em trânsito e verifica se o certificado do servidor de banco de dados foi assinado por uma autoridade certificadora (AC) confiável. Esse modo também verifica se o nome do host ao qual você está se conectando corresponde exatamente ao nome comum (CN) ou a um nome alternativo do sujeito (SAN) no certificado do servidor. Esse modo impede que invasores usem um certificado válido para um domínio diferente e se passem pelo servidor de banco de dados.

  Se o nome do host não corresponder ao CN ou SAN do certificado, a conexão vai falhar. Configure uma resolução de DNS para corresponder ao certificado ou use um modo de segurança diferente. Use esse modo para ter a opção mais segura e evitar ataques person-in-the-middle (PITM).

• O modo Criptografar dados e verificar somente a AC. Esse modo criptografa todos os dados usando TLS no protocolo TCPS e verifica se o certificado do servidor é assinado por uma CA em que o cliente confia. No entanto, esse modo não verifica o nome do host do servidor. Esse modo se conecta com sucesso desde que o certificado seja válido e emitido por uma CA confiável, independente de o nome do host no certificado corresponder ao nome do host a que você está se conectando.

  Use esse modo se quiser garantir que você está se conectando a um servidor cujo certificado é assinado por uma CA confiável, mas o nome do host não é verificável ou você não tem controle sobre a configuração do nome do host.

• O modo Somente criptografia. Esse modo criptografa todos os dados transferidos entre o cliente e o servidor. Ele não realiza nenhuma validação de certificado ou nome do host.

  Esse modo oferece algum nível de segurança ao proteger os dados em trânsito, mas pode ficar vulnerável a ataques PITM.

  Use esse modo se precisar garantir que todos os dados estejam criptografados, mas não puder ou não quiser verificar a identidade do servidor. Recomendamos usar esse modo ao trabalhar com VPCs particulares.

• O modo Sem criptografia ou verificação. Esse modo não criptografa dados e não realiza nenhuma verificação de certificado ou nome do host. Todos os dados são enviados como texto simples.

  Não recomendamos o uso desse modo em um ambiente em que dados sensíveis são tratados. Recomendamos usar esse modo apenas para testes em uma rede isolada em que a segurança não é uma preocupação.

Certificado de servidor confiável (PEM)

Se você estiver usando o modo Criptografar dados e verificar CA e nome do host ou o modo Criptografar dados e verificar CA, também poderá fornecer um ou mais certificados codificados em PEM. Esses certificados são necessários em alguns cenários em que o serviço de transferência de dados do BigQuery precisa verificar a identidade do servidor de banco de dados durante a conexão TLS:

• Se você estiver usando um certificado assinado por uma CA particular na sua organização ou um certificado autoassinado, forneça a cadeia de certificados completa ou o único certificado autoassinado. Isso é necessário para certificados emitidos por CAs internas de serviços gerenciados de provedores de nuvem, como o Amazon Relational Database Service (RDS).
• Se o certificado do servidor de banco de dados for assinado por uma CA pública (por exemplo, Let's Encrypt, DigiCert ou GlobalSign), não será necessário fornecer um certificado. Os certificados raiz dessas CAs públicas são pré-instalados e confiáveis para o serviço de transferência de dados do BigQuery.

É possível especificar certificados codificados em PEM no campo Certificado PEM confiável na configuração de transferência, com os seguintes requisitos:

• O certificado precisa ser uma cadeia de certificados válida codificada em PEM.
• O certificado precisa estar totalmente correto. Qualquer certificado ausente na cadeia ou conteúdo incorreto causa falha na conexão TLS.
• Para um único certificado, forneça um certificado autoassinado do servidor de banco de dados.
• Para uma cadeia de certificados completa emitida por uma CA particular, forneça a cadeia de confiança completa. Isso inclui o certificado do servidor de banco de dados e todos os certificados de CA intermediários e raiz.

Transferências completas ou incrementais

É possível especificar como os dados são carregados no BigQuery selecionando a preferência de gravação Completa ou Incremental na configuração da transferência ao configurar uma transferência do PostgreSQL. As transferências incrementais são compatíveis com o pré-lançamento.

Como alternativa, configure uma transferência de dados incremental (prévia) para transferir apenas os dados que foram alterados desde a última transferência, em vez de carregar o conjunto de dados inteiro a cada transferência. Se você tiver configurado uma transferência incremental de dados, especifique os modos de gravação append ou upsert para definir como os dados são gravados no BigQuery durante uma transferência incremental de dados. As seções a seguir descrevem os modos de gravação disponíveis.

Modo de gravação de anexação

O modo de gravação por anexação só insere novas linhas na tabela de destino. Essa opção anexa estritamente os dados transferidos sem verificar registros existentes. Portanto, esse modo pode causar duplicação de dados na tabela de destino.

Ao selecionar o modo de adição, é necessário escolher uma coluna de marca d'água. Uma coluna de marca-d'água é necessária para que o conector do PostgreSQL rastreie as mudanças na tabela de origem.

Modo de gravação de upsert

O modo de gravação upsert atualiza uma linha ou insere uma nova linha na tabela de destino verificando uma chave primária. Você pode especificar uma chave primária para permitir que o conector do PostgreSQL determine quais mudanças são necessárias para manter a tabela de destino atualizada com a tabela de origem. Se a chave primária especificada estiver presente na tabela do BigQuery de destino durante uma transferência de dados, o conector do PostgreSQL vai atualizar essa linha com novos dados da tabela de origem. Se uma chave primária não estiver presente durante uma transferência de dados, o conector do PostgreSQL vai inserir uma nova linha.

Ao selecionar o modo de upsert, você precisa escolher uma coluna de marca d'água e uma chave primária:

• Uma coluna de marca-d'água é necessária para que o conector do PostgreSQL rastreie as mudanças na tabela de origem.

  Selecione uma coluna de marca-d'água que seja atualizada sempre que uma linha for modificada. Recomendamos colunas semelhantes à UPDATED_AT ou LAST_MODIFIED.

• A chave primária pode ser uma ou mais colunas na sua tabela que são necessárias para que o conector do PostgreSQL determine se precisa inserir ou atualizar uma linha.

  Selecione colunas que contenham valores não nulos e exclusivos em todas as linhas da tabela. Recomendamos colunas que incluem identificadores gerados pelo sistema, códigos de referência exclusivos (por exemplo, IDs de incremento automático) ou IDs de sequência imutáveis baseados em tempo.

  Para evitar possíveis perda de dados ou corrupção de dados, as colunas de chave primária selecionadas precisam ter valores exclusivos. Se você tiver dúvidas sobre a exclusividade da coluna de chave primária escolhida, recomendamos usar o modo de gravação de adição.

Comportamento de ingestão incremental

Quando você faz mudanças no esquema da tabela na sua fonte de dados, as transferências incrementais de dados dessas tabelas são refletidas no BigQuery das seguintes maneiras:

Antes de começar

• Crie um usuário no banco de dados PostgreSQL.
• Verifique se você concluiu todas as ações necessárias para ativar o serviço de transferência de dados do BigQuery.
• Crie um conjunto de dados do BigQuery para armazenar os dados.
• Verifique se você tem os papéis necessários para concluir as tarefas neste documento.

Funções exigidas

Se quiser configurar notificações de execução de transferência do Pub/Sub, verifique se você tem a permissão pubsub.topics.setIamPolicy do Identity and Access Management (IAM). As permissões do Pub/Sub não serão necessárias caso você configure apenas notificações por e-mail. Para saber mais informações, consulte Notificações de execução do serviço de transferência de dados do BigQuery.

Para receber as permissões necessárias para criar uma transferência de dados do serviço de transferência de dados do BigQuery, peça ao administrador para conceder a você o papel do IAM Administrador do BigQuery (roles/bigquery.admin) no seu projeto. Para mais informações sobre a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações.

Esse papel predefinido contém as permissões necessárias para criar uma transferência de dados do serviço de transferência de dados do BigQuery. Para acessar as permissões exatas necessárias, expanda a seção Permissões necessárias:

Essas permissões também podem ser concedidas com funções personalizadas ou outros papéis predefinidos.

Para mais informações, consulte Conceder acesso ao bigquery.admin.

Conexões de rede

Se um endereço IP público não estiver disponível para a conexão com o banco de dados PostgreSQL, configure um anexo de rede.

Para instruções detalhadas sobre a configuração de rede necessária, consulte os seguintes documentos:

• Se você estiver transferindo do Cloud SQL, consulte Configurar o acesso à instância do Cloud SQL.
• Se você estiver transferindo da AWS, consulte Configurar a VPN e o anexo de rede da AWS-Cloud de Confiance .
• Se você estiver transferindo do Azure, consulte Configurar a VPN do Azure-Cloud de Confiance e o anexo de rede.

Configurar uma transferência de dados do PostgreSQL

Adicione dados do PostgreSQL ao BigQuery configurando uma configuração de transferência usando uma das seguintes opções:

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 executar manualmente uma transferência de dados fora da sua programação regular, inicie uma execução de preenchimento.

Mapeamento de tipo de dados

A tabela a seguir mapeia os tipos de dados do PostgreSQL para os tipos de dados correspondentes do BigQuery.

Resolver problemas

Se você tiver problemas para configurar a transferência de dados, consulte Problemas de transferência do PostgreSQL.

Preços

Para informações sobre preços de transferências do PostgreSQL, consulte Preços do serviço de transferência de dados.

A seguir

• Leia uma visão geral sobre o serviço de transferência de dados do BigQuery.
• Saiba como gerenciar transferências, incluindo informações sobre a configuração, como listar configurações e acessar o histórico de execução.
• Saiba como carregar dados com operações entre nuvens.