topic Re: Using tdbinput component (oracle) input query and output to csv file - Hanging in Talend Studio

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

Anonymous — Sat, 16 Nov 2024 07:39:09 GMT

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

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

Anonymous — Fri, 14 Sep 2018 17:13:25 GMT

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.

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

Anonymous — Fri, 14 Sep 2018 19:16:34 GMT

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

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

Anonymous — Fri, 14 Sep 2018 19:22:23 GMT

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

screenshot.png

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

Anonymous — Fri, 14 Sep 2018 20:47:58 GMT

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.

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

Anonymous — Mon, 17 Sep 2018 17:39:09 GMT

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')"

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

Anonymous — Mon, 17 Sep 2018 17:45:26 GMT

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

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

Anonymous — Mon, 17 Sep 2018 19:43:56 GMT

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!

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

Anonymous — Mon, 17 Sep 2018 21:27:56 GMT

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...

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

Anonymous — Mon, 17 Sep 2018 21:41:12 GMT

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.

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

Anonymous — Mon, 17 Sep 2018 21:58:34 GMT

Attached is the screenshot of the job from my previous post and query I am trying to run.

I'm wondering if the TX_DATE has the problem because in the database the format is 8/1/2018 12:00:00 AM for a particular record.

So I am trying to convert using to_date to a particular data format.

Am I missing anything?

Please help .

screenshot2.png

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

Anonymous — Mon, 17 Sep 2018 22:21:56 GMT

That looks OK. However, my brain is a not SQL parser and I can make mistakes 🙂 Have you tried running that query in something like TOAD? You should always try out your queries in another tool to get them to work first.

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

Anonymous — Mon, 17 Sep 2018 22:29:18 GMT

Yes , I tried this query first in TOAD and then put the same query in Open studio dbinput component.

It gets executed on Toad and hangs here...

That's the part i'm struggling with 😞

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

Anonymous — Mon, 17 Sep 2018 22:41:04 GMT

OK, if it doesn't appear to complete (or hangs) in Talend, this is not an error. In TOAD the resultset will be broken down into pages of results. The first 1000 records will be return (for example) and they will appear. But the query will continue running in the background. Try limiting the results to the TOP 1000 in Talend. To do this, add....

 AND rownum < 1000

....to your WHERE clause. This should return some records. If that works, your problem is your table probably needs indexing or you need to further filter your query

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

Anonymous — Tue, 18 Sep 2018 16:42:09 GMT

Thanks A LOT!!!!

And for all the quick responses. Rownum<1000 worked and it opened a window to fetch other records....

This has worked for me. Thanks much for your help and suggestions.

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

Anonymous — Thu, 20 Sep 2018 22:44:16 GMT

Another problem with the query.

Can't I remove some of the columns in the dbinput component query? It says schema is different and throws a warning at my component and job fails.

Please advice.

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

Anonymous — Thu, 20 Sep 2018 22:52:38 GMT

The schema must match the selected columns and they must be in the same order. If you remove columns from the query, you will need to remove them from the schema as well

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

Anonymous — Thu, 20 Sep 2018 23:13:54 GMT

Ok, understood. Thank you for the quick one

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

Anonymous — Fri, 21 Sep 2018 03:04:28 GMT

There seems to be a problem in retrieving schemas in Talend Open Studio 7.0.1

The next and finish buttons are greyed out. and the schema is not visible as well.

I saw that this was a bug and it was solved? Any ideas if this bug still remains in OS 7.0.1 version.

I actually tried to resize the window and then tried close test connection etc. but it didn't work.

Any thoughts ? and how I can proceed....

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

Anonymous — Fri, 21 Sep 2018 03:34:28 GMT

Hello,

What's OS version are you using? I cannot reproduce this issue on my end, Win 10 64 bit. Have you tried to restart your studio to see if it is OK with you?

Best regards

Sabrina