Confluent Platform: Connecting SAP HANA to Kafka

5 min readAug 24, 2020

Introduction

In today’s world we deal a lot with real-time streaming data and events that come from a bunch of different sources and from which we can derive stats, facts and information that are crucial for today’s businesses.

Processing and storing these real-time streams in a scalable and fault-tolerant manner is now possible using Kafka as an Event Streaming Platform and KsqlDB as an Event Streaming Database. These technologies have made Event Stream Processing Applications thrive.

I have planned a series of articles that explain and demonstrate how to connect the following data sources using Kafka Connector to the Kafka Cluster :

ServiceNow Source Connector
Splunk Source Connector
SAP Source Connector
PostgreSQL along with Camunda

In this article, i will be focuing on setting up SAP Source Connector using a containerized SAP and Confluent Platform.

Having Kafka linked to SAP will allow us to store real-time SAP streams in a Kafka topic and to process them using the event streaming database KsqlDB.

The principles behind Kafka and KsqlDB have been explained in my previous articles:

In this demonstration, I will be using :

Docker in order to run the Kafka cluster.
Confluent Platform in order to have the necessary commands.

Requirements

First, you need to have docker installed. To install Docker, follow these steps for Ubuntu:

Then, you need to install Confluent Platform. It is a fully managed Kafka service and enterprise stream processing platform that allows building Real-time data streaming applications.

tar -xzf confluent-5.5.1-2.12.tar.gz

After that, add the path of the confluent as follows :

sudo su
nano /etc/profile

Add the following

export CONFLUENT_HOME=/home/ubuntu/confluent-5.5.1
export PATH=$PATH:$CONFLUENT_HOME/bin

source /etc/profile

The Confluent Hub Client is integrated within the Confluent Platform.It allows you to install the different connectors to connect KsqlDB to your data sources.

confluent-hub

Here are the connectors that you need to install for this series of demo. However, for this demonstration, we will install the SAP Kafka Source Connector manually. For the installation, select the second option : 2./(installed rpm/deb package ) . The connectors will be installed in /usr/share/confluent-hub-components .

sudo su# To Connect Camunda's Postgres Database
confluent-hub install confluentinc/kafka-connect-jdbc:5.5.0# To Connect Splunk
confluent-hub install confluentinc/kafka-connect-splunk-source:1.0.2
confluent-hub install splunk/kafka-connect-splunk:1.2.0# To Connect ServiceNow
confluent-hub install confluentinc/kafka-connect-servicenow:2.0.1# To Connect Json and CSV Files
confluent-hub install jcustenborder/kafka-connect-spooldir:latest# To Connect via APIs
confluent-hub install confluentinc/kafka-connect-http:1.0.16
confluent-hub install castorm/kafka-connect-http:0.7.6

SAP Kafka Source Connector setup

First, you need to get Java JRE installed.

sudo apt-get -y install openjdk-8-jre-headless unzipsudo apt install openjdk-8-jdk-headlesssudo apt-get updatesudo apt-get upgrade

Then, go to SAP Development Tools web site and download the following :

hanaclient-latest-linux-x64.tar.gz

sudo tar -xzf hanaclient-latest-linux-x64.tar.gz

Then run the following:

cd ~/client./hdbinstjava -jar ~/sap/hdbclient/ngdbc.jar -v

You will have all the files extracted to ~/sap/hdbclient/ . In this file you will find ngdbc.jar .

Next, you need to setup the Kafka Source Connector for SAP.

# the confluent-hub-components directory should contain all your kafka connectors that you have installed previously using confluent-hub command.cd /usr/share/confluent-hub-components/# Install SAP Source Connectorsudo git clone https://github.com/SAP/kafka-connect-sap.gitcd kafka-connect-sapsudo apt install mavensudo mvn clean install -DskipTestssudo cp -r ~/sap/hdbclient/ngdbc.jar /usr/share/confluent-hub-components/kafka-connect-sap/target

Confluent Platform along with SAP using Docker

First, create the /opt/docker-compose directory and put the following docker-compose.yml File.

sudo mkdir /opt/docker-compose
sudo nano /opt/docker-compose/docker-compose.yml

Ensure having a Docker Hub account and to run docker login to enter your credentials before running docker-compose up -d to be able to pull the SAP HANA image.
Ensure creating mkdir data/hana/ inside /opt/docker-compose to persist SAP HANA data outside of the container. This directory will be mounted using ./data/hana:/hana/mounts/ in the volume part of the hana container inside docker-compose.yml as follows:

volumes:- /data/hana:/hana/mounts

Ensure according the right permissions to data/hanaas follows:

sudo chmod a+rwx data/hana/

The installed connectors will also be mounted in the connect container inside docker-compose.yml as follows :

volumes:- /usr/share/confluent-hub-components/:/usr/share/confluent-hub-components

Demonstration

First, run docker-compose:

docker-compose up -d

Then run the following command to check SAP Hana’s logs

docker logs hana -f

Once done, access the hana container to confirm that the SAP installation has been successful

docker-compose exec hana bash

Then, inside the hana containerrun the following:

whoami

You should be logged in as hxeadm, the default SAP HANA, express edition user.

You can also enter the following:

HDB info

hdbnameserver
hdbcompileserver
hdbdiserver
hdbwebdispatcher

Before using the connector, you can verify that you can access to your SAP database as follows:

Method 1 : Inside the hanacontainer

Inside the hana container, You can log into your tenant database with the following command:

hdbsql -i 90 -d HXE -u SYSTEM -p HXEHana1

Then , run the following to check the available tables:

\dt

Run the following to display the content of the DUMMYtable

SELECT * FROM SYS.DUMMY;

Method 2 : Outside the hana container

cd ~/sap/hdbclient/java -jar ~/sap/hdbclient/ngdbc.jar -v

To connect to our SAP, we use the following command

java -jar ngdbc.jar -u <user,password>[-n <hostname:port>][-i <instance_number>][-d <database_name>][-o <connect option>][-c <sql command>]java -jar ngdbc.jar -u SYSTEM,HXEHana1 -n 127.0.0.1:39041 -d HXE -c "SELECT  * FROM SYS.DUMMY"

Once you have confirmed that you can successfully access to your SAP, run the SAP Kafka Connector as follows :

Method 1

Access your confluent control-center :

http://localhost:9021

2. In the connect part, click on upload connector then upload the following connector

You can check connector’s logs as follows

curl -s -X GET http://127.0.0.1:8083/connectors/sap/status

You should have you connector up and running. Then, you can verify that data have been successfully imported to the topic test_topic_1 .

3. Consume your imported data from the test_topic_1 topic as follows

docker-compose exec broker  \
  kafka-console-consumer --bootstrap-server broker:29092 --topic test_topic_1 --from-beginning

Method 2

docker-compose exec ksqldb-cli ksql http://ksqldb-server:8088

Then, create the connector

CREATE SOURCE CONNECTOR sap WITH ('connector.class' = 'com.sap.kafka.connect.source.hana.HANASourceConnector',
'tasks.max' = '1',
'connection.password' = 'HXEHana1',
'topics' = 'test_topic_1',
'connection.user' = 'SYSTEM',
'connection.url' = 'jdbc:sap://hana:39041/',                  'test_topic_1.table.name' = '"SYS"."DUMMY"');

Finally, you can verify if things have worked correctly

show connectors;
describe connector sap;
show topics;
print 'test_topic_1' from beginning;

You can run the following commands to stop and clean docker

docker stop $(docker ps -a -q) &&docker rm $(docker ps -a -q)