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

References –> 

1785797 – SAP HANA Database Data Export