We have seen how to create a system on HANA Studio and how to create a user on SAP HANA Data Base in my previous blog.
Now we are discussing about How to create a table on SAP HANA 1.0 Data Base and about types of tables.
We have 3 types of tables:
- Row based
- Column based
- Table type
This is similar to traditional row based relational data base table. Major difference between traditional data base to In Memory data base is, Data always is maintained in RAM.Data is stored and retrieved row based.This technique is aquired from P*time In-Memory data base system which was acquired by SAP in 2005.
Data is stored in column wise in data base. This is originated from TREX (Text Retrieval and Information Extraction) technology.
This type of data is stored ion disk and only retrieved when it is required. When we don’t want to use the data for a long period of time and very rarely accessed or not needed any more,then we can keep maintain the data in this table.
Steps to Create a table
To create a table, initially we need to have SCHEMA, which is collection of Column views, Procedure, Tables and Views. We can compare this with Info Area from BW System to easily understand.
Now let us start with HANA Studio to create a Schema. Create a schema using SQL Editor as shown in the given below screen shot.
As soon as we selected SQL Editor, we get the below screen and write a syntax to create a schema.
Syntax: create schema <Schema_Name>
Execute the SQL Query using Deploy Button from right side corner.As soon as we execute the query, it will display the status information.
Correct the Error and execute it again.
So far, we could able to create schema successfully. Please refresh the Catalog menu to view created Schema.
As soon as it is refreshed, we can see the created table in our CBR_PRJ Schema.
Now, Let us start creating a table on schema SBR_PRJ. select scehma -> select Tables folder -> Select New table.
As we discussed earlier, we have 3 types of tables. By default system will create Column store data base.
Add your fields to data base as shown given below. here we need to provide field name, data type, size, default values and primary key.
We can check the table in the table space as shown given below.
Now using SQL statement we can create entries for CBR_EMP_DETAILS table.
We can check the below SQL statement which has been created by system. We can also create a table using SQL statement.
Syntax: Create column table “schema_name”.”table_name” ( Filed1 datatype (size), Filed2 data type (size) …. Primary key (Filed name) );
Now we need to create entries into the table CBR_EMP_DETAILS, for this we have another beautiful option.
i.e. Insert Statement as shown here, system will automatically create the insert statement to create table entries.
As soon as we select the insert statement, system will create the insert statement as shown given below.
Here we can also create multiple entries as shown given below.
We have created a table and table entries in CBR_EMP_DETAILS . Now we can see another beautiful option which has been provided by SAP HANA.
Select table -> open data preview to view table content.
We have 3 options to view the content which is there in table.
- Raw Data
- Distinct Values
We can also check the distinct values using distinct values tab option as shown below.
last option is Analysis tab, which is more useful to view data in different types options like table format or chart format.
So far, we have seen how t create a schema,table and how to see the table content in different formats.
I will keep you update with latest blogs on HANA. 🙂
I would be very happy to see your comments and suggestions.
Thanks & Regards,
Ravi K Chandragiri