Appending Data Capture using Domo Workbench
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:
- 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.
- 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.
I work for Domo.
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as "Accepted Solution"
Best Answers
-
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?
2 -
You can find a curated and updated article on this topic in the Help Center here: http://knowledge.domo.com?cid=wb4faq
0
Answers
-
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?
2 -
SeanPT great feedback! We will make sure to update the documentation. Thank you.
Thanks!
I work for Domo.
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as "Accepted Solution"1 -
this is great, can you point me to something similar using magic and Redshift?
0 -
@canioacaputo, please feel free to open a new thread with your request for better exposure!
1 -
You can find a curated and updated article on this topic in the Help Center here: http://knowledge.domo.com?cid=wb4faq
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive