HANA – Multiple(selected) tables binary data export / Import using script

HANA admin / developers frequently get tasks to export/import selective list of HANA tables data to disk for stage refresh,  backup, re-partitioning  etc.

Developed bash script code helps automate export or import activity for multiple selective tables.

Features

◉ Server level export /import execution instead from studio as client

◉ script can be run in background at HANA server and progress can be monitor

◉ easy configuration using hdbuserstore

◉ input list of tables and script will take care of export /import

Pre-requisite

◉ Create text file with list of table names i.e. schema_name.table_name format

◉ create hdbuserstore key

Execution

◉ ensure hdbuserstore key created as mentioned i.e. SIDexp

◉ table_list.txt file created 

◉ Export path exists and has sufficient space as per table data 

◉ execute as –>    sh <script_name.sh>   >  log.txt  &

◉ monitor log.txt for progress

Below are two different scripts for Export and Import.

Export related script 

#!/bin/bash

   while read line; do

      mtable="$line"

        /usr/sap/SID/HDB80/exe/hdbsql -U SIDexp "EXPORT $mtable AS BINARY into '/hanabackup/export_test/tables'"

        RC=$?

    if [ $RC -eq 0 ]

     then

     echo "\n $mtable export completed successfully $RC"

    fi

    if [ $RC -ne 0 ]

     then

     echo "\n $mtable export completed with error $RC"

    fi

   done < /hanabackup/export_test/table_list.txt

Import script

as of now script consider target schema /table structure is already exists and it will replace only data.

also considering meta data i.e. structure is same in source and target table.

#!/bin/bash

print_help ()

{

cat << !

Usage --> Script will import (binary exported using HANA tools) selected require tables to specified schema.

Paramters

USER KEY -  hdbuserstore key to connect DB

HANA SID - Datbase SID

MSOURCE - Source schema name

MTARGET - Target schema name

EXPORT_PATH - Exported file path

table_file - List of tables to import schema_name.table_name format in text file with path

Examples

   $0 --hdbkey=<<hdbuserstore KEY>> --sourceschema=<<MSOURCE>> 

     --targetschema=<<MTARGET>>  --exportpath=<<EXPORT_PATH>>  

     --hsid=<<Database SID>> 

     --tablefile=<<List of schema_name.table with path>>

!

}

for margu in "$@"

do

PARM=$(echo $margu | cut -f1 -d=)

VALUE=$(echo $margu | cut -f2 -d=)

case $PARM in

--hdbkey)

mhkey=`echo $VALUE | tr 'A-Z' 'a-z' ` ;;

--sourceschema)

msource=$VALUE ;;

--hsid)

mhsid=$VALUE ;;

--tablefile)

mtablefile=$VALUE ;;

--exportpath)

mexportpath=$VALUE ;;

--targetschema)

mtarget=$VALUE ;

;;

*)

echo "Invalid Argument $PARM" ;

print_help $0 ;

exit 2;;

esac

done

if [ -z "$mhsid" ];

 then

  echo "Please check syntax --hsid "

  print_help $0 ;

  exit;

fi

if [ -z "$mtablefile" ];

 then

  echo "Please check syntax --tablefile "

  print_help $0 ;

  exit;

fi

if [ -z "$mhkey" ];

 then

  echo "Please check syntax --hdbkey "

  print_help $0 ;

  exit;

fi

if [ -z  "$msource" ];

 then

  echo "Please check syntax for --sourceschema "

  print_help $0 ;

  exit;

fi

if [ -z "$mtarget"  ];

 then

  echo "Please check syntax for --targetschema "

  print_help $0 ;

  exit;

fi

if [ -z "$mexportpath"  ];

 then

  echo "Please check syntax for --exportpath "

  print_help $0 ;

  exit;

fi

mlogfile="`date \"+%Y%m%d_%H_%M_%S\"`"

mlogfile="$mtarget"_"$mlogfile"_"$iPrefix.log"

printf "\n Import - log file /tmp/$mlogfile using /usr/sap/$mhsid/HDB80/exe/hdbsql " >> /tmp/$mlogfile

printf "\n IMPORT <table_name> from '"$mexportpath"' WITH REPLACE DATA ONLY RENAME SCHEMA $msource TO $mtarget  THREADS 10 "  >> /tmp/$mlogfile

printf "\n --------------------------------------------------------------------------------------------------" >> /tmp/$mlogfile

printf "\n  " >> /tmp/$mlogfile

printf "\n  " >> /tmp/$mlogfile

#exit;

   while read line; do

      mtable="$line"

     /usr/sap/$mhsid/HDB80/exe/hdbsql -U $mhkey "IMPORT $mtable from '"$mexportpath"' WITH REPLACE DATA ONLY RENAME SCHEMA $msource TO $mtarget  THREADS 10"

     RC=$?

    if [ $RC -eq 0 ]

     then

     printf "\n $mtable import completed successfully from $msource TO $mtarget with error code $RC"  >> /tmp/$mlogfile

    fi

    if [ $RC -ne 0 ]

     then

     printf "\n $mtable import from $msource TO $mtarget completed with error $RC"  >> /tmp/$mlogfile

    fi

   done < $mtablefile

printf "\n eof " >> /tmp/$mlogfile