Ejecuta consultas federadas de PostgreSQL

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

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

Limitaciones

Las consultas federadas de PostgreSQL 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 PostgreSQL.
  • La latencia de la consulta federada puede ser bastante más alta que la ejecución de la consulta directamente en PostgreSQL.
  • Es posible que el servicio de conexión subyacente hiberne las conexiones inactivas. La primera consulta que usa una conexión de PostgreSQL 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 PostgreSQL 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 PostgreSQL. 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 PostgreSQL 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 PostgreSQL. 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 16 bits (int2, smallint, smallserial) o columnas de números de punto flotante de precisión simple (float4, real). 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(small_col AS INT64) AS small_col,
CAST(real_col AS FLOAT64) AS real_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.

  • Tanto time [ without time zone ] como time with time zone (timetz) truncan los valores fraccionarios de fracciones de segundo (microsegundos) a segundos enteros. Si se requiere precisión de microsegundos, formatea o convierte la columna en text o varchar en la sentencia EXTERNAL_QUERY.

  • Todas las columnas numeric y decimal de PostgreSQL se asignan a BIGNUMERIC(76, 38). Las columnas con una escala superior a 38 se redondean a 38 posiciones decimales, y los valores que superan el rango de BIGNUMERIC (más de 38 dígitos enteros completos) fallan con un error de fuera de rango. Además, los valores especiales NaN, Infinity y -Infinity no son compatibles y fallan en el tiempo de ejecución. Para consultar números con partes enteras más grandes, mayor precisión fraccionaria o valores especiales, convierte la columna en text en la sentencia EXTERNAL_QUERY.

  • Los valores character(n)/char(n) (bpchar) de longitud fija conservan los espacios finales hasta la longitud n cuando se asignan a STRING de BigQuery.

  • 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. Convierte las fechas históricas en text en la sentencia EXTERNAL_QUERY para conservar las fechas exactas.

Entornos y versiones de PostgreSQL compatibles

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

  • Bases de datos de PostgreSQL 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 PostgreSQL autoalojadas en Compute Engine o en otra infraestructura
  • PostgreSQL 16 (recomendado) y PostgreSQL 15

Antes de comenzar

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

Roles obligatorios

Para obtener los permisos que necesitas para consultar PostgreSQL, 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 PostgreSQL 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 los pedidos de ventas transaccionales en PostgreSQL:

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/postgres_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 PostgreSQL

Puedes consultar las tablas information_schema de PostgreSQL con EXTERNAL_QUERY para recuperar los metadatos de la base de datos:

Enumera todas las tablas de un esquema

SELECT *
FROM EXTERNAL_QUERY(
  'projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID',
  '''SELECT table_schema, table_name, table_type
     FROM information_schema.tables
     WHERE table_schema = 'SCHEMA_NAME'
     ORDER BY table_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
     FROM information_schema.columns
     WHERE table_schema = 'SCHEMA_NAME' AND table_name = 'TABLE_NAME'
     ORDER BY ordinal_position''');

Asignación de tipos de datos

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

Tipo de datos de PostgreSQL Tipo de datos de BigQuery
Tipos booleanos
bool, boolean BOOL
Tipos numéricos
int2, smallint, smallserial INT64 (consultas interactivas; consulta Limitaciones para CREATE TABLE ... AS SELECT)
int4, integer, serial INT64
int8, bigint, bigserial INT64
numeric, decimal BIGNUMERIC(76, 38)
float4, real FLOAT64 (consultas interactivas; consulta Limitaciones para CREATE TABLE ... AS SELECT)
float8, double precision FLOAT64
Tipos de fecha y hora
date DATE
timestamp [ without time zone ] DATETIME
timestamp with time zone, timestamptz TIMESTAMP
time [ without time zone ] TIME
time with time zone, timetz TIME
Tipos de caracteres y texto
character(n), char(n), bpchar STRING
character varying(n), varchar(n) STRING
text 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 PostgreSQL (que cobra tu proveedor de base de datos de PostgreSQL o entorno de hosting)
  • El costo del ancho de banda de la transferencia de los resultados de la consulta de PostgreSQL a BigQuery (cargos de red de salida del proveedor de hosting de la base de datos)
  • El costo de procesamiento de la ejecución de la consulta en BigQuery

Los costos de procesamiento y salida de PostgreSQL se facturan directamente por tu proveedor de PostgreSQL o host de infraestructura, no por Google. Para limitar el costo del ancho de banda, te recomendamos que escribas la consulta en la instrucción 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?