Skip to Content

INTRODUCTION:

This document is regarding a solution for a specific requirement (mentioned below) that I had when working on a SAP HANA project for a client.

REQUIREMENT:

I had a requirement to export a complete schema (SCHEMA X) from one system (Development  – BD2) and import to another schema (SCHEMA Y) (Quality system – BQ2) so as to make all tables available in target schema as available in source.

SCHEMA X in target system has all the catalog objects as in source system.

Therefore when SCHEMA X is exported from source and imported into target system, HANA system raises an error that the catalog objects (in my case – tables) already exist in target system (as the exported file contains references to SCHEMA X and not to SCHEMA Y).

PROCESS:

To achieve the above requirement, just exporting and importing catalog object will not work as the source schema name is different from the target schema.

Also there is SCHEMA MAPPING option from HANA Modeler perspective which is suitable for modeling views but the method I used is a common approach for moving the catalog objects from one schema to another.

Source schema: ARJUN (Development)

Target schema: SRIVATSAN (Quality)

All the tables available in ARJUN schema of DEV system has to be exported and imported to SRIVATSAN schema of Quality system.

Both the schemas are available in Quality system.

ARJUN schema in quality system already has all the tables as development system so there is a requirement to rename all the occurrences of ARJUN to SRIVATSAN in the table creation code after the EXPORT.

Step 1: EXPORT the source schema (ARJUN)


  • Right click the schema and choose EXPORT

/wp-content/uploads/2014/11/1_580720.jpg

/wp-content/uploads/2014/11/2_580721.jpg

  • Click Next.

/wp-content/uploads/2014/11/3_580722.jpg


  • Choose Type as BINARY.
  • Provide an empty folder path for the export.
  • Click Finish.


/wp-content/uploads/2014/11/4_580723.jpg

Step 2 : Renaming the exported schema (from ARJUN to SRIVATSAN).


  • Open NOTEPAD++ (Advanced text editor that I used for renaming)
  • Press Ctrl + F
  • Go to Find in Files
  • Enter the text to be found and text to be replaced with.
  • Choose the Directory in which the exported file is present.

/wp-content/uploads/2014/11/5_580724.jpg


  • Click Find All.
  • All occurrences of the search text are displayed.


/wp-content/uploads/2014/11/6_580725.jpg


  • Again press Ctrl + F , open  “Find in Files” tab.


/wp-content/uploads/2014/11/7_580726.jpg

  • Click “Replace in Files”.


/wp-content/uploads/2014/11/8_580730.jpg

  • Click OK.
  • Now, all the table creation statements of the exported schema will have SRIVATSAN instead of ARJUN.


/wp-content/uploads/2014/11/9_580731.jpg


  • Also it is necessary to RENAME the folder present inside the index folder to the name of the SCHEMA to which the file has to be imported.


/wp-content/uploads/2014/11/10_580732.jpg

Now, the exported binary file is ready to be imported to the target schema (SRIVATSAN) of target system (Quality).

Step 3 : Import the catalog object.


  • Go to HANA studio.
  • Click FILE -> IMPORT.


/wp-content/uploads/2014/11/11_580738.jpg

  • Choose “Catalog objects” under SAP HANA.


/wp-content/uploads/2014/11/12_580739.jpg


  • Choose the target system (Quality).


/wp-content/uploads/2014/11/13_580740.jpg


  • Click NEXT.
  • Choose the Location of the exported file.


/wp-content/uploads/2014/11/14_580741.jpg

  • Click Next.
  • Choose the required tables that are to be imported to target system.


/wp-content/uploads/2014/11/15_580749.jpg


  • Click Next.
  • Choose the required options for DATA and THREADS.


/wp-content/uploads/2014/11/16_580744.jpg

  • Click Finish.

Now, the tables that are required are imported to SRIVATSAN schema.

Conclusion:

This approach is a general method to  copy tables from one system to other (different schemas). This might be achieved even through SCHEMA MAPPING which I haven’t tried practically.


Please share your views and comments.

To report this post you need to login first.

5 Comments

You must be Logged on to comment or reply to a post.

  1. Lars Breddemann

    Hi there,

    you’re right, there exists an automatic option to rename the schema upon import (IMPORT – SAP HANA SQL and System Views Reference – SAP Library) – it’s just not available in the GUI.

    Another aspect to this whole activity (schema copy) is that it is not really suited as a transport mechanism. Important things like the the privileges granted on the objects are not copied.

    If it is really just about the table definitions (your IMPORT screen shot shows that the import data check box is not ticked), then it’s usually preferable to have the tables defined in a development project (repository) and use the SAP HANA transport mechanism to get them into a different system.

    Besides all that: I like the effort that went into this blog post. Thanks for sharing!

    – Lars

    (1) 
  2. Sreehari V Pillai

    nice insight Lars Breddemann .

    I would suggest, exporting the schema and other catalog objects and import it to the target system is not the best transport mechanism . Most of all the objects can be created in the repository and we can easily transport them other than export & import . I doubt how the import will happen, if the table definition refer some other schema objects(like create table “oneschema”.”tabname” (select * from “otherschema”.”table”)).

    Sree

    (0) 

Leave a Reply