Tracking Coronavirus COVID-19 Near Real Time with SAP HANA XSA

In this blog, I am going to walk through how you can create yourself a simple app to track the COVID-19 status in near real time with SAP HANA XS Advanced and display it in a web server or with SAP Analytics Cloud using custom widget.

Let’s get started to create the app in SAP HANA XSA.

Create HANA XSA app

• Logon to SAP HANA XSA Web IDE and create a new project from template.
  
• Select SAP Cloud Platform Business Application. Click Next.
  
• Enter the project name covid19. You can give any name you like. Click Next.
  
• On this section, just click Next to continue.
  
• We will not include Service (at this moment) and Database. Also unchecked the “Unable user authentication (UAA”) with the intention that anyone can access the app without credentials. Click Next to continue.
  
• Click Finish to complete the setup.
  

Create Node.JS Module

• Right click on the project covid19 and select Node.js Module.
  
• Enter the module name srv. You can give any name you like. Click Next.
  
• Unchecked “Enable XSJS support” and click Next to continue.
  
• Click Finish to complete.
  
• You should see the srv folder created. Open the package.json.
  
• We need to add additional libraries like csvtojson and geojson.
• Insert the following lines under dependencies:

  "dependencies": {
      "@sap/cds": "^3.10.0",
      "express": "^4.17.1",
      "@sap/xssec": "^2.1.17",
      "@sap/xsenv": "^2.0.0",
      "hdb": "^0.17.0",
      "@sap/hdbext": "^6.0.0",
      "@sap/hana-client": "^2.4.139",
      "@sap/textbundle": "latest",
      "@sap/logging": "^5.0.1",
      "@sap/audit-logging": "^3.0.0",
      "nodemailer": "^6.2.1",
      "passport": "~0.4.0",
      "async": "^3.0.1",
      "ws": "^7.0.0",
      "accept-language-parser": "latest",
      "node-xlsx": "^0.15.0",
      "node-zip": "~1.1.1",
      "xmldoc": "~1.1.2",
      "winston": "^3.2.1",
      "body-parser": "^1.19.0",
      "elementtree": "latest",
      "then-request": "latest",
      "compression": "~1.7",
      "helmet": "^3.18.0",
      "request": "^2.81.0",
      "csvtojson": "^2.0.10",
      "geojson": "^0.5.0"
  }​

  

• Replace server.js with this content:

  /*eslint no-console: 0, no-unused-vars: 0, no-undef:0, no-process-exit:0*/
  /*eslint-env node, es6 */
  "use strict";
  const port = process.env.PORT || 3000;
  const server = require("http").createServer();
  
  const cds = require("@sap/cds");
  //Initialize Express App for XSA UAA and HDBEXT Middleware
  const xsenv = require("@sap/xsenv");
  const passport = require("passport");
  const xssec = require("@sap/xssec");
  const xsHDBConn = require("@sap/hdbext");
  const express = require("express");
  global.__base = __dirname + "/";
  
  //logging
  var logging = require("@sap/logging");
  var appContext = logging.createAppContext();
  
  //Initialize Express App for XS UAA and HDBEXT Middleware
  var app = express();
  
  //Compression
  app.use(require("compression")({
    threshold: "1b"
  }));
  
  //Helmet for Security Policy Headers
  const helmet = require("helmet");
  // ...
  app.use(helmet());
  app.use(helmet.contentSecurityPolicy({
    directives: {
      defaultSrc: ["'self'"],
      styleSrc: ["'self'", "sapui5.hana.ondemand.com"],
      scriptSrc: ["'self'", "sapui5.hana.ondemand.com"]
    }
  }));
  // Sets "Referrer-Policy: no-referrer".
  app.use(helmet.referrerPolicy({ policy: "no-referrer" }));
  
  app.use(logging.middleware({
  	appContext: appContext,
  	logNetwork: true
  }));
  
  var http = require("http");
  
  app.use(function(req, res, next) {
      res.header("Access-Control-Allow-Origin", "REPLACE_WITH_YOUR_SAP_ANALYTICS_CLOUD_DOMAIN); // update to match the domain you will make the request from
      res.header("Access-Control-Allow-Headers", "Origin, X-Requested-With, Content-Type, Accept");
      next();
  });
  
  app.get("/node", (req, res) => {
  	res.send("OK");
  });
  
  //Setup Additonal Node.js Routes
  require("./router")(app, server);
  
  //Start the Server 
  server.on("request", app);
  server.listen(port, function () {
  	console.info(`HTTP Server: ${server.address().port}`);
  });
  
  ​

  At line 50, replace “REPLACE_WITH_YOUR_SAP_ANALYTICS_CLOUD_DOMAIN” with your localhost or SAP Analytics Cloud domain. This is to resolve the CORS issue.

  

• Create a folder router inside srv folder. Also create a folder routes inside router folder.
  
• Create index.js in router folder with this content:

  /*eslint-env node, es6 */
  "use strict";
  
  module.exports = (app, server) => {
  	app.use("/node", require("./routes/myNode")());
  };​

• Create myNode.js in routes folder with this content:

  /*eslint no-console: 0, no-unused-vars: 0, no-shadow: 0, newcap:0*/
  /*eslint-env node, es6 */
  "use strict";
  const express = require("express");
  const async = require("async");
  const https = require('https');
  const csv = require('csvtojson')
  const GeoJSON = require('geojson')
  
  function formatDate(d) {
  	//get the month
  	var month = d.getMonth();
  	//get the day
  	//convert day to string
  	var day = d.getDate().toString() - 1;
  	//get the year
  	var year = d.getFullYear();
  
  	//pull the last two digits of the year
  	year = year.toString().substr(-2);
  
  	//increment month by 1 since it is 0 indexed
  	//converts month to a string
  	month = (month + 1).toString();
  
  	//if month is 1-9 pad right with a 0 for two digits
  	if (month.length === 1) {
  		month = month;
  	}
  
  	//if day is between 1-9 pad right with a 0 for two digits
  	if (day.length === 1) {
  		day = day;
  	}
  
  	//return the string "MMddyy"
  	return month + '/' + day + '/' + year;
  }
  
  function readHeader() {
  	return new Promise(resolve => {
  		const result = [];
  		https.get(
  			'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv',
  			(resp) => {
  				let data = '';
  
  				// A chunk of data has been recieved.
  				resp.on('data', (chunk) => {
  					data += chunk;
  				});
  
  				// The whole response has been received. Print out the result.
  				resp.on('end', () => {
  					csv({
  							noheader: false,
  							output: "json"
  						})
  						.fromString(data)
  						.then((csvRow) => {
  							const result = [];
  							var d = new Date();
  							var date = formatDate(d);
  							console.log(date);
  							for (var i = 0; i < csvRow.length; i++) {
  								result.push({
  									'Province/State': csvRow[i]['Province/State'],
  									'Country/Region': csvRow[i]['Country/Region'],
  									'Lat': csvRow[i]['Lat'],
  									'Long': csvRow[i]['Long']
  								})
  							}
  							resolve(result);
  						})
  				});
  
  			}).on("error", (err) => {
  			console.log("Error: " + err.message);
  		});
  	});
  }
  
  function readConfirmed() {
  	return new Promise(resolve => {
  		const result = [];
  		https.get(
  			'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv',
  			(resp) => {
  				let data = '';
  
  				// A chunk of data has been recieved.
  				resp.on('data', (chunk) => {
  					data += chunk;
  				});
  
  				// The whole response has been received. Print out the result.
  				resp.on('end', () => {
  					csv({
  							noheader: false,
  							output: "json"
  						})
  						.fromString(data)
  						.then((csvRow) => {
  							const result = [];
  							var d = new Date();
  							var date = formatDate(d);
  							console.log(date);
  							for (var i = 0; i < csvRow.length; i++) {
  								result.push(csvRow[i][date]);
  							}
  							resolve(result);
  						})
  				});
  
  			}).on("error", (err) => {
  			console.log("Error: " + err.message);
  		});
  	});
  }
  
  function readDeath() {
  	return new Promise(resolve => {
  		const result = [];
  		https.get(
  			'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Deaths.csv',
  			(resp) => {
  				let data = '';
  
  				// A chunk of data has been recieved.
  				resp.on('data', (chunk) => {
  					data += chunk;
  				});
  
  				// The whole response has been received. Print out the result.
  				resp.on('end', () => {
  					csv({
  							noheader: false,
  							output: "json"
  						})
  						.fromString(data)
  						.then((csvRow) => {
  							const result = [];
  							var d = new Date();
  							var date = formatDate(d);
  							console.log(date);
  							for (var i = 0; i < csvRow.length; i++) {
  								result.push(csvRow[i][date]);
  							}
  							resolve(result);
  						})
  				});
  
  			}).on("error", (err) => {
  			console.log("Error: " + err.message);
  		});
  	});
  }
  
  function readRecovered() {
  	return new Promise(resolve => {
  		const result = [];
  		https.get(
  			'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Recovered.csv',
  			(resp) => {
  				let data = '';
  
  				// A chunk of data has been recieved.
  				resp.on('data', (chunk) => {
  					data += chunk;
  				});
  
  				// The whole response has been received. Print out the result.
  				resp.on('end', () => {
  					csv({
  							noheader: false,
  							output: "json"
  						})
  						.fromString(data)
  						.then((csvRow) => {
  							const result = [];
  							var d = new Date();
  							var date = formatDate(d);
  							console.log(date);
  							for (var i = 0; i < csvRow.length; i++) {
  								result.push(csvRow[i][date]);
  							}
  							resolve(result);
  						})
  				});
  
  			}).on("error", (err) => {
  			console.log("Error: " + err.message);
  		});
  	});
  }
  
  module.exports = function () {
  	var app = express.Router();
  	var userScope = null;
  
  	app.get("/getSessionInfo", (req, res) => {
  		async function msg() {
  			const header_msg = await readHeader();
  			const confirmed_msg = await readConfirmed();
  			const death_msg = await readDeath();
  			const recovered_msg = await readRecovered();
  
  			const result = [];
  
  			for (var i = 0; i < header_msg.length; i++) {
  				result.push({
  					'Province/State': header_msg[i]['Province/State'],
  					'Country/Region': header_msg[i]['Country/Region'],
  					'Lat': parseFloat(header_msg[i]['Lat']),
  					'Long': parseFloat(header_msg[i]['Long']),
  					'Recovered': parseFloat(recovered_msg[i]),
  					'Confirmed': parseFloat(confirmed_msg[i]),
  					'Death': parseFloat(death_msg[i])
  				})
  			}
  
  			var geojson = GeoJSON.parse(result, {
  				Point: ['Lat', 'Long']
  			});
  
  			res.type("application/json").status(200).send(JSON.stringify(geojson));
  		}
  		msg();
  	});
  
  	return app;
  };​

  Basically, I am getting the data from https://github.com/CSSEGISandData/COVID-19:  time_series_19-covid-Confirmed.csv, time_series_19-covid-Deaths.csv and time_series_19-covid-Recovered.csv and convert it to GeoJSON format.
  You can see the high level design in the diagram below:
  

Create Web Module

• Right click again on the project covid19 and select Basic HTML5 module.
• Enter the module name web. You can give any name you like. Click Next.
  
• Click Finish to complete.
  
• Open xs-app.json and replace the content with:

  {
  	"welcomeFile": "index.html",
  	"authenticationMethod": "none",
  	"routes": [{
  		"source": "/node(.*)",
  		"destination": "srv_api",
  		"csrfProtection": true,
  		"authenticationType": "none"
  	}]
  }​

  

• Now edit mta.yaml and replace the content with the following:
  

  ID: covid19
  _schema-version: "2.1"
  version: 0.0.1
  modules:
    - name: covid19-srv
      type: nodejs
      path: srv
      parameters:
        memory: 512M
        disk-quota: 256M
      provides:
        - name: srv_api
          properties:
            url: '${default-url}'
      requires:
        - name: zcovid-uaa
  
  
    - name: covid19-web
      type: html5
      path: web
      requires:
        - name: zcovid-uaa
        - name: srv_api
          group: destinations
          properties:
            name: srv_api
            url: '~{url}'
            forwardAuthToken: true
  
  resources:
    - name: zcovid-uaa
      type: com.sap.xs.uaa-space
      parameters:
        config-path: ./xs-security.json
  ​

  Don’t forget to set zcovid-uaa. You may refer to this blog on how to do it.

• Create a file xs-security.json  in the project root folder and replace the content with the following:

  {
  	"xsappname": "zcovid19",
  	"scopes": [{
  		"name": "$XSAPPNAME.Display",
  		"description": "display"
  	}, {
  		"name": "$XSAPPNAME.Create",
  		"description": "create"
  	}, {
  		"name": "$XSAPPNAME.Edit",
  		"description": "edit"
  	}, {
  		"name": "$XSAPPNAME.Delete",
  		"description": "delete"
  	}, {
  		"name": "$XSAPPNAME.DataGenerator",
  		"description": "data generator"
  	}, {
  		"name": "xs_authorization.read",
  		"description": "Read authorization information from UAA"
  	}, {
  		"name": "xs_authorization.write",
  		"description": "Write authorization information to UAA"
  	}, {
  		"name": "$XSAPPNAME.ODATASERVICEUSER",
  		"description": "Enter"
  	}, {
  		"name": "$XSAPPNAME.ODATASERVICEADMIN",
  		"description": "Enter"
  	}],
  	"attributes": [{
  		"name": "client",
  		"description": "Session Client",
  		"valueType": "int"
  	}, {
  		"name": "country",
  		"description": "country",
  		"valueType": "s"
  	}],
  	"role-templates": [{
  		"name": "Viewer",
  		"description": "View all records",
  		"scope-references": [
  			"$XSAPPNAME.Display"
  		],
  		"attribute-references": [
  			"client", "country"
  		]
  	}, {
  		"name": "Editor",
  		"description": "Edit and Delete records",
  		"scope-references": [
  			"$XSAPPNAME.Create",
  			"$XSAPPNAME.Edit",
  			"$XSAPPNAME.Delete",
  			"$XSAPPNAME.Display",
  			"$XSAPPNAME.DataGenerator",
  			"$XSAPPNAME.ODATASERVICEUSER",
  			"$XSAPPNAME.ODATASERVICEADMIN"
  		],
  		"attribute-references": [
  			"client"
  		]
  	}]
  }​

  

Run the App

• Run the NodeJS module: select srv and click Run. If no error, you will see the similar below message:
  
• Run the Web module: select web and click Run. If no error, you will see the similar below message:
  
• Now open the web URL and add suffix /node/getSessionInfo: https://<HANA_Web_url>/node/getSessionInfo
  If there is no error, you will see the similar screenshot below. We will use this URL in  the web app later.
  

That’s all the steps that we need to do on HANA XSA.

Consume GeoJSON Data in Web App

Create a file map.html with the below content and put in local web server.

<!doctype html>
<html lang="en-us">
  <head>
    <meta charset="utf-8">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">
    <title>COVID-19</title>
    <link rel="stylesheet" href="https://unpkg.com/leaflet@1.3.3/dist/leaflet.css" />
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/flatpickr/dist/flatpickr.min.css">
    <script src="https://cdn.jsdelivr.net/npm/flatpickr"></script>

    <style>
        body {
            font-family: sans-serif;
            margin: 0px;
            border: 0px;
            padding: 0px;
        }
        .container {
            top: 0;
            left: 0;
            right: 0;
            bottom: 0;
            position: absolute;
        }
        .header {
            position: absolute;
            top: -8px;
            left: 0px;
            padding-left: 50px;
            right: 0;
            padding: 10px;
            z-index: 1000;
            background-color: rgba(255,255,255,0.75);
        }
        h2 {
            margin: 10px 0;
            margin-left: 50px;
        }
        h3 {
            margin: 0;
        }
        #map {
            height: 100%;
            width: 100%;
        }

        #property-list, #playback {
            display: inline-block;
        }

        .view {
            display: inline-block;
            font-size: 12px;
            border: 1px solid black;
            border-radius: 3px;
            margin: 3px;
            padding: 4px;
            background: #ffab96;
        }

        .view:hover {
            background: dodgerblue;
            color: white;
            margin-top: -2px;
            box-shadow: 1px 1px 1px black;
        }

        .view:active {
            margin-top: -2px;
        }

        #playback {
            margin-right: 1em;
            margin-left: 1em;
        }

        #playback .view {
            background-color: #ab96ff;
        }

        .view.selected {
            background: white;
            color: black;
        }

        #datepicker {
            margin-left: 50px;
            font-size: 12px;
        }

        .flatpickr-minute {
            pointer-events: none;
        }

        .flatpickr-minute + .arrowUp {
            pointer-events: none;
        }

        .flatpickr-minute + .arrowUp + .arrowDown {
            pointer-events: none;
        }

        .numInputWrapper:nth-child(3):hover {
            background: none;
        }

        .numInputWrapper:nth-child(3):hover .arrowUp {
            display: none;
        }

        .numInputWrapper:nth-child(3):hover .arrowDown {
            display: none;
        }
    </style>
  </head>

  <body>
    <div class="header">
        <h2>COVID-19 (2019-nCoV)</h2>
    </div>
    <div class="container">
        <div id="map"></div>
    </div>

    <!-- leaflet -->
    <script src="https://unpkg.com/leaflet@1.3.3/dist/leaflet.js"></script>

    <!-- D3 -->
    <script src="https://d3js.org/d3.v5.min.js"></script>

	<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

    <!-- Demo setup -->
    <script>
        var circles = [];
        var initDate = '2001-02-23t09:00:00';
        var timeFormatter = d3.timeFormat('%Y-%m-%dt%H:%M:%S');
        var numberFormatter = d3.format(",");

        var properties = [
            { code: 'Confirmed', desc: 'Confirmed' },
            { code: 'Death', desc: 'Death' },
			{ code: 'Recovered', desc: 'Recovered' }
        ];
        var currProperty = 'Confirmed';
		var ProvinceState = 'Province/State';
		var CountryRegion = 'Country/Region';

        var theMap = L.map('map', {maxZoom: 14});
        theMap.attributionControl.addAttribution('COVID-19 (2019-nCoV) <a href="https://github.com/CSSEGISandData/COVID-19">JHU CSSE</a>');
        theMap.attributionControl.addAttribution('Map tiles by <a href="http://stamen.com">Stamen Design</a>, under <a href="http://creativecommons.org/licenses/by/3.0">CC BY 3.0</a>. Map data by <a href="http://openstreetmap.org">OpenStreetMap</a>, under <a href="http://www.openstreetmap.org/copyright">ODbL</a>');

        L.tileLayer('http://tile.stamen.com/terrain/{z}/{x}/{y}.png').addTo(theMap);
        // center of map
        theMap.setView([31.160629, 112.248863], 4);

        var radiusScale = d3.scaleLinear().domain([0, 200]).range([7, 70]).clamp(true);
        var colorScale = d3.scaleSequential(d3.interpolateOrRd).domain([0, 100]);
        function renderCircles() {
            circles.forEach(function (c) { c.remove(); })
            circles = [];

            theData.features.forEach(function (feature) {
                var c = L.circleMarker(
                    [feature.geometry.coordinates[1], feature.geometry.coordinates[0]],
                    {
                        radius: radiusScale(feature.properties[currProperty] * 0.001),
                        color: colorScale(feature.properties[currProperty]),
                        fillColor: colorScale(feature.properties[currProperty]),
                        fillOpacity: 0.5
                    });
                c.addTo(theMap);
				if(feature.properties[ProvinceState] !== "") {
					c.bindTooltip('<h3>' + feature.properties[ProvinceState] + '</h3> - ' + feature.properties[CountryRegion] + '<br><br><b>' + currProperty + ': </b>' + numberFormatter(feature.properties[currProperty]) + '<br>' + '<b>Death: </b>' + numberFormatter(feature.properties.Death) + '<br>' + '<b>Recovered: </b>' + numberFormatter(feature.properties.Recovered));
				} else {
					c.bindTooltip('<h3>' + feature.properties[CountryRegion] + '</h3><br><b>' + currProperty + ': </b>' + numberFormatter(feature.properties[currProperty]) + '<br>' + '<b>Death: </b>' + numberFormatter(feature.properties.Death) + '<br>' + '<b>Recovered: </b>' + numberFormatter(feature.properties.Recovered));
				}
                circles.push(c);
            });
        }

        function fetchData(dateStr) {
			var url = 'https://<HANA_Web_url>/node/getSessionInfo';
            d3.json(url).then(function(data) {
				console.log(data);
				theData = data;
                renderCircles();
            });
        }

        fetchData(initDate);

    </script>

  </body>
</html>

In fetchData function, replace the URL with the HANA Web URL:https://<HANA_Web_url>/node/getSessionInfo

Open map.html in local web browser:

I also created the custom widget in SAP Analytics Cloud to consume the GeoJSON data from SAP HANA XSA:

Complete source code: https://github.com/ferrygun/SAPHANA_COVID19

Please stay at home and stay safe everyone !!

References:

SAP Analytics Cloud Custom Widget Collection