In this CodeLab we will integrate SAP HANA as a datastore for a Cloud IoT Core powered data stream.

What You'll Learn

Prerequisites

You must already have completed the following SAP HANA tutorials to use this one:

Turn on the Cloud IoT Core API

First we need to set up Cloud IoT Core. Go to the API Library and enable Google Cloud IoT Core:

Click "Enable API."

Create a Pub/Sub Topic

gcloud pubsub topics create iot-hana-pub
gcloud pubsub subscriptions create iot-hana-sub --topic=iot-hana-pub

Create your Cloud IoT Device Registry using the following gcloud command:

gcloud iot registries create iot-hana-registry --region=us-central1 \
  --event-notification-config=topic=iot-hana-pub

Generate RSA Public and Private Keys

Make a working area:

mkdir iot-hana
cd iot-hana
openssl req -x509 -newkey rsa:2048 -days 3650 -keyout rsa_private.pem \
    -nodes -out rsa_public.pem -subj "/CN=unused"

With your keys in hand, you're ready to register a device. Register your device using the public key.

gcloud iot devices create iot-weather-station-dev --region=us-central1 \
  --registry=iot-hana-registry \
  --public-key path=rsa_public.pem,type=rs256

Congratulations, you have now set up Cloud Pub/Sub, created your device registry, and added a device to the registry!

Create a working area and initialize NPM for your Virtual Device:

cd ~/iot-hana
mkdir weather-station-dev
cd weather-station-dev
touch index.js
npm init # accept defaults for all prompts
npm install -s jsonwebtoken mqtt uuid
edit index.js

Write the code for your IoT Device Application

Index.js

'use strict';

const fs = require('fs');
const jwt = require('jsonwebtoken');
const mqtt = require('mqtt');

const PROJECT_ID = process.env.GOOGLE_CLOUD_PROJECT;
const PRIVATE_KEY_FILE = '../rsa_private.pem';
const CLOUD_REGION = 'us-central1';
const REGISTRY_ID = 'iot-hana-registry';
const DEVICE_ID = 'iot-weather-station-dev';

const MQTT_CLIENT_ID = `projects/${PROJECT_ID}/locations/${CLOUD_REGION}/registries/${REGISTRY_ID}/devices/${DEVICE_ID}`;


const createJwt = () => {
  const token = {
          iat: parseInt(Date.now() / 1000, 10),
          exp: parseInt(Date.now() / 1000, 10) + 20 * 60,
          aud: PROJECT_ID
  };
  
  const privateKey = fs.readFileSync(PRIVATE_KEY_FILE);
  return jwt.sign(token, privateKey, {algorithm: 'RS256'});
};

let connectionArgs = {
        host: 'mqtt.googleapis.com',
        port: 8883,
        clientId: MQTT_CLIENT_ID,
        username: 'unused',
        password: createJwt(),
        protocol: 'mqtts',
        secureProtocol: 'TLSv1_2_method'
};

let client = mqtt.connect(connectionArgs);

const mqttTopic = `/devices/${DEVICE_ID}/events`;

const publishWeather = () => {
        console.log('publishing weather...');

        const payload = JSON.stringify({
                temp: (Math.random()*100).toFixed(2),
                
        });
        
        client.publish(mqttTopic, payload, {qos: 1}, (err) => {
                if (!err) {
                        console.log(`... ${payload}`);
                } else {
                        console.log(err);
                }
        })
        setTimeout(publishWeather, 1000);        
};

client.subscribe(`devices/${DEVICE_ID}/config`);

client.on('connect', (success) => {
        if (!success) return console.log('Client not connected.');
        
        publishWeather();
});

client.on('message', (topic, message, packet) => {
        console.log('message received: ', Buffer.from(message, 'base64').toString('ascii'));
});

Using your editor of choice run the following SQL Command:

CREATE TABLE "BIG"."IOT_WEATHER_STATION_INGEST" (USAF varchar(6), WBAN varchar(5), TEMP double);

We can create a Google Cloud Function to read data from the Device and store it in SAP HANA.

Enable the Cloud Functions API

Navigate to the Cloud Functions API page and click "Enable":

Create Working Area for a Cloud Function

cd ~/iot-hana
mkdir weather-station-ingest
cd weather-station-ingest
touch .npmrc .gcloudignore package.json index.js
edit .

Edit the NPM Configuration

Because SAP's SAP HANA nodejs library is hosted in a private NPM repository we need to specify a configuration for NPM so Google Cloud Functions understands how to download and compile it.

Edit the .npmrc file:

edit .npmrc

Copy the following into the file:

.npmrc

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

Configure nodejs Package

Edit package.json as the following:

package.json

{
        "name": "iot-hana-weather-station-ingest",
        "version": "1.0.0",
        "description": "",
        "main": "index.js",
        "scripts": {
                "test": "echo \"Error: no test specified\" && exit 1"
        },
        "author": "",
        "license": "ISC",
        "dependencies": {
                "@sap/hana-client": "^2.3.21",
                "cloud-functions-runtime-config": "latest",
                "safe-buffer": "^5.1.2"
        }
}

Avoid uploading needless files to Google Cloud Function

Edit .gcloudignore:

edit .gcloudignore

Set the content as the following:

.gcloudignore

.gcloudignore
.git
.gitignore

Write a Cloud Function

Edit index.js to be the following:

index.js

'use strict';

const Buffer = require('safe-buffer').Buffer;
const hanaClient = require('@sap/hana-client');
const runtimeConfig = require('cloud-functions-runtime-config');
const ENTRY_POINT = process.env.ENTRY_POINT;

const hanaConnection = () => {
        return Promise.all([
                runtimeConfig.getVariable(ENTRY_POINT, 'HANA_EXPRESS_LOCATION'),
                runtimeConfig.getVariable(ENTRY_POINT, 'HANA_EXPRESS_USER'),
                runtimeConfig.getVariable(ENTRY_POINT, 'HANA_EXPRESS_PASSWORD')
        ]).then((connectionValues) => {
                return new Promise((resolve, reject) => {
                        var connection = {
                                serverNode:        connectionValues[0],
                                uid:                 connectionValues[1],
                                pwd:                 connectionValues[2]
                        };
                
                        var hana = hanaClient.createConnection();
                        hana.connect(connection, (err) => {
                                if (err) return reject(err);
                                return resolve(hana);
                        });
                });
        });
};

const addRecord = (hana, record) => {
        return new Promise((resolve, reject) => {
                var params = [
                        record.usaf,
                        record.wban,
                        record.temp
                ];
                console.log(params);
                var stmt = hana.prepare('INSERT INTO BIG.IOT_WEATHER_STATION_INGEST VALUES(?, ?, ?)');
                stmt.execBatch([params], (err, rows) => {
                        if (err) return reject(err);
                        return resolve();
                });
        });
};

const iotIngest = (event, callback) => {
        const record = JSON.parse(Buffer.from(event.data.data, 'base64').toString());
        console.log("New record:");
        console.log(record);

        var handleError = (err) => {
                console.log(err);
                callback(err);
        };
        
        hanaConnection().then((hana) => {
                addRecord(hana, record)
                        .then(callback, handleError);
                
        }, handleError);
};

exports.iotIngest = iotIngest;

Setup Runtime Config

We don't want to hardcode sensitive or environmental information or configuration into cloud functions. We'll use the Runtime Configurator API of Deployment Manager to store and manage configuration for the function.

We need to configure the following SAP HANA, express edition environment variables:

Run the following commands in Google Cloud Shell:

gcloud services enable runtimeconfig.googleapis.com
gcloud beta runtime-config configs create iotIngest
gcloud beta runtime-config configs variables set HANA_EXPRESS_LOCATION '[YOUR_HANA_EXTERNAL_IP]:39015' --config-name iotIngest
gcloud beta runtime-config configs variables set HANA_EXPRESS_USER 'SUPER' --config-name iotIngest
gcloud beta runtime-config configs variables set HANA_EXPRESS_PASSWORD 'HanaRocks1!' --config-name iotIngest

Deploy the Google Cloud Function

Run the following in Google Cloud Shell:

npm install
gcloud beta functions deploy iotIngest --trigger-resource iot-hana-pub --trigger-event google.pubsub.topic.publish

Run the Virtual Device

In Cloud Shell run the following command:

cd ~/iot-hana/weather-station-dev
node index.js

This will start streaming data through IoT Core to Cloud Pub/Sub. Our Cloud Function will respond to each message and insert a record into SAP HANA.

Query Records from SAP HANA

In an SQL Editor configured for SAP HANA run the following query:

SELECT * FROM "BIG"."IOT_WEATHER_STATION_INGEST";

You should see records. If you re-run the query while the Virtual Device is active you should see additional records.

What We've Covered