[resolved] Custom query in ELT component

Four Stars

[resolved] Custom query in ELT component

I am joining some tables in tELTNetezzaMap and I am wondering which component should I use to bring the results of the query into the map component.
Thanks

Accepted Solutions
One Star

Re: [resolved] Custom query in ELT component

ELt components generate the SQL that need that runs in the database (target usually) to perform transformations.  In general they are "Insert Select as".  The transformation could be based on joins, aggregations (sum, count etc.. group by).  There needs to be ELTMap, 1 or more inputs and an output.  The ELTMap (Eg. tELTHiveMap for Hive) is the critical part where you define what the sql needs to be. There is a "Generated SQL for Select...." tab in ELTMap which shows the SQL being generated.
The advantages are:
- When needing to build aggregates or do transformations of data that already exists in target, rather than pulling the data into DI server, the transformation happens in the DB.
- Easy supportability (graphical objects rather than lines of code)
- Generates consistent SQL
The current limitations are:  It cannot handle Derived tables (or inline SQLs in Oracle), generating complex OLAP functions and reading the database to generate scheme (automatically) for these components.  The scheme needs to be provided manually or xml input.

All Replies
One Star

Re: [resolved] Custom query in ELT component

Have you used the ELT components before? They work quite differently than ETL components.
The way I think of it is the the ELT map component creates the sql. The ELT Output component writes the result to a table.
So there is no "bring the results into the map"
These components are mostly about generating SQL rather than Java.
Four Stars

Re: [resolved] Custom query in ELT component

Component are quite limited so at last we switched to the query push down approach . In elt we were trying to make a custom query for the input in the tELTMAP and joining this query with other table, however we could not find such a component where I could define my custom query.
One Star

Re: [resolved] Custom query in ELT component

ELt components generate the SQL that need that runs in the database (target usually) to perform transformations.  In general they are "Insert Select as".  The transformation could be based on joins, aggregations (sum, count etc.. group by).  There needs to be ELTMap, 1 or more inputs and an output.  The ELTMap (Eg. tELTHiveMap for Hive) is the critical part where you define what the sql needs to be. There is a "Generated SQL for Select...." tab in ELTMap which shows the SQL being generated.
The advantages are:
- When needing to build aggregates or do transformations of data that already exists in target, rather than pulling the data into DI server, the transformation happens in the DB.
- Easy supportability (graphical objects rather than lines of code)
- Generates consistent SQL
The current limitations are:  It cannot handle Derived tables (or inline SQLs in Oracle), generating complex OLAP functions and reading the database to generate scheme (automatically) for these components.  The scheme needs to be provided manually or xml input.
One Star

Re: [resolved] Custom query in ELT component

If you want to just stick a query and not Talend generate, I believe you can use tNetezzaRow.  You can stick Insert-Select as query and run it.  Though I haven;t used tNetezzaRow, I have used tHiveRow which behaves the way I mentioned.