SAP HANA Health Checks from Python

Photo by Shotlist on Unsplash

I work a lot with the SAP HANA database, and I write hobby code in Python. This week I had the opportunity to bring the best of these 2 worlds together to create a simple Python app that helps me keep tabs on the health of our SAP HANA database.

If you have ever needed to run health checks on your SAP HANA database you will have come across SAP Note 1969700: Statement Collection for SAP HANA. It contains a set of individual SQL statements, prepared by SAP for analyzing SAP HANA databases. There are instructions in the note for running the scripts via an ABAP program, or you could copy and paste into the HANA SQL Console, but why do that when you can create a Dash app instead :-)

SAP Notes are bug correction instructions or short articles that elaborate on one or more aspects of SAP’s software

I’ve also been dabbling with connecting to SAP HANA from Python using the hana_ml library, which allows you to run HANA SQL and present the results back python in the form of a pandas dataframe. That was the inspiration: could I actually write an app to call any of the SQL statements provided by SAP in Note 1969700 and present the results in a python Dash app.

This is what I came up with:

A Python Browser of HANA Health Check SQL Scripts

The the results appear neatly in a Dash data table. You can even download the results to Excel.

To achieve this for yourself you will need to 1) implement the app described below 2) configure a connection to your SAP HANA database, 3) download the set of SQL scripts provided by SAP in Note 1969700.

I used:

• Dash — a framework for developing dashboards, written exclusively in Python
• The various styles from the Dash Bootstrap Components library
• hana_ml —the SAP machine learning Python library, which provides HANA connectivity and support for pandas dataframes

Caution:

Please be aware that you may need additional licensing, see here

This is how I did it:

This is a Dash app, so all the code goes into a single script. If you want to hit the code directly: https://github.com/ajax0/hana_health_python

You’ll need to install Dash, see here for that and more on this fantastic framework.

Step 1 — Import Libs

import dash
import dash_core_components as dcc
import dash_html_components as html
import dash_table
import dash_bootstrap_components as dbc
from dash.dependencies import Input, Output, State
from flask import Flask, send_from_directory
from hana_ml import dataframe
import configparser
import pandas as pd
import base64
from pathlib import Path

In the above code:

I import the relevant libraries needed for the app. Its mostly dash stuff, but you’ll find pandas and hana_ml there too. hana_ml is the SAP HANA Python Client API for machine learning algorithms.

Step 2— Connection Function

def make_conn ():
 config = configparser.ConfigParser()
 config.read(‘mdd_hbp.ini’)
 connection_context = dataframe.ConnectionContext(
 config[‘MDD_HBP’][‘url’],
 int(config[‘MDD_HBP’][‘port’]),
 config[‘MDD_HBP’][‘user’],
 config[‘MDD_HBP’][‘pwd’])
 return connection_context

In the above code:

We define a function to make a connection to our SAP HANA database. I am using the configparser library to hide my connection settings from plain sight in a file called ‘xxx_hbp.ini’:

.ini file for use with configparser

Step 3— Run SQL Script Function

def run_sql (sql_script):
 connection_context = make_conn()
 if connection_context:
 sqldataset = (connection_context.sql(sql_script)) 
 df = sqldataset.collect()
 connection_context.close()
 return df
 else:
 print(“Conn_error”)

In the above code:

1. We pass in the SQL script that is loaded from a file, see later.
2. We make a connection to the HANA database.
3. We then run the SQL query, and
4. Finally return the result in the form of a pandas dataframe.

Step 4 — Set the Upload Directory

UPLOAD_DIRECTORY = Path(“sql_scripts/”)

In the above code:

We set a constant to point to the directory that will hold the files containing the individual SAP HANA SQL scripts.

Step 5— Setup the Dash app

server = Flask(__name__)
app = dash.Dash(external_stylesheets=[dbc.themes.LUX])

In the above code:

We setup the flask server and initiate the Dash app, applying an external style sheet as we go.

Step 6— Create the App Layout:

app.layout = dbc.Container(fluid=True, children=[
 html.Div( [ html.H1(“HANA SQL Browser — Note 1969700”),
 html.Div( [ dbc.Row( [ dbc.Col(html.Div(dcc.Upload( id=”upload-data”, children=html.Div([“Click to select a file to upload.”] ), style={ “width”: “100%”, “height”: “30px”, “lineHeight”: “30px”, “borderWidth”: “1px”, “borderStyle”: “solid”, “borderRadius”: “5px”, “textAlign”: “center”, “margin”: “10px”, }, multiple=False, ), ), width={“size”: 4}, ),
 dbc.Col(html.Div(dbc.Button(“Run SQL”, id=’run_sql’, color=”success”, className=”mr-1"), ), width={“size”: 2}, ), dbc.Col(html.Div( [
 dbc.Button(“Toggle Notes”, color=”info”, id=”notes”, className=”mr-1"),
 dbc.Button(“Toggle Script”, color=”info”, id=”script”, className=”mr-1"),
 dbc.Button(“Toggle Output”, color=”info”, id=”output”, className=”mr-1"),
 ] ), width={“size”: 6}, ), ], ), ],), 
 
 dbc.Collapse( [ html.H3(“SQL Notes”), dcc.Textarea( id=’textarea-sqlnotes’, value=’<the sql notes section of the selected file will appear here>’, style={“margin”: “10px”, ‘width’: ‘80%’, ‘height’: 150, ‘font-size’: ‘10px’}, ), ], id=”notes-collapse”), 
 
 dbc.Collapse( [ html.H3(“SQL Script”), dcc.Textarea( id=’textarea-sqlscript’, value=’<the sql notes section of the selected file will appear here>’, style={“margin”: “10px”, width’: ‘80%’, ‘height’: 150, ‘font-size’: ‘10px’}, ), ], id=”script-collapse”),
 
 dbc.Collapse( [ html.H3(“SQL output”), dash_table.DataTable( id = ‘table’, 
 style_as_list_view=True, style_header={‘backgroundColor’: white’,’fontWeight’: bold’}, filter_action=”native”, sort_action=”native”, sort_mode=”multi”, page_action=”native”, page_current= 0, page_size= 10, export_format=’xlsx’, export_headers=’display’ ), ], id=”output-collapse”, style={“margin”: “10px”, ‘width’: ‘80%’}, ), ], )])

In the above code we set out the app layout:

1. A header
2. A file-upload widget: clicking here opens a file open dialog and allows you to select any of the SQL scripts downloaded from SAP Note: 1969700
3. A button to run the loaded SQL script
4. A button to toggle the SQL Notes textarea
5. A button to toggle the SQL Script textarea
6. A button to toggle the output Dash data table
7. A textarea to hold the SQL Notes
8. A textarea to hold the SQL SQL
9. An output Dash data table

Step 7 — Callbacks

@app.callback(Output(“notes-collapse”, “is_open”), Input(“notes”, “n_clicks”), [State(“notes-collapse”, “is_open”)],)
def toggle_notes(n_notes, is_open):
 if n_notes:
 return not is_open
 return is_open
@app.callback(Output(“script-collapse”, “is_open”), Input(“script”, “n_clicks”), [State(“script-collapse”, “is_open”)],)
def toggle_script(n_script, is_open):
 if n_script:
 return not is_open
 return is_open
@app.callback(Output(“output-collapse”, “is_open”), Input(“output”, “n_clicks”), [State(“output-collapse”, “is_open”)],)
def toggle_ouput(n_output, is_open):
 if n_output:
 return not is_open
 return is_open
@app.callback(Output(‘textarea-sqlnotes’, ‘value’), [Input(“upload-data”, “filename”), Input(“upload-data”, “contents”)],)
def update_output(uploaded_filename, uploaded_file_contents):
 “””Set TextArea valuefrom SQL Notes”””
 if uploaded_filename is not None and uploaded_file_contents is not None:
 path_file = UPLOAD_DIRECTORY / uploaded_filename
 file = open(path_file)
 file_contents = file.read().replace(‘]\n\n’, ‘]’)
 start = file_contents.find(‘/*’) + 5
 end = file_contents.find(‘*/’, start)
 value = file_contents[start:end]
 value = ‘File Name: ‘ + uploaded_filename + ‘\n\n’ + value
 else:
 value = “< no file selected >”
 return value
@app.callback(Output(‘textarea-sqlscript’, ‘value’), [Input(“upload-data”, “filename”), Input(“upload-data”, “contents”)],)
def update_output(uploaded_filename, uploaded_file_contents):
 “””Set TextArea valuefrom SQL Notes”””
 if uploaded_filename is not None and uploaded_file_contents is not None:
 path_file = UPLOAD_DIRECTORY / uploaded_filename
 file = open(path_file)
 file_contents = file.read()
 start = file_contents.find(‘/*’)
 end = file_contents.find(‘*/’, start) + 2
 value = file_contents[end:]
 value = ‘SELECT’ + value
 else:
 value = “< no file selected >”
 return value
 
@app.callback([Output(component_id=’table’, component_property=’data’), Output(component_id=’table’, component_property=’columns’)], [Input(“run_sql”, “n_clicks”), Input(“textarea-sqlscript”, “value”)])
def on_button_click(n, sql_txt):
 if n is not None:
 df = run_sql(sql_txt)
 if not df.empty:
 columns = [{‘name’: col, ‘id’: col} for col in df.columns]
 data = df.to_dict(orient=’records’)
 return data, columns
 else:
 return [{}], []
 else:
 return [{}], []

In the above code we setup all of the callback functions for each of the individual elements.

Step 7 — Instantiate the Dash app

if __name__ == “__main__”:
 app.run_server(debug=True, port=8051)

In the above code we trigger the app to run on port 8051.

Usage Notes

1. For some of the SQL Scripts you may have to edit the SQL Script to use the SAP<SID> Schema, see the SQL Notes section for more.
2. Check the SQL Notes section for each file for additional execution hints

I hope this post is useful, I certainly had fun creating it. Let me know if you encounter any challenges. And drop me a comment if you do use this productively.

BR, Andrew