topic Re: scanario in Talend Studio

scanario

Anonymous — Thu, 07 Sep 2017 06:17:42 GMT

Hi,

How to implement below logic.

tExcelInput---->tOralceOutput1(table_1)
|
| (onComponentsOk_1)
|
tOracleInput ------>tOralceOutput2(table_2)

table_1

columns: emono, date_col

1, 20-07-2017

2, 20-07-2017

table_2: existing data before load table_1 records from excel file.

emono, date_col,

1, 18-07-2017

2, 18-07-2017

Scenario:

1) In between tOralceOutput1 and tOralceOutput2, Need to check below logic.

if table_1.date_col <> table_2.date_col

wait for process to call ( tOracleInput ------>tOralceOutput2(table_2)

check every 15min continues(infinity loop)

else

if table_1.date_col == table_2.date_col

continues to next step ( tOracleInput ------>tOralceOutput2(table_2)

end if

Thanks

Shree

Re: scanario

Anonymous — Thu, 07 Sep 2017 10:07:37 GMT

I have concerns about the required logic here. You have, in your pseudo code, a line that says....

check every 15min continues(infinity loop)

Talend jobs are batch. They should start, process and end. It is not a great idea to leave them essentially polling every 15 mins. To do this you may want to use Talend ESB.

The other concern with this is the expectation that your dates will match exactly. Are they dates or Strings representing dates? If it is the latter, then this will work (although don't compare them with "="). Otherwise you may want to truncate your dates to just days, since the whole date will go down to milliseconds.

However, before you get to the actual comparisons, you do need to think about whether you have to have a job that *could* run forever. What is the business the requirement you are trying to solve?

Re: scanario

Anonymous — Fri, 08 Sep 2017 03:00:47 GMT

Thanks for your input.

Yes, it is business requirement. Whole job i cant share if want share any gmail ID i can.

Coming to point 1.

I have concerns about the required logic here. You have, in your pseudo code, a line that says....
check every 15min continues(infinity loop)

Talend jobs are batch. They should start, process and end. It is not a great idea to leave them essentially polling every 15 mins. To do this you may want to use Talend ESB.

Ans: Let me check with Talend ESB. Any specific component in ESB i need to work around.

Data type is Date and Oracle DB.

Please let me know any further input or if you have any question?

Thanks

Shree

Re: scanario

Anonymous — Fri, 08 Sep 2017 09:37:57 GMT

With regard to the ESB you will want to look into Routes. Do a bit of reading on Apache Camel routes. It isn't as straight forward as the ETL tool though. You will be wanting to look at this http://camel.apache.org/sql-component.html

Another way you could approach this (which would be safer if using an ETL job) is to schedule the whole job to run every 5 or so minutes. This would remove the need for using the ESB, but would make the job a lot safer. Something to consider. This really does depend on your business requirement I guess.

If you are using Oracle, you can output the Date data truncated to yyyyMMdd which would solve potential matching issues.

Re: scanario

Anonymous — Mon, 11 Sep 2017 06:23:14 GMT

Thanks for inputs.

Let me check with team and will go through Apache Camel routes.

Regards

shree

Re: scanario

Anonymous — Mon, 25 Sep 2017 06:22:34 GMT

Hi,

Could you please help me on any other way to do it? It is urgent.

Thanks and Regards

Shree

Re: scanario

Anonymous — Mon, 25 Sep 2017 09:27:32 GMT

An easier way (but maybe a little bit more restrictive) would be to create the job and not worry about the 15 minute looping logic inside the job. Handle the looping using a scheduling tool. Just call the job every 15 mins.

Re: scanario

Anonymous — Mon, 25 Sep 2017 11:28:42 GMT

Thanks for your inputs.

How to achieve looping here. any specific components?

Can i use tPostjob and tPrejob to achieve this logic?

if both table date matches then create temp file and using twaitforfile components wait for that file until once is received run the 2nd subjob and in tPostjob delete the temp file which created after match condition.

Thanks

Shree

Re: scanario

Anonymous — Mon, 25 Sep 2017 11:58:08 GMT

It is not a good idea to have a Talend job running forever. They are not meant for that. So what I am suggesting is that you create a job to test your logic BUT don't include the waiting functionality. So the flow would be like below....

Scenario:

1) In between tOralceOutput1 and tOralceOutput2, Need to check below logic.

if table_1.date_col <> table_2.date_col

wait for process to call ( tOracleInput ------>tOralceOutput2(table_2)

~~check every 15min continues(infinity loop)~~

else

if table_1.date_col == table_2.date_col

continues to next step ( tOracleInput ------>tOralceOutput2(table_2)

end if

Instead of checking every 15 mins within the job, schedule the job to be run every 15 mins.

Re: scanario

Anonymous — Mon, 25 Sep 2017 12:34:54 GMT

Sure bro,

Sound good, With going bit deeper level.

1) When i need to run main job and sub job. (tOralceOutput1 table) and tOralceOutput2.

2) How to schedule the job using TAC or any other tools. (we are using open studio).

3) when i need to check dates values from both tables? is there anything create dependencies between them in TAC based on main job condition?

Thanks

Shree

Re: scanario

Anonymous — Mon, 25 Sep 2017 13:04:33 GMT

If you are using Open Studio (the free version) you won't have TAC. In order to schedule, you will need to use a scheduling tool to run your job. There are loads you can choose from (many open source). If you are using Linux you can use the crontab.

I am not sure what your question 3 really means to be honest. But I will say that when comparing dates you have to consider that dates go down to milliseconds. You have to take this into account when comparing dates. The same day will not necessarily match because days are not the smallest unit of time in a date.

Re: scanario

Anonymous — Tue, 26 Sep 2017 10:30:27 GMT

Hi,

Thanks for inputs.

I have few question can you please point out.

1) Can you mention any two open source scheduler.

2) whether i need to create separate jobs for load tOralceOutput1 and tOralceOutput2. if yes, how to schedule the jobs before comparing dates and after comparing? orders to run?

3) how to set dependencies between two jobs if required?

How to implement below logic.

tExcelInput---->tOralceOutput1(table_1)
|
| (onComponentsOk_1)
|
tOracleInput ------>tOralceOutput2(table_2)

table_1
columns: emono, date_col
1, 20-07-2017
2, 20-07-2017

table_2: existing data before load table_1 records from excel file.
emono, date_col,
1, 18-07-2017
2, 18-07-2017

Scenario:
1) In between tOralceOutput1 and tOralceOutput2, Need to check below logic.

if table_1.date_col <> table_2.date_col
wait for process to call ( tOracleInput ------>tOralceOutput2(table_2)
~~check every 15min continues(infinity loop)~~
else
if table_1.date_col == table_2.date_col
continues to next step ( tOracleInput ------>tOralceOutput2(table_2)
end if

Please let me know if you need more informations.

Thanks

Shree

Re: scanario

Anonymous — Wed, 04 Oct 2017 04:36:23 GMT

Hi,

May i know any update on query. Can you please reply my query once you get free time or available time.

Thanks

Shree

Re: scanario

Anonymous — Wed, 04 Oct 2017 09:38:18 GMT

Hi Shree,

I am not sure what you are asking now. Unfortunately I don't really have the time to take on the logic of a complete job. You need to try and extrapolate from what people have said and experiment with it. That is the best way to learn. Another thing you need to think about is reducing the scale of your question and not asking another question on the same thread once the first question is answered. First of all that will help the community, since your smaller questions will likely be answered quicker AND other people may have those questions.

I'm not meaning to be mean, I just think that everyone will benefit from working this way. I also think that breaking a problem down into smaller problems helps you to get your head around the bigger problem and understand what you are doing better

Regards

Richard

Re: scanario

Anonymous — Wed, 04 Oct 2017 10:24:52 GMT

Thanks Richard.

I will try to debug and update on community if any positive result.

Regards

Shree