Consultas federadas de Spanner

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

La federación de BigQuery con Spanner permite a BigQuery consultar datos alojados en Spanner en tiempo real, sin tener que copiarlos ni transferirlos.

Puedes consultar datos de Spanner de dos formas:

  • Crea un conjunto de datos externo de Spanner.
  • Usa la función EXTERNAL_QUERY.

Usar conjuntos de datos externos

La forma más sencilla de consultar tablas de Spanner es crear un conjunto de datos externo. Una vez que hayas creado el conjunto de datos externo, las tablas de la base de datos de Spanner correspondiente estarán visibles en BigQuery y podrás usarlas en tus consultas (por ejemplo, en combinaciones, uniones o subconsultas). Sin embargo, no se transfieren datos del almacenamiento de Spanner al de BigQuery.

No es necesario crear una conexión para consultar datos de Spanner si crea un conjunto de datos externo.

Usar la función EXTERNAL_QUERY

Al igual que con otras bases de datos federadas, también puedes consultar datos de Spanner con la función EXTERNAL_QUERY. Esto puede ser útil si quiere consultar una base de datos de Spanner que use el dialecto de PostgreSQL o si quiere tener más control sobre los parámetros de conexión.

Antes de empezar

  • Asegúrate de que tu administrador de BigQuery haya creado una conexión de Spanner y la haya compartido contigo. Consulta Elegir la conexión adecuada.
  • Para obtener los permisos que necesitas para consultar una instancia de Spanner, pide a tu administrador que te asigne el rol de gestión de identidades y accesos (IAM) Usuario de conexión de BigQuery (roles/bigquery.connectionUser). También debes pedirle a tu administrador que te conceda uno de los siguientes permisos:
    • Si usas el control de acceso pormenorizado, necesitas acceso a un rol de base de datos que tenga el privilegio SELECT en todos los objetos de esquema de Spanner de tus consultas.
    • Si no eres un usuario con control de acceso pormenorizado, necesitas el rol de gestión de identidades y accesos de lector de bases de datos de Cloud Spanner (roles/spanner.databaseReader).

    Para obtener información sobre cómo conceder roles de gestión de identidades y accesos, consulta el artículo Gestionar el acceso a proyectos, carpetas y organizaciones. Para obtener información sobre el control de acceso pormenorizado, consulta Acerca del control de acceso pormenorizado.

Elegir la conexión adecuada

Si usas el control de acceso pormenorizado de Spanner, cuando ejecutes una consulta federada con una función EXTERNAL_QUERY, debes usar una conexión de Spanner que especifique un rol de base de datos. Después, todas las consultas que ejecutes con esta conexión usarán ese rol de base de datos.

Si usas una conexión que no especifica un rol de base de datos, debes tener los roles de gestión de identidades y accesos indicados en la sección Antes de empezar.

Consultar datos

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

Formula tu consulta de Spanner en GoogleSQL o PostgreSQL, según el dialecto especificado de la base de datos.

En el ejemplo siguiente se hace una consulta federada a una base de datos de Spanner 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(
  'my-project.us.example-db',
  '''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;

Spanner Data Boost

Data Boost es una función sin servidor totalmente gestionada que proporciona recursos de computación independientes para las cargas de trabajo de Spanner compatibles. Data Boost te permite ejecutar consultas analíticas y exportaciones de datos con un impacto casi nulo en las cargas de trabajo de la instancia de Spanner aprovisionada. Data Boost te permite ejecutar consultas federadas con una capacidad de computación independiente de tus instancias aprovisionadas para evitar que afecte a las cargas de trabajo de Spanner. Data Boost es más útil cuando ejecutas consultas ad hoc complejas o cuando quieres procesar grandes cantidades de datos sin que afecte a la carga de trabajo de Spanner. Ejecutar consultas federadas con Data Boost puede reducir significativamente el consumo de CPU y, en algunos casos, la latencia de las consultas.

Antes de empezar

Para obtener el permiso que necesitas para habilitar el acceso a Data Boost, pide a tu administrador que te conceda el rol de gestión de identidades y accesos Lector de bases de datos de Cloud Spanner con Data Boost (roles/spanner.databaseReaderWithDataBoost) en la base de datos de Spanner. Para obtener más información sobre cómo conceder roles, consulta el artículo Gestionar el acceso a proyectos, carpetas y organizaciones.

Este rol predefinido contiene el permiso spanner.databases.useDataBoost , que es necesario para habilitar el acceso a Data Boost.

También puedes obtener este permiso con roles personalizados u otros roles predefinidos.

Habilitar Data Boost

Cuando se usan conjuntos de datos externos, Data Boost se usa siempre y no tienes que habilitarlo manualmente.

Si quieres usar Data Boost en tus consultas de EXTERNAL_QUERY, debes habilitarlo al crear una conexión que use tu consulta.

Leer datos en paralelo

Spanner puede dividir determinadas consultas en partes más pequeñas, o particiones, y obtener las particiones en paralelo. Para obtener más información, incluida una lista de limitaciones, consulta Leer datos en paralelo en la documentación de Spanner.

Para ver el plan de ejecución de una consulta de Spanner, consulta Cómo ejecuta Spanner las consultas.

Cuando se ejecutan consultas federadas con conjuntos de datos externos, siempre se usa la opción "Leer datos en paralelo".

Para habilitar las lecturas paralelas al usar EXTERNAL_QUERY, habilítalas al crear la conexión.

Gestionar la prioridad de ejecución de consultas

Cuando ejecutas consultas federadas con una función EXTERNAL_QUERY, puedes asignar prioridad (high, medium o low) a consultas concretas especificando la opción query_execution_priority:

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''',
  '{"query_execution_priority":"high"}');

La prioridad predeterminada es medium.

Las consultas con prioridad high competirán con el tráfico transaccional. Las consultas con prioridad low se realizan con el mejor esfuerzo posible y pueden interrumpirse por la carga en segundo plano, como las copias de seguridad programadas.

Cuando se ejecutan consultas federadas con conjuntos de datos externos, todas las consultas siempre tienen prioridad medium.

Ver el esquema de una tabla de Spanner

Si usa conjuntos de datos externos, sus tablas de Spanner se mostrarán directamente en BigQuery Studio y podrá ver sus esquemas.

Sin embargo, también puedes ver los esquemas sin definir conjuntos de datos externos. También puedes usar la función EXTERNAL_QUERY para consultar vistas de information_schema y acceder a los metadatos de la base de datos. En el siguiente ejemplo se devuelve información sobre las columnas de la tabla MyTable:

Base de datos SQL de Google 

SELECT *
FROM EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT t.column_name, t.spanner_type, t.is_nullable
    FROM information_schema.columns AS t
    WHERE
      t.table_catalog = ''
      AND t.table_schema = ''
     AND t.table_name = 'MyTable'
    ORDER BY t.ordinal_position
  ''');

Base de datos PostgreSQL 

SELECT * from EXTERNAL_QUERY(
 'my-project.us.postgresql.example-db',
  '''SELECT t.column_name, t.data_type, t.is_nullable
    FROM information_schema.columns AS t
    WHERE
      t.table_schema = 'public' and t.table_name='MyTable'
    ORDER BY t.ordinal_position
  ''');

Para obtener más información, consulta las siguientes referencias de esquemas de información en la documentación de Spanner:

Precios

Consultas entre regiones

BigQuery admite consultas federadas en las que las instancias de Spanner y los conjuntos de datos de BigQuery se encuentran en regiones diferentes. Estas consultas generan un coste adicional de transferencia de datos de Spanner. Para obtener más información, consulta los precios de Spanner.

Se te cobrará por la transferencia de datos en función de los siguientes SKUs:

  • Salida de transferencia de datos entre zonas de la misma región de la red
  • Transferencia de datos de salida entre regiones de la red al mismo continente
  • Transferencia de datos de salida entre regiones de red a otro continente

La transferencia de datos se cobra en función de la región de BigQuery en la que ejecutes la consulta y de la región de Spanner más cercana que tenga réplicas de lectura y escritura o de solo lectura.

En las configuraciones multirregionales de BigQuery (US o EU), los costes de transferencia de datos de Spanner se determinan de la siguiente manera:

  • Multirregión de BigQuery US: región de Spanner us-central1
  • Multirregión de BigQuery EU: región de Spanner europe-west1

Por ejemplo:

  • BigQuery (US multirregional) y Spanner (us-central1): se aplican costes a la transferencia de datos dentro de la misma región.
  • BigQuery (US multirregión) y Spanner (us-west4): se aplican costes a la transferencia de datos entre regiones del mismo continente.

Solución de problemas

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

Problema: la consulta no se puede particionar por raíz.
Resolución: Si configura la conexión para leer datos en paralelo, el primer operador del plan de ejecución de la consulta debe ser una unión distribuida o el plan de ejecución no debe tener ninguna unión distribuida. Para resolver este error, consulta el plan de ejecución de la consulta y vuelve a escribirla. Para obtener más información, consulta Cómo ejecuta Spanner las consultas.
Problema: se ha superado el plazo.
Resolución: Selecciona la opción para leer datos en paralelo y reescribe la consulta para que se pueda particionar por raíz. Para obtener más información, consulta Cómo ejecuta Spanner las consultas.

Siguientes pasos