Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Machine learning basics with SAP HANA Express edition & Tensorflow Part 1.

shivamshukla12
Contributor
‎07-08-2018 5:59 AM
Hi Just thought to share my learning experience of ML with SAP HANA, Goal was to consume tensor-flow prediction model from SAP HANA using external machine library (EML), for achieving this goal we must complete a set of prerequisites before starting this activity.once you complete all the following tasks you would be able to communicate with tensorflow from HANA & for windows 10 user there is a Ubuntu terminal which i have setup hence your tensorflow model server & vm machine will be running on same machine. please follow the below steps to start & finish.

 

Note - I strictly followed the video tutorial by philip.mugglestone on SAP HANA Academy & also referenced machine learning in a box by abdel.dadouche.

Objective – Learn how to use tensorflow capabilities using SAP HANA External Machine library (EML) to consume any model, so if you are running SAP HANA 2.0 Instance make sure you have done the installation of EML so here are the checks which you have to perform if eml is installed or not but for installation you can walk through below sap help document.

 

Blog Post referenced links - https://github.com/saphanaacademy/EML                                                                                                                https://www.youtube.com/saphanaacademy

 

Prerequisites 

Note - I am assuming you have installed SAP HANA Express edition

 

Please follow below steps for installing bash on Ubuntu on windows 10 & tensor-flow serving on Ubuntu.

  1. Turn windows feature on for windows subsystem for Linux go to features in windows 10 & check the checkbox.Or open the power-shell as administrator & run below command –Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux

  2. Run command prompt & execute command – lxrun /install follow the further steps enter username/password according to your choice. if installation is suuccessful you will see the below screen                                                                                                                                                                       

  3.  Check python version type Python -V                                                                                                                                                                                                                                                                                                                                                                                                                                       From above we can see python 2.7 which is really well suited for Tensor-flow installation on Ubuntu terminal.

  4. Now for Tensor-flow installation on Ubuntu please execute all the below steps I have capture from SAP HANA Academy GitHub repo. section GithubExternalMachineLibrary.

  5. Go to Code Snippets EML 07 Install TensorFlow Serving.txt & follow the installation steps once installation is successful you can see the below tensorflow environment on your Ubuntu terminal.                                                                                                                                                           Tensorflow Serving Installation                                                                                                                                                   


Here you finished both the installation on Win10 you have tensorflow running on Ubuntu        Terminal & Hana Express edition running on Virtual Machine, lets start the next activities                    some more installation for external machine learning on HANA Express edition.                                  login into Hana Express Edition

EML Installation - login to hxehost using XSA_ADMIN - (Assuming you have setup HANA 2.0 SPS02/03)



How to install External Machine library on HANA Express edition follow the below sap help document.

SAP HANA External Machine Library Installation

Download eml.tgz file using download manager -

Check download directory & Change directory if required -  cd /usr/sap/HXE/home/bin

Execute – hxe_gc.sh  (you will see free & used memory)

Download eml file - HXEDownloadManager_linux.bin linuxx86_64 vm eml.tgz



Important thing to check here is the download directory, now go to the download directory & extract the eml.tgz file.



Run tar -xvzf eml.tgz

Now install the eml on HANA –



Navigate to the folder HANA_EXPRESS_20 & you can see the file install_eml.sh , run the command to install eml on hana



You will see the message like eml installation is successful.

Login to hxehost using system user before this make sure your XSA is up & running.

https://hxehost:39030/

if this is fine then move to web IDE on port 53075.

https://hxehost:53075/

login into this using XSA_DEV user & password is what you set during installation on SAP HANA 2.0 SPS03, click on the database explorer –



Add the database & choose multitenant option here as SAP HANA 2.0 Supports multitenant database only, once this is added in explorer open sql console for this database & check your eml installation here.

Code snippets which I have used across can be found here –

https://github.com/saphanaacademy/EML (all the credit goes to Philip )

 

SELECT * FROM "SYS"."AFL_AREAS" WHERE AREA_NAME = 'EML';

SELECT * FROM "SYS"."AFL_PACKAGES" WHERE AREA_NAME = 'EML';

SELECT * FROM "SYS"."AFL_FUNCTIONS" WHERE AREA_NAME = 'EML';



 

You will be able to see all the results here, which states eml installation is perfectly fine & you can see functions like PREDICT & PREDICTM for single & multiple scoring which I would be using later in the example.

Check tenant database  -  SELECT * FROM SYS.M_DATABASES;   you can see HXE tenant is available



ALTER DATABASE HXE ADD 'scriptserver';  it will start all the services on tenant now login into Tenant (HXE) database using SYSTEM USER.



Run – select * from SYS.M_SERVICES & you can see all the services (like xscontroller & indexserver) are running & active



 

So here we complete out setup including HANA Server installation & XSA , EML & able to login into HXE tenant.

Now create a user for machine learning & assign all the authorization related to monitoring & creation of EML procedure, remote source execute below commands.

-- create user

CREATE USER EMLUSER PASSWORD Password1;

-- authorize EML administration

GRANT MONITORING TO EMLUSER;

GRANT CREATE REMOTE SOURCE TO EMLUSER;

GRANT SELECT, UPDATE, INSERT, DELETE ON _SYS_AFL.EML_MODEL_CONFIGURATION TO EMLUSER;

 

-- authorize creation & removal of EML procedures

GRANT AFLPM_CREATOR_ERASER_EXECUTE TO EMLUSER;

-- authorize execution of EML procedures

GRANT AFL__SYS_AFL_EML_EXECUTE TO EMLUSER;

 

Ubuntu Part - 

 

Before we login into HXE tenant using EML user we will have to do some installation on Ubuntu Bash so open bash on ubuntu on windows & activate tensorflow virtual environment.

Run - source ~/tensorflow/bin/activate

PS1=(‘$>’)



Clone the git repository - git clone --recurse-submodules https://github.com/tensorflow/serving

Go to serving directory cd serving



 

Train & save model

sudo mkdir /MNIST_model

sudo chmod 777 /MNIST_model

python tensorflow_serving/example/mnist_saved_model.py /MNIST_model

Execute -  saved_model_cli show --dir /MNIST_model/1 --all



It shows that the method is taking image binary data as an input & going to give 10 string & 10 numbers in output.

 

Execute -  tensorflow_model_server --port=9000 --model_name=mnist --model_base_path=/MNIST_model/



This is going to serve the Tensor-flow model in this case MNIST model at port 9000 (default port)

You can see tensor-flow model MNIST is running at port 9000 so here we done with out model import & execution now we have to call the method from our SAP HANA Server to check if we can utilize the mixed capability of eml & tensor-flow.

 

Come to hxetenant -

 

Login into HXE tenant using EML user

Open SQL Console  -

 

-- clean up

DROP REMOTE SOURCE "TensorFlowModelServer";

DROP TABLE "PARAMETERS";

DELETE FROM "_SYS_AFL"."EML_MODEL_CONFIGURATION" WHERE "Parameter"='RemoteSource' and "Value"='TensorFlowModelServer';

 

-- create remote source

CREATE REMOTE SOURCE "TensorFlowModelServer" ADAPTER "grpc" CONFIGURATION 'server=0.0.0.0;port=9000';

It Is going to Create remote source for tensorflow model server & adapter is grpc (google remote procedure call) server = 192.168.0.103 (check ip address of your ubuntu server installed on windows 10 ifconfig) : port=9000 default port

 

-- register model

INSERT INTO "_SYS_AFL"."EML_MODEL_CONFIGURATION" VALUES ('mnist', 'RemoteSource', 'TensorFlowModelServer');

SELECT * FROM "_SYS_AFL"."EML_MODEL_CONFIGURATION";



Your model has been registered here in _SYS_AFL schema as the user is authorized to for performing create/update/delete activities.

 

 

-- create parameters table (used in subsequent calls)

CREATE TABLE "PARAMETERS" ("Parameter" VARCHAR(100), "Value" VARCHAR(100));

 

-- apply registered models

CALL "_SYS_AFL"."EML_CTL_PROC" ('UpdateModelConfiguration', "PARAMETERS", ?);

This control procedure will require the parameters table for execution as in case of multiple remote source we need to pass resource name we are going to communicate with.



 

-- verify model is up and running on remote source

INSERT INTO "PARAMETERS" VALUES ('Model', 'mnist');

CALL "_SYS_AFL"."EML_CHECKDESTINATION_PROC" ("PARAMETERS", ?);

 



You will get return as 0 if it is successfully talking to tensorflow from HANA

Here you come to the final stage where you are going to pass some input data of MNIST model from HANA to TF & you can expect some intelligent output like you are passing an image data of a numeric digit in binary format & tensorflow will tell you what that number is –

So open sql console using emluser –

-- clean up
DROP TYPE "T_PARAMS";
DROP TYPE "T_DATA";
DROP TYPE "T_RESULTS";
DROP TABLE "SIGNATURE";
DROP TABLE "PARAMS";
DROP TABLE "RESULTS";
DROP VIEW "V_DATA";
CALL "SYS"."AFLLANG_WRAPPER_PROCEDURE_DROP" ('EMLUSER', 'SCORE_DIGITS');

Remove any existing type of same name before we start or in case of any error you can again clean & recreate all this.

 

Go to your bash on ubuntu -  activate tensorflow environment

source ~/tensorflow/bin/activate

saved_model_cli show --dir /MNIST_model/1 –all

 

Check signature of the method (serving_default) which we are going to consume from HANA

You can see the input parameter is accepting one string of binary format & giving output (two tensors) in classes & scores & Here class is related to classifier which we have built in python & it will give you result based on the input & score will tell you the accuracy of that particular class



 

Import MNIST model data.

Download mnist file from here  - https://github.com/saphanaacademy/EML/raw/master/Source%20Data/mnist.tar.gz



Right click on catalog & click import catalog object & choose mnist.tar.gz file & click on import button in the bottom. Once you are done, will be able to see the data in eml catalog.

 

MNIST table definition & table data



 

Input.

-- create table types

Execute below statement for parameter & data table types

CREATE TYPE "T_PARAMS" AS TABLE ("Parameter" VARCHAR(100), "Value" VARCHAR(100));

CREATE TYPE "T_DATA" AS TABLE ("x" BLOB);

Create containers for passing the data as input to procedure , parameter table would be storing the information about the model & model name in our case (mode - MNIST)

T_DATA table would be containing the binary data for the image which would be used as a input without  label to tensorflow & in output we would be getting the label back.

 

Output

-- use with rev 20

CREATE COLUMN TABLE "SIGNATURE" ("POSITION" INTEGER, "SCHEMA_NAME" NVARCHAR(256), "TYPE_NAME" NVARCHAR(256), "PARAMETER_TYPE" VARCHAR(7));

INSERT INTO "SIGNATURE" VALUES (1, 'EMLUSER', 'T_PARAMS', 'IN');

INSERT INTO "SIGNATURE" VALUES (2, 'EMLUSER', 'T_DATA', 'IN');

INSERT INTO "SIGNATURE" VALUES (3, 'EMLUSER', 'T_RESULTS', 'OUT');

CALL "SYS"."AFLLANG_WRAPPER_PROCEDURE_CREATE" ('EML', 'PREDICT', 'EMLUSER', 'SCORE_DIGITS', "SIGNATURE");

 

I am using old version of tensorflow hence using result for table structure of rev21 -  execute above statement for result table



 

-- create signature table then generate stored procedure

CREATE COLUMN TABLE "SIGNATURE" ("POSITION" INTEGER, "SCHEMA_NAME" NVARCHAR(256), "TYPE_NAME" NVARCHAR(256), "PARAMETER_TYPE" VARCHAR(7));

INSERT INTO "SIGNATURE" VALUES (1, 'EMLUSER', 'T_PARAMS', 'IN');

INSERT INTO "SIGNATURE" VALUES (2, 'EMLUSER', 'T_DATA', 'IN');

INSERT INTO "SIGNATURE" VALUES (3, 'EMLUSER', 'T_RESULTS', 'OUT');

CALL "SYS"."AFLLANG_WRAPPER_PROCEDURE_CREATE" ('EML', 'PREDICT', 'EMLUSER', 'SCORE_DIGITS', "SIGNATURE");



Create stored procedure wrapper which in turn will create a score_digits procedure in EML Schema & that’s going to use the signature table.

Procedure SCORE_DIGITS –



 

So you can see signature of the procedure above as two importing parameter modelname & data (T_PARAMS contains model information) T_DATA (contains binary data of image)

 

Execute below commands as well

       CREATE TABLE "PARAMS" LIKE "T_PARAMS";

CREATE TABLE "RESULTS" LIKE "T_RESULTS";

 

-- data to be scored

DROP VIEW "V_DATA";

CREATE VIEW "V_DATA" AS

SELECT "Image" AS "x"

FROM "MNIST"

WHERE "Label" = 7

 

Create one view with data for image with label 7.



 

INSERT INTO "PARAMS" VALUES ('Model', 'mnist');

 

Model & ModelName (mnist in our case)

Call procedure Score digits -

So here we are passing parameter table & image data in procedure .

 

CALL "SCORE_DIGITS" ("PARAMS", "V_DATA",?);

So magic here is we are using capabilities of tensorflow with eml HANA , so you can do a lot by storing data in hana database & can utilize the capabilities of both eml & tensorflow.



 

And we got the output two tensors containing classes & scores but we see here Class1 is 7 & accuracy of Class1 is 99% and we can also validate this using our MNIST model data we uploaded.

I passed the data for 4 then after this & you can see the class & scoring below for that.



 

In the same manner we can predict multiple digits using PREDICTM function , I will try to add that in another ML & HANA blog.

 

Stay tuned for some more ML blogs in future...
1 Comment
Labels in this area
Popular Blog Posts