SAP HANA Deployment Infrastructure (HDI) SQL API

[HalilGünal]

Introduction

In this post, I'm going to show you how to use the SQL API for the SAP HANA Deployment Infrastructure (HDI).

First of all we will setup our environment. We will create a new tenant and enable the diserver on this tenant. If you have already a tenant and the diserver is already enabled you can jump directly to the HDI Setup.

The next step will be to setup the hdi components to be able to work with a HDI container with the SQL API.

You can find all the information below also in the official documentation SAP HANA Deployment Infrastructure (HDI) Reference. This blog is just an overview and step by step guide to setup a simple HDI.

Environment setup

Creating new tenant

As I want to provide you a short end-to-end introduction I will first create a new database tenant and call it HIG. Connect to the SYSTEMDB with a user that has the DATABASE ADMIN privilege. Im using the SYSTEM user and execute the following statement for creating a new tenant with the diserver.

CREATE DATABASE HIG ADD 'diserver' SYSTEM USER PASSWORD Wwx8G8U3tK9nntJS;

Enabling diserver

If you have an existing tenant and want to use there HDI you can check if diserver is enabled there.

SELECT * FROM SYS_DATABASES.M_SERVICES WHERE SERVICE_NAME = 'diserver' AND DATABASE_NAME = 'HIG' AND ACTIVE_STATUS = 'YES';

In case the diserver is not enabled you can use following statement to enable the diserver for the tenant.

ALTER DATABASE HIG ADD 'diserver';

Now you are able to use HDI in your tenant.

HDI Setup

After the tenant is created and diserver is enabled we need to setup the HDI components in the tenant database.

As the image 1.) HDI Components illustrates, we need following components:

• HDI Administrator


• HDI Container Group


• HDI Container Group Administrator


• HDI Container


• HDI Container Administrator

 

1.) HDI Components
© SAP HANA Deployment Infrastructure (HDI) Reference

Please login into the tenant database to setup the HDI components.

HDI Administrator

is responsible for the setup and overall maintenance of HDI.

--create a user

CREATE USER HDI_ADMIN PASSWORD LF52EHVDwesTYhRx NO FORCE_FIRST_PASSWORD_CHANGE;

-- create a temporary table which contains privileges to assign to the user HDI_ADMIN 

CREATE LOCAL TEMPORARY TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;

-- fill the temporary table with the corresponding user and with the values from the table T_DEFAULT_DI_ADMIN_PRIVILEGES

INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'HDI_ADMIN', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_DI_ADMIN_PRIVILEGES;

-- call the procedure to assign the HDI admin privileges to the user HDI_ADMIN 

CALL _SYS_DI.GRANT_CONTAINER_GROUP_API_PRIVILEGES('_SYS_DI', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

-- remove the temporary table

DROP TABLE #PRIVILEGES;

After calling the procedure _SYS_DI.GRANT_CONTAINER_GROUP_API_PRIVILEGES you will get two result tabs. In the first result tab you will see the messages with information about the execution of the HDI call and a summary. You should see the following message if granting was successfully.

Granting API privileges in the container group "_SYS_DI" and the parameters "[]"... ok

In the second tab a result overview about the HDI call. The first column contains the return code the second column the request id and the third column the message. In our case you should have "0" in Out(1). The table 2.) SQL API Return Codes for HDI describes the return codes.

After almost every procedure execution you will get similar results. Please check the summary in the first result tab for an “ok” and in the second result tab for a “0” as return code.

Return Code	Meaning	Details
0	Success	The call was successful, and the messages did not report any warnings or errors
1	Warning	The messages contain warnings, but no errors.
-1	Error	The messages contain errors.
-2	Fatal Error	No messages could be logged. This indicates a problem with the database.

2.) SQL API Return Codes for HDI

For more information please refer to the official documentation.

HDI Container Group

is used for administrating a set of HDI containers. Only the HDI Admin can create a container group.

-- connect with the HDI Admin

CONNECT HDI_ADMIN PASSWORD LF52EHVDwesTYhRx;

-- call the corresponding procedure to create the container group

CALL _SYS_DI.CREATE_CONTAINER_GROUP('HDI_CG', _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

HDI Container Group Administrator

is responsible for managing the SAP HDI containers that are organized into one or more HDI container groups.

-- login with the SYSTEM user to create a new user

CONNECT SYSTEM PASSWORD Wwx8G8U3tK9nntJS;

-- create a user

CREATE USER HDI_CG_ADMIN PASSWORD XTNkqaKpM3D2gfFt NO FORCE_FIRST_PASSWORD_CHANGE;

-- login with HDI_ADMIN to grant the container group privileges to the HDI_CG_ADMIN user

CONNECT HDI_ADMIN PASSWORD LF52EHVDwesTYhRx;

-- create a temporary table which contains privileges to assign to the user HDI_CG_ADMIN 

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;

-- fill the temporary table with the corresponding user and with the values from the table T_DEFAULT_CONTAINER_GROUP_ADMIN_PRIVILEGES

INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'HDI_CG_ADMIN', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_CONTAINER_GROUP_ADMIN_PRIVILEGES;

-- call the procedure to assign the container group admin privileges to the user HDI_CG_ADMIN 

CALL _SYS_DI.GRANT_CONTAINER_GROUP_API_PRIVILEGES('HDI_CG', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

-- remove the temporary table

DROP TABLE #PRIVILEGES;

HDI Container

contains the database development artifacts, both design-time and run-time. The HDI container group administrator can create a new container.

-- Connect with the container group admin

CONNECT HDI_CG_ADMIN PASSWORD XTNkqaKpM3D2gfFt;

-- call the corresponding procedure to create the container HDI_C in the container group HDI_CG

CALL _SYS_DI#HDI_CG.CREATE_CONTAINER('HDI_C', _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

HDI Container Administrator

has administrator access to an SAP HDI container. to grant the container administrator privileges a container group admin should call the procedure GRANT_CONTAINER_API_PRIVILEGES with corresponding parameters.

-- login with the SYSTEM user to create a new user

CONNECT SYSTEM PASSWORD Wwx8G8U3tK9nntJS;

-- create the HDI_C_ADMIN user 

CREATE USER HDI_C_ADMIN PASSWORD MvfNa7JfqRFP4U5a NO FORCE_FIRST_PASSWORD_CHANGE;

-- login with HDI_CG_ADMIN to grant the container admin privileges to the HDI_C_ADMIN user

CONNECT HDI_CG_ADMIN PASSWORD XTNkqaKpM3D2gfFt;

-- create a temporary table which contains privileges to assign to the user HDI_C_ADMIN 

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES; 

-- fill the temporary table with the corresponding user and with the values from the table T_DEFAULT_CONTAINER_ADMIN_PRIVILEGES

INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'HDI_C_ADMIN', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_CONTAINER_ADMIN_PRIVILEGES; 

-- call the procedure to assign the container administrator privileges to the user HDI_C_ADMIN 

CALL _SYS_DI#HDI_CG.GRANT_CONTAINER_API_PRIVILEGES('HDI_C', #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?); 

-- remove the temporary table

DROP TABLE #PRIVILEGES;

As final step before to be able to deploy the database artifacts we need to configure the plug-in libraries available by default in a SAP HDI container.

Configuring the default plug-in libraries

-- connect with the container admin user

CONNECT HDI_C_ADMIN PASSWORD MvfNa7JfqRFP4U5a;

-- call the corresponding procedure to configure the plug-in libraries

CALL HDI_C#DI.CONFIGURE_LIBRARIES(_SYS_DI.T_DEFAULT_LIBRARIES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

You can use the procedure HDI_C#DI.LIST_CONFIGURED_LIBRARIES to list all configured plug-in libraries.

CALL HDI_C#DI.LIST_CONFIGURED_LIBRARIES(_SYS_DI.T_NO_PARAMETERS, ?, ?, ?, ?)

Granting developer privileges to a user

The HDI container administrator is able to deploy the database artifacts which are then used to generate the database objects in the container. But the container administrator should only used for administrative tasks and not for development. To grant access to the HDI container content-development API – in other words to make a user as a container developer the procedure GRANT_CONTAINER_API_PRIVILEGES can be used. But this time we need to grant the privileges from the _SYS_DI.T_DEFAULT_CONTAINER_USER_PRIVILEGES table.

-- login with the SYSTEM user to create a new user

CONNECT SYSTEM PASSWORD Wwx8G8U3tK9nntJS;

-- create HDI_C_DEV user 

CREATE USER HDI_C_DEV PASSWORD Lgb8PgUzaqC7TZSC NO FORCE_FIRST_PASSWORD_CHANGE;

-- login with HDI_C_ADMIN as container admin

CONNECT HDI_C_ADMIN PASSWORD MvfNa7JfqRFP4U5a;

-- create a temporary table which contains privileges to assign to the user HDI_C_DEV

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_API_PRIVILEGES;

-- fill the temporary table with the corresponding user and with the values from the table T_DEFAULT_CONTAINER_USER_PRIVILEGES

INSERT INTO #PRIVILEGES (PRINCIPAL_NAME, PRIVILEGE_NAME, OBJECT_NAME) SELECT 'HDI_C_DEV', PRIVILEGE_NAME, OBJECT_NAME FROM _SYS_DI.T_DEFAULT_CONTAINER_USER_PRIVILEGES;

-- call the procedure to assign the container developer privileges to the user HDI_C_DEV

CALL HDI_C#DI.GRANT_CONTAINER_API_PRIVILEGES(#PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

-- remove the temporary table

DROP TABLE #PRIVILEGES;

 

Deploying design-time artifacts

After a developer user has been created we can use this user to write the design-time artifacts and deploy them as run-time objects.

WRITE

-- connect with the HDI container developer user HDI_C_DEV

CONNECT HDI_C_DEV PASSWORD Lgb8PgUzaqC7TZSC;

-- create a temporary table which contains the design-time objects definitions

CREATE LOCAL TEMPORARY COLUMN TABLE #PATHS LIKE _SYS_DI.TT_FILESFOLDERS_CONTENT;

-- list the plug-ins in .hdiconfig which are used in the project

INSERT INTO #PATHS (PATH, CONTENT) VALUES ('.hdiconfig', '{ "plugin_version" : "2.0.30.0", "file_suffixes" : { "hdbtable" : { "plugin_name" : "com.sap.hana.di.table" }, "hdbview" : { "plugin_name" : "com.sap.hana.di.view" } } }');

-- configure the namespace structure in .hdinamespace

INSERT INTO #PATHS (PATH, CONTENT) VALUES ('.hdinamespace', '{ "name": "", "subfolder": "ignore" }');

-- create the src folder

INSERT INTO #PATHS (PATH, CONTENT) VALUES ('src/', NULL);

-- create a file named t.hdbtable which contains a table definition

INSERT INTO #PATHS (PATH, CONTENT) VALUES ('src/t.hdbtable', 'COLUMN TABLE T ( A INTEGER )');

-- create a file named v.hdbview which contains a view definition

INSERT INTO #PATHS (PATH, CONTENT) VALUES ('src/v.hdbview', 'VIEW V AS SELECT A FROM T');

-- write the files or folders in the container's work file system

CALL HDI_C#DI.WRITE(#PATHS, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

-- remove the temporary table

DROP TABLE #PATHS;

After calling the procedure HDI_C#DI.WRITE you should see in the summary Writing 5 files to the container "HDI_C"... ok. Now the desing-time objects are created in the work file system but they are not deployed yet as run-time objects in the container. To do this so we will use the make procedure. To see which objects are in the work file system before creating the run-time objects the list procedure can be used.

LIST

-- connect with the HDI container developer user HDI_C_DEV

CONNECT HDI_C_DEV PASSWORD Lgb8PgUzaqC7TZSC;

-- create a temporary table which contains the corrospending parameters

CREATE LOCAL TEMPORARY COLUMN TABLE #PARAMETERS LIKE _SYS_DI.TT_PARAMETERS;

-- insert recursive, true to display the files also in the subfolders

INSERT INTO #PARAMETERS ( KEY, VALUE ) VALUES ('recursive', 'true');

-- call the procedure with the PARAMETERS 

CALL HDI_C#DI.LIST(_SYS_DI.T_NO_FILESFOLDERS, #PARAMETERS, ?, ?, ?, ?);

-- remove the temporary table

DROP TABLE #PARAMETERS; 

For more information please refer to the official documentation.

MAKE

As we can see from the result of the list procedure we we have created some desing-time objects. With the make procedure the run-time objects can be created.

-- connect with the HDI container developer user HDI_C_DEV

CONNECT HDI_C_DEV PASSWORD Lgb8PgUzaqC7TZSC;

-- create a temporary table which contains a list of desing-time objects 

CREATE LOCAL TEMPORARY COLUMN TABLE #DEPLOY_PATHS LIKE _SYS_DI.TT_FILESFOLDERS;

-- insert the path of the desing-time objects

INSERT INTO #DEPLOY_PATHS (PATH) VALUES ('.hdiconfig');

INSERT INTO #DEPLOY_PATHS (PATH) VALUES ('.hdinamespace');

INSERT INTO #DEPLOY_PATHS (PATH) VALUES ('src/t.hdbtable');

INSERT INTO #DEPLOY_PATHS (PATH) VALUES ('src/v.hdbview');

-- in case to undeploy objects

CREATE LOCAL TEMPORARY COLUMN TABLE #UNDEPLOY_PATHS LIKE _SYS_DI.TT_FILESFOLDERS;

-- in case to pass some parameters

CREATE LOCAL TEMPORARY COLUMN TABLE #PATH_PARAMETERS LIKE _SYS_DI.TT_FILESFOLDERS_PARAMETERS;

-- call the procedure to generate the run-time objects

CALL HDI_C#DI.MAKE(#DEPLOY_PATHS, #UNDEPLOY_PATHS, #PATH_PARAMETERS, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

-- remove the temporary tables

DROP TABLE #DEPLOY_PATHS; 

DROP TABLE #UNDEPLOY_PATHS; 

DROP TABLE #PATH_PARAMETERS; 

The message Make succeeded (0 warnings): 4 files deployed (effective 4), 0 files undeployed (effective 0), 0 dependent files redeployed in summary means the run-time objects are created.

To access the run-time objects you will need to grant some privileges to a consumer user because the container is fully isolated. The developer user HDI_C_DEV shouldn't used for consuming data from the container. So we will create a consumer user HDI_C_CONSUMER and assign the user the SELECT privilege on containers schema.

-- login with the SYSTEM user to create a new user

CONNECT SYSTEM PASSWORD Wwx8G8U3tK9nntJS;

-- create the HDI_C_CONSUMER user 

CREATE USER HDI_C_CONSUMER PASSWORD RU7AeXTFGdsRxz2q NO FORCE_FIRST_PASSWORD_CHANGE;

-- connect with the container admin user

CONNECT HDI_C_ADMIN PASSWORD MvfNa7JfqRFP4U5a;

-- create a temporary table which contains privileges to assign to the user HDI_C_CONSUMER

CREATE LOCAL TEMPORARY COLUMN TABLE #PRIVILEGES LIKE _SYS_DI.TT_SCHEMA_PRIVILEGES;

-- fill the temporary table with the corresponding user and privileges to assign

INSERT INTO #PRIVILEGES ( PRIVILEGE_NAME, PRINCIPAL_SCHEMA_NAME, PRINCIPAL_NAME ) VALUES ( 'SELECT', '', 'HDI_C_CONSUMER' );

-- call the procedure to assign the privileges to the user HDI_C_CONSUMER

CALL HDI_C#DI.GRANT_CONTAINER_SCHEMA_PRIVILEGES( #PRIVILEGES, _SYS_DI.T_NO_PARAMETERS, ?, ?, ?);

-- remove the temporary table

DROP TABLE #PRIVILEGES;

In the summary you should see Granting schema privileges in the container "HDI_C" and the parameters "[]"... ok. Now you can access the object from the containers schema with the consumer user.

To keep it simple i have granted the select privilege on the schema level to a user. You should check the roles to define more granular privileges and the procedure GRANT_CONTAINER_SCHEMA_ROLES to grant a role to a user.

-- connect with the consumer user

CONNECT HDI_C_CONSUMER PASSWORD RU7AeXTFGdsRxz2q;

-- access to the table T in the container HDI_C

SELECT * FROM HDI_C.T;

Finally we can access the table T in the container HDI_C.

SAP HANA Calculation View API

There are much more possibilities besides just creating some tables or normal views. You can use the SAP HANA Calculation View API to create SAP HANA calculation views programmatically. For more details you can also visit the blog SAP HANA Calculation View API – Introduction.

References

SAP HDI Container Schemas
SAP HANA Deployment Infrastructure
HDI Components
Create an SAP HDI Administrator
Grant SAP HDI Container-Group Administrator Privileges to Another User
Configure the Default Build Plug-in Libraries Available to an SAP HDI ContainerGrant Access to the SAP HDI Container Content-Development API