Technical Articles
Handling Temporal Data in SAP Cloud Application Programming Model with Hana.
In this post I will explain how to handle temporal data in SAP Cloud Application Programming model with HANA native artifacts.
Temporal data is time dependent. We can say if there is any timestamp associated with any data set it can be called as temporal. SAP Cloud Application Programming model provides, out of the box support to for temporal entities.
Two ways we can declare entities as temporal.
1. With annotations @cds.valid.from/to
We can just add these annotations to the date fields to in an entity where temporal data needs to be handled.
entity Product {
validFrom : Date @cds.valid.from;
validTo : Date @cds.valid.to;
}
2. Declare an entity using aspect temporal
Using this predefined aspect temporal we can record changes in the database with valid from/to boundaries. Deploying this cds artefacts in the database will create validFrom and validTo timestamp fields in the respective tables.
using { temporal } from '@sap/cds/common';
entity Product : temporal {
key productId : String(20);
productName : String(20);
productCategory : String(20);
}
Read requests on these entities will return valid data as of now without specifying any parameter.
With this approach we can see time slices are recorded in a single table. Many change requests with time, data size in single table will keep on increasing and cause performance issues.
We can consider the alternate approach to achieve this is by HANA System Versioned tables.=
System versioned tables with SAP Cloud Application Programming model :
System versioned tables support the tracking of changes on column store tables by capturing the validity period of each record.
System-versioned tables always consist of two physical tables:
- The main table of records that are currently valid.
- A corresponding history table (a one-to-one correspondence) of archived records. A naming convention is not required but may be helpful, for example, to append “_history” to the name of the main table.
The valid_from and valid_to columns are timestamps and are maintained by the system.
Below are the steps to achieve this.
Step 1:
Create a devspace in Business Application Studio selecting full stack cloud project including SAP HANA tools extension. Start the devspace.
Step 2:
Open terminal in the development space. Create an application by running the below command in the directory : /home/user/projects
mvn -B archetype:generate -DarchetypeArtifactId=cds-services-archetype -DarchetypeGroupId=com.sap.cds \
-DarchetypeVersion=RELEASE \
-DgroupId=com.sap.cap -DartifactId=temporal-cap-service -Dpackage=com.sap.cap
It will download required maven libraries and create a project. Open created project in explorer.
File-> Open Workspace
Step 3:
Create the file schema.cds under db folder. Define two entities Product and Product_hist giving required field data.
Product entity is for original data where Product_hist is to capture the data which has been modified.
We can mark the fields validFrom and validTo with the annotations – @cds.api.ignore and @assert.notNull in order to make sure these fields are not appeared in the metadata of entity’s odata and we don’t need to pass these fields in the payload while creating.
namespace rdp;
entity Product {
key productId : String(20);
productName : String(20);
productCategory : String(20);
productGroupId : String(20);
@cds.api.ignore
@assert.notNull: false
validFrom : Timestamp not null;
@cds.api.ignore
@assert.notNull: false
validTo : Timestamp not null;
}
entity Product_History {
productId : String(20);
productName : String(20);
productCategory : String(20);
productGroupId : String(20);
validFrom : Timestamp;
validTo : Timestamp;
}
Step 4:
Create a HANA artifact for systemversioning.
ProductTableVersioning.hdbsystemversioning
SYSTEM VERSIONING "RDP_PRODUCT"("VALIDFROM", "VALIDTO") HISTORY TABLE "RDP_PRODUCT_HISTORY" NOT VALIDATED
The history entity has essentially the same structure as the main entity but must also meet a number of consistency requirements, specifically: the history entity does not have the key.
We need to make sure that in System Versioned HANA artifact file, the database tables and the columns we refer are in Uppercase.
Step 5:
Create corresponding service for the entity.
using rdp from '../db/schema';
service TemporalCapService {
entity Product as projection on rdp.Product;
}
Deploy the code using MTA configurations giving HANA service instance details.
Once the code is deployed we could see that the tables are created in HANA DB. And we can have OData services to perform CRUD operations as well.
CRUD Operations on temporal entities:
Create Request: POST
https://www.example.com/v4/TemporalCapService/Product
Request Payload:
{"productId":"test1","productName":"testName1","productCategory":"testCat1","productGroupId":"123"}
Object is created with ValidFrom and ValidTo columns populated time details.
Update Request :PATCH
https://www.example.com/v4/TemporalCapService/Product(‘test1’)
Request Payload:
{"productName":"testName123","productCategory":"testCat","productGroupId":"123"}
We can see that, RDP_PRODUCT table is updated with new productname, where as RDP_PRODUCT_HISTORY has a record which was changed in the original table.
Delete Request:
Deletes the data from DB but the history table will get updated with the data present in the original table.
https://www.example.com/v4/TemporalCapService/Product(‘test1’)
This is all from my side. I hope this article is useful. Thank you.
Hi Naveen,
thanks for your great blog post!
Do you know any recommended way to read historical data via an OData service when using system versioned tables?
For example, i'd like to read the data which was valid at "2021-05-31 09:30:00"
Thanks in advance and best regards!
Nicola
Thank you for the create blog. With the later versions of CAP ~5.30 this does not work anymore if draft mode is enabled. Saving a draft will result in a insert statement trying to insert null into VALID_FROM & TO.
Workaround for me is this:
Hi Tobias, since you said you worked on entity with draft, would like to know if you have answer for the question below?
When you post the request, without any handler logic, the records will be posted to the draft tables instead of active tables? Is there a way to post data to the active table directly from POSTMAN?
https://cap.cloud.sap/docs/java/fiori-drafts It looks possible, but need some work.
Thank you.
Hi,
never done it, and currently i am working with the nodejs version.
I heard it is very well possible with java and not so much in nodejs from a different project in my company.
Might have changed by now.
Sorry for letting you down.
Cheers.
If you explicitly exclude the fields from the projection, you don't need handlers.
Dear Author,
I have done exactly the same way as you have described in the blog. However, whenever I try to insert the data into DB directly or using the app, we are getting a repetitive error - Cannot insert/update/upsert into generated field.
Do you know what could be the issue?
Note : We are using VALIDFROM and VALIDTO as mentioned with default values of infinity timestamp, so that existing records in the table are also updated with these default values.
Thanks,
Anshuman