Post Google Next '19 curiosity - Playing with Cloud Run and SAP HANA

[lsubatin]

So what do you do to decompress after an overnight flight, an intense week of fruitful interactions and an endless supply of information?



Play with Google Cloud Run! Cloud Run was one of the over 100 announcements from Google Next '19. You can watch the session by the product managers here: https://www.youtube.com/watch?v=16vANkKxoAU

This is my initial experiment. I have some previous experiments with the Google App Engine, cloud functions and Google Kubernetes Engine so I could not wait to try this one out.

I began this quickstart and got easily carried away: https://cloud.google.com/run/docs/quickstarts/build-and-deploy

I will build an image with Node.js to perform a fuzzy search on SAP HANA.

Background: Crunching the data

I uploaded a couple of food databases ( Food DB  and  Open Food Facts ) into my favorite in-memory platform, SAP HANA.

This database contains names of commercial products, their ingredients and a nutrition score, among others.

I like combining the multi-model queries, so here is part of what I did with the data:

1. Ran a Multi-Class Logistic Regression algorithm to determine the nutrition score of the full database (only 20% of the records had the value). Shout-out to abdel.dadouche for the help with the statistics)


2. Enabled fuzzy search on some text fields to make it easier to find the foods I like


3. Used a MapReduce function to split the ingredients (all together in the ingredients field, separated by a comma). I shamelessly reused this from rich.heilman's Open SAP slides.


4. Built a graph so everyone choosing food at the booth is related to other people choosing the same ingredients... Who doesn't want to meet fellow foodies?

Enable the Cloud Run API

Easy, from the console dashboard: APIs and Services-> Dashboard -> + Enable APIs and Services

Prepare the development environment

The instructions say you should download the gcloud SDK. I prefer the online editor and web cloud console, so I’m skipping this and clicking on that magical icon.



 

If running locally, make sure everything is up to date:

gcloud components install beta

gcloud components update

These are all the files I think I'll need:

touch .npmrc package.json index.js Dockerfile

edit .npmrc 

Now I have the fancy editor and newly-created files:



I will need the Node.js module to connect to HANA that lives in SAP’s npm repo, so the following line belongs in that .npmrc file:

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

Making sure that configuration is working:



Now, moving on to package.json, this is what I have there:

{

    "name": "knative-accessing-hana",

    "version": "1.0.0",

    "description": "Post-conference curiosity",

    "main": "index.js",

    "scripts": {

        "start": "node index.js"

    },

    "author": "Lucia",

    "dependencies": {

        "@sap/hana-client": "^2.4.126",

        "express": "^4.16.4"

    }

}

 

The juicy piece, the index.js file running a sample fuzzy search on the names of the products:

'use strict';

const dbClient = require('@sap/hana-client');

const express = require('express');

const app = express();



app.get('/', (req, res) => {



    const hanaConnection = () => {

        return new Promise((resolve, reject) => { 



            const host = process.env.HANA_HOST || '133.456.789.452:39015';

            const user = process.env.HANA_USR || 'SQLDEV';

            const password =  process.env.HANA_PWD || 'nOTp455w0Rd';

            const hdbParams = {

                serverNode: host,

                uid: user,

                pwd: password

            };



            var conn = dbClient.createConnection();

            conn.connect(hdbParams, (err) => { 

                if (err) {

                    res.type("text/plain").status(500).send(`ERROR: ${err.toString()}`);

				return reject();

                } 

                return resolve(conn);

            });

            

        });



    };



    const readFood = (conn, results) => {

        return new Promise((resolve, reject) => {

            let searchParam = req.query.searchFood; 

            if (typeof searchParam === "undefined" || searchParam === null) {

			    searchParam = 'Nutella';

		    } 

            console.log('Parameter ' + searchParam);

            let query = 'SELECT TOP 10 * FROM "FOOD"."COMM_FOODS" where NUTRITION_GRADE_FR IS NULL ' +  

                                        'AND ENERGY_100G > 0 ' +

                                        'AND contains(product_name, ? , fuzzy(0.8));'

            console.log('Query :' + query);

            let stmt = conn.prepare( query );

            stmt.exec([searchParam], (err, results) => {

			    if (err) {

                    res.type("text/plain").status(500).send(`ERROR executing statement: ${err.toString()}`);

				    return reject();

                }

                var result = JSON.stringify(results);

				res.type("application/json").status(200).send(result);

			    return resolve();

		    });

        });

    };





    hanaConnection()

    .then( (conn) => {  

        readFood(conn)

        .catch(err => {

		    return res.type("text/plain").status(500).send(`ERROR: ${err.toString()}`);

	}) } )

    .catch(err => {

		return res.type("text/plain").status(500).send(`ERROR: ${err.toString()}`);

	});



});    







const port = process.env.PORT || 8080;



app.listen(port, () => {

    console.log('I am listening on port', port);



});

 

Test the Node.js module from the console

You can quickly test from here before deploying:



 

Just install the dependencies first:

$ npm install express

$ npm config set @sap:registry https://npm.sap.com

$ npm install @sap/hana-client

 



 

And run:



Using this button on the top right corner:



 

A new tab is opened and I can add the search term to my query:

Prepare a Docker image

I’m just pulling the official Node.js image here and copying the local files.

# Use the official Node.js 10 image.

# https://hub.docker.com/_/node

FROM node:10



# Create and change to the app directory.

WORKDIR /usr/src/app



# Copy application dependency manifests to the container image.

# A wildcard is used to ensure both package.json AND package-lock.json are copied.

# Copying this separately prevents re-running npm install on every code change.

COPY package.json package*.json ./

COPY .npmrc ./



# Install production dependencies.

RUN npm install --only=production



# Copy local code to the container image.

COPY . .



# Run the web service on container startup.

CMD [ "npm", "start" ]

 

Build the container image

This is a simple command, just replacing my project ID.

gcloud builds submit --tag gcr.io/[PROJECT-ID]/run-hana-run

And SUCCESS is what we all want to see here:

Run the Container image

The code is expecting the information to connect to HANA in environment variables, because this may be quick and dirty but I don't want to hardcode credentials after my initial tests:



 

So I'm passing the credentials as environment variables for my container:

gcloud beta run deploy --image gcr.io/PROJECY_ID/run-hana-run --set-env-vars=HANA_HOST=123.456.789.55:39015,HANA_USR=SQLUSER,HANA_PWD=SomePASSWORD

 



There it is! Isn't it yummy?



Here is my service in Cloud Run, including the environment variables and logs:



 

And I can easily deploy a new version:



 

They definitely kept the promise: this was very easy and I only needed to focus on the code.

If you want to try this out yourself, these are the instructions to deploy SAP HANA, express edition on Google Cloud and here is one of many ways to load data.

What are your experiments about? Let me know on Twitter or LinkedIn !