Archive | December 2012

Table User Defined Functions( Table UDF ) in HANA

With the release of HANA 1.0 SP5, not only do we get a new SQLScript editor and debugger, but there are also a few new SQLScript language features introduced with this release as well. Today, I would like to introduce Table UDFs.  Table UDFs are read-only user-defined functions which accept multiple input parameters and return exactly one results table.  SQLScript is the only language which is supported for table UDFs. Since these functions are ready-only, only read-only statements can be used within the function.  So you may not use statements like INSERT, UPDATE or DELETE. Also, any procedure calls within the function must also be read-only.  Currently, you can only create these functions in the catalog via the SQL Editor.  The syntax for creating a new table UDF is pretty straight forward.  For those of you who have been working with SQLScript since the beginning, you may notice the resurrection of the CREATE FUNCTION statement. This statement was used to create SQLScript procedures in earlier versions of HANA.  Of course, it has since been replaced with CREATE PROCEDURE. The CREATE FUNCTION statement will now be used to create UDFs in HANA.  In the following example, I’m creating the table UDF called GET_BP_ADDRESSES_BY_ROLE which accepts one input parameter called “partnerrole”, and returns a table with the structure defined explicitly.  You can also use global types to define the output structure as well. This function will execute one SELECT statement which contains an INNER JOIN of two tables and returns the result set to the output parameter.

CREATE FUNCTION "SAP_HANA_EPM_DEMO".get_bp_addresses_by_role( partnerrole nvarchar(3))
    RETURNS table ( "PartnerId" NVARCHAR(10), "PartnerRole" NVARCHAR(3), 
                    "EmailAddress" NVARCHAR(255), "CompanyName" NVARCHAR(80), 
                    "AddressId" NVARCHAR(10), "City" NVARCHAR(40),
                    "PostalCode" NVARCHAR(10), "Street" NVARCHAR(60) )
 LANGUAGE SQLSCRIPT 
 SQL SECURITY INVOKER AS
 
BEGIN
 RETURN 
   select a."PartnerId", a."PartnerRole", a."EmailAddress", a."CompanyName",
          a."AddressId", b."City", b."PostalCode", b."Street"
           from "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::businessPartner" as a 
           inner join "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::addresses" as b
                      on a."AddressId" = b."AddressId"
                            where a."PartnerRole" = :partnerrole;
END;

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Once the above CREATE FUNCTION statement is executed via the SQL Editor, the new function will then show up in the catalog under the "Functions" folder.

12-7-2012 12-41-25 PM.png

The function can now be used in the FROM clause of your SELECT statements.   You can pass the input parameters as well.

select * from "SAP_HANA_EPM_DEMO".get_bp_addresses_by_role('02');

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

Additional features to UDFs are planned for future support packages, including scalar UDFs, as well as the ability to create UDFs via the HANA Development Workbench directly in your XS projects. Very similar to how we can now create procedures in HANA 1.0 SP5.

Introducing the New SAP HANA SQLScript Debugger

For those who have been working with SQLScript for awhile, you know that it is quite cumbersome to try and figure out what has gone wrong when a procedure does not return the correct results. Of course we have the TRACE statement which can be inserted into the SQLScript procedure which then writes out the intermediate results to a trace table during execution. But this approach is flawed. Firstly, the inspection of the results has to be done after the entire procedure execution is complete.  You can’t see the results in real-time as the code is being executed.  Secondly, and maybe most importantly, what customer will allow you to insert TRACE statements into a procedure directly in a productive environment?  We need the ability to trace variables during execution without having to modify the procedure in any way. Would it not be great for developers, and support personnel to be able to debug SQLScript procedures directly using a real integrated debugger? Well, good news!  With the release of SAP HANA 1.0 SP5, I am pleased to announce the availability of the new SQLScript Debugger!

The new debugger is tightly integrated into SAP HANA Studio.   It uses the standard eclipse “Debug” perspective, so there is basically zero learning curve for those who are familiar with debugging in eclipse.   You can set/remove breakpoints, resume to the next breakpoint, and terminate the debug session. Other step features are still in the planning phases.

12-5-2012 10-43-37 AM.png

The most powerful feature of the debugger is of course the variable evaluation feature.  When the procedure is executed in the debugger, all of the variables are listed in the “Variables” tab including table variables. You can evaluate the values of the variables as you execute each statement of the SQLScript code.   For table variables, you can right-click and choose “Open Preview”.  This will open a tab at the bottom showing the data in table format.

12-5-2012 10-37-43 AM.png

As you probably have already realized, the new SQLScript debugger is a huge deal for HANA developers. We can now debug SQLScript procedures on the fly and see exactly what is going on during execution of each statement.  Currently, the debugger does have some restrictions which we are working to resolve over the next several revisions.  A list of these restrictions is documented in SAP Note 1779803. Finally, look for new videos coming soon to  SAP HANA Academy page showing how to use the new development tools delivered with SAP HANA 1.0 SP5.