Incremental extract ODBC

[rdevroome]

Hi, I would like to make incremental extracts via ODBC.
I can’t find a standard feature of easymorph that supports this.
Is it possible to find the latest date that is already extracted, store this in a parameter and use this in a select statement to collect the new rows from ODBC?
In other words, how can I select the latest date from a table and write that to a parameter?
Thanks in advance!

[dgudkov]

Hi Ruben and welcome to the Community!

Incremental updates are surely possible in EasyMorph, but there is no “tick a checkbox” feature for it. An incremental update workflow has to be constructed. I’ll make an example and get back to you.

[reynsnivea]

Hi Dmitry,

It would be nice if one day it could be a standard feature of EasyMorph.

@rdevroome: maybe you could consider change data capture on the database to track inserts, update and deletes automatically.

Kind regards
Nikolaas

[rdevroome]

@dgudkov thank you, an example would be really nice.

@reynsnivea, it’s a very big table with a lot of transactions that also grows pretty quick.
I have to do some check on some field anyway to see where I stopped last time.

Kind regards,

Ruben

[dgudkov]

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)

[rdevroome]

Thanks for this!
I will have a look at this the coming days.

Other question, is there a simple way to write the result of a query into a parameter value?

Kind regards,

Ruben

[dgudkov]

@rdevroome

Other question, is there a simple way to write the result of a query into a parameter value?

You can do that with the "Iterate" action. It assigns parameters of the called project with values from columns. Keep in mind that it's a loop action - it will call the iterated project as many times as there are rows in the current table.

[Juan_Francisco_Carre]

@dgudkov Estoy migrando mis Tablas por ETL mi pregu ta es la siguiente una vez migrada mis tablas como puedo hacer para que las actualizaciones diarias ed esas tablas se realizen desde easymorph me podrias dar una orientacion por favor

[dgudkov]

Hi @Juan_Francisco_Carre and welcome to the Community!

Please post in English, that’s a rule of this Community forum. Thank you for understanding.

[Juan_Francisco_Carre]

@dgudkov Hello, I am migrating tables to EasyMorph, how can I create incrementals so that my tables are automatically updated every certain time. Thank you

[dgudkov]

@Juan_Francisco_Carre, check out my answer earlier in this thread:

Incremental extract ODBC

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 d…