You can create row or Column store tables in SAP HANA using create table option. A table can be created by executing a data definition create table statement or using graphical option in HANA studio.

When you create a table, you also need to define attributes inside it.

SQL statement to create a table in HANA Studio SQL Console −

Create column Table TEST (
ID INTEGER,
NAME VARCHAR(10),
PRIMARY KEY (ID)
);

Creating a table in HANA studio using GUI option −

1 32

When you create a table, you need to define the names of columns and SQL data types. The Dimension field tells the length of value and the Key option to define it as primary key.

SAP HANA supports the following data types in a table −

2 31

SAP HANA supports 7 categories of SQL data types and it depends on the type of data you have to store in a column.

Numeric
Character/ String
Boolean
Date Time
Binary
Large Objects
Multi-Valued

The following table gives the list of data types in each category −

3 31

Date Time

These data types are used to store date and time in a table in HANA database.

DATE − data type consists of year, month and day information to represent a date value in a column. Default format for a Date data type is YYYY-MM-DD.
TIME − data type consists of hours, minutes, and seconds value in a table in HANA database. Default format for Time data type is HH: MI: SS.
SECOND DATE − data type consists of year, month, day, hour, minute, second value in a table in HANA database. Default format for SECONDDATE data type is YYYY-MM-DD HH:MM:SS.
TIMESTAMP − data type consists of date and time information in a table in HANA database. Default format for TIMESTAMP data type is YYYY-MM-DD HH:MM:SS:FFn, where FFn represents fraction of second.

Numeric

TinyINT − stores 8 bit unsigned integer. Min value: 0 and max value: 255
SMALLINT − stores 16 bit signed integer. Min value: -32,768 and max value: 32,767
Integer − stores 32 bit signed integer. Min value: -2,147,483,648 and max value: 2,147,483,648
BIGINT − stores 64 bit signed integer. Min value: -9,223,372,036,854,775,808 and max value: 9,223,372,036,854,775,808
SMALL − Decimal and Decimal: Min value: -10^38 +1 and max value: 10^38 -1
REAL − Min Value:-3.40E + 38 and max value: 3.40E + 38
DOUBLE − stores 64 bit floating point number. Min value: -1.7976931348623157E308 and max value: 1.7976931348623157E308

Boolean

Boolean data types stores Boolean value, which are TRUE, FALSE

Character

Varchar − maximum of 8000 characters.
Nvarchar − maximum length of 4000 characters
ALPHANUM − stores alphanumeric characters. Value for an integer is between 1 to 127.
SHORTTEXT − stores variable length character string which supports text search features and string search features.

Binary

Binary types are used to store bytes of binary data.

VARBINARY − stores binary data in bytes. Max integer length is between 1 and 5000.

Large Objects

LARGEOBJECTS are used to store a large amount of data such as text documents and images.

NCLOB − stores large UNICODE character object.
BLOB − stores large amount of Binary data.
CLOB − stores large amount of ASCII character data.
TEXT − it enables text search features. This data type can be defined for only column tables and not for row store tables.
BINTEXT − supports text search features but it is possible to insert binary data.

Multivalued

Multivalued data types are used to store collection of values with same data type.

Array

Arrays store collections of value with the same data type. They can also contain null values.

SAP HANA – SQL Overview

SQL stands for Structured Query Language.

It is a standardized language for communicating with a database. SQL is used to retrieve the data, store or manipulate the data in the database.

SQL statements perform the following functions −

Data definition and manipulation
System management
Session management
Transaction management
Schema definition and manipulation

The set of SQL extensions, which allow developers to push data into database, is called SQL scripts.

Data Manipulation Language (DML)

DML statements are used for managing data within schema objects. Some examples −

SELECT − retrieve data from the database
INSERT − insert data into a table
UPDATE − updates existing data within a table

Data Definition Language (DDL)

DDL statements are used to define the database structure or schema. Some examples −

CREATE − to create objects in the database
ALTER − alters the structure of the database
DROP − delete objects from the database

Data Control Language (DCL)

Some examples of DCL statements are −

GRANT − gives user’s access privileges to database
REVOKE − withdraw access privileges given with the GRANT command

Why do We Need SQL?

When we create Information Views in SAP HANA Modeler, we are creating it on top of some OLTP applications. All these in back end run on SQL. Database understands only this language.

To do a testing if our report will meet the business requirement we have to run SQL statement in database if Output is according to the requirement.

HANA Calculation views can be created in two ways – Graphical or using SQL script. When we create more complex Calculation views, then we might have to use direct SQL scripts.

How to open SQL console in HANA Studio?

Select the HANA system and click on SQL console option in system view. You can also open SQL console by right click on Catalog tab or any on any Schema name.

4 29

SAP HANA can act both as Relational as well as OLAP database. When we use BW on HANA, then we create cubes in BW and HANA, which act as relational database and always produce a SQL Statement. However, when we directly access HANA views using OLAP connection, then it will act as OLAP database and MDX will be generated.

SAP HANA – Log Configuration

The SAP HANA system logs all the transactions that change application data or the database catalog in log entries and stores them in log area. It uses these log entries in log area to roll back or repeat SQL statements. The log files are available in HANA system and can be accessed via HANA studio on Diagnosis files page under Administrator editor.

5 26

During a log backup process, only the actual data of the log segments is written from the log area to service-specific log backup files or to a third-party backup tool.

After a system failure, you may need to redo log entries from log backups to restore the database to the desired state.

If a database service with persistence stops, it is important to ensure that it is restarted, otherwise recovery will be possible only to a point before service is stopped.

Configuring Log backup Timeout

The log backup timeout determines the interval at which the log segments are backed up if a commit has taken place in this interval. You can configure the log backup timeout using the Backup Console in SAP HANA studio −

6 21

You can also configure the log_backup_timeout_s interval in the global.ini configuration file.

The log backup to the “File” and backup mode “NORMAL” are the default settings for the automatic log backup function after installation of SAP HANA system. Automatic log backup only works if at least one complete data backup has been performed.

Once the first complete data backup has been performed, the automatic log backup function is active. SAP HANA studio can be used to enable/disable the automatic log backup function. It is recommended to keep automatic log backup enabled otherwise log area will continue to fill. A full log area can result a database freeze in HANA system.

7 17

You can also change the enable_auto_log_backup parameter in the persistence section of the global.ini configuration file.

So, this brings us to the end of blog. This Tecklearn ‘SQL Overview and Data Types in SAP Hana’ blog helps you with commonly asked questions if you are looking out for a job in SAP Hana and SAP Domain. If you wish to learn SAP Hana and build a career in SAP domain, then check out our interactive, SAP HANA Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/sap-hana-training-certification/

SAP HANA Training

About the Course

SAP HANA is an in-memory computing application that is designed and developed to boost the business processes, deliver smart solutions, and simplify both hardware and software environments. Our Sap Hana Training course will help you understand and learn the fundamentals and will also felicitate on training hands-on for the better grasp on the course. Further, we have the highly qualified professionals who will train you about Sap Hana Studio, Modelling, Security features and its various other aspects. You will understand why SAP HANA is a fundamentally different database engine upon the completion of this SAP HANA course.

Why Should you take SAP HANA Training?

The average Sap Hana Consultant salary $165,750 per year or $85 per hour. (neuvoo.com).
SAP HANA is the highest growing technology; hence, there is no surprise in plenty of career opportunities in this field. Since it is one among the fastest-growing products in the history of SAP, it is considered by the industries as a ground-breaking key for in-memory databases.
SAP HANA currently has more than 6,500 customers globally.

What you will Learn in this Course?

Introduction to SAP HANA

Fundamentals of SAP HANA
Capabilities of SAP HANA
Limitations of SAP HANA

Key Features of SAP HANA

Key Features: High Performance functionalities In-Memory computing, Columnar store database, Data Compression and Massive Parallel Processing
Using SAP HANA for Non-SAP Applications

Architecture of SAP HANA

Detailed Architecture of SAP HANA Database
Concept of SAP HANA Landscapes and Scenarios

Overview of HANA Studio

SAP HANA System – Perspectives, Administration, Modelling, Development Plan
HANA Database SQL Basics and Database SQL Script
Types of statements and data types
Operators, expressions and basic query execution
Sub-queries, Types of Joins, Expressions and Loops
Catalog – Schema, Table, Views, Functions, Stored Procedures, Index, Synonyms, Sequences, Triggers

Data Provisioning

Data Provisioning with Flat File upload
Provisioning – SDA (Smart Data Access)
Joins Types in HANA

SAP HANA Modelling

Types of Models
Attribute Views, Joins and Using Filter Operations
Creating Restricted and Calculated Columns
Using Hierarchies
Analytic Views – Star Schema design and Multi-Dimensional Modelling
Variables and Input parameters

Calculation Views

Dimension Calculation View
Information View
SAP HANA Variables
Introduction to Input Parameters

SAP Project

Using HANA analytical view building of COPA (Controlling and Profitability Analysis) model
SAP HANA COPA for evaluation of market segments and classification of markets according to the products, customers or any combination of it

Dimension Calculation View

Dimension Calculation View – Star Join Calculation view
Using Projection, Join, Aggregation, Union and Rank

In-depth Modelling

Refactoring information models
Schema Mapping
Propagate to schematics and Show Lineage
Schema Mapping
Generating Time Data
Union Pruning
Using Time Travel
Migrating deprecated Information models
Using Currency Conversion
Web based Modelling Work bench

Analytic Privileges and Decision Tables

Classical Analytic Privileges
SQL Analytic Privileges
Dynamic analytic Privileges.
Turning Business Rules into Decision tables
Table Functions

SAP HANA Table Function

Query Optimizing Technique related to SAP HANA Tables
Web Based Modelling work bench

SAP HANA on Cloud

SAP Analytics with SAP Reporting environment SAP BOBJ – tools, WEBI, LUMIRA, DASHBOARD (integration between sap Hana and bob)

Advanced Topics Overview

SAP HANA Dynamic tiering
Delta Merge
SDI (Smart Data Integration)
SDA (Smart Data Access)

DATA Provisioning

SLT – SAP Landscape Transformation
BODS – Business Objects Data Services

Analytical Privileges

Classical XML Based Analytical Privileges
SQL Analytical Privileges

HANA Administration and Security

Hana Administration
Security in SAP HANA – User Management

Got a question for us? Please mention it in the comments section and we will get back to you.

527