topic Re: Incremental Load in Talend Studio

Incremental Load

Anonymous — Thu, 04 Apr 2019 06:23:14 GMT

Hi,

I want to implement an Incremental load in Talend itself. I have gone through some blogs and posts which suggest creating a table in db to store the job name and last run time. I want a solution that handles the incremental loading by capturing the last update date in Talend itself and while executing the job next time it should pick the value and run for the newly updated records. Or any other approach which doesn't include creating a new table for this purpose.

Thanks in advance,

Sowmiya R

Re: Incremental Load

manodwhb — Thu, 04 Apr 2019 06:50:53 GMT

@sowmi333 ,either you should capture the job name and last run time in database table or file, in talend we can cannot store and which can not able to pick that in the next run.

check the below link.

http://mahadevanrv.blogspot.in/2015/04/talend-incremental-load-using-talend.html

Re: Incremental Load

Anonymous — Thu, 04 Apr 2019 06:54:39 GMT

Hi Sowmiya,

The reason for recommending to store the control values in DB is because DBs have better capabilities to recover the data easily in case of a data loss or disaster. Instead of DB, you can store this last run data in a file also. In that case, the onus of maintaining and protecting this data will be on the operational team. What will happen in case the file is accidentally deleted by a user? Your whole process will go for a toss in that case.

End of day, Talend is an ETL tool and it is not a storage medium. So trying to use Talend as the medium to store the control data is not the recommended approach. My personal preference is always to use DB for storing these type of data due to ts capabilities to come out of disaster recovery easily but you can also go by file approach.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Incremental Load

joearun4u — Thu, 04 Apr 2019 07:28:22 GMT

Hi Sowmiya,

If you don't want to create any additional table in DB, Every time when you execute the job , you can get max(LastUpdateddate) from the Target table and make it pass in to TDBInput query like 'select * from <table> where lastUpdateDate =<value from the previous component>.

Thanks,

Joe

Re: Incremental Load

Anonymous — Thu, 04 Apr 2019 07:53:55 GMT

Hi Joe,
I have a doubt what you mean by the previous component in the phrase "TDBInput query like 'select * from <table> where lastUpdateDate =<value from the previous component>.". Can you please elaborate on it

Re: Incremental Load

Anonymous — Thu, 04 Apr 2019 08:21:19 GMT

Hi,

The data is extracted from the DB to a variable in the previous step by max(LastUpdateddate). This will be stored to a context variable (context.last_run).

He meant to say, use this value from previous step when you are querying next time.

'select * from <table> where lastUpdateDate >= context.last_run'

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Incremental Load

Anonymous — Thu, 04 Apr 2019 09:17:06 GMT

Hi @nthampi ,

Thanks much for your suggestion. Let me it try out and update you 🙂

Thanks,

Sowmiya R

Re: Incremental Load

joearun4u — Thu, 04 Apr 2019 10:36:37 GMT

Exactly, I meant the same step as you have explained.

but here the problem is, if the Target table is huge like 900 M records with 100's of columns, it will take more time to fetch the LastUpdatedDate . Always the better plan is to store the execution time in a Table for fast processing.

Thanks,

Joe

Re: Incremental Load

akumar2301 — Thu, 04 Apr 2019 10:43:04 GMT

There is another way to get the last execution time OF JOB .one of the way is to use Metaservlet with below command :

----------------------------------------------------------
  Command: taskLog
----------------------------------------------------------
Description             : Retrieve the logs of the specified task
- lastExecution: true/false(default). Get the lastest execution log or not.
Requires authentication : true
Since                   : 5.1
Sample                  : 
{
  "actionName": "taskLog",
  "authPass": "admin",
  "authUser": "admin@company.com",
  "endTime": "\"2016-03-24 13:00:00\"",
  "lastExecution": false,
  "startTime": "\"2016-03-24 12:00:00\"",
  "taskId": 1
}
Specific error codes    : 
       220: Error happened when reading logs.
       225: Error happened when date range is illegal.
       226: Error appears when parsing the date.
--------------------------------------------------

Re: Incremental Load

Anonymous — Thu, 04 Apr 2019 12:42:20 GMT

Hi @uganesh ,

Could you please elaborate on the Metaservlet as I'm new to Talend

Thanks,

Sowmiya

Re: Incremental Load

Anonymous — Thu, 04 Apr 2019 12:53:52 GMT

Hi @joearun4u ,

Yes, as you said I'm having over a million records and When I executed as suggested by you and @nthampi I found that new records are getting inserted but I guess if we are running it daily then the approach will work but if we want to run once a month or something like that for eg : last run date is 04-APR-2019 again I will run on 04-MAY-2019 in this case If I take MAX(LAST_UPDATE_DATE) then it takes only the records from the date 04-MAY-2019 leaving behind the records for one month. Did I miss anything Kindly guide further

Thanks,

Sowmiya R

Re: Incremental Load

Anonymous — Thu, 04 Apr 2019 12:59:09 GMT

Hi,

Once you execute the data between last run time and current time (during first run), the next step is to capture the current time (I mean the end time of current run) back to the DB.

This will be the start date for the next month. In your example, 04-APR-2019 was the first time you ran the job. So after job execution, you need to capture this date to the DB. So when the next time in May you are running the job, it will fetch all the records between 04-APR-2019 and 01-May-2019.

The key part is to store the date of current run back to DB so that we you are executing next time, it will take the full delta range instead of current date.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Incremental Load

Anonymous — Thu, 04 Apr 2019 13:18:08 GMT

Hi @nthampi ,

I have done this approach already and working fine but the customer doesn't want to create a new table in DB and want the Talend itself to capture the last run time and pick up the time while executing the next time. SO I'm looking for any other alternative approach that Talend alone can handle

Thanks,

Sowmiya R

Re: Incremental Load

joearun4u — Thu, 04 Apr 2019 13:37:33 GMT

Hi Sowmiya,

Definitely It will work..

You have to capture the Max(lastUpdateDate) along with time for eg, 04-Mar-2019 12:37:08. Then you have to pass the value to input query as 'select * from <SourceTableName> where lastUpdatedate > '04-Mar-2019 12:37:08'. So it will fetch all the records which is greater than the given date '04-Mar-2019 12:37:08'.

Thanks,

Joe

<Don't forget to give kudos>

Re: Incremental Load

Anonymous — Thu, 04 Apr 2019 18:48:16 GMT

Hi Sowmiya,

Could you please share what is the compelling reason shared by customer for not using the DB approach? It is quite an established approach used by many enterprise customers throughout the globe.

So I am really interested to know why the customer is afraid to have a configuration table to handle this data. If customer is looking to store the information inside Talend, we need to educate the customer that Talend is an ETL tool and not a data storage medium.

Our Talend community members can definitely shed some light in this area if customer would like to discuss on this part in Talend Community through this post itself.

Apart from that, I believe the original question for this post is already covered by the posts. If you are happy with the details provided for using DB in this scenario, could you please mark the topic as resolved by marking all the posts which helped you to arrive at the solution?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Incremental Load

Vijay_K_N — Mon, 10 Feb 2020 06:05:46 GMT

hi sowmya,

can you share the incremental job to me

Re: Incremental Load

sushantk19 — Fri, 17 Apr 2020 14:51:11 GMT

Hi,

Can you help me create incremental job. I am new to Talend.

I am attaching the context variable defined by me at staging level. we have 2 fields updated_at(coming from source system) and Load_at (defined for capturing the data load time) in staging table. I am getting the below error when i run the talend job;

Null value will be used for context parameter last_run: Unparseable date: " max(load_at)"
[statistics] connecting to socket on port 3920
[statistics] connected
Exception in component tDBInput_1 (job_Staging_000_sylius_Address)
java.sql.SQLSyntaxErrorException: Unknown column 'context.last_run' in 'where clause'

Steps followed by me are as below,

1. create a context variable in job.

2. Use this context variable in DB input query as attached

3. run the job.

what is issue in this design? i am not creating any DB table to store this value.

DB_input.png
incremental_context.png

Re: Incremental Load

sushantk19 — Mon, 20 Apr 2020 11:57:33 GMT

Hi Sowmiya, Can you please share your job design and screenshot of your context variable defination. I have a same scenario. My source is like MySQL DB and Target is Redshift DB.

Re: Incremental Load

sushantk19 — Mon, 20 Apr 2020 11:59:41 GMT

@manodwhb: can you please share your job design and screenshot of your context variable defination. I have a similar scenario. My source is MySQL Db and target is Redshift DB.

Re: Incremental Load

Vijay_K_N — Mon, 20 Apr 2020 17:53:28 GMT

context variables are passed this way

"select * from sylius_address where updated_at> "+context.lastrun+"";