The following application was created by a project with the purpose to connect MQTT parts with the SAP HANA DB.
The goal was to implement an application that is capable of transforming incoming MQTT topics to a SQL format that is compliant with the Hana DB, as the original data was streamed with MQTT, whereas the HANA database was only able to receive data sent in Hana SQL format.
Basic information about the application and key features
The Hana Injector is based on a python flask
Furthermore, the architecture includes multiple packages, such as the Python MQTT Paho client and the PyHDB libarie.
The features of this application are the conversion process in SQL, the element identification and the insertion into the HANA DB.
Architecture and functional principles of the Hana Injector
Before starting the application, you must ensure that both the MQTT server and the corresponding HANA DB server are running.
The current version does not have an interface that performs checks such as the above-mentioned prerequisites, as in a general operation both servers must be active.
In case these preconditions are not met, the application will throw multiple errors and will eventually crash.
At first, a JSON string is being sent via MQTT. This string is being directed into the Hana Injector, where the string is then being transformed into a Hand DB compatible SQL, which is predefined.
The transformation process identified the elements and puts these variables in the SQL statement. For this process, the statements were defined before the application starts. As soon as the transforming is done, the string is transformed
The application is currently stable and the actual status is a stable (Version 1.0.1).
This application haven’t the opportunity to control it about an configuration file. It is only possible to update and customize the application code. The customization about an configuration file comming soon in version 1.0.1.
Configuration Code Options
|Python File Variable/ Method||Example||Meaning|
|client.subscribe()||client.subscribe(“Customite/Topic”)||Define the subcribed Topics|
|client.username_pw_set()||client.username_pw_set(“Customize username”, “Customize password”)||Define the optional username and password for the authentication|
|client.message_callback_add()||client.message_callback_add(“Customize/Topic”,converter.write_mqtt_to_hana)||Define the selected Topic and the corresponding method|
|client.connect()||client.connect(“Customize IP or Hostname”, 1883, 60)||Define the Hostname or IP addres and the port|
|pyhdb.connect()||pyhdb.connect(host=”Customize IP or Hostname”, port=39041, user=”Customize username”, password=”Customize password”)||Define the Hostname or IP addres, port, username and the password|
|load_on_handa_db_production_order()||load_on_handa_db_production_order(order_id, order_date, customer_name)||Customize the selected elements and the SQL statement|
|load_on_handa_db_production_order_items()||load_on_handa_db_production_order_items(order_id, color_name, brick_quantity)||Customize the selected elements and the SQL statement|
|load_on_handa_db_production_status()||load_on_handa_db_production_status(order_id, device_id, date_time, status_code)||Customize the selected elements and the SQL statement|
|write_mqtt_to_hana()||write_mqtt_to_hana(mosq, obj, msg)||Define the application switch and the following methods|
- The application was developed for Python 3.5.3 and may also run under higher Python versions.
- Download the correct file for your Operating System.
- Unpack the file and copy the Jar and configuration folder & file to your local Operating System.
- Adjust your favorite user and set the correct rights at the Jar file (
sudo chownuser :userPath/Hana_Injector .jar & sudo chmod755 Path/Hana_Injector.jar)
- Optional by Linux and MAC OS X Systems: Copy, implement and customize the Service Script.
- Download and copy the Script from here.
- Place the Script inside your /etc/init.d directory.
- Set the correct rights to your Script (
sudo chmod755 /etc/init.d/ hana-injector).
- Set the correct owner to your Script (sudo chown root:root /etc/init.d/hana-injector).
- Customize the path inside the Script (sudo nano /etc/init.d/hana-injector).
- Activate the Script and implement it your system start process with (
sudo systemctlenable hana-injector).
- Start the Service Script (
sudoservice hana-injector start) to run the application.
- Or start the MQTT_Kafka Jar file (/
usr/bin/java -jar Path/Hana_Injector.jar &) to run the application.
If you want you can support my work. The corresponding PayPal link will follow shortly.
You can find the license agreement here.
Please, contact me if you find a bug or you need help!