Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
mertez
Contributor III
Contributor III

[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
Labels (2)
1 Solution

Accepted Solutions
_AnonymousUser
Specialist III
Specialist III

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.

View solution in original post

4 Replies
Anonymous
Not applicable

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.
mertez
Contributor III
Contributor III
Author

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.
_AnonymousUser
Specialist III
Specialist III

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.
Anonymous
Not applicable

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.