Start building your own chatbot now

In this tutorial, we will build a simple chatbot that leverages SAP HANA XS advanced model (XSA) and SAP Conversational AI to retrieve the email address information of the company, as well as to update the email address from and to the SAP HANA database. We will ask the bot what the email address of the company is. If the relevant record exists, the bot will reply with the email address information. If there is no record, we will ask the bot to update the email address, and the address will be persisted in the SAP HANA database.

sap-conversational-ai-chatbot-hana-xs

High-Level Architecture

high-level-architecture-chatbot

There are a few components we need to set up to get the bot up and running. Some of these reside in the cloud, like SAP Conversational AI, and some reside on premise, like SAP HANA XSA.

The components are as follows:

  • SAP HANA XSA: on-premise
  • Cloud Connector: on-premise
  • SAP Cloud Platform Cloud Foundry: in the cloud
  • SAP Conversational AI: in the cloud

These components enable us to create our bot with the following end-to-end flows:

  1. SAP Conversational AI integrates with the bot back end via a webhook.
  2. The app router appnode-demo exposes the bot back end securely.
  3. Cloud Connector establishes a secure connection between SAP Cloud Platform Cloud Foundry and SAP HANA XSA in the on-premise landscape.
  4. The bot back end is written in NodeJS app in SAP HANA XSA to search and update the email address in the SAP HANA database.
  5. The end user interacts with the bot via web browser.

Let’s go through the following steps and build our bot.

SAP HANA XSA

Step 1 – Create New Project “zchatbot”

Log on to SAP Web IDE for SAP HANA XSA and create a new project using the template for New SAP Cloud Platform Business Application. Click Next to continue.

Enter the project name zchatbot and click Next to continue.

In the Template Customization section, simply click Next to continue.

We will add the NodeJS module later. In the Project Details section, just set Service (srv) to Not included. We also need to add the database module to store the data. Set Database (db) to SAP HANA Database. Click Next to continue.

Click Finish to complete.


You will see that the zchatbot project structure is created in the workspace.

Step 2 – Create Artifacts in Database Module

We will create the database artifacts hdbtable for table, hdbprocedure for stored procedure, and hdbsequence for database sequence.

  • Navigate to the db folder in the zchatbot project structure.
  • Under db, open the src folder, create a new folder called data, and create a new database artifact for the hdbtable called zchatbot.hdbtable.

COLUMN TABLE "ZCHATBOT" (
	"ID" INTEGER CS_INT NOT NULL COMMENT 'ID',
	"COMPANY" NVARCHAR(100) COMMENT 'Company',
	"COMPANYCODE" NVARCHAR(10) COMMENT 'Company Code',
	"EMAILADDRESS" NVARCHAR(50) COMMENT 'Email Address',
	PRIMARY KEY ("ID")) 
	COMMENT 'ZCHATBOT'	 
	UNLOAD PRIORITY 5 AUTO MERGE

Under db, open the src folder, create a new folder called procedure, and create two new database artifacts for hdbprocedure called insertData.hdbprocedure and updateData.hdbprocedure.

Open insertData.hdbprocedure and insert the following code:

PROCEDURE "insertData" ( 
	in ID INTEGER,
	in COMPANY NVARCHAR(100),
	in COMPANYCODE NVARCHAR(10),
	in EMAILADDRESS NVARCHAR(50)
) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER 
	--DEFAULT SCHEMA 
	--READS SQL DATA AS
	AS -- "READS SQL DATA " removed  
BEGIN
/***************************** 
	Write your procedure logic 
 *****************************/
insert into
	"ZCHATBOT"
	values 
		(
			ID,
			COMPANY,
			COMPANYCODE,
			EMAILADDRESS
		);
END;

Open updateData.hdbprocedure and insert the following code:

PROCEDURE "updateData" ( 
	in COMP NVARCHAR(100),
	in EMAILADD NVARCHAR(50)
) 
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER 
	--DEFAULT SCHEMA 
	--READS SQL DATA AS
	AS -- "READS SQL DATA " removed  
BEGIN
/***************************** 
	Write your procedure logic 
 *****************************/
update
	"ZCHATBOT"
	set EMAILADDRESS = EMAILADD
	Where COMPANY = COMP;
END;

Under db, open the src folder, create a new folder called sequence, and create a new database artifact for hdbsequence called zchatbotSeqId.hdbsequence.

Open zchatbotSeqId.hdbsequence and insert the following code:

SEQUENCE "zchatbotSeqId" START WITH 100
RESET BY SELECT IFNULL(MAX("ID"), 100) + 1 FROM "ZCHATBOT"

We should have the required database artifacts for data, procedures, and sequence, as shown in the following screenshot. We will now build the db module.

If there are no errors during the build, you will get a message like the one shown in the following screenshot.

Step 3 – Create NodeJS Modules

Right-click on the zchatbot folder and select New > Node.js Module.

Enter the module name srv and click Next.

In the Template Customization section, just click Next to continue.

Click Finish to complete.

Open server.js in the srv folder and insert the following code:

/*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 || 8083;
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
}));
app.use(passport.initialize());
var hanaOptions = xsenv.getServices({
	hana: {
		tag: "hana"
	}
});
hanaOptions.hana.pooling = true;
app.use(
	xsHDBConn.middleware(hanaOptions.hana)
);

// Redirect any to service root
app.get("/", (req, res) => {
	res.send("Bot Server");
});
app.get("/node", (req, res) => {
	//res.redirect(odataURL);
	res.send("Bot Server");
});

//Setup Additional 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}`);
});

In the srv folder, create a new folder called router. Inside the router folder, then create index.js and insert the following code:

/*eslint-env node, es6 */
"use strict";

module.exports = (app, server) => {
	app.use("/node", require("./routes/myNode")());
};

Inside the router folder, create a new folder called routes, then create a file called myNode.js and insert the following code.

/*eslint no-console: 0, no-unused-vars: 0, no-shadow: 0, newcap:0*/
/*eslint-env node, es6 */
"use strict";
var express = require("express");
var async = require("async");
var bodyParser = require("body-parser");
var client = require("@sap/hana-client");
var xsenv = require("@sap/xsenv");
var hdbext = require('@sap/hdbext');

var hanaOptions = xsenv.getServices({
	hana: {
		tag: "hana"
	}
});

var hanaConfig = {
	host: hanaOptions.hana.host,
	port: hanaOptions.hana.port,
	user: hanaOptions.hana.user,
	password: hanaOptions.hana.password,
	CURRENTSCHEMA: hanaOptions.hana.schema
};

function sendTextReply(res, msg) {
	res.send({
		replies: [{
			type: 'text',
			content: msg
		}],
		conversation: {
			memory: {
				key: "Null"
			}
		}
	});
}

function sendCardsReply(res, card_title, card_sub_title, image_url, button_title, button_type, button_value, memkey) {
	res.send({
		replies: [{
			type: "card",
			content: {
				title: card_title,
				subtitle: card_sub_title,
				imageUrl: image_url,
				buttons: [{
					title: button_title,
					type: button_type,
					value: button_value
				}]
			}
		}],
		conversation: {
			memory: {
				key: memkey
			}
		}
	});
}

function sendQuickReply(res, msg, memkey) {
	res.send({
		replies: [{
			type: "quickReplies",
			content: {
				title: msg,
				buttons: [{
					title: "Yes",
					value: "Yes"
				}, {
					title: "No",
					value: "No"
				}]
			}
		}],
		conversation: {
			memory: {
				key: memkey
			}
		}
	});
}

function sendButtonReply(res, msg, title, linkvalue) {
	res.send({
		replies: [{
			type: 'buttons',
			content: {
				title: msg,
				buttons: [{
					title: title,
					type: "web_url",
					value: linkvalue
				}]
			}
		}],
		conversation: {
			memory: {
				key: "Null"
			}
		}
	});
}

module.exports = function () {
	var app = express.Router();

	app.use(bodyParser.json());

	app.post('/node', async(req, res) => {

		var slug = req.body.nlp.intents[0].slug;
		console.log("slug: " + slug);
		var entities = req.body.nlp.entities;

		if (slug === "query-email-address") {
			if (entities.hasOwnProperty("emailaddr") && entities.hasOwnProperty("company")) {
				var org = entities.company[0].raw;
				console.log(org);

				var dbClass = require(global.__base + "utils/dbPromises");
				var client = await dbClass.createConnection();
				var db = new dbClass(client);

				try {
					var statement = await db.preparePromisified(
						`SELECT EMAILADDRESS FROM ZCHATBOT WHERE COMPANY = ?`);

					var dataResults = await db.statementExecPromisified(statement, [org]);
					if (dataResults.length > 0) {
						console.log(dataResults[0].EMAILADDRESS);
						sendTextReply(res, "Email address is " + dataResults[0].EMAILADDRESS)
					} else {
						sendTextReply(res, "There is no email address for " + org);
					}
				} catch (e) {
					console.log(e);
					sendTextReply(res, " An error has occurred ");
				}
			}
		}

		if (slug === "write-email-address") {
			if (entities.hasOwnProperty("emailaddr") && entities.hasOwnProperty("company") && entities.hasOwnProperty("email")) {
				var org = entities.company[0].raw;
				var emailaddress = entities.email[0].raw;

				var dbClass = require(global.__base + "utils/dbPromises");
				var client = await dbClass.createConnection();
				var db = new dbClass(client);

				try {
					var statement = await db.preparePromisified(
						`SELECT EMAILADDRESS FROM ZCHATBOT WHERE COMPANY = ?`);

					var dataResults = await db.statementExecPromisified(statement, [org]);
					if (dataResults.length > 0) {

						var inputParams = {
							COMP: org,
							EMAILADD: emailaddress
						};

						hdbext.createConnection(hanaConfig, function (error, client) {
							if (error) {
								console.error(error);
							}

							hdbext.loadProcedure(client, null, "updateData", function (err, sp) {
								sp(inputParams, (err, parameters, results) => {
									if (err) {
										console.log(err);
									}
									sendTextReply(res, "Email address has been updated to " + emailaddress)
								});
							});
						});

					} else {

						var statement = await db.preparePromisified(
							`SELECT \"zchatbotSeqId\".NEXTVAL AS ID
							 FROM DUMMY`);
						var dataResults = await db.statementExecPromisified(statement, []);

						var ID = dataResults[0].ID;
						var COMPANY = org;
						var COMPANYCODE = "01";
						var EMAILADDRESS = emailaddress;
						var inputParams = {
							ID: ID,
							COMPANY: COMPANY,
							COMPANYCODE: COMPANYCODE,
							EMAILADDRESS: EMAILADDRESS
						};

						hdbext.createConnection(hanaConfig, function (error, client) {
							if (error) {
								console.error(error);
							}

							hdbext.loadProcedure(client, null, "insertData", function (err, sp) {
								sp(inputParams, (err, parameters, results) => {
									if (err) {
										console.log(err);
									}
									sendTextReply(res, "Email address has been updated to " + emailaddress)
								});
							});
						});
					}
				} catch (e) {
					console.log(e);
					sendTextReply(res, " An error has occurred ");
				}

			}
		}
	});

	return app;
};

Open package.json in the srv folder and insert the following code:

{
    "name": "serve",
    "description": "Generated from ../package.json, do not change!",
    "version": "1.0.0",
    "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"
    },
    "engines": {
        "node": "^8.9",
        "npm": "^6"
    },
    "devDependencies": {},
    "scripts": {
    	"postinstall": "cds build/all --project .. --clean",
		"start": "node server.js"
    },
    "i18n": {
		"folders": [
			"_i18n"
		]
	},
	"cds": {
		"data": {
			"driver": "hana"
		}
	}
}

Step 4 – Update mta.yaml

The last step we need to do is to update the mta.yaml file. Open the yaml file in the root folder and update the content by replacing it with the code shown under the following screenshot:

ID: zchabot
_schema-version: "2.1"
version: 0.0.1
modules:
  - name: zchabot-db
    type: hdb
    path: db
    parameters:
      memory: 256M
      disk-quota: 256M
    requires:
      - name: zchabot-db-hdi-container

  - name: srv
    type: nodejs
    path: srv
    parameters:
      memory: 512M
      disk-quota: 256M
    provides:
      - name: srv_api
        properties:
          url: '${default-url}'
    requires:
      - name: zchabot-db-hdi-container

resources:
  - name: zchabot-db-hdi-container
    type: com.sap.xs.hdi-container
    properties:
      hdi-container-name: ${service-name}

Step 5 – Run the “srv” Module

Select the srv folder and click the Run.

Open the Run console and check if the app is running without errors.

Open the link to the app URL. If there are no errors, you will see the following message:

Step 6 – Add Web Module

Right-click on the zchatbot folder and select New > Basic HTML5 Module.

Enter the module name web and click Finish to complete.

Under web, open the resources folder, then open index.html, and update the content by replacing it with the code shown under the following screenshot.

We will get the channelId and token later when we create the bot in SAP Conversational AI. We will then use these values to update the corresponding variables in this HTML code.

Setting Up Cloud Connector

  • Download and install Cloud Connector on your local machine.
  • Execute bat (if you are using a Windows machine) to run Cloud Connector.
  • Open the Cloud Connector URL at https://localhost:8443/.

Under the Connector section, click Add Subaccount, enter the required information, and then save it. You need to log on to the SAP Cloud Platform – Cloud Foundry – Overview section to get the subaccount ID.

Select Cloud To On-Premise for the subaccount you just created.

Click the plus sign to add System Mapping.

Enter the required information as shown in the following screenshot. In the Internal Host field, enter the URL of the NodeJS app we created in Step 5 – Run the “srv” Module.

Click Save to complete the setup.

Click Check Availability and see if the check result is “Reachable”, which means it can connect to the NodeJS app.

Under Resources Of…, click the plus sign and enter the information as shown in the following screenshot.

Setting Up SAP Cloud Platform Cloud Foundry

Step 1 – Create a Destination

Log on to SAP Cloud Platform cockpit and select Cloud Foundry.

Click Cloud Foundry Trial and navigate to the Cloud Foundry Subaccount.

Select Connectivity > Destinations > New Destination.

Enter the following information. The URL is the location of the NodeJS app we created in SAP HANA XSA. Click Save to continue.

Click Check Connection to check the connectivity to the NodeJS app and make sure the connection is successful.

Step 2 – Create a Destination Instance

Navigate to your dev space.

Select Services > Service Marketplace. Filter for destination.

On the Service: Destination – Instances screen, click New Instance.

Choose lite as the service plan and click Next.

On the following screen, just click Next.

We will specify the required service in the manifest file to create the binding between the application router and this service. Click Next to continue.

Enter destinationnode-demo-lite as the instance name and click Finish.

Step 3 – Create a Connectivity Instance

Go back to your dev space again and navigate to Service Marketplace. Filter by connectivity.

On the Service: Connectivity – Instances screen, click New Instance.

Choose lite as the service plan and click Next.

On this screen, just click Next.

We will specify the required service in the manifest file to create the binding between the application router and this service.  Click Next to continue.

On this screen, enter connectivity-demo-lite as the instance name and click Finish.

Step 4 – Create an XSUAA Instance

Go to your dev space and navigate to Service Marketplace. Filter by Autho and choose Authorization & Trust Management.

On the Service: Authorization & Trust Management – Overview screen, choose Instances

Now choose New Instances.

Choose application as the service plan and choose Next.

Provide the following parameter and click Next.

{
	"xsappname": "appnode-demo",
	"tenant-mode": "dedicated",
	"description": "Security profile of called application",
	"scopes": [
		{
			"name": "uaa.user",
			"description": "UAA"
		}
	],
	"role-templates": [
		{
			"name": "Token_Exchange",
			"description": "UAA",
			"scope-references": [
				"uaa.user"
			]
		}
	]
}

On this screen, enter xsuaanode-demo as the instance name and click Finish.

Select Service Instances. You should then see the following service instances we just created.

Step 5 – Create and Configure the App Router

We will create an application router NodeJS that is installed using SAP’s NPM registry (https://npm.sap.com). The information about this registry is in the npm startup file .npmrc.

Create a new folder called appnode in your local machine.

Create a new file called package.json in this folder with the following content:

{
"name": "appnode-demo",
"version": "1.0.0",
"description": "",
"scripts": {
"start": "node node_modules/@sap/approuter/approuter.js",
"test": "echo \"Error: no test specified\" && exit 1"
},
"license": "ISC",
"dependencies": {
"@sap/approuter": "^5.3.0"
}
}

In the same folder, create a new file called .npmrc with the following content:

@sap:registry=https://npm.sap.com/

Create a new file called xs-app.json in the same folder with the following content. The destination hanaxsa is the one we created in Step 1 – Create Destination.
We will set the authentication type to basic for the “Call Webhook” setting in SAP Conversational AI.

{
  "authenticationMethod": "route",
"routes": [
{
"source": "/",
"target": "/",
"destination": "hanaxsa",
"csrfProtection": false,
"authenticationType": "basic"
}
]
}

Step 6 – Deploy the App Router

Create a zip file called appnode.zip which contains all the files you created earlier.

Create a file called manifest.yml for the appnode. We define the required services xsuaanode-democonnectivity-demo-lite, and destinationnode-demo-lite to create the binding between the app router and these services during deployment.

---
applications:

- name: appnode-demo
  host: appnode-demo-
  buildpack: https://github.com/cloudfoundry/nodejs-buildpack.git
  memory: 128M
  services:
    - xsuaanode-demo
    - connectivity-demo-lite
    - destinationnode-demo-lite

Replace unique-ID in the host property with your subaccount ID.

Navigate to your dev space and choose Applications > Deploy Application.

Upload the files appnode.zip and manifest.yml and click Deploy.

After you click Deploy, SAP Cloud Platform will try to start the app router and check the Requested State status. If there are no errors, the status should turn green.

Step 7 – Run the App Router

In your dev space, navigate to Applications > appnode-demo.

Take a note of the link of the app route: https://appnode-demo-<unique-ID>.cfapps.eu10.hana.ondemand.com/.
We will use this link in the webhook setting in SAP Conversational AI later.

Open the Application Routes URL. You should see the following message: “Bot Server”.

Create a chatbot with SAP Conversational AI

Step 1 – Create a New Bot

Log on to SAP Conversational AI and click on New Bot.

Select Greetings as predefined skills for our bot.

Enter the name saphanabot, enter the topics internal-customer and customer-support, and set the default language to English.

Under Data Policy, set Type of data to Non-personal and End users to Non-vulnerable.

Set the bot visibility to Public.

Click Create A Bot to complete.

Step 2 – Train: Create Intents and Entities

We will create two intents. Select the Train tab and create the first one, write-email-address. Click + Create to continue.

Click Create Intent.

Add the expression please update email address of XYZ to info@company.com and press Enter.

Select the expression you just created and create the following entities: EMAILADDR for email address, COMPANY for XYZ, and EMAIL for info@company.com.

You should now see the entities shown in the following screenshot.

Let’s also create another expression with the same entities.

Create the second intent query-email-address with two expressions and two entities: EMAILADDR and COMPANY.

Step 3 – Build: Create Skill

Select the Build tab and click + Create skill.


Enter the skill name update_email and select the skill type Business. Click Create Skill to continue.

Click on the update_email skill we just created.

Select the Triggers tab. We want this skill to be triggered when the intents write-email-address and query-email-address are present. Let’s add and set these two intents.

Go to the Actions tab and click Add New Message Group.

Click Call Webhook to call the external API, which is our NodeJS app in Cloud Foundry.

Set the webhook protocol to POST and enter the NodeJS app URL. We also need to specify the username and password since we are using Basic authentication for the NodeJS app in Cloud Foundry. Click Save to complete.

Step 4 – Connect

Now we will connect our bot with Webchat so that we can reuse it later. Go to the Connect tab and select Webchat.

You can customize the look and feel of the Webchat user interface. I will just leave it as it is. Click Create to continue.

We need to add the following channelId and token information to the web module in SAP HANA XSA.

Step 5 – Rerun Web Module in SAP HANA XSA

Go back to SAP HANA XSA: Step 6 – Add Web Module and update the channelId and token variables in the HTML5 code with the values from Step 4 – Connect.

Restart the web module in SAP HANA XSA.

Open the link of the web module to start chatting with the bot.

Conclusion

We have gone through the required steps to build a simple chatbot that leverages SAP HANA XSA and SAP Conversational AI to search for and store email address information in the database. We also learned how to set up the app router in Cloud Foundry and how to establish the secure connection between SAP Cloud Platform and SAP HANA XSA with Cloud Connector.
Finally, we learned how to create expressions, intents, and entities, and how to set up the webhook in SAP Conversational AI.

I hope you find this tutorial useful. If you have any questions about it, feel free to ask us in the comments section below or go to SAP Answers.

References

Want to build your own conversational bot? Get started with SAP Conversational AI !

Follow us on

This site uses Akismet to reduce spam. Learn how your comment data is processed.