Connect SAP Hana Cloud Database Through Python

6 min readMay 7, 2022

In this blog, we will open SAP HANA Database in SAP HANA Database Explorer. We will create schema, table and insert records in table. After that we will use Python module to connect SAP HANA Cloud database and fetch data from SAP HANA Cloud Database.

In first part, we have created SAP BTP Cockpit trial account. If you don’t have an account in sap.com, you can follow below like:

How to Setup SAP BTP Trial Account and Create SAP HANA Cloud Database

Go to SAP BTP Cockpit Trail Account

After login to sap.com. Go to this url: https://cockpit.hanatrial.ondemand.com/trial/#/home/trial.

Then click “Go To Your Trial Account”.

Next screen will show like below. Then click on “trial”.

After clicking on “trial”, next screen will show like below. We need to click on “dev”.

In next screen, we need to click on “SAP HANA Cloud”.

In next screen -> Click on “Actions” -> “Open in SAP HANA Database Explorer”.

It will ask the “Choose your identity provider”, click on “Sign in with default identity provider”.

Next screen will show like below. Now SAP HANA Database instance opened in SAP HANA Database Explorer.

Create schema and table in database

Create Schema

Right click on “Schemas” -> “Open SQL Console”.

Create schema in SQL Console. In this example, i am creating schema called hotel. Then click on Run.

CREATE SCHEMA hotel;

Schema created successfully.

Create table

Create table called customer. Then click on Run.

CREATE COLUMN TABLE hotel.customer(
    cno NUMERIC(4) PRIMARY KEY,
    title CHAR(7),
    firstname CHAR(20),
    name CHAR(40) NOT NULL,
    zip CHAR(5),
    address CHAR(40) NOT NULL
);

We can see, table customer created successfully.

Insert records in customer table.

INSERT INTO hotel.customer VALUES(3000, 'Mrs', 'Jenny', 'Porter', '10580', '1340 N. Ash Street, #3');
INSERT INTO hotel.customer VALUES(3100, 'Mr', 'Peter', 'Brown', '48226', '1001 34th St., APT.3');
INSERT INTO hotel.customer VALUES(3200, 'Company', NULL, 'Datasoft', '90018', '486 Maple St.');
INSERT INTO hotel.customer VALUES(3300, 'Mrs', 'Rose', 'Brian', '75243', '500 Yellowstone Drive, #2');
INSERT INTO hotel.customer VALUES(3400, 'Mrs', 'Mary', 'Griffith', '20005', '3401 Elder Lane');
INSERT INTO hotel.customer VALUES(3500, 'Mr', 'Martin', 'Randolph', '60615', '340 MAIN STREET, #7');
INSERT INTO hotel.customer VALUES(3600, 'Mrs', 'Sally', 'Smith', '75243', '250 Curtis Street');
INSERT INTO hotel.customer VALUES(3700, 'Mr', 'Mike', 'Jackson', '45211', '133 BROADWAY APT. 1');
INSERT INTO hotel.customer VALUES(3800, 'Mrs', 'Rita', 'Doe', '97213', '2000 Humboldt St., #6');
INSERT INTO hotel.customer VALUES(3900, 'Mr', 'George', 'Howe', '75243', '111 B Parkway, #23');
INSERT INTO hotel.customer VALUES(4000, 'Mr', 'Frank', 'Miller', '95054', '27 5th St., 76');
INSERT INTO hotel.customer VALUES(4100, 'Mrs', 'Susan', 'Baker', '90018', '200 MAIN STREET, #94');
INSERT INTO hotel.customer VALUES(4200, 'Mr', 'Joseph', 'Peters', '92714', '700 S. Ash St., APT.12');
INSERT INTO hotel.customer VALUES(4300, 'Company', NULL, 'TOOLware', '20019', '410 Mariposa St., #10');

Record inserted in customer table.

If you want to see table and data, you select “Table”, which is showing on the left side -> Then select Table which you want to see. Here i am clicking on Customer -> then click on “Open Data”.

You can see raw data of customer table like below:

Now let us pull this customer data through python. For that we need to install SAP HANA(hana-ml) module.

Python Machine Learning Client for SAP HANA (hana-ml)

This package enables Python data scientists to access SAP HANA data and build various machine learning models using the data directly in SAP HANA. This page provides an overview of hana-ml.

hana-ml uses SAP HANA Python driver (hdbcli) to connect to and access SAP HANA.

Python machine learning client for SAP HANA consists of two main parts:

SAP HANA DataFrame, which provides a set of methods for accessing and querying data in SAP HANA without bringing the data to the client.

2. A set of machine learning APIs for developing machine learning models.

Specifically, machine learning APIs are composed of two packages:

PAL package

PAL package consists of a set of Python algorithms and functions which provide access to machine learning capabilities in SAP HANA Predictive Analysis Library(PAL). SAP HANA PAL functions cover a variety of machine learning algorithms for training a model and then the trained model is used for scoring.

APL package

Automated Predictive Library (APL) package exposes the data mining capabilities of the Automated Analytics engine in SAP HANA through a set of functions. These functions develop a predictive modeling process that analysts can use to answer simple questions on their customer datasets stored in SAP HANA.

Install hana-ml library

sudo -E pip install hana-ml

Connect SAP HANA Cloud Database with Python

Open Jupyter notebook and create a json file, in that write the SAP HANA Database details.

Create configuration of SAP HANA Cloud Database

Create a json file called hana_cloud_config.json in current directory and write database details in that. Then save and close the file.

{
  "user": "DBADMIN",
  "pwd": "xxxxxxxx",
  "url": "4467ff97-b72a-4367-9711-ae5a3068e1dc.hana.trial-us10.hanacloud.ondemand.com",
  "port": 443
}

Load the json file.

Create a new python file in jupyter notebook and read the json file.

import jsonsap_hana_config_file = "hana_cloud_config.json"with open(sap_hana_config_file) as f:
    sap_hana_config = json.load(f)
    db_url  = sap_hana_config['url']
    db_port = sap_hana_config['port']
    db_user = sap_hana_config['user']
    db_pwd  = sap_hana_config['pwd']

We can print and see the database details.

db_port, db_user, db_url, db_pwd

Connect SAP HANA Cloud with ConnectionContext

Import the ConnnectionContext module.

from hana_ml.dataframe import ConnectionContext

class hana_ml.dataframe.ConnectionContext(address=’’, port=0, user=’’, password=’’, autocommit=True, packetsize=None, userkey=None, **properties)

Bases: object

Represents a connection to an SAP HANA system.

ConnectionContext includes methods for creating DataFrames from data on SAP HANA. DataFrames are tied to a ConnectionContext, and are unusable once their ConnectionContext is closed.

cc = ConnectionContext(db_url, db_port, db_user, db_pwd)
cc

Output: <hana_ml.dataframe.ConnectionContext at 0x7f27355fc910>

Print the HANA version

print(cc.hana_version())

Output: 4.00.000.00.1649750720 (fa/CE2022.4)

Fetch data from SAP HANA

We have to give schema and table name. Here my schema is hotel and table is customer.

hana_ml_df = cc.table('CUSTOMER', schema='HOTEL')
hana_ml_df

Output: <hana_ml.dataframe.DataFrame at 0x7f27356304d0>

SAP HANA DataFrame object is created. We need to transform a SAP HANA DataFrame to a Pandas DataFrame. We can use collect() function for that.

Convert into Pandas dataframe

help(hana_ml_df.collect)

….

df = hana_ml_df.collect()df.head()

Output:

View the dataframe details

df.info()