Tipos de actualización SQL

Última modificación el 03/04/2024

Vision permite dos opciones principales para actualizar o refrescar los datos de un dataset cuyo origen de datos es una base de datos relacional (SQL): actualización completa o incremental. Además, la actualización incremental se subdivide en varias opciones en función del tipo de incremental que se desee o se pueda aplicar.

Tipos de actualización desde BBDD SQL

Actualización Completa

Este tipo de actualización es muy simple: dato un dataset se desechan los datos anteriores y se cargan nuevamente a partir del resultado de la ejecución de la consulta. Por seguridad esta actualización se lleva a cabo de la siguiente manera:

Se lanza un proceso que carga los datos en un dataset auxiliar oculto para los usuarios.
Si el proceso tiene éxito, es decir, los datos devueltos por la consulta se cargan sin problemas, el dataset se modifica para apuntar a los datos recien actualizados y se borran los datos antiguos. De esta forma se evita que una carga interrumpida deje al dataset en un estado de corrupción de datos, siendo el resultado de la actualización OK.
Si hay un error en el proceso auxiliar este se aborta y/o se deshecha, y el dataset mantienen intactos sus datos originales. El estado de la última actualización es por tanto ERROR.

Actualización Incremental

Una actualización incremental deja intacta una parte de los datos del dataset, y carga unos datos nuevos. Por tanto, es recomendable cuando el volumen de datos a mover entre BBDD y Vision es elevado.

Para que se pueda realizar una actualización incremental hay 3 elementos necesarios:

Un campo "incremental" de tipo fecha o numérico.
Un criterio de partición, por ejemplo "mayor que".
Un valor de referencia igual al máximo valor del campo incremental en la anterior carga de datos, y que por tanto se modifica después de cada refresco.

Este criterio se ha de incluir en la consulta SQL que actualiza los datos, de tal forma que todos los registros de la consulta SQL que cumplen que el campo incremental es mayor que el valor de referencia se insertan en el dataset sin tocar los datos ya presentes, mientras que cuando no se cumple la condición la propia consulta excluye tales datos.

El refresco incremental, no obstante, puede modificarse para diferenciar casuísticas particulares más delicadas, ya que en la realidad el criterio incremental no siempre se cumple en el 100% de los casos. Por ello, en función de la naturaleza del propio proceso de registro y actualización de los datos hay diferentes opciones:

Incremental Normal: deja intactos los datos presentes y carga contenido nuevo.
Incremental Ventana: deja intacta una parte de los datos presentes, borra otra parte de acuerdo con una ventana temporal, y carga contenido nuevo desde el inicio de la ventana.
Incremental Últimos cambios: deja intacta una parte de los datos presentes, actualiza otra parte de ellos, y también carga contenido nuevo.

Además, y como complemento a la actualización incremental, también se permite truncar el histórico de datos. La idea subyacente a un refresco incremental es que se van añadiendo nuevos datos para actualizar el presente y eliminando datos antiguos que quedan ya en el pasado lejano.

Incremental Normal

El tipo de actualización incremental Normal deja intactos los datos presentes y carga contenido nuevo, registros a partir de cierto valor del campo "incremental"

Configuración de un incremental Normal

Campo incremental: es la columna de una tabla o el alias de un campo presente en la consulta SQL (y por tanto el "alias de BBDD" o Fieldname del campo en el dataset) que se va a usar en la condición incremental, generalmente una columna numérica autoincremental o una fecha.
Variable: es la variable que utiliza internamente Vision y que toma el valor máximo del campo incremental en el dataset antes de la actualización ($last_value$). Este valor no se modifica, a menos que se precise implementar una condición incremental más compleja, en cuyo caso hay que contactar con el administrador de Vision para valorar el caso.
La condición incremental se añade a la sentencia SQL insertando en la parte where de la consulta un texto especial: $condition$. Vision entiende este texto como una variable, al ejecutar la consulta, resuelve primero el valor de $last_value$ y sustituye $condition$ por campo_incremental > last_value . Teniendo esto en cuenta, el usuario ha de escribir una consulta SQL válida, insertando la variable $condition$ correctamente en la clausula where de la consulta.

Sintaxis SQL válida usando un alias en la condición incremental

Incremental Ventana

El tipo de actualización incremental Ventana es una extensión del incremental normal, ya que inserta datos a partir de un valor anterior al valor máximo del campo incremental en el dataset. Esta actualización solo se permite utilizar un campo incremental de tipo fecha: la "ventana" es el intervalo que el usuario indica por configuración. Todos los datos del dataset dentro de la ventana se borran, y se lanza la consulta SQL con una condición que da como resultado datos a partir de la fecha inicial de la ventana, lo que incluye registros con fechas ya cargadas anteriormente y con fechas nuevas no cargadas (como el incremental normal). Los registros del dataset anteriores al inicio de la ventana se dejan intactos.

Este tipo de actualización da respuesta al caso de uso en el que, sin querer hacer una actualización completa, es necesario cargar registros nuevos y también modificar algunos registros recientes, sin entrar en el detalle de cuales son. Situaciones así se dan en la práctica con frecuencia: datos contables en un ejercicio fiscal abierto que se corrigen, importes de venta que se modifican por descuentos aplicados, etc.

La configuración de este tipo de refesco se divide en 2 partes:

La parte incremental procede de forma similar al incremental normal: precisa indicar el campo incremental e insertar en la parte where de la consulta SQL el texto $condition$, que será entendido como una variable y Vision sustituirá por la expresión campo_incremental >= window_start_value.
Se indica como determinar la fecha inicial de la ventana, que será una fecha anterior a la fecha actual window_start_value = today - X "time units". Para determinar X y time units se dispone de 3 opciones diferentes:
- Periodo actual: la ventana es un periodo dinámico que abarca el intervalo temporal "en curso" correspondiente a la unidad seleccionada, y que puede tomar los valores años, meses, semanas, días, horas o minutos.Por ejemplo, si se elige la unidad “años” el inicio del intervalo comienza el 1 de enero del año actual, si se elige "mes" el 1 del mes en curso, etc.
- Intervalo exacto: la ventana es un intervalo temporal fijo tal cual indica el número de periodos y la unidad temporal.Por ejemplo, si se indica "2 semanas" y se ejecuta la actualización un "jueves" la ventana tiene inicio el jueves de hace 2 semanas.
- Intervalo completo: esta opción es una combinación del periodo actual y del intervalo exacto, ya que "redondea" el inicio de la ventana al intervalo completo de la unidad seleccionada.Por ejemplo, si se indica "1 año" la ventana comienza el 1 de enero del año anterior al actual (se va un año hacia el pasado y se redondea conforme a la unidad temporal), si se indica "0 meses" el intervalo comienza el día 1 del mes actual, si se indica "2 semanas" el intervalo comienza el domingo de hace 2 semanas (la semana en Vision comienza el domingo y termina el sábado).

Configuración de un incremental Ventana

Incremental Últimos Cambios

El tipo de actualización incremental Últimos cambios deja intacta una parte de los datos presentes, actualiza (o borra) otra parte de ellos, y también carga contenido nuevo. La configuración de este tipo de refesco se divide en 3 partes:

Campo incremental: es la columna de una tabla o el alias de un campo presente en la consulta SQL (y por tanto el "alias de BBDD o Fieldname" del campo en el dataset) que se va a usar en la condición incremental, generalmente la fecha de creación/actualización de cada registro.
Se precisa definir una clave primaria (PK) del registro. En el cuadro de texto correspondiente se escriben los nombres de los campos que forman esa PK (hay que pulsar Enter para habilitar cada campo escrito). En el proceso de actualización, para cada registro que llega como resultado de la consulta se busca el registro equivalente en el dataset comparando por la PK: si se encuentra se actualiza por el nuevo, y si no se encuentra se inserta tal cual ya que es un registro "nuevo".
La condición incremental se añade a la sentencia SQL insertando en la parte where de la consulta un texto especial: $condition$. Vision entiende este texto como una variable, al ejecutar la consulta, resuelve primero el valor de $last_value$ y sustituye $condition$ por campo_incremental > last_value . Teniendo esto en cuenta, el usuario ha de escribir una consulta SQL válida, insertando la variable $condition$ correctamente en la clausula where de la consulta.
El proceso permite no solo actualizar datos sino también borrar registros. Para ello se ha de incluir en la consulta SQL un campo que obligatoriamente se debe llamar "_delete": cuando el valor del campo es "true" y la PK del registro coincide con la PK de una fila del dataset ésta se elimina. No obstante, esta parte no es obligatoria: si no se incluye simplemente no se borra ningún registro.

Configuración de un incremental Últimos cambios

Es importante notar que este tipo de actualización precisa tener cierta lógica en la gestión y estructura de la información en base de datos:

La consulta SQL debe incluir, preferiblemente, solo registros "nuevos" o que se van a modificar. En ese sentido es un proceso "casi" incremental, es decir, se recomienda no incluir información de registros ya existentes que no han cambiado, ya que se estaría trasladando información innecesaria entre la base de datos origen y Vision, y en Vision se estarían realizando comparaciones por PK inútiles, ya que el contenido queda igual que estaba. Lo habitual para poder usar este tipo de actualización es tener informada la fecha de cuando se ha hecho el registro de la información (creación o actualización), y por tanto el campo incremental será esa fecha de "control", y no la fecha asociada a otro evento presente en el dataset (una venta, una factura, etc.).
El proceso de borrado se realiza en Vision a partir de la información que llega en la consulta SQL. En este sentido, en la base de datos origen se debe realizar un borrado lógico de los registros, es decir, marcar los registros como eliminados en lugar de hacer un borrado físico, ya que de no ser así esos registros no formarían parte del resultado de la consulta SQL! Si no es posible evitar el borrado físico una posible solución es, previo al borrado, copiar los registros que se van a eliminar a una tabla auxiliar. De esta forma en la consulta SQL ya se pueden incluir esos registros, por ejemplo mediante un join a la tabla auxiliar.

Histórico de Datos

Los datasets con actualización incremental permiten configurar un borrado de datos históricos. En muchas ocasiones los datos de periodos pasados no aportan demasiado valor en los dashboards, pero pueden repercutir negativamente en el rendimiento de las visualizaciones, ya que pueden aumentar considerablemente el volumen de los datasets que los widgets usan como origen de datos.

Para realizar este borrado se define la ventana temporal de los registros que se mantienen. Una vez configurada esta opción, Vision se encarga de eliminar automáticamente los datos anteriores a esa ventana: en cada actualización de datos se añade a la inserción un segundo proceso que procede a la eliminación de esos registros "antiguos".

La configuración del borrado de datos históricos consta de 2 partes:

Campo para borrado de datos: este es el nombre del campo en el dataset, y obligatóriamente ha de ser de tipo fecha para poder generar la ventana temporal.
Se indica como determinar la fecha inicial de la ventana, que será una fecha anterior a la fecha actual window_start_value = today - X "time units". La unidad temporal puede ser años, meses, semanas, días, horas o minutos, mientras que el número de periodos X debe ser un entero mayor o igual que 0. Además, la fecha se redondea conforme a la unidad temporal.Por ejemplo "0 años" conserva datos a partir del año actual (borra registros con fecha previa al 1 de enero del año en curso), "2 months" conserva datos del mes actual y los 2 anteriores (borra datos con fecha anterior al día 1 de hace 2 meses), etc.

Configuración del borrado de datos históricos

Bookmark or share this article

Esta página foi útil?

Obrigado pelo seu voto.

login para deixar a sua opinião.

Obrigado pelo seu feedback. Iremos analisá-lo para continuarmos a melhorar!