Google App Engine meets SAP HANA express edition

craigcmehil · ‎2017 Mar 08

Not long ago lsubatin posted about using SAP HANA express edition (HXE) on the Google Cloud Platform. So after a little, well basically begging she let me use her existing install to do some experimenting.

So experimenting I did....

The end result I am pleased to say was a nice robust Node.js application that contained both a Node.js HDB library connection as well as an OData source using Bootstrap, JQuery, Google Maps and and and and...

The first challenge was just figuring out how the Google App Engine worked in particular the part related to Node.js. With the use of their quick start guide I was up and running in a short while. Then it was a matter of repeating the process but with my own code and Github repository.

Once my repo was connected to the Development section I was able to launch the Google Cloud Shell and clone my repo into a new folder.

Then I was able to (after testing of course) run the "gcloud deploy" option and deploy it. I know I might have skipped over a few steps there, maybe more than a few but the point was - it was literally that easy to have my app up and running.

Speaking of the app though, since I was copying the quickstart guide it was rather quick which meant the yaml file was there already. I'm not a fan of those...

# [START runtime]

runtime: nodejs

env: flex

# [END runtime]



# Temporary setting to keep gcloud from uploading node_modules

skip_files:

 - ^node_modules$

For the rest since I was working with Node.js I just needed to follow the basic tutorials, like how to generate the "package.json" file. There are several tutorials out there actually so there is no limit to finding something to help you get started. Some of the ones I started with were around Node.js and Express.

http://nodeguide.com/beginner.html

https://expressjs.com/en/starter/hello-world.html

https://expressjs.com/en/starter/generator.html

I decided to use Express for mine as it meant I could stick with pure HTML for my templates (through the view engine, "ejs") and there were a ton of examples already out there.

Using the templates, I was able to define my first route access my HXE system and pass variables into my template.

app.get('/', function (req, res) {

  sql_command = "SELECT TO_VARCHAR(ROUND((FREE_PHYSICAL_MEMORY) /1024/1024/1024, 2)) AS FREEMEM FROM PUBLIC.M_HOST_RESOURCE_UTILIZATION";

  async.waterfall([connect, executeSQL, disconnect], function (err, rows) {

    client.end();

    if (err) {

      return console.error(err);

    }

    lv_freemem = rows[0].FREEMEM;

    res.render('index', {

      version: lv_version,

      app_version: lv_appversion,

      freemem: lv_freemem,

      diskvol: lv_disk,

      cpu: lv_cpu,

      alerts: lv_alerts,

      users: lv_users

    });

  });

})

The template, as I mentioned since I chose "ejs" I was able to use pure HTML.

<!DOCTYPE HTML>

<html lang="en">

<head>

    <link rel="apple-touch-icon" sizes="57x57" href="https://blogs.sap.com/images/apple-icon-57x57.png">

    <link rel="apple-touch-icon" sizes="60x60" href="https://blogs.sap.com/images/apple-icon-60x60.png">

    <link rel="apple-touch-icon" sizes="72x72" href="https://blogs.sap.com/images/apple-icon-72x72.png">

    <link rel="apple-touch-icon" sizes="76x76" href="https://blogs.sap.com/images/apple-icon-76x76.png">

    <link rel="apple-touch-icon" sizes="114x114" href="https://blogs.sap.com/images/apple-icon-114x114.png">

    <link rel="apple-touch-icon" sizes="120x120" href="https://blogs.sap.com/images/apple-icon-120x120.png">

    <link rel="apple-touch-icon" sizes="144x144" href="https://blogs.sap.com/images/apple-icon-144x144.png">

    <link rel="apple-touch-icon" sizes="152x152" href="https://blogs.sap.com/images/apple-icon-152x152.png">

    <link rel="apple-touch-icon" sizes="180x180" href="https://blogs.sap.com/images/apple-icon-180x180.png">

    <link rel="icon" type="image/png" sizes="192x192"  href="https://blogs.sap.com/images/android-icon-192x192.png">

    <link rel="icon" type="image/png" sizes="32x32" href="https://blogs.sap.com/images/favicon-32x32.png">

    <link rel="icon" type="image/png" sizes="96x96" href="https://blogs.sap.com/images/favicon-96x96.png">

    <link rel="icon" type="image/png" sizes="16x16" href="https://blogs.sap.com/images/favicon-16x16.png">

    <link rel="manifest" href="https://blogs.sap.com/images/manifest.json">

    <meta name="msapplication-TileColor" content="#ffffff">

    <meta name="msapplication-TileImage" content="/images/ms-icon-144x144.png">

    <meta name="theme-color" content="#ffffff">

    <meta charset="utf-8">

    <meta name="viewport" content="width=device-width, initial-scale=1">

    <meta http-equiv="content-type" content="text/html; charset=utf-8">

    <script src="/js/jquery-3.1.1.min.js"></script>

    <link rel="stylesheet" href="https://blogs.sap.com/css/bootstrap.min.css">

    <script src="/js/bootstrap.min.js"></script>

</head>

<body>



    <div>

        <div>

            <nav class="navbar navbar-inverse" role="navigation" style="padding-left:130px;">

                <img src="/images/logo.png" class="pull-left" style="display: inline-block; width: 50px">

                <ul class="nav navbar-nav">

                    <li class="active"><a href="https://blogs.sap.com/">Home<span class="sr-only">(current)</span></a></li>

                    <li><a href="https://blogs.sap.com/fuzzy">Fuzzy Search</a></li>

                    <li><a href="https://blogs.sap.com/map">Maps</a></li>

                    <li><a href="https://www.sap.com/cmp/nl/sap-developer-news/index.html">Subscribe</a></li>

                </ul>

            </nav>

        </div>

        

        <br/>

        <div class="jumbotron">

            <div class="container">

                <p>

                    <img src="/images/hana.png" class="pull-left" style="display: inline-block;">

                    <a href="https://www.sap.com/developer/topics/sap-hana-express.html">SAP HANA</a> is a data platform that provides everything you need to build real-time 

                    data-driven applications. Learn about the different features and components, how 

                    to use them and how to program your applications.<br>

                    <br>

                </p>

            </div>



        </div>

   

        <div class="container">

            <div class="row">

                <div class="col-md-4">

                    <h2>SAP Developers</h2>

                    <p>The following is a Node JS demo connecting to SAP HANA, express edition running within the Google Cloud Platform. </p>

                    <img src="/images/landscape.png">

 

                </div>

                <div class="col-md-4">

                    <h2>Components</h2>

                    <ul>

                        <li>Node Library: express</li>

                        <li>Node Library: ejs</li>

                        <li>Node Library: util</li>

                        <li>Node Library: async</li>

                        <li>Node Library: hdb</li>

                        <li>JS Library: JQuery</li>

                        <li>JS Library: JQuery UI</li>

                        <li>Layout Library: Bootstrap</li>

                        <li>SAP HANA, express edition</li>



                    </ul>

                </div>

                <div class="col-md-4">

                    <div class="panel panel-success">

                        <div class="panel-heading">

                        <h3 class="panel-title">Server Information</h3>

                        </div>

                        <div class="panel-body">

                            <p class="text-muted">HANA v<%= version %></p>

                            <p class="text-muted">Application v<%= app_version %></p>

                            <p class="text-muted">Active Users <span class="label label-info"><%= users %></span></p>

                            <p class="text-muted">Free Memory <span class="label label-info"><%= freemem %></span> GB</p>

                            <p class="text-muted">CPUs <span class="label label-info"><%= cpu %></span></p>

                            <p class="text-muted">Free Disk Space <span class="label label-info"><%= diskvol %></span> GB</p>

                            <p class="text-muted">Alerts <span class="label label-danger"><%= alerts %></span><p>

                        </div>

                    </div>

                </div>

            </div>

        </div>



    </div>



</body>

</html>

Here I passed in several variables such as the following,

<p class="text-muted">HANA v<%= version %></p>

<p class="text-muted">Application v<%= app_version %></p>

Those variables came from the "app.js" file where I defined the route

res.render('index', {

      version: lv_version,

      app_version: lv_appversion,

      ...

}

Since I put the SQL call to access the server free memory into the route itself every time the "index" is clicked the SQL will run and return the current value. However, one thing to remember in order to make this work when I deployed I also needed to add a Firewall rule under the Networking options for the HDB library to be able to find the server.

Then whether I was working on my local system for testing on on the Cloud system then it would connect, in my case it was port 30041 but that was something I had to check on the system directly to see which port was allocated (usually 30015).

Having my "index" working it was now time to go a bit more complex, I decided to use an existing example from a few months ago my "Fuzzy Search" which meant I needed to get onto the HXE system and create a basic XS application with the data, structures, and sample data. Then I moved the UI layer to my Node.js application.

This meant I needed to be able to send data from my webpage back to my main Node.js application. This actually was a lot easier than I originally thought it would be. I just needed to set the form action to a new route that I needed to add to my main Node.js application.

<form id="form1" method="GET" action="/fuzzy_get">

    <label for="txtFieldSearch">Mispelled keyword to search the <span class="label label-success"><%= sql_count %></span> reviews for:</label>

    <input id="txtFieldSearch" name="txtFieldSearch" type="text" size="30" value="<%= search_field %>" />

    <input id="txtfuzzyweight" name="txtfuzzyweight" type="hidden" />

    <p></p>

    <p></p>

    <div align="center">

        <input class="ui-button ui-widget ui-corner-all" type="button" value="View SQL" onclick="display()">

        <input class="ui-button ui-widget ui-corner-all" type="button" value="Submit SQL" onclick="submitDetailsForm()">

    </div>

    <p></p>

    <div id="txtSearch"><%= search_sql %></div> 

    <p></p>

</form>

Therefore in my "app.js" I added a new route, "/fuzzy_get"

app.get('/fuzzy_get', function (req, res) {

  var lv_res = {

    txtFieldSearch: req.query.txtFieldSearch,

    txtfuzzyweight: req.query.txtfuzzyweight

  };

  sql_command = "SELECT SCORE() AS score, RTEXT FROM \"FSM\".\"sap.devs.demo.fuzzymovie.data::movies.Review\" WHERE CONTAINS(RTEXT,'" + req.query.txtFieldSearch + "', FUZZY(0." + req.query.txtfuzzyweight + ") ) ORDER BY score DESC limit 15";

  async.waterfall([connect, executeSQL, disconnect], function (err, rows) {

    client.end();

    if (err) {

      return console.error(err);

    }

    res.render('fuzzy', {

      data: rows,

      version: lv_version,

      app_version: lv_appversion,

      search_field: req.query.txtFieldSearch,

      search_sql: sql_command,

      sql_count: lv_count

    });

  });

})

There I was able to access the form fields and then execute my SQL and return the "rows" back to my templated page. Here I do a little "old school" JavaScript and created the table of results thanks the to the "ejs" engine.

<table padding="2">

    <tr>

        <th>Score</th><th>Review</th>

    </tr>

    <% for (var i = 0; i < data.length; i++) { %>

        <tr>

        <td align="left" valign="top"><%= data[i].SCORE %>&nbsp;&nbsp;&nbsp;</td>

        <td><%= data[i].RTEXT %></td>

        </tr>    

    <% } %>

</table>

I was on a roll and moved on from this point to a second route and the addition of a Google Map example. I based this off another blog from a few months ago. However, this time instead of my own data from Openpaths I decided to put in the locations of all the SAP offices worldwide along with locations of several folks who helped me test it real quick.

In this part I decided to mix it up a bit and use an OData service to submit my current geo location to the server and add it to the collection for displaying later.

// Insert to HANA

var timeStamp = new Date().getTime();

var data = {

    "ID": 1,

    "PDATE": "/Date(" + timeStamp + ")/",

    "PLAT": "" + position.coords.latitude + "",

    "PLON": "" + position.coords.longitude + "",

    "PDESC": " "

};

console.log("Data: " + JSON.stringify(data));

var aUrl =

    '/sap/devs/demo/mylocation/services/location_input.xsodata/locs';

jQuery.ajax({

    url: aHost + aUrl,

    method: 'POST',

    crossDomain: true,

    contentType: "application/json",

    data: JSON.stringify(data),

    success: function (data) {

        console.log('Posted to SAP HANA');

        console.log(data);

    },

    failure: function (errMsg) {

        console.log(errMsg);

    },

    error: function (errMsg) {

        console.log(errMsg);

    },

    xhrFields: {

        withCredentials: true

    },

    crossDomain: true

});

Otherwise I used the HDB library to connected directly to the server and execute SQL commands. I pulled the results of two different tables and passed those rows to my template.

app.get('/map', function (req, res) {

  sql_command = "SELECT * FROM \"HGL\".\"sap.devs.demo.mylocation.data::hanalocations_details.locations\"";

  async.waterfall([connect, executeSQL, disconnect], function (err, rows) {

    client.end();

    if (err) {

      return console.error(err);

    }

    var result = JSON.stringify({

      Items: rows

    });

    sql_command = "SELECT * FROM \"HGL\".\"sap.devs.demo.mylocation.data::hanalocations_details.offices\"";

    async.waterfall([connect, executeSQL, disconnect], function (err, rows) {

      client.end();

      if (err) {

        return console.error(err);

      }

      var office_result = JSON.stringify({

        Items: rows

      });

      res.render('map', {

        api_key: API_KEY,

        version: lv_version,

        app_version: lv_appversion,

        data: result,

        offices: office_result

      });

    });

  });

})

Once in the template, it was a bit different to get the data in place. Turned out to be a bit easier as well.

// Marker Locations

var result = "<%= data %>"

var map_data = replaceAll(result);

map_data = JSON.parse(map_data);

// Offce locations

var office_result = "<%= offices %>"

var map_officedata = replaceAll(office_result);

map_officedata = JSON.parse(map_officedata);

For some reason though I also needed a little helper function as the data was being passed over looking a bit strange, whether this is a standard issue or something specific to what I did I am not yet sure.

function replaceAll(str) {

    return str.replace(new RegExp('"', 'g'), '"');

}

Otherwise that was the only "special" things I had to to make this work beyond the standard basic examples you can find all over the internet for working with Google Maps.

That was my first experiences with the Google App Engine and I have to say, it was an extreme pleasure to work with! My testers agreed.

Google App Engine meets SAP HANA express edition

Get Your SAP HANA Idea Incubator Badge Today!

SCN Mission - SAP HANA Quiz Challenge is now retired

Share your #HANAStory and Win