Conectar-se ao PostgreSQL

Como administrador do BigQuery, é possível criar uma conexão para acessar dados do PostgreSQL. Com essa conexão, os analistas de dados podem consultar dados no PostgreSQL.

Ambientes e versões compatíveis do PostgreSQL

A federação do BigQuery é compatível com os seguintes ambientes de implantação do PostgreSQL:

  • Instâncias locais do PostgreSQL conectadas ao Google Cloud pelo Cloud VPN ou Cloud Interconnect
  • Instâncias autogerenciadas do PostgreSQL no Compute Engine
  • Instâncias do PostgreSQL hospedadas na nuvem acessíveis por endereços IP públicos ou anexos de rede

A federação do BigQuery é compatível com as seguintes versões do PostgreSQL:

  • PostgreSQL 16 (recomendado)
  • PostgreSQL 15

Antes de começar

Ative as APIs necessárias, configure o banco de dados do PostgreSQL e conceda papéis do Identity and Access Management que dão aos usuários as permissões necessárias para executar cada tarefa neste documento.

  1. Ative a API BigQuery Connection.

    Ativar a API

  2. Se você planeja rotear o tráfego por redes privadas usando anexos de rede, ative também a API Compute Engine.

    Ativar a API Compute Engine

  3. No banco de dados PostgreSQL, crie um usuário dedicado com privilégios SELECT nas tabelas ou no esquema que você pretende 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;

    Substitua:

    • USER_NAME: o nome de usuário do banco de dados.
    • PASSWORD: a senha do usuário do banco de dados.
    • SCHEMA_NAME: o esquema das tabelas que você quer consultar.

  4. Configure a conectividade de rede com a instância do banco de dados PostgreSQL seguindo um destes procedimentos:

    • Se você estiver roteando o tráfego por redes privadas, crie um anexo de rede na mesma região da conexão do BigQuery. O peering de DNS não é compatível. Conecte-se usando um endereço IP ou um nome de host que possa ser resolvido na rede VPC.
    • Se você estiver se conectando pela Internet pública, adicione todos os intervalos de endereços IP do Google às regras de firewall do banco de dados PostgreSQL.

Funções exigidas

Para receber as permissões necessárias para se conectar ao PostgreSQL, peça ao administrador para conceder a você os seguintes papéis do IAM:

Para mais informações sobre a concessão de papéis, consulte Gerenciar o acesso a projetos, pastas e organizações.

Também é possível conseguir as permissões necessárias usando papéis personalizados ou outros papéis predefinidos.

Conectar o BigQuery ao PostgreSQL

É possível conectar o BigQuery ao PostgreSQL no console doGoogle Cloud ou na ferramenta de linha de comando bq.

Console

  1. No console do Google Cloud , acesse a página BigQuery.

    Acessar o BigQuery

  2. No painel Explorer, clique em Adicionar dados.

    A página Adicionar dados é aberta.

  3. No painel Filtrar por, na seção Tipo de fonte de dados, selecione Bancos de dados.

    Se preferir, no campo Pesquisar fontes de dados, digite postgresql.

  4. Na seção Fontes de dados em destaque, clique em PostgreSQL.

  5. Na seção Acessar dados externos no local, clique no card de solução Federação do BigQuery com a descrição Consultas federadas para o PostgreSQL (não os cards do Cloud SQL ou do AlloyDB para PostgreSQL).

    O painel Fonte de dados externa é aberto.

  6. No painel Fonte de dados externa, configure o seguinte:

    • No campo ID da conexão, insira um ID de conexão exclusivo (por exemplo, postgres_prod_conn).
    • Na seção Tipo de local, selecione Região e, em seguida, selecione a região em que o conjunto de dados do BigQuery está localizado. As consultas que usam essa conexão precisam ser executadas nessa região.
    • (Opcional) No campo Nome de exibição, insira um nome descritivo para a conexão.
    • (Opcional) No campo Descrição, insira uma descrição para este recurso de conexão.
    • Na seção Criptografia, selecione Google-managed encryption key ou Chave do Cloud KMS para usar uma chave de criptografia gerenciada pelo cliente (CMEK).
    • No campo Host:porta, insira o host e a porta do servidor de banco de dados PostgreSQL no formato HOST:PORT (por exemplo, 10.10.2.2:5432).
    • No campo Nome de usuário, insira o usuário do banco de dados que você criou nos pré-requisitos.
    • No campo Senha, digite a senha do usuário do banco de dados.
    • No campo Banco de dados, insira o nome do banco de dados PostgreSQL.
    • (Opcional) No campo Anexo de rede, se você estiver usando a conectividade particular, insira o caminho do recurso do anexo de rede no formato projects/PROJECT_ID/regions/NA_REGION/networkAttachments/ATTACHMENT_NAME.
    • Na lista Modo TLS, selecione um dos modos TLS compatíveis (por exemplo, Criptografar dados, verificar a AC e o nome do host (recomendado)).
    • Na lista Autoridade certificadora (CA), selecione CA pública (Web PKI) ou selecione uma CA personalizada e cole o certificado formatado em PEM.

  7. Clique em Criar conexão.

bq

Use o comando bq mk com a sinalização --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

Substitua:

  • LOCATION: a região do BigQuery (por exemplo, us-central1) que corresponde ao conjunto de dados que você pretende consultar com o PostgreSQL. As consultas que usam essa conexão precisam ser executadas nessa região.
  • PROJECT_ID: o ID do projeto Google Cloud .
  • HOST_PORT: o host e a porta do servidor de banco de dados PostgreSQL no formato HOST:PORT (por exemplo, 10.10.2.2:5432).
  • DATABASE: o nome do banco de dados PostgreSQL.
  • USERNAME: o usuário do banco de dados PostgreSQL.
  • PASSWORD: a senha do usuário do banco de dados.
  • TLS_MODE: o modo de criptografia TLS (ENCRYPT_VERIFY_CA_AND_HOST, ENCRYPT_VERIFY_CA, ENCRYPT_VERIFY_NONE ou DISABLE). Se você usar um certificado de CA personalizado com ENCRYPT_VERIFY_CA_AND_HOST ou ENCRYPT_VERIFY_CA, inclua o objeto "private_pki": { "trusted_certificates_pem": "CERT_CONTENT" } em "tls".
  • NA_REGION: a região em que o anexo de rede está localizado.
  • ATTACHMENT_NAME: o nome do anexo de rede. Se a conexão for por IP público, omita o objeto "network".
  • CONNECTION_ID: o identificador exclusivo desta conexão.
  • (Opcional) --kms_key_name=KMS_KEY_NAME: uma chave de criptografia gerenciada pelo cliente (CMEK). Se omitido, as credenciais serão protegidas pelo Google-owned and Google-managed encryption keypadrão.

Configuração de Transport Layer Security (TLS)

O conector do PostgreSQL é compatível com o Transport Layer Security (TLS) para criptografar o tráfego de consulta em trânsito entre o BigQuery e o banco de dados do PostgreSQL.

Ao conectar o BigQuery ao PostgreSQL, selecione um dos seguintes modos de TLS:

  • Criptografar dados e verificar a autoridade certificadora (CA) e o nome do host (modo ENCRYPT_VERIFY_CA_AND_HOST). Realiza a validação completa do servidor usando TLS. Criptografa todos os dados em trânsito, verifica se o certificado do servidor de banco de dados é assinado por uma AC confiável e se o nome do host ao qual você está se conectando corresponde ao nome comum (CN, na sigla em inglês) ou ao nome alternativo do sujeito (SAN, na sigla em inglês) no certificado. Obrigatório para conexões por IPs públicos.

  • Criptografar dados e verificar apenas a AC (modo ENCRYPT_VERIFY_CA). Criptografa todos os dados em trânsito e verifica se o certificado do servidor está assinado por uma AC confiável, mas não verifica o nome do host do servidor. Use esse modo se o certificado do servidor for assinado por uma CA confiável, mas você se conectar usando um endereço IP ou um nome de host que não corresponda ao CN ou SAN do certificado.

  • Somente criptografia (modo ENCRYPT_VERIFY_NONE). Criptografa todos os dados transferidos entre o BigQuery e o servidor. Não realiza a verificação de certificado ou nome do host. Recomendado apenas em VPCs particulares.

  • Sem criptografia ou verificação (modo DISABLE). Não criptografa dados e não realiza a verificação de certificado ou nome do host. Permitido apenas ao se conectar por anexos de rede privada em redes VPC protegidas.

Certificados de servidor confiáveis no formato PEM

Se você usa o modo ENCRYPT_VERIFY_CA_AND_HOST ou ENCRYPT_VERIFY_CA, faça o seguinte:

  • Se o certificado do servidor de banco de dados for assinado por uma CA pública (como as incluídas no programa de CA raiz da Mozilla, incluindo Let's Encrypt, DigiCert ou Serviços de confiança do Google), não será necessário fornecer um certificado. Os certificados raiz de ACs públicas são confiáveis por padrão.
  • Se você usar um certificado assinado por uma CA interna ou particular na sua organização ou um certificado autoassinado, forneça a cadeia de certificados completa no formato Privacy-Enhanced Mail (PEM) no campo Certificado PEM confiável durante a configuração da conexão.

Compartilhar conexões com os usuários

Você pode conceder os seguintes papéis para permitir que os usuários consultem dados e gerenciem conexões:

  • roles/bigquery.connectionUser: permite aos usuários usar conexões para se conectar a fontes de dados externas e executar consultas nelas.

  • roles/bigquery.connectionAdmin: permite que os usuários gerenciem conexões.

Para mais informações sobre os papéis e as permissões do IAM no BigQuery, consulte Papéis e permissões predefinidos.

Selecione uma das seguintes opções:

Console

  1. Acessar a página do BigQuery.

    Acessar o BigQuery

    As conexões são listadas no projeto, em um grupo chamado Conexões.

  2. No painel à esquerda, clique em Explorer:

    Botão destacado para o painel "Explorer".

    Se o painel esquerdo não aparecer, clique em Expandir painel esquerdo para abrir.

  3. Clique no projeto, em Conexões e selecione uma conexão.

  4. No painel Detalhes, clique em Compartilhar para compartilhar uma conexão. Em seguida, siga estas etapas:

    1. Na caixa de diálogo Permissões de conexão, compartilhe a conexão com outros principais adicionando ou editando principais.

    2. Clique em Salvar.

bq

Não é possível compartilhar uma conexão com a ferramenta de linha de comando bq. Para compartilhar um recurso de conexão, use o console do Google Cloud ou o método da API BigQuery Connections para compartilhar uma conexão.

API

Consulte o método projects.locations.connections.setIAM na seção de referência da API REST BigQuery Connections e forneça uma instância do recurso policy.

Java

Antes de testar esta amostra, siga as instruções de configuração do Java no Guia de início rápido do BigQuery: como usar bibliotecas de cliente. Para mais informações, consulte a documentação de referência da API BigQuery em Java.

Para autenticar no BigQuery, configure o Application Default Credentials. Para mais informações, acesse Configurar a autenticação para bibliotecas de 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");
    }
  }
}

Preços

As consultas e conexões federadas estão sujeitas às seguintes cobranças:

A seguir