topic Re: Database Lookup in Talend Studio

Database Lookup

Anonymous — Sat, 16 Nov 2024 12:48:50 GMT

Hi,
I have a process to fetch the data from data base and write it to a file.
Requirement as follows
Fetch the data from MAIN table, for every record I need to have a lookup into another table to fetch some more data.
I have created a job as shown in the attachment. Problem here is in the lookup table I have huge data.
When I use query without condition it is working fine, but it is taking more time.
My question is how can I apply the condition that will fetch only required records from lookup table.
Example:
Working Example:
MAIN table query is as follows
"select SECURITY_NAME,SEDOL,CUSIP,ISIN,RIC,msci_security_code from MSCI_DNSA where ISO_COUNTRY_SYMBOL='"+(String)globalMap.get("output.IsoCountrySymbol")+"'"
Lookup table query is as follows
"select sec_code,ticker,exchange from SECURITYINFO"
Not working Example:
MAIN table query is as follows
"select SECURITY_NAME,SEDOL,CUSIP,ISIN,RIC,msci_security_code from MSCI_DNSA where ISO_COUNTRY_SYMBOL='"+(String)globalMap.get("output.IsoCountrySymbol")+"'"
Lookup table query is as follows
"select sec_code,ticker,exchange from SECURITYINFO where sec_code='"+(String)globalMap.get("row3.msci_security_code")+"'"
Can you please suggest on this.
Thanks & Regards,
Syed

Re: Database Lookup

Anonymous — Wed, 13 Jul 2011 08:53:19 GMT

are these in the same DB?

Re: Database Lookup

Anonymous — Wed, 13 Jul 2011 08:59:56 GMT

Hi,
I have both requirement.
Yes, they are in same DB but different schemas.
Thanks & Regards,
Syed

Re: Database Lookup

Anonymous — Wed, 13 Jul 2011 10:01:19 GMT

Hi Syed
Have a try to design the job as below and see if it has a bit improvement of performance,
......tOracleInput(msci_dnia)--row3--tFlowToIterate--iterate---tFixedFlowInput---main--tMap--main--tFileOutputDelimited
|
lookup
|
tOracleInput
on tFixedFlowInput: define the current iterate row
and now you can define the lookup query as below:
"select sec_code,ticker,exchange from SECURITYINFO where sec_code='"+(String)globalMap.get("row3.msci_security_code")+"'"
Best regards
Shong

Re: Database Lookup

Anonymous — Wed, 13 Jul 2011 11:23:40 GMT

Hi Shong,
I have created the job as you suggested, but still same problem.
Lookup is not fetching the corresponding value. I think the globalMap value is not available to Lookup DB.
Please Suggest.
Thanks & Regards,
Syed

Re: Database Lookup

Anonymous — Thu, 14 Jul 2011 05:45:43 GMT

Hi Shong,
Can you please suggest to solve this issue.
Thanks & Regards,
Syed

Re: Database Lookup

Anonymous — Thu, 14 Jul 2011 06:20:11 GMT

Hi,
Please do not post in this topic. Create a new topic.
Thanks & Regards,
Syed

Re: Database Lookup

Anonymous — Thu, 14 Jul 2011 07:37:13 GMT

Hi Syed
Move the tMap component into a child job should be ok, for example:
father job:
......tOracleInput(msci_dnia)--row3--tFlowToIterate--iterate---tRunJob
child job:
tFixedFlowInput---main--tMap--main--tFileOutputDelimited
|
lookup
|
tOracleInput
on tRunjob: call the child job, pass the current iterate row to child job using context variables.
in child job, you can define the lookup query as below:
"select sec_code,ticker,exchange from SECURITYINFO where sec_code='"+context.sec_code+"'" //context.sec_code is one of variables in child job.
Best regards
Shong

Re: Database Lookup

Anonymous — Thu, 14 Jul 2011 09:13:49 GMT

Hi Shong,
I have a complex job as shown in the attachment. Which can have many lookups.
My requirement should be in one job. Dividing it in many jobs will make the more complex.
The 'tRunJob' is not accepting subjobs, it requires only jobs.
Please suggest me how can I solve this within a job.

Thanks & Regards,
Syed

Re: Database Lookup

Anonymous — Thu, 14 Jul 2011 10:06:03 GMT

Hi
As your job showed, you should be able to get the value of one column of row1 and set the query as below on lookup component:
"select sec_code,ticker,exchange from SECURITYINFO where sec_code='"+(String)globalMap.get("row1.msci_security_code")+"'"
Best regards
Shong

Re: Database Lookup

Anonymous — Thu, 14 Jul 2011 11:32:38 GMT

Hi Shong,
I have tried the same, but the problem is lookup component is not part of job process.
Because of lookup component is linking to tMap as a different process, we are not able to get the values which
are part of main job.
Please suggest on this.
Thanks & Regards,
Syed

Re: Database Lookup

Anonymous — Fri, 15 Jul 2011 05:32:51 GMT

Hi Shong,
Please suggest to solve this Issue.
Even I have tried with context variables using 'tJava' as shown in the screen shot.
This also not working.
Thanks & Regards,
Syed

Re: Database Lookup

Anonymous — Fri, 15 Jul 2011 05:35:38 GMT

Used the query as follows
"select sec_code,ticker,exchange from SECURITYINFO where sec_code='"+context.getProperty("sec_code")+"'"

Re: Database Lookup

Anonymous — Fri, 15 Jul 2011 07:31:14 GMT

Hi Shong,
Please suggest to solve this Issue.
Even I have tried with context variables using 'tJava' as shown in the screen shot.
This also not working.
Thanks & Regards,
Syed

Hi,
I don't understand it don't also work in tJava component. Is there a column called msci_security_cod in row1? Can you upload a screenshot of schema of row1?
Best regards
Shong

Re: Database Lookup

Anonymous — Fri, 15 Jul 2011 07:50:28 GMT

Hi Shong,
Please find the screen shots for the query results from schema.

Thanks & Regards,
Syed

Re: Database Lookup

Anonymous — Fri, 15 Jul 2011 08:27:02 GMT

Hi
Have a try to upcase all column name in schema and query,
msci_security_code to MSCI_SECURITY_CODE, (String)gloalMap.get("MSCI_SECURITY_CODE")
Best regards
Shong

Re: Database Lookup

Anonymous — Fri, 15 Jul 2011 10:16:50 GMT

Hi Shong,
There is no problem in queries because I am able to print the values in 'tJava'.
If you see in one of my screen shot for 'tJava' there I am setting the 'msci_security_code' value in context
and I am retrieving it from context and printing. This is working means I am able to fetch the value from context in 'tJava'
But when I try to fetch the same in lookupDB is null.
I think you understood where I am facing the problem.
Thanks & Regards,
Syed

Re: Database Lookup

Anonymous — Fri, 15 Jul 2011 10:22:32 GMT

Hi Syed
Yes, I know your problem! For me, it should work with your job, see attachment, of course, I also tested it on my local machine and it works!
Best regards
Shong

Re: Database Lookup

Anonymous — Fri, 15 Jul 2011 10:23:21 GMT

Re: Database Lookup

Anonymous — Fri, 15 Jul 2011 10:25:16 GMT

Hi
yes, I know your problem! For me, it should work with your job, see attachment. Of course, I also tested it on my local machine.
Best regards
Shong