Consultas federadas do AlloyDB

Como analista de dados, é possível consultar dados no AlloyDB para PostgreSQL pelo BigQuery usando consultas federadas.

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

Antes de começar

Consultar dados

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

Suponha que você armazene uma tabela de clientes no BigQuery e armazene uma tabela de vendas no AlloyDB, e queira mesclar as duas tabelas em uma única consulta. No exemplo a seguir, fazemos uma consulta federada para uma tabela do AlloyDB chamada orders e mescla os resultados com uma tabela do BigQuery chamada mydataset.customers.

A consulta de exemplo inclui três partes:

  1. Executar a consulta externa SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id no banco de dados AlloyDB para conseguir a primeira data de pedido de cada cliente por meio da função EXTERNAL_QUERY.

  2. Mesclar a tabela de resultados de consultas externas à tabela de clientes no BigQuery por customer_id.

  3. Selecione as informações do cliente e a data do primeiro pedido 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;

Ver um esquema de tabela do AlloyDB

É possível usar a função EXTERNAL_QUERY para consultar tabelas information_schema e acessar metadados do banco de dados. Por exemplo, é possível listar todas as tabelas no banco de dados ou visualizar o esquema da tabela. Para mais informações, consulte 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';");

Rastrear consultas federadas do BigQuery

Quando você 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 você estiver monitorando registros para uso de consultas, a anotação a seguir poderá ajudar a identificar as consultas provenientes do BigQuery.

  1. Acesse a página Análise de registros.

    Acesse o Explorador de registros

  2. Na guia Consulta, insira a seguinte consulta:

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

  3. Clique em Executar consulta.

    Se houver registros disponíveis para consultas federadas do BigQuery, uma lista de registros semelhante à seguinte aparecerá nos 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 Cloud Logging.

Solução de problemas

Nesta seção, descrevemos possíveis erros ao enviar uma consulta federada para o AlloyDB e apresentamos possíveis soluções para a solução de problemas.

Problema: falha ao se conectar ao servidor de banco 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: verifique se você usou credenciais válidas e seguiu todos os pré-requisitos ao criar a conexão com o AlloyDB. Verifique se a conta de serviço criada automaticamente quando uma conexão com o AlloyDB é criada tem o papel Cliente AlloyDB (roles/alloydb.client). Para mais informações, consulte Conceder acesso à conta de serviço.

A seguir