SQL is a database query language which you use to interrogate databases with. There is a standard SQL which is common to all databases and then there are specific SQL Syntaxes related to each type of database such as Sap Hana, Oracle, MS SQL and so on. Learning the Basic SQL is good to start with. From Basic SQL you will be able to do everything you need to as a beginner. For example, creating tables, inserting updating deleting data from tables, writing queries.
Here is a simple simple online learning SQL link
Getting to SQL hana specific features is what we are going to guide to.
The easiest way to write any sap hana SQL is to use the interactive SQL tool
Using and maintaining table data is a very important part in a structured hana data model. Standard user tables and temporary tables have different purpose. Normal tables hold permanent data where as temporary tables are used to store and process intermediate results. Hana temporary table data is lost at the end of a session. It is mainly used in stored procedures.
Row tables and column tables have an serious impact in sap hana performance. Sap Hana Column tables have the advantage of being more compact and more performant in comparison to row table.
If you are new with Sap Hana and you are dealing with SQL on Sap Hana databases, well you will be happy to know, SQL syntaxes are standard across all databases. However, you will find now and then, non standard specific commands for a particular database.
Although, it is possible to operate Hana DDL via the Hana studio interface, it is advisable to perform any Data Definition Language (DDL) Statements via SQL Script. It is easier to trace, update and debug what has been done that way.
The following section will show you various tips and syntaxes regarding SQL in SAP HANA. You will find out how to retrieve a table column description in sap hana, how to extract a DDL to recreate a Table.
(DDL) Data Definition Language
Commands related to creation and alteration of objects such as table :
CREATE : to create objects ( table, synonyms, procedure, ...)
ALTER : to modify objects ( table, ...)
DROP : to remove objects ( table, synonyms, procedure, ...) from the database
TRUNCATE : to remove all records from a table
COMMENT : to add comments to a column
RENAME : to rename an object
(DML) Data Manipulation Language
Commands related to data manipulation in a table object:
SELECT : to retrieve data from the a table
INSERT : to insert data into a table
UPDATE : to updates data within a table
DELETE : to delete data from a table
MERGE : to insert or update following conditions
CALL : to execute a pprocedure
(DCL) Data Control Language
Commands related to access privileges to objects:
GRANT : to gives privileges to access and manipulate data and database
REVOKE : to remove privilege assigned to a user
(TCL) Transaction Control
Commands to manage data changes.
COMMIT : to save data changes permanently into a table
ROLLBACK : to wipe out any data changes made since last command : COMMIT
SAVEPOINT : a fixe point a transaction can be rollback to
How to create tables in sap hana?
There are different table type in Sap Hana. The main type of tables, which would be present in sap hana pool memory, is the Column table. Row tables could be present but would normaly not be permanent.
Local or global Temporary tables can be created on the fly within sap hana procedures and would exist only during procedures execution.
Create COLUMN table As select from another table With filtered Data
CREATE COLUMN TABLE "A_SCHEMA_X"."A_NEW_TABLE" as
(SELECT * FROM "A_SCHEMA_Y"."OLD_TABLE" );
CREATE COLUMN TABLE "SCHEMA2017"."BACKUPFILE_NAMES" as (SELECT DESTINATION_PATH FROM M_BACKUP_CATALOG_FILES WHERE BACKUP_ID >= (SELECT TOP 1 BACKUP_ID FROM M_BACKUP_CATALOG WHERE STATE_NAME = 'successful' AND ENTRY_TYPE_NAME = 'complete data backup' ORDER BY UTC_START_TIME desc) );
Create COLUMN table With selected column of another table WITH NO Data
CREATE COLUMN TABLE "A_SCHEMA_X"."A_NEW_TABLE" AS (SELECT NAME, TOWN, COUNTRY FROM "A_SCHEMA_Y"."OLD_TABLE") WITH NO DATA;
Create COLUMN table LIKE another table WITH NO Data
CREATE COLUMN TABLE "A_SCHEMA_X"."A_NEW_TABLE" LIKE "A_SCHEMA_Y"."OLD_TABLE";
Create COLUMN table like another table WITH Data
CREATE COLUMN TABLE "A_SCHEMA_H"."A_NEW_TABLE" LIKE "A_SCHEMA_Z"."VERY_OLD_TABLE" WITH DATA;
How to retrieve a sap hana table column description rapidly?
You would like to know what are the different column types of a set of tables in order to decide how to build a query. You would like to build a query to retrieve or to update specific column name. You would like to know which columns to join together.
Here are 2 ways to get the quick table description using either hdbsql or hana studio:
How to get sap hana table description using the view TABLE_COLUMNS?
This query can be run either with hana studio SQL console or with hdbsql. If you change and run the query with hdbsql, you have possibilities, via shell script, to build up more sophisticated results.
How to get sap hana Table description with hdbsql specific option?
If you are using hdbsql, the quickest and the most simple way to get a table descrition is to use the hdbsql option \dc < SCHEMA_NAME >.< TABLE_NAME >
You can either get all table descriptions within a schema, which is very quick and handy, or you can target a specific table_name.
There is no particularity in the way to get rid of a table. Dropping table is done via a standard SQL syntax. Be aware there is no way to recover a table that would have been dropped accidentally. All data is lost for that table and database integrity may be at stake. You may have to recover the entire database from the lastest backup.
Creating new tables from existing table structure is very often a requirement when working on a new development project. Generating any DDL table could be a headache. Here are various ways to generate any create table DDL.
1/ Using the procedure "get_object_definition"
2/ Using hana studio.
3/ Using export table.
How to generate DDL using sap hana get_object_definition procedure?
Almong the various ways of extracting table DDL is the sap hana procedure "get_object_definition". Using this method requires either hana studio SQL console or hdbsql. This procedure will generate the create table syntax of the defined table. This method is particularly usefull for getting several DDL at once. If you integrate it within a shell script you can generate a complete set of DDL that way.
Be aware column result could be truncated if the DDL generated is tool large.
To solve the probleme : Set up the LOB column size in Hana studio. ( Hana Studio, Window, Preferences, Runtime, Result )
Here is a truncated DDL example after a paste in notepad : .
How to generate DDL using sap hana studio?
Hana studio is another way of get perfect and complete table DDL. This method is simple but it is one table at a time method. This procedure will generate a full create table syntax.
This method is very handy for getting a DDL from one database and executing the syntax immediately in another tenant database.
How to get table DDL using sap hana studio export?
Hana studio export will produce table DDL as well as data CSV files if requires and if you selected the option. This method is simple and you can select multiple tables.
The export procedure requires an empty directory in which individual subdirectories will be created for each table.
Now the export has been done, look onto the selected directory on you desktop. A subdirectory is created for each individual table. For the Example : D:/tmp/index/PR/PROJ_MANAG_SCHEMA/
You will find a file called "create.sql" on each subdirectory. The file contains the table DDL.
Sap hana table represent units of data storage. They are defined with a name and a table type. The table types are ROW or COLUMN. As Sap hana is an In Memory database all tables are uploaded into memory. Most of them will be of type column. Column tables are smaller than similar row tables in comparison.
Managing sap hana tables requires creating, altering, loading data, dropping, retrieving, inserting, updating and deleting data.
In this section you will discover how to manipulate data from a sap hana table using SQL syntaxes. Like any type of databases, Sap hana allows you to select, update, delete and insert data into tables.
The insert command add data to a sap hana table. The action can be rollback if the commit has not been applied. The command has to be commited in order to apply the new data immediately and permanently.
INSERT into "A_USER"."A_TABLE" values ('A01','TB_NAME01','TB_IDX01',1,'','' );
INSERT into "A_USER"."A_TABLE" values ('A02','TB_NAME02','TB_IDX02',2,'SAP','' );
INSERT into "A_USER"."A_TABLE" values ('A03','TB_NAME03','TB_IDX03',3,'','2017/01/15' );
Insert into GLOBAL temporary table The # determine the local tb
INSERT INTO A_GLOBAL_TEMP_TABLE VALUES (1,'BEN','DUBLIN');
INSERT INTO A_GLOBAL_TEMP_TABLE VALUES (2,'YAN','PARIS');
INSERT INTO A_GLOBAL_TEMP_TABLE VALUES (2,'DANIEL','BRUXELLES');
Insert into LOCAL temporary table The # determine the local table
INSERT INTO #A_LOCAL_TEMP_TABLE VALUES (1,'PAUL','LONDON');
INSERT INTO #A_LOCAL_TEMP_TABLE VALUES (2,'PETER','GUILDFORD');
INSERT INTO #A_LOCAL_TEMP_TABLE VALUES (2,'DANIEL','EXETER');
Insert into table from a data selection
INSERT INTO "A_SCHEMA"."A_REPORT_TABLE"
SELECT A."DATE", A."CLIENT", A."HARWARE", A."SALES_QTY", A."SALES_VALUE", B."CLIENT_TYPE"
FROM "A_SCHEMA"."CLIENT_TABLE" A
LEFT OUTER JOIN
ON (A."CLIENT" = B."CLIENT")
WHERE A."DATE" = '2017/01/15';
The delete command remove data from a sap hana table. The action can be rollback if the commit has not been applied. The command has to be commited in order to apply changes immediately and permanently.
The update command changes data from a sap hana table. The action can be rollback if the commit has not been applied. The command has to be commited in order to apply changes immediately and permanently.
Update table data
SET BTITLE = (SELECT distinct BTITLE FROM B_ARC_BOOK_TB WHERE B.TID = A.TID )