How to create tables and load data into those tabl...

Former Member · ‎07-13-2012

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.

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.

From the Quick Launch tab page, choose SQL Editor.
In the SQL editor, write the script, create schema <schema name>.
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.

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.

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.

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.

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

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.

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

Method 2:-

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

Context Menu.

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.

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.

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

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

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

Select the table to be loaded and click OK button.

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

One to One: Choose this to map field by field.
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.

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.

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.

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

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.

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.

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.

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

This will open a new session in the right panel.

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

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.