Working with Tables

In the session “2.3 Row Store Vs Column Store”, we learned following things:

• SAP HANA supports both raw based and column based tables.
• Column tables provide
• Faster data access
• Better compression
• Better parallel processing

In this session, we will learn :

• How to create, update and delete tables in SAP HANA.
• How to see definition and data preview of tables.
• How to change column type of a table.

Create a Table Using Standard SQL:

1. Select your Schema. Right click and select “SQL Console”. Otherwise you can also click on “SQL” button on top panel as shown in below figure.

2. Copy the SQL statement below in SQL editor.

CREATE COLUMN TABLE TEST_TABLE1 ( 

            ID INTEGER, 

            NAME VARCHAR(10),

            DESCRIPTION VARCHAR(100),

            PRIMARY KEY (ID)

);

3. Click on Execute

4. Right-click on Tables under the Navigator Tab and click on Refresh to display the name of the newly created table.

5. Right click on the table and click on “Open Definition” to see the table details.

Create a Table with The SAP HANA Studio Tools:

1. Select your Schema. Right click and select “New Table”

2. Enter the Table Name

3. Choose the Table Type, e.g. “Column Store”

4. Enter the required table fields, data types, Key characteristics, etc. by clicking on the “+” sign below, and click on the Create table icon (or F8) when ready.

Enter Data in Table and Display Data Preview:

1. Open SQL Editor and copy below SQL statements. Click on Execute icon (or F8).

INSERT INTO TEST_TABLE1 VALUES (1, ‘A’, ‘A12345′);

INSERT INTO TEST_TABLE1 VALUES (2, ‘B’, ‘B12345′);

2. Right click on the table and select “Open Content”. This will show the content of the table as shown in image below.

Change Column Type of Tables:

1. You can change the table type from COLUMN to ROW or vice-versa. Open the SQL editor and copy the below SQL statement. Click on Execute (or F8).

ALTER TABLE TEST_TABLE1 ALTER TYPE ROW;

2. Open the table definition by right clicking on table and selecting “Open Definition”. Check that the table type has been changed from COLUMN to ROW.

Click here to go to Next Chapter. 4.5 Attribute View