Carregar dados do PostgreSQL no BigQuery

Para programar transferências de dados recorrentes do PostgreSQL para o BigQuery, crie uma configuração de transferência para especificar quais objetos de dados serão transferidos e com que frequência programar a transferência de dados. Depois de definir a configuração de transferência, o serviço de transferência de dados do BigQuery transfere os dados mais recentes para uma tabela do BigQuery na programação especificada.

Para saber como funciona uma transferência do PostgreSQL, consulte Introdução às transferências de dados do PostgreSQL.

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 compatíveis com o banco de dados PostgreSQL.

  • Uma única configuração de transferência só pode dar 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, enquanto as outras transferências de dados que se sobrepõem à primeira são ignoradas.

    Para evitar transferências ignoradas em uma única configuração de transferência, recomendamos que você aumente o tempo entre grandes transferências de dados 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 esse motivo, 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 chave primária ou indexada, somente os seguintes tipos de coluna serão compatíveis com 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 aceitar mais de 2.000.000 de registros por tabela.

Limitações de transferência incremental

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

  • Só é possível escolher colunas TIMESTAMP 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 aceitar 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 alterar 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 alterar 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 em cluster 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 de dados. Todas as transferências de dados incrementais subsequentes vão transferir apenas as linhas novas e atualizadas da 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.

  • Ao fazer uma transferência incremental, use o mapeamento de tipo de dados atualizado.

Antes de começar

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 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 de Administrador do BigQuery (roles/bigquery.admin) no 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:

Permissões necessárias

As permissões a seguir são necessárias para criar uma transferência de dados do serviço de transferência de dados do BigQuery:

  • Permissões do serviço de transferência de dados do BigQuery:
    • bigquery.transfers.update
    • bigquery.transfers.get
  • Permissões do BigQuery:
    • bigquery.datasets.get
    • bigquery.datasets.getIamPolicy
    • bigquery.datasets.update
    • bigquery.datasets.setIamPolicy
    • bigquery.jobs.create

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 do banco de dados PostgreSQL, será necessário configurar um anexo de rede.

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

Configurar uma transferência de dados do PostgreSQL

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

Console

  1. Acesse a página Transferências de dados.

    Acesse Transferências de dados

  2. Clique em Criar transferência.

  3. Na seção Tipo de origem, em Origem, selecione PostgreSQL.

  4. Na seção Detalhes da fonte de dados, faça o seguinte:

    • Em Anexo de rede, selecione um anexo de rede existente ou clique em Criar anexo de rede. Para mais informações, consulte a seção Conexões de rede deste documento.
    • Em Host, insira o nome do host ou o endereço IP do servidor de banco de dados PostgreSQL.
    • Em Número da porta, insira o número da porta do servidor de banco de dados PostgreSQL.
    • Em Nome do banco de dados, insira o nome do banco de dados PostgreSQL.
    • Em Nome de usuário, insira o nome do usuário do PostgreSQL que está iniciando a conexão com o banco de dados.
    • Em Senha, insira a senha do usuário do PostgreSQL que iniciou a conexão do banco de dados PostgreSQL.
    • Em Modo TLS, selecione uma opção no menu. Para mais informações sobre os modos TLS, consulte Configuração do TLS.
    • Em Certificado PEM confiável, insira o certificado público da autoridade de certificação (CA) que emitiu o certificado TLS do servidor de banco de dados. Para mais informações, consulte Certificado de servidor confiável (PEM).
    • Em Ativar mapeamento legado, selecione true (padrão) para usar o mapeamento legado do tipo de dados. Selecione false para usar o mapeamento de tipo de dados atualizado. Se você estiver fazendo uma transferência incremental, esse valor precisará ser false. Para mais informações sobre as atualizações de mapeamento de tipo de dados, consulte 16 de março de 2027.
    • Em Tipo de ingestão, selecione Completa ou Incremental.
      • Se você selecionar Incremental, em Modo de gravação, selecione Adicionar ao final ou Upsert. Para mais informações sobre os diferentes modos de gravação, consulte Transferências completas ou incrementais.

    • Em Objetos do PostgreSQL a serem transferidos, clique em Procurar.

      Selecione os objetos a serem transferidos para o conjunto de dados de destino do BigQuery. Nesse campo, também é possível inserir manualmente qualquer objeto para incluir na transferência de dados.

      • Se você tiver selecionado Adicionar como modo de gravação incremental, escolha uma coluna como marca d'água.
      • Se você tiver selecionado Upsert como modo de gravação incremental, selecione uma coluna como marca d'água e uma ou mais colunas como chave primária.

  5. No campo Nome de exibição, na seção Nome de configuração da transferência, insira um nome para a transferência. O nome da transferência pode ser qualquer valor que permita identificá-la, caso você precise modificá-la mais tarde.

  6. Na seção Opções de programação, realize estas ações:

    • Selecione uma frequência de repetição. Se você selecionar a opção Horas, Dias (padrão), Semanas ou Meses, também precisará especificar uma frequência. Também é possível selecionar a opção Personalizada para criar uma frequência de repetição mais específica. Se você selecionar a opção On demand, essa transferência de dados só vai ser executada quando você acioná-la manualmente.
    • Se aplicável, selecione Começar agora ou Começar no horário definido e escolha uma data de início e um horário de execução.

  7. Na seção Configurações de destino, em Conjunto de dados, selecione o conjunto de dados que você criou para armazenar seus dados ou clique em Criar novo conjunto de dados e crie um para usar como o conjunto de dados de destino.

  8. Opcional: na seção Opções de notificação, faça o seguinte:

    • Para ativar as notificações por e-mail, clique no botão de alternância Notificações por e-mail. Quando você ativa essa opção, o administrador de transferências recebe uma notificação por e-mail quando ocorre uma falha na execução de uma transferência.
    • Para configurar as notificações de execução do Pub/Sub para sua transferência, clique no botão de alternância Notificações do Pub/Sub para a posição de ativação. Selecione o nome do tópico ou clique em Criar um tópico.

  9. Clique em Salvar.

bq

Insira o comando bq mk e forneça a flag de criação da transferência --transfer_config:

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

Substitua:

  • PROJECT_ID (opcional): o ID do projeto do Google Cloud . Se a flag --project_id não for fornecida para especificar um projeto específico, o projeto padrão será usado.
  • DATA_SOURCE: a fonte de dados, que é postgresql.
  • DISPLAY_NAME: o nome de exibição da configuração da transferência de dados. O nome da transferência pode ser qualquer valor que permita identificá-la, caso você precise modificá-la mais tarde.
  • DATASET: o conjunto de dados de destino na configuração da transferência de dados.

  • PARAMETERS é o parâmetro da configuração de transferência criada no formato JSON. Por exemplo, --params='{"param":"param_value"}'. Veja a seguir os parâmetros de uma transferência do PostgreSQL:

    • connector.networkAttachment (opcional): o nome do anexo de rede para se conectar ao banco de dados PostgreSQL.
    • connector.database: o nome do banco de dados PostgreSQL.
    • connector.endpoint.host: o nome do host ou o endereço IP do banco de dados.
    • connector.endpoint.port: o número da porta do banco de dados.
    • connector.authentication.username: o nome de usuário do usuário do banco de dados.
    • connector.authentication.password: a senha do usuário do banco de dados.
    • connector.tls.mode: especifique uma configuração de TLS para usar com esta transferência:
      • ENCRYPT_VERIFY_CA_AND_HOST para criptografar dados e verificar a AC e o nome do host
      • ENCRYPT_VERIFY_CA para criptografar dados e verificar apenas a AC
      • ENCRYPT_VERIFY_NONE apenas para criptografia de dados
      • DISABLE para nenhuma criptografia ou verificação
    • connector.tls.trustedServerCertificate: (opcional) forneça um ou mais certificados codificados em PEM. Obrigatório apenas se connector.tls.mode for ENCRYPT_VERIFY_CA_AND_HOST ou ENCRYPT_VERIFY_CA.
    • ingestionType: especifique full ou incremental. As transferências incrementais estão disponíveis em pré-lançamento. Para mais informações, consulte Transferências completas ou incrementais.
    • writeMode: especifique WRITE_MODE_APPEND ou WRITE_MODE_UPSERT.
    • watermarkColumns: especifica colunas na tabela como colunas de marca-d'água. Este campo é obrigatório para transferências incrementais.
    • primaryKeys: especifique colunas na tabela como chaves primárias. Este campo é obrigatório para transferências incrementais.
    • connector.legacyMapping: defina como true (padrão) para usar o mapeamento de tipo de dados legado. Defina como false para usar o mapeamento de tipo de dados atualizado. Se você estiver fazendo uma transferência incremental, esse valor precisará ser false. Para mais informações sobre as atualizações de mapeamento de tipo de dados, consulte 16 de março de 2027.
    • assets: uma lista dos nomes das tabelas do PostgreSQL a serem transferidas do banco de dados PostgreSQL como parte da transferência.

Por exemplo, o comando a seguir cria uma transferência do PostgreSQL chamada My Transfer:

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"}'

Quando você especifica vários recursos durante uma transferência incremental, os valores dos campos watermarkColumns e primaryKeys correspondem à posição dos valores no campo assets. No exemplo a seguir, dep_id corresponde à tabela DB1/USER1/DEPARTMENT, enquanto report_by e report_title correspondem à tabela DB1/USER1/EMPLOYEES.

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

API

Use o método projects.locations.transferConfigs.create e forneça uma instância do recurso TransferConfig.

Quando você salva a configuração de transferência, o conector do PostgreSQL aciona automaticamente uma execução de transferência de acordo com a opção de programação. A cada execução de transferência, o conector do PostgreSQL transfere todos os dados disponíveis do PostgreSQL para o BigQuery.

Para executar manualmente uma transferência de dados fora da programação regular, inicie uma execução de preenchimento.

Resolver problemas

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

Metadados de transferência

Também é possível usar o conector do PostgreSQL para transferir metadados para o Knowledge Catalog. Para mais informações, consulte Carregar metadados do PostgreSQL no Knowledge Catalog.

A seguir