Google App Engine meets SAP HANA express edition
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.
- 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="/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 %> </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.
Hi Craig,
Me and Former Membercould not manage to connect HXE by HANA studio, can you recommend us something if you have experienced this?
Cheers,
Sarhan.
did you have a specific issue? Did you remember to select the setting for the Multi-tenant database?
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
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
Thank you for your efforts!
Hopefully Lucia can explain this or extend her tutorial with explicit steps on how to set this up!
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!
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:
The connection to the SYSTEMDB@HXE works perfectly fine. No problems at all.
Hosts file is also extended on my computer.
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:
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:
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!
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.
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,
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
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?
Was there any luck? Iam also facing same problem
Hi, just saw this by chance. I would recommend you go to the Q&A for better response times (there are more people getting alerts for questions there than just the author of the blog post).
Assuming the tenant is up, you can connect to it with hdbsql and you have changed global.ini (note that with the last upgrade it is now "map_hxehost" and no longer "map_localhost"). Updated version here: https://www.sap.com/developer/how-tos/2017/06/hxe-gcp-eclipse-tenant-connect.html
If none of that works, there is a known error for an old version of HANA Studio that had this effect.
Tom,
Look at Steven Chen's response above...
You should set both map_localhost and use_default_route appropriately for this to work.
I tried setting map_localhost alone and it dd not work.
I am using hana2 sps2
-ravi
Craig, very good article! Is it possible you share this GitHub demo project?
Dear all,
I still can not solve the issue after taking the advices from Lucia....
Sorry just saw this, are you still having issues??
Good aftrnoon,
I need help. I have installed the HANA Express in google cloud platform, how can I connect a HANA Express tenant database created on Google Cloud Platform with my local installed HANA studio ?
thank you.
Hi, I would recommend you check the Q&A section and tutorials next time.
This should help: https://www.sap.com/developer/how-tos/2016/09/hxe-howto-eclipse.html
Hi,
I am following the link :https://www.sap.com/developer/tutorials/hxe-gcp-getting-started-launcher.html to connect to configure a hana Express Edition DB on Google Cloud.
For the first time I did the config and successfully completed till the step-3 and was waiting for our internal admin team to edit my host file. Since it was get delayed and i don't want to waste the balance of google cloud, i stopped the VM in google cloud. Now I want to perform the step-4 and proceed further, unable to do so. below is the message in putty
Have a lot of fun...
Apurba@hxehost:~> sudo su - hxeadm
hxeadm@hxehost:/usr/sap/HXE/HDB90> cat /usr/sap/HXE/home/xsa_config_status
cat: /usr/sap/HXE/home/xsa_config_status: No such file or directory
hxeadm@hxehost:/usr/sap/HXE/HDB90>
after adding the host file entry, page can not be found error is displayed in the browser. looks like services need to start manually. Can you help me to proceed on this.
Regards
Apurba K saha
Hi, Apurba,
Either you stopped the VM in the middle of the installation process or you chose the "server only" installation (and that is why you do not see the XSA status file).
If you chose the XS Advanced option and do not see the file, chances are that the process was interrupted when you stopped the VM from the Google console. I would recommend you start a new process if this is the case as you would have to check the logs, see what did not get installed and finish the installation process (it's pretty time consuming and requires some expertise)
I think you can reuse the external IP address if you detach it from your dead installation, so you do not need to map the hosts file again.
If you chose the server-only option, the file will not be there (and any steps saying "XS Advanced" do not apply to your instance).
Regards,
Lucia.