Consultas federadas de Cloud SQL

Como analista de datos, puedes consultar datos en Cloud SQL desde BigQuery mediante consultas federadas.

La federación de Cloud SQL en BigQuery permite que BigQuery consulte datos que se encuentran en Cloud SQL en tiempo real sin copiarlos ni moverlos. La Federación de consultas admite instancias de MySQL (segunda generación) y PostgreSQL en Cloud SQL.

Como alternativa, para replicar los datos en BigQuery, también puedes usar Cloud Data Fusion o Datastream. Para obtener más información sobre el uso de Cloud Data Fusion, consulta Replica datos de MySQL a BigQuery.

Antes de comenzar

Consulta los datos

Para enviar una consulta federada a Cloud SQL desde una consulta de Google SQL, usa la función EXTERNAL_QUERY.

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

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;

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 operativa PostgreSQL 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. La selección de la información del cliente y la fecha del primer pedido

Visualiza un esquema de tabla de Cloud SQL

Puedes usar la función EXTERNAL_QUERY() para consultar tablas information_schema a fin de acceder a los metadatos de la base de datos, como listas de todas las tablas en la base de datos o el esquema de la tabla. Las siguientes consultas de ejemplo de information_schema funcionan en MySQL y PostgreSQL. Puedes obtener más información sobre las tablas information_schema de MySQL y las tablas information_schema de PostgreSQL.

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

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

Detalles de la conexión

En la siguiente tabla, se muestran las propiedades de conexión de Cloud SQL:

Nombre de la propiedad Valor Descripción
name string Es el nombre del recurso de conexión con el formato project_id.location_id.connection_id
location string Es la ubicación de la conexión, que es la misma que la ubicación de la instancia de Cloud SQL, con las siguientes excepciones: us-central1 de Cloud SQL se mapea a de EE.UU. de BigQuery, y europe-west1 de Cloud SQL se mapea a la UE de BigQuery.
friendlyName string Es un nombre visible fácil de usar para la conexión.
description string Es la descripción de la conexión.
cloudSql.type string Puede ser “POSTGRES” o “MYSQL”.
cloudSql.instanceId string Es el nombre de la instancia de Cloud SQL, por lo general, con el siguiente formato:

Project-id:location-id:instance-id

Puedes encontrar el ID de la instancia en la página de detalles Instancias de Cloud SQL.
cloudSql.database string Es la base de datos de Cloud SQL a la que deseas conectarte.
cloudSql.serviceAccountId string La cuenta de servicio configurada para acceder a la base de datos de Cloud SQL.

En la siguiente tabla, se muestran las propiedades de la credencial de la instancia de Cloud SQL:

Nombre de la propiedad Valor Descripción
username string Nombre de usuario de la base de datos
password string Contraseña de la base de datos

Realiza un seguimiento de las consultas federadas de BigQuery

Cuando ejecutas una consulta federada en Cloud SQL, 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 registros para el uso de consultas en una base de datos MySQL o PostgreSQL, la anotación siguiente puede ayudarte a identificar consultas provenientes 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="cloudsql_database"
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
*/

Soluciona problemas

En esta sección, se proporciona ayuda para solucionar los problemas que puedes encontrar cuando envías una consulta federada a Cloud SQL.

Problema: No se pudo establecer la conexión con el servidor de la base de datos. Si consultas una base de datos de MySQL, es posible que encuentres el siguiente error:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query.

Como alternativa, si consultas una base de datos de PostgreSQL, es posible que encuentres el siguiente 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 que se usaron las credenciales válidas y de que se hayan seguido todos los requisitos previos para crear la conexión para Cloud SQL. Verifica si la cuenta de servicio que se crea de forma automática cuando se crea una conexión a Cloud SQL tiene el rol de cliente de Cloud SQL (roles/cloudsql.client). La cuenta de servicio tiene el siguiente formato: service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com. Para obtener instrucciones detalladas, consulta Otorga acceso a la cuenta de servicio.

¿Qué sigue?