topic Re: Incremental Job in talend using sql in Talend Studio

Incremental Job in talend using sql

Anonymous — Fri, 14 Apr 2017 04:45:05 GMT

Dear,

I want to implement a incremental job ,
i want to incrementally pull data from the below query :-

select distinct ID from schema_name.table_name where ID like ‘11%’

I have never used incremental load in talend .

Can you give me steps to build one job ?
Many Thanks,
Amit

Re: Incremental Job in talend using sql

Anonymous — Fri, 14 Apr 2017 06:38:27 GMT

can anyone help? i am using teradata as DB and want to pull records incrementally for this query , we have load_date as one column

Re: Incremental Job in talend using sql

TRF — Fri, 14 Apr 2017 07:01:43 GMT

If you want to get IDs from "1%" to "99%" add a tLoop before tTeradataInput and change the select like this:
"Select ID from schema_name.table_name where ID like '"+((Integer)globalMap.get("tLoop_1_CURRENT_VALUE"))+"%'"

You may also explain the reason why you want to proceed incrementaly for better proposal.

Re: Incremental Job in talend using sql

Anonymous — Fri, 14 Apr 2017 07:57:07 GMT

Dear,

We have a date field , and daily this will run , so i want to pull only current days id's not all id's.

we have to build a CDC (incrementally)

Can u help in this ?

Re: Incremental Job in talend using sql

TRF — Fri, 14 Apr 2017 08:46:36 GMT

Your 1st question wasn't the same or wasn't clear.
Try this:
Select ID from myTable where myDate > current_date
Check the syntax for teradata SQL query.

Re: Incremental Job in talend using sql

TRF — Fri, 14 Apr 2017 08:48:25 GMT

Maybe ">= current_date"

Re: Incremental Job in talend using sql

Anonymous — Fri, 14 Apr 2017 09:30:24 GMT

Dear ,

Thanks , but first time it will be full load then it will be incremental load , will this logic work in this scenario? will i have to store the max date and then compare it with current date or something like that ?
can you let me know steps for the job ?

Many Thanks...

Re: Incremental Job in talend using sql

TRF — Fri, 14 Apr 2017 09:37:19 GMT

Sure in this case you need to have a logic as you suggest, store the last queried date and if it doesn't exists start with a date such as 1900-01-01 to retrieve all the records.
One more time, try to explain your case completly at the 1st question to avoid bad answers.

Re: Incremental Job in talend using sql

Anonymous — Fri, 14 Apr 2017 10:06:28 GMT

Dear,

On daily basis that query will run and will pull the records incrementally , 1st run will pull entire data set and from next day it will pull records incrementally based on date ,

Please let me know steps how shall implement this CDC logic

Re: Incremental Job in talend using sql

cterenzi — Fri, 14 Apr 2017 15:29:51 GMT

To load records created since the last load, you need to store the last run time somewhere. You can write it to a file or to a database. At the start of your load job, read in the value from where you stored it, assign it to a variable (replacing a null value as TRF suggests), then use that variable in your query. After the load is complete, you write the current timestamp to your data store for the next run.

The first time it runs, you won't have a value, so you'll replace it with 1900-01-01 which should retrieve all records. Subsequent runs will use the timestamp you store instead of 1900-01-01.