Consultas federadas do AlloyDB

Como analista de dados, pode consultar dados no AlloyDB para PostgreSQL a partir do BigQuery usando consultas federadas.

A federação do AlloyDB do BigQuery permite que o BigQuery consulte dados residentes no AlloyDB em tempo real sem copiar nem mover os dados.

Antes de começar

Consultar dados

Para enviar uma consulta federada para o AlloyDB a partir de uma consulta GoogleSQL, use a função EXTERNAL_QUERY.

Suponhamos que armazena uma tabela de clientes no BigQuery, enquanto armazena uma tabela de vendas no AlloyDB e quer juntar as duas tabelas numa única consulta. O exemplo seguinte faz uma consulta federada a uma tabela do AlloyDB denominada orders e junta os resultados a uma tabela do BigQuery denominada mydataset.customers.

A consulta de exemplo inclui 3 partes:

  1. Execute a consulta externa SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id na base de dados do AlloyDB para obter a data da primeira encomenda de cada cliente através da função EXTERNAL_QUERY.

  2. Junte a tabela de resultados da consulta externa à tabela de clientes no BigQuery por customer_id.

  3. Selecione as informações do cliente e a data da primeira encomenda no conjunto de resultados final.

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'us.connection_id',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;

Veja um esquema de tabela do AlloyDB

Pode usar a função EXTERNAL_QUERY para consultar tabelas information_schema para aceder aos metadados da base de dados. Por exemplo, pode listar todas as tabelas na base de dados ou ver o esquema da tabela. Para mais informações, consulte o artigo Tabelas information_schema do PostgreSQL.

-- List all tables in a database.
SELECT * FROM EXTERNAL_QUERY("region.connection_id",
"select * from information_schema.tables;");

-- List all columns in a table.
SELECT * FROM EXTERNAL_QUERY("region.connection_id",
"select * from information_schema.columns where table_name='x';");

Acompanhe as consultas federadas do BigQuery

Quando executa uma consulta federada no AlloyDB, o BigQuery anota a consulta com um comentário semelhante ao seguinte:

/* Federated query from BigQuery. Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID. */

Se estiver a monitorizar registos de utilização de consultas, a seguinte anotação pode ajudar a identificar consultas provenientes do BigQuery.

  1. Aceda à página Explorador de registos.

    Aceda ao Explorador de registos

  2. No separador Consulta, introduza a seguinte consulta:

    resource.type="alloydb.googleapis.com/Instance"
textPayload=~"Federated query from BigQuery"

  3. Clique em Executar consulta.

    Se existirem registos disponíveis para consultas federadas do BigQuery, é apresentada uma lista de registos semelhante à seguinte em Resultados da consulta.

    YYYY-MM-DD hh:mm:ss.millis UTC [3210064]: [4-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT 1 FROM (SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

YYYY-MM-DD hh:mm:ss.millis UTC [3210532]: [2-1]
db=DATABASE, user=USER_ACCOUNT
STATEMENT: SELECT "company_id", "company type_id" FROM
(SELECT FROM company_name_table) t;
/* Federated query from BigQuery.
Project ID: PROJECT_ID, BigQuery Job ID: JOB_ID
*/

    Para mais informações sobre o Cloud Logging, consulte o artigo Cloud Logging.

Resolução de problemas

Esta secção descreve os potenciais erros que pode encontrar quando envia uma consulta federada para o AlloyDB e apresenta possíveis resoluções para a resolução de problemas.

Problema: não foi possível estabelecer ligação ao servidor de base de dados com este erro: Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request.

Resolução: certifique-se de que usou credenciais válidas e seguiu todos os pré-requisitos ao criar a ligação ao AlloyDB. Verifique se a conta de serviço criada automaticamente quando é criada uma ligação ao AlloyDB tem a função de cliente do AlloyDB (roles/alloydb.client). Para mais informações, consulte o artigo Conceda acesso à conta de serviço.

O que se segue?