topic Re: [resolved] Custom query in ELT component in Talend Studio

[resolved] Custom query in ELT component

mertez — Tue, 20 May 2014 16:14:12 GMT

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

Re: [resolved] Custom query in ELT component

Anonymous — Tue, 20 May 2014 16:25:27 GMT

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.

Re: [resolved] Custom query in ELT component

mertez — Mon, 09 Jun 2014 21:41:49 GMT

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.

Re: [resolved] Custom query in ELT component

_AnonymousUser — Tue, 22 Jul 2014 18:23:18 GMT

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.

Re: [resolved] Custom query in ELT component

Anonymous — Tue, 22 Jul 2014 19:33:51 GMT

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.