Database Lookup

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
22 REPLIES

Re: Database Lookup

are these in the same DB?

Re: Database Lookup

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

Re: Database Lookup

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Database Lookup

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

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

Re: Database Lookup

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

Re: Database Lookup

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Database Lookup

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
Community Manager

Re: Database Lookup

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Database Lookup

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

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

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

Re: Database Lookup

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Database Lookup

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

Thanks & Regards,
Syed
Community Manager

Re: Database Lookup

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Database Lookup

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
Community Manager

Re: Database Lookup

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Database Lookup

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
Community Manager

Re: Database Lookup

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
----------------------------------------------------------
Talend | Data Agility for Modern Business

Re: Database Lookup

Hi Shong,
Please do a favour. Please send me the example which you have tried to the following id.
I will create the same schema and try.
Email Id:sd.shafiullah@gmail.com
Thanks & Regards,
Syed

Re: Database Lookup

Hi Shong,
I had debug the code generated for my job.
Whats happening is first the lookup query gets executed and holds the data into memory then later normal job process
getting executed. So in this case we will be not having the value for where condition. So using the where condition for
the lookupdb is not usefull.
I change the code and debug it, it is working but taking long time to complete the job.
Do you have any other alternate solution for this.

Thanks & Regards,
Syed
One Star

Re: Database Lookup

Can't you include the lookup as part of the main query? That way everything should run in sequence.