Skip to Content

Not long ago Lucia 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.

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="/images/apple-icon-57x57.png">
    <link rel="apple-touch-icon" sizes="60x60" href="/images/apple-icon-60x60.png">
    <link rel="apple-touch-icon" sizes="72x72" href="/images/apple-icon-72x72.png">
    <link rel="apple-touch-icon" sizes="76x76" href="/images/apple-icon-76x76.png">
    <link rel="apple-touch-icon" sizes="114x114" href="/images/apple-icon-114x114.png">
    <link rel="apple-touch-icon" sizes="120x120" href="/images/apple-icon-120x120.png">
    <link rel="apple-touch-icon" sizes="144x144" href="/images/apple-icon-144x144.png">
    <link rel="apple-touch-icon" sizes="152x152" href="/images/apple-icon-152x152.png">
    <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-icon-180x180.png">
    <link rel="icon" type="image/png" sizes="192x192"  href="/images/android-icon-192x192.png">
    <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32.png">
    <link rel="icon" type="image/png" sizes="96x96" href="/images/favicon-96x96.png">
    <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16.png">
    <link rel="manifest" href="/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="/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="/">Home<span class="sr-only">(current)</span></a></li>
                    <li><a href="/fuzzy">Fuzzy Search</a></li>
                    <li><a href="/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.

To report this post you need to login first.

17 Comments

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

  1. Lars Golhar

    Hi Craig,

    we installed it via the Google Cloud Platform Launcher which sets the multidb mode for the System by default to multidb in the global.ini.

    In my case I always get an error message which states that connection failed because it

    “cannot connect to host <internal-IP-of-my-GCP-VM>:<port-of-the-tenant-db>“.

    I can connect to the database via hdbsql in the GCP ssh-browser but not via HANA studio.

    My guess is that the Launcher at GCP is not set up properly and that some critical settings are missing, e.g. firewall-rules for multidb-use, etc.

    (I extended the firewall-rules but no success to connect either.)

    For me personal it seems to be the best option to wait for a release of HANA 2.0 on SAP Cloud Platform.

    Thank you anyway for trying to help!

    Kind regards,

    Lars

    (0) 
    1. Craig Cmehil Post author

      That seems very unusual, let me get Lucia to check here.

       

      When we were connecting to her instance we needed to set our local hosts file with a name like 

       

      xx.xx.xx.xx    ubuntu-server-only

      xx.xx.xx.xx    db1.ubuntu-server-only

      (0) 
        1. Lucia Subatin

          Hi, Lars!

          I did not need to set the hosts up to connect to the systemdb in the instance created from the launcher ( that did apply to the manually created instance on Ubuntu and to the tenant db as Craig is showing). Did the hosts setup solve your problem?

          Cheers!

          (0) 
          1. Lucia Subatin

            As for explicit instructions to setup of the hosts file:

            Add the lines to /etc/hosts if you are using Mac/linux or in C:\Windows\System32\drivers\etc\hosts in Windows as follows:

            <<external ip from Google console>>  <<hostname>>

             

            If you don’t know what the hostname is, from the SSH console in the Google compute console, use command:

            hostname -s

            if this doesn’t work, could you share what settings you are using in HANA Studio to connect? Connection errors also happen when the “Multiple container” – “System database” option is not selected:

             

             

            (0) 
  2. Lars Golhar

    Hi Lucia,

    thank you for replying on this. I don’t need to set the hosts up to connect to the systemdb too. This works perfectly with the the launcher.

    My problem is starting when I create a tenant database and want to connect to it from HANA studio.

    If I follow the steps shown in this tutorial I get always the same error:

    “cannot connect to host <internal-IP-of-my-GCP-VM>:<port-of-the-tenant-db>“.

    Cheers

     

    Extended description:

    • 1 .SYSTEMDB – Connection works

     

    • 2. Tenant DB is created

    • 3. Services running

    • 4. Trying to Login to MTDB1:

    • 5. Error Message:

    (0) 
    1. Lucia Subatin

      Thanks a lot for the details, they are very helpful! As you remark, HANA Studio is redirecting the connection to the internal ip on port 39041. First, please make sure to add the firewall rule to enable tcp on that port. You’ll find the firewall rules in GCP  (the menu on the top left corner) –> Networking.

      As we need it to keep the external IP when it performs this redirection, please set the following parameter on global.ini, under public_hostname_resolution:

       

      1. Enter the Administration tool from the SYSTEMDB:
      2. Open the configuration tab and expand global.ini. Right click on global.ini –> public_hostname_resolution -> Add parameter
      3. Keep “Assign values to System”, click next
      4. Set key “map_localhost” and your external ip in the value.

      This is what it should look like (there’s an external IP address in the blurred vaue) when you exit:

      Proceed to create the connection to the tenant db.

       

      Cheers!

      (2) 
      1. Lars Golhar

        Hi Lucia, you are great! I am finally able to connect to the tenant db! Thank you so much!

        Cheers!

        P.S. For anyone trying to implement this. After adding the rule to your list and afterwards tagging it to your vm, you have to restart the vm to get the new firewall rule working.

        (2) 
      2. Steven Chen

        Hello Lucia,

        I was struggling this problem for 6 hrs. I took you advice but failed yet.

        Finally I solved it with changing public_hostname_resolution->use_default_route: ip = no

         

        That means besides that map_localhost parameter, this use_default_route should be set to ‘no’.

        Hope it can help ones who are/will face the problem.

         

        Best regards,

        (0) 
      3. Elliott Bajema

        Thanks Lucia, that’s awesome.

        Interestingly, I wasn’t able to assign values to System in the Config tab.

        But executing the SQL:

        ALTER SYSTEM ALTER CONFIGURATION (‘global.ini’, ‘SYSTEM’) SET (‘public_hostname_resolution’, ‘map_localhost’) = ‘xx.xx.xx.xx’ WITH RECONFIGURE;

        did allow me to change this, after which point I could successfully add the Tenant DB without issue.
        (I had already whitelisted the 3904x range in the firewall).

        I did not need to change ‘use_default_route‘ to no as Steven Chen did above.

        Kind Regards,
        -Elliott

         

        (0) 
        1. Tom Turchioe

          I’m having no luck either.  I created an instance on GCP.  I created a tenant (DEMOS).

          I created the 3 firewall rules for 39040, 39041, and 39042.

          Even with a shotgun firewall rule opening up 39040 to 39049.

          I tried appending no port to the IP address specified in the Add System dialog in the HANA Studio (i.e. xx.yy.zz.aa) and with a port (xx.yy.zz.aa:39041).  I tried setting the map_localhost in global.ini under public_hostname_resolution and also toggling use_default_root.  I even issued the ALTER SYSTEM command with the appropriate IP.  I’ve run different permutations of these.  All to no avail.

          In HANA Studio, the error log shows an attempted connection to xx.yy.zz.aa:39041 to no avail.  I’ve restarted the VM, exited and restarted HANA studio.  Yes, I’m able to connect to SYSTEMDB using the SYSTEM user for instance 90:

          this is quite elusive.  I’m really hoping for pilot error.  Anyone?

           

          (0) 

Leave a Reply