hana_ml Python package. It became quite a popular post. But it does require running some code to get a file loaded.hana_ml functionality can be wrapped into some kind of UI and made available as an app on SAP Business Technology Platform for a user just upload any CSV file into a staging table in the SAP HANA Cloud database.Streamlit module that dmitrybuslov and andreas.forster wrote about in Low-code data analysis application with SAP HANA push-down. Streamlit promises to help rapidly develop applications in Python.cf command-line tool (CLI)If you are not familiar with deploying Python applications to SAP BTP, CloudFoundry environment, then please check Create a Python Application via Cloud Foundry Command Line Interface tutorial first.
I won't repeat steps from there, like how to logon to your SAP BTP account usingcfCLI. But I will cover extras we are going to work with / experiment with here.



"STAGING4UPLOADS"."STAGING" with corresponding structure created and data uploaded.
hana service:cf marketplace -e hana
hana service instance named hdb-staging-schema with the plan schema and database schema name to be created STAGING4UPLOADS:cf create-service hana schema hdb-staging-schema -c '{ "schema" : "STAGING4UPLOADS" }'
cf create-service-key hdb-staging-schema hdb-staging-schema-sk
cf service-key hdb-staging-schema hdb-staging-schema-sk
STAGING4UPLOADS.CONNECT STAGING4UPLOADS PASSWORD "Your_Long_Password_From_The_ServiceKey";
SELECT Current_User, Current_Schema FROM dummy;
mvp in the name: there is not much error-handling to make the code more readable).from io import StringIO
import streamlit as st
import hana_ml.dataframe as dataframe
from cfenv import AppEnv
import pandas as pd
st.set_page_config(
page_title="CSV-to-HDB",
page_icon="https://hana-cockpit.cfapps.us10.hana.ondemand.com/hcs/sap/hana/cloud/assets/images/sap_logo.svg",
layout="wide",
)
st.title('CSV-to-HDB')
env = AppEnv()
HANA_SERVICE = 'hdb-staging-schema'
hana = env.get_service(name=HANA_SERVICE)
# Instantiate connection object
conn = dataframe.ConnectionContext(address=hana.credentials['host'],
port=int(hana.credentials['port']),
user=hana.credentials['user'],
password=hana.credentials['password'],
encrypt='true',
sslTrustStore=hana.credentials['certificate'])
st.write(f'1. Connected to the schema {conn.get_current_schema()}')
df_data = None
some_file = st.file_uploader("2. Upload a file to be loaded into SAP HANA Cloud db", type={"csv", "txt"})
if some_file is not None:
# To read file as bytes:
bytes_data = some_file.getvalue()
#st.write(bytes_data)
# To convert to a string based IO:
stringio = StringIO(some_file.getvalue().decode("utf-8"))
#st.write(stringio)
# To read file as string:
string_data = stringio.read().splitlines()
st.write('File preview (up to 5 lines):', string_data[:5])
# Load CSV into pandas DataFrame
df_data = None
if some_file is not None:
df_data = pd.read_csv(some_file, sep=None).convert_dtypes()
st.write(f"Pandas dataframe size: {0.0 if df_data is None else round(df_data.memory_usage(deep=True).sum()/1024/1024, 2)} MB")
st.write('Dataframe (up to 5 rows):', df_data if df_data is None else df_data.head(5))
# Load data to SAP HANA
service_schema = hana.credentials['schema']
target_table = st.text_input('HANA table name: ', 'STAGING')
if st.button('3. Persist data into the HANA db') and df_data is not None and target_table!='':
df_remote = dataframe.create_dataframe_from_pandas(connection_context = conn,
pandas_df = df_data,
table_name = target_table,
schema = service_schema,
force = True,
disable_progressbar = True)
st.write(f'Successful creation of the table {target_table}: {conn.has_table(table=target_table, schema = service_schema)}')
sep=None in Pandas' read_csv() to automate the recognition of the separator in the file.cfenv
hana-ml
streamlitcfenv is a tiny utility that simplifies interactions with Cloud Foundry environment variableshana-ml is a Python machine learning client for SAP HANAstreamlit is a Streamlit’s open-source app framework to create web appspython-3.9.*.* which ensures that the latest available buildpck for Python 3.9 will be used.web: streamlit run loadfile_mvp.py --server.port ${PORT}${PORT} which ensures that streamlit app runs (via --server.port argument) on the default web port of the CloudFoundry app instead of the default Streamlit port 5000.---
applications:
- name: ((app-name))
routes:
- route: ((app-name))-((account-name)).cfapps.((btp-region)).hana.ondemand.com
memory: 2048M
buildpacks:
- python_buildpack
env:
STREAMLIT_SERVER_MAX_UPLOAD_SIZE: 100
services:
- hdb-staging-schemahdb-staging-schemaa service instance.STREAMLIT_SERVER_MAX_UPLOAD_SIZE to limit a size of a file to 100MB. Bigger file size would require a bigger memory size of the CloudFoundry app than a limit set to 2GB in the manifest.app-name, account name account-name (used to create unique URLs for your deployments), and a BTP region btp-region where the app is deployed.vars.yml described below.app-name: csv2hdb
account-name: e8ee8684trial
btp-region: us10cf push --vars-file vars.ymlPushing app csv2hdb to org e8ee8684trial / space dev as witalij... ...
Applying manifest file /.../sap-tech-bytes/scripts/manifest.yml...
Manifest applied
Packaging files to upload...
Uploading files...
1.89 KiB / 1.89 KiB [=========================================================================================================================================] 100.00% 1s
Waiting for API to complete processing files...
Staging app and tracing logs...
Downloading python_buildpack...
Downloaded python_buildpack
Cell d5f8ba8f-6b80-4d41-b335-1a257634845d creating container for instance e4c343c7-2922-43d4-a6a0-b7055e4ab8a7
Cell d5f8ba8f-6b80-4d41-b335-1a257634845d successfully created container for instance e4c343c7-2922-43d4-a6a0-b7055e4ab8a7
Downloading app package...
Downloaded app package (1.9K)
-----> Python Buildpack version 1.7.49
-----> Supplying Python
-----> Installing python 3.9.9
Copy [/tmp/buildpacks/49b223c631091864eee2a117ec43f025/dependencies/15b399819fce43a5b5eedb0316dbb3c1/python_3.9.9_linux_x64_cflinuxfs3_6a29c275.tgz]
Using python's pip module
...
-----> Running Pip Install
Collecting cfenv
Downloading cfenv-0.5.3-py2.py3-none-any.whl (4.5 kB)
Collecting hana-ml
Downloading hana_ml-2.13.22060800-py3-none-any.whl (5.1 MB)
Collecting streamlit
Downloading streamlit-1.10.0-py2.py3-none-any.whl (9.1 MB)
...
Exit status 0
Uploading droplet, build artifacts cache...
Uploading droplet...
Uploading build artifacts cache...
Uploaded build artifacts cache (127.7M)
Uploaded droplet (201.5M)
Uploading complete
...
Waiting for app csv2hdb to start...
Instances starting...
name: csv2hdb
requested state: started
isolation segment: trial
routes: csv2hdb-e8ee8684trial.cfapps.us10.hana.ondemand.com
last uploaded: Thu 09 Jun 12:55:08 CEST 2022
stack: cflinuxfs3
buildpacks:
...
name version detect output buildpack name
python_buildpack 1.7.49 python python
type: web
sidecars:
instances: 1/1
memory usage: 2048M
start command: streamlit run loadfile_mvp.py --server.port ${PORT}
state since cpu memory disk details
#0 running 2022-06-09T10:55:34Z 0.0% 106.1M of 2G 751M of 1Gcf app csv2hdb
cf delete -rf csv2hdb
cf delete-service-key -f hdb-staging-schema hdb-staging-schema-sk
cf delete-service -fw hdb-staging-schema-f flag!You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
| User | Count |
|---|---|
| 4246 | |
| 3357 | |
| 2603 | |
| 2153 | |
| 1983 | |
| 1255 | |
| 1164 | |
| 1122 | |
| 1100 |