Consultas federadas de Cloud SQL

Las consultas federadas en BigQuery te permiten consultar datos que se encuentran en Cloud SQL en tiempo real sin copiarlos ni moverlos. La federación de consultas admite instancias de Cloud SQL para MySQL y Cloud SQL para PostgreSQL en Cloud SQL.

Como alternativa, para replicar los datos en BigQuery, 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 en BigQuery.

Antes de comenzar

Para ejecutar consultas federadas en tus datos de Cloud SQL, haz lo siguiente:

  1. Asegúrate de que el administrador de BigQuery haya creado una conexión de Cloud SQL y la haya compartido contigo.
  2. Verifica que se haya otorgado el rol de cliente de Cloud SQL (roles/cloudsql.client) al agente de servicio de conexión de BigQuery. Para obtener más información, consulta Otorga acceso al agente de servicio.
  3. Si tu instancia de Cloud SQL usa una dirección IP privada, asegúrate de que se haya habilitado una ruta de acceso privada cuando se creó la instancia. Para obtener más información, consulta Conéctate a Cloud SQL y Configura una IP privada.

Roles obligatorios

Para obtener los permisos que necesitas para consultar una instancia de Cloud SQL, pídele a tu administrador que te otorgue el rol de IAM Usuario de conexión de BigQuery (roles/bigquery.connectionUser) en tu proyecto. Para obtener más información sobre cómo otorgar roles, consulta Administra el acceso a proyectos, carpetas y organizaciones.

También puedes obtener los permisos necesarios mediante roles personalizados o cualquier otro rol predefinido.

Consulta los datos

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

Formato del identificador de conexión

El primer argumento de la función EXTERNAL_QUERY es el identificador de conexión. Puedes especificar el identificador de conexión con uno de los siguientes formatos:

  • Formato corto: [LOCATION.]CONNECTION_ID (por ejemplo, 'us.my-connection' o 'my-connection')
  • Formato completamente calificado: projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID
  • Formato de ruta de acceso descriptiva: PROJECT_ID.LOCATION.CONNECTION_ID (por ejemplo, 'my-project.us.my-connection')

Si ejecutas una consulta en una vista compartida en varios proyectos, usa siempre el identificador de conexión completamente calificado para evitar conectarte al proyecto incorrecto.

Ejecuta una consulta federada

Supongamos que almacenas una tabla de clientes en BigQuery y una tabla de pedidos 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

Selecciona una de las siguientes opciones para ejecutar la consulta federada:

Console

  1. En la consola de Google Cloud , ve a la página BigQuery.

    Ir a BigQuery

  2. En el editor de consultas, ingresa tu consulta:

    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;

    Reemplaza CONNECTION_ID por el ID de tu conexión de BigQuery.

  3. Haz clic en Ejecutar.

    Los resultados de la consulta aparecen en el panel Resultados de la consulta.

bq

  2. En la consola de Google Cloud , activa Cloud Shell.

    Activa Cloud Shell

    En la parte inferior de la consola de Google Cloud , se inicia una sesión de Cloud Shell en la que se muestra una ventana de línea de comandos. Cloud Shell es un entorno de shell con Google Cloud CLI ya instalada y con valores ya establecidos para el proyecto actual. La sesión puede tardar unos segundos en inicializarse.

  3. Ejecuta el comando bq query con la marca --use_legacy_sql=false:

    bq query \
    --use_legacy_sql=false \
    '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;'

    Reemplaza CONNECTION_ID por el ID de tu conexión de BigQuery.

La consulta de ejemplo incluye 3 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 Cloud SQL 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 las vistas INFORMATION_SCHEMA y acceder a los metadatos de la base de datos, como enumerar todas las tablas de la base de datos o mostrar los esquemas de las tablas. Las siguientes consultas de ejemplo de INFORMATION_SCHEMA funcionan en MySQL y PostgreSQL. Puedes obtener más información en 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';");

Reemplaza CONNECTION_ID por el ID de tu conexión de BigQuery.

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 te ayuda a identificar consultas provenientes de BigQuery.

  1. En la consola de Google Cloud , accede a la página Explorador de registros.

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

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 Es el nombre del recurso de conexión en el formato projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID.
location cadena La 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 ejecutan en esta ubicación pueden usar esta conexión.
friendlyName cadena Es un nombre visible fácil de usar para la conexión.
description string Es la descripción de la conexión.
cloudSql.type cadena Puede ser POSTGRES o MYSQL.
cloudSql.instanceId cadena Nombre de la instancia de Cloud SQL, por lo general, en el formato: PROJECT_ID:LOCATION:INSTANCE_ID. Puedes encontrar el ID de la instancia en la página Instancias de Cloud SQL.
cloudSql.database cadena Es la base de datos de Cloud SQL a la que se conectará.
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 Es el nombre de usuario de la base de datos.
password cadena Es la contraseña de la base de datos.

Rendimiento de las consultas y prácticas recomendadas

Para optimizar el rendimiento de las consultas federadas y evitar colocar una carga innecesaria en tu instancia de Cloud SQL, ten en cuenta las siguientes prácticas recomendadas:

  • Filtra con anticipación en la consulta externa: Aplica filtros WHERE y agregaciones GROUP BY dentro de la instrucción de SQL EXTERNAL_QUERY en lugar de filtrar en la consulta externa de BigQuery. Ejecutar filtros en Cloud SQL reduce la cantidad de datos transferidos a través de la red a BigQuery.
  • Elimina columnas: Selecciona solo las columnas específicas que se necesitan en la sentencia EXTERNAL_QUERY en lugar de usar SELECT *. Esto reduce el volumen de transferencia de red y el tiempo de ejecución de la consulta.
  • Usa réplicas de lectura: Para aislar las cargas de trabajo de consultas analíticas de tu base de datos de producción principal, crea una réplica de lectura de Cloud SQL y configura tu conexión de BigQuery para consultar la réplica.
  • Coloca conjuntos de datos e instancias en la misma ubicación: Para obtener la latencia más baja, ubica tu conjunto de datos y conexión de BigQuery en la misma región que tu instancia de Cloud SQL o en la multirregión correspondiente (como US o EU). Para obtener más información, consulta Regiones admitidas.

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.

No se pudo establecer conexión con el servidor de la base de datos

Cuando 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.

Cuando 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.

Solución

Para resolver este problema, verifica los siguientes requisitos previos:

  1. Asegúrate de que se hayan usado credenciales válidas y de que se hayan seguido todos los pasos de configuración cuando creaste la conexión para Cloud SQL.

  2. Verifica que se haya otorgado el rol de cliente de Cloud SQL (roles/cloudsql.client) al agente de servicio de conexión de BigQuery. El correo electrónico del agente de servicio tiene el siguiente formato:

    service-PROJECT_NUMBER@gcp-sa-bigqueryconnection.iam.gserviceaccount.com

    Para obtener instrucciones detalladas, consulta Otorga acceso al agente de servicio.

  3. Si tu instancia de Cloud SQL usa una dirección IP privada, asegúrate de haber habilitado una ruta de acceso privada cuando creaste la instancia de Cloud SQL. Habilitar una ruta de acceso privada permite que BigQuery acceda a los datos en Cloud SQL y ejecute consultas a través de una conexión privada.

No se pudo establecer conexión con la base de datos de MySQL debido al complemento de autenticación

Cuando ejecutas una consulta federada en una instancia de MySQL, es posible que encuentres el siguiente error:

Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2059): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory at [1:15]

Solución

Este error se produce en las cuentas de usuario de la base de datos configuradas con la autenticación caching_sha2_password, que no es compatible con las consultas federadas.

Si tu instancia de Cloud SQL ejecuta la versión 8.0 o una anterior de MySQL, puedes cambiar el complemento de autenticación para la cuenta de usuario que usa BigQuery. Ejecuta el siguiente comando en la instancia de Cloud SQL:

ALTER USER 'USERNAME'@'%' IDENTIFIED WITH mysql_native_password BY 'PASSWORD';

Reemplaza lo siguiente:

  • USERNAME: Es la cuenta de usuario de la base de datos que BigQuery usa para conectarse a tu instancia de Cloud SQL para MySQL.
  • PASSWORD: Es la contraseña de la cuenta de usuario de la base de datos.

Si tu instancia de Cloud SQL ejecuta la versión 8.4 de MySQL, el complemento mysql_native_password dejará de estar disponible. No recomendamos cambiar una base de datos existente de la versión 8.4 a la 8.0 para las cargas de trabajo de producción.

La actualización de una instancia de una versión anterior a MySQL 8.4 no afecta las conexiones de base de datos existentes, siempre que no cambies el complemento de autenticación de usuario antes de la actualización. Debido a que solo los usuarios creados con mysql_native_password se pueden usar para consultas federadas, las consultas enviadas a MySQL 8.4 deben usar una cuenta de usuario creada antes de la actualización.

Limitaciones

Las consultas federadas en Cloud SQL están sujetas a las siguientes limitaciones:

  • Consultas de solo lectura: Las consultas federadas son de solo lectura. Las consultas externas deben ser sentencias SELECT. No se admiten las sentencias de lenguaje de definición de datos (DDL) ni de lenguaje de manipulación de datos (DML).
  • Rendimiento de las consultas: Las consultas federadas no son tan rápidas como las consultas en el almacenamiento de BigQuery. BigQuery debe esperar a que Cloud SQL ejecute la consulta externa y transfiera el conjunto de resultados a través de la red.
  • Restricciones de región: La ubicación de procesamiento de consultas de BigQuery debe coincidir con la ubicación de la instancia de Cloud SQL o ser compatible con ella. Para obtener más información, consulta Regiones admitidas.
  • Tipos de datos no admitidos: Si una consulta externa devuelve un tipo de datos que no es compatible con BigQuery, la consulta falla. Puedes convertir el tipo de datos no admitido en un tipo de datos compatible dentro de la consulta externa. Para obtener más información, consulta Asignaciones de tipos de datos.
  • Cantidad máxima de conexiones: Una consulta federada puede hacer referencia a un máximo de 10 conexiones únicas.
  • Limitación del complemento de autenticación: El complemento caching_sha2_password de MySQL no es compatible con las consultas federadas. Si deseas obtener más información, consulta Solución de problemas.

¿Qué sigue?