SAP HANA: Table Functions

Table Functions are now the recommended script-based artefact whenever a modelling requirement cannot be fulfilled with graphical information views.

In previous versions of SAP HANA, it was possible to create scripted calculation views, which are similar in their key principles but provide less flexibility and performance. In particular, Table User Defined Functions allow an improved movement of filters from client tools down to the source data.

You create design-time Table Functions with the SAP HANA Developer perspective.

It is also possible to convert existing Scripted Calculation Views into Table Functions, with a migration tool available in SAP HANA Studio since SAP HANA SPS11.

Note:

Table functions can return only a single result set.

Table functions are read-only functions. This means that you cannot use insert, update, delete, commit, or exec SQL statement inside a table function. If you have any of these SQL statements in a table function, it will not activate.

Table Functions

• are side-effect free read-only functions that use standard SQL

• can be used as source data in Graphical Calculation Views

• consume data from tables, models, functions, predictive algorithms, etc.

EXAMPLES:

To start, as always, make sure you are in the Development Perspective.

Step 1:  From the Developer Perspective, choose Repositories tab.

Step 2: Select the package where you want to create a Table Function, Right-click the package selects New and choose Other.

Step 3: This brings up the new window and select Table Function and click Next.

Step 4: Now provide a name to this table function. Here I named it as a TBL_FN_DEMO click anywhere outside the box .hdbtablefunction will automatically appended to the name and Click Finish.

Step 5: This opens up a table function with some default code already present and makes up the skeleton of the overall code which is always needed.

Firstly, let’s clean up the comments placed by the system. Remove them by deleting the text between BEGIN and END block.

 Step 6: Let’s understand these rows one by one. The FUNCTION keyword marks the beginning of a SAP HANA function.

"USER2"."KABIL_PRACTICE::TBL_FN_DEMO" ( )

This keyword is followed by the auto-generated complete name of the table function which is a combination of the

USER2 = username

KABIL_PRACTICE = Package Name

TBL_FN_DEMO = Table Function Name

        The set of open and closed brackets “( )” at the end is used to add any Input parameters to this table function. For now, we will leave it blank inside.

RETURNS return_table_type

         The next line starts with a RETURNS keyword. The return_table_type is just a placeholder for the actual code. This is similar to the columns folder in the Scripted view where you specify the list of output fields for the Scripted node.

        We need to remove this return_table_type and provide the structure and datatype of the output we expect from this table function.

        As seen below, we add the keyword TABLE to specify that the output of this function is a table and it will have 4 fields

        Below the BEGIN Keyword we have to write RETURN Keyword. This tells HANA that the select statement you write below this would be the output of this table function.

Step 7: Now, we are required to write the select statement which provides output in the same structure and order as the RETURNS TABLE statement.

Now, write your logic below the RETURN statement as shown as below:

CODE:

FUNCTION "USER2"."KABIL_PRACTICE::TBL_FN_DEMO" ( )

      RETURNS Table(

      "Order_ID" Decimal(8,1),

      "Order_Date" Date,

      "Order_Quantity" Decimal(5,1),

      "Sales" Decimal(31,13)

      )

      LANGUAGE SQLSCRIPT

      SQL SECURITY INVOKER AS

BEGIN

RETURN

select      "Order_ID",

            "Order_Date",

            "Order_Quantity",

            "Sales"

from "KABIL_PRACTICE"."SUPERSTORE_SALES";

END;

Step 8: Finally validate and activate the Table Function.

             you can now notice that an active table function is now available in your package.

Step 9: To check the data consistency of this table function, select Modeler Perspective, from the SQL Console run a below query:

select * from "USER2"."KABIL_PRACTICE::TBL_FN_DEMO" ( ) ;

 Result:

Thank you for visitng My Blog....

Share Your Comments