Hana Injector

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 micro service architecture. Therefore, one of the key features of the application is the low consumption of resources, as the framework allows for the efficient distribution and usage of resources in general.

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

Preconditons

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.

Functional principles

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 to the HANA DB SQL format and can then be inserted to the HANA DB.

Version Informations

The application is currently stable and the actual status is a stable (Version 1.0.1).

Version	Status	Change Description
0.9.1	Beta
1.0.1	Stable

Installation

Configuration File

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.

Python Files

Configuration Code Options

Python File Variable/ Method	Example	Meaning
myMQTT
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
mySQL
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
myConverter
write_mqtt_to_hana()	write_mqtt_to_hana(mosq, obj, msg)	Define the application switch and the following methods

Requirements

The application was developed for Python 3.5.3 and may also run under higher Python versions.

Establishment

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 chown user:user Path/Hana_Injector .jar & sudo chmod 755 Path/Hana_Injector.jar)
Optional by Linux and MAC OS X Systems: Copy, implement and customize the Service Script.
1. Download and copy the Script from here.
2. Place the Script inside your /etc/init.d directory.
3. Set the correct rights to your Script (sudo chmod 755 /etc/init.d/hana-injector).
4. Set the correct owner to your Script (sudo chown root:root /etc/init.d/hana-injector).
5. Customize the path inside the Script (sudo nano /etc/init.d/hana-injector).
6. Activate the Script and implement it your system start process with (sudo systemctl enable hana-injector).
Start the Service Script (sudo service 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.

Download

Donations

If you want you can support my work. The corresponding PayPal link will follow shortly.

License Agreement

You can find the license agreement here.

Support

Please, contact me if you find a bug or you need help!