SQL Query Push Down to external Source

[amos_mastel]

We have connected several external sources to a HANA database and included them as virtual tables. From this tables we want to include the data into our reporting queries to implement a so called drill down scenario where the user can report on data loaded in the HANA database and then drill down to detail data from the external source.

What we now see is that the HANA database does not push down the sql query, it does read the full content of the data table and then try to run the selection in the HANA database. The sources we connect are hadoop and adls storages and contain billions of records, so this always ends in a timeout. We have created database views with specific data type definitions to abstract the untyped (strings) raw data. Is there any setting we can do that the query is passwed down to the spark cluster we use for the data selection?

We currently cannot use the SAP HANA Spark controller from an architecture perspective.

[DotEiserman]

Hi Amos,

I often see my queries being "pruned" when I use functions that are not supported by the adapter, in this case HANA holds them back, fetches all the data and then applies the function in HANA. You can easily see what is supported by your adapter by calling the following procedure and comparing the results with the functions you've used in your query, it really could be something as simple current_date or add_days that is throwing you off:

CALL GET_REMOTE_SOURCE_PROPERTIES (<remote_source_name>, ?)

Documented here:

https://help.sap.com/viewer/6b94445c94ae495c83a19646e7c3fd56/2.0.05/en-US/d77d39343e9d4f5d9aac084e0c...

Thanks

Dot