DATA FLOWS IN SAP DATASPHERE (DSP) – Part 3: Using SQL Table Functions and incorporate them into your Data Flow
Disclaimer
SAP recently announced the launch of SAP Datasphere, which is the next generation of SAP Data Warehouse Cloud. Therefore, also the name from SAP Data Warehouse Cloud changed to SAP Datasphere. This blog, which was posted ahead of this change, refers to SAP Data Warehouse Cloud. Everything which is mentioned within the post is also true for SAP Datasphere.
Introduction
In our third part of this series, we want to show why you might need to use an SQL View with a Table Function and how you can incorporate that in your Data Flow. We came up with this Blog idea as we recently wanted to remove characters by using a regular expression, widely known as Regex.
For our use case we could also use the script operator as Pandas has various functions for Regex functionalities but as we have seen this might lead to slow performance when dealing with a high volume of records.
At first, we also checked if the Projection Operator has a regex function, but we could not find any. Also, not in the documentation provided by SAP https://help.sap.com/docs/SAP_DATA_INTELLIGENCE/fd995896a5f841c696d2b6825d39f755/d1a49cf88cd8483b8cc2a6e216094fcc.html?locale=en-US.
Building the SQL Script (Table Function)
In our setup we started then to build a standard SQL View first and would like to duplicate our column street and have it without any numbers.
The function we would like to use is the REPLACE_REGEXPR documented here https://help.sap.com/docs/HANA_SERVICE_CF/7c78579ce9b14a669c1f3295b0d8ca16/dcf1045ce51d45119cfe8ba17cf9da4f.html .
Unfortunately, we receive a few error messages when we try to use a Standard SQL View.
Therefore, we tried to switch our SQL View to a SQLScript (Table Function).
Now it is necessary that we adjust our SQL Script and save and deploy our View. And we now also have Syntax Support after switching the Language.
After our view has been deployed it is possible to preview our data and check if everything works as expected.
At this point we would like to note two things we came across when working with a view of type SQLScript (Table Function):
1. The columns you define in the “Model Properties” need to match the one you are returning within your SQL Script. Here you can add and remove columns as you wish.
2. We tried to add a constant within our SQL Script and always received an error in our Data Preview, although we did not receive any errors in the Validation.
3. As soon as you have deployed a View as Standard SQL View or as a SQL Script (Table Function), there is no switching back anymore. So, if you need a certain functionality at a later point, rebuilding a Data Flow or something similar might be needed as an easy change is not possible anymore.
Using a View in a Data Flow
Now as we have built our view, we can incorporate this in a Data Flow. We simply add our view and a target and deploy it.
After we executed the Data Flow successfully, we can see that the result of the view has been written in the target successfully.
Conclusion
In this blog we saw how to utilize SQL Script Table Functions in a view to implement your logic and integrate it in a Data Flow. Of Course, the example shown here is very simple and with this type of SQL View you can build way more complex logics. Our use case could have also been built easily with the Script Operator, but as shown this might have some disadvantages when it comes to performance.