GCP: optimizando consultas y sentencias en BigQuery

BigQuery nos permite realizar las siguientes operaciones:

1. Consulta de datos: Puedes ejecutar consultas SQL complejas para extraer datos de tus conjuntos de datos y realizar análisis avanzados.
2. Análisis de datos en tiempo real: BigQuery admite consultas en tiempo real sobre datos de streaming, lo que te permite analizar y visualizar datos en tiempo real a medida que llegan.
3. Análisis geoespacial: BigQuery incluye funciones y operaciones para realizar análisis geoespaciales, como cálculos de distancia, intersecciones espaciales y agrupaciones geográficas.
4. Operaciones de agregación: Puedes realizar operaciones de agregación como SUM, AVG, COUNT, MAX y MIN en tus datos para resumir la información y obtener insights.
5. Procesamiento de texto: BigQuery proporciona funciones y operadores para procesar datos de texto, como búsquedas de patrones, análisis de sentimientos y extracción de entidades.
6. Integración con herramientas de análisis y visualización: Puedes integrar BigQuery con herramientas de análisis y visualización de datos populares como Google Data Studio, Tableau y Power BI para crear paneles interactivos y visualizaciones de datos.
7. Machine Learning: BigQuery ML te permite construir y entrenar modelos de aprendizaje automático directamente en tus datos almacenados en BigQuery, sin necesidad de moverlos a otro lugar.
8. Carga y exportación de datos: Puedes cargar datos en BigQuery desde archivos locales, Google Cloud Storage, servicios de streaming como Pub/Sub y otras fuentes de datos. También puedes exportar datos desde BigQuery a diferentes formatos de archivo y servicios de almacenamiento.
9. Seguridad y control de acceso: BigQuery ofrece controles de acceso granulares y opciones de cifrado para proteger tus datos y garantizar la conformidad con las normativas de privacidad.
10. Administración y monitoreo: BigQuery proporciona herramientas para administrar y monitorear tus recursos, consultas y cargas de trabajo, como el tablero de control de BigQuery y Cloud Monitoring.

Sin embargo, también tiene ciertos limitantes como lo pueden ser:

• Debes ser cuidadoso por el uso, ya que las cuotas pueden restringir ciertas operaciones iterativas. 
• Si operas sobre una misma tabla, puede haber bloqueos (lo que evitará un buen almacenamiento de tu información). 
• No permite subconsultas como en Informix o herramientas similares. 
• El tamaño de una fila no puede superar los 10MB.
• Etc.

Y es ahí donde entran las optimizaciones con CTEs (Common Table Expressions) o WITH en las consultas. Imaginemos el siguiente bloque de código:

/*
   Este bloque es para actualizar la información de
 la tabla2 desde la tabla1.
*/

begin
declare fecha_origen date default '2026-04-13';
declare fecha_actual date default '2026-05-09';

for record 
in(select user, info_process from
 `mydataset.tabla1` 
where date_process = fecha_origen) 
do
  update `mydataset.tabla2` 
set user = record.user, 
info_process = record.info_process, 
date_process= fecha_actual 
where date_process = fecha_origen ;
end for;

end;

El bloque realiza la operación de actualización correctamente, pero no es lo más óptimo. Pues debemos cuidar los recursos.

Rehacemos el bloque pero usando la cláusula de WITH. Esto nos permitirá la optimización del bloque y ahorraremos tiempo y recursos valiosos.

Tenemos entonces lo siguiente:

/*
   Este bloque es para actualizar la información de
 la tabla2 desde la tabla1.
*/

begin
declare fecha_origen date default '2026-04-13';
declare fecha_actual date default '2026-05-09';

update `mydataset.tabla2` as tab_up 
set user = src.user, 
info_process = src.info_process, 
date_process= fecha_actual 
from(
   select id, user, info_process 
   FROM `mydataset.tabla1` where date_process = fecha_origen 
) as src where  tab_up.id = src.id 
 and  tab_up.date_process = fecha_origen ;

end;

Aunque no empleamos la cláusula, seguimos su misma lógica: optimizar la consulta.

¿Qué pasaría si quisieramos hacer una inserción?

Tomando en cuenta el siguiente bloque:

/*
   Este bloque es para actualizar la información de
 la tabla2 desde la tabla1.
*/

begin
declare fecha_actual date default '2026-05-09';

for record 
in(select valor_mensual from
 `mydataset.tabla1` 
where date_process = fecha_actual and importe < 99.9) 
do
  
insert into `mydataset.tabla2`(valor_mensual) value (record.valor_mensual); 

end for;

end;

El bloque trabaja casi perfectamente, pero no es óptimo el uso de recursos.

Rehacemos el bloque con la lógica de WITH:

/*
   Este bloque es para actualizar la información de
 la tabla2 desde la tabla1.
*/

begin
declare fecha_actual date default '2026-05-09';


insert into `mydataset.tabla2`(valor_mensual) 
with source as(
  select t.valor_mensual from `mydataset.tabla1` t
  where t.date_process = fecha_actual and t.importe < 99.9
) select * from source;



end;

Como se puede observar si usamos la cláusula WITH y no solo su lógica como en el ejemplo del bloque UPDATE.

Y cómo es lógico, también lo podemos aplicar a las consultas con SELECT. Miremos una consulta no optimizada y comparémosla con una que sí lo está:

declare fecha_actual date default '2026-05-09';
declare importe_max float64;
set importe_max = 99.9;

select user, date_process, importe, valor_mensual
 `mydataset.tabla2`
where date_process = (
   select date_process
 `mydataset.tabla1`where date_process = fecha_actual 
) and importe = (
  select importe
 `mydataset.tabla1`where importe < importe_max 
);

Optimizada:

DECLARE fecha_actual DATE DEFAULT DATE '2026-05-09';
DECLARE importe_max FLOAT64 DEFAULT 99.9;

WITH filtro_fecha AS (
  SELECT date_process
  FROM `mydataset.tabla1`
  WHERE date_process = fecha_actual
),
filtro_importe AS (
  SELECT importe
  FROM `mydataset.tabla1`
  WHERE importe < importe_max
)
SELECT user, date_process, importe, valor_mensual
FROM `mydataset.tabla2`
WHERE date_process IN (SELECT date_process FROM filtro_fecha)
  AND importe IN (SELECT importe FROM filtro_importe);

¿Y qué de las operaciones de borrado?

Sin optimizar:

begin
declare fecha_actual date default '2026-05-09';

for record 
in(select valor_mensual from
 `mydataset.tabla1` 
where date_process = fecha_actual and importe < 99.9) 
do
  
delete from `mydataset.tabla2`where valor_mensual = record.valor_mensual
 and date_process = fecha_actual;

end for;

end;

Optimizada:

DECLARE fecha_actual DATE DEFAULT DATE '2026-05-09';

WITH valores_a_borrar AS (
  SELECT valor_mensual
  FROM `mydataset.tabla1`
  WHERE date_process = fecha_actual
    AND importe < 99.9
)
DELETE FROM `mydataset.tabla2`
WHERE date_process = fecha_actual
  AND valor_mensual IN (SELECT valor_mensual FROM valores_a_borrar);

Como hemos visto, el uso de la cláusula WITH nos permite evitar subconsultas repetitivas y hace el código más legible y eficiente.

Seguiremos hablando de este tema en próximas entregas.

Enlaces:

https://codemonkeyjunior.blogspot.com/2024/04/gcp-google-cloud-bigquery.html
WITH statements in BigQuery SQL (Youtube)