Consultas federadas de AlloyDB

Como analista de datos, puedes consultar datos en AlloyDB para PostgreSQL desde BigQuery a través de consultas federadas.

La federación de AlloyDB de BigQuery permite que BigQuery consulte datos que se encuentran en AlloyDB en tiempo real sin copiarlos ni moverlos.

Antes de comenzar

Consulta datos

Para enviar una consulta federada a AlloyDB desde una consulta de GoogleSQL, usa la función EXTERNAL_QUERY.

Supongamos que almacenas una tabla de clientes en BigQuery y una tabla de ventas en AlloyDB y deseas unir las dos tablas en una sola consulta. En el siguiente ejemplo, se realiza una consulta federada a una tabla de AlloyDB llamada orders y se unen los resultados con una tabla de BigQuery llamada mydataset.customers

La consulta de ejemplo incluye 3 partes:

  1. La ejecución de la consulta externa SELECT customer_id, MIN(order_date) AS first_order_date FROM orders GROUP BY customer_id en la base de datos de AlloyDB para obtener la primera fecha del pedido de cada cliente a través de la función EXTERNAL_QUERY

  2. La unión de la tabla de resultados de la consulta externa con la tabla de clientes en BigQuery según customer_id

  3. Selecciona la información del cliente y la fecha del primer pedido en el 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;

Visualiza un esquema de tabla de AlloyDB

Puedes usar la función EXTERNAL_QUERY para consultar las tablas information_schema para acceder a los metadatos de la base de datos. Por ejemplo, puedes enumerar todas las tablas en la base de datos o ver el esquema de la tabla. Para obtener más información, consulta Tablas information_schema de 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';");

Realiza un seguimiento de las consultas federadas de BigQuery

Cuando ejecutas una consulta federada en AlloyDB, BigQuery anota la consulta con un comentario similar al siguiente:

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

Si supervisas los registros para el uso de consultas, la siguiente anotación puede ayudarte a identificar las consultas que provienen de BigQuery.

  1. Ir a la página Explorador de registros.

    Ir al Explorador de registros.

  2. En la pestaña Consulta, ingresa la siguiente consulta:

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

  3. Haz clic en Ejecutar consulta.

    Si hay registros disponibles para las consultas federadas de BigQuery, aparecerá una lista de registros similares a los siguientes en los resultados de la 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 obtener más información sobre Cloud Logging, consulta Cloud Logging.

Soluciona problemas

En esta sección, se describen posibles errores que puedes encontrar cuando envías una consulta federada a AlloyDB y se proporcionan posibles soluciones de problemas.

Problema: No se pudo establecer una conexión con el servidor de la base de datos con este error: 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.

Resolución: Asegúrate de haber usado credenciales válidas y seguido de todos los requisitos previos mientras creaste la conexión a AlloyDB. Verifica si la cuenta de servicio que se crea automáticamente cuando se crea una conexión a AlloyDB tiene el rol de cliente de AlloyDB (roles/alloydb.client). Para obtener más información, consulta Otorga acceso a la cuenta de servicio.

¿Qué sigue?