Technical Articles
Partition a table in HANA Deployment Infrastructure(HDI)
HDI is the environment that helps you to create tables and other database objects in an automated way. Specific HDI helps to change tables without the customer to care writing “alter” or other statements. But there are cases where you want to do something with the table that is related to its physical storage. The default rights might not match your needs so here is an option.
Careful: The system does not know what you altered.
- Each new instance of HDI may or may not need the alter statements (QA, PROD)
- Export/Import may not “survive” these changes and you have to reapply them
- to be used with hdbmigrationtable
This is a deep change and you should be aware that this is not the standard but some additional functionality with the customer being fully responsible on the results!
The SAP Business Technology Platform creates a HDI-service for you. That are the following schemas:
- <myHDIservice> -> schema that contains the runtime db objects
- <myHDIservice>#DI -> schema that contains metainformation and the sources
- <myHDIservice>#OO -> schema for the ObjectOwner
- <123456789>_DT -> schema for each DesignTime user in a binding
- <123456789>_RT -> schema for each RunTime user in a binding
The rights of RT-user are defined in the default_access_role.
GRANT_CONTAINER_SCHEMA_PRIVILEGES – SAP Help Portal
https://www.npmjs.com/package/@sap/hdi-deploy#the-default_access_role-role
The use of the HDI API in SQL is here: The Default Access Role for HDI Containers – SAP Help Portal
Option 1: Add alter to RT user
Customer can override the default role with his own definition:
In the sample the option “ALTER” was added to the role. Customer must be very sure what to change with this right.
In the src section of DB module
Create a folder “defaults”
In the defaults folder create a file “default_access_role.hdbrole”
The default of the role is currently defined as:
{ "role": { "name": "default_access_role", "schema_privileges": [ { "privileges": [ "DELETE", "CREATE TEMPORARY TABLE“, "EXECUTE", "INSERT", "SELECT", "SELECT CDS METADATA", "UPDATE" ] } ] } }
Adding the “, ALTER” to the list of right will allow the RunTime-User to alter tables.
Compare default_access_rights
Rights after the change:
Runtime user alters a HDI table
The “RT user” can now successfully alter the table with a given partition.
Option 2 – Dedicated “alter” user (suggested approach)
The better approach is the usage of a dedicated “ALTERUSER”:
Steps:
- Create a dedicated “ALTERUSER”
- Grant the “alter” rights to the “ALTERUSER”
- Let the ALTERUSER alter the table
Create the alter-user as DBAMIN:
--DROP USER ALTERUSER; CREATE USER ALTERUSER PASSWORD "Manager123456" NO FORCE_FIRST_PASSWORD_CHANGE VALID FROM NOW UNTIL FOREVER -- never do this in production! SET USERGROUP DEFAULT;
The DT-user grants the rights to the ALTERUSER
Sample here with a HDI demonstration case:
Project name: RIGHTS_IN_HDI
HDI Container name: RIGHTS_IN_HDI_HDI_DB_1
Alter role name: alter_access_role
Alter user name: ALTERUSER
Open from within the SAP Business Application Studio the DatabaseExplorer
In the context menu of the HDI-Container choose “SQL(ADMIN) Console”
Open SQL console (ADMIN)
Use below statement to grant the alter_access_role to the ALTERUSER
SET SCHEMA RIGHTS_IN_HDI_HDI_DB_1#DI; CREATE LOCAL TEMPORARY COLUMN TABLE #ROLES LIKE _SYS_DI.TT_SCHEMA_ROLES; INSERT INTO #ROLES ( ROLE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME ) VALUES ( 'alter_access_role', '', 'ALTERUSER' ); CALL GRANT_CONTAINER_SCHEMA_ROLES(#ROLES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?); DROP TABLE #ROLES;
Example of event.hdbmigrationtable
==version=1 column table Events( eventid BIGINT not null GENERATED ALWAYS AS IDENTITY, eventdate datetime not null, eventname nvarchar(25), eventstype int default 0 )
With the ALTERUSER
SET SCHEMA RIGHTS_IN_HDI_HDI_DB_1; SELECT top 10 * from EVENTS; -- just to prove we are right here ALTER TABLE EVENTS PARTITION BY RANGE (year(EVENTDATE)) (PARTITION 2020 <= values < 2025 , -- looking ahead PARTITION OTHERS page loadable ) -- want NSE usage ; SELECT * FROM M_TABLE_PARTITIONS WHERE TABLE_NAME = 'EVENTS' ;
Summary:
HDI does an automated change of tables each time you change the table design-time definition. With *.hdbmigrationtable you have the possibility to also change physical parameters as partitions in the runtime definition on that particular database without the risk to get overwritten by the next version of the design-time object. However this should be tested thoroughly.
Also please make sure that only dedicated users have the right to alter runtime objects. So you make sure that changes do not happen accidental.
Option 2 requires that the container has a role by name alter_access_role. This may be obvious. But, It is not stated in the blog.
This can be done by deploying a file named alter_access_role.hdbrole with contents as below.
Thanks Dinu for your comment. You are right with your finding. Maybe your approach is a bit tight with this rights, but this up to your needs.
One could also use the default_access_role.hdbrole and rename or copy it to the alter_access_role.hdbrole.
Hello Volker,
Table partitioning in general (more or less) is the same procedure as above(by running sql) for repository tables , In this blog by saying "Partition a table in HANA Deployment Infrastructure(HDI)" are you trying to explain the way the access is managed to perform a table partition on .hdbtable or .hdbmigrationtable in a container?
I have a follow up question, it would be easier to ask if above question is answered:)
regards
Sagar
Hi Sagar,
we added partitioning to the .hdbmigrationtable plug-in. Also there is a new role in HANA Cloud that is called PARTITION ADMIN.
Using .hdbmigrationtable is a stable way to achieve consistent change management with partitions.
Not sure what you mean with repository tables.
BR
Volker
Hello Volker,
Bit of context, I am speaking in terms of HANA on-prem (HANA 2.0 59.09)
By repository tables I mean tables which are created using HANA studio.
We use HDI way of development for some projects (we are on the way to migrate all developments) , in HDI way of development we use .hdbtable by default for all table artifacts , so my follow up question was linked to this if I partition the table(.hdbtable) using SQL : ALTER statement(via DB explorer) , if I re-deploy my container having this .hdbtable will the partition sustain or we have to redo partition again ?
regards
sagar
Hi Sagar,
.hdbtable: with a new version of the table you will create a new table.
With the deploy parameter "try_fast_migration=true" and no change in the partition you should get away from a table copy.
.hdbmigrationtable: supports now also alter table partition... a way you might get a smother change. For sure if your subsequent changes are not changing the partition.
BR. Volker
Thanks Volker,
I shall tryout this solution.
regards
sagar
Hi Sagar,
here a sample:
Hope this works for you. This also uses NSE. From a business point of view this does not make sense. But it shows the concept.
BR
Volker