Combine / Collapse multiple transformation into one SQL in Dremio

1

Hi ,

I have use case where there are 50 transformations , basically compare two fields in the table and create a new field

field_1=field_1_0 as field1_match
field_2=field_2_0 as field 2- match

when i create this transformation Dremio is creating a quite large SQL as each step a new SQL Subquery and ends up with massive nested Subqueries.

My Question is , is there any utility which can collapse / optimise all the nested into one single Query. The issue i am facing the big nested query is pushed down to the database which is quite slow.

Understand that my collapsing, we miss the traceability of transformation , which is fine with me.
Is there any external tool which can take big nested SQL into one SQL non-nested one ?

Thanks in advance

2

Hi @Sathish_Senathi

I guess from what you are saying you are using the UI to build the SQL. Have you tried to manually create the SQL?

Thanks
@balaji.ramaswamy

3

Hi Balaji,

I understand i can write RAW SQL, but the value of Dremio is that i dont have to know SQL and still get high performant SQL written for me.

In this case, i am concerned that the SQL generated by Dremio seems ot not very optimised. My Question is whether this feature is in roadmap

I would like an option to Optimize ( but loose the step and step transformation visibility)