topic Re: Using Oracle sequence to generte ID while writing in file in Talend Studio

Using Oracle sequence to generte ID while writing in file

vidya821 — Thu, 07 Sep 2017 10:04:30 GMT

Hi All,

I have this requirement wherein input file has below format

Person_ID | Name | DOB

Now, the Person_ID value in the input file can be blank sometimes and so i need to assign Person_ID generated via Oracle sequence,

Can anyone please tell me what is best way to call Oracle sequence for this matter.

(One way is i can create Code Routine in Talend to call every time when Person_ID is blank or null,

The only thing is i have to open, create and close DB connection in the code everytime it is called )

Re: Using Oracle sequence to generte ID while writing in file

Anonymous — Thu, 07 Sep 2017 10:15:27 GMT

You can either use a t{database}Input component and just select the next value. Below is the code I use for a MS SQL Server version of this...

"SELECT NEXT VALUE FOR "+((String)globalMap.get("sequence"))+" as seqnum"

....this allows me to select the next value from a dynamically selected sequence.

Alternatively you can use the Additional Columns functionality. This is explained here....

https://community.talend.com/t5/Troubleshooting-Development/Using-an-Oracle-sequence-while-inserting-data/ta-p/21700

Re: Using Oracle sequence to generte ID while writing in file

vidya821 — Thu, 07 Sep 2017 10:20:06 GMT

Hi Rhall,

I have to assign the Person_ID in tMap component while generating an output file,
in this case how can i use DB input component ?

Re: Using Oracle sequence to generte ID while writing in file

Anonymous — Thu, 07 Sep 2017 10:27:35 GMT

Use the t{Database}Input as a lookup to the tMap. Set the lookup model to "Reload at each row". This will generate a new ID for every row in the main flow. If you do not want a new ID for every row in the main flow, you will have to put some logic in to ensure the main flow does not include that data. You can have another process to load the ID-less data afterwards or before.

Re: Using Oracle sequence to generte ID while writing in file

Anonymous — Thu, 07 Sep 2017 10:32:57 GMT

Alternatively, you can load all data (regardless of whether it needs an ID or not) through the tMap and use some funky logic in the t{database}Input to identify if the next ID needs to be returned. To do this requires a bit of configuration using the "globalMap key" you will see appearing in the tMap when you set the Lookup Model to "Reload at each row". If you add a boolean there to identify if you need a new ID or not, you can pass the boolean value to your Select query. I have actually used logic similar to this, like below...

(globalMap.get("sequence")!=null ? 
"SELECT NEXT VALUE FOR "+((String)globalMap.get("sequence"))+" as seqnum" 
:
"SELECT null")

In the above example I am checking for the presence of a "sequence" to decide whether I return anything or not.

Re: Using Oracle sequence to generte ID while writing in file

vidya821 — Thu, 07 Sep 2017 10:33:31 GMT

The logic to avoid use of sequence if the person id already exists - in the lookup table if i use a filter on Main table Person_ID is blank or null and give outer join along with Reload at each row should help rt ?

But will this always call sequence irrespective of Main Table Person_ID being blank/null or not and then assign the value to the output Person_ID file ?

If this is case then there is a waste of sequence which is not at all feasbile

Re: Using Oracle sequence to generte ID while writing in file

Anonymous — Thu, 07 Sep 2017 10:47:29 GMT

My second post covered the above scenario

Re: Using Oracle sequence to generte ID while writing in file

vidya821 — Thu, 07 Sep 2017 16:47:37 GMT

Thanks, this worked.
I have one more question
when talend component reads the file data using inputfile component then does it loads the data at once in memory or it reads line by line ?

I have a situation here,
I have to generate an output file but before this file i have to generate the person id for person_id column in the file as per by post subject.

There seems to be two ways to do it
1) Create a temp file and then perform the operation of assigning person id and write the data in final file
or
2) before temp file creation, let the data flow to tHashOutput and i can then work on this data and create final file without going for temp file

Can you please provide me inputs for this as performance is a major concern here
Note: The records processed are around 500000 and may increase in future
or any other solution is most welcomed !!

Thanks

Re: Using Oracle sequence to generte ID while writing in file

Anonymous — Thu, 07 Sep 2017 16:52:00 GMT

The DB writes can be done in batches (look at the Advanced tab for options on this).

I would recommend you avoid writing to a temp file if performance is going to be a concern. In this situation (if I understand you correctly), I would use the tHash components. However, you *may* run into memory issues, so keep that in mind.

Re: Using Oracle sequence to generte ID while writing in file

vidya821 — Fri, 08 Sep 2017 08:02:41 GMT

I am not writing into the DB, i need a create an output file from an input file...
here is the scenario,
1) I have an input file with 500000 records with PersonId blank
2) Data exists in oracle data base (similar records or with some extra records)
3) If the file data matches with DB data then i have to get the PersonId from DB record and assign it to the file data
else i have to use DB sequence to assign PersonId to these records
4) Output file should contain the existing input file records with PersonId assigned

Re: Using Oracle sequence to generte ID while writing in file

Anonymous — Fri, 08 Sep 2017 09:41:11 GMT

To be honest, it doesn't matter where you are writing your data, it will be quicker to carry out the intermediary processing in memory rather than writing to a file. Are you not writing the generated ID to the database at all? Not even an update to the record set you are querying?

Re: Using Oracle sequence to generte ID while writing in file

vidya821 — Fri, 08 Sep 2017 11:22:15 GMT

Thanks, my task is till file generation, what exactly is to be done with the file further - am not sure 🙂
anyways i have implented using tHash, now checking the performnce...hope it comes out good

Moreover i m thinkig of using sequence generator in talend to improve performance

Re: Using Oracle sequence to generte ID while writing in file

Anonymous — Fri, 08 Sep 2017 11:27:56 GMT

The sequence generator in Talend would have been my suggestion earlier had I known it was just for a file, but I suspect that you will need to ensure that numbers are not repeated. A sequence handled by the database is better for that. If your IDs can be repeated and there is no need to do anything inside the database, use a Talend sequence.