Carregue dados do PostgreSQL para o BigQuery

Pode carregar dados do PostgreSQL para o BigQuery através do conetor do Serviço de transferência de dados do BigQuery para PostgreSQL. O conetor suporta instâncias do PostgreSQL alojadas no seu ambiente no local, no Cloud SQL, bem como noutros fornecedores de nuvem pública, como a Amazon Web Services (AWS) e o Microsoft Azure. Com o Serviço de transferência de dados do BigQuery, pode agendar tarefas de transferência recorrentes que adicionam os 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ências simultâneas para uma única base de dados do PostgreSQL é determinado pelo número máximo de ligações simultâneas suportadas pela base de dados do PostgreSQL. O número de tarefas de transferência simultâneas deve ser limitado a um valor inferior ao número máximo de ligações simultâneas suportadas pela base de dados PostgreSQL.
  • No PostgreSQL, alguns tipos de dados são mapeados para o tipo de string no BigQuery para evitar a perda de dados. Por exemplo, os tipos numéricos definidos no PostgreSQL que não têm uma precisão e uma escala definidas são mapeados para o tipo de string no BigQuery.
  • Uma única configuração de transferência só pode suportar uma execução de transferência de dados num determinado momento. No caso em que uma segunda transferência de dados esteja agendada para ser executada antes de a primeira transferência estar concluída, apenas a primeira transferência de dados é concluída, enquanto todas as outras transferências de dados que se sobreponham à primeira transferência são ignoradas.
    • Para evitar transferências ignoradas numa única configuração de transferência, recomendamos que aumente a duração do tempo entre transferências de dados grandes configurando a frequência de repetição.

Antes de começar

Funções necessárias

Se pretender configurar notificações de execução de transferências para o Pub/Sub, certifique-se de que tem a autorização de pubsub.topics.setIamPolicygestão de identidade e de acesso (IAM) . Não são necessárias autorizações do Pub/Sub se configurar apenas notificações por email. Para mais informações, consulte o artigo Notificações de execução do Serviço de transferência de dados do BigQuery.

Para receber as autorizações de que precisa para criar uma transferência de dados do PostgreSQL, peça ao seu administrador para lhe conceder a função do IAM de administrador do BigQuery (roles/bigquery.admin) no seu projeto. Para mais informações sobre a atribuição de funções, consulte o artigo Faça a gestão do acesso a projetos, pastas e organizações.

Esta função predefinida contém as autorizações necessárias para criar uma transferência de dados do PostgreSQL. Para ver as autorizações exatas que são necessárias, expanda a secção Autorizações necessárias:

Autorizações necessárias

São necessárias as seguintes autorizações para criar uma transferência de dados do PostgreSQL:

  • bigquery.transfers.update
  • bigquery.datasets.get

Também pode conseguir estas autorizações com funções personalizadas ou outras funções predefinidas.

Ligações de rede

Se não estiver disponível um endereço IP público para a ligação à base de dados do PostgreSQL, tem de configurar uma associação de rede.

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

Configure uma transferência de dados do PostgreSQL

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

Consola

  1. Aceda à página Transferências de dados.

    Aceda a Transferências de dados

  2. Clique em Criar transferência.

  3. Na secção Tipo de origem, para Origem, selecione PostgreSQL.

  4. Na secção Nome da configuração de transferência, para Nome a apresentar, introduza um nome para a transferência. O nome da transferência pode ser qualquer valor que lhe permita identificar a transferência se precisar de a modificar mais tarde.

  5. Na secção Opções de agendamento, faça o seguinte:

    • Selecione uma frequência de repetição. Se selecionar a opção Horas, Dias (predefinição), Semanas ou Meses, também tem de especificar uma frequência. Também pode selecionar a opção Personalizado para criar uma frequência de repetição mais específica. Se selecionar a opção A pedido, esta transferência de dados só é executada quando aciona manualmente a transferência.
    • Se aplicável, selecione a opção Começar agora ou Começar a uma hora definida e indique uma data de início e um tempo de execução.

  6. Na secção Definições de destino, para Conjunto de dados, selecione o conjunto de dados que criou para armazenar os dados ou clique em Criar novo conjunto de dados e crie um para usar como conjunto de dados de destino.

  7. Na secção Detalhes da origem de dados, faça o seguinte:

    • Para Anexo de rede, selecione um anexo de rede existente ou clique em Criar anexo de rede. Para mais informações, consulte a secção Ligações de rede deste documento.
    • Para Anfitrião, introduza o nome de anfitrião ou o endereço IP do servidor de base de dados PostgreSQL.
    • Para Número da porta, introduza o número da porta do servidor de base de dados do PostgreSQL.
    • Em Nome da base de dados, introduza o nome da base de dados do PostgreSQL.
    • Para Nome de utilizador, introduza o nome de utilizador do utilizador do PostgreSQL que inicia a ligação à base de dados do PostgreSQL.
    • Para Palavra-passe, introduza a palavra-passe do utilizador do PostgreSQL que inicia a ligação à base de dados do PostgreSQL.

    • Para transferir objetos do PostgreSQL, execute um dos seguintes procedimentos:

      • Clique em Procurar para selecionar as tabelas do PostgreSQL necessárias para a transferência e, de seguida, clique em Selecionar.
      • Introduza manualmente os nomes das tabelas nos objetos PostgreSQL a transferir.

    Detalhes da origem de dados na página Criar transferência

  8. Opcional: no menu Conta de serviço, especifique uma conta de serviço personalizada para autorizar a transferência. Certifique-se de que a conta de serviço usada tem todas as funções e autorizações necessárias. Para mais informações, consulte o artigo Transfira a propriedade como uma conta de serviço.

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

    • Para ativar as notificações por email, clique no botão Notificações por email para o ativar. Quando ativa esta opção, o administrador da transferência recebe uma notificação por email quando uma execução de transferência falha.
    • Para configurar notificações executadas pelo Pub/Sub para a transferência, clique no botão Notificações do Pub/Sub para o ativar. Pode selecionar o nome do tópico ou clicar em Criar um tópico para criar um.

  10. Clique em Guardar.

bq

Introduza o comando bq mk e forneça a flag de criação de 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 o seguinte:

  • PROJECT_ID (opcional): o ID do seu Trusted Cloud projeto. Se a flag --project_id não for fornecida para especificar um projeto em particular, é usado o projeto predefinido.
  • DATA_SOURCE: a origem de dados, que é postgresql.
  • DISPLAY_NAME: o nome a apresentar da configuração de transferência de dados. O nome da transferência pode ser qualquer valor que lhe permita identificar a transferência se precisar de a modificar mais tarde.
  • DATASET: o conjunto de dados de destino para a configuração de transferência de dados.

  • PARAMETERS: os parâmetros da configuração de transferência criada no formato JSON. Por exemplo: --params='{"param":"param_value"}'. Seguem-se os parâmetros para uma transferência do PostgreSQL:

    • networkAttachment (opcional): o nome da associação de rede a associar à base de dados PostgreSQL.
    • connector.database: o nome da base de dados do PostgreSQL.
    • connector.endpoint.host: o nome do anfitrião ou o endereço IP da base de dados.
    • connector.endpoint.port: o número da porta da base de dados.
    • connector.authentication.username: o nome de utilizador do utilizador da base de dados.
    • connector.authentication.password: a palavra-passe do utilizador da base de dados.
    • assets: uma lista dos nomes das tabelas do PostgreSQL a transferir da base de dados do PostgreSQL como parte da transferência.

Por exemplo, o comando seguinte cria uma transferência do PostgreSQL denominada 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}'

API

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

Quando guarda a configuração da transferência, o conetor do PostgreSQL aciona automaticamente uma execução da transferência de acordo com a opção de agendamento. Com cada execução de transferência, o conetor do PostgreSQL transfere todos os dados disponíveis do PostgreSQL para o BigQuery.

Para executar manualmente uma transferência de dados fora da sua programação normal, pode iniciar uma execução de preenchimento.

Mapeamento de tipos de dados

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

Tipo de dados do PostgreSQL Tipo de dados do BigQuery
bigint INTEGER
bigserial INTEGER
bit(n) STRING
bit varying(n) STRING
boolean BOOLEAN
bytea BYTES
character STRING
character varying STRING
double precision FLOAT
integer INTEGER
numeric(precision, scale)/decimal(precision, scale) NUMERIC
real FLOAT
smallint INTEGER
smallserial INTEGER
serial INTEGER
text STRING
date DATE
time [ (p) ] [ without timezone ] TIMESTAMP
time [ (p) ] with time zone TIMESTAMP
timestamp [ (p) ] [ without timezone ] TIMESTAMP
timestamp [ (p) ] with time zone TIMESTAMP
xml STRING
tsquery STRING
tsvector STRING
uuid STRING
box STRING
cidr STRING
circle STRING
inet STRING
interval STRING
json STRING
jsonb STRING
line STRING
lseg STRING
macaddr STRING
macaddr8 STRING
money STRING
path STRING
point STRING
polygon STRING

Resolver problemas

Se tiver problemas com a configuração da transferência de dados, consulte o artigo Problemas de transferência do PostgreSQL.

O que se segue?