Skip to Content
Technical Articles
Author's profile photo Volker Saggau

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%20default_access_rights

Compare default_access_rights

 

Rights after the change:

Runtime%20user%20alters%20a%20HDI%20table

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:

  1. Create a dedicated “ALTERUSER”
  2. Grant the “alter” rights to the “ALTERUSER”
  3. 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%20SQL%20console%20%28ADMIN%29

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.

 

 

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Dinu PAVITHRAN
      Dinu PAVITHRAN

      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.

      { 
        "role":{ 
          "name":"alter_access_role", 
          "schema_privileges":[ 
            { 
              "privileges":[ "ALTER" ] 
            } 
          ] 
        } 
      }
      Author's profile photo Volker Saggau
      Volker Saggau
      Blog Post Author

      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.

      Author's profile photo SAGAR SUDALAKUNTA VORSA
      SAGAR SUDALAKUNTA VORSA

      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

      Author's profile photo Volker Saggau
      Volker Saggau
      Blog Post Author

      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

      Author's profile photo SAGAR SUDALAKUNTA VORSA
      SAGAR SUDALAKUNTA VORSA

      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

      Author's profile photo Volker Saggau
      Volker Saggau
      Blog Post Author

      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

      Author's profile photo SAGAR SUDALAKUNTA VORSA
      SAGAR SUDALAKUNTA VORSA

      Thanks Volker,

      I shall tryout this solution.

       

      regards

      sagar

      Author's profile photo Volker Saggau
      Volker Saggau
      Blog Post Author

      Hi Sagar,

       

      here a sample:

      == version = 3
      
      column table "GX_EMPLOYEES" (
      
      "EMPLOYEE_ID"NVARCHAR(50) NOT NULL,
      
      "EMPLOYEE_FIRSTNAME"NVARCHAR(50),
      
      "EMPLOYEE_LASTNAME"NVARCHAR(50),
      
      "EMPLOYEE_ACCOUNT_NO"NVARCHAR(50),
      
      "EMPLOYEE_SALARY"DECIMAL(8,2),
      
      "EMPLOYEE_START_YEAR"INTEGER NOT NULL DEFAULT 1900,
      
      "EMPLOYEE_GENDER"NVARCHAR(1),
      
      "EMPLOYEE_REGION"NVARCHAR(50),
      
      "EMPLOYEE_ZIPCODE"NVARCHAR(50),
      
      "EMPLOYEE_T-LEVEL"NVARCHAR(50),
      
      "EMPLOYEE_EDUCATION"NVARCHAR(50),
      
      primarykey ("EMPLOYEE_ID")
      
      
      
      
      )
      
      PARTITIONby
      
      HASH ("EMPLOYEE_ID") PARTITIONS 1 SUBPARTITION BY
      
      RANGE("EMPLOYEE_START_YEAR")
      
      (
      
      (PARTITION0<=values<2008
      
      page loadable GROUP NAME 'HISTORY'),
      
      (PARTITION others)
      
      )
      
      == migration = 3
      
      altertable"GX_EMPLOYEES"
      
      PARTITIONby
      
      HASH ("EMPLOYEE_ID") PARTITIONS 1 SUBPARTITION BY
      
      RANGE("EMPLOYEE_START_YEAR")
      
      (
      
      (PARTITION0<=values<2008
      
      page loadable GROUP NAME 'HISTORY'),
      
      (PARTITION others)
      
      )

      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