Manage ETL Processes Via Telegram

Earlier, we developed a process of creating a SOAP web service for interacting with SAP Business Warehouse (BW) ETL processes (aka process chains). Web services could really help integrate functionality across the systems in your company’s landscape. Moreover, we can benefit from integration between web services and some front end tools, let’s say, Telegram messenger.

Telegram Botfather Logo

Telegram is a simple and free messaging app. You can use Telegram on all your devices at the same time — your messages sync seamlessly across any number of your phones, tablets, or computers. Also, the Telegram platform has a rich Bot API which could be used to cover many of your business needs.

This article will show how to monitor ETL processes in your data warehouse system simply by sending commands to your bot via Telegram messenger and receiving back information about current ETL status.

Let’s look at the checklist of components and tasks needed for our goal:

• Software for integration with Telegram Bot API and Web Service. We will use Python programming language for this with some third-party libraries.
• Server or virtual machine for run Docker container. Actually, we could run our Python software on a local machine for testing purposes. Nevertheless, we will run our software in a Docker container for production use cases. In this article, the example is running on the SAP DI platform, but it could be any other platform — AWS, Azure, GCP, etc.
• SOAP web service to get statuses of ETL processes. We made one for getting statuses from the SAP BW system in the previous article.
• The connection between containerization platform and web service. It is mandatory to establish a connection between systems. This task is not covered by this article, please ask network administrators in your company to do these settings. In my case, it was needed to create a new VPN connection between the company’s network and cloud provider on the top of which your bot software will be running.

The diagram shows the whole picture of how software parts work together:

Software components and integration diagram

At first, you must register a new Telegram Bot account. Go to the BotFather bot via Telegram messenger through the link https://t.me/botfather. Use command /new bot to create your new bot. Choose the name and username for your bot. Username must end with ‘bot’. After that, you will get a token that will be used for accessing Telegram API from your Python program. Keep this token in secret, because it is used to control your bot account:

Chat with BotFather bot

Now you should prepare a Python environment and install additional libraries for calling web services and working with Telegram API. I prefer the pip package manager for this. This command will help you install additional libraries for Python locally:

pip -m install lxml bs4 python-telegram-bot requests

Following is an example of a Python script you could use for interaction with the SOAP web service we have created in the previous article. I knew the correct message body format for my service from SoapUI, so I just put it in the body variable:

You have to change a few parameters at the beginning:

• token — your Telegram token from BotFather;
• bot_usr — list of Telegram user IDs which could send commands to bot;
• bw_endpoint, bw_user, bw_pass — your SOAP service credentials.

Please, fill out these variables and start your program. If you use Python IDLE you should see something like this:

Python 3.8.6 Shell in IDLE

Try to send /start a message to the bot in the Telegram app. If you see this response from the bot then your web service cannot be reached by the program (maybe you need to establish a VPN connection, as mentioned before):

WebService Unavailable

If you did all in the right way then the answer will be look like this:

SAP BW process chain manipulation bot
=================
Please, use one of the following commands:
/start returns this message
/status returns current chains status

Try to use another command /status to get information about the last process chain execution in the SAP BW system. The answer should be like this:

Process Chain ZCHAIN_TEST
Last Run: Status G, Start Date/Time: 20201125 160656

Wow! You just did it. You made a Telegram bot for monitoring ETL processes in the company’s data warehouse. There is a lot of things you could improve here. E.g., it is better to save user credentials not in code but in special key-storage from your cloud service provider, here is an example with AWS Secrets Manager. Another thing to improve is to load the SOAP envelope format from service first and send requests in this format (here is an example with the zeep library). Nevertheless, this code is a good start point from where you could begin your journey to the world of Python and web services.

The next step is for running your bot software in Docker on top of the SAP Data Intelligence (DI) platform, although it will be pretty much the same on different cloud computing platforms. Do not forget to set up a VPN connection for your cloud account too, if needed.

Before you can run your Python software you need to create a docker image. Open the SAP DI modeler and click on the Repository tab on the left side of the window. Now call the context menu with a right mouse click and choose “Create Docker File”. Enter the name:

Creation of new Docker image in SAP DI

Navigate to your new folder in the hierarchy and select “Dockerfile” in it. These are the lines you need to put in it. Additional information about docker file content is provided at the official Docker documentation web page here https://docs.docker.com/engine/reference/builder/.

FROM $com.sap.sles.base
RUN python3 -m pip install --no-cache-dir --user lxml bs4 python-telegram-bot requests

Add some tags to it. Tags system is used for easily defining of proper docker image for your new operators in the SAP DI modeler. I have entered ‘sapdibot’. Save (1) your docker file and build (2) the image:

Contents of Dockerfile

Go to the “Operators” tab and create a new one based on the provided Python3 Operator. Select correct tags at the “Tags” tab in BW operator preferences. You have to create new because standard linked to another docker image which does not have required libraries:

New Python3 BW Operator

As we planned only the run of new bot software you just need to put the new operator in the new Graph. Use the “Graphs” tab to create your own. It is a good approach to clear your operator’s script from any sensitive information like usernames, endpoints, and so on. You could put this information in a Graph’s element and change script (1) as you want here with effect only on this Graph.

In addition, you could start processes in SAP DI itself using a variety of internal tools. Anyway, our goal was to create a Telegram bot which will call SAP BW web service for consuming and manipulating data in that system.

Save your Graph (2) and run (3). Now your bot is alive and working in the cloud:

Running Graph on SAP DI platform

Sit back and relax. You have just earned new professional skills and can interact with your data warehouse through chat messages!

Telegram iOS App with Bot chat