How to automate routine jobs of HANA DB and output of the automated job to Excel sheet

Schedule HANA Query using HDBSQL and HDBUSERSTORE.

We can connect to SAP HANA System using  HANA client HDBSQL.

HDBSQL is a command line tool for executing SQL commands on SAP HANA databases. 

We can use HDBSQL to automate mundane tasks and write scripts to run them at certain frequency. 

Requirement: We want to schedule a script at HANA Client/server to run a SQL weekly and send the output via email in excel file format.

Prerequisite: We need SAP HANA client installed on the source system. We also need an SAP HANA database user to connect and HANA system connection information.
We need OS level access from where we want to schedule the script .
Scenario : In our case, we want to schedule HANA SQL using HDBSQL and send the email to receiverEmailAddress@yourcompanyDomain.com

Target System : saphanadev.yourcompany.domain.com
Target Instance Number  : 00
Target HANA user : SYSTEM 
Password : In123
Source system(HANA client)  : saphanaqas(can be any Linux system with HANA client)

Steps:

• Create a secure store entry and use that ID/User to connect to HANA System.

• Create the shell script files that we will schedule using crontab, this script will use the HDB Secure store connection to execute our SQL file.

• Schedule the script using crontab.

We will create a HANA DB Secure store in HANA client and then will use that secure store to make HANA SQL query.

1. Execute following commands in order to create a Secure store entry :

hdbuserstore SET DEV "saphanadev.yourcompany.domain.com:30015" SYSTEM In123

This will create a secure store entry with unique name DEV.

2.Create a SQL file that will have the SQL that you want to schedule. In our case the file name is "HANA_SELECT_BACKUP_STATUS.sql"

3.Write a shell script file with following content.

hdbsql -U DEV -I /fullPath/HANA_SELECT_BACKUP_STATUS.sql -o /fullPath/HANA_SELECT_BACKUP_STATUS_OUTPUT.csv;

echo "Output of HANA_SELECT_BACKUP_STATUS.sql from HANA"|mailx -a /fullPath/HANA_SELECT_BACKUP_STATUS_OUTPUT.csv -r senderEmailAddress@yourcompanyDomain -s "HANA Backup Script Output from  $HOSTNAME" receiverEmailAddress@yourcompanyDomain.com; 

3. Schedule the shell script using the crontab functionality.

0 0 * * 7 /fullPath/YourScriptName.sh

Please note : You need to give correct permission to the owner for all the files so that it can call the script and make require changes.