Funciones de consultas federadas

GoogleSQL para BigQuery admite las siguientes funciones de consultas federadas.

Lista de funciones

Nombre Resumen
EXTERNAL_QUERY Ejecuta una consulta en una base de datos externa y muestra los resultados como una tabla temporal.

EXTERNAL_QUERY

EXTERNAL_QUERY('connection_id', '''external_database_query'''[, 'options'])

Descripción

Ejecuta una consulta en una base de datos externa y muestra los resultados como una tabla temporal. El tipo de datos de la base de datos externa se convierte en un tipo de datos de GoogleSQL en la tabla de resultados temporal con estas asignaciones de tipos de datos.

  • external_database_query: Es la consulta que se ejecutará en la base de datos externa

  • connection_id: Es el ID del recurso de conexión. El recurso de conexión contiene la configuración para la conexión entre la base de datos externa y BigQuery. Si no tienes un proyecto predeterminado configurado, antepone el ID del proyecto al ID de conexión en el siguiente formato:

    projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID

    Reemplaza lo siguiente:

    • PROJECT_ID: El ID del proyecto
    • LOCATION: Es la ubicación de la conexión.
    • CONNECTION_ID: El ID de la conexión.

    Por ejemplo, projects/example-project/locations/us/connections/sql-bq. Para obtener más información, consulta Crea un recurso de conexión.

+ options: una cadena opcional de un mapa en formato JSON con pares clave-valor de nombre y valor de opción (ambos distinguen mayúsculas de minúsculas).

For example::
``` '{"default_type_for_decimal_columns":"numeric"}' ```

Supported options:

|Option Name | Description
|-------- | -------
|"default_type_for_decimal_columns" | Can be "float64", "numeric", "bignumeric" or "string". With this option, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to the provided BigQuery type. When this option is not provided, the MySQL Decimal type or PostgreSQL Numeric type will be mapped to BigQuery NUMERIC type.
|"query_execution_priority" | Can be "low", "medium" or "high". Only supported in Spanner. Specifies priority for execution of the query. Execution priority is "medium" by default.

Notas adicionales:

  • Por lo general, la función EXTERNAL_QUERY se usa en una cláusula FROM.
  • Puedes usar la función EXTERNAL_QUERY() para acceder a los metadatos de la base de datos externa.
  • EXTERNAL_QUERY() no respetará el orden del resultado de la consulta externa, incluso si esta incluye ORDER BY.

Tipo de datos mostrados

Tabla de BigQuery

Ejemplos

Supongamos que necesitas incluir la fecha del primer pedido de cada uno de tus clientes en un informe. Por el momento, estos datos no están disponibles en BigQuery, pero sí lo están en tu base de datos operativa PostgreSQL en . En el siguiente ejemplo de consulta federada, se logra este objetivo. Este ejemplo consta de 3 partes:

  1. La ejecución de 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 PostgreSQL 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
SELECT
  c.customer_id, c.name, SUM(t.amount) AS total_revenue, rq.first_order_date
FROM customers AS c
INNER JOIN transaction_fact AS t ON c.customer_id = t.customer_id
LEFT OUTER JOIN
  EXTERNAL_QUERY(
    '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;

Puedes usar la función EXTERNAL_QUERY() para consultar tablas information_schema a fin de acceder a los metadatos de la base de datos, como listas de todas las tablas en la base de datos o el esquema de la tabla. Las siguientes consultas de ejemplo de information_schema funcionan en MySQL y 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';'''
  );

EXTERNAL_QUERY() no respetará el orden del resultado de la consulta externa, incluso si esta incluye ORDER BY. En el siguiente ejemplo de consulta, se ordenan las filas por ID de cliente en la base de datos externa, pero BigQuery no mostrará las filas de resultado en ese orden.

-- ORDER BY will not order rows.
SELECT *
FROM
  EXTERNAL_QUERY(
    'connection_id',
    '''SELECT * FROM customers AS c ORDER BY c.customer_id'''
  );

Asignaciones de tipos de datos

Cuando ejecutas una consulta federada, los datos de la base de datos externa se convierten en tipos de GoogleSQL. A continuación, se muestran las asignaciones de tipos de datos de MySQL a BigQuery y PostgreSQL a BigQuery.

Debes contar con la siguiente información sobre la asignación:

  • La mayoría de los tipos de datos de MySQL pueden coincidir con el mismo tipo de datos de BigQuery, con algunas excepciones, como decimal, timestamp y time.
  • PostgreSQL admite muchos tipos de datos no estándar que no son compatibles con BigQuery, por ejemplo, money, path, uuid y boxer, entre otros.
  • Los tipos de datos numéricos en MySQL y PostgreSQL se asignarán al valor de NUMERIC de BigQuery de forma predeterminada. El rango de valores NUMERIC de BigQuery es más pequeño que el de MySQL y PostgreSQL. También se puede asignar a BIGNUMERIC,FLOAT64, o STRING con “default_type_for_decimal_columns” en opciones de EXTERNAL_QUERY.

Manejo de errores

Si tu consulta externa contiene un tipo de datos que no es compatible con BigQuery, fallará de inmediato. Puedes convertir el tipo de datos no compatible en un tipo de datos de MySQL o PostgreSQL diferente que sea compatible. Consulta los Tipos de datos no compatibles para obtener más información sobre cómo convertir tipos de datos.

Asignación de tipos de MySQL a BigQuery

Tipo de MySQL Descripción en MySQL Tipo de BigQuery Diferencias entre los tipos
Número entero
INT 4 bytes, 2^32 - 1 INT64
TINYINT 1 byte, 2^8 - 1 INT64
SMALLINT 2 bytes, 2^16 - 1 INT64
MEDIUMINT 3 bytes, 2^24 - 1 INT64
BIGINT 8 bytes, 2^64 - 1 INT64
UNSIGNED BIGINT 8 bytes, 2^64 - 1 NUMERIC
Numérico exacto
DECIMAL (M, D) Un decimal se representa mediante (M,D) en el que M es la cantidad total de dígitos y D es la cantidad de decimales. M <= 65 NUMERIC, BIGNUMERIC, FLOAT64, or STRING

 DECIMAL (M,D) se asignará a NUMERIC de forma predeterminada o se puede asignar a BIGNUMERIC, FLOAT64 o STRING con default_type_for_decimal_columns.
Numérico aproximado
FLOAT (M,D) 4 bytes, M <= 23 FLOAT64
DOUBLE (M,D) 8 bytes, M <= 53 FLOAT64
Fecha y hora
TIMESTAMP De '1970-01-01 00:00:01' UTC a '2038-01-19 03:14:07' UTC TIMESTAMP TIMESTAMP de MySQL se recupera como zona horaria UTC sin importar desde dónde llame el usuario a BigQuery
DATETIME De '1000-01-01 00:00:00' a '9999-12-31 23:59:59' DATETIME
DATE De “1000-01-01” a “9999-12-31” DATE
TIME Hora en formato “HH:MM:SS”
De “-838:59:59” a “838:59:59”		 TIME
 El intervalo de TIME en BigQuery es menor, de 00:00:00 a 23:59:59
YEAR INT64
Caracteres y strings
ENUM Un objeto de string con un valor elegido de una lista de valores permitidos STRING
CHAR (M) Una string de longitud fija de entre 1 y 255 caracteres STRING
VARCHAR (M) Una string de longitud variable de entre 1 y 255 caracteres STRING
TEXT Un campo con una longitud máxima de 65,535 caracteres STRING
TINYTEXT Una columna TEXT con una longitud máxima de 255 caracteres STRING
MEDIUMTEXT Una columna TEXT con una longitud máxima de 16,777,215 caracteres STRING
LONGTEXT Una columna TEXT con una longitud máxima de 4,294,967,295 caracteres STRING
Binario
BLOB Un objeto binario grande con una longitud máxima de 65,535 caracteres BYTES
MEDIUM_BLOB Un BLOB con una longitud máxima de 16,777,215 caracteres BYTES
LONG_BLOB Un BLOB con una longitud máxima de 4,294,967,295 caracteres BYTES
TINY_BLOB Un BLOB con una longitud máxima de 255 caracteres BYTES
BINARY Una string binaria de longitud fija de entre 1 y 255 caracteres BYTES
VARBINARY Una string binaria de longitud variable entre 1 y 255 caracteres BYTES
Otro
SET Cuando se declara la columna SET, se deben predefinir algunos valores. Luego, se debe usar INSERT en cualquier conjunto de valores predefinidos para esta columna STRING
GEOMETRY GEOGRAPHY AÚN COMPATIBLE
BIT INT64 AÚN COMPATIBLE

Asignación de tipos de PostgreSQL a BigQuery

Nombre Descripción Tipo de BigQuery Diferencias entre los tipos
Número entero
smallint 2 bytes, de -32,768 a +32,767 INT64
smallserial Ver smallint INT64
integer 4 bytes, de -2,147,483,648 a +2,147,483,647 INT64
serial Ver integer INT64
bigint 8 bytes, de -9,223,372,036,854,775,808 a 9,223,372,036,854,775,807 INT64
bigserial Ver bigint INT64
Numérico exacto
numeric [ (p, s) ] Precisión hasta 1,000. NUMERIC, BIGNUMERIC, FLOAT64, or STRING numeric [ (p, s) ] se asignará a NUMERIC de forma predeterminada o se puede asignar a BIGNUMERIC, FLOAT64 o STRING con default_type_for_decimal_columns.
Decimal [ (p, s) ] Ver numeric NUMERIC Ver numeric
money 8 bytes, 2 dígitos de escala, de -92,233,720,368,547,758.08 a +92,233,720,368,547,758.07 NO COMPATIBLE
Numérico aproximado
real 4 bytes, número de punto flotante de precisión simple FLOAT64
double precision 8 bytes, número de punto flotante de precisión doble FLOAT64
Fecha y hora
date Fecha de calendario (año, mes, día) DATE
time [ (p) ] [ without time zone ] Hora del día (sin zona horaria) TIME
time [ (p) ] with time zone Hora del día con la zona horaria incluida NO COMPATIBLE
timestamp [ (p) ] [ without time zone ] Fecha y hora (sin zona horaria) DATETIME
timestamp [ (p) ] with time zone Fecha y hora con la zona horaria incluida TIMESTAMP TIMESTAMP de PostgreSQL se recupera como zona horaria UTC sin importar desde dónde llame el usuario a BigQuery
interval Un intervalo de tiempo NO COMPATIBLE
Caracteres y strings
character [ (n) ] String de caracteres de longitud fija STRING
character varying [ (n) ] String de caracteres de longitud variable STRING
text String de caracteres de longitud variable STRING
Binario
bytea Datos binarios (“arreglo de bytes”) BYTES
bit [ (n) ] String de bits de longitud fija BYTES
bit varying [ (n) ] String de bits de longitud variable BYTES
Otro
boolean Lógica booleana (verdadero o falso) BOOL
inet Dirección de host IPv4 o IPv6 NO COMPATIBLE
path Ruta geométrica en un plano NO COMPATIBLE
pg_lsn Número de secuencia de registro de PostgreSQL NO COMPATIBLE
point Punto geométrico en un plano NO COMPATIBLE
polygon Ruta geométrica cerrada en un plano NO COMPATIBLE
tsquery Búsqueda de texto NO COMPATIBLE
tsvector Documento de búsqueda de texto NO COMPATIBLE
txid_snapshot Instantánea del ID de transacción a nivel de usuario NO COMPATIBLE
uuid Identificador único universal NO COMPATIBLE
xml Datos XML STRING
box Cuadro rectangular en un plano NO COMPATIBLE
cidr Dirección de red IPv4 o IPv6 NO COMPATIBLE
circle Círculo en un plano NO COMPATIBLE
interval [ fields ] [ (p) ] Intervalo de tiempo NO COMPATIBLE
json Datos JSON textuales STRING
jsonb Datos JSON binarios descompuestos NO COMPATIBLE
line Línea infinita en un plano NO COMPATIBLE
lseg Segmento de línea en un plano NO COMPATIBLE
macaddr Dirección MAC (Control de acceso a medios) NO COMPATIBLE
macaddr8 Dirección MAC (Control de acceso a medios) en formato EUI-64 INCOMPATIBLE

Tipos de datos de MySQL y PostgreSQL no compatibles

Si tu consulta externa contiene un tipo de datos que no es compatible con BigQuery, fallará de inmediato. Puedes convertir el tipo de datos no admitido en un tipo de datos compatible de MySQL o PostgreSQL diferente.

  • Tipo de datos MySQL no admitido
    • Mensaje de error: Invalid table-valued function external_query Found unsupported MySQL type in BigQuery. at [1:15]
    • Tipo no admitido: GEOMETRY, BIT
    • Resolución: Convierte el tipo de datos no admitidos en STRING.
    • Por ejemplo: SELECT ST_AsText(ST_GeomFromText('POINT(1 1)'));; este comando convierte el tipo de datos no admitido GEOMETRY en STRING.
  • Tipo de datos de PostgreSQL no admitido
    • Mensaje de error: Invalid table-valued function external_query Postgres type (OID = 790) is not supported now at [1:15]
    • Tipo no compatible: money, time with time zone, inet, path, pg_lsn, point, polygon, tsquery, tsvector, txid_snapshot, uuid, box, cidr, circle, interval, jsonb, line, lseg, macaddr, macaddr8
    • Resolución: Convierte el tipo de datos no compatible en STRING
    • Por ejemplo: SELECT CAST('12.34'::float8::numeric::money AS varchar(30));; este comando convierte el tipo de datos no admitido money en string.

Asignación de tipos de Spanner a BigQuery

Cuando ejecutas una consulta federada de Spanner, los datos de Spanner se convierten en tipos de GoogleSQL.

Tipo de Spanner para GoogleSQL Tipo de Spanner para PostgreSQL Tipo de BigQuery
ARRAY - ARRAY
BOOL bool BOOL
BYTES bytea BYTES
DATE date DATE
FLOAT64 float8 FLOAT64
INT64 bigint INT64
JSON JSONB JSON
NUMERIC numeric* NUMERIC
STRING varchar STRING
STRUCT - No es compatible con las consultas federadas de Spanner
TIMESTAMP timestamptz TIMESTAMP con nanosegundos truncados

* Los valores numéricos de PostgreSQL con una precisión mayor que la precisión que admite BigQuery se redondean. Los valores mayores que el valor máximo generan un error Invalid NUMERIC value.

Si tu consulta externa contiene un tipo de datos que no es compatible con las consultas federadas, la consulta fallará de inmediato. Puedes convertir el tipo de datos no admitido en un tipo de datos compatible.