Technical Articles
How to export and import table from HANA DB
HANA DB TABLE EXPORT AND IMPORT:
EXPORT:
1.Create a folder inside work directory(Not inside trace directory or any other location which is not accessible by all nodes in case of a distributed environment)
In above I have created a folder called dbtab.
2.Execute the below command to export
4=>Here 4 represents the number of simultaneous threads that we want HANA to use to run our import . This can be increased as well
3.Once the above command has run succesfully, we can see folders inside the folder we created like index/*/*
This ends the EXPORT process.
TRANSFER TO THE TARGET:
If you want this specific table to be imported into target, we need to transfer the folder dbtab inside work folder of the target system . It can be done using scp or WinSCP.
scp -r dbtab dcxadm@servername:/usr/sap/SID/HDBnn/work
IMPORT PROCESS:
Prechecks:
1.If the table that we are going to replace belongs to SLT, that target replication has to be suspended. Any other activity that is going to hold a exclusive locks should be stopped to prevent lock time out
2..Before we import any table, we need to check if that table is present in the target HANA DB. If yes, we need to rename the existing table before importing the new one. Though this is not mandatory if we are going to use the additional clause “WITH REPLACE” in IMPORT command, it is always better to have backups before renaming or replacing any db related objects .
Command to rename :
rename table SAPDBR.Targettablename> to SAPDBR.Targettablename>_OLD;
Steps:
Once we have placed the target table inside work directory , we can proceed with table import.
To Import this table back in overwrite mode execute the below command.
IMPORT “SAPERP”.”DBTABLOG” AS BINARY FROM ‘/usr/sap/<SID>/HDB<nn>/work/dbtab’ WITH REPLACE THREADS 4;
4=>Here 4 represents the number of simultaneous threads that we want HANA to use to run our import . This can be increased as well.
NOTE:
IMPORT/EXPORT of tables process are considered as jobs in HANA DB and we can check the progress of the same in JOB PROGRESS tab in HANA Studio.
Thanks for reading!
Like and leave a comment if it was useful!
Nice blog.
We can also export import using the HANA Studio Catalog folder (without using command line).
One question, why you are using WITH REPLACE command while exporting the table from one database to a file system? While importing the table 'WITH REPLACE' command is understood.
Hi Sakti, As you said WITH REPLACE is not a mandatory clause and i used it in EXPORT as i was running EXPORT of same table for the second time in the same folder . As you said, WITH REPLACE in EXPORT is not required for newly created folder .
Nice Blog
Looking for syntax to export a bil record table into multiple flat files and import the same back into a different Hana DB.
Nice blog!
Question: is it possible to use the export sentence in an sqlscript using a variable instead of a conttant for the the destination file? I tried different ways, but could not make it work.
Thank you!
Patricia