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);

Acerca de pparnisari
Casi 44 vueltas al sol. Licenciado en sistemas. Curioso, investigador, excelente para resolver problemas prácticos. Casado, 2 hijos, cada vez con mas preguntas y respuestas mas en duda. Almost 44 laps to the sun. System engineer. Curious, researcher, great for solving practical problems. Married, 2 children, each time with more questions and more answers in doubt.

Deja un comentario

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: