Schema in SAP HANA – Learn to Create Schema in 6 Easy Steps
Moving on with understanding the concepts and capabilities of SAP HANA, we will learn about the schema in this tutorial. Schemas are important when you work on SAP HANA Studio. We will also discuss the types of schemas in SAP HANA and steps to create a schema in SAP HANA Modeler.
What is a Schema in SAP HANA?
A schema is like a container which contains all the different elements or objects of a relational database. The elements of the system reside in the Catalog node of SAP HANA Information Modeler. Within the Catalog node, the relational SAP HANA database is divided into sub-databases known as schemas. These schemas, acting as logical containers keep all the objects relevant to the database such as tables, views, functions indexes, stored procedures, etc.
In other words, it categorizes all the important information about a database into folders. The schema is created as a transportable design-time file (with extension “.hdbschema”) which is a generally flat file stored in data repositories.
Components of a Schema in SAP HANA
Each schema consists of a fixed set of folders containing relevant information:
- Column views
- EPM Models
- EPM Query Sources
Types of Schemas in SAP HANA
There are three types of schemas in SAP HANA:
1. User Defined Schema
The user-defined schema or custom schemas are created by users such as a HANA DBA, System Administrator or a developer. You can create a user-defined schema using an SQL code.
The syntax for creating such a schema is:
CREATE SCHEMA <schema_name>;
Now, if you wish the objects such as tables or views residing under this schema get consumes in other information views or schemas, then you need to grant access at the time of creating a schema.
The syntax for access granting statement is:
GRANT SELECT ON <schema_name> TO SYS_REPO WITH GRANT OPTION
2. System Defined Schema
The system defined schema contains SAP HANA system information and is created with the SAP HANA database by a system. There are standard system defined schemas such as _SYS_BIC, _SYS_REPO, _SYS_BI, _SYS_STATISTICS, _SYS_XS, etc. Each of this system defined schema contains information relevant to specific system aspects.
- _SYS_BIC: Contains column views of all the activated objects.
- _SYS_REPO: Contains all the objects available in the repository such as activated objects, inactive objects, runtime objects, package details, etc.
- _SYS_BI: Contains the metadata for the column views.
- _SYS_STATISTICS: Contains information regarding system configuration and parameters.
- _SYS_XS: Contains data related to SAP HANA Extended Applications Services.
3. SLT Driven Schema
This schema is created when SLT is configured to carry out replication of tables. It will contain all the tables that replicate into SAP HANA through SLT. Such schemas create on Data Provisioning by SLT process.
How to Create Schema in SAP HANA Modeler
We can create schemas in SAP HANA in two ways:
1. By creating a user which automatically creates a schema for that user.
2. Another method is by creating a customized schema under an existing user without creating a new user.
Below are the steps to create a customized schema:
Step 1: Open the SQL Console in SAP HANA Modeler.
Step 2: After that, the SQL Console is ready for you to enter the SQL statement.
CREATE SCHEMA TEST_SCHEMA OWNED BY TRAINER
Enter the SQL statement to create a schema. In the image shown below, the schema named TEST_SCHEMA will create which a pre-existing user, TRAINER, owns
Step 3: To execute this statement, select the statement written and click on the green execute icon (indicated by the red arrow). You can use keyboard shortcut F8 for this.
A message at the bottom of the console will confirm successful execution.
Step 4: Then, to find the newly created schema, refresh the Catalog.
Step 5: After that, the schema will add.
Also, if you go and check the list of users, no new user is added.
Step 6: Then, you can also drop the table by writing a small code as shown below.
CREATE SCHEMA TEST_SCHEMA OWNED BY TRAINER DROP SCHEMA TEST_SCHEMA
This concludes our lesson on SAP HANA Schema. A schema is an important structural component of SAP HANA database and gives a logical framework to users. In the next segment, we will talk about a component of the schema in SAP HANA; tables.
Are you clear with the concept of Schema in SAP HANA? If you have any queries, just enter in the comment section.