Creating an SAP HANA HDB Table

Welcome to the next tutorial on the three part series explain different ways to create tables. In this one, we learn how to create an SAP HANA HDB table. It is recommended that you also read the first two parts as well.

To recap, the main methods to create a table are:

  1. Using the graphical method that has been already discussed.
  2. Using the SQL script method which has been already discussed.
  3. Using the HDB tables method which will be discussed here.
  4. Using the HDBDD method discussed in a separate tutorial.(Recommended method in non XSA projects).
  5. Using the HDBCDS method which would be discussed in a future tutorial(Recommended method in XSA projects).

To start off, if you remember my earlier tutorials, I recommended to always remain in the “SAP HANA Development” perspective of HANA. For this tutorial, it is mandatory that you are in the development perspective to move forward. Read this tutorial again if you don’t remember how to switch perspectives.

Grouping Development objects – Creating a SAP HANA Package

Until now, we have done everything in the Catalog folder, but the HDB table code is to be written in the Content folder. All such objects and future analytical objects that we create will be in the Content folder. All developments have to be grouped under a “Package”. Package is just another layer of grouping for all development and modeling objects. Inside content, let’s create a new package for all our future developments. To do this, right click on the content folder, and follow the path shown below to click on “Package”

SAP HANA HDB TABLE HDBTABLE CREATE HDB TABLE

Give it a name and description and press OK. I am prefixing a zero to make sure my package comes up on top as it is sorted by symbols, numbers and then alphabets. You can provide any relevant names.

SAP HANA HDB TABLE HDBTABLE CREATE HDB TABLE

Then move to the repositories tab as shown below. If you are going here for the first time, you need to import the Workspace into your local memory.

To do this, press “Import Remote Workspace” after right clicking on the (Default) repository as shown below.

SAP HANA HDB TABLE HDBTABLE CREATE HDB TABLE

Provide a folder for this to be imported to.

SAP HANA HDB TABLE HDBTABLE CREATE HDB TABLE

Once done, you will notice a small tick symbol on top of the repository icon as marked below.

SAP HANA HDB TABLE HDBTABLE CREATE HDB TABLE

Creating an SAP HANA HDB table in the repository

Now, right click on the newly created package and select the “Other” from the context menu path shown the illustration.

Note: If you don’t see the repository tab and are lost at this point, that means you are not in the Development perspective. You can see this on the top right of your HANA Studio as shown below.

SAP HANA HDB TABLE HDBTABLE CREATE HDB TABLE

A wizard opens up. Write ‘table’ in the search bar marked by the red arrow as shown below. The objects with Table in the name show up. Select Database table as circled below and click next.

SAP HANA HDB TABLE HDBTABLE CREATE HDB TABLE

Provide a table name as shown below and leave the template as blank.

SAP HANA HDB TABLE HDBTABLE CREATE HDB TABLE

This will open up a blank editor screen where in you need to put in the HDB Table code. This is a simple code although it is not SQL It is a HANA internal syntax.



The syntax to be used is:

table.schemaName        = “<Schema_Name>” ;

table.tableType              =  <Type_of_table> ;

table.columns                 =

[

{name = “<field1>” ; sqlType = <SQL_Datatype1>; length = <Length_of_characters>;comment = “<Optional_Description>”;},

{name = “<field2>” ; sqlType = <SQL_Datatype2>; length = <Length_of_characters>;comment = “<Optional_Description>”;},

{name = “<fieldN>” ; sqlType = <SQL_DatatypeN>; length = <Length_of_characters>;comment = “<Optional_Description>”;},

];

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

What does this even mean?

<Schema_Name> refers to the target schema where you wish the table to be created in.

<Type_of_table> can take two values ROWSTORE and COLUMNSTORE. You should ideally keep this as COLUMNSTORE unless there is a special requirement to do so. To know the difference between ROWSTORE and COLUMNSTORE click here.

<field>  refers to the name of the fields you wish to add in this table.



<SQL_Datatype> refers to the datatype of the field

length = <Length_of_characters>;  This section signifies the length of your field. Please omit this for INTEGER types as they don’t require a specified length.

comment = “<Optional_Description>”; This is also an optional section specifying the description of a field.

<primary_key_field> takes all the primary key field names separated by a comma.

Let’s create the same table structure we created in the other cases. To download the code I used below, click here.

According to the above syntax, the code would be as below. Press the activate button marked by the arrow below.

SAP HANA HDB TABLE HDBTABLE CREATE HDB TABLE

This should create this table in the 0TEACHMEHANA schema. Once built, it’s the same as a regular table. Go back to the Systems tab and in this schema, go to the tables folder and refresh it.

SAP HANA HDB TABLE HDBTABLE CREATE HDB TABLE

You would notice that the table has been created.  It works the same way but has some additional capabilities that we will discuss in upcoming tutorials. Also, you might notice that the package name is also prefixed to the table name automatically. That’s something exclusive to HDB Tables.

SAP HANA HDB TABLE HDBTABLE CREATE HDB TABLE

Double clicking on the HDB table shows the structure has been defined as required.

SAP HANA HDB TABLE HDBTABLE CREATE HDB TABLE

This marks the successful completion of our tutorial.

HDBTables are the recommended form of tables to be used whenever custom tables are to be created. The only reason I use the other forms of table creation are for quickly creating and testing something but for actual deliverables, only HDB tables must be created.

Edit (25-Jun-2017): Only CDS based tables are the best practice as of this date. These can be HDBDD tables (described in the next tutorial) or HDBCDS table (for XSA based projects)

Their advantages will be further clear in our further tutorials so stay tuned for new posts.

Thank you for reading this tutorial on creating an SAP HANA HDB table and if you liked it, please share this document across social media using the share buttons below. Also, don’t forget to subscribe to our newsletter to get the latest updates on added tutorials.

Happy learning!

<<Previous Tutorial                                                                                                             Next Tutorial>>

Tagged , , , , , , , , , , , , , .

7 Comments

    • In this example, it is just a field name. There is no special “use” for it. It’s just a measurable quantity of revenues in US dollars. You can use anything you wish to.

  1. Hi, i have 2 issues when altering HDB tables.

    1. Once i activated the HDB table and later if i want to change the order of the columns it is not changing. For ex: I have 3 fields in below order and activated successfully.

    Product_Group
    MASTER_MATERIAL
    LEGAL_MATERIAL

    Then i want to change the order of these columns as below but it is not changing.
    MASTER_MATERIAL
    Product_Group
    LEGAL_MATERIAL

    2. I believe it is possible to change the data type length of the column. for ex: i changed Product_Group data type length from NVARCHAR(65) to NVARCHAR(100). Some times it activated successfully but some times it gives me

    “A SQL error occurred while creating the table in the schema” error and i have no way to activate the table again unless delete and re-create it.(deleting is not good choice as the table is already used in many views)

    Please let me know what are the datatypes and length’s we can alter in HDB table.

    Regards,
    Mathi

  2. Hi,
    I would like to know if there is any way of scheduling procedures without the development console?

    Thanks
    Murali

    • XS Jobs are primarily used for this purpose but 3rd party scheduling tools also may be used even though I’ve personally never had such a scenario.

  3. Hi Shyam,

    I am getting below error messge while activing my calcualtion view

    Short Text
    Information view could not be created

    Cause
    Dependent information views might not have been activated successfully

    Recommendations
    1) Check if the dependent information views exist.
    2) Also check if the dependent information views have been activated successfully.

    Error Message
    Repository: Encountered an error in repository runtime extension;Model inconsistency. Create Scenario failed:

    Multiple definitions of node REGION found(calculationNode (REGION) -> operation (CalculationView))

    The following errors occurred: Inconsistent calculation model (34011)
    Details (Errors):
    – calculationNode (REGION) -> operation (CalculationView): Multiple definitions of node REGION found.
    – calculationNode (REGION) -> inputs -> input (REGION): Input references node REGION.
    – calculationNode (SALES) -> operation (CalculationView): Multiple definitions of node SALES found.
    – calculationNode (SALES) -> inputs -> input (SALES): Input references node SALES.

    PRODUCT_IDREGION_IDPRODUCT_NAMEREGION_NAMESUB_REGION_NAMEREGION_IDSALES_AMOUNTSALE_REPORT

  4. Hi,

    I m getting error as Target Schema” doesn’t exist when i try to activate table. Please advise.

    Thanks,
    Venkata

Leave a Reply

Your email address will not be published. Required fields are marked *