Integrate SAP-HANA EML Library And TensorFlow Model Server (TMS) To Predict S&P 500 Index: Part 1: Install and Configure SAP-HANA

• Download source files (zip) - 2.2 KB
• Download source files (gz) - 1.9 KB

Introduction

This is the first article in a series of three articles, in which we will discuss the number of aspects of using SAP-HANA to perform the AI data analysis based on the example of predicting the S&P 500 Index stocks market indicator values using a TensorFlow model, previously built and exported to TensorFlow Model Server, which is integrated with SAP-HANA data analytical services, such as SAP-HANA's External Machine Learning (EML) library.

In this article, we will ground our discussion on the process of installing and configuring SAP-HANA server virtual machine, as well as performing a series of additional configuration and maintenance tasks such as installing SAP-HANA clients and EML library, Python's development tools, and packages, Docker.io 'aka' TensorFlow Model Server (TMS) to serve the S&P 500 AI prediction model, etc.

Specifically, in this tutorial, we will demonstrate how to download SAP-HANA Express Edition 2.0 from the SAP Download Center web-site and deploy an instance of a virtual machine running SAP-HANA services in the VMware Workstation virtualization environment. After that, we will perform the number of configuration tasks to install all development tools and libraries required for integrating SAP-HANA and TensorFlow Model Server to perform the AI data analysis in either Python client scripts or SAP-HANA SQL-engine level backend.

Download And Install SAP-HANA Express Edition 2.0

The first that we have to do right from the very beginning is to visit SAP HANA developers download center at https://www.sap.com/cmp/td/sap-hana-express-edition.html and sign up to obtain a free version of SAP-HANA Express Edition 2.0:

Since the registration has been completed successfully, we must obtain the SAP-HANA Express 2.0 download manager executable by clicking on the specific link in the SAP-HANA download page as follows:

Additionally, using the download manager application requires Oracle JDK 8 to be installed. Since that we must download and install it separately from:

• https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

before running the download manager application.

After installing Oracle JDK 8, we must run the download manager application (HXEDownloadManager_win.exe) to download all components required to deploy SAP-HANA Express Edition 2.0 virtual server on the development machine:

Specifically, we will need the following components to be downloaded via SAP-HANA download manager:

Server-Only Virtual Machine (hxe.ova) is the main SAP-HANA’s component downloaded by using the download manager application. The file being downloaded contains a virtual machine container image stored in Open Virtualization Format (OVF). We will use the hxe.ova container image to create the specific SAP-HANA server virtual machine in the VMware Workstation or VMware Player virtualization environment. SAP-HANA External Machine Learning (EML) Library is another component by installing which we enable the machine learning features of the SAP-HANA virtual server being deployed. The following component is typically installed after the virtual server maintenance process is being completed. Also, there’re two more components such as SAP-HANA Clients for Linux or Windows. The SAP-HANA clients for Linux are primarily used for providing local connectivity of various client applications, such as HDBSQL and SAP-HANA services both running inside the SAP-HANA server virtual machine environment, while the SAP-HANA clients for Windows allow the external applications to establish a connection to those services remotely, from the Windows-based development machine.

Create SAP-HANA Express 2.0 Server Virtual Machine

Since we’ve successfully downloaded all required SAP-HANA components, now it’s time to create SAP-HANA Express 2.0 server virtual machine using the specific container image from the hxe.ova file previously downloaded. Here’s a step-by-step guideline on how to create the SAP-HANA virtual machine:

1. In VMware Workstation, use the following command File -> Open… to open the hxe.ova container image:

   

2. Specify the name for a new virtual machine (for example hxeinst) and its location such as D:\VMs\hxeinst as shown in the figure above, and then click on Import button.

3. After the SAP-HANA server virtual machine has been created, specify the amounts of system memory and the number of processors used by this virtual machine:

   

   It’s highly recommended to allocate 32768 MB of system memory, as well as to dedicate 4 physical CPUs with 2 logical cores for SAP-HANA server virtual machine environment to provide the maximum performance and system stability.

4. Power on the SAP-HANA server virtual machine being created by using VM -> Power -> Start Up Guest command.

Pre-Configure SAP-HANA Express 2.0 Server

After the SAP-HANA server virtual machine has been created, we must complete several pre-configuration steps to have an ability to use it for a variety of applications.

Specifically, during this preliminary step, we must obtain the SUSE Enterprise Linux 12 SP1 for SAP Applications trial registration key to be able to configure an instance of SAP HANA server virtual machine previously created, upon our needs. To do that, we must visit https://www.suse.com/products/sles-for-sap/download/ and complete the registration steps required to obtain the specific key that we will further use during the SAP-HANA server maintenance phase. After completing the registration, the SLES 12 SP1 for SAP Apps trial registration key can be easily copied from the product download page:

Since the virtual machine has been powered on and booted up successfully, the first thing that must be done is to configure default keyboard layout and time zone, as well as to login into the SAP-HANA server using the following default administrator credentials as shown in the figure below:

Login: hxeadm; Password: HXEHana1

Also, please don’t forget to scrap the server’s IP-address (e.g.,192.168.0.131). Further, we will use the following IP-address to connect to the server being maintained.

Since we’ve entered the correct administrator login and password, we will be immediately prompted to change the default password to any arbitrary password of your choice. Additionally, we also will have to set the master SAP-HANA database password and finally toggle ‘y’ when asked to proceed with the further configuration process:

It’s highly recommended to set the same password for both SAP-HANA server administrator account and HANA database master to make the authorization process easier.

The figure below illustrates the SAP-HANA server configuration process:

The entire configuration process is very simple, but it takes some time until the server will launch some of the SAP-HANA services and re-create all systems and database security keys.

After the configuration process is successfully finished, the SAP-HANA server will automatically login with the system administrator credentials previously set:

As you can see, the process of pre-configuring the SAP-HANA server virtual machine is rather simple. You’re in just a few clicks away from creating a minimalist SAP-HANA virtual server configuration that can be used for a variety of applications. Further, we will discuss how to configure our SAP-HANA virtual server deployed to perform the data analysis using the SAP-HANA EML library and serve a variety of AI and machine learning models using the TensorFlow Model Server (TMS).

Configure SAP-HANA HDB Clients And External Machine Learning (EML) Library

To install and configure SAP-HANA EML library and TensorFlow Model Server (TMS), we will write down a bash-script that does the following:

1. Upload Clients_Linux_x86_64.tgz and Eml.tgz Files To SAP-HANA Virtual Server

Before performing any of the maintenance and configuration tasks, first, what must be done is extract configuration files from hxe_config.tar.gz archive, such as create_db.sql, create_user.sql, eml_setup.sql, hxe_config.sh on the development machine and upload them to /usr/sap/HXE/home/ directory using an FTP-client connection:

Also, we will need two more files such as clients_linux_x86_64.tgz and eml.tgz from our SAP-HANA downloads. Finally, what we have to do is upload these two files to the same directory on the server.

2. Connect to SAP-HANA Server Virtual Machine Using SSH

To have the ability to use this tutorial, we must first connect to a running instance of our SAP-HANA server virtual machine. To do that, we will use PuTTY SSH-client for that purpose. To connect to the server, we will use the server IP-address, such as 192.168.0.131, and administrator account credentials (e.g., login: hxeadm and password previously set). After the connection is established, we must change to hxeadm administrator home directory by using this command: cd ~ :

3. Register Your Trial Version of SLES 12 SP2 and Install SLES 12 SP2 SDK

Firstly, we must register our trial version of SLES 12 SP2 to have an ability to activate all SLES extensions and repositories. This is typically done by running the commands below:

sudo SUSEConnect --status-text
sudo SUSEConnect -r <YOUR_SLES_REGISTRATION_KEY> -e <EMAIL_ADDRESS>
sudo SUSEConnect -p sle-sdk/12.2/x86_64

4. Create TensorFlow Model Server Administrator Account

According to the best practices, we must create a TensorFlow Model Server administrator account and add it to the sudoers list by entering the following sequence of commands:

sudo useradd -m -d /home/tmsadm -c "TensorFlow Administrator" tmsadm
sudo passwd tmsadm
sudo bash -c 'echo "tmsadm ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers'

This is needed to avoid some side effects on standard hxeadm administrator account that is running an instance of SAP-HANA services.

5. Install SAP-HANA HDB Clients for Linux

The next step is to install SAP-HANA HDB clients by extracting the installation files from the archive and running a specific installation script from tmsadm account bash console. Make sure that an appropriate file clients_linux_x86_64.tgz has already been copied to /home/tmsadm/ directory:

cd ~
sudo cp clients_linux_x86_64.tgz /home/tmsadm/

sudo su -l tmsadm

cd ~

sudo tar -xvf ~/clients_linux_x86_64.tgz
sudo tar -xvf ~/hdb_client_linux_x86_64.tgz

sudo ~/HDB_CLIENT_LINUX_X86_64/hdbinst

Finally, after the SAP-HANA HDB clients have been successfully installed, we must add a path to the HDB client application to the tmsadm user profile:

sudo echo "export PATH=\$PATH:/home/tmsadm/sap/hdbclient" >> ~/.profile
source .profile

6. Install SAP-HANA EML Library

After SAP-HANA HDB clients were installed, we must also setup SAP-HANA EML library by executing the following commands from hxeadm account bash console. Before running this command below, make sure that the eml.tgz file has been already copied to the /usr/sap/HXE/home location:

sudo su -l hxeadm
    
cd ~

sudo tar -xvf ~/eml.tgz -C ~

Before running EML library installation script, please make sure that hxeadm user takes full ownership of the ~/HANA_EXPRESS_20 directory, sub-directories, and files:

sudo chown -R hxeadm ~/HANA_EXPRESS_20
~/HANA_EXPRESS_20/install_eml.sh

7. Install Python's Pip, Virtual Environment, SAP-HANA HDB Clients CLI for Python, TensorFlow, TensorFlow Serving APIs

Finally, after we’ve installed the SAP-HANA HDB clients and EML library, we must install Python’s Pip, virtual environment as well as required TensorFlow and TensorFlow Serving APIs packages. To do that, we must execute the following sequence of commands from tmsadm account bash console:

sudo su -l tmsadm

cd ~

# Download get-pip.py file using CURL:
curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py

# Run get-pip.py script to install Python’s Pip:
sudo python get-pip.py --trusted-host pypi.python.org

# Add Pip default path to the tmsadm account profile:
sudo echo "export PATH=\$PATH:/home/tmsadm/.local/bin" >> ~/.profile
source .profile

# Install and setup Python’s virtual environment:
cd ~
pip install --user virtualenv
virtualenv --python=python2.7 --system-site-packages ~/tms
source ~/tms/bin/activate

# Install SAP-HANA HDB client for Python, TensorFlow and TensorFlow Serving APIs:
sudo pip install --user /usr/sap/hdbclient/hdbcli-2.4.126.tar.gz
sudo pip install 'tensorflow==1.8'
sudo pip install 'tensorflow-serving-api==1.12.0'

8. Create TensorFlow Model Server Configuration File

After installing all required Python packages and tools, we must create a directory that will contain the specific models being served, as well as the proper configuration file used by TensorFlow Model Server (TMS) to load and serve those models:

sudo su -l tmsadm

mkdir -p ~/export
echo "model_config_list: {}" >> ~/export/models.config

A complete SAP-HANA EML and TensorFlow Model Server (TMS) maintenance bash-script can be downloaded by clicking on the hxe_config.tar.gz link at the top of this article’s page. Specifically, the following archive contains three files: clients_linux_x86_64.tgz, eml.tgz, and hxe_config.sh. We must upload these files via FTP-connection to /usr/sap/HXE/home/ directory on server.

After that, we must connect to the server using PuTTY SSH-client using hxeadm administrator account and use the following commands in the bash console to run this script:

hxeadm@hxehost:/usr/sap/HXE/HDB90> cd ~
hxeadm@hxehost:/usr/sap/HXE/home> ./hxe_config.sh <YOUR_SLES_REGISTRATION_KEY> <EMAIL_ADDRESS>

For those who have already followed the SAP-HANA configuration tutorial posted above, performing this step is unnecessary.

Configure SAP-HANA EML Library

Since we’ve successfully installed SAP-HANA EML library, now it’s time to create our project’s tenant database (e.g. “SP500”) and an appropriate user to have an ability to use the SAP-HANA EML library. This is typically done by using the following SQL-scripts:

create_db.sql

CREATE DATABASE SP500 ADD 'scriptserver' SYSTEM USER PASSWORD <MASTER_DB_PASSWORD>;

The following script above creates a new tenant database SP500 and enables the script-server component in the new tenant is created. To run this script, we will use the following command from the bash-console:

hdbsql -i 90 -d SYSTEMDB -u SYSTEM -p <MASTER_DATABASE_PASSEWORD> -m -I create_db.sql

After that, we must create a new tenant database user and grant all appropriate permissions to the newly created user, such as:

create_user.sql

CREATE USER SP500 PASSWORD  NO FORCE_FIRST_PASSWORD_CHANGE;
ALTER USER SP500 DISABLE PASSWORD LIFETIME;
GRANT AFLPM_CREATOR_ERASER_EXECUTE TO SP500;
GRANT AFL__SYS_AFL_AFLPAL_EXECUTE TO SP500;
GRANT DATA ADMIN TO SP500;
GRANT IMPORT TO SP500;

GRANT EXECUTE on _SYS_REPO.GRANT_ACTIVATED_ROLE TO SP500;

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

To run the following script above, execute the following command:

hdbsql -i 90 -d SP500 -u SYSTEM -p <MASTER_DATABASE_PASSEWORD> -m -I create_user.sql

Finally, since both the new tenant database and new database user have been successfully created, we can use the following SQL-script to maintain a new SAP-HANA EML library configuration:

SET SCHEMA SP500;

INSERT INTO _SYS_AFL.EML_MODEL_CONFIGURATION VALUES_
         ('sp500_model' , 'RemoteSource', 'TensorFlow');

CREATE REMOTE SOURCE "TensorFlow" ADAPTER "grpc" CONFIGURATION 'server=localhost;port=8500';

---DROP TABLE UPDATE_CONFIGURATION_PARAMS;
---DROP TABLE UPDATE_CONFIGURATION_RESULT;
---DROP PROCEDURE UPDATE_CONFIGURATION;

CREATE TABLE UPDATE_CONFIGURATION_PARAMS ("Parameter" VARCHAR(100), "Value" VARCHAR(100));
CREATE TABLE UPDATE_CONFIGURATION_RESULT _
       ("Key" VARCHAR(100), "Value" INTEGER, "Text" VARCHAR(100));

CREATE PROCEDURE UPDATE_CONFIGURATION() AS
BEGIN
  DECLARE CURSOR CUR FOR
      SELECT VOLUME_ID FROM SYS.M_VOLUMES WHERE SERVICE_NAME = 'indexserver';
  FOR CUR_ROW AS CUR DO
      EXEC 'CALL _SYS_AFL.EML_CTL_PROC_
      (''UpdateModelConfiguration'', UPDATE_CONFIGURATION_PARAMS, UPDATE_CONFIGURATION_RESULT)'
         || ' WITH OVERVIEW WITH HINT(ROUTE_TO(' || :CUR_ROW.VOLUME_ID || '))';
  END FOR;
END;

TRUNCATE TABLE UPDATE_CONFIGURATION_RESULT;
CALL UPDATE_CONFIGURATION();
---SELECT * FROM UPDATE_CONFIGURATION_RESULT;

CREATE TABLE CHECK_PARAMS ("Parameter" VARCHAR(100), "Value" VARCHAR(100));
INSERT INTO CHECK_PARAMS VALUES ('Model', '*');
CALL _SYS_AFL.EML_CHECKDESTINATION_PROC(CHECK_PARAMS, ?);

To run the following script above, execute the following command:

hdbsql -i 90 -d SP500 -u SP500 -p <SP500_USER_PASSWORD> -m -I eml_setup.sql

After running all these three scripts, a new tenant database, a new database user and SAP-HANA EML configuration are successfully created.

Install Docker and TensorFlow Model Server (TMS)

The last SAP-HANA virtual server configuration step is to install TensorFlow Model Server (TMS). To do that, we must first install Docker.io virtualization containers environment by running the following commands after performing all those configuration steps listed above:

sudo su -l hxeadm
    
sudo zypper addrepo https://download.opensuse.org/repositories/
      Virtualization:containers/openSUSE_Leap_15.0/Virtualization:containers.repo
    
sudo zypper dist-upgrade
sudo zypper install docker

To have an ability to run docker as a service, we must additionally use the following commands in hxeadm administrator bash console:

sudo systemctl start docker
sudo systemctl enable docker
sudo chkconfig docker on

After we’ve successfully installed Docker.io, we must pull the TensorFlow/Serving:Latest container from the docker repository. To do that, we must run the following command:

sudo docker pull tensorflow/serving:latest

Later, we will thoroughly discuss how to run the TensorFlow Model Server to serve the specific TensorFlow models.

Connect to SAP-HANA Using Eclipse IDE And SAP-HANA Studio Add-ins

Since we’ve already configured SAP-HANA server for using External Machine Learning (EML) library and TensorFlow Model Server, we can connect to our running instance of SAP-HANA server from the development machine using Eclipse IDE and SAP-HANA Studio add-ins development tools. To do that, we must first download Eclipse IDE at https://www.eclipse.org/downloads/, install and run it.

We must make sure that we will complete the following installation steps:

1. Run the following command Help -> Install New Software… in the Eclipse IDE menu bar
2. In software installation dialog window, add the URL: https://tools.hana.ondemand.com/2019-03 and press Enter:

   

3. Select all required SAP-HANA features and toggle Next
4. On the next wizard page, toggle Next
5. Confirm the License Agreements and choose Finish to start the installation

To connect to a running instance of SAP-HANA server, do the following:

1. Open Eclipse IDE application, scroll down the page and select Open Administrator Console:

   

2. In the Eclipse SAP-HANA Administration Console toolbar, toggle Connect... button and select Add System... command:

   

In the dialog box shown in the figure above, enter the SAP-HANA server virtual machine IP-address (e.g. 192.168.0.131), instance number, which is '90'. Also, select Multiple containers and Tenant database, provide the database name (e.g., "SP500"), and click on Next button.

In the next wizard's page, select Authentication by database user and enter User Name and Password to connect to the tenant database SP500 previously created. Finally, click Finish button to establish the database connection:

Conclusion

In this article, we've discussed how to deploy SAP-HANA server based on SLES 12 SP2 Linux distribution and provided guidelines on how to enable all SAP-HANA services and features required for perform AI data analysis on the SAP-HANA SQL-engine backend. The following tutorial is very useful for those who plan to develop any of SAP-HANA-based solutions locally on a development machine. However, the entire SAP-HANA-based solution being created can be easily migrated to the cloud with just minor changes in the configuration scripts, Python codes, and the database itself.

In the succeeding article in the series, we will discuss how to build, re-train and export the TensorFlow AI artificial neural network-based model that can be used for the S&P 500 Index indicator values prediction.

History

• 8^th September, 2019: Initial version published