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

[former_member203825]

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

 

References --> 

1785797 - SAP HANA Database Data Export