Ejecuta consultas federadas de MySQL

Como analista de datos, puedes consultar datos relacionales que se encuentran en MySQL directamente desde BigQuery en tiempo real, sin copiarlos ni moverlos.

La federación de MySQL de BigQuery usa la API de BigQuery Connection para conectarse a los motores de base de datos de MySQL. Luego, puedes usar la función EXTERNAL_QUERY para enviar tu consulta en SQL directamente a MySQL para su ejecución, y los resultados se transmiten directamente de vuelta.

Limitaciones

Las consultas federadas de MySQL están sujetas a las siguientes limitaciones:

  • Solo puedes ejecutar consultas de solo lectura a través de la función EXTERNAL_QUERY. No se admiten las sentencias del lenguaje de definición de datos (DDL) ni del lenguaje de manipulación de datos (DML).
  • Solo se puede acceder a la federación a través de la función EXTERNAL_QUERY. No se admite la creación de tablas externas de BigQuery ni de conjuntos de datos permanentes que se asignen directamente a tablas de MySQL.
  • La latencia de la consulta federada puede ser bastante más alta que la ejecución de la consulta directamente en MySQL.
  • Es posible que el servicio de conexión subyacente hiberne las conexiones inactivas. La primera consulta que usa una conexión de MySQL después de un período de inactividad puede tardar más de un minuto en ejecutarse mientras se inicializa el conector.
  • No se admite el intercambio de tráfico de DNS. Debes conectarte con una dirección IP o un nombre de host que se pueda resolver por completo dentro de tu red de VPC.
  • La consulta en SQL de MySQL debe especificar alias de columna explícitos para cualquier columna que contenga resultados o expresiones de funciones (por ejemplo, SELECT COUNT(*) AS total_count). Las expresiones sin alias producen nombres de columna con caracteres especiales que no son válidos en las definiciones de esquema de tabla de BigQuery.
  • Cuando los Controles del servicio de VPC restringen el uso de la API de Compute Engine en el proyecto de consulta, la consulta federada falla si la conexión usa un adjunto de red. Asegúrate de que se configuren las reglas de entrada y salida adecuadas de los Controles del servicio de VPC.

  • No se admiten pushdowns de SQL adicionales para MySQL. Los filtros y las agregaciones de la consulta externa de BigQuery no se envían a la consulta interna dentro de la función EXTERNAL_QUERY. Debes especificar predicados y proyecciones de columnas directamente dentro de la instrucción de SQL de MySQL interna para limitar las filas y columnas que se transfieren por la red.

  • Solo se admite la autenticación de nombre de usuario y contraseña de la base de datos de MySQL. No se admiten los proveedores de identidad de terceros, Kerberos ni la autenticación de IAM del proveedor de servicios en la nube.

  • Las declaraciones CREATE TABLE ... AS SELECT (CTAS) fallan con errores Unsupported type INT32 o Unsupported type FLOAT si la consulta selecciona columnas de números enteros de 32 bits (TINYINT, SMALLINT y BOOLEAN/TINYINT(1)) o columnas de números de punto flotante de precisión simple (FLOAT). Para guardar los resultados de la consulta en una tabla nueva de BigQuery, convierte de forma explícita las columnas de números enteros en INT64 y las columnas de números de punto flotante en FLOAT64 en tu consulta:

    CREATE TABLE mydataset.mytable AS
SELECT
CAST(tiny_col AS INT64) AS tiny_col,
CAST(small_col AS INT64) AS small_col,
...
FROM EXTERNAL_QUERY(...);

    La inserción en una tabla existente con INSERT INTO <table> SELECT ... se realiza correctamente sin conversiones manuales porque BigQuery amplía automáticamente los valores INT32 y FLOAT para que coincidan con las columnas INT64 y FLOAT64 de la tabla de destino.

  • Si bien las columnas DECIMAL y NUMERIC de MySQL se asignan a BIGNUMERIC(76, 38), MySQL permite una precisión de hasta 65. Si un valor contiene más de 38 dígitos enteros (la precisión menos la escala es mayor que 38), supera el rango de números enteros de BIGNUMERIC y la consulta falla en el entorno de ejecución. Para consultar valores con más de 38 dígitos enteros, conviértelos en CHAR o VARCHAR en la sentencia EXTERNAL_QUERY.

  • El tipo de datos TIME trunca los valores fraccionarios de menos de un segundo (microsegundos) a segundos enteros (por ejemplo, 12:34:56.789123 se convierte en 12:34:56). Además, TIME de MySQL admite duraciones transcurridas de -838:59:59 a 838:59:59, mientras que TIME de BigQuery solo admite valores de hora del día de 00:00:00 a 23:59:59.999999. Los valores fuera de 00:00:00 a 23:59:59 causan errores de consulta en el entorno de ejecución. Si se requieren valores de precisión de microsegundos o de duración transcurrida, convierte la columna en CHAR o VARCHAR dentro de la sentencia EXTERNAL_QUERY.

  • Las fechas y marcas de tiempo anteriores al 15 de octubre de 1582 están sujetas a un cambio de calendario de 10 días debido a la transición histórica del calendario gregoriano. Para conservar las fechas históricas exactas anteriores a 1582, conviértelas en CHAR o VARCHAR en la sentencia EXTERNAL_QUERY.

Entornos y versiones de MySQL compatibles

La federación de consultas admite bases de datos de MySQL en los siguientes entornos de implementación:

  • Bases de datos de MySQL alojadas en la nube o locales a las que se puede acceder a través de direcciones IP públicas o adjuntos de red (a través de Cloud VPN o Cloud Interconnect)
  • Instancias de MySQL autoalojadas en Compute Engine o en otra infraestructura
  • MySQL 8.4 LTS y 8.0

Antes de comenzar

Asegúrate de que el administrador de BigQuery cree una conexión de MySQL y la haya compartido contigo.

Roles obligatorios

Para obtener los permisos que necesitas para consultar MySQL, pídele a tu administrador que te otorgue el rol de IAM de usuario de conexión de BigQuery (roles/bigquery.connectionUser) en el 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 MySQL desde una consulta de GoogleSQL, usa la función EXTERNAL_QUERY.

En el siguiente ejemplo, se muestra una consulta federada que une los registros de clientes en BigQuery llamados mydataset.customers con pedidos de ventas transaccionales en MySQL:

SELECT
  c.customer_id,
  c.name,
  fo.first_order_date
FROM
  mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'projects/my-project/locations/us-central1/connections/mysql_prod_conn',
  '''SELECT CUSTOMER_ID, MIN(ORDER_DATE) AS first_order_date
     FROM ORDERS
     GROUP BY CUSTOMER_ID''') AS fo
  ON fo.customer_id = c.customer_id
GROUP BY
  c.customer_id,
  c.name,
  fo.first_order_date;

Visualiza un esquema de tabla de MySQL

Puedes consultar las vistas INFORMATION_SCHEMA de MySQL a través de EXTERNAL_QUERY para inspeccionar los metadatos de la base de datos, descubrir tablas y revisar los esquemas de columnas antes de ejecutar los análisis.

Enumera todas las bases de datos

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME
     FROM INFORMATION_SCHEMA.SCHEMATA''');

Enumera las tablas de una base de datos

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, ENGINE
     FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_SCHEMA = 'DATABASE_NAME'""");

Visualiza columnas y tipos de datos en una tabla

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  """SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_TYPE
     FROM INFORMATION_SCHEMA.COLUMNS
     WHERE TABLE_SCHEMA = 'DATABASE_NAME' AND TABLE_NAME = 'TABLE_NAME'""");

Asignación de tipos de datos

Cuando ejecutas una consulta federada, BigQuery convierte los datos de MySQL en tipos de datos de GoogleSQL según la siguiente tabla:

Tipo de datos de MySQL Tipo de datos de BigQuery
Tipos numéricos
BOOLEAN, BOOL, TINYINT(1) INT64 (consultas interactivas; consulta Limitaciones para CREATE TABLE ... AS SELECT)
TINYINT, SMALLINT INT64 (consultas interactivas; consulta Limitaciones para CREATE TABLE ... AS SELECT)
MEDIUMINT, INT, INTEGER INT64
BIGINT INT64
BIGINT UNSIGNED, BIGINT ZEROFILL, SERIAL BIGNUMERIC(76, 38)
DECIMAL, DEC, NUMERIC, FIXED BIGNUMERIC(76, 38)
FLOAT FLOAT64 (consultas interactivas; consulta Limitaciones para CREATE TABLE ... AS SELECT)
DOUBLE, DOUBLE PRECISION, REAL FLOAT64
Tipos de fecha y hora
DATE DATE
DATETIME DATETIME
TIMESTAMP TIMESTAMP
TIME TIME
Tipos de cadenas y texto
CHAR STRING
VARCHAR STRING
BINARY BYTES
VARBINARY BYTES
TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB BYTES
TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT STRING
ENUM STRING
SET STRING

Precios

El costo de ejecutar una consulta federada se basa en tres factores:

  • El costo de procesamiento de la ejecución de la consulta en MySQL (que cobra tu proveedor de base de datos de MySQL o entorno de hosting)
  • El costo del ancho de banda de la transferencia de los resultados de la consulta de MySQL a BigQuery (cargos de red de salida de tu proveedor de hosting de bases de datos)
  • El costo de procesamiento de la ejecución de la consulta en BigQuery

Los costos de procesamiento y salida de MySQL se facturan directamente a través de tu proveedor de MySQL o host de infraestructura, no de Google. Para limitar el costo del ancho de banda, te recomendamos que escribas la consulta en la sentencia EXTERNAL_QUERY de modo que excluya todas las columnas y filas que no son necesarias para calcular el resultado final.

No se aplican costos adicionales por ejecutar consultas federadas en BigQuery. Para obtener más información sobre los precios de BigQuery, consulta Precios.

Si tu conexión usa componentes de redes de Google Cloud , también se podrían aplicar los siguientes precios:

¿Qué sigue?