How to connect HANA DB on Cloud Foundry environmen...

yutaroshimizu · ‎06-03-2019

【Introduction】

There are 2 different environments of SAP Cloud Platform(SAP CP) called Neo environment(Neo) and Cloud Foundry envirnment(CF).
SAP CP Neo works on a server provided by SAP.
On the other hand, SAP CP CF works on third party server such as Amazon Web Services (AWS) , Microsoft Azure,Google Cloud Platform(GCP).

Also, 2 cloud environemnt's functionalities are different from each other.
For instance, CF has IoT service and HANA as a Service. Neo has HANA1.0.
You can choose Neo env and CF env, or both envs according to your business case.

In this blog, I attempted to verify that Neo procedure call to remote procedure on CF, because I want to process data with PAL in HANA2.0 on CF. Specifically, I want to call FFT functionality in PAL.

This is what I'd been wanting to do.

I come up with good idea below to realize my concepts.
1.Create empty table on CF.

2.Connect to CF and Create virtual table on Neo.
If you update or insert the virtual table, then a table is updated on CF.

3.Insert data to the virtual table to run SQL script.

4.Create stored procedure which wrap FFT procedure.

5.Create a virtual table.

6.Call a virtual table.

【IT Architechture】

You need to use SDI(Smart Data Integration) to implement binding HANA on Neo and HANA on CF.
SDI compose 'Data Provisioning Server' and 'Data Provisioning Agent'.
DP server is activate to import Delivery Unit to Neo.
You need to set up DPAgent on your machine such as Linux or Windows machine.
I will show IT Architecture outline below.

If you construct system architecture above, I will show you instruction below.

【Topic/table of Content】

Preparation

Import a DeliveryUnit

DPAgent settings

User Creation

Configuration of HANA connection setting on DPAgent

Agent Registation

HANA Adapter Registation

Connection to HANA

Remote Sources Creation

Virtual Tables Creation

Virtual Procedure Creation

【Environment】

You need to prepare 2 cloud environments to connect each HANA tables and also you need to laptop PC or server to install a DPAgent.
Cloud Environment ：2 cloud environments as Cloud Foundry and Neo
Database ：2 Databases as HANA2.0(CF) and HANA1.0(Neo)
PC/Service 　：1 PC （※ In my case, I used WindowsPC）

【Version】

WindowsPC Edition 　：Windows 10 Enterprise 2016 LTSB
CPU 　：Intel(R)Core(TM)i5-7300U CPU @ 2.60GHz 2.71GHz
Memory(RAM) 　：8.00GB
HANA version(Neo) 　：1.00.122.23.1548298510 (fa/hana1sp12)
HANA version(CF) 　：4.10.5

【1. Preparation】

First, you download a zip file and a sar file below on SAP software download page.
・「HANAIMDP03P_11-70000027.ZIP」
・「IMDB_DPAGENT200_03P_53-70002517.SAR」
The file「HANAIMDP03P_11-70000027.ZIP」include a tgz file「HANA_IM_DP_03_11.tgz」.
If you type an search form keyword 'HANA DP Agent' and 'SDI', then you can easily find these files.

Type an input form a keyword 'HANA DP Agent'

Select a link「IMDB_DPAGENT200_03P_53-70002517.SAR」
（※ Select a suitable your PC）

Type a SAP S User ID. After that, you can download a file「IMDB_DPAGENT200_03P_53-70002517.SAR」.

Type a keyword 'SDI' for a search form.

Select 'SAP HANA SDI, SAP HANA smart data integration, Maintenance Product'.

Select 'SAP HANA SDI 1.0'.

Select 'COMPRISED SOFTWARE COMPONENT VERSIONS'.

Select 'HANA DP 1.0'.

Download 「HANAIMDP03P_11-70000027.ZIP」

You decompress the ZIP file and you get a 「HANA_IM_DP_03_11.tgz」file.
In addition, you should download a sapcar.exe file, because you will use sapcar command later chapter.

I show you software download page below.
・「https://launchpad.support.sap.com/#/softwarecenter」

【2. Import a DeliveryUnit】

In this step, you import a downloaded file「HANA_IM_DP_03_11.tgz」into HANA system(Neo) with file import functionality of HANA Studio.

Open a context menu on left side HANA Studio and select 'Import' option.

Select a 'Delivery Unit' from 'Import'.
Next, 'Import Through Delivery Unit' window opens. Select a HANA scheme which you created.

There is file selection on the top of 'Import Through Delivery Unit' window.
Check a 'Client' radio button and select file reference.

Select a file 「HANA_IM_DP_03_11.tgz」 which you downloaded in the previous chapter.

While a moment, you can see objects/packages in a window「Object import simulation」.
Finally, you push Finish button. A file import process starts.

【3. DPAgent settings】

Firstly, you boot a hdbsetup.exe in a compressed file and input all mandatory fields on the Data Provisioning Agent window.
You can start up a DPAgent, after you created DPAgent.

Input a downloaded file「IMDB_DPAGENT200_03P_53-70002517.SAR」to your folder.

Unzip the compressed file with sapcar command.

sapcar -xvf IMDB_DPAGENT200_03P_53-70002517.SAR

Boot a hdbsetup.exe in the compressed file. While a moment, 「SAP HANA Data Provisioning Agent」window will open.

There are 5 Steps in「SAP HANA Data Provisioning Agent」section below.
Step1「Define dataprovagent Properties」
Select a folder icon in the left side 「Install new SAP HANA Data Provisioning Agent」window and you specify your folder.

Step2「Define Installation Properties」
Input agent name for 「Agent Unique Name」form and also input WindowsPC account'domain name and user for「Domain\Username」 and 「Password for Agent service user」form which you use.
例)
Agent Unique Name ：「dpagent_dev」
Domain\Username for Agent service ：「QUNIE\xxxx」
Password for Agent service user ：「****」

Step3「Review & Confirm」
Push an install button after you confirm details.

Step4「Install Software」
Wait a moment until install process is finished.
Step5「Finish」
Push a 'Finish' button after you successfully complete the installation process.

Change a current directory which you create in Step1.
例)

cd C:\usr\sap\dataprovagent\bin

agentcli --configAgent

You can see 「DPAgent Configuration Tool」on your console window.
Select a 「2. Start or Stop Agent」menu in the Option 1～10,q,b.
Next, Select a 「1. Start Agent」menu in Option 1～3,q,b on 「Start or Stop Agent」

【4. User Creation】

You should prepare 2 users below which will are required by a HANA Connection Settings in「Agent Admin HANA User」selction.
・「SDI_USER」
・「SDI_DP_AGENT」

Select 'Security' -> 'User', after you logon HANA schema in your HANA Studio.

Create 2 users below by right click 'User'.
・「SDI_USER」
・「SDI_DP_AGENT」

Configure Roles,Priviledges for created「SDI_USER」 below.
・Granted Roles
「sap.hana.xs.ide.roles::CatalogDeveloper」
「sap.hana.xs.ide.roles::EditorDeveloper」
「sap.hana.xs.ide.roles::SecurityAdmin」
・System Priviledges
「AGENT ADMIN」
「ADAPTER ADMIN」
「CREATE REMOTE SOURCE」
・Application Priviledges
「sap.hana.im.dp.admin::Administrator」

Configure Roles, Privileges for created「SDI_DP_AGENT」below.
・Application Privileges
「sap.hana.im.dp.proxy::AgentMessaging」

Access HANA workbench and also change the initial password for 「SDI_USER」 and 「SDI_DP_AGENT」.

【5. Configuration of HANA connection setting on DPAgent】

Input Connection Settings for 「DPAgent Configuration Tool」 window to connect DPAgent to HANA DB on Neo environment.

Select 「6.SAP HANA Connection」 option in「DPAgent Configuration Tool」
After that, select 「1. Connect to SAP HANA on Cloud(HTTP/HTTPS)」option in「SAP HANA Connection」

Configure connection settings as you follow steps below.
Enter Use HTTPS[true]:Valid optionstrue|false
⇒true
Entry Host Name[xxxx.jp1.hana.ondemand.com]:
⇒[HANA post which you created]
例）
xxxx.jp1.hana.ondemand.com

Enter Post Number[443]:
⇒443

Enter Agent Admin HANA User[SDI_USER]:
⇒SDI_USER

Enter Agent Admin HANA User Password: (*****)
⇒[Password which you changed]
Enter Agent Admin HANA User Password: (*****) (confirm)
⇒[Password which you changed]
Enter Use HTTP Proxy Server[false]: Valid options: true|false
⇒false

Enter Agent XS HANA User Name[SDI_DP_AGENT]:
⇒SDI_DP_AGENT

Enter Agent XS HANA User Password: (*****)
⇒[Password which you changed]
Enter Agent XS HANA User Password: (*****) (confirm)
⇒[Password which you changed]

【6. Agent Registation】

At first, you register a HANA adapter in「DPAgent Configuration Tool」.

Select a 「7.Agent Registration」option in 「DPAgent Configuration Tool」.
Next, select a 「1.Register Agent」option in 「Agent Registration」.

Register an Agent following the next steps.
Enter Agent Name[dpagent_dev]:
⇒dpagent_prod
After a moment, agent registration process is finished.

【7. HANA Adapter Registation】

You register HANA adapter to connect DPAgent to HANA DB on Neo environment in「DPAgent Configuration Tool」.

Select a 「8.Adapter Registration」option in 「DPAgent Configuration Tool」
Next, select a 「1.Display Adapters」option in 「Adapter Registration」.

Confirm registered adapters. You can see 「No」 at HanaAdapter.

Select a 「2.Register Adapters」option in「Adapter Registration」.

Select a 「1. Display Adapters」and Confirm registered adapters again. You can see 「Yes」at HanaAdapter.

【8. Connection to HANA】

You start up「SAP HANA Data Provisioning Agent Configuration」tool and connect to HANA on Neo environment with the tool.

Push a 「Connect to HANA」button on 「SAP HANA Data Provisioning Agent Configuration」window.

Input connection settings for「Enter HANA server connection information」.
HANA Hostname ：[HANA host which you created]
HANA port ：443
HANA Agent Admin User ：SDI_USER
HANA Agent Admin Password ：[Password which you changed]

Push a 「Connect」button in the bottom of the window.

【9. Remote Sources Creation】

You establish HANA Service connection settings as Cloud Foundry environment on Remote Source which is in Neo environment.
After that, you successfully refer to HANA DB on Cloud Foundry environment.

At first, open a 「SAP HANA Service Dashboard」on Cloud Foundry environment and also check「Endpoints」.
You can see 「Endpoints」 in the bottom of 「Details」section.
ex)
Direct SQL Connectivity：[HANA host]:[HANA port]

Select HANA schema with HANA Studio. Open「Provisioning」and select「Remote Sources」.
Create a remote source by right click「New Remote Source」.

Input 「Endpoints」which you checked, SSL and your account to 「Configrations」window.
Configurations
-Database
Host* ：[HANA host which you created]
Port Number* ：[HANA port which you created]
JDBC Connection Properties ：encrypt=true;validateCertificate=true
-Connections
Enable SSL encryption ：true
Credentials
Credentials Mode* ：Technical user
-Credential
User(Case Sensitive) ：[HANA user name]
User(Case Sensitive) ：[HANA password]
-Credential
User(Case Sensitive) ：[HANA user name]

Intput「Source Name」and Seve.

【10.Virtual Tables Creation】

You select Remote Souce that you established and create virtual table on HANA DB on Neo environment from Cloud Foundry environment.

Select「Provisioning」->「Remote Sources」.Open Remote Source.
you can see the table of HANA DB on Cloud Foundry.

Open HANA schema and select table. Create virtual table by right click 「Add as Virtual Table」.

Register virtual table name on 「Create Virtual Table」window. Select a HANA schema to make the virtual table and push a「Create」button.

Confirm the virtual table.

【11. Virtual Procedure Creation】

You can call something procedures on Cloud Foundry from neo if you define the virtual procedure.

I will show you DDL below.

CREATE VIRTUAL PROCEDURE "[Your Schema]"."[Your Table]"(OUT param_0 TABLE (__DP_RETURN_CODE__ TINYINT)) CONFIGURATION '{

  "__DP_UNIQUE_NAME__": "\"[Your Schema]\".\"VIBRATION_FFT\"",

  "__DP_HAS_NESTED_PARAMETERS__": false,

  "__DP_USER_DEFINED_PROPERTIES__": {},

  "__DP_INPUT_PARAMETER_PROPERTIES_": [],

  "__DP_RETURN_PARAMETER_PROPERTIES_": [],

  "__DP_VIRTUAL_PROCEDURE__": true,

  "__DP_HAS_INTERNAL_OUTPUT_PARMETER__": true,

  "__DP_DEFAULT_OUTPUT_PARAMETER_INDEX__": 0

}' AT "[Your Virtual Procedure]"

After define your virtual procedure, you can call it this SQL statement.

CALL "[Your Schema]"."[Your Virtual Procedure]"(NULL);

【Summary】

To sum up, you can see HANA tables on Cloud Foundry environment to use SDI.

You understand Virtual Table creation and Virtual Procedure creation.

It is a useful way for SAP developer/engineer who considers exchanging data between Neo and Cloud Foundry environments.

That's All