SAP HANA – Modeling Content Migration – part 1: Preparations, Security Requirements, Export
1 Introduction
There are many reasons why content migration is needed. Just to name few – you need it when transporting changes between development and production HANA database, you might use it when merging or splitting HANA databases or you can use it during change of database design.
In last two blogs I was showing example how HANA database can be designed in terms of data architecture and security concept:
SAP HANA – Security Concept and Data Architecture (part 1)
SAP HANA – Security Concept and Data Architecture (part 2)
In case that you like this concept and you would like to implement it upon existing content – then you need to use content migration to relocate objects without breaking dependencies between them.
2 Preparations
Before starting any activity you need to create detailed plan what you want to migrate and where. Be sure to document source and planned target location for each object in database.
Below you can find examples of rules you should follow when creating this plan:
- replicated tables can be migrated into new schema during preparation and validation but then you should re-provision whole schema again from source system to ensure functional replication
- information models (attribute views, analytic views and calculation views) should be migrated into one or more target packages based on project they belong to
Note: Manual adjustments for information models might be required in case that objects from one schema will be relocated into multiple target schemas.
Here you can see example of migration plan:
According to this plan you need to create and prepare target area (schemas and packages) where you intend to move current content. This can be done in same HANA database or in brand new HANA database. As a last step you will need to create other related objects like analytic privileges, security roles and users for modelers and applications.
Do not forget to prepare plan for external applications connecting to HANA database to understand how to deal with new location of objects (for example BO Data Services).
2.1 Required security authorizations
In order to successfully perform content migration you need following privileges.
2.1.1 Source: Export of data content
In order to successfully perform export you need to have read access (SELECT privilege) against the object (or its schema) and you need to have EXPORT system privilege.
2.1.2 Source: Export of information models
First you need EXECUTE privilege on stored procedure REPOSITORY_REST to be able to retrieve list of defined packages. Then you need REPO.READ privilege against the package from which you want to run export.
2.1.3 Target: Import of data content
For table import you need to have IMPORT system privilege and write access to target schema:
- for table creation (and optionally data load): CREATE ANY
- for table dropping and re-creation (and optionally data load): CREATE ANY, DROP
Note: It might look like error but you do not need any other privileges (no need to grant SELECT, INSERT or DELETE privilege).
In case that target schema does not exist and you wish to create is as part of import process you need to grant the user system privileges IMPORT and CREATE SCHEMA (no other privileges are required).
Note: From owner perspective it does not matter who started the import. Object owner is always user SYSTEM.
2.1.4 Target: Import of information models
Again main prerequisite is EXECUTE privilege on stored procedure REPOSITORY_REST to enable listing of existing packages. In order to be able to create models user needs to have REPO.READ and REPO.EDIT_NATIVE_OBJECTS privileges against target package.
Note: You need further privileges to be able to activate objects. (See my security blogs for more details.)
3 Migration of data content
3.1 Source: Export of data content
Migration of schema objects is relatively straightforward. Switch into perspective Information Modeler (Menu Window / Open Perspective / Information Modeler).
Choose Export option in Content section of the menu.
Select option Tables in SAP HANA Studio folder and click Next.
On next screen choose correct connection.
Select all tables that should be exported and add them to the list.
Select BINARY format for export and ensure that option “Catalog + Data” is selected. Click Finish.
Following directory structure will be created in selected location:
/usr/sap/HDB/HDB00/work
+— index
+— IDES
| +— ZC
| +— ZCOPAACTUAL2
| | +— attributes
| +— ZCOPAFORECAST2
| +— attributes
+— TECHED2011
+— KN
| +— KNA1
| +— attributes
+— MA
| +— MARA
| +— attributes
+— SC
| +— SCAL1
| +— attributes
+— T0
+— T001W
+— attributes
3.1.1 Source: Export of data content using SQL interface
In case that you need to export high amount of tables it is more comfortable to use SQL interface. You can use SQL command EXPORT with following syntax:
EXPORT “<schema1>”.”<table1>”[,”<schema2>”.”<table2>”,…] AS BINARY INTO ‘<target directory>’
[WITH REPLACE | WITH (REPLACE) CATALOG ONLY | WITH (REPLACE) DATA ONLY]
In our case we will use this command:
EXPORT “TECHED2011″.”KNA1″,”TECHED2011″.”MARA”,”TECHED2011″.”SCAL1″,”TECHED2011″.”T001W”, “IDES”.”ZCOPAACTUAL2″,”IDES”.”ZCOPAFORECAST2″ AS BINARY INTO ‘/usr/sap/HDB/HDB00/work’
3.2 Source: Export of information models
In Information Modeler perspective choose again option Export in section Content.
Select option Information Models in Information Modeler folder and click Next.
On next screen choose correct connection.
Select package you wish to export and click Add – all objects in package will be added to the list. Select target folder on your host. Click Finish.
Wait for all jobs to end successfully.
Following directory structure will be created in selected location:
C:\Data\HANA\export
+— HDB
+— techedcopa
+— analyticviews
+— attributeviews
+— calculationviews
Second part of this blog can be found here:
SAP HANA – Modeling Content Migration – part 2: Import, Post-migration Activities
great blog and keep up the good work in the forum.
thx,
greg
Thank you for your feedback.
Actually I was thinking if I should write another blog or not (I had plans to deep dive into SLT - but now I am not sure about that). So far there is minimal feedback and very very low amount of page views (and for those that are/were featured - half of the readers do not continue to the second part).
Maybe my blogs are too technical or maybe not usable my many SDN visitors. I am not sure what I am doing wrong and what I should change.
Tomas
you are not doing anything wrong in my book and the value of your blog entry will only increase over time. often, this type of content belongs to wikis rather than blogs, but if you add something personal like a client success story or how you have overcome a particular issue this will increase your traction.
picking up something controversial and voicing a strong opinion will increase the hits but not everybody is comfortable doing this and it may turn against you if overdone.
as HANA acquires more users they may come back to your blog far in the future, so it's up to you if you want to build value over time or need something quick.
again, you are already adding a lot of value by responding in detail to queries in the forum. others may have a different feeling about this, but i think most of SDN would agree.
you are not doing anything wrong in my book and the value of your blog entry will only increase over time. often, this type of content belongs to wikis rather than blogs, but if you add something personal like a client success story or how you have overcome a particular issue this will increase your traction.
picking up something controversial and voicing a strong opinion will increase the hits but not everybody is comfortable doing this and it may turn against you if overdone.
as HANA acquires more users they may come back to your blog far in the future, so it's up to you if you want to build value over time or need something quick.
again, you are already adding a lot of value by responding in detail to queries in the forum. others may have a different feeling about this, but i think most of SDN would agree.
Thanks for your kind words.
Just to explain - I am not going after views (so I am not going to write something radical just for the sake of having a lot of views). But views are key KPIs that are providing me important feedback (what is interesting and what not, etc).
Regarding the wiki - I was thinking if I should use the blog or wiki but then I decided for blog. With wiki I should be constantly returning back to review and update while blog is fixed in time when it was written and that's it. SAP is working extremely hard and bringing a lot of improvements and it would be very difficult to keep the same pace in updating everything (especially when addressing more and more of content).
For new blog - currently I am quite occupied but I am afraid that I will not be able to help myself anyway and I will write something - as long as at least someone finds it interesting it is worth doing it. 🙂
Thanks for support.
Tomas
Tomas Krojzl: Great Blog - Thanks.
Regards,
Rama
Hi Tomas,
Honest confession: I have been wandering all over for a simple scenario as stated below.
Request your help in the following scenario:
I have a schema "A" with 100 tables (created and with data). I want to have these 100 tables in schema "B". When I export schema A or individual tables from schema A, I am able to import the tables back only in schema A. How can I import these exported tables into schema B?
Thank you.
Regards,
KG
Hello,
it is covered in the blog (however the blog was broken during conversion to new SCN) - all you need to do is to rename schema name in table structure and adjust schema name inside create.sql files... I would suggest to use some utility to mass-adjust like sed on linux or ****.exe on windows...
however do not forget to adjust the models that are using this table as well otherwise they will become inconsistent... (I will try to fix the blog)
Tomas
I fixed the broken formatting...
Thanks Tomas! Appreciate that!
Regarding changing the SQL files, I doubt if I will be able to do the same, since the Export is done on the server directory. In case I am unable to, any other work around that you may suggest?
Tomas,
It worked and worked like a charm. Had a few initial glitches in terms of syntax of the command, since I ain't a console guy 🙂
Thanks a lot!
Regards,
KG