Executar consultas federadas do PostgreSQL

Como analista de dados, é possível consultar dados relacionais que residem no PostgreSQL diretamente do BigQuery em tempo real, sem copiar ou mover dados.

A federação do BigQuery PostgreSQL usa a API BigQuery Connection para se conectar a mecanismos de banco de dados PostgreSQL. Em seguida, use a função EXTERNAL_QUERY para enviar a consulta SQL diretamente ao PostgreSQL para execução, e os resultados serão transmitidos diretamente de volta.

Limitações

As consultas federadas do PostgreSQL estão sujeitas às seguintes limitações:

  • Só é possível executar consultas somente leitura usando a função EXTERNAL_QUERY. As instruções de linguagem de definição de dados (DDL) e de linguagem de manipulação de dados (DML) não são compatíveis.
  • A federação só pode ser acessada pela função EXTERNAL_QUERY. Não é possível criar tabelas externas do BigQuery ou conjuntos de dados permanentes que sejam mapeados diretamente para tabelas do PostgreSQL.
  • A latência da consulta federada pode ser visivelmente maior do que a execução da consulta diretamente no PostgreSQL.
  • O serviço de conexão subjacente pode hibernar conexões inativas. A primeira consulta que usa uma conexão do PostgreSQL após um período de inatividade pode levar mais de um minuto para ser executada enquanto o conector é inicializado.
  • O peering de DNS não é compatível. É preciso se conectar usando um endereço IP ou um nome de host totalmente resolvível na rede VPC.
  • A consulta SQL do PostgreSQL precisa especificar aliases de coluna explícitos para todas as colunas que contêm resultados ou expressões de função (por exemplo, SELECT COUNT(*) AS total_count). Expressões sem alias produzem nomes de coluna com caracteres especiais que são inválidos nas definições de esquema de tabela do BigQuery.
  • Quando o uso da API Compute Engine no projeto de consulta é restrito pelo VPC Service Controls, a consulta federada falha se a conexão usa um anexo de rede. Verifique se as regras de entrada e saída apropriadas do VPC Service Controls estão configuradas.

  • Não há suporte para pushdowns SQL adicionais no PostgreSQL. Os filtros e as agregações na consulta externa do BigQuery não são enviados para a consulta interna na função EXTERNAL_QUERY. É preciso especificar predicados e projeções de coluna diretamente na instrução SQL interna do PostgreSQL para limitar as linhas e colunas transferidas pela rede.

  • Somente a autenticação de nome de usuário e senha do banco de dados PostgreSQL é aceita. Não há suporte para provedores de identidade de terceiros, Kerberos e autenticação do IAM do provedor de nuvem.

  • As instruções CREATE TABLE ... AS SELECT (CTAS) falham com erros Unsupported type INT32 ou Unsupported type FLOAT se a consulta selecionar colunas de números inteiros de 16 bits (int2, smallint, smallserial) ou colunas flutuantes de precisão única (float4, real). Para salvar os resultados da consulta em uma nova tabela do BigQuery, converta explicitamente as colunas de números inteiros em INT64 e as colunas flutuantes em FLOAT64 na consulta:

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(small_col AS INT64) AS small_col,
CAST(real_col AS FLOAT64) AS real_col,
...
FROM EXTERNAL_QUERY(...);

    A inserção em uma tabela com INSERT INTO <table> SELECT ... é bem-sucedida sem transmissões manuais porque o BigQuery amplia automaticamente os valores INT32 e FLOAT para corresponder às colunas INT64 e FLOAT64 da tabela de destino.

  • Tanto time [ without time zone ] quanto time with time zone (timetz) truncam valores fracionários de subsegundos (microssegundos) para segundos inteiros. Se a precisão de microssegundos for necessária, formate ou converta a coluna em text ou varchar na instrução EXTERNAL_QUERY.

  • Todas as colunas numeric e decimal do PostgreSQL são mapeadas para BIGNUMERIC(76, 38). As colunas com escala maior que 38 são arredondadas para 38 casas decimais, e os valores que excedem o intervalo BIGNUMERIC (mais de 38 dígitos inteiros completos) falham com um erro de fora do intervalo. Além disso, os valores especiais NaN, Infinity e -Infinity não são compatíveis e falham no ambiente de execução. Para consultar números com partes inteiras maiores, maior precisão fracionária ou valores especiais, converta a coluna em text na instrução EXTERNAL_QUERY.

  • Os valores character(n) / char(n) (bpchar) de comprimento fixo preservam os espaços à direita até o comprimento n quando mapeados para o STRING do BigQuery.

  • As datas e os carimbos de data/hora anteriores a 15 de outubro de 1582 estão sujeitos a uma mudança de 10 dias no calendário devido à transição histórica do calendário gregoriano. Converta datas históricas em text na instrução EXTERNAL_QUERY para preservar as datas exatas.

Ambientes e versões compatíveis do PostgreSQL

A federação de consultas é compatível com bancos de dados PostgreSQL nos seguintes ambientes de implantação:

  • Bancos de dados PostgreSQL hospedados na nuvem ou no local acessíveis por endereços IP públicos ou anexos de rede (pelo Cloud VPN ou Cloud Interconnect)
  • Instâncias do PostgreSQL auto-hospedadas no Compute Engine ou em outra infraestrutura
  • PostgreSQL 16 (recomendado) e PostgreSQL 15

Antes de começar

Verifique se o administrador do BigQuery criou uma conexão do PostgreSQL e compartilhou com você.

Funções exigidas

Para receber as permissões necessárias para consultar o PostgreSQL, peça ao administrador para conceder a você o papel do IAM de Usuário de conexão do BigQuery (roles/bigquery.connectionUser) no projeto. 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 por meio de papéis personalizados ou de outros papéis predefinidos.

Consultar dados

Para enviar uma consulta federada ao PostgreSQL de uma consulta do GoogleSQL, use a função EXTERNAL_QUERY.

O exemplo a seguir é uma consulta federada que mescla registros de clientes no BigQuery chamados mydataset.customers com pedidos de vendas transacionais no PostgreSQL:

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/postgres_prod_conn',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
     FROM orders
     GROUP BY customer_id''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

Visualizar um esquema de tabela do PostgreSQL

É possível consultar as tabelas information_schema do PostgreSQL usando EXTERNAL_QUERY para recuperar metadados do banco de dados:

Listar todas as tabelas em um esquema

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT table_schema, table_name, table_type
     FROM information_schema.tables
     WHERE table_schema = 'SCHEMA_NAME'
     ORDER BY table_name''');

Ver colunas e tipos de dados em uma tabela

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT column_name, data_type, is_nullable
     FROM information_schema.columns
     WHERE table_schema = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
     ORDER BY ordinal_position''');

Mapeamento de tipo de dados

Quando você executa uma consulta federada, o BigQuery converte os dados do PostgreSQL em tipos de dados do GoogleSQL de acordo com a seguinte tabela:

Tipo de dados do PostgreSQL Tipo de dados do BigQuery
Tipos booleanos
bool, boolean BOOL
Tipos numéricos
int2, smallint, smallserial INT64 (consultas interativas; consulte Limitações para CREATE TABLE ... AS SELECT)
int4, integer, serial INT64
int8, bigint, bigserial INT64
numeric, decimal BIGNUMERIC(76, 38)
float4, real FLOAT64 (consultas interativas; consulte Limitações para CREATE TABLE ... AS SELECT)
float8, double precision FLOAT64
Tipos de data e hora
date DATE
timestamp [ without time zone ] DATETIME
timestamp with time zone, timestamptz TIMESTAMP
time [ without time zone ] TIME
time with time zone, timetz TIME
Tipos de caracteres e texto
character(n), char(n), bpchar STRING
character varying(n), varchar(n) STRING
text STRING

Preços

O custo de executar uma consulta federada é baseado em três fatores:

  • O custo de computação da execução da consulta no PostgreSQL (cobrado pelo provedor de banco de dados ou ambiente de hospedagem do PostgreSQL)
  • O custo da largura de banda da transferência dos resultados da consulta do PostgreSQL para o BigQuery (cobranças de rede de saída do provedor de hospedagem do banco de dados)
  • O custo de computação da execução da consulta no BigQuery.

Todos os custos de computação e saída do PostgreSQL são faturados diretamente pelo provedor do PostgreSQL ou pelo host da infraestrutura, não pelo Google. Para limitar o custo da largura de banda, recomendamos que você grave a consulta na instrução EXTERNAL_QUERY para que exclua todas as colunas e linhas que não sejam necessárias para calcular o resultado final.

Não há custo adicional para executar consultas federadas no BigQuery. Para mais informações sobre os preços do BigQuery, consulte Preços.

Se a conexão usar componentes de rede do Google Cloud , os seguintes preços também poderão ser aplicados:

A seguir