SAP HANA to Azure data lake connection

[anikesh_jyotishi2]

Introduction:

This blog provides a step-by-step guide to establishing a connection between SAP HANA and Azure Data Lake. Follow the outlined steps to seamlessly integrate these platforms for efficient data management and analysis.

DISCLAIMER

The content of this blog post is provided “AS IS”. This information could contain technical inaccuracies, typographical errors, and out-of-date information. This document may be updated or changed without notice at any time. Use of the information is therefore at your own risk. In no event shall SAP be liable for special, indirect, incidental, or consequential damages resulting from or related to the use of this document.

Overview:- 

Basic Azure data lake DB connection architecture with Hana.

Preparation

• Check with the functional team and increase compute based on future growth
• Check odbc LINUX Driver compatibility:- in my case its red hat enterprise Linux 7 (Check OS  version from os-release )
         

         Microsoft link for compatibility check:- System Requirements (ODBC Driver for SQL Server) - ODBC Driver for SQL Server | Microsoft Docs

• Always use the latest ODBC connector for Azure Data Lake. If the operating system is not compatible with the latest version, raise a case with Azure to confirm the appropriate ODBC version.
• Check Installed old installed ODBS drivers
• Required root access for setup
  
  

     Installation

1.  Download files from OS
2.  Update yum update
3.  take config backup and remove old unixODBC to avoid conflicts
4.  install MS SQL (Azure data lake) driver and MySQL tools
5.  install unixODBC-utf16-devel this step is optional but recommended (Based on MS documentation)
6. Create symlinks for tools

Note:- Use the below link and check your OS commands

http://learn.microsoft.com/en-gb/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for...

Choose the appropriate OS for all Azure instructions related to ODBC installation.

Download files from OS

Download the package to configure the Microsoft repo

Update yum update

Update OS yup tool by below command

sudo yum update

 

Take config backup and remove old unixODBC to avoid conflicts:-

Take a backup of all odbc config files you can find all locations by the below command.

Run the below command to uninstall the old driver version

sudo yum remove unixODBC* (If you use * it will uninstall all old versions)

Install MS SQL driver and MySQL tools:-

Install MS SQL Linux driver and ms tool by the below command

sudo ACCEPT_EULA=Y yum install -y mssql-tools18

Check environment

after installation check binaries in the below locations

 

 Install unixODBC this step is optional but recommended:-

Use below command for unixODBC install

Create symlinks for tools :-

Create a soft link for MS SQL Linux tools, So that it can be available from everywhere.

Check installed driver:-

Configuration

• Update sidadm environment
• Check target DB host and port is accessible for Hana Server
• Create ODBC env file  and update target MS db details in odbc driver file
• Create SDA in HANA DB and Test SDA connection
• BW source system creation
• Check consistency and execute
  
  

Update sidadm environment :-

Update sidadm LD_LIBRARY_PATH by MySQL driver location, Created .customer.sh file in sidadm home location, and update the below details.

Hana DB default home path is /usr/sap/SID/home

Check target Azure Data lake and port is accessible for Hana Server:-

Azure data lake should be static and delicate SQL instance

Public IP with 1433 port

Ensure the SAP HANA Linux server’s public IP is whitelisted in the Azure SQL Database firewall.

• Go to Azure Portal → SQL Server → Networking → Add client IP or configure a firewall rule for the HANA server's IP.
• If using a private endpoint, ensure the HANA server is in the same VNet or peered network.

Create ODBC environment variable file:- 

under Hana sidadm home path create the .odbc.ini file and update target db details.

Check below the MS link for odbc config file syntax.

Connect to SAP HANA via ODBC | SAP Help Portal

.odbc.ini file location :- /usr/sap/SID/home

[Nama for Hana SDA Data source name]

SSL mode no/option include if your Linux OS is not trusted with Azure data lake and SSL cert
 

Create SDA in HANA DB and Test SDA connection:-

Login into Hana tenant db by the system user, Go to Provisioning --> Remote source right click and create new Remote source

use same .odbc.ini header name for Data Source Name, provide target Azure data lake db user id and password.

Expand Remote Source and check configured targeted Azure data lake details

Expend DBO user and right click on the table you would like to access and select add as Virtual Table and give schema name.

use virtual table and create view based on your requirement.
 

BW source system creation: -

Create a logical name for SDA remote DB and go to RSA1 --> SAP Hana Smart data access

Right-click and create a new connection and select the above SDA connection.

Check consistency and execute 

Right-click on the new data source and check the connectivity

Reference Documents: – 

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/system-requirements?view=sql-server-ver1...

 

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for...

https://help.sap.com/docs/SAP_BPC_VERSION_BW4HANA/dd104a87ab9249968e6279e61378ff66/47e03114c6744b248...

https://help.sap.com/docs/SAP_HANA_PLATFORM/0eec0d68141541d1b07893a39944924e/66a4169b84b2466892e1af9...

1868209 – SAP HANA Smart Data Access: Central Note
 
I hope this document will help you to understand the remote Azure data lake connection via SDA.

Cheers!

Anikesh Jyotishi