Consultas federadas de Cloud SQL

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

La federación de BigQuery con Cloud SQL permite a BigQuery consultar datos alojados en Cloud SQL en tiempo real, sin tener que copiarlos ni transferirlos. La federación de consultas admite instancias de MySQL (2.ª generación) y PostgreSQL en Cloud SQL.

También puede usar Cloud Data Fusion o Datastream para replicar datos en BigQuery. Para obtener más información sobre cómo usar Cloud Data Fusion, consulta Replicar datos de MySQL en BigQuery.

Antes de empezar

Consultar datos

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

Supongamos que almacena una tabla de clientes en BigQuery y una tabla de ventas en Cloud SQL, y quiere unir las dos tablas en una sola consulta. En el siguiente ejemplo, se hace una consulta federada a una tabla de Cloud SQL llamada orders y se combinan 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 tres partes:

  1. Ejecuta 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 de PostgreSQL para obtener la fecha del primer pedido de cada cliente mediante la función EXTERNAL_QUERY().
  2. Une la tabla de resultados de la consulta externa con la tabla de clientes de BigQuery por customer_id.
  3. Seleccione la información del cliente y la fecha del primer pedido.

Ver el esquema de una tabla de Cloud SQL

Puede usar la función EXTERNAL_QUERY() para consultar tablas information_schema y acceder a metadatos de bases de datos, como mostrar todas las tablas de la base de datos o el esquema de una tabla. Las siguientes consultas de information_schema funcionan tanto en MySQL como en PostgreSQL. Puedes consultar 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 cadena Nombre del recurso de conexión con el formato project_id.location_id.connection_id.
location cadena Ubicación de la conexión, que debe coincidir con la ubicación de la instancia de Cloud SQL o ser una multirregión de la jurisdicción correspondiente. Por ejemplo, una instancia de Cloud SQL en us-east4 puede usar US, mientras que una instancia de Cloud SQL en europe-north1 puede usar EU. Solo las consultas de BigQuery que se ejecuten en esta ubicación podrán usar esta conexión.
friendlyName cadena Nombre visible de la conexión.
description cadena Descripción de la conexión.
cloudSql.type cadena Puede ser "POSTGRES" o "MYSQL".
cloudSql.instanceId cadena Nombre de la instancia de Cloud SQL, normalmente con el formato

Project-id:location-id:instance-id

. Puedes encontrar el ID de instancia en la página de detalles de la instancia de Cloud SQL.
cloudSql.database cadena La base de datos de Cloud SQL a la que quieres conectarte.
cloudSql.serviceAccountId cadena 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 cadena Nombre de usuario de la base de datos
password cadena Contraseña de la base de datos

Hacer 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 monitorizas los registros de uso de consultas en una base de datos MySQL o PostgreSQL, la siguiente anotación puede ayudarte a identificar las consultas procedentes de BigQuery.

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

    Ir al Explorador de registros

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

    resource.type="cloudsql_database"
textPayload=~"Federated query from BigQuery"

  3. Haz clic en Realizar una consulta.

    Si hay registros disponibles para las consultas federadas de BigQuery, en Resultados de la consulta se mostrará una lista de registros similar a la siguiente:

    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
*/

Solución de problemas

En esta sección se explica cómo solucionar los problemas que pueden surgir al enviar una consulta federada a Cloud SQL.

Problema: no se ha podido conectar con el servidor de la base de datos. Si consultas una base de datos MySQL, puede que se produzca 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.

Si consultas una base de datos de PostgreSQL, puede que se produzca 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.
Solución: Asegúrate de que se hayan usado credenciales válidas y de que se hayan seguido todos los requisitos previos para crear la conexión de Cloud SQL. Comprueba si la cuenta de servicio que se crea automáticamente cuando se crea una conexión a Cloud SQL tiene el rol 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 el artículo Dar acceso a la cuenta de servicio.

Siguientes pasos