Load Incremental com Merge

Clever_Anjos — Wed, 27 Apr 2022 16:12:23 GMT

Desde o surgimento do novo prefixo MERGE (https://help.qlik.com/en-US/sense/February2022/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPrefixes/Merge.htm) 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:

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

topic Load Incremental com Merge in Brasil

Load Incremental com Merge