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)

GCP: manejo de excepciones en BigQuery

 

El manejo de excepciones es un mecanismo para identificar, tratar y recuperar el flujo normal de un programa en ejecución. El lenguaje de BigQuery nos permite gestionar los errores cuando estos ocurren  en nuestras consultas SQL. Miremos unos ejemplos.

El siguiente bloque de BigQuery tratará de ejecutar una consulta a una tabla que no existe (tabla_no_existente). El sub-bloque tiene un manejo de excepción de tipo ``ERROR``.

DECLARE error STRING;
BEGIN
  BEGIN
     -- Primera consulta, fallará.
     SELECT * FROM mydataset.tabla_no_existente;
  EXCEPTION WHEN ERROR THEN
      SET error = 'Ha ocurrido una excepcion.';
      RAISE; -- Termina la ejecución de todo el bloque. La segunda consulta no se realizará.
   END;

   -- Segunda consulta.
   SELECT * FROM mydataset.tabla_existente LIMIT 1;
END;

Si queremos que la segunda consulta se ejecute aún si ocurre un error en la primera consulta, entonces tan solo quitamos la instrucción ``RAISE``, la cual se utiliza para generar explícitamente un error o reactivar una excepción existente.

Tendríamos lo siguiente:

DECLARE error STRING;
BEGIN
  BEGIN
     -- Primera consulta, fallará.
     SELECT * FROM mydataset.tabla_no_existente;
  EXCEPTION WHEN ERROR THEN
      SET error = 'Ha ocurrido una excepcion.';
   END;

   -- Segunda consulta. Se ejecuta aunque la primera haya fallado.
   SELECT * FROM mydataset.tabla_existente LIMIT 1;
END;

La primera consulta fallará, pero no se terminará la ejecución del bloque completo. Se ejecutará la segunda consulta.

¿Cómo obtener información detallada del error ocurrido?

Crearemos un Stored Procedure que nos permitirá mostrar a detalle el error en la ejecución de una consulta SQL en BigQuery.

CREATE OR REPLACE PROCEDURE `mydataset.exec_sp_error`(OUT error_flag STRING)
BEGIN
   -- Esta consulta fallará.
   SELECT usuario,* FROM mydataset.tabla_no_existente;

  EXCEPTION WHEN ERROR THEN
   SET error_flag = "Error: ";
   SET error_flag = concat(salida, @@error.message);
   SET error_flag = concat(salida, ". Causa: "); 
   SET error_flag = concat(salida, @@error.statement_text);  
END;

Invocamos el Stored Procedure y consultamos la variable ``error_flag``:

DECLARE error_flag STRING;
-- Invocamos el Stored Procedure
CALL`mydataset.exec_sp_error`(error_flag);
-- Mostrará el error a detalle
SELECT error_flag;

En lenguajes de programación como Java, C++ y C# se puede hacer algo como esto:

try{
   int divide = 1/0;
}catch(ArithmeticException ex){
   System.err.printf("%s\n", ex.getCause());
   System.err.printf("%s\n", ex.getMessage());
   ex.printStackTrace();
}

Lo cual disparará una excepción de tipo ``ArithmeticException``, pues no podemos dividir un número por cero.

En BigQuery el código equivalente sería:

BEGIN
  SELECT 1/0; -- División por cero. 
EXCEPTION WHEN ERROR THEN
  SELECT @@error.message AS error_message;
  SELECT @@error.stack_trace AS stack_trace;
  SELECT @@error.statement_text AS text_error;
END;

Cuando se lance la excepción se mostrará los errores en la consola de BigQuery.

Continuaremos sobre esta serie de BigQuery más adelante.

Enlaces:

https://codemonkeyjunior.blogspot.com/2024/11/stored-procedures-oracle-plsql-gcp.html
https://cloud.google.com/bigquery/docs/error-messages

GCP: Crear un archivo CSV a partir de una consulta en BigQuery

En esta ocasión veremos como crear un archivo CSV a partir del resultado de una consulta en GCP BigQuery.

¿Qué haremos?

1. Crearemos una tabla temporal. 
2. Consultaremos una tabla de la cual queremos los datos.
3. Con los datos obtenidos crearemos un archivo CSV.

¿Qué es una tabla temporal en BigQuery?

Es una tabla creada temporalmente. Nos servirá como pivote para obtener ciertos datos de una consulta.

¿Cómo exportamos datos en BigQuery?

Usaremos la siguiente sentencia para exportar datos:

EXPORT DATA

El código es el siguiente:

CREATE OR REPLACE PROCEDURE `myproject.mydataset.create_file_csv`(input_fecha STRING)
BEGIN
  -- Crear una tabla temporal con los datos de la consulta
  CREATE TEMP TABLE temp_table AS
  SELECT * FROM `myproject.mydataset.Informe`
  WHERE fecha = CAST(input_fecha AS DATE);

  -- Exportar la tabla temporal a un archivo CSV en GCS, ya que BigQuery no soporta TXT directamente
  EXPORT DATA OPTIONS(
    uri='gs://your_bucket/path/to/file*.csv',
    format='CSV',
    overwrite=true,
    header=true
  ) AS
  SELECT * FROM temp_table;

END;

Para invocar el SP:

CALL `myproject.mydataset.create_file_csv`('2025-01-23');

Tendremos que ir a nuestro Bucket para comprobar que el archivo se ha creado.

En próximas entregas continuaremos con este tema.

Enlaces:

https://cloud.google.com/bigquery/docs/exporting-data

Más sobre esProc SPL, un lenguaje orientado al tratamiento de datos

En una anterior entrega dimos un vistazo a esProc SPL, un lenguaje orientado al tratamiento y almancenamiento de datos.

Peculiaridades del lenguaje

esProc SPL a diferencia del lenguaje de programación basado en texto, escribe código en líneas de cuadrícula, similar a una hoja de Excel.

• esProc SPL puede generar alta eficiencia a un costo mucho menor.
• esProc SPL es una biblioteca de clases de computación de datos basada en JVM
• Tiene muchas más y mejores funcionalidades que los otros lenguajes de procesamiento de datos basados en JVM (como Kotlin y Scala).
• Puede realizar cálculos de estilo SQL sin bases de datos.
• Admite cálculos directos en archivos.
• esProc SPL permite microservicios más flexibles.
• esProc también se puede integrar en una aplicación para que actúe como una base de datos incorporada.
• esProc SPL facilita la consecución de algoritmos de alto rendimiento y, por tanto, obtiene un rendimiento informático mucho mayor que el almacén de datos relacional tradicional.

Como se ha explicado, al usar este lenguaje, trabajaremos con celdas o líneas de cuadrícula. En ellas escribiremos nuestras sentencias a ejecutar. Miremos un ejemplo:

=1-2+3-4
=3*54
=6/2.32
=1.43434343434+2.65656556+0.0988888

Para ejecutar estas líneas debemos presionar Ctrl + F9 y observaremos el resultado.

Existe un libro que puedes consultar si te llama la atención ver más a detalle este lenguaje:

https://www.scudata.com/html/SPL-programming-book.html

Enlaces:

https://www.scudata.com/html/SPL-programming-book.html
https://www.reddit.com/r/esProcSPL/comments/1ga07gr/what_is_esproc_spl/
https://github.com/SPLWare/esProc

GCP: Recorrer consultas con FOR en BigQuery

(GCP) BigQuery posee un lenguaje propio similar al PL/SQL de Oracle. Con este lenguaje podemos realizar diversas operaciones con los datos de las tablas.

Desde simples consultas (SELECT), inserciones de datos (INSERT), actualizaciones (UPDATE) y hasta eliminar datos (DELETE). También operaciones de creación (CREATE) o truncado (TRUNCATE).

Además de contar con diversas funciones de cadena, tiempo, matemáticas, etc. que nos pueden servir para distintos fines.

SELECT "Hola, mundo en BigQuery!!";

-- Mismo mensaje usando la función FORMAT
SELECT ('%s', "Hola, mundo en BigQuery!!");

¿Qué podemos hacer con este lenguaje de BigQuery?

Podemos validar si un campo es nulo:

-- Recordar que cada bloque tiene un inicio y un fin
BEGIN
  -- Declaramos una variable de tipo INTEGER
  DECLARE resultado INT64;
  -- Seteamos el resultado de la consulta a la variable resultado
  SET resultado = (SELECT COUNT(campo) FROM `PROJECT.DATASET.Tabla`);
  -- Verificamos si el resultado es null
  IF resultado IS NULL THEN
     SELECT FORMAT('%s', 'El resultado es NULL');
  END IF;

END;

Podríamos recorrer el resultado de una consulta con un bucle FOR ... DO

BEGIN
   -- Declaramos las variables
   DECLARE fecha DATE;
   DECLARE resultado INT64;

   -- Seteamos fecha
   fecha = '2013-04-12';

   -- Recorremos resultado de consulta, similar al CURSOR de PL/SQL de Oracle
   FOR rowI IN(SELECT campo FROM `PROJECT.DATASET.TablaContable` WHERE fch = fecha)
    DO
  -- Seteamos el valor rescuperado en la variable resultado
  SET resultado = rowI[0];
  SELECT FORMAT('Recuperamos el valor: %d', resultado);
  -- Realizamos una actualización en otra tabla
  UPDATE `PROJECT.DATASET.TablaContableTemp` SET campo = resultado
  WHERE fch = fecha; 

END FOR;

END;

Recordar que es necesario que cada bloque empiece con la palabra BEGIN y termine con la palabra END con punto y coma. Misma regla aplica cuando uno crear un Stored Procedure.

-- Stored Procedure para actualizar tabla: TABLACONTABLETEMP
CREATE OR REPLACE PROCEDURE  `PROJECT.DATASET.ActualizaTablaContableTemp`(fecha DATE)
BEGIN
   -- Declaramos las variables
   DECLARE resultado INT64;

   -- Recorremos resultado de consulta, similar al CURSOR de PL/SQL de Oracle
   FOR row IN(SELECT campo FROM `PROJECT.DATASET.TablaContable` WHERE fch = fecha)
    DO
  -- Seteamosel valor rescuperado en la variable resultado
  SET resultado = row[0];
  SELECT FORMAT('Recuperamos el valor: %d', resultado);
  -- Realizamos una actualización en otra tabla
  UPDATE `PROJECT.DATASET.TablaContableTemp` SET campo = resultado
  WHERE fch = fecha; 

END FOR;

END;

Para invocarlo bastaría esta línea:

CALL`PROJECT.DATASET.ActualizaTablaContableTemp`('2013-04-12');

Más ejemplos en próximas entregas.

Enlaces:

https://codemonkeyjunior.blogspot.com/2024/06/gcp-funciones-matematicas-de-cadena-y.html

Aprender Oracle SQL con LiveSQL

Si deseas aprender SQL o mejorar tus habilidades existe un sitio para hacerlo: https://livesql.oracle.com/

LiveSql es un sitio con tutoriales, ejemplos y editor que nos permite ejecutar sentencias SQL como:

• Crear tablas.
• Crear vistas.
• Consultar datos de las tablas.
• Crear bloques PL/SQL.
• Crear funciones y procedimientos PL/SQL.
• etc.

¿Qué es SQL?

Son las siglas de Structured Query Language. Un lenguaje especializado para la creación y manipulación de la información contenida en tablas de información perteneciente a una base de datos.

El SQL se divide en:

• DCL: Data Control Language (GRANT/REVOQUE).
• TCL: Transaction Control Language (COMMIT/ROLLBACK/SAVEPOINT).
• DDL: Data Definition Language(CREATE/ALTER/DROP/RENAME/TRUNCATE).
• DML:Data Manipulation Language(INSERT/SELECT/UPDATE/DELETE).

DCL, nos permitirá otorgar y revocar permisos a tablas, funciones y procedimientos y otros objetos.

TCL, nos permitirá el control de las transacciones.

DDL, nos permitirá el control de la definición de los objetos.

DML, nos permitirá la manipulación de la información contenida.

LiveSql está enfocado tanto a principiantes como experimentados en el conocimiento de SQL. No permite crear o borrar bases de datos. No las necesitas, con que puedas crear tablas y manipularlas te bastará, ya que trabajarás con la BD que provee el sitio.

Oracle posee, al igual que otros SGBD (Sistema Gestor de Bases de Datos) su propios tipos de datos como lo pueden ser:

VARCHAR
CHAR
FLOAT
NUMBER
DATE
LONG
BINARY_FLOAT
BINARY_DOUBLE
TIMESTAMP
CLOB
BLOB
...

El editor nos permitirá ejecutar cualquier sentencia SQL válida:

select to_char(sysdate, 'HH:MI:SS') as hora from dual;

HORA

03:13:05

Podremos ejecutar bloques PL/SQL:

DECLARE
BEGIN
   dbms_output.put_line('Fecha: '||sysdate);
END;
/

Fecha: 12-MAY-24

Más ejemplos en próximos posts.

Enlaces:

https://livesql.oracle.com/

JDBI con Groovy

En esta ocasión usaremos Groovy para crear una aplicación usando JDBI. Prácticamente es el mismo código usado la vez anterior que se dio un vistazo a JDBI. La diferencia radica en el uso de @Grapes y Grab para importar y cargar las librerías necesarias.

He aquí el código

Programando en Java ... no. 8

Este ejercicio es muy sencillo, no es nada del otro mundo, crearemos una conexión a una base de datos MySQL, un método para insertar, actualizar, borrar y mostrar todos los datos de una tabla.

Herramientas:

• Eclipse Luna
• Librería  mysql-connector-java-5.1.34-bin.jar

Creamos la base de datos
create database test;

Creamos la tabla persona
create table persona(id int auto_increment primary key, nombre varchar(55),apellidoP varchar(55),apellidoM varchar(55),edad int, peso float,talla float);

Insertar valores a la tabla
insert into persona (nombre,apellidoP,apellidoM,edad,peso,talla) values ("Ernesto","Huerta","Flores",44,78.2,1.70), ("Mariana","Garcia",Torres",23,56.0,1.67);

Código de la conexión a la base de datos (Conexion.java)

La clase Persona.java tendrá las mismas propiedades de la tabla persona

public class Persona {
    private Integer id;
    private String nombre;
    private String apellidoP;
    private String apellidoM;
    private int edad;
    private Float peso;
    private Float talla;
   
    public Persona(){}

    //...demás métodos
}

Método para consultar los datos de la tabla

Método para crear persona

;

Método para actualizar persona

Método para eliminar persona