1 / 21

Incremental Load

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.

dennislindsey
Download Presentation

Incremental Load

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Incremental Load using qvd files

  2. Incremental Load Is sometimes called… Incremental Load Differential Load Delta Load

  3. Incremental Load Goal: Load only the new or the changed records from the database. The rest should already be available, one way or another.

  4. 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…

  5. 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

  6. Different DB-changes If source allows … • Append only. (Logfiles) • Insert only. (No Update or Delete) • Insert and Update. (No Delete) • Insert, Update and Delete.

  7. 1) Append only • Must be Log file • Loads records added in the end of the file

  8. 1) Append only Buffer (Incremental) Load* From LogFile.txt (ansi, txt, delimiter is '\t', embedded labels); Done!

  9. 2) Insert only • Can be any DB • Loads INSERTed records • Needs the field ModificationDate

  10. 2) Insert only QV_Table: SQLSELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)#;

  11. 2) Insert only QV_Table: SQLSELECT PrimaryKey, X, Y FROM DB_TABLE WHERE ModificationTime >= #$(LastExecTime)#; Concatenate LOAD PrimaryKey, X, Y FROM File.QVD;

  12. 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

  13. 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!

  14. 3) Insert and Update • Can be any DB • Loads INSERTed and UPDATEd records • Needs the fields ModificationDate and PrimaryKey

  15. 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!

  16. 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

  17. 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…

  18. 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

  19. LastExecutionTime & Error handling Let ThisExecTime = Now(); { Load sequence } IfScriptErrorCount = 0 then Let LastExecTime = ThisExecTime; EndIf

  20. 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

  21. 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

More Related