SQL Querying/Expression/Transformations

[jaina15]

Hi @jmcorreia ,

Do we have capability to pass on some specific SQL statements as it is or like doing aggregations based on multiple columns?
Also, what if multiple bronze/silver tables are source for silver/gold table, then how do we have to pass on the transform spec?
Could you please help me on the above?

Regards,
Shubham

[jmcorreia]

Hi @jaina15,

thank you for the question!
There are different ways you can use SQL Statements, so maybe I would need to understand the use case a bit better.

1. You can pass a dataframe as input for the input_spec, which basically means that you have full flexibility to do a spark.sql(select a, sum(b) from table group by a) and have it as input.
2. You can also define custom_transformations, any time you don't find any transformation suitable for your use case. There is an example here https://github.com/adidas/lakehouse-engine/blob/master/tests/feature/data_loader_custom_transformer/test_data_loader_custom_transformer_calculate_kpi.py, https://github.com/adidas/lakehouse-engine/blob/master/tests/feature/data_loader_custom_transformer/test_data_loader_custom_transformer_delta_load.py

In regards to joins, here you have examples:

• https://adidas.github.io/lakehouse-engine-docs/lakehouse_engine/transformers/joiners.html#Joiners.join
• https://github.com/adidas/lakehouse-engine/blob/master/tests/resources/feature/transformations/joiners/batch.json

I hope it helps. Please close the question, if it is solved :) .

Best Regards,
José

[jaina15]

Hey @jmcorreia ,

Thanks for the response!!

Just a follow-up: What if we have more than 2 tables to join? Like a complex join involving around 10 tables? Can we perform join at a time only for 2 tables only?
I was thinking of something like, we pass on all the table names as a df which are involved in the query in some method and then that method can be customized according to the requirements of the user i.e., he/she can write his own complex sql query and then return the result as a df.

[jmcorreia]

Hi @jaina15,
for now the process would be to join 2 tables each time. Alternatively, for complex use cases, you can join your Dataframes before and that pass the resulting Dataframe as a parameter for the input spec (option 1 I shared) or you can join with separate dataframes on a custom transformation (option 2 I shared).
Anyway, a transformer supporting the behavior you described to join several tables at once, could also be useful. If you have ideas, feel free to contribute with the implementation for that transformer :)