Incremental partitioned reload – Part II

In the first part of this article we saw what it was an incremental load and how it’s done using a technique that meets most of the situations that we can find.

But as I said there are cases where the volume of data being handled is so large (on the order of terabytes) than simply reading and / or recording involved QVD file can take a considerable amount of time and server resources, being that maybe we just need add a few news to it.

To avoid this situation what we should do is to split this file into several fragments which each contain a set of rows grouped by some criteria. One approach that is often used is the month-year of a record’s field, thereby generating QVDs repositories that looks something like this:

                                 Extraction_Customers_CreateDate_012013.QVD
                                 Extraction_Customers_CreateDate_022013.QVD
                                 Extraction_Customers_CreateDate_032013.QVD
                                 Etc….

Where a fixed part of the file name describes the content/source/stage and a variable part describes the period (month-year) corresponding to the records in each file, in this case the customer’s creation Date.

Although we can not always have a date we can always count on a numeric field by which to classify and group records, either the Id field or other unique identifier that can be created using the Autonumber() or Hash…() functions.

The important thing here is that the field you choose must meet the following characteristics:

      – MUST be numeric: to define ranges or segments.
      – NEVER change value over time: in order to locate in what QVD file is stored as a record.
      – Should have a statistical distribution close to random (ie the class frequencies should tend to be equal)
      – As a consequence of this should not have empty ranges: using the example customer’s creation date this means that customers should be created EVERY month in a continuous way.

The last two points are not mandatory, but rather a recommendation for best performance.

In general the same unique identifier used to perform incremental load is an excellent (if not the best) candidate field to partition the QVD, because at the source (transactional) system it is usually numeric, time-invariant (PK), and auto-increment (1 to 1).

To improve readability of the code we will use procedures (Subs) operating similar to a function or routine other languages. Here’s the code of the procedure commented which should be saved in a file called “incrementalPartitionedReload.txt” that must be in the same folder as the qvw on which we are working:
 
sub loadIncrementalPartitioned (V_chunck,V_KEY,V_QVDname,V_SQLquery,V_incrementalWhere,V_fieldsLoaded,V_fieldsTransformations)

     Set TimestampFormat=’YYYY-MM-DD hh:mm:ss[.fff]‘;
    
LET TABLA_SOURCE=’DATA_SOURCE’;
    
    
// set the variable used to recover ONLY the news (since last execution)
     LET v_CompareDate_1 = DATE(TODAY()-1,’YYYY-MM-DD’);
    
    
// Check if the first full load has been done
     IF NOT ISNULL(QvdCreateTime(‘$(_QVDsPath)$(V_QVDname)_incrementalReload_.QVD’)) THEN
    
        
//Extract data from datasource
           pre_$(TABLA_SOURCE):
          
LOAD $(V_fieldsLoaded);
          
SQL $(V_SQLquery)
          
$(V_incrementalWhere);
          
          
//for each row calculate the corresponding patition and make transformations (if any)
           $(TABLA_SOURCE):
          
NoConcatenate
          
LOAD *,
               
floor($(V_KEY)/$(V_chunck)) as TARGET_QVD
               
$(V_fieldsTransformations)
           RESIDENT pre_
$(TABLA_SOURCE);  
          
DROP TABLE pre_$(TABLA_SOURCE);
           //Fill a little table which will be used to build the affected (if already exist) QVDs files names, each one containing upto V_chunk rows        
           AffectedQVDs:
          
LOAD DISTINCT TARGET_QVD
          
RESIDENT $(TABLA_SOURCE);
          
          
// get the quantity of QVDs that will be used
           LET rows = NoOfRows(‘AffectedQVDs’);
    
          
FOR row=1 to rows
              
// assembles QVD file name
                let txtQVD =’[' & (FieldValue('TARGET_QVD',row) * V_chunck) &';'& ((FieldValue('TARGET_QVD',row)+1) * V_chunck - 1) & ']‘;
               
// If the QVD file already exists add the news to this, discarding the old versions of same records
                IF NOT ISNULL(QvdCreateTime(‘$(_QVDsPath)$(V_QVDname)=$(txtQVD).QVD’)) THEN         
                         
CONCATENATE ($(TABLA_SOURCE))
                         
$(TABLA_SOURCE):
                         
LOAD *
                         
FROM $(_QVDsPath)$(V_QVDname)=$(txtQVD).QVD (QVD
                         
WHERE NOT EXISTS (PRIMARY_KEY);
               
ENDIF     
    
          
NEXT
    
    
ELSE
    
// Is the first run of this script. It’s necessary to do a full SQL extraction
    
           pre_
$(TABLA_SOURCE):
          
LOAD $(V_fieldsLoaded);
          
SQL $(V_SQLquery);
          
    
          
//for each row calculate the corresponding patition and make field transformations (if any)
           $(TABLA_SOURCE):
          
NoConcatenate
          
LOAD *,
               
floor($(V_KEY)/$(V_chunck)) as TARGET_QVD
               
$(V_fieldsTransformations)
           RESIDENT pre_
$(TABLA_SOURCE);  
          
DROP TABLE pre_$(TABLA_SOURCE);
    
          
//will store a dummy file just for signal that the first full load has been done
           flagFile:
          
NoConcatenate
          
LOAD 1 as dummy
          
AutoGenerate 1;
          
STORE flagFile INTO $(_QVDsPath)$(V_QVDname)_incrementalReload_.QVD;
          
DROP TABLE flagFile;
    
    
END IF
    
    
//Recover the max and min value of the field used to partition
     MAXI_MINI:
    
LOAD min($(V_KEY)) as minPK,
          
max($(V_KEY)) as maxPK
    
RESIDENT $(TABLA_SOURCE);
    
    
    
LET maxPK=round(Peek(‘maxPK’));
    
LET minPK=round(Peek(‘minPK’));
    
// calculates the number of partitions
     LET partitions=ceil(maxPK/V_chunck);
    
    
LET V_I=1;
    
    
    
do while V_I<= partitions
          
if (V_I*V_chunck>=minPK) then
               
// extract the chunk for each partition        
                toStore:
               
NoConcatenate
               
LOAD *
               
RESIDENT $(TABLA_SOURCE)
               
WHERE $(V_KEY)>= ($(V_I)-1)*$(V_chunck) and $(V_KEY)<$(V_I)*$(V_chunck);
               
               
LET q = NoOfRows(‘toStore’);
               
               
//verify if the partition is not empty
                IF q>0 then
                    
Let since=(V_I-1)*V_chunck ;
                    
Let upto=(V_I*V_chunck)-1;
                    
// store the partition, using an assembled file name with information about the content
                     STORE toStore INTO $(_QVDsPath)$(V_QVDname)=[$(since);$(upto)].QVD;
               
ENDIF
               
               
DROP TABLE toStore;
          
ENDIF
          
let V_I=V_I+1;
    
loop
    
drop table $(TABLA_SOURCE);
    
    
// prepare variable to detect the news in the next run
     LET v_CompareDate_2 = ‘$(v_CompareDate_1)’;
end Sub

and finally we complete in the qvw script our extractor, in this case for the Customers table:

 //////////////LIBRARIES/////////////////////////
// Load library
$(Include=.\incrementalPartitionedReload.txt);
//////////////END LIBRARIES////////////////////

CONNECT TO [...YOUR DB CONNECTION STRING....];
// Quantity of IDs in each QVD’s partition, change to build bigger partitions and therefore more or less QVDs
LET _chunk = 500;
// Unique ID used to patitioning and insert the news
LET _Key=’IDCUSTOMER’;
// First part of  QVDs file name
LET _QVDname=’Extract_CUSTOMER_’ & _Key;
// Extraction SQL query
SET _SQLquery=SELECT * FROM CUSTOMERS;
// WHERE clause which detects the news (inserts + updates)
SET _incrementalWhere= WHERE LASTUPDATE >= TO_DATE(‘$(v_CompareDate_2)’,’YYYY-MM-DD’);
// Datasource fields read and load to QV
SET _FieldsLoaded= IDCUSTOMER, NAME, STREET, NUMBER, CITY, LASTUPDATE;
// Optional: filds tranformations
SET _transformFields= ,CITY & ‘,’ & STREET & ‘ ‘ & NUMBER AS FULL_ADDRESS; // Could be empty – Optional, but if it is must begin with “,”
// QVDs path
SET _QVDsPath =..\Qvd\;
// call the incremental partitioned load:
call loadIncrementalPartitioned(_chunk,_Key, _QVDname, _SQLquery, _incrementalWhere, _FieldsLoaded, _transformFields);

 

Substituting the values ​​of the variables _chunk, _Key, _QVDname, _SQLquery, _incrementalWhere, _FieldsLoaded, _QVDsPath and _transformFields can easily adapt and create incremental scripts to load partitioned.

For example, with _chunk = 500 we get something like this:

QVDS Partitioned

Last considerations:

 - The first time the script is run in addition to a full extraction of the datasource and save QVDs with data, it saves a “dummy” QVD file which is only used to indicate (flag) that the first run was already done. If this file is deleted the script will return to repeat a full reloading.

 - The _chunk variable determines the number of rows in each QVD and QVDs quantity. The number varies depending on the number of rows in the table and if there are “holes” (discontinuities) in the numbering.

- If we do not want partition the table but still keep doing incremental reloading must only define a value _chunk large enough so that all Ids enter into the single partition that will be created.

 - Finally to read and use QVDs created in the layer extraction in our intermediate layer (or presentation depending on the application architecture) we just make use of wildcar “*” to load all the parts as if they were one. P.Ej:

 LOAD *
FROM
[D:\Ventas\Qvd\Extraction_Customer_IDCUSTOMER=[*]].QVD]
(qvd);

Recarga Incremental Particionada – Parte II

En la primera parte de este artículo vimos que era y como se hace una carga incremental empleando una técnica que satisface la mayoría de las situaciones con que nos podemos encontrar.

Pero como dije anteriormente hay casos donde el volumen de datos que se maneja es de tal magnitud (en el orden de TeraBytes) que la simple lectura y/o grabación del archivo QVD involucrado puede tomar una cantidad considerable de tiempo y recursos del servidor, siendo que quizás solo debemos agregar unas pocas novedades al mismo.

Para evitar esta situación lo que debemos hacer es partir este archivo en varios fragmentos donde cada uno de ellos contenga un conjunto de filas agrupadas por algún criterio. Un criterio que se suele emplear es el mes-año de algún campo del registro, generando de este modo repositorios de QVDs que se ven mas o menos así:

                                 Extraction_Customers_CreateDate_012013.QVD
                                 Extraction_Customers_CreateDate_022013.QVD
                                 Extraction_Customers_CreateDate_032013.QVD
                                 Etc….

Donde una parte fija del nombre del archivo describe el contenido/origen/etapa y otra parte variable describe el periodo (mes-año) al que corresponden los registros contenidos en cada archivo, en este caso la fecha de alta (Creation Date) del Cliente.

Aunque no siempre podemos contar con una fecha siempre podremos contar con un campo numérico por el cual clasificar y agrupar los registros, ya sea el identificador único u otro campo que podamos crear usando las funciones Autonumber() o Hash…().

Lo importante es que el campo que elijamos debe cumplir con las siguientes características:

     -     DEBE ser numérico: para poder definir rangos o segmentos.
     -     NUNCA debe cambiar de valor a lo largo del tiempo: para poder ubicar en que archivo QVD está almacenado un registro dado.
     -     Debería tener una distribución cercana a random (es decir que las frecuencias de clase deben tender a ser iguales)
     -     Y como una consecuencia de lo anterior no debería tener rangos vacíos: usando el ejemplo de fecha de creación del cliente esto implica que se deberían crear clientes TODOS los meses.

Los dos últimos puntos no son una restricción absoluta, sino más bien una recomendación para obtener un mejor rendimiento.

En general el mismo identificador único empleado para realizar la carga incremental es un excelente (sino el mejor) campo candidato para particionar el QVD, debido a que en su sistema origen suele ser del tipo numérico, invariable en el tiempo (PK), único y autoincremental (de 1 en 1).

Para mejorar la lectura del código vamos a usar procedimientos (Subs) que funcionan semejantes a una función o rutina de otros lenguajes. A continuación va el código del procedimiento comentado, el cual debería ser guardado en un archivo llamado “incrementalPartitionedReload.txt“  que debe estar en la misma carpeta que el qvw sobre el que estamos trabajando:
 
sub loadIncrementalPartitioned (V_chunck,V_KEY,V_QVDname,V_SQLquery,V_incrementalWhere,V_fieldsLoaded,V_fieldsTransformations)

     Set TimestampFormat=’YYYY-MM-DD hh:mm:ss[.fff]‘;
    
LET TABLA_SOURCE=’DATA_SOURCE’;
    
    
// set the variable used to recover ONLY the news (since last execution)
     LET v_CompareDate_1 = DATE(TODAY()-1,’YYYY-MM-DD’);
    
    
// Check if the first full load has been done
     IF NOT ISNULL(QvdCreateTime(‘$(_QVDsPath)$(V_QVDname)_incrementalReload_.QVD’)) THEN
    
        
//Extract data from datasource
           pre_$(TABLA_SOURCE):
          
LOAD $(V_fieldsLoaded);
          
SQL $(V_SQLquery)
          
$(V_incrementalWhere);
          
          
//for each row calculate the corresponding patition and make transformations (if any)
           $(TABLA_SOURCE):
          
NoConcatenate
          
LOAD *,
               
floor($(V_KEY)/$(V_chunck)) as TARGET_QVD
               
$(V_fieldsTransformations)
           RESIDENT pre_
$(TABLA_SOURCE);  
          
DROP TABLE pre_$(TABLA_SOURCE);
           //Fill a little table which will be used to build the affected (if already exist) QVDs files names, each one containing upto V_chunk rows        
           AffectedQVDs:
          
LOAD DISTINCT TARGET_QVD
          
RESIDENT $(TABLA_SOURCE);
          
          
// get the quantity of QVDs that will be used
           LET rows = NoOfRows(‘AffectedQVDs’);
    
          
FOR row=1 to rows
              
// assembles QVD file name
                let txtQVD =’[' & (FieldValue('TARGET_QVD',row) * V_chunck) &';'& ((FieldValue('TARGET_QVD',row)+1) * V_chunck - 1) & ']‘;
               
// If the QVD file already exists add the news to this, discarding the old versions of same records
                IF NOT ISNULL(QvdCreateTime(‘$(_QVDsPath)$(V_QVDname)=$(txtQVD).QVD’)) THEN         
                         
CONCATENATE ($(TABLA_SOURCE))
                         
$(TABLA_SOURCE):
                         
LOAD *
                         
FROM $(_QVDsPath)$(V_QVDname)=$(txtQVD).QVD (QVD
                         
WHERE NOT EXISTS (PRIMARY_KEY);
               
ENDIF     
    
          
NEXT
    
    
ELSE
    
// Is the first run of this script. It’s necessary to do a full SQL extraction
    
           pre_
$(TABLA_SOURCE):
          
LOAD $(V_fieldsLoaded);
          
SQL $(V_SQLquery);
          
    
          
//for each row calculate the corresponding patition and make field transformations (if any)
           $(TABLA_SOURCE):
          
NoConcatenate
          
LOAD *,
               
floor($(V_KEY)/$(V_chunck)) as TARGET_QVD
               
$(V_fieldsTransformations)
           RESIDENT pre_
$(TABLA_SOURCE);  
          
DROP TABLE pre_$(TABLA_SOURCE);
    
          
//will store a dummy file just for signal that the first full load has been done
           flagFile:
          
NoConcatenate
          
LOAD 1 as dummy
          
AutoGenerate 1;
          
STORE flagFile INTO $(_QVDsPath)$(V_QVDname)_incrementalReload_.QVD;
          
DROP TABLE flagFile;
    
    
END IF
    
    
//Recover the max and min value of the field used to partition
     MAXI_MINI:
    
LOAD min($(V_KEY)) as minPK,
          
max($(V_KEY)) as maxPK
    
RESIDENT $(TABLA_SOURCE);
    
    
    
LET maxPK=round(Peek(‘maxPK’));
    
LET minPK=round(Peek(‘minPK’));
    
// calculates the number of partitions
     LET partitions=ceil(maxPK/V_chunck);
    
    
LET V_I=1;
    
    
    
do while V_I<= partitions
          
if (V_I*V_chunck>=minPK) then
               
// extract the chunk for each partition        
                toStore:
               
NoConcatenate
               
LOAD *
               
RESIDENT $(TABLA_SOURCE)
               
WHERE $(V_KEY)>= ($(V_I)-1)*$(V_chunck) and $(V_KEY)<$(V_I)*$(V_chunck);
               
               
LET q = NoOfRows(‘toStore’);
               
               
//verify if the partition is not empty
                IF q>0 then
                    
Let since=(V_I-1)*V_chunck ;
                    
Let upto=(V_I*V_chunck)-1;
                    
// store the partition, using an assembled file name with information about the content
                     STORE toStore INTO $(_QVDsPath)$(V_QVDname)=[$(since);$(upto)].QVD;
               
ENDIF
               
               
DROP TABLE toStore;
          
ENDIF
          
let V_I=V_I+1;
    
loop
    
drop table $(TABLA_SOURCE);
    
    
// prepare variable to detect the news in the next run
     LET v_CompareDate_2 = ‘$(v_CompareDate_1)’;
end Sub

y por último completamos en el qvw el script de nuestro extractor, en este caso para la tabla Customers:

 //////////////LIBRARIES/////////////////////////
// Load library
$(Include=.\incrementalPartitionedReload.txt);
//////////////END LIBRARIES////////////////////

CONNECT TO [...YOUR DB CONNECTION STRING....];
// cantidad de IDS en cada QVD particionado, cambiar para hacer particiones
// mas grandes o pequeñas, y por lo tanto
// mayor o menor cantidad de archivos QVDs
LET _chunk = 500;
// ID unico usado para particionar e ingresar las novedades
LET _Key=’IDCUSTOMER’;
// Primera parte del nombre que tendrán los QVDs
LET _QVDname=’Extract_CUSTOMER_’ & _Key;
// SQL que se ejecuta contra la BD
SET _SQLquery=SELECT * FROM CUSTOMERS;
// Clausula WHERE que detecta  las novedades (inserts + updates)
SET _incrementalWhere= WHERE LASTUPDATE >= TO_DATE(‘$(v_CompareDate_2)’,’YYYY-MM-DD’);
// Campos del datasource que son leidos y cargados
SET _FieldsLoaded= IDCUSTOMER, NAME, STREET, NUMBER, CITY, LASTUPDATE;
// Optativo: transformaciones sobre los campos de la misma tabla
SET _transformFields= ,CITY & ‘,’ & STREET & ‘ ‘ & NUMBER AS FULL_ADDRESS; // Could be empty – Optional, but if it is must begin with “,”
// Path donde serán guardados y leidos los QVDs
SET _QVDsPath =..\Qvd\;
// call the incremental partitioned load:
call loadIncrementalPartitioned(_chunk,_Key, _QVDname, _SQLquery, _incrementalWhere, _FieldsLoaded, _transformFields);

 

Sustituyendo los valores de las variables _chunk, _Key, _QVDname, _SQLquery, _incrementalWhere, _FieldsLoaded, _transformFields y _QVDsPath se puede adaptar y crear fácilmente scripts de carga incremental particionada.

Por ej, con _chunk=500 obtendremos algo como: QVDS Partitioned

Ultimas consideraciones:

-          La primera vez que se ejecuta el script además de hacer una extracción full del datasource y grabar los QVDs con los datos se grabará un QVD “dummy” que solo es utilizado para señalar que la primera ejecución ya fue realizada. Si este archivo es borrado se volverá a repetir una recarga full.

-          La variable _chunk determinará la cantidad de filas en cada QVD y la cantidad de QVDs. El número varía dependiendo de la cantidad de filas en la tabla y de si existen “huecos” (discontinuidades) en la numeración.

-          Si no queremos particionar la tabla pero si queremos seguir haciendo recarga incremental solo debemos definir un valor _chunk lo suficientemente grande de tal modo que todos los Ids entren en la única partición que será creada.

-          Por último para leer y usar los QVDs de  la capa de extracción en nuestra capa intermedia (o de presentación dependiendo de la arquitectura de la aplicación) solo debemos hace uso del wildcar “*” para cargar todas la partes como si fueran una sola. P.Ej:

 LOAD *
FROM
[D:\Ventas\Qvd\Extraction_Customer_IDCUSTOMER=[*]].QVD]
(qvd);

Incremental partitioned reload – Part I

In this article I’ll explain how to make one (usually daily) periodic incremental load and provide the code to perform incremental loads partitioned by any numeric field.

Basically an incremental load works as follows:

incrementalLoad

 This means that for the first time perform a full load and then just take each day’s  news of the previous day and then add to these news everything that was already stored in the QVD and that is NOT on these news.

For example if a client (ID = 99) on day X is created on data source, and his address declared as “Route 66 Mi 456″ that day will detect this row as a novelty, because it does not exist in the QVD, and therefore will be added to qvd. Later on X + N day customer moves to “Route 2 Mi 38″ and the process of incremental reload will detect the novelty and will add the rest of the QVD (other customers), discarding the data which had kept to the client 99 (Route 66 Mi 456) and instead keeping customer ID=99 data  that were recovered from the BD.

Achieve this requires that the table on which it will do incremental recharge meets two characteristics:

1-      It must has a unique identifier.

2-      It must have a field of type date or date-time where each time a record is created and / or modified in ANY way by the source system (transactional) this field is updated with the current date-time. This task is a commitment that must be agreed with the responsible source system.

To specify the explained and move forward here’s the code for a basic incremental recharge:

Set TimestampFormat=’YYYY-MM-DD hh:mm:ss[.fff]‘; //Spanish format

LET v_CompareDate_1 = DATE(TODAY()-1,’YYYY-MM-DD’); // Yesterday

//Checks if the QVD file exists for the first time
IF NOT ISNULL(QvdCreateTime(‘incrementalTable.QVD’)) THEN
//It is not the first time, because the QVD file exists

//Recover all the news (new records + updates since last load (v_CompareDate_2)

incrementalTable:
LOAD *; //If prefer can detail a list of fields
SQL SELECT *  //Can detail fields list
FROM sourceTable
WHERE LastUpdateDate >= TO_DATE(‘$(v_CompareDate_2)’,’YYYY-MM-DD’);

//Adds records stored en prvious days into QVD file and doesn’t exist as news
CONCATENATE (incrementalTable)
LOAD *
FROM incrementalTable.QVD (QVD)
WHERE NOT EXISTS (UNIQUEID);

ELSE
// first time running: the QVD file doesn’t exist

// So it makes a full load

incrementalTable:
LOAD *;
SQL SELECT * FROM sourceTable;

END IF

STORE incrementalTable incrementalTable.QVD;
DROP TABLE incrementalTable;

LET v_CompareDate_2 = ‘$(v_CompareDate_1)’;
 

We will highlight some points of the code:

1) How to detect the latest news?

We do read all of BD what happened after the last time ran this same script: “WHERE LastUpdateDate> = TO_DATE (‘$ (v_CompareDate_2)’, ‘YYYY-MM-DD’)”. This could be yesterday or two weeks ago, it does not matter because the date of the last run (less one day) was stored in “v_CompareDate_2″ variable (remember that QV variables are persistent over time).

2) How to avoid loading new rows that had been previously loaded (duplicity)?

If the QVD had rows stored already present among the news we will keep these news and discard what was stored: “WHERE NOT EXISTS (UNIQUEID)”

3) How the join the news with other previously stored data?

Concatenating two sets (news + “legacy” data): “CONCATENATE (incrementalTable)”

4) How “prepare” the script for the next run? Seting the value of  “v_CompareDate_2″ to “v_CompareDate_1″:   “LET v_CompareDate_2 = ‘$ (v_CompareDate_1)’”

Thus we have a script able to load an entire table in his first run and after that just extract and store the news replacing the old versions of these records if any.

However although this technique optimizes BD reading and transfer of data it has a performance issue:  if the resulting QVD is truly large (> 1 or 2 TByte) ,  every time it runs reads and saves a very large file to disk, so even with fairly powerful servers it can take on the order of 10-20 minutes, doing heavy execution and consuming valuable time of an operating window that in many organizations is often narrowed to a few dawn hours.

 In the second part of this article I will explain how to modify and package this code to read and write only small portions of QVD (actually many small QVDs), optimizing the time and resource disk access.

Recarga incremental particionada – Parte I

En este artículo voy explicar cómo realizar una carga incremental peródica (habitualmente diaria) y proveer el código para realizar cargas incrementales particionadas por cualquier campo numérico.

Básicamente una carga incremental funciona del siguiente modo:

incrementalLoad

 

Es decir que por primera vez realizamos una carga completa y luego cada día solo tomamos las novedades del día anterior y a estas novedades le agregamos todo lo que ya estaba almacenado en el QVD y que NO está en dichas novedades.

A modo de ejemplo si el día X el cliente (ID=99)  se da de alta y declara como dirección “Ruta 66 Km 456” ese día se va a detectar esta fila como una novedad, debido a que no existe en el QVD, y se va a agregar a dicho QVD. Posteriormente el día X+N el cliente se muda a “Ruta 2 Km 38” y el proceso de recarga incremental va a detectar la novedad y la va a agregar al resto del archivo QVD (otros clientes) DESCARTANDO por completo de este archivo lo que había guardado para el cliente 99 (Ruta 66 Km 456) y en su lugar guardando los datos del cliente 99 que se recuperaron de la BD.

Para hacer esto se requiere que la tabla sobre la cual se va a hacer la recarga incremental cumpla con 2 características:

1-      Debe tener un identificador único.

2-      Debe tener un campo de tipo fecha o fecha-hora donde cada vez que un registro es creado y/o modificado en CUALQUIER modo por el sistema origen (transaccional) este campo se actualice con la fecha hora del momento. Esta tarea es un compromiso que debe pactarse con los responsables del sistema origen.

Para concretar lo explicado y seguir avanzando aquí va el código de una recarga incremental básica:

Set TimestampFormat=’YYYY-MM-DD hh:mm:ss[.fff]‘; //Spanish format

LET v_CompareDate_1 = DATE(TODAY()-1,’YYYY-MM-DD’); // Yesterday

//Checks if the QVD file exists for the first time
IF NOT ISNULL(QvdCreateTime(‘incrementalTable.QVD’)) THEN
//It is not the first time, because the QVD file exists

//Recove all the news (new records + updates since last load (v_CompareDate_2)

incrementalTable:
LOAD *; //If prefer can detail a list of fields
SQL SELECT *  //Can detail fields list
FROM sourceTable
WHERE LastUpdateDate >= TO_DATE(‘$(v_CompareDate_2)’,’YYYY-MM-DD’);

//Adds records stored en prvious days into QVD file and doesn’t exist as news
CONCATENATE (incrementalTable)
LOAD *
FROM incrementalTable.QVD (QVD)
WHERE NOT EXISTS (UNIQUEID);

ELSE
// first time running: the QVD file doesn’t exist

// So it makes a full load

incrementalTable:
LOAD *;
SQL SELECT * FROM sourceTable;

END IF

STORE incrementalTable incrementalTable.QVD;
DROP TABLE incrementalTable;

LET v_CompareDate_2 = ‘$(v_CompareDate_1)’;
 

 Vamos a destacar algunos puntos del código:

1)      ¿Cómo detectamos las novedades?

Lo hacemos leyendo de la BD todo lo que sucedió después de la última vez que corrió este mismo script: “WHERE LastUpdateDate >= TO_DATE(‘$(v_CompareDate_2)’,’YYYY-MM-DD’)”. Esto podría ser ayer o hace dos semanas, no importa porque la fecha de la última corrida (menos un día) quedó almacenada en la variable v_CompareDate_2 (recordar que las variables de QV son persistentes en el tiempo).

2)      ¿Cómo evitamos cargar novedades de filas que ya habían sido cargadas previamente?

Si en el QVD hubiera filas almacenadas ya presentes entre las novedades nos quedaremos con las novedades y DESCARTAMOS lo que estaba guardado: “WHERE NOT EXISTS (UNIQUEID)”

3)      ¿Cómo juntamos las novedades con el resto de los datos previamente almacenados?

Concatenando ambos conjuntos: “CONCATENATE (incrementalTable)”

4)  ¿Cómo “preparamos” el script para la próxima ejecución? Setando la fecha v_CompareDate_2 al valor de v_CompareDate_1: “LET v_CompareDate_2 = ‘$(v_CompareDate_1)’”

De este modo tenemos listo un script capaz de cargar la totalidad de una tabla en su primera ejecución y luego solo extraer y almacenar las novedades sustituyendo las viejas versiones de esos registros si existieran.

No obstante esta técnica qunque optimiza la lectura de la BD y la transferencia de datos desde esta tiene un problema de performance en caso que el QVD resultante sea verdaderamente grande (>1 ó 2 TByte) y es que cada vez que corre este script tal y como está primero lee y luego graba un archivo muy grande al disco, lo que incluso con servidores medianamente potentes puede estar en el orden de los 10-20 minutos, haciendo muy pesada la ejecución, consumiendo valioso tiempo de una ventana operativa que en muchas organizaciones suele estrecharse a unas pocas horas de madrugada.

En la segunda parte de esta nota voy a explicar cómo modificar y empaquetar este código para leer y grabar solo pequeñas porciones del QVD (en realidad muchos pequeños QVDs), optimizando los tiempos y el recurso de acceso a disco.

Using expression’s color to show more information

In this post I show how can use the color expression’s in a chart to show information to the user in a single glance and maintaining the graph in a simple and easily readable format.

The idea is that each bar (in the case of a bar chart) can be assigned to a particular color using RGB () function with some kind of calculation based on the model’s data.

For example suppose you have a table of receivables with the following information:

Receivables:
load * inline [
CustomerID, Name, Debt, Days_Old, Credit_Qualification
1,   Pedro , 100, 50, 2-Good
2,   Maria , 140, 20, 1-Very Good
3,   Susana, 180, 90, 3-Poor
4,   John, 50, 35, 4-Bad
5,   Carlos, 70, 70, 5-Very Bad
6,  Sofia, 250, 10, 2-Good
];

And we want to show debtors in a bar chart where the expression is the amount owed, the dimension the debtor’s name, but we also want to show the age of the debt as follows: the oldest debt we will highlight with intense red color and vice versa, getting something like this:

Chart

 

Because Sofia has a quite new debt (10 days, low risk) will use a dimmed color, in contrast with a 90 days Susana’s debt showed with deep red.

To achieve this we will use the RGB function RGB(255, X, X) where 255 represents the red component and X is a function: while X is closest to 230 (maximum value) more faint result the color, and the more close to 0 (zero) more intense.

To do this add the following code to the script:

 MinMax:
Load
min(Days_Old) as minDaysOld,
max(Days_Old) as maxDaysOld
RESIDENT Receivables;

LET V_minDaysOld = Peek(‘minDaysOld’,0);
LET V_maxDaysOld = Peek(‘maxDaysOld’,0);

Drop Table MinMax;

 Finally we create an expression and use it in the “Background Color” from the main expression:

 BackgroundColor

 Of course you can create color expression that is required, with more or less levels of complexity or assigning colors to age ranges, etc..

The important thing to remember here is another resource, the color, which can be used to display user’s valuable information.

Usar los colores de las expresiones para mostrar información

En este post muestro en forma breve como se puede utilizar el color de un gráfico para transmitir información al usuario en un solo golpe de vista y manteniendo el gráfico en un formato simple y fácilmente legible.

La idea consiste en que a cada barra (para el caso de un gráfico de barras) se le puede asignar un color definido mediante una función RGB() con algún tipo de cálculo sobre los datos del modelo.

Por ejemplo supongamos que tenemos una tabla de deudores con la siguiente información:

Receivables:
load * inline [
CustomerID, Name, Debt, Days_Old, Credit_Qualification
1,   Pedro , 100, 50, 2-Good
2,   Maria , 140, 20, 1-Very Good
3,   Susana, 180, 90, 3-Poor
4,   John, 50, 35, 4-Bad
5,   Carlos, 70, 70, 5-Very Bad
6,  Sofia, 250, 10, 2-Good
];

Y queremos mostrar los deudores en un gráfico de barras donde la expression sea la suma adeudada, la dimension el nombre del deudor, pero tabién queremos mostrar la antigüedad de la deuda del siguiente modo: mientras mas antigua vamos a resaltarla con un color rojo mas intenso y vice-versa, obteniendo algo así:

Chart

Debido a que Sofia tiene la deuda con menor antigüedad (10 días, bajo riesgo) usamos un color tenue, por el contrario Susana con una deuda de 90 días la mostramos con un rojo intenso.

 Para conseguir esto vamos a usar la función RGB(255,X,X) donde 255 representa el componente red y X una función: mientras X más se aproxime a 230 (valor máximo) mas tenue resultará el color, y mientras mas cercano a 0 (cero) mas intenso.

Para lograr esto agregamos el siguiente código al script:

 MinMax:
Load
min(Days_Old) as minDaysOld,
max(Days_Old) as maxDaysOld
RESIDENT Receivables;

LET V_minDaysOld = Peek(‘minDaysOld’,0);
LET V_maxDaysOld = Peek(‘maxDaysOld’,0);

Drop Table MinMax;

 Por ultimo debemos crear una expresión y emplearla en el “Background Color” de la expresión principal:

 BackgroundColor

 Desde luego se puede crear la expresión que se requiera, con mayor o menor nivel de complejidad o asignando colores a rangos de antigüedad, etc.

Lo importante aquí es tener presente un recurso más, el color, que se puede emplear para mostrar información.

LookUps, Apply Map, Group By, Select Distinct: diferentes técnicas para marcar filas

Este post tiene por objeto documentar y recopilar los principales métodos que conozco para marcar (“flaguear”) filas en una tabla del modelo respecto de otra, usando un campo creado para tal propósito y agregándolo a la tabla que se busca marcar.

Los motivos por los que se suelen marcar filas mediante el uso de flags son en general de performance y/o de volumen: en el primer caso se pueden contar, sumar o hacer otra medida recorriendo una sola tabla y evitando relaciones que pueden resultar complejas y en el segundocaso  solo se desea calificar la primera entidad según datos de la segunda entidad, pero sin necesidad de preservar los datos de la segunda, economizando espacio. En definitiva es una técnica bastante usada al construir una tabla de hechos (fact tables) en un modelo estrella  y algo menos usadas en un modelo snow-flake.

Si bien existen distintas técnicas lo más importante es tener perfectamente clara la relación que existe entre ambas tablas y su cardinalidad ya que de esto último dependerá el conjunto de técnicas que podrán ser aplicadas sin que se generen errores por duplicación de filas.

Si la relación entre las tablas es 1 a 0-ó-1:

Como ejemplo tomemos dos tablas: Empleados (tabla Employees) y Parejas (tabla Spouses para esposos, esposas, cónyuges en general):

Employees:
load * inline [
EmpId, EmpName
1,   Pedro
2,   Maria
3,   Susana
4,   John
5,   Carlos
6,  Sofia
];

Spouses:
Load * inline [
SpouseID, EmpId, Name
1, 1, Sonia
2, 2, Paul
3, 4, Gabriela
5, 6, Serge
];
 

Lo que se buscamos es marcar los empleados agregando el campo “Spouse” (cónyuge) y asignarle el Valor Y/N según se encuentre o no una fila en la tabla de “Spouse”.

Usando “Left Join”:

Primero el código:

LEFT JOIN (Employees)
LOAD
     EmpId,                                                  /// link field
     if(isNull(EmpId),’N’,’Y’) as Spouse
RESIDENT Spouses;

Drop Table Spouses;

 Y el resultado que obtenemos es:

 Cero

Pero Susana y Carlos en lugar de mostrar “N” (tal como sería el requerimiento de usuario) en el campo Spouse muestran “–“ (nulo). Esto es porque el código if(isNull(EmpId),’N’,’Y’) se ejecuta para cada línea de la tabla Spouse, donde efectivamente no existen filas que correspondan a Susana y Carlos y obviamente nunca EmpId llega a ser nulo en dicha tabla.

Por esto para finalizar el requerimiento necesitamos hace una segunda pasada, costosa y poco recomendable:
tmpEmployees:
Noconcatenate
Load
     EmpId,
     EmpName,
     if(isNull(Spouse),’Y’,’N’) as Spouse
RESIDENT Employees;

Drop table Employees;
Rename Table tmpEmployees to Employees;
 

Aquí buscamos los campos donde “Spouse” es nulo y cambiamos este valor por “N”, obteniendo el siguiente resultado:

 quinta

Por último  el uso de “Left Join” podría  ocasionar que se dupliquen filas si en algún caso la relación 1-a-0/1 no fuera así, creando una situación difícil de detectar entre (algunas veces) millones de registros.

Usando “Apply Map”:

Esta es una técnica Mucho más recomendable por performance, especialmente si se trabaja con grandes volúmenes,  y por requerir una única pasada (además de construir  el mapa): 

mapSPOUSES:   //Construimos el mapa donde a cada empleado que tiene esposa y

             //le asignamos “Spouse=Y”.
MAPPING
LOAD 
     EmpId,
     ‘Y’ as Spouse
Resident Spouses;

Drop Table Spouses;

// Aplicamos el mapa reemplazando el campo EmpId por lo que devuelve el mapa
// si se encuentra EmpId en él (es decir “Y”) y si el mapa no encuentra la
// relación ponemos una “N”

tmpEmployees:
Load
     EmpId,
     EmpName,
     applymap(‘mapSPOUSES’,EmpId,’N’) as Spouse
RESIDENT Employees;

Drop Table Employees;
RENAME Table tmpEmployees to Employees;

 Con lo cual obtenemos en una sola pasada: quinta

Usando “Exists”:

Para usar esta técnica (en este ejemplo) lo primero que debemos hacer es cambiar el nombre del campo EmpId a EmpId2 en la tabla Spouses:

Spouses:
Load * inline [
SpouseID, EmpId2, Name .........

 Luego el código:

Employees2:
NoConcatenate
Load
     EmpId,
     EmpName,
     'Y' as Spouse
RESIDENT Employees
where EXISTS(EmpId2,EmpId);

Lo que produce (solo los empleados en relación conyugal):

 cuarta

Y aquí el código complete la tabla con los “N”:

Employees2:
Load
     EmpId,
     EmpName,
     'N' as Spouse
RESIDENT Employees
where NOT EXISTS(EmpId2,EmpId);

Drop Tables Spouses, Employees;
Rename Table Employees2 to Employees;

 Lo que produce: quinta

Usando “Lookup”:

Employees2:
NoConcatenate
Load
     EmpId,
     EmpName,
     if(isnull(lookup('SpouseID', 'EmpId', EmpId, 'Spouses')),'N','Y') as Spouse
RESIDENT Employees;

Drop Tables Spouses, Employees;

Y obtenemos: quinta

 Cuando el volumen de las tablas es particularmente grande es altamente aconsejable el empleo de Apply Map por razones de performance por sobre cualquier otra técnica incluso lookup, aunque vale aclarar que el método basado en EXISTs es altamente performante (a mi entender incluso más que apply map) solo que son bastante restringidas las ocasiones en que puede ser empleado.

Si la relación entre las tablas es de 1 a 0,1 o más filas:

Para esto vamos a cambiar un poco el diseño de las tablas sobre el que vamos a trabajar, armando tablas con relación 1 a 0-1-N:

Employees:
load * inline [
EmpId, EmpName
1,   Pedro
2,   María
3,   Susana
4,   John
5,   Carlos
6,  Pablo
];

Childs:
Load * inline [
ChildID, EmpId, Name
1, 1, Joseph
2, 2, Micaela
3, 2, Santino
4, 4, Mili
5, 5, Catty
6, 6, Sebastián
7, 6, Florencia
]; 

Usando “Left Join” con Group By:

Aquí la idea es obtener una única fila de la tabla Childs por cada empleado, agregando cierta información, en este caso contando la cantidad de hijos:

Left Join (Employees)
Load
     EmpId,
     Count(ChildID) as Childs
RESIDENT Childs
Group By EmpId;

Con lo que obtenemos:

 sexta
Y si queremos convertir los nulos en 0 (ceros), agregamos:

tmp_Eployees:
Load
     EmpId,
     EmpName,
     if(isNull(Childs),0,Childs) as Childs2
RESIDENT Employees;

Drop Tables Childs, Employees;
RENAME Table tmp_Employees to Employees;

 Con lo que logramos: septima

Usando “Left Join” con “Select Distinct”:

La idea aquí es nuevamente obtener una única fila para joinear con la tabla Employees y de esta forma no duplicar filas. Pero aquí en lugar de usar Group by (que tiene muy mala performance en grandes volúmenes) lograremos una única fila por empleado de la siguiente forma:

Left Join (Employees)
Load DISTINCT
     EmpId,
     if(not isNull(ChildID),’Y’) as Childs
RESIDENT Childs;

La limitación de ésta técnica a diferencia del Grup By es que no podemos obtener cantidades u otras agregaciones (p.ej: sum, concat, etc.) sino que debemos limitarnos a obtener algún tipo de flag siempre igual, justamente para que el DISTINCT deje solo una fila por empleado, obteniendo: 

octava

Y si queremos convertir los nulos en 0 (ceros), agregamos:

tmp_Eployees:
Load
     EmpId,
     EmpName,
     if(isNull(Childs),’N’,Childs) as Childs2
RESIDENT Employees;

Drop Tables Childs, Employees;
RENAME Table tmp_Employees to Employees;

Obteniendo: novena

 Usando “LookUp “

Employees2:
NoConcatenate
Load
     EmpId,
     EmpName,
     if(isnull(lookup(‘ChildID’, ‘EmpId’, EmpId, ‘Childs’)),’N’,’Y’) as Childs
RESIDENT Employees;

Drop Tables Childs, Employees;

 Y obtenemos el resultado buscado en una sola pasada:

novena

Usando “ Exists”:

Aplicamos la misma técnica antes explicada:

Primero modificamos el nombre del campo EmpId (a EmpId2) en la tabla Childs:

 Childs:
Load * inline [
ChildID, EmpId2, Name …

 Y luego el código:

Employees2:
NoConcatenate
Load
     EmpId,
     EmpName,
     ‘Y’ as Childs
RESIDENT Employees
where EXISTS(EmpId2,EmpId);

Employees2:
Load
     EmpId,
     EmpName,
     ‘N’ as Childs
RESIDENT Employees
where NOT EXISTS(EmpId2,EmpId);

 Obteniendo en dos tiempos: novena

 Por ultimo usando APPLY MAP:

Construimos el mapa donde a cada empleado que tiene hijos y le asignamos Child=Y. 

mapChilds:  

MAPPING
LOAD 
     EmpId,
     ‘Y’ as Child
Resident Childs;
Drop Table Childs;

Aplicamos el mapa reemplazando el campo EmpId por lo que retorna el mapa. Si se encuentra EmpId en él (es decir “Y”) y si el mapa no encuentra la relación ponemos una “N”.

tmpEmployees:
Load
     EmpId,
     EmpName,
     applymap(‘mapChilds’,EmpId,’N’) as Child
RESIDENT Employees;

Drop Table Employees;
RENAME Table tmpEmployees to Employees;

De esta forma obteniendo una única fila para cada empleado ya que APPLY MAP solo devuelve la primera coincidencia por cada fila. En este sentido funciona similar a un Select DISTINCT:

novena

Seguir

Recibe cada nueva publicación en tu buzón de correo electrónico.

%d personas les gusta esto: