Load Incremental com Merge

[Clever_Anjos]

Desde o surgimento do novo prefixo MERGE (https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...) ficou bem mais fácil criar uma carga incremental.

Esse comando recebe uma tabela com operações de insert,update e delete e é capaz de alterar um tabela já carregada em memória

Agora nossa cargas incrementais de QVD ficam com um código bem mais limpo e performático.

Para os exemplos abaixo temos duas premissas:

• OrderID é a chave primária da tabela (se for uma chave composta, só ajustar)
• update_dt armazena a data/hora da ultima inserção/alteração do registro

Vejamos os exemplos:

1. "Insert Only"

   QVDFile='lib://DataFiles/NW.Orders_InsertOnly.qvd';
   if FileSize('$(QVDFile)') > 0 then //If there is already extracted data
   	[NW.Orders]:
       Load	
           OrderID,
           CustomerID,
           EmployeeID,
           OrderDate,
           RequiredDate,
           ShippedDate,
           ShipVia,
           Freight,
           ShipName,
           ShipAddress,
           ShipCity,
           ShipRegion,
           ShipPostalCode,
           ShipCountry
       from [$(QVDFile)](qvd);
       t:load Max(OrderID) as MaxOrderID Resident [NW.Orders];
       LastOrderID=Peek('MaxOrderID'); // Retrieves the last OrderID
       Drop Table t;
   else 
   	LastOrderID = '0';
       [NW.Orders]:load null() as OrderID AutoGenerate 0; // fake table
   endif 
   
   Merge(updated_dt) on OrderID Concatenate([NW.Orders])
   Load If(Exists(OrderID),'U','I') as Operation, *; // New record? based on OrderID
   SELECT OrderID,
   	CustomerID,
   	EmployeeID,
   	OrderDate,
   	RequiredDate,
   	ShippedDate,
   	ShipVia,
   	Freight,
   	ShipName,
   	ShipAddress,
   	ShipCity,
   	ShipRegion,
   	ShipPostalCode,
   	ShipCountry,
           updated_dt
   FROM northwind.orders
   Where OrderID > '$(LastOrderID)';
   
   Store [NW.Orders] into [$(QVDFile)](qvd);
   ​

2. Carga de "Insert" e "Update" 

   QVDFile='lib://DataFiles/NW.Orders_InsertUpdate.qvd';
   if FileSize('$(QVDFile)') > 0 then //If there is already extracted data
   	[NW.Orders]:
       Load	
           OrderID,
           CustomerID,
           EmployeeID,
           OrderDate,
           RequiredDate,
           ShippedDate,
           ShipVia,
           Freight,
           ShipName,
           ShipAddress,
           ShipCity,
           ShipRegion,
           ShipPostalCode,
           ShipCountry,
           updated_dt
       from [$(QVDFile)](qvd);
       t:load Max(updated_dt) as updated_dt Resident [NW.Orders];
       updated_dt=Timestamp(Peek('updated_dt'),'YYYY-MM-DD hh:mm:ss.fff'); // Retrieves the last udpate
       Drop Table t;
   else 
   	updated_dt = '2000-01-01';
       [NW.Orders]:load null() as OrderID AutoGenerate 0; // fake table
   endif 
   
   Merge(updated_dt) on OrderID Concatenate([NW.Orders])
   Load If(Exists(OrderID),'U','I') as Operation, *; // New record? based on OrderID
   SELECT OrderID,
   	CustomerID,
   	EmployeeID,
   	OrderDate,
   	RequiredDate,
   	ShippedDate,
   	ShipVia,
   	Freight,
   	ShipName,
   	ShipAddress,
   	ShipCity,
   	ShipRegion,
   	ShipPostalCode,
   	ShipCountry,
       updated_dt
   FROM northwind.orders
   Where updated_dt > '$(updated_dt)';
   
   Store [NW.Orders] into [$(QVDFile)](qvd);
   ​

3. Carga de "Insert" e "Update" e "Delete"

QVDFile='lib://DataFiles/NW.Orders_InsertUpdateDelete.qvd';
if FileSize('$(QVDFile)') > 0 then //If there is already extracted data
	[NW.Orders]:
    Load	
        OrderID,
        CustomerID,
        EmployeeID,
        OrderDate,
        RequiredDate,
        ShippedDate,
        ShipVia,
        Freight,
        ShipName,
        ShipAddress,
        ShipCity,
        ShipRegion,
        ShipPostalCode,
        ShipCountry,
        updated_dt
    from [$(QVDFile)](qvd);
    t:load Max(updated_dt) as updated_dt Resident [NW.Orders];
    updated_dt=Timestamp(Peek('updated_dt'),'YYYY-MM-DD hh:mm:ss.fff'); // Retrieves the last udpate
    Drop Table t;
else 
	updated_dt = '2000-01-01';
    [NW.Orders]:load null() as OrderID AutoGenerate 0; // fake table
endif 
trace $(updated_dt);

Merge(updated_dt) on OrderID Concatenate([NW.Orders])
Load If(Exists(OrderID),'U','I') as Operation, *; // New record? based on OrderID
SQL SELECT OrderID,
	CustomerID,
	EmployeeID,
	OrderDate,
	RequiredDate,
	ShippedDate,
	ShipVia,
	Freight,
	ShipName,
	ShipAddress,
	ShipCity,
	ShipRegion,
	ShipPostalCode,
	ShipCountry,
    updated_dt
FROM northwind.orders
Where updated_dt > '$(updated_dt)';

//Keeping only records which OrderID is still in database
Inner Keep([NW.Orders])
SQL SELECT 
	DISTINCT OrderID
FROM northwind.orders;    

Store [NW.Orders] into [$(QVDFile)](qvd);