Creating objects in the Hana DataBase

Blog series starts here

The first thing you’ll need is a user with the appropriate authorization in order to create your user & schema. As I wrote in a previous blog post, I’ve decided to create my own schema in order to create the “metrics mart”. To do so you need an admin account with the necessary authorizations to create that user. Later we’ll use this account to create the objects.

User creation

You have several options whether create it using HDB studio -> Connect to your HDB and go to “Security”-> “Users” and go through this to assign the necessary roles and privileges :

 

Or use SQL console to create this account. As I have several roles and privileges to assign and will implement the objects in several HDBs, I’ll use a script to do so.

The script looks like this, where you set the necessary roles etc … :

CREATE USER Z_MARKH PASSWORD Myfavpassword;
ALTER USER Z_MARKH DISABLE PASSWORD LIFETIME;

CALL PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::TrustStoreViewer','Z_MARKH');
CALL PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::SQLCCViewer','Z_MARKH');
CALL PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::SMTPDestViewer','Z_MARKH');
CALL PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::SAMLViewer','Z_MARKH');
CALL PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::RuntimeConfViewer','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::NetDestViewer','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::JobViewer','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::HTTPDestViewer','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::TrustStoreAdministrator','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::SQLCCAdministrator','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::SMTPDestAdministrator','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::SAMLAdministrator','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::RuntimeConfAdministrator','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::NetDestAdministrator','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::JobAdministrator','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::HTTPDestAdministrator','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.admin.roles::JobSchedulerAdministrator','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.roles::Transport','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.roles::SLP_display','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.pe.roles::PE_Display','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.roles::SLP_CTS_ping_admin','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.roles::Display','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.pe.roles::PE_Execution','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.roles::SLP_CTS_deploy_admin','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.roles::ExecuteTransport','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.roles::Developer','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.pe.roles::PE_Activation','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.roles::DevelopmentExpert','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.pe.roles::PE_Administrator','Z_MARKH');
call PUBLIC.GRANT_ACTIVATED_ROLE('sap.hana.xs.lm.roles::Administrator','Z_MARKH');

GRANT CONTENT_ADMIN TO Z_MARKH;
GRANT MODELING TO Z_MARKH;
GRANT CATALOG READ TO Z_MARKH;
GRANT CREATE SCHEMA TO Z_MARKH;
GRANT DATA ADMIN TO Z_MARKH;
GRANT EXPORT TO Z_MARKH;
GRANT IMPORT TO Z_MARKH;
GRANT REPO.CONFIGURE TO Z_MARKH;
GRANT REPO.EXPORT TO Z_MARKH;
GRANT REPO.IMPORT TO Z_MARKH;
GRANT REPO.MAINTAIN_DELIVERY_UNITS TO Z_MARKH;
GRANT REPO.MODIFY_CHANGE TO Z_MARKH;
GRANT REPO.MODIFY_FOREIGN_CONTRIBUTION TO Z_MARKH;
GRANT REPO.MODIFY_OWN_CONTRIBUTION TO Z_MARKH;
GRANT REPO.WORK_IN_FOREIGN_WORKSPACE TO Z_MARKH;
GRANT TABLE ADMIN TO Z_MARKH;
GRANT SELECT ON SCHEMA _SYS_STATISTICS TO Z_MARKH;

That Z_MARKH user will allow me to create and manipulate the data I need in the HDB. It will also have the necessary authorizations in order to use and manage the xsengine.

Workspace creation

In order to create objects the the HDB using the HDBstudio, you’ll have to create a workspace.To do so, with the dev account created previously ( Z_MARKH )  open the SAP HANA development perspective :

The perspective opens and you get access to the Repositories view :

 

Select the system you need to create a repository for and then do the following choices to create your workspace : Select “Default” and “create repository workspace”:

 

Check the workspace Root and Location suits your needs and click Finish. Your workspace gets created and you now have the possibility to create a repository package to store your objects in.Here I’ve created the Z_MARKH package in order to store the objects I’ll create later on.

 

 

 

Hana Tables creation

In order to store the formatted data pulled out of the system views, I have to build a repository comprised of several tables. Creating a Hana Table is done this way :

Select the package in which the table will be created and

Select other -> type in “table” -> select SAP HANA -> Database table

The next screen shows in which workspace folder the objects will be stored.

Filename : type in the name you want to give to this table ( filename will be completed automatically with .hdbtable ) .

Once you click finish, the windows to defined your table will open :

A syntax example is as follows :

table.schemaName        = "Z_MARKH" ;
table.tableType              =  COLUMNSTORE ;
table.columns                 =
[
{name = "EVENT_TIME" ; sqlType = VARCHAR; length = 32; comment = "Timestamp ID (UTC)";},
{name = "HOST" ; sqlType = VARCHAR; length = 16;comment = "Host name";},
{name = "PORT" ; sqlType = VARCHAR; length = 6;comment = "Port";},
{name = "SERVICE_NAME" ; sqlType = VARCHAR; length = 24;comment = "Service name";},
{name = "EVENT_NAME" ; sqlType = VARCHAR; length = 24;comment = "Event name";},
{name = "SYSTEM_ACTIVE" ; sqlType = VARCHAR; length = 24;comment = "System active";},
{name = "SYSTEM_STATUS" ; sqlType = VARCHAR; length = 24;comment = "System status";},
{name = "HOST_ACTIVE" ; sqlType = VARCHAR; length = 24;comment = "Host active";},
{name = "HOST_STATUS" ; sqlType = VARCHAR; length = 24;comment = "Host status";},
{name = "SERVICE_ACTIVE" ; sqlType = VARCHAR; length = 24;comment = "Service active";},
{name = "Z_SYSTEM_ID" ; sqlType = VARCHAR; length = 3; comment = "Hana SID";},
{name = "Z_DB_NAME" ; sqlType = VARCHAR; length = 3; comment = "Hana DB NAME";},
{name = "Z_HOST" ; sqlType = VARCHAR; length = 16;comment = "Host name";},
{name = "Z_USAGE" ; sqlType = VARCHAR; length = 16;comment = "DB usage";}

];

table.primaryKey.pkcolumns = ["EVENT_TIME","HOST","PORT","SERVICE_NAME"];

A quick description of the fields :

table.schemaName	= “<Schema_Name>” ; -- refers to the target schema where you wish the table to be created in

table.tableType	= 	<Type_of_table> ; -- wheteher COLUMNSTORE or ROWSTORE

--Table column clause :  this will define our table layout
table.columns	=

[
{name = “<field1>” ; sqlType = <Sql_Data_type1>;length = <Length_of_characters>;comment =“<Optional comment / description of the field>”;},
...
{name = “<fieldN>” ; sqlType = <Sql_Data_typeN>; length =<Length_of_characters>;comment = “<Optional comment / description of the field>”;},
];

table.primaryKey.pkcolumns = [“<primary_key_field1>”,”<primary_key_fieldN>”];

You’ll find a HANA datatypes reference here. Once you’ve defined the table, you just have to activate it. A syntax and consistency check is performed automatically.

If everything is O.K, the table is created and activated. The table appears in the package with a green checkmark,, meaning everything is O.K :

All the tables for my simplistic data model will be created this way ( will represent around 30 tables to be created in the Z_MARKH schema ).

Stored procedures creation

The data loads are based on the execution of stored procedures. These will format and load the data pulled out of the system views to my metrics-mart. Steps are similar to the creation of an HDB table but you’ll choose :

The next screen shows in which workspace folder the objects will be stored.

Filename : type in the name you want to give to this table ( filename will be completed automatically with .hdbprocedure ) .

You’ll also specify the target schema for the procedure :

 

When everything is specified just click finish

The stored procedure editor pops up with some default code in :

• Quick explanation :

PROCEDURE : After this keyword is the name of the schema where this procedure will be stored ( Z_MARKH ) followed by the development package name ( Z_MARKH , again ) and part the procedure name we gave during the procedure creation. The whole constitutes the stored procedure name.

() : the brackets are used to enter the input parameters like in this procedure for example :

 

LANGUAGE : this keyword tells the HANA system the language that will be used in this piece of code. Will be SQLSCRIPT. Can also be R language.

SQL SECURITY :

INVOKER : means the security privileges of the ivoker user will be used.

DEFINER : means the security privileges of the definer user will be used

READS SQL DATA : tells the HANA system that this stored procedure is used to read data out of tables only and will not perform any data manipulation (inserts/deletes/updates) . This will , of course, be removed from the procedures used to load data in the metrics mart.

BEGIN / END; : the stored procedure code will be written between these keywords.

 

Once you’ve defined the stored procedure, just like for the definition of the table, you just have to activate it. A syntax and consistency check is performed automatically for the stored procedure, reviewing the consistency up to the definition of the table.

If everything is O.K, the stored procedure is created and activated. The procedure appears in the package with a green checkmark,, meaning everything is O.K

 

Next step 

 

Next step: create the needed objects for the Xsengine scheduler. For this solution, I want everything to run in HANA, so in order to have the data loads done, I’ll plan everything using the xsengine scheduler. Some additional objects need to be created for this purpose.

To be continued: BOBJ & HANA for Basis (4) … .