topic Re: Oracle - Bulk loading? in Talend Studio

Oracle - Bulk loading?

talendtester — Sat, 16 Nov 2024 09:15:19 GMT

I am getting a little over 3K rows per second using tFileInputDelimited > tOracleOutput.

I have been looking at tOracleOutputBulk and tOracleOutputBulkExec, is there a way to improve the speed without having to install TOS on the Oracle database server?

Re: Oracle - Bulk loading?

Anonymous — Tue, 26 Sep 2017 09:47:41 GMT

Not sure about the bulk components, but in the tOracleOutput component, try to play around with the following settings in 'Advanced Settings':

Commit every x records
Use batch size

Setting the 'Use batch size' setting has proven to help us in the past.

Re: Oracle - Bulk loading?

talendtester — Tue, 26 Sep 2017 19:04:25 GMT

I did see an improvement already when I did "Use Batch Size".

Right now I am committing every 150K and a batch size of 75K.

Re: Oracle - Bulk loading?

TRF — Tue, 26 Sep 2017 19:21:54 GMT

If you want to try the bulk, I think you just need to install sqlldr on the same machine where the job runs (or the Studio when running from there).

Re: Oracle - Bulk loading?

talendtester — Tue, 26 Sep 2017 20:00:46 GMT

I do have sqlldr installed on the same server the TOS job is located.
The Oracle database is on a completely different server.

When I make the job:
tFileInputDelimited > tMap > tOracleOutputBulkExec

The rows go across and look like they processed, but then the job dies with error "java.lang.NullPointerException".

Re: Oracle - Bulk loading?

TRF — Tue, 26 Sep 2017 20:39:43 GMT

There is a lot of reason for this kind of error.

I understand the job doesn't crash when not using bulk operations, but it is not easy to tell the reason of the error with idea of what the job looks like.

Can you share the design?

Also, tell us when the error occurs, immediatly when coming into the bulk component or after a delay?

Share also the complete error message.

Re: Oracle - Bulk loading?

talendtester — Tue, 26 Sep 2017 20:53:14 GMT

The error is happening on the tOracleOutputBulkExec component:

Re: Oracle - Bulk loading?

Anonymous — Wed, 27 Sep 2017 06:59:07 GMT

Hello,

The tOracleOutputBulk and tOracleBulkExec components are used together in a two step process. In the first step, an output file is generated. In the second step, this file is used in the INSERT operation used to feed a database. These two steps are fused together in the tOracleOutputBulkExec component, detailed in a separate section. The advantage of using two separate steps is that the data can be transformed before it is loaded in the database.

Please have a look at this scenario:TalendHelpCentercenario: Inserting transformed data in MySQL database

Best regards

Sabrina

Re: Oracle - Bulk loading?

talendtester — Tue, 31 Oct 2017 21:23:08 GMT

I looked at the link.

Is it saying there are two options for bulk loading data from a file, but because I don't have TOS DI installed on the Oracle database server, I can't use either option, correct?

Option 1:

tOracleOutputBulk and tOracleBulkExec

Limitation: The database server/client must be installed on the same machine where the Studio is installed or where the Job using tOracleBulkExec is deployed, so that the component functions properly.

Option 2:

tOracleOutputBulkExec

Limitation: I wish there was a good example of how to use the two options.

The database server/client must be installed on the same machine where the Studio is installed or where the Job using tOracleOutputBulkExec is deployed, so that the component functions properly.

Re: Oracle - Bulk loading?

Anonymous — Tue, 11 Sep 2018 11:42:11 GMT

Hi,

You can try one more option.

First generate the file which has to load the data in DB in the server where Talend is installed (file location in tOracleOutputBulk).

Then use FTP to transfer the file to the database server to some location and give that location as input in tOraclBulkExec component.

Hope it helps

Thanks,

Kiran

Re: Oracle - Bulk loading?

talendtester — Tue, 11 Sep 2018 16:53:47 GMT

The problem is the component expects TOS to be installed on the database server.

TOS is installed on a virtual machine which is different than the database sever virtual machine.

I don't have permissions to install TOS on the database server virtual machine.