Overview
SAP has various functionalities which enable features that can be leveraged into the various usecases. SAP Datasphere or SAP Data Warehouse Cloud is a comprehensive data service which enables the creation of graphical and SQL views on top of the HANA entities provided by the SAP HANA System. When there is usecase of integrating SAP Datasphere with any UI-oriented applications, then there comes an idea to create APIs on top of views using Python and exposing them to the UI-based applications like 3D Unity software.
The Prerequisites
- BTP Subaccount
- SAP Datasphere service
- Pycharm or any Python IDE
Developing Python Application
1.Creating Python Project- For this Pycharm is used. PyCharm is an integrated development environment (IDE) used for programming in Python. To create an app for HANA views consumption, it will include the following required files-
a. Manifest.yml- This is a YAML based schema definition that contains meta information about environment variables, directories and other configurational details. In Command, application name will be given.
Python code:
applications:
- name: App_name
memory: 1024M
command: python File_name.py
manifest.yml
b. Runtime.txt – It specifies Python runtime version on which the current application is running
c. Requirements.txt- This file specifies all the modules, packages, and libraries getting imported while developing the application.
Python code:
pandas
Flask
hdbcli
requests
numpy
Office365-REST-Python-Client
openpyxl
pandas_read_xml
Requirements.txt
d. cf.exe- It allows to run the python application on cloud foundry.
2. Establishing BTP Connection with Python-
- Import the dbapi module: It lets Python connect to the database server
- Use the connect method: Using the connection method returns an instance of the connection class. In this method, all the details like user, address, password, and port to be mentioned to establish a connection with Python.
Python code:
def DWC_conn():
conn = dbapi.connect(
address=' ',
port='443',
user='',
password='',
encrypt=True,
sslValidateCertificate=False,
TLS=True)
return conn
BTP Connection with Python
3.Reading the view from SAP Datasphere- After importing connection method from the connection file, it includes the Sql query to read data from the Hana view deployed in the SAP Dataphere.
Python code:
def Read_Table(view_name):
cursor = conn.cursor()
sql_command = "Select * from Space_Name."+ view_name
cursor.execute(sql_command)
Reading View from Datasphere
4.Project_Name.py file- It specifies the app routing stands for mapping the URLs to a specific function that will handle the logic for that URL. To enable the different ways of using requests, there is need to import Flask Library and establish routes for the functions reading different HANA views.
Python code:
@app.route('/api_name, methods=["GET"])
def dwc_revenue_api():
df_auth_details, column_headers = Read_Table("View_Name")
df_auth_details = pd.DataFrame(df_auth_details, columns=column_headers)
result = df_auth_details.to_json(orient="index")
return result
Routing using Flask
Deployment of Python Application in cloud foundry
Open the command prompt and make sure that the path of the directory matches the path of application. Cf.exe and manifest.yml files should be in same folder. It contains 2 steps-
Step 1: Cloud Foundry Login- In command prompt, cf login command should be executed which requires all the login details like username, password and the BTP instance space name.
Step 2. API Creation- In command prompt, ‘
cf push’ command is to be executed which deploys the app to cloud foundry. Flask library used in python code is responsible for creating the API. This command will start the application and in few minutes, it will display the started status of the application. After that, one more command
‘cf run-task App_name --command "python File_name.py" --name taskfromcli’ will be executed which will run the application and in SAP BTP cockpit, API link will be present. In the end of link, the defined routes keyword in app file will be added as suffix after slash.