Incremental Load using qvd files
Incremental Load Is sometimes called… Incremental Load Differential Load Delta Load
Incremental Load Goal: Load only the new or the changed records from the database. The rest should already be available, one way or another.
Comments on Buffer Load • Buffer (Incremental) Load … is a solution only for Log files (text files), but not for DBs. • Buffer (Stale after 7 days) Select … is not a good solution. It makes a full Load after 7 days. And nothing in between…
Incremental Load • Load new data from Database table (slow, but few records) • Load old data from QVD file (many records, but fast) • Create new QVD file • Procedure must be repeated for each table
Different DB-changes If source allows … • Append only. (Logfiles) • Insert only. (No Update or Delete) • Insert and Update. (No Delete) • Insert, Update and Delete.
1) Append only • Must be Log file • Loads records added in the end of the file
1) Append only Buffer (Incremental) Load* From LogFile.txt (ansi, txt, delimiter is '\t', embedded labels); Done!
2) Insert only • Can be any DB • Loads INSERTed records • Needs the field ModificationDate
2) Insert only QV_Table: SQLSELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)#;
2) Insert only QV_Table: SQLSELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)#; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD;
2) Insert only QV_Table: SQLSELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)#; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD; STORE QV_TableINTO File.QVD; Almost doneBut there is a small chancethat a record gets loaded twice
2) Insert only QV_Table: SQLSELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)# AND ModificationTime < #$(BeginningThisExecTime)#; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD; STORE QV_TableINTO File.QVD; Done!
3) Insert and Update • Can be any DB • Loads INSERTed and UPDATEd records • Needs the fields ModificationDate and PrimaryKey
3) Insert and Update QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)#; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD WHERENOT Exists(PrimaryKey); STORE QV_Table INTO File.QVD; Done!
4) Insert, Update and Delete • Can be any DB • Loads INSERTed and UPDATEd records • Removes DELETEd records • Needs the fields ModificationDate and PrimaryKey • Tricky to implement
4) Insert, Update and Delete QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)#; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD WHERENOT EXISTS(PrimaryKey); InnerJoin SQLSELECT PrimaryKey FROM DB_TABLE; STORE QV_Table INTO File.QVD; OK, but slow…
4) Insert, Update and Delete ListOfDeletedEntries: SQLSELECT PrimaryKey AS Deleted FROM DB_TABLE WHEREDeletionFlag = 1 and ModificationTime >= #$(LastExecTime)#; QV_Table: SQL SELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)#; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD WHERENOTExists(PrimaryKey) ANDNOTExists(Deleted,PrimaryKey); DropTableListOfDeletedEntries; STORE QV_Table INTO File.QVD; OK, but needs a DeletionFlag
LastExecutionTime & Error handling Let ThisExecTime = Now(); { Load sequence } IfScriptErrorCount = 0 then Let LastExecTime = ThisExecTime; EndIf
Final Script Let ThisExecTime = Now(); QV_Table: SQLSELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)# AND ModificationTime < #$(ThisExecTime)#; ConcatenateLOAD PrimaryKey, X, Y FROM File.QVD WHERENOT EXISTS(PrimaryKey); InnerJoin SQLSELECT PrimaryKey FROM DB_TABLE; IfScriptErrorCount = 0 then STORE QV_TableINTO File.QVD; Let LastExecTime = ThisExecTime; EndIf
Summary 1 • Incremental Load possible for… • Append only. (Logfiles) Yes! • Insert only. (No Update or Delete) Yes! • Insert and Update. (No Delete) Yes! • Insert, Update and Delete. Slow, or demands DeletionFlag
