Introduction

OData service, a kind of RESTful service, is a very popular topic today.  By using OData service, you can query, update, insert and delete data through a unified way using HTTP. 
The deployment of an OData service is very simple and rapid in HANA, by using the XS Engine.   On the other hand, for doing some data analysis development, it is also very flexible and rapid using R.  So what about bring these two stuffs together?
This article illustrates a way to combine the OData service and R together, in order to deploy a predictive analysis OData Service in HANA really simple and fast. 
The key technology here is to use the RESULT VIEW of a procedure.  By using RESULT VIEW, developers can call this procedure by simply querying this RESULT VIEW.  An OData service in HANA can be set to query on a result view, which makes calling a procedure through the OData Service possible. 
Here I’ll use an example to illustrate how to combine the OData service and R procedure.
In this example, I write a simple R procedure to calculate the mean and standard deviation of one column of a table.

Preparation

We assume that you have a workspace in HANA Studio to track a HANA system.  In this example, our HANA system’s SID is: “HDB”, and instance number is: “00”.  Our workspace name is: “dm-dingshun_HDB_00”. 
Also we assume you have created a HANA XS project for this demo, and have shared this project to the workspace you want to apply.  In this example, our project name is “TestOData”, and the content of this project will be deployed to the “helloodata” package in the HANA instance.  After the project is created and shared.  You will find the project in the Project Explorer:

/wp-content/uploads/2013/10/blog_odata_01_293698.png

Create the Schema and Data Table

The first step is to create the schema of this demo.  This step is optional.  You can also use an existing schema and existing data tables.  Here we will create a schema called ‘HELLO_ODATA’.  We create a file called ‘HELLO_ODATA.xsschema’ in our project, and write down the following contents:

schema_name="HELLO_ODATA";

Then you activate this file.  After that, you’ll see the schema in this HANA instance.

Then, you create a table for input data in this demo.  We create a file called “otable.hdbtable” .  Here is the content:

table.schemaName = "HELLO_ODATA";
table.tableType = COLUMNSTORE;
table.columns = [
    {
        name = "ID";
        sqlType = INTEGER;
        nullable = false;
       
    },
    {
        name = "NAME";
        sqlType = NVARCHAR;
        nullable = false;
        length = 20;
        comment = "dummy comment";
    },
    {
        name = "VAL";
        sqlType = DOUBLE;
        nullable = true;
        defaultValue = "0";
    }
];
table.primaryKey.pkcolumns = ["ID"];

Then you activate this file, and you can find this table in the Catalog of this instance:

/wp-content/uploads/2013/10/blog_odata_02_293723.png

OK.  You can insert some data into it.  It might possible that the user you used could not insert data on it.  You can firstly grant privileges on the user.  Here I use the user “SYSTEM”:

CALL _SYS_REPO.GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT(
    'select', 'HELLO_ODATA', 'SYSTEM'
);
CALL _SYS_REPO.GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT(
    'insert', 'HELLO_ODATA', 'SYSTEM'
);
CALL _SYS_REPO.GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT(
    'update', 'HELLO_ODATA', 'SYSTEM'
);
CALL _SYS_REPO.GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT(
    'delete', 'HELLO_ODATA', 'SYSTEM'
);

Then insert some data into it:

TRUNCATE TABLE HELLO_ODATA."helloodata::otable";
INSERT INTO HELLO_ODATA."helloodata::otable" VALUES (1, 'AAA', 1.1);
INSERT INTO HELLO_ODATA."helloodata::otable" VALUES (2, 'BBB', 2.2);
INSERT INTO HELLO_ODATA."helloodata::otable" VALUES (3, 'CCC', 3.3);
INSERT INTO HELLO_ODATA."helloodata::otable" VALUES (4, 'DDD', 3.1415926);

Create the R procedure 

The script of the R procedure is really easy: It calculates the mean and standard deviation of the ‘VAL’ column of the table.  Here is the script to create the output table type as well as the procedure.  Open a SQL console and execute the following script:

DROP TYPE HELLO_ODATA.DUMMY_OUTPUT_T;
CREATE TYPE HELLO_ODATA.DUMMY_OUTPUT_T AS TABLE(
    KEY VARCHAR(64),
    VAL DOUBLE
);
DROP PROCEDURE HELLO_ODATA.DUMMY_R_PROC;
CREATE PROCEDURE HELLO_ODATA.DUMMY_R_PROC(
    IN input1 HELLO_ODATA."helloodata::otable",
    OUT result HELLO_ODATA.DUMMY_OUTPUT_T
)
LANGUAGE RLANG READS SQL DATA
AS
BEGIN
    result <- data.frame(
        KEY=c("Average", "Standard Deviation"),
        VAL=c(mean(input1$VAL), sd(input1$VAL))
    )
END;

Key Part: Creating a Result View

Next thing we need to do is to create a result view on that.  This step is the most important part of the whole process.  Because the user cannot specify the input table name, we need to wrap a SQL script procedure on this R procedure, with no input parameters and only one output parameter, specify the input table inside it, and expose it as a result view.  If you create a read only procedure with only one output parameter, you can set it as a result view.  Here is the code snippet:

DROP VIEW HELLO_ODATA.DUMMY_R_RESULT_VIEW;
DROP PROCEDURE HELLO_ODATA.DUMMY_R_WRAPPER;
CREATE PROCEDURE HELLO_ODATA.DUMMY_R_WRAPPER(
    OUT result HELLO_ODATA.DUMMY_OUTPUT_T
)
LANGUAGE SQLSCRIPT READS SQL DATA
WITH RESULT VIEW HELLO_ODATA.DUMMY_R_RESULT_VIEW
AS
BEGIN
    inputTable = CE_COLUMN_TABLE(HELLO_ODATA."helloodata::otable");
    CALL HELLO_ODATA.DUMMY_R_PROC(:inputTable, result);
END;

In this procedure, we specify the procedure a ‘read only’ one with “READS SQL DATA”, and assign this procedure to a Result view, the name is: “HELLO_ODATA.DUMMY_R_RESULT_VIEW”.  After creating this procedure, an extra result view can be seen on the Column View folder:

/wp-content/uploads/2013/10/blog_odata_03_293756.png

OK.  You can try on the result view.  When you query on this result view using ‘SELECT’, it would actually call the wrapper SQL Script procedure, which in turn calls the R Procedure.  Very interesting.

/wp-content/uploads/2013/10/blog_odata_04_293757.png

Expose the Result View as an OData Service  

The last step is to expose this result view as an OData service.  We create a file in our project called “hello.xsodata”, and specify the data sources need to be exposed:

service {
    "helloodata::otable" as "MyTable";
    "HELLO_ODATA"."DUMMY_R_RESULT_VIEW" as "ResultView"
    keys generate local "GenID";
}

Here we exposed two entries, separated by ‘;’.  The first entry is our input table, and the exposed name is “MyTable”, and the second is our result view, named “ResultView”. 

Then you need to create an empty file called ‘.xsapp’, which means this package is ready to receive external HTTP requests. 

Also an authentication file, called ‘.xsaccess’, is needed.  This file specifies which database users are allowed to access this package contents through URL.  Here is the content:

{
    "exposed": true
} 

Now you can have a try on the web browser:  Type in the following URL to retrieve the input table: 

http://<host>:<8000 + instance #>/helloodata/hello.xsodata/MyTable

/wp-content/uploads/2013/10/blog_odata_05_293758.png

Type in the following URL to retrieve the analysis result using R:

http://<host>:<8000 + instance #>/helloodata/hello.xsodata/ResultView

/wp-content/uploads/2013/10/blog_odata_06_293762.png

By default the data is retrieved as an ATOM format.  You can add the query parameter: $format=json to make it transferred as the JSON format:

/wp-content/uploads/2013/10/blog_odata_07_293763.png

Now let’s try something interesting:  You can insert data into the input table by sending an HTTP request.  There is a command line tool in Linux to issue HTTP requests: ‘curl’.  Type in the following command and see what happened:

curl -H 'Authorization: Basic U1lTVEVNOm1hbmFnZXI=' \
-H 'Content-Type: application/json' \
-X POST 'http://dm-dingshun.dhcp.pvgl.sap.corp:8000/helloodata/hello.xsodata/MyTable' \
-d '{"ID": 5, "NAME": "EEE", "VAL": "5.5"}'

/wp-content/uploads/2013/10/blog_odata_08_293764.png

Note here we send a HTTP POST command to our Input table URL, with the header ‘Authorization’ and ‘Content-Type’, as well as one line of data: {5, “EEE”, “5.5”}.  The content of an ‘Authorization’ key can be obtained through some Web tracing tools.  For example, here is how to get the value from Google Chrome:

/wp-content/uploads/2013/10/blog_odata_09_293766.png

Now when you retrieve the input table through URL again in the web browser, you can see that the data is actually inserted into the table!

/wp-content/uploads/2013/10/blog_odata_10_293767.png

Now when we retrieve the analysis OData, the data is also changed.  Average is changed from about 2.43 to about 3.048, and standard deviation is changed from about 1.014 to about 1.6278:

/wp-content/uploads/2013/10/blog_odata_11_293772.png

OK.  Up to now, we have built a complete analysis OData service using R.  It’s really simple and deployment process is quite fast.  We can easily build a Web application or a smartphone application on top of that.

Discussion about Parameter Issue

Here we use the result view with no input arguments.  It might be possible that we build a calculation view accepting some input parameters.  The HANA OData service configuration files support parameters for an OData data source.  You can use the configuration ‘parameters via entity’ to make it a data source accepting parameters.  You can refer to this article: http://scn.sap.com/community/developer-center/hana/blog/2013/01/22/rest-your-models-on-sap-hana-xs

Conclusion

Throughout the whole process of building this OData service utilizing R.  The key steps can be summarized as follows:

  1. Input data is needed. 
  2. Create one or several R procedure(s) doing the (different) analys(es)
  3. Create one or several wrapper procedure(s), each for one R procedure, and export it(them) as a result view(s)
  4. Build the OData service, making the result view(s) as the data source. 

The process of building an OData service utilizing R is very simple and fast.  Developers only need to write very little code to make it work. 

Building this kind of analysis OData service makes it possible to doing some data analytics through a HTTP interface, which could be a data source for a Web application or smartphone application.

Reference

  1. SAP HANA Developer Guide  (http://help.sap.com/hana/SAP_HANA_Developer_Guide_en.pdf)
  2. SAP HANA SQLScript Reference  (http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf)
  3. REST your Models on SAP HANA XS (http://scn.sap.com/community/developer-center/hana/blog/2013/01/22/rest-your-models-on-sap-hana-xs)
To report this post you need to login first.

1 Comment

You must be Logged on to comment or reply to a post.

  1. Raf Boudewijns

    Very nice, Shun Ding! However, I believe you made a little mistake in the “Create the Schema and Data Table” paragraph. According to me the extension for the filename to create the schema should be “.hdbschema” (instead of “.xsschema”).

    Other than that, pretty cool stuff!

    (0) 

Leave a Reply