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 externaconnection_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áusulaFROM
. - 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 incluyeORDER 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:
- 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ónEXTERNAL_QUERY()
- La unión de la tabla de resultados de la consulta externa con la tabla de clientes en BigQuery según
customer_id
- 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
ytime
. - PostgreSQL admite muchos tipos de datos no estándar que no son compatibles con BigQuery, por ejemplo,
money
,path
,uuid
yboxer
, 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 valoresNUMERIC
de BigQuery es más pequeño que el de MySQL y PostgreSQL. También se puede asignar aBIGNUMERIC
,FLOAT64
, oSTRING
con “default_type_for_decimal_columns” en opciones deEXTERNAL_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 admitidoGEOMETRY
enSTRING
.
- Mensaje de error:
- 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 admitidomoney
enstring
.
- Mensaje de error:
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.