topic Re: Retrieve Schema from custom SQL query (Component tPostgresqlInput) in Talend Studio

Retrieve Schema from custom SQL query (Component tPostgresqlInput)

Anonymous — Wed, 12 Jun 2013 23:20:24 GMT

Hi,
I am using Talend Open Studio / Date Integration some time in order to read/write database tables. Now I have to read data through a custom SQL query from Postgres.
Job Definition: tPostgresqlInput --> tFileOutputExcel
Database Connection o.k.
Job Run counts the exact number of rows.
Everything looks fine, Job completes, but XLSX-file ist empty. Warning message says sth like "no schema given" and "schema is different from the query" in component tPostgresqlInput.
Klicking "guess schema" in component basic settings seems to make nothing.
Is it possible at all, to automatically retrieve the schema from a custom query?
Thanks in advance.

Re: Retrieve Schema from custom SQL query (Component tPostgresqlInput)

Anonymous — Thu, 13 Jun 2013 03:06:36 GMT

Hi,

Everything looks fine, Job completes, but XLSX-file ist empty. Warning message says sth like "no schema given" and "schema is different from the query" in component tPostgresqlInput.
Klicking "guess schema" in component basic settings seems to make nothing.
Is it possible at all, to automatically retrieve the schema from a custom query?

It is possible to automatically retrieve the schema from a custom query. Such as "Select * from table name", and then click the "guess schema" button in component basic settings.
Best regards
Sabrina

Re: Retrieve Schema from custom SQL query (Component tPostgresqlInput)

Anonymous — Thu, 13 Jun 2013 18:09:56 GMT

Hi Sabrina,
thank you for your reply. Is this solution valid for any type of query? I tried "guess schema" several times (in several configuration variations) - without succes. There shows up a dialog and disappears before I can read.
By the way, my query is not a plain select from one table, but a complex statement with a dozen joins, aggregates, subqueries, resulting in about 200 columns.
I configured Postgresql server 8.4 to log ALL queries and found out, that there is no query sent to Postgresql server when pressing "guess schema".
Kind regards

Re: Retrieve Schema from custom SQL query (Component tPostgresqlInput)

Anonymous — Fri, 14 Jun 2013 07:33:45 GMT

Hi,
The button of "Guess Schema" can guess the column structure of table by sql query but not multiple SQL statement.
See the related forum https://community.talend.com/t5/Design-and-Development/multiple-rows/td-p/110878.
You can use a simple query to check if the "guess schema" option works well?
Best regards
Sabrina