La sentencia synchronize se ha ampliado con opciones para incluir lógica personalizada escrita en Invantive PSQL. La lógica personalizada en PSQL se ejecuta cuando se dispara un evento a partir de insert, update y delete. Se puede especificar lógica personalizada de disparador (trigger) por separado para ejecutarse antes y después del evento, además de en lugar del evento.
El uso de lógica personalizada para disparar eventos tiene las siguientes ventajas en escenarios complejos de sincronización o integración de datos, aumentando la usabilidad como plataforma integral de integración para datos empresariales:
- facilita las tareas de integración de datos,
- reduce la cantidad de trabajo necesario para tareas de sincronización complejas
- aumenta la capacidad de servicio, auditabilidad y fiabilidad.
La nueva funcionalidad está disponible a partir de la versión 22.1 BETA y, en el futuro, en la próxima versión de producción.
Funcionalidad de sincronización existente
La funcionalidad de sincronización de datos de synchronize de la versión 22.0 se mantiene totalmente operativa y con soporte.
Usando synchronize, una única sentencia SQL puede comparar y alinear de forma unidireccional o bidireccional el contenido de dos tablas. Esta alineación mediante sincronización de datos garantiza que los valores de la tabla para columnas con el mismo nombre coincidan (permitiendo pequeñas diferencias en el formato de datos, como long y short). Hay ejemplos de sincronización de datos disponibles en:
- Synchronise your Data with one Statement across multiple Applications
- Synchronize Exact Online with Visma.net Financials
Las sentencias existentes seguirán sincronizando los datos de forma idéntica sin cambios en el código. Synchronize sigue siendo la solución preferida para la sincronización de datos por lotes. Utilice los demás medios de Invantive SQL para la sincronización de datos en tiempo real, como Invantive App Online, que captura eventos de webhook y los traduce en acciones sobre tablas.
Retos de la sincronización de datos
Sincronizar datos es un concepto potente para la integración de datos, con un valor empresarial muy apreciado. Sin embargo, el proceso de sincronización de datos puede plantear requisitos adicionales más allá de la funcionalidad de synchronize de la versión 22.0, como tablas diferentes para leer y escribir, una opción de simulación (dry-run) y/o auditoría de acciones.
Tablas diferentes para leer y escribir
Un reto típico para las herramientas de sincronización de datos se produce cuando la tabla (o API) que debe adaptarse es distinta de la tabla utilizada para comparar los datos. Por ejemplo, algunas plataformas tienen tablas (relativamente lentas) que permiten la inserción, mientras que la comparación puede usar una versión optimizada de solo lectura. Un ejemplo popular de esto son las tablas de transacciones de Exact Online; mientras que las tablas (de solo lectura) *Incremental devuelven fácilmente grandes volúmenes de datos, como millones de transacciones, en cuestión de minutos, la versión de lectura/escritura está limitada a un máximo de 60 filas por segundo y un uso acumulado de lectura de como máximo 300.000 filas al día.
Usando lógica personalizada para disparar eventos, la sincronización puede leer de una tabla incremental como AccountsIncremental y realizar los cambios a través de la (más lenta) tabla Accounts. Esto se ejecutará hasta cientos de veces más rápido, al tiempo que reduce la carga y aumenta la escalabilidad en entornos grandes.
Por supuesto, en este escenario se asume que ambas tablas contienen, con el tiempo, datos cada vez más idénticos (y, por supuesto, los mismos datos justo después de que finalice la sincronización). La reducción del número de cambios necesarios disminuye en gran medida el tiempo de ejecución amortizado gracias a la lógica personalizada.
Simulación (dry-run) y auditoría de acciones
Otro reto para las herramientas de sincronización de datos empresariales es que la sincronización se ejecute correctamente, pero que, dada la complejidad del proceso de sincronización, sea difícil auditar el proceso real. O, peor aún, a veces la lista de cambios debe aprobarse antes de que pueda realizarse la sincronización real.
Usando código personalizado para sincronizar datos, los cambios pueden mostrarse de antemano antes de ejecutarse, y se puede registrar fácilmente un registro de auditoría de los cambios reales, incluso cuando la plataforma de destino carece de funciones de auditoría.
Ejemplo de lógica personalizada
La sentencia synchronize sincroniza la tabla de origen con la tabla de destino, usando lógica personalizada para todos los eventos de disparo. Hay nueve posibles disparadores en una sincronización, concretamente cada combinación de:
- evento de cambio insert, update y delete,
- momento de ejecución before (antes), instead of (en lugar de) y after (después) del evento de cambio.
Al final de la sentencia synchronize los disparadores se especifican después de la palabra clave triggers como el momento de ejecución y el evento de cambio. Por ejemplo, la siguiente lógica personalizada se ejecuta antes de cada inserción:
synchronize
...
triggers
before insert
begin
... code ...
end;
El código personalizado puede hacer referencia a los valores antiguos y nuevos utilizando los prefijos :old y :new respectivamente. Tenga en cuenta que :old y :new dependen de la dirección de la sincronización. Especialmente en la sincronización bidireccional, la tabla de origen y la de destino pueden diferir continuamente en la lista de columnas. Las columnas no disponibles en el origen o destino seleccionado con las referencias :old y :new se evaluarán como null.
Por ejemplo, cuando una tabla contiene la columna name, el siguiente código imprimirá las modificaciones del nombre:
synchronize
...
triggers
before update
begin
if ( :old.name is null and :new.name is not null )
or
( :old.name is not null and :new.name is null )
or
:old.name != :new.name
then
dbms_output.put_line('Name is changed from ' || :old.name || ' to ' || :new.name || '.');
end if;
end;
Ejemplo de lógica personalizada
El siguiente ejemplo sincroniza el origen con la tabla de destino usando disparadores. Cree y rellene primero las tablas tal como se muestra al final de esta publicación antes de sincronizar los datos.
El resultado de la ejecución en este caso es siempre:
before insert on ID A
instead of insert on ID A: 5
after insert on ID A
before update on ID B
instead of update on ID B: 4 → 7
after update on ID B
Sentencia de sincronización de datos:
sync source@inmemorystorage
to target@inmemorystorage
with insert or update or delete
identified
by dummy_string
triggers
before insert
begin
dbms_output.put_line('before insert on ID ' || :new.dummy_string);
end
instead of insert
begin
dbms_output.put_line('instead of insert on ID ' || :new.dummy_string || ': ' || :new.dummy_int32);
end
after insert
begin
dbms_output.put_line('after insert on ID ' || :new.dummy_string);
end
before update
begin
dbms_output.put_line('before update on ID ' || :new.dummy_string);
end
instead of update
begin
dbms_output.put_line('instead of update on ID ' || :new.dummy_string || ': ' || :old.dummy_int32 || ' -> ' || :new.dummy_int32);
end
after update
begin
dbms_output.put_line('after update on ID ' || :new.dummy_string);
end
before delete
begin
dbms_output.put_line('before delete on ID ' || :old.dummy_string);
end
instead of delete
begin
dbms_output.put_line('instead of delete on ID ' || :old.dummy_string);
end
after delete
begin
dbms_output.put_line('after delete on ID ' || :old.dummy_string);
end
Ejemplo de creación de tablas
Como preparación para la sincronización, el siguiente código SQL crea dos tablas (source y target) con el siguiente contenido.
Tabla Source
| dummy_string | dummy_int32 | dummy_datetime |
|---|---|---|
| A | 5 | now |
| B | 7 | now |
| C | 9 | now |
Tabla Target
| dummy_string | dummy_int32 |
|---|---|
| B | 4 |
| C | 9 |
Código de creación de tablas
create or replace table source@inmemorystorage
as
select 5 dummy_int32
, now() dummy_datetime
, 'A' dummy_string
union all
select 7 dummy_int32
, now() dummy_datetime
, 'B' dummy_string
union all
select 9 dummy_int32
, now() dummy_datetime
, 'C' dummy_string
create or replace table target@inmemorystorage
as
select 'B' dummy_string
, 4 dummy_int32
union all
select 'C' dummy_string
, 9 dummy_int32
Carga masiva (bulk loading)
En general, los disparadores de lógica personalizada se llamarán por cada fila, de modo que, en caso de que haya dos filas que actualizar, el orden de llamada será:
- before update fila 1
- (instead of) update fila 1
- after update fila 1
- before update fila 2
- (instead of) update fila 2
- after update fila 2
Sin embargo, Invantive SQL proporciona compatibilidad con carga masiva en varias plataformas, como SQL Server, PostgreSQL y Oracle RDBMS (controlador nativo). En ese caso, las inserciones se agrupan en lotes. El tamaño de cada lote se puede especificar usando la palabra clave batchsize (documentación).
Cada lote se procesa normalmente como una unidad indivisible. Sin embargo, no se garantiza que sea una transacción atómica. En caso de fallo, una o más filas del lote no se cargarán correctamente y otras sí. En general, es imposible determinar cuáles fallaron y/o son válidas sin volver a leer el contenido real de la base de datos.
Cuando no se usa ningún disparador instead of y se realiza carga masiva con un tamaño de lote superior a 1, el orden de llamada para dos filas que se van a insertar será:
- before insert fila 1
- before insert fila 2
- bulk insert filas 1 y 2
- after insert fila 1
- after insert fila 2
Cuando hay más filas para cargar de las que permite el tamaño del lote, el orden de llamada será el mismo, pero solo para las filas de un lote. Los lotes siguientes repetirán el orden desde el principio con filas diferentes.