Raspberry Pi on SAP HANA Cloud Platform - without Java - Part 1: XSODATA Backend

[gregorw]

Last year rui.nogueira started a Blog series about his journey to bring temperature sensor data collected by the Raspberry Pi to the SAP HANA Cloud Platform. I want to share here my experience reproducing this scenario by using also the SAP HANA Cloud Platform but without any Java. My implementation is using the following components:

• Node-RED on the Raspberry Pi to collect the temperature sensor data
• HANA XSODATA Service to receive the sensor data from the Raspberry Pi and provide data to HTML5 app
• HTML5 App using XML Views and OData Binding for the SAPUI5 based UI

For all SAP HANA Cloud Services I use the Trial Account which has some limitations especially regarding HANA but is free of charge for developers in non productive use.

HANA XSODATA Service

First of all let's build the HANA XSODATA Service which will be used to persist the data sent from the Raspberry Pi. I provide the source files as there running in my trial account. You have to replace all entries with NEO_3KDKYA4JI7SBY1TZEVG6QA348 with the schema that you find in your HANA DB on the trial account. Also s0001142741trial must be replaced with your own account name which is a combination of <user name> + trial.

I've structured the files like this:

DB Tables

There are two tables to store the data sensors and measurements. I've defined both in the HANA repository using .hdbtable files. Be sure to create them in the NEO_ schema. Otherwise you will have problems allowing access to the tables later on.

sensors.hdbtable

table.schemaName = "NEO_3KDKYA4JI7SBY1TZEVG6QA348";

table.tableType = COLUMNSTORE;

table.description = "Temperature Sensors";

table.columns = [

          {name = "id";          sqlType = BIGINT;    nullable = false;},

          {name = "device";      sqlType = NVARCHAR;  nullable = false; length=50;},

          {name = "type";        sqlType = NVARCHAR;  nullable = false; length=50;},

          {name = "description"; sqlType = NVARCHAR;  nullable = true; length=250;},

          {name = "lastMeasurement";       sqlType = BIGINT;    nullable = true;}

];

table.primaryKey.pkcolumns = ["id"];

measurements.hdbtable

table.schemaName = "NEO_3KDKYA4JI7SBY1TZEVG6QA348";

table.tableType = COLUMNSTORE;

table.description = "Temperature Measurements";

table.columns = [

          {name = "id";       sqlType = BIGINT;    nullable = false;},

          {name = "storedAt"; sqlType = TIMESTAMP; nullable = false;},

          {name = "measuredAt"; sqlType = TIMESTAMP; nullable = false;},

          {name = "sensorId"; sqlType = BIGINT;    nullable = false;},

          {name = "value";    sqlType = DOUBLE;    nullable = false;},

          {name = "unit";     sqlType = NVARCHAR;  nullable = false; length=30;}

];

table.primaryKey.pkcolumns = ["id"];

HDB Sequences

To have unique ID's for the sensors and measurements I've created two HDB Sequences (.hdbsequence)

SensorID.hdbsequence

schema= "NEO_3KDKYA4JI7SBY1TZEVG6QA348";

start_with= 1;

minvalue= 1;

nominvalue=true;

cycles= false;

depends_on_table="s0001142741trial.xsdemo.hello.data::sensors";

MeasurementID.hdbsequence

schema= "NEO_3KDKYA4JI7SBY1TZEVG6QA348";

start_with= 1;

minvalue= 1;

nominvalue=true;

cycles= false;

depends_on_table="s0001142741trial.xsdemo.hello.data::measurements";

Stored procedures

Stored procedures that are intended to be used with an XSODATA service must implement a specific interface. The IN must be the type of the DB table. OUT must be a defined error structure. So let's define first this error structure:

error.hdbstructure

table.schemaName = "NEO_3KDKYA4JI7SBY1TZEVG6QA348";

table.columns = [

{name = "HTTP_STATUS_CODE"; sqlType = INTEGER;},

{name = "ERROR_MESSAGE"; sqlType = NVARCHAR; length = 100;},

{name = "DETAIL"; sqlType = NVARCHAR; length = 100;}

];

As the insert of a new measurement should also update the corresponding sensor entry in the sensor table with the ID of the last measurement I've created this stored procedure:

insertMeasurement.hdbprocedure

PROCEDURE "NEO_3KDKYA4JI7SBY1TZEVG6QA348"."s0001142741trial.xsdemo.hello.procedures::insertMeasurement"

  ( IN row "NEO_3KDKYA4JI7SBY1TZEVG6QA348"."s0001142741trial.xsdemo.hello.data::measurements",

    OUT error "NEO_3KDKYA4JI7SBY1TZEVG6QA348"."s0001142741trial.xsdemo.hello.data::error" )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  DEFAULT SCHEMA NEO_3KDKYA4JI7SBY1TZEVG6QA348

  AS

BEGIN

/*****************************

  Write your procedure logic

*****************************/

declare id bigint;

declare sensorCount int;

declare lv_id bigint;

declare storedAt datetime;

declare measuredAt datetime;

declare sensorId bigint;

declare value double;

declare unit string;

/* select on single fields did not fill variables */

select *

     into id, storedAt, sensorId, value, unit, measuredAt

  from :row;

/* do we have a valid sensorId? */

select count(id) into sensorCount

  from "s0001142741trial.xsdemo.hello.data::sensors"

  where "id" = sensorId;

if sensorCount = 1 then

    /* get next Measurement ID so we can insert that into lastMeasurement column of Sensors */

    select "s0001142741trial.xsdemo.hello.data::MeasurementID".NEXTVAL into lv_id from dummy;

    insert into "s0001142741trial.xsdemo.hello.data::measurements"

      values (lv_id, CURRENT_TIMESTAMP, sensorId, value, unit, measuredAt);

    update "s0001142741trial.xsdemo.hello.data::sensors"

      set "lastMeasurement" = lv_id

      where "id" = sensorId;

    /* Currently don't return an error */

else

    error = select 400 as http_status_code,

            'Provided Sensor ID does not exist' error_message,

            'Please create the Sensor first and then send Measurements' detail from dummy;

end if;

END;

Also for the creation of sensors I switched over to use a XSJSLIB Function as I want to get the ID generated from the HDB Sequence. This is not possible using HDB Procedures. This topic is discussed here: XSOData Post return ID. With the help of the discussion at Manage multi-table insert using OData Service and the Blog OData Service Definition Modification Exist for Tables with an IDENTITY Column I was able to come up with this:

jsexit.xsjslib

/**

@param {connection} Connection  - The SQL connection used in the OData request

@param {beforeTableName} String - The name of a temporary table with the single entry before the operation (UPDATE and DELETE events only)

@param {afterTableName}  String - The name of a temporary table with the single entry after the operation (CREATE and UPDATE events only)

*/

function insert_sensor(param) {

  $.trace.debug("entered function");

  // let before = param.beforeTableName;

  let after  = param.afterTableName;

  // Get next Sensor ID

  let pStmt = param.connection.prepareStatement('SELECT "s0001142741trial.xsdemo.hello.data::SensorID".NEXTVAL FROM dummy');

  var rs = pStmt.executeQuery();

  var SensorId = '';

  while (rs.next()) {

    SensorId = rs.getString(1);

  }

  pStmt.close();

  // Read from temporary table

  pStmt = param.connection.prepareStatement('SELECT * FROM "'+after+'"');

  pStmt.executeQuery();

  rs = pStmt.executeQuery();

  if (rs.next()) {

    // Insert into DB

    pStmt = param.connection.prepareStatement('INSERT INTO "s0001142741trial.xsdemo.hello.data::sensors" VALUES( ?, ?, ?, ?, ? )');

    pStmt.setString(1, SensorId);

    pStmt.setString(2, rs.getString(2)); // Device

    pStmt.setString(3, rs.getString(3)); // Type

    pStmt.setInt(4, 0); // last measurement

    pStmt.setString(5, rs.getString(5));  // Description

    pStmt.execute();

    pStmt.close();

    // Update table provided in the interface to return the generated ID in the returned data of the OData call

    pStmt = param.connection.prepareStatement('UPDATE "' + after

      + '" set "id"        = ?,' +

      '  "device"          = ?,' +

      '  "type"            = ?,' +

      '  "description"     = ?');

    pStmt.setString(1, SensorId);

    pStmt.setString(2, rs.getString(2)); // Device

    pStmt.setString(3, rs.getString(3)); // Type

    pStmt.setString(4, rs.getString(5));  // Description

    pStmt.execute();

    pStmt.close();

  }

  rs.close();

}

XSODATA Service

With the DB tables, sequences and stored procedures in place we can define the XSODATA Service

iotscenario.xsodata

service {

    "s0001142741trial.xsdemo.hello.data::sensors"

        navigates ("sensor_measurements" as "sensor_measurements", "last_measurement" as "last_measurement")

        create using "s0001142741trial.xsdemo.hello.odata:jsexit.xsjslib::insert_sensor";

    "s0001142741trial.xsdemo.hello.data::measurements"

        create using "s0001142741trial.xsdemo.hello.procedures::insertMeasurement";

    association "sensor_measurements" principal "sensors"("id") multiplicity "1"

        dependent "measurements"("sensorId") multiplicity "*";

    association "last_measurement" principal "sensors"("lastMeasurement") multiplicity "1"

        dependent "measurements"("id") multiplicity "1";

}

Authorizations

Before we can access the XSODATA service we must grant access authorizations and expose the XS application

.xsaccess

{

"exposed" : true

}

.xsapp

Just an empty file defining the folder as an XS app.

.xsprivileges

{ "privileges" :

       [ { "name" : "Basic", "description" : "Basic usage privilege" } ]

}

model_access.hdbrole

role s0001142741trial.xsdemo.hello::model_access {

    application privilege: s0001142741trial.xsdemo.hello::Basic;

    catalog schema "NEO_3KDKYA4JI7SBY1TZEVG6QA348": SELECT, INSERT;

    sql object s0001142741trial.xsdemo.hello.data::sensors: SELECT,INSERT,UPDATE;

    sql object s0001142741trial.xsdemo.hello.data::measurements: SELECT,INSERT;

    sql object s0001142741trial.xsdemo.hello.procedures::insertMeasurement: EXECUTE;

    sql object s0001142741trial.xsdemo.hello.procedures::insertSensor: EXECUTE;

}

To assign this authorization to users you must use this special call on the HCP trial:

call "HCP"."HCP_GRANT_ROLE_TO_USER"('s0001142741trial.xsdemo.hello::model_access','S0001142741');

It's very important to execute this command in the NEO DB schema!

Testing

To test the XSODATA Service I've used the Chrome App Postman. For Firefox there are Add-ons like RESTClient which does a similar job. As the HCP trial supports only SAML authentication to access HANA via HTTPS make sure that you've called i.e. the $metadata URL of your OData Service before using Postman or RESTClient. So I try:

https://s2hanaxs.hanatrial.ondemand.com/s0001142741trial/xsdemo/hello/odata/iotscenario.xsodata/$met...

and get:

Create Sensor

Now let's create our first sensor. The URL is https://s2hanaxs.hanatrial.ondemand.com/s0001142741trial/xsdemo/hello/odata/iotscenario.xsodata/sens... and the HTTP Method is POST. The HTTP Headers must be set to:

Header	Value
Content-Type	application/json;odata=minimalmetadata
Accept	application/json;odata=minimalmetadata
Accept-Charset	UTF-8

This is the raw content:

{

  "id": "1",

  "device": "Fish Truck",

  "type": "Raspberry Pi",

  "description": "Salmon"

}

As the result body you should get something like:

{

    "d": {

        "__metadata": {

            "uri": "https://s2hanaxs.hanatrial.ondemand.com:443/s0001142741trial/xsdemo/hello/odata/iotscenario.xsodata/sensors(1)",

            "type": "s0001142741trial.xsdemo.hello.odata.iotscenario.sensorsType"

        },

        "id": "1",

        "device": "Fish Truck",

        "type": "Raspberry Pi",

        "lastMeasurement": "0",

        "description": "Salmon",

        "sensor_measurements": {

            "__deferred": {

                "uri": "https://s2hanaxs.hanatrial.ondemand.com:443/s0001142741trial/xsdemo/hello/odata/iotscenario.xsodata/sensors(1)/sensor_measurements"

            }

        },

        "last_measurement": {

            "__deferred": {

                "uri": "https://s2hanaxs.hanatrial.ondemand.com:443/s0001142741trial/xsdemo/hello/odata/iotscenario.xsodata/sensors(1)/last_measurement"

            }

        }

    }

}

Insert Measurement

As we now have a first sensor in place we can insert a measurement. The URL is https://s2hanaxs.hanatrial.ondemand.com/s0001142741trial/xsdemo/hello/odata/iotscenario.xsodata/meas... and again we're using HTTP POST. Please use the same headers as above. The raw content is:

{

  "id": "1",

  "storedAt": "/Date(1424206653261)/",

  "sensorId": "1",

  "value": "18.625",

  "unit": "°C",

  "measuredAt": "/Date(1424206652682)/"

}

Read Data

As we have now created some data let's read it. When you call the service with the URL parameter $expand=last_measurement also the last measurement will be returned. The complete URL is https://s2hanaxs.hanatrial.ondemand.com/s0001142741trial/xsdemo/hello/odata/iotscenario.xsodata/sens.... Use again the header settings from the above POST requests. But now please use the HTTP GET method. You will get this result:

{

    "d": {

        "__metadata": {

            "uri": "https://s2hanaxs.hanatrial.ondemand.com:443/s0001142741trial/xsdemo/hello/odata/iotscenario.xsodata/sensors(1)",

            "type": "s0001142741trial.xsdemo.hello.odata.iotscenario.sensorsType"

        },

        "id": "1",

        "device": "Fish Truck",

        "type": "Raspberry Pi",

        "lastMeasurement": "1",

        "description": "Salmon",

        "sensor_measurements": {

            "__deferred": {

                "uri": "https://s2hanaxs.hanatrial.ondemand.com:443/s0001142741trial/xsdemo/hello/odata/iotscenario.xsodata/sensors(1)/sensor_measurements"

            }

        },

        "last_measurement": {

            "__metadata": {

                "uri": "https://s2hanaxs.hanatrial.ondemand.com:443/s0001142741trial/xsdemo/hello/odata/iotscenario.xsodata/measurements(132298)",

                "type": "s0001142741trial.xsdemo.hello.odata.iotscenario.measurementsType"

            },

            "id": "1",

            "storedAt": "/Date(1424206653261)/",

            "sensorId": "1",

            "value": "18.625",

            "unit": "°C",

            "measuredAt": "/Date(1424206652682)/"

        }

    }

}

Conclusion

Hope you can follow the above steps and get your XSODATA service running. Let me know via the comments if you encounter any issues. Stay tuned for the next part of this blog series where I will describe what I've used to send the temperature sensor data from the Raspberry Pi to the HCP trial.

[Rui-Nogueira]

Very nice Gregor!!

Will add a link to my blog post and reference to your blog here for those users who want to have a HANA native app instead of a Java app on SAP HANA Cloud Platform.

Best,

Rui

PS: You referenced the wrong Rui Nogueira in your blog post 🙂 That's someone else.

[gregorw]

Hi Rui,

thank you for the feedback and the link. I've updated the link to your profile.

Best regards

Gregor

[tbroek]

Gregor, thanks, you've just filled my weekend agenda :wink:

[former_member208047]

This is awesome. Thanks Gregor

[Former Member]

Thanks for this awesome tutorial.

I really looking forwards for the next part and the implementation on the Raspberry side.

I have try it by my own and have same problems in my python script to work with the SAML authorization – hope you can help me with this :smile:

BR

Rafael

[RishiKhandelwal]

Great one Gregor.

Gonna try this and eagerly waiting for the next part. :smile:

Cheers,

Rishi

[Kinsbrunner]

Hi Gregor,

Nice stuff! I am interested on leveraging my IoT skills and was thinking of buying a Raspberry Pi 2 (and maybe Sense Hat sensors). Do you think this is a good idea? Can this Sense Hat sensors be handled through Java?

Lastly but not less important, I've seen some demos with Java but also yours with XSODATA; which is the "standard" way of doing it and which are the differences?

Hope you can help me with all my questions.

Thanks and regards.

Alejandro.

[gregorw]

Dear Alejandro,

please avoid cross posting the same question to different blogs.

I would suggest you follow first Rui's Blog series which is using Java in the Backend and Frontend. It has step by step instructions. The new "standard way" should be using the HCP IoT Service.

Best regards

Gregor

[Former Member]

Hi Gregor

Did you manage to use node-red on your Raspberry to push data to the HCP IoT service?

Kind regards

Erik

[gregorw]

Hi Erik,

I have tried the HCP IoT Service only using the Chrome Add-On Postman. But as that works fine it should be no problem to use node-red also.

Best regards

Gregor

[Former Member]

Hi Gregor

I also use the Postman tool to successfully post messages to HCP IoT. But when I try to set up a node-red flow I do not get any response from the HCP service. Any chase you could give me some pointers?

Kind regards

Erik

msg.headers = {"Authorization" : "Bearer 05c77ca74394b450b197f472424c9a","Content-type" : "application/json;charset=utf-8"};

msg.payload = {"messageType":"1a54b20a1e11ab863b07" ,"messages":[{"text":"node-red-01"}]};

return msg;

[gregorw]

Hi Erik,

I've just tried it with node-red using the following 3 nodes to create the message:

[ 

   { 

      "id":"8149b919.75d38",

      "type":"template",

      "z":"8e9d922.fc8cff",

      "name":"JSON for IoT Service",

      "field":"payload",

      "format":"handlebars",

      "template":"{\n \"mode\":\"sync\", \n \"messageType\":\"YourMessageType\", \n \"messages\":[\n   {\n    \"sensor\": \"{{payload.sensor}}\", \n    \"value\":{{payload.temperature}}, \n    \"unit\":\"°C\"}\n ]\n}",

      "x":350,

      "y":96,

      "wires":[ 

         [ 

            "1b141772.1a2239"

         ]

      ]

   },

   { 

      "id":"1b141772.1a2239",

      "type":"function",

      "z":"8e9d922.fc8cff",

      "name":"Add IoT Service HTTP Header",

      "func":"var newMsg = { \n\tpayload: msg.payload,\n\theaders: {\n        'Content-Type': 'application/json;charset=utf-8',\n        'Authorization': 'Bearer YourToken'\n    }\n};\nreturn newMsg;",

      "outputs":1,

      "noerr":0,

      "x":601,

      "y":98,

      "wires":[ 

         [ 

            "9c5e4b6c.240a88"

         ]

      ]

   },

   { 

      "id":"9c5e4b6c.240a88",

      "type":"http request",

      "z":"8e9d922.fc8cff",

      "name":"Send to IoT Service",

      "method":"POST",

      "ret":"txt",

      "url":"https://iotmmss0001142741trial.hanatrial.ondemand.com/com.sap.iotservices.mms/v1/api/http/data/YourD...",

      "x":754,

      "y":179,

      "wires":[ 

         [ 

            "61bcca81.63e104"

         ]

      ]

   }

]

Best regards

Gregor

[Former Member]

Fantastic Gregor 🙂 It works 🙂 Thanks a lot 🙂

[former_member189845]

Is it Possible to connect Raspberry Pi via blue tooth and send sensor data to SAP HCP with out the Code ? , Correct me if i'm wrong.

[gregorw]

What would you like to connect via bluetooth? The raspberry pi to the internet or a bluetooth sensor?

[Former Member]

If you have a pi3 you can connect any bluetooth device to it and send data to HCP using IOT services or by xsoOData.

For pi2 you would need a bluetooth adapater.