Introducción a las consultas federadas

En esta página se explica cómo usar las consultas federadas y se ofrecen directrices para consultar datos de Spanner, AlloyDB y Cloud SQL desde BigQuery.

Las consultas federadas te permiten enviar una declaración de consulta a bases de datos de AlloyDB, Spanner o Cloud SQL y obtener el resultado en forma de tabla temporal. Las consultas federadas usan la API de conexión de BigQuery para establecer una conexión con AlloyDB, Spanner o Cloud SQL. En tu consulta, usas la función EXTERNAL_QUERY para enviar una instrucción de consulta a la base de datos externa mediante el dialecto SQL de esa base de datos. Los resultados se convierten en tipos de datos de GoogleSQL.

Almacenes de datos admitidos

Puedes usar consultas federadas con los siguientes almacenes de datos:

Flujo de trabajo

  • Identifica el Google Cloud proyecto que incluye la fuente de datos que quieres consultar.
  • Un bigquery.admin usuario crea un recurso de conexión en BigQuery.
  • El usuario administrador concede permiso para usar el recurso de conexión al usuario B.
    • Si el administrador y el usuario B son la misma persona, no es necesario conceder permiso.
  • El usuario B escribe una consulta en BigQuery con la nueva función de SQL EXTERNAL_QUERY.

Alternativas a las consultas federadas: tablas y conjuntos de datos externos

Otra opción para consultar bases de datos operativas, como Bigtable, Spanner, Cloud Storage, Google Drive y Salesforce Data Cloud, es usar tablas y conjuntos de datos externos. Los conjuntos de datos y las tablas externos te permiten ver tablas y sus esquemas, así como consultarlos sin usar una función SQL EXTERNAL_QUERY. No tienes que volver a introducir los datos en BigQuery y puedes usar la sintaxis de BigQuery en lugar de escribir en el dialecto de la base de datos SQL específica.

Regiones disponibles

Para ver una lista de las ubicaciones admitidas, consulta las siguientes secciones:

AlloyDB y Cloud SQL

Las consultas federadas solo se admiten en las regiones que admiten tanto la fuente de datos externa como BigQuery.

Puedes crear una conexión y ejecutar una consulta federada entre regiones según las siguientes reglas:

Regiones individuales

Una sola región de BigQuery solo puede consultar un recurso de la misma región.

Por ejemplo, si tu conjunto de datos está en us-east4, puedes consultar instancias de Cloud SQL o de AlloyDB que se encuentren en us-east4. La ubicación de procesamiento de las consultas es la región única de BigQuery.

Multirregión

Una multirregión de BigQuery puede consultar cualquier región de origen de datos que se encuentre en la misma zona geográfica grande (EE. UU. o UE). Las ubicaciones multirregionales no están disponibles para las instancias de Cloud SQL, ya que solo se usan para las copias de seguridad.

  • Una consulta que se ejecuta en la multirregión de EE. UU. de BigQuery puede consultar cualquier región de la zona geográfica de EE. UU., como us-central1, us-east4 o us-west2.

  • Una consulta que se ejecuta en la multirregión de la UE de BigQuery puede consultar cualquier región de los Estados miembros de la Unión Europea, como europe-north1 o europe-west3.

  • La ubicación en la que se ejecuta la consulta debe ser la misma que la del recurso de conexión. Por ejemplo, las consultas ejecutadas desde la multirregión de EE. UU. deben usar una conexión ubicada en la multirregión de EE. UU.

El rendimiento de las consultas varía en función de la proximidad entre el conjunto de datos y la fuente de datos externa. Por ejemplo, una consulta federada entre un conjunto de datos de la multirregión de EE. UU. y una instancia de Cloud SQL de us-central1 es rápida. Sin embargo, si ejecutas la misma consulta entre la multirregión de EE. UU. y una instancia de Cloud SQL en us-east4, el rendimiento puede ser más lento.

La ubicación de procesamiento de consultas es la ubicación multirregional, ya sea US o EU.

Spanner

En Spanner, se admiten configuraciones regionales y multirregionales. Una región o multirregión de BigQuery puede consultar una instancia de Spanner en cualquier región de Spanner admitida. Para obtener más información, consulta las consultas entre regiones.

Asignaciones de tipos de datos

Cuando ejecutas una consulta federada, los datos de la fuente de datos externa se convierten en tipos de GoogleSQL. Para obtener más información, consulta el artículo sobre las consultas federadas de Cloud SQL.

Cuotas y límites

  • Consultas federadas entre regiones. Si la ubicación de procesamiento de las consultas de BigQuery y la ubicación de la fuente de datos externa difieren, se genera una consulta entre regiones. Puedes ejecutar hasta 1 TB de consultas entre regiones por proyecto y día. A continuación, se muestra un ejemplo de consulta entre regiones.
    • La instancia de Cloud SQL está en us-west1, mientras que la conexión de BigQuery se basa en la multirregión de EE. UU. La ubicación de procesamiento de las consultas de BigQuery es US.
  • Quota. Los usuarios deben controlar la cuota de consultas en la fuente de datos externa, como Cloud SQL o AlloyDB. No hay ningún ajuste de cuota adicional para las consultas federadas. Para conseguir el aislamiento de la carga de trabajo, se recomienda consultar solo una réplica de lectura de la base de datos.
  • Número máximo de bytes facturados permitido. Este campo no se admite en las consultas federadas. No es posible calcular los bytes facturados antes de ejecutar las consultas federadas.
  • Número de conexiones: Una consulta federada puede tener un máximo de 10 conexiones únicas.
  • Cloud SQL MySQL y PostgreSQL. Se aplican cuotas y limitaciones.

Limitaciones

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

  • Rendimiento. Es probable que una consulta federada no sea tan rápida como una consulta que solo se haga en el almacenamiento de BigQuery. BigQuery debe esperar a que la base de datos de origen ejecute la consulta externa y mueva temporalmente los datos de la fuente de datos externa a BigQuery. Además, es posible que la base de datos de origen no esté optimizada para consultas analíticas complejas.

    El rendimiento de las consultas también varía en función de la proximidad entre el conjunto de datos y la fuente de datos externa. Para obtener más información, consulta la sección sobre las regiones admitidas.

  • Las consultas federadas son de solo lectura. La consulta externa que se ejecuta en la base de datos de origen debe ser de solo lectura. Por lo tanto, no se admiten las instrucciones DML ni DDL.

  • Tipos de datos no admitidos. Si tu consulta externa contiene un tipo de datos que no es compatible con BigQuery, la consulta fallará inmediatamente. Puedes convertir el tipo de datos no admitido en otro tipo de datos admitido.

  • Claves de encriptado gestionadas por el cliente (CMEK). La CMEK se configura por separado para BigQuery y para las fuentes de datos externas. Si configura la base de datos de origen para que use CMEK, pero no BigQuery, la tabla temporal que contiene los resultados de una consulta federada se cifra con una Google-owned and Google-managed encryption key.

Precios

  • Si usas el modelo de precios bajo demanda, se te cobra por el número de bytes devueltos de la consulta externa al ejecutar consultas federadas desde BigQuery. Para obtener más información, consulta los precios de los análisis bajo demanda.

  • Si usas las ediciones de BigQuery, se te cobra en función del número de ranuras que utilices. Para obtener más información, consulta los precios de la capacidad de computación.

Pushdowns de SQL

Las consultas federadas están sujetas a la técnica de optimización conocida como "pushdowns" de SQL. Mejoran el rendimiento de una consulta delegando operaciones como el filtrado en la fuente de datos externa en lugar de realizarlas en BigQuery. Reducir la cantidad de datos transferidos desde la fuente de datos externa puede reducir el tiempo de ejecución de las consultas y los costes. Los pushdowns de SQL incluyen la eliminación de columnas (cláusulas SELECT) y los pushdowns de filtros (cláusulas WHERE).

Cuando usas la función EXTERNAL_QUERY, las inserciones de SQL funcionan reescribiendo la consulta original. En el siguiente ejemplo, se usa la función EXTERNAL_QUERY para comunicarse con una base de datos de Cloud SQL:

SELECT COUNT(*)
FROM (
  SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE a = 'Y' AND b NOT IN ('COMPLETE','CANCELLED');

Sustituye CONNECTION_ID por el ID de la conexión de BigQuery.

Sin la inserción de SQL, la siguiente consulta se envía a Cloud SQL:

SELECT *
FROM operations_table

Cuando se ejecuta esta consulta, se envía toda la tabla a BigQuery, aunque solo se necesiten algunas filas y columnas.

Con las inserciones de SQL, la siguiente consulta se envía a Cloud SQL:

SELECT `a`, `b`
FROM (
  SELECT * FROM operations_table) t
WHERE ((`a` = 'Y') AND (NOT `b` IN ('COMPLETE', 'CANCELLED')))

Cuando se ejecuta esta consulta, solo se envían a BigQuery dos columnas y las filas que coinciden con el predicado de filtrado.

Las inserciones de SQL también se aplican al ejecutar consultas federadas con conjuntos de datos externos de Spanner.

Puedes examinar las inserciones de contenido (si las hay) en el plan de consulta.

Limitaciones

Las inserciones de SQL tienen varias limitaciones que varían en función de la fuente de datos externa y de la forma en que consultes los datos.

Limitaciones de la federación de consultas al usar EXTERNAL_QUERY

  • Las inserciones de SQL solo se aplican a las consultas federadas del formulario SELECT * FROM T.
  • Solo se admiten la eliminación de columnas y la inserción de filtros. En concreto, no se admiten las inserciones de cálculo, unión, límite, orden y agregación.
  • En el caso de las inserciones de filtros, los literales deben ser de uno de los siguientes tipos: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME y TIMESTAMP. No se admiten literales que sean structs.
  • Las derivaciones de funciones SQL solo se aplican a las funciones que admiten tanto BigQuery como una base de datos de destino.
  • Las inserciones de SQL solo se admiten en AlloyDB, Cloud SQL y Spanner.
  • No se admiten las inserciones de SQL en SAP Datasphere.

Limitaciones de la federación de consultas al usar conjuntos de datos externos de Spanner

  • Se admiten las optimizaciones de columnas, filtros, cálculos y agregaciones parciales. En concreto, no se admiten las agregaciones de unión, límite y orden.
  • En el caso de las inserciones de filtros, los literales deben ser de uno de los siguientes tipos: BOOL, INT64, FLOAT64, STRING, DATE, DATETIME, TIMESTAMP, BYTE o Arrays. No se admiten literales que sean structs.
  • Las derivaciones de funciones SQL solo se aplican a las funciones que admiten tanto BigQuery como Spanner.

Funciones admitidas por fuente de datos

A continuación, se indican las funciones de SQL admitidas por fuente de datos. No se admiten funciones para SAP Datasphere.

Cloud SQL MySQL

  • Operadores lógicos: AND, OR y NOT.
  • Operadores de comparación: =, >, >=, <, <=, <>, IN, BETWEEN y IS NULL.
  • Operadores aritméticos: +, - y * (solo para INT64 y FLOAT64).

Cloud SQL PostgreSQL y AlloyDB

  • Operadores lógicos: AND, OR y NOT.
  • Operadores de comparación: =, >, >=, <, <=, <>, IN, BETWEEN y IS NULL.
  • Operadores aritméticos: +, -, * y / (solo para los tipos INT64, FLOAT64 y DATE, excepto para la resta de DATE).

Dialecto PostgreSQL de Spanner

  • Operadores lógicos: AND, OR y NOT.
  • Operadores de comparación: =, >, >=, <, <=, <>, IN, BETWEEN y IS NULL.
  • Operadores aritméticos: +, -, * y / (solo para INT64, FLOAT64 y NUMERIC).

Spanner - dialecto GoogleSQL

  • Operadores lógicos: AND, OR y NOT.
  • Operadores de comparación: =, >, >=, <, <=, <>, IN, BETWEEN y IS NULL.
  • Operadores aritméticos: +, -, * y / (solo para INT64, FLOAT64 y NUMERIC).
  • Operadores aritméticos seguros: SAFE_ADD, SAFE_SUBTRACT, SAFE_MULTIPLY y SAFE_DIVIDE (solo para INT64, FLOAT64 y NUMERIC).
  • Si usa conjuntos de datos externos, tenga en cuenta lo siguiente:
    • Desplazamiento de la computación,
    • Partial Aggregate (Agregación parcial)
    • Funciones String
    • Funciones matemáticas
    • Funciones de conversión
    • Funciones de matriz.

Trabajar con ordenaciones en fuentes de datos externas

Una fuente de datos externa puede tener una intercalación definida en una columna (por ejemplo, que no distinga entre mayúsculas y minúsculas). Cuando ejecutas una consulta federada, la base de datos remota tiene en cuenta la intercalación configurada.

Veamos el siguiente ejemplo, en el que tienes una columna flag con una ordenación que no distingue entre mayúsculas y minúsculas en la fuente de datos externa:

SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table where flag = 'Y'")

Sustituye CONNECTION_ID por el ID de la conexión de BigQuery.

La consulta anterior devuelve las filas en las que flag es y o Y porque la consulta se ejecuta en la fuente de datos externa.

Sin embargo, en el caso de la federación de consultas con fuentes de datos de Cloud SQL, SAP Datasphere o AlloyDB, si añade un filtro a su consulta principal, la consulta se ejecuta en BigQuery con la intercalación predeterminada. Consulta la siguiente consulta:

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE flag = 'Y'

Debido a la ordenación predeterminada que distingue entre mayúsculas y minúsculas en BigQuery, la consulta anterior solo devuelve las filas en las que la marca es Y y excluye las filas en las que la marca es y. Para que la cláusula WHERE no distinga entre mayúsculas y minúsculas, especifica la ordenación en la consulta:

SELECT * FROM
  (
    SELECT * FROM EXTERNAL_QUERY("CONNECTION_ID", "select * from operations_table")
  )
WHERE COLLATE(flag, 'und:ci') = 'Y'

Siguientes pasos