Currently Being Moderated

To create a table in HANA we have different methods. I will show you some of these methods to create a table and load the data into it using flat files.

 

Prerequisites: - SAP HANA Studio with the Server system added to the Studio. See the link at the end of this blog to learn on how to do this.

 

Note: - Click on images to see clearer and larger images.

 

Go to Administrative Perspective. You can find this icon on the top right corner of the HANA Studio.

 

Perspective.JPG

 

Method 1:-

 

                                                                          

Schema: - Schema is a logical place for creating and maintaining Tables / Views / DB objects.

 

 

Procedure to create schemas where you import table definitions.

  1. From the Quick Launch tab page, choose SQL Editor.
  2. In the SQL editor, write the script, create schema <schema name>.
  3. Execute the script.

    

System creates the schema, and places it under the Catalog node.

 

From the Navigator list select your Schema and expand to see the child nodes. In the child nodes right click on the Table folder and select New Table.

Create_table.JPG

This will open a new session where you can define the table name,

Table type, column names and the data types. For more explanation on type of tables see link at the bottom of this blog.

 

Tip:- While defining the column names only Aplhanumeric and Underscore is accepted.

        Choose the data types wisely if not the table will be created but the data load to the table will fail. You can alter the table, please see below on how to alter a table.

create_table1.JPG

After you define the column names press the execute icon or press F8 to create the table. You will see the below information after executing.

create_table2.JPG

 

If you want to alter the table, you can follow the below steps.

 

How to Alter a Table.

 

Right click on the table to be changed and Select Open

Definition option from the context menu.

 

Alter_table.JPG

In the definition screen right click on any column and select “Export SQL” or simply click on the icon shown below.

Alter_table1.JPG

In the SQL Editor tab, delete the existing code and write the SQL command to alter the table and press Execute button or simply press F8 key. Sample SQL code is shown below. For more SQL commands see the link at the buttom of this blog.

Alter_table3.JPG

Go back to Table Definition tab and click refresh to see the changes. The data type for column MAT_GRP is now changed.

  Alter_table4.JPG

 

Method 2:-

Right click on the Schema and select “SQL Editor” from

Context Menu.

  create_table_sql_editor.JPG

A new tab is opened and you have a place to write your SQL commands to create a table. Sample code for creating a table is shown below. After writing your SQL commands press Execute icon or simply press F8. You can see the status of the execution below. Once after Successful execution, right on the Table folder and click on refresh option to see the newly created table.

create_table_sql_editor1.JPG

 

Now that we have created tables, we will see how to load data into these tables using flat file as source.

 

Click on File menu ---> Import.

Load_data_existing_table.JPG

In the resulting window, select “Data From Local File” option under SAP HANA Content folder and click Next button.

Load_data_existing_table1.JPG

In the resulting window, Select your Target system and click Next button.

Load_data_existing_table2.JPG

In the resulting window, select the source file and enter the details in File Details and Target Table options.

Load_data_existing_table3.JPG

Select the table to be loaded and click OK button.

Load_data_existing_table4.JPG

In the resulting window, Map the fields between source and target structures. You have two options to choose the mapping.

 

  1. One to One: Choose this to map field by field.
  2. Map by Name: Choose this to map the fields with the same column name. The system will automatically propose the mapping based on the matching column names. If any fields are not mapped, you can manually map those fields by dragging the fields from source structure on to target structure.

 

You can see the preview of the source data.

Load_data_existing_table5.JPG

Once you click next, you will see the below screen with mapping. If you don’t want to load data to any column then you can delete the join. A primary key join is mandatory.

Load_data_existing_table6.JPG

Click Next button, you will see the below screen. If you have any existing data in the target table it will below shown in the Data from existing table section. Click on Finish button to start the loading.

Load_data_existing_table7.JPG

You can see the Job log in the Job log tab located in the below.

Load_data_existing_table8.JPG

Method 3:-

 

Follow the same steps above (File-->Import) but the only change is instead of selecting “Existing table” option select “New Table option. Select your Schema and give a table name and click Next button.

Create_table_from_flatfile.JPG

The system proposes the mapping automatically, you have to select the Key in the target structure. You can change the data type in the target structure. Click Finish.

Create_table_from_flatfile1.JPG

A table is created under the Schema and the data is also loaded into the table. If you don’t see the table, right click on the schema and select refresh.

Load_data_existing_table9.JPG

To preview the data, Right click on the table and select “Open Data Preview” option from the context menu.

Data Preview.JPG

This will open a new session in the right panel.

Data Preview1.JPG

Click on Distinct Values tab to see the number of distinct values for each object.

Distinct Values.JPG

Click on Analysis tab to see the data in the form of Charts. You can select different types of charts from the “Chart type” drop down. Drag the objects from the “Available Objects” window and drop them in the “Labels Axis” and “Value Axis”. You can filter the data, to do this drag the object to be filtered into the “Filter” List.

Analsys.JPG

 

This brings to an end on how to create a table in HANA and load the data into those tables.

 

Blog on how to create a system in HANA Studio.

http://scn.sap.com/community/developer-center/hana/blog/2012/07/03/how-to-create-a-system-on-sap-hana-studio-and-how-to-create-a-user-on-sap-hana-data-base

 

Basic SQL commands in HANA

http://wiki.sdn.sap.com/wiki/display/inmemory/SAP+HANA+commonly+used+SQL

 

SAP In-Memory Database SQLScript Guide

http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/20514d3c-f4e1-2e10-55a5-dc9a3efc0639?QuickLink=index&overridelayout=true&52591874613119

 

Data Types in HANA

http://help.sap.com/hana/html/_csql_data_types.html

 

Details about Tables (Column and Row Store) in HANA

http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/d0086776-fa54-2c10-2ea6-f8000c954e25

Comments

Actions

Filter Blog

By author:
By date:
By tag: