Incremental extract ODBC

5

Here is an example of incremental load. It has 2 modules: “Generate rows” and “Incremental load”. There is also a helper module that queries database.

The example works as follows:

Module “Generate rows” generates in a databases (empty DB comes with the example) random data points with timestamps that differ from each other by 1 minute. The logic of the module is pretty much self-explanatory so I won’t explain it much.

Module “Incremental load” reads these data points incrementally from the database. It works as follows:

  1. Check if a text file with max timestamp (max-timestamp.txt) exists.
  2. If it doesn’t exist then read all rows from the database table and save the max timestamp into max-timestamp.txt. To read all rows the {Max timestamp} parameter is set to 0 (which corresponds to 1/1/1900) to make the filtering condition in the query unfulfillable.
  3. Otherwise, if max-timestamp.txt exists, then read the max timestamp from it and pass it as the {Max timestamp} parameter to the “Query database” module (with the help of the “Iterate” action) which will read incremental data (if any).

How to use the example:

  1. Download and unzip the example.
  2. Run the “Generate rows” module (if you can’t find modules read this). This will generate 10 rows with random data and timestamps going 10 minutes back from now.
  3. Switch to the “Incremental load” module and run it. Since the example comes without max-timestamp.txt on the 1st run the module imports all data and creates max-timestamp.txt with the latest timestamp saved.
  4. Wait for a couple of minutes, switch back to “Generate rows” and generate a few more rows. This should generate new rows in the database table. The timestamps of the new rows should now be greater than the timestamp saved in max-timestamp.txt.
  5. Switch again to “Incremental load” and run it. Now it should read only the incremental data - rows that have timestamp that is later than the saved timestamp.
  6. Run “Incremental load” again (using the “Reload and run” button). Now it reads no data because the saved max timestamp was updated in step 5 but no new data has been generated since then.

Incremental load.zip (2.9 KB)

image
image932×641 18.4 KB