This blog is part of the larger series on all new developer features in SAP HANA SPS 11: SAP HANA SPS 11: New Developer Features

Core data services (CDS) is an infrastructure for defining and consuming semantically rich data models. Using a a data definition language (DDL), a query language (QL), and an expression language (EL), CDS is envisioned to encompass write operations, transaction semantics, constraints, and more.

A first step toward this ultimate vision for CDS was the introduction of the hdbdd development object for SAP HANA native development in SPS 06. This new development object utilized the Data Definition Language of CDS to define tables and structures. It can therefore be consider an alternative to hdbtable and hdbstructure.

In SPS 11 we continue to develop CDS with a focus on expanding bringing this functionality into the HDI (HANA Deployment Infrastructure) as well as rounding out the general functionality based upon high priority customer requested features.

For previous new features in HANA Core Data Services please have a look at this blogs:

HDI – HANA Deployment Infrastructure

HDI (HANA Deployment Infrastructure) is a service layer of the SAP HANA database that simplifies the deployment of HANA database artifacts by providing a declarative approach for defining database objects and ensuring a consistent deployment into the database based on a transactional all-or-nothing deployment model and implicit dependency management. It is intended to improve the overall management of database artifacts, particularly in scenarios where its necessary to deploy multiple versions of the same data model into the same database instance.

There are a few high level points about HDI that are important to understand for the impacts to HANA Core Data Services:

  • Containers:  All development objects within the scope of HDI now must be within an HDI Container.  The HDI Container allows multiple deployments, sandboxing and enhanced security options for all database artifacts.
  • HDI focuses on deployment only:  Unlike the classic HANA Repository, there is no version control or lifecycle management aspects.  These topics are now provided by Git/GitHub.
  • Database Objects only: Unlike the classic HANA Repository, HDI only covers pure database development objects. It has nothing to do with JavaScript, XSODATA, or other application-layer artifacts.

For more details on HDI please see this blog:  SAP HANA SPS 11: New Developer Features; HDI

The reason that we recap the basic concepts of HDI here in the CDS blog is that CDS being a database artifact is directly impacted by the introduction of HDI.  In reality there are now two versions of CDS that are available in each HANA system as of SPS 11 and higher.  Not all features will be delivered to both versions. Some newer features of CDS are only available in the HDI version of CDS. Other features might be delivered to both but via different syntax between Repository CDS and HDI CDS.

CDS HDI Version Incompatible Changes

In general, CDS works essentially the same in HDI like it does in the SAP HANA Repository version. However there are some changes that were necessary to adapt to the new realities of HDI. For instance HDI is completely Schema-Free development and therefore all schema references must be removed from development artifacts.  HDI also changes the role of the package namespace as there are no such thing as global database objects or alias any longer. All access is local to the container, including namespaces. The change over to HDI also provided the opportunity to make some syntax changes in order to improve the usability of the language. For instance annotations are largely abandoned in this new version.  Also the file extension for CDS development objects was changed from HDBDD to HDBCDS.

The following annotations are no longer supported in the HDI version of CDS:

  • @Schema
  • @Catalog (index and tableType)
  • @SearchIndex
  • @SearchIndexes
  • @nokey
  • @GenerateTableType
  • @WithStructuredPrivilegeCheck

For all the above annotations, they have been replaced by regular syntax in the HDI version of CDS and the old syntax will not be allowed. This new syntax variant is also optionally available in the older repository version of CDS. It is recommended in the old repository version that you use this new syntax for all new CDS development objects.

Schema:

The annotation @Schema is no longer supported in HDI as logically no longer makes sense.  HDI based development is “Schema-less”.  The Schema is automatically managed by the HDI container configuration in the manifest/mta descriptor files of the project.

Catalog; Index and Table Type:

The Catalog annotation is not supported in HDI.  In order to specify indexes, use the corresponding syntax in the new Technical Configuration section. The specification of table type is now split. The storage type (row vs. column) is specified in the technical configuration section.  In order to create a temporary table, use the new keyword temporary entity in the base entity definition.

Fulltext and Search:

The annotations @SearchIndex and @SearchIndexes are not supported in HDI.  In order to define fulltext indexes, please use the corresponding syntax in the new Technical Configuration section.

Keyless Entities:

In order to define an entity without key elements; in the old Repository version of CDS you had to use the annotation @nokey.  In HDI, it is possible to define an entity without key elements without any specific specification. It simply is a base supported option. The annotation @nokey is no longer supported within the HDI version of CDS.

Generate Type Type:

In the old Repository version of CDS; for each structured CDS type, a HANA table type was generated by default.  The generation of table types could only be avoided explicitly via the @GenerateTypeType annotation. In the new HDI version of CDS, no table type is generated for a structured type by default. In order to force the generation of a table type, you must use the keyword “table type” instead of type:


type Struc1 { ... }; // no HANA table type is generated
table type Struc2 { ... }; // a corresponding HANA table type is created


Access Control via DCL

The annotation @WithStructuredPrivilegeCheck is no longer supported in the HDI version of CDS.  Please use the following syntax variant instead:


view MyView as select from Foo {
<select_list>
} <where_groupBy_Having_OrderBy> with structured privilege check;


New Features

Technical Configuration

The definition of an entity can now contain a section called technical configuration. In this section you can specify the following

  • storage type (row vs. column)
  • indexes
  • full text indexes
  • partitioning (HDI version only)
  • grouping (HDI version only)
  • unload priority (HDI version only)

The syntax in the technical configuration section is as close as possible to the corresponding DDL syntax in the HANA SQL Create Table statement. Each clause in the technical configuration section must end with a semicolon.

Syntax Structure:


entity MyEntity {
  <element_definitions>
} technical configuration {
  <technical_configurations>
};


Technical Configuration – Row/Column

In the technical configuration for an entity, the storage type (row or column) for the generated table can be specified.  If no storage type is specified the column store type is used by default. In the Repository version, this is an alternative way to specify the storage type verses the annotation @Catalog.tableType. It is not possible to use both the annotation and the technical configuration at the same type to define the storage type for an entity.  In HDI, the storage type can only be specified via the technical configuration. The respective annotation is no longer supported.

Syntax Structure:


entity MyEntity {
  <element_list>
} technical configuration {
  <store_type> store;
};


Example:


entity MyEntity {
  key id : Integer;
  a : Integer;
} technical configuration {
  row store;
};


Technical Configuration – Index

In the Repository version, this is an alternative way to specify the index verses the @index annotation. It is not possible to use both the @index annotation and the technical configuration at the same time to define indexes for an entity.  In HDI, indexes can only be specified via the technical configuration,. The @index annotation is no longer supported.

Example:


entity MyEntity {
  key id : Integer;
  a : Integer;
  b : Integer;
  c : Integer;
  s {
    m : Integer;
    n : Integer;
  };
} technical configuration {
  index MyIndex1 on (a, b) asc;
  unique index MyIndex2 on (c, s) desc;
};


Technical Configuration – FullText Index

The <fulltext_parameter_list> parameter is basically identical to the standard HANA SQL syntax for CREATE FULLTEXT INDEX. A fuzzy search index corresponds to the addition FUZZY SEARCH INDEX ON for a table column. It is not possible to specify both a fulltext index and a fuzzy search index for the same element. In the Repository version this technical configuration sections is an alternative way to specifying fulltext indexes verses the annotations @SearchIndex and @SearchIndexes. It is not possible to use both the annotations and the technical configuration at the same time to define fulltext indexes for entities. In the HDI version, fulltext indexes can only be specified via the technical configuration. The respective annotations are no longer supported. The fulltext parameters CONFIGURATION and TEXT MINING CONFIGURATION are not supported in HDI.

Example:


entity MyEntity {
  key id : Integer;
  t : String(100);
  s {
    u : String(100);
  };
} technical configuration {
  fulltext index MyFTI1 on (t) <fulltext_parameter_list>;
  fuzzy search index on (s.u);
};

Technical Configuration – Partitioning (HDI Only)

In the technical configuration for an entity, the partitioning information for the generated table can be specified now.  The <partition_clause> is identical to the respective clause in the standard HANA SQL CREATE TABLE statement. The partition clause is only supported in HDI.

Example:


entity MyEntity {
  key id : Integer;
  a : Integer;
} technical configuration {
  partition by hash (id) partitions 2,
               range (a) (partition 1 <= values < 10, partition values = 10, partition others);
};

Technical Configuration – Grouping (HDI Only)

In the technical configuration for an entity, the grouping information for the generated table can be specified. The <group_option> is identical to the respective clause in the standard HANA SQL CREATE TABLE statement. The group option is only supported in HDI.

Example:


entity MyEntity {
  key id : Integer;
  a : Integer;
} technical configuration {
  group type Foo group subtype Bar group name Wheeeeezz;
};

Technical Configuration – Unload Priority (HDI Only)

In the technical configuration for an entity, the Unload Priority for the generated table can be specified. The Unload Priority is only supported in HDI.


Example:


entity MyEntity {
  <element_list>
} technical configuration {
  unload priority <integer_literal>;
};

Namespace is Optional (HDI Only)

In HDI, it is possible to define a CDS source file without a namespace.  Just omit the namespace directive. This is because HDI changes the role of the package namespace as there are no such thing as global database objects or alias any longer. All access is local to the container, including namespaces.

Join

CDS now supports the JOIN clause in view definitions. This is true in both the Repository and HDI versions. The following JOIN types are supported:

  • [INNER] JOIN
  • LEFT [OUTER] JOIN
  • RIGHT [OUTER] JOIN
  • FULL [OUTER] JOIN
  • CROSS JOIN

Example:


entity E {
  key id : Integer;
  a : Integer;
};
entity F {
  key id : Integer;
  b : Integer;
};
entity G {
  key id : Integer;
  c : Integer;
};
view V_join as select from E join (F as X full outer join G on X.id = G.id) on E.id = c {
  a, b, c
};

SELECT DISTINCT

CDS now supports SELECT DISTINCT semantic.  Note the position of the DISTINCT keyword directly in front of the curly brace. This feature is supported in both Repository and HDI versions of CDS.

Example:


view V_dist as select from E distinct { a };

Constants in Queries and Associations

Constants could be defined already before SPS 11, but so far their usage was restricted to annotation assignments and default values for entity elements. With SPS 11, constants can also be used in views and in the ON-condition of unmanaged associations. When constants are used in a view definition, their name must be prefixed with the scope operator “:”. Usually names that appear in a query are resolved as alias or element names. The scope operator makes the compiler resolve the name outside of the query.  This feature is supported in both Repository and HDI versions of CDS.

Cardinality in Filters

When an infix filter effectively reduces the cardinality of a to-N association to a to-1, this can be made explicit in the filter. This allows to use the association also in the WHERE clause, where to-N associations are not normally allowed. This feature is supported in both Repository and HDI versions of CDS.


Example:


entity Person {
  key id : Integer;
  name : String(100);
  address : Association[*] to Address on address.personId = id;
};
entity Address {
  key id : Integer;
  personId : Integer;
  type : String(20); // home, business, vacation, ...
  street : String(100);
  city : String(100);
};
view V as select from Person {
  name
} where address[1: type='home' ].city = 'Hamburg';

Calculated Fields

The definition of an entity can now contain calculated fields. The values for calculated fields are not persisted in the database, but only computed on access, i.e. when the element is selected. Basically when the field is used in a select statement, it is replaced by the expression. The calculation expression can contain arbitrary expressions and SQL functions. Please note: in HANA tables, you can define columns with the addition “GENERATED ALWAYS AS.” These columns are physically present in the table and all the values are actually stored. They behave like ordinary columns, with the difference that their value is computed upon insertion rather than specified in the INSERT statement. This is in contrast to the calculated fields: for them, no values are actually stored. The values are computed upon SELECT. This feature is supported in both Repository and HDI versions of CDS.

Restrictions:

  • The definition of a calculated field must not contain other calculated fields, associations, aggregations, or sub queries.
  • A calculated field cannot be a key
  • No index can be defined on a calculated field
  • A calculated field cannot be used as a foreign key for a managed assocation

Empty Structure Types

You can define structured types  that do not contain any elements. Note that no HANA table type can be generated for such a type. Therefore in the Repository version of CDS you must switch off table type generation with the respective annotation before you can use this new feature.  In HDI you must not use the keyword table type.

Key Elements in a View

In a view definition, elements of the select list can now be marked as “key”. In order to be able to sue the keyword “key” for an element of the select list, an explicit alias with keyword “as” must be present. Making a select list element “key” does not have any semantic relevance. It has no influence on the generated SQL view and no checks or constraints are applied. This feature is supported in both Repository and HDI versions of CDS.

Boolean

CDS type BOOLEAN is now fully supported. It can be used in an entity definition.  CDS type Boolean is mapped to the native HANA type BOOLEAN that was introduced in SPS 10.

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. Wolfgang Röckelein

    Will the CDS type BOOLEAN map to Edm.Boolean when a table/view is published with XSODATA?

    Will the key definition of a view carry over to XSODATA or will I still have to declare the key there also?

    (0) 
  2. Abhishek Kumar

    Hi Thomas,

    I am using SAP Web IDE and created .hdbcds file. I created few entities and a view. Could you please tell me how to access these entities and views as this is not schema specific. I am getting invalid table name error if I use the old .hdbdd way. Eg:

    SELECT * FROM “MY_SCHEMA”.”Playground.My::TESTCDS.MyModel.customHier.T_HIERARCHY”;

    The above statement gives error.

    I am using SAP HANA Web Based Development Workbench: Editor v 1.120.20 and this is not doing syntax check also in .hdbcds file. Is it OK?

    Thanks in advance.

    (0) 
    1. Thomas Jung Post author

      >I am using SAP HANA Web Based Development Workbench: Editor v 1.120.20 and this is not doing syntax check also in .hdbcds file. Is it OK?

      HDBCDS is for HDI and the new SAP Web IDE for SAP HANA. If you are still using the old repository tools then you should continue to use the old file extension HDBDD.

      (0) 

Leave a Reply