How to create tables using hdbtable in SAP HANA

1
17014

Dear SAPLearners, in this tutorial we will learn how to create tables using hdbtable in SAP HANA.

In this tutorial i will create a table in SAP HANA Cloud Trial Instance.If you have access to your own SAP HANA system you can still follow these syntax.

Using hdbtable syntax you can create design-time file in the repository to create tables in SAP HANA.Table will be created in HANA database when you successfully activated the file.

Prerequisites

  • You have access to SAP FREE HANA Cloud Platform.if you don’t have click here to get developer access.
  • You should have created a new HANA Instance. To know how to create click here.

Environment

SAP Cloud Platform Neo

Step-by-Step Procedure

1. Logon to SAP HANA Cloud Platform cockpit.

2. In SAP HANA Cloud Platform cockpit, choose HANA Instance from the menu and click on development workbench tools.

HANA Web-based Development Workbench
HANA Web-based Development Workbench

3. You should see development workbench editor like below.

HANA Development Workbech
HANA Development Workbench

4. Now click on drop down beside to the plus sign to navigate to the HANA Catalog.

HANA Catalog

5. You should see the screen like below.

SAP HCP Catalog

6. You can see the list of schema available, NEO_ is the your schema. We are going to create tables under this schema only.

7. Now go back to development workbench editor and expand your package and right click on the package → choose Create File.

Create a File8. Provide the file name EmpTable.hdbtable. “EmpTable” is your table name. Copy the below hdbtable syntax and paste it in the file.

// 1.Schema Name
table.schemaName = "NEO_360QD7TDE6SWBC6V001WO9VGJ";

// 2.Table Type
table.tableType = COLUMNSTORE;

// 3.Temporary table or not
table.temporary = true;

// 4.Table Logging
table.loggingType = NOLOGGING;

// 5.Table columns
table.columns = [
{name = "EMP_ID";   sqlType = INTEGER; nullable = false;},
{name = "EMP_NAME"; sqlType = VARCHAR; nullable = false;},
{name = "EMP_DEPT"; sqlType = NVARCHAR; nullable = true; length = 10; defaultValue = "SAP-ABAP";},
{name = "EMP_SAL";  sqlType = DECIMAL; nullable = false; precision = 12; scale = 3;}
                ];

// 5.Table indexes
table.indexes =  [
{name = "MYINDEX1"; unique = true; order = DSC; indexType = B_TREE; indexColumns = ["EMP_NAME"];}
	         ];
	
// Table primary keys
table.primaryKey.pkcolumns = ["EMP_ID"];

9. Now we will look at the above syntax

  • table.schemaName – schema name in which you want to store the table.
  • table.tableType – table type of the table.
    • There are 2 possible values COLUMNSTORE (or) ROWSTORE.
  • table.temporary – this option makes the data in the table session-specific, data from the local temporary table is automatically deleted when the session is terminated.
    • There are 2 possible values true (or) false.
  • table.loggingType – this option enables logging in a table.
    • There are 2 possible values LOGGING (or) NOLOGGING.
  • table.columns – this option enables to add columns to the table
    • name – column name
    • sqlType – column data type
    • nullable – true/false
    • unique – true/false
    • length – length of the column
    • scale – E.g:- 3,10,15
    • precision – E.g:- 2,3,5
    • defaultValue
    • comment 
  • table.indexes – this option enables to create indexes to the table.
  • table.primaryKey.pkcolumns – this option enables to create primary keys in the table.

Below are the different SQL TYPES you can use when declaring the columns.

  • DATE
  • TIME
  • TIMESTAMP
  • SECONDDATE
  • INTEGER
  • TINYINT
  • SMALLINT
  • BIGINT
  • REAL
  • DOUBLE
  • FLOAT
  • SMALLDECIMAL
  • DECIMAL
  • VARCHAR
  • NVARCHAR
  • CHAR
  • NCHAR
  • CLOB
  • NCLOB
  • ALPHANUM
  • TEXT
  • SHORTTEXT
  • BLOB
  • VARBINARY

10. Now head back to your SAP HANA catalog to see the table which we created.

SAP HANA Table in SchemaCongrats, you have successfully created table in SAP HANA.

Please stay tuned to us for more SAP HANA tutorials.Please feel free to comment and let us know your feedback.

Comments are closed.