Appending Data Capture using Domo Workbench

dark_knight

New Job

 

Creating a new job that will append the data that has changes in the RDBMS since last load.

 

Job setup

First do the initial load of the data using the Create New Data source.

 

 

The two key setup lines in the sql that are very important are:

1. Where "ReportDate" > '!{lastvalue:ReportDate}!'

The Where clause must contain the field that holds the value that indicate the changes since last load.

The '!{lastvalue:ReportDate}!' lastvalue contains the last value for the Query Variable as shown. (See setup of Query Variable section). It is important to note that the Query Variable must be called the same as the field in the Query that indicates the changes since last Load.

1. Order by

It is important to have an Order By clause in the SQL statement, since it will be the value from the last row that will be saved in the Lastvalue variable. If you don’t order by the column that contains the change data capture field then you might not get the changed rows in the query.

 

 

After the initial load change the job to do an “Append to Existing Data Source“ to start appending data to the existing Data Source.

 

 

 

 

 

 

 

Query Variable setup

The Query Variable setup is done on the separate ‘Edit Query Variables’ screen.

You have the option of setting a Job Variable or a Global Variable.

 

You will need to create a Job Variable that matches the ‘Name’ of the field that contains the data that controls the Changed data.

 

 

The ‘Current Value’ will indicate the last value the job loaded. The initial value must be less than the first record you want to import.

If you need to do a reload of the data you will be able to manually change the Current Value in the initial job, and then make a ‘Replace’ of the data. Once a reload job has been executed the latest loaded value will be updated to the last current value loaded.

SeanPT

This is fantastic. I am trying this for the first time today and I'm using a field ImportTime for my  lastvalue.

I setup the initial variable using Edit Query Variables. ImportTime and a timestamp that would be before all my data.

 

I then added a line to my query

WHERE cs.[ImportTime] > '!{lastvalue:ImportTime}!'

ORDER BY cs.[ImportTime]

 

And everything uplaoded just fine.

Except the manual ImportTime that I put in for the variable didn't update when the job ran.

So I went back and tried it again and again and I couldn't get it to update the variable.

 

Finally I tried something on a lark - I added the ImportTime to select clause. I don't need that import time at all in any report. It is worthless for me in data analytics. But once I added that as a column the variable updated and all is right in Toy Land tonight. So maybe update the documentation?

SamHoward

You can find a curated and updated article on this topic in the Help Center here: http://knowledge.domo.com?cid=wb4faq

SeanPT

This is fantastic. I am trying this for the first time today and I'm using a field ImportTime for my  lastvalue.

I setup the initial variable using Edit Query Variables. ImportTime and a timestamp that would be before all my data.

 

I then added a line to my query

WHERE cs.[ImportTime] > '!{lastvalue:ImportTime}!'

ORDER BY cs.[ImportTime]

 

And everything uplaoded just fine.

Except the manual ImportTime that I put in for the variable didn't update when the job ran.

So I went back and tried it again and again and I couldn't get it to update the variable.

 

Finally I tried something on a lark - I added the ImportTime to select clause. I don't need that import time at all in any report. It is worthless for me in data analytics. But once I added that as a column the variable updated and all is right in Toy Land tonight. So maybe update the documentation?

dark_knight

SeanPT great feedback! We will make sure to update the documentation. Thank you.

 

canioacaputo

this is great, can you point me to something similar using magic and Redshift?

kshah008

@canioacaputo, please feel free to open a new thread with your request for better exposure!

SamHoward

You can find a curated and updated article on this topic in the Help Center here: http://knowledge.domo.com?cid=wb4faq