Using tdbinput component (oracle) input query and output to csv file - Hanging

[Anonymous]

Hello Gurus,

I am trying since many days to as my dbinput as oracle (the connection is successful) in the query section I am writing a query to fetch records for August month 2018 and then I have output connection a a csv file (tFileOutputDelimited) but it is hanging to starting. Not sure why... However I have questions below.

1. Can I write a sql query in the query section of tDBInput component to fetch data for 1 month ? inside the quotes?

2. is there any other component I should be using.

I used cursor option and extended the rows to fetch but it doesn't work.

please help

[Anonymous]

You need to show us your configuration of your job, your input component and show us your query. This description is not enough information I'm afraid.

[Anonymous]

So here is the attached screenshot.

So i'm pretty much new to TOS and created quite a few jobs only. please help if i'm supposed to use any other component to output into a csv file from an oracle sql query. there are billion of records in this table and one month data would be in millions of rows.

thanks for the quick reply.

screenshot.png

[Anonymous]

attached is the run job which says connected and it does not pass any rows... kind of just says starting

screenshot.png

[Anonymous · Accepted solution]

OK, the first thoughts I have about this are as follows...

1) You should not use "Select * From" with Talend. It can lead to issues if your schema does not exactly match the databases expectation of order of columns. I'd recommend manually typing out each column and checking that it matches the columns in the schema.

2) Can you run the query in a different application? A query analyser for example. Does it run there? You may need to index your table.

3) Another thing to do is to go to the Advanced Settings of your tOracleInput component and set the "use cursor" option to ticked. Use the default cursor size.

Once you have done all of the above (well, at least 1 and 3), your job should start running better. However pay attention to 2, your table may need indexing as well. 

[Anonymous]

Thank you very much for your inputs.

I did the way you said ; the job started to run but I don't know how long it will take to finish  as there are billions of records.

 but I have one question.

1. Can I write a where condition to fetch the data for August 2018 only . there are more than 50 million records .

2. I tried to write the query as below but it gets connected and disconnected without any errors.

Please help if I'm missing anything.

select ....

where

to_date( TPMS.T_ACCOUNT_TOLL_18.TX_DATE)

between to_date('2018-08-01','YYYY-MM-DD')

and to_date('2018-01-31','YYYY-MM-DD')"

[Anonymous]

First of all your dates are not just for August 2018. Your query should be ....

select ....

where

 

to_date( TPMS.T_ACCOUNT_TOLL_18.TX_DATE)

between to_date('2018-08-01','YYYY-MM-DD')

and to_date('2018-08-31','YYYY-MM-DD')"

The other thing I noticed is that your dates are stored as Varchar (or I assume they are due to your to_date usage). This is not going to help with any indexes you may have on date unless you are using function based indexes. This is something to consider with so many records

[Anonymous]

Thank you for the quick reply.

the datatype for the column tx_date is date in the database (oracle)

I used to convert it to_date to a particular format just to get august 2018 data in my csv file o/p.

I'm not able to understand why it is not taking the where condition . I tried below 2 things as you mentioned.

1. select * .... from .... where to_date( TPMS.T_ACCOUNT_TOLL_18.TX_DATE)
between to_date('2018-08-01','YYYY-MM-DD')
and to_date('2018-08-31','YYYY-MM-DD')"

2. select .... from where TPMS.T_ACCOUNT_TOLL_18.TX_DATE between to_date('01-08-2018','DD-MM-YYYY') and to_date('31-08-2018','DD-MM-YYYY')

However my query runs on TOAD which I am using to test...

in both cases the job just hangs in starting and does not proceed.

i'm not able to understand what syntax I should give,

Thanks!

[Anonymous]

It looks like I cannot use to_date function like the way I use in Oracle SQL queries.

It has a different format to filter dates in the query tab?

please help gurus...

[Anonymous]

Any query you write in your db components will work if it works with that database type. I suspect the problem is caused by your formatting of the code. To enable your queries to be a bit more dynamic, ALL SQL queries are written as Java Strings. This means you can dynamically change components at runtime, but it also means you have to write your SQL as taking into account the requirements of Java Strings. Can you post exactly what you have put in your DB component. It will be easier to resolve.