newbie: automatic schema creation for query?

One Star

newbie: automatic schema creation for query?

Hi, forgive me if this question has been answered, but I've gone through the intro tutorials and the manual and still am missing something.
I have a select query pulling various volumns from multiple tables. I simply want to export the result set, including column headers, to an Excel file.
I've saved the query in the Repository and dragged it onto the workspace. I have also created a tFileOutputExcel object on the workspace. I can join the two files together and run the process. However, the wrong fields are dumped into the Excel file. When I look at the query details, it looks like it is using an entirely unrelated table-based schema.
Do I need to manually create a schema that contains the columns which I've specified in the query? If so, how do I create a schema that is not based on any one table? If not, how can I tell TOS to export the columns specified in the select query?
Thanks in advance for your help!
Community Manager

Re: newbie: automatic schema creation for query?

Hello
Do I need to manually create a schema that contains the columns which I've specified in the query? If so, how do I create a schema that is not based on any one table? If not, how can I tell TOS to export the columns specified in the select query?

You need to manually create a schema that contains the columns which you have specified in the query, for example:
"SELECT person.id, person.name
FROM person"
you should create a shema include two columns: id and name. When you define the schema, the Db column should be same as the fileds in db table.
I have a select query pulling various volumns from multiple tables. I simply want to export the result set, including column headers, to an Excel file.

You can use serval tDBxxxinput components to select columns from different tables, then link to a tMap components and do some join or merge the data.
Feel free to post your questions!
Best regards

shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: newbie: automatic schema creation for query?

I have a select query pulling various volumns from multiple tables. I simply want to export the result set, including column headers, to an Excel file.

You can use serval tDBxxxinput components to select columns from different tables, then link to a tMap components and do some join or merge the data.

Do we have to use one tDBxxxinput component for each table we want to query in a database?
My situation :
I have an input database and I want to make a query on several linked tables in it.
I defined a Db connection with all the tables schemas in my repository.
I add a tAccessInput component in my job.
The basics settings for the tAccessInput component wants me to choose one TABLE schema only.
I can't figure out what I have to do to be able to specify several table names and schemas for my query.
Thanks for your answer and your help!
Helene.
One Star

Re: newbie: automatic schema creation for query?

Follow the previous post :
When I try to build my query with the SQLBuilder, I can use the designer to define the tables, the fields and the relations I want in my query.
The SQL statement producted is syntaxically correct, but, when I run it, no result appears in the "Query results view"(I just have the SQL statement).
Despite this problem, If I want to use this query in the basics settings of my tAccessInput component (by filling the "Query Type" fields), how can I fill right the other fields ("Table Name", "Table Schema"....) to allow the query to run on all the tables referenced ?
Thanks again for the help.
Helene.

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch