Introduction
When you start learning CAP, running cds add tiny-sample generates all the necessary sample artifacts, including a CSV file inside the db/data folder. Once deployed, the data from this CSV file is loaded as initial data into SAP HANA Cloud.
This mechanism seems convenient for loading initial data into SAP HANA Cloud for productive applications, doesn’t it?
However, there’s a catch. If you change the CSV data or modify the schema, the data in SAP HANA Cloud will be reinitialized with the CSV content — including the data entered by users! Let’s take a look at what the documentation says:
On SAP HANA, only use CSV files for configuration data that can’t be changed by application users. → See CSV data gets overridden in the SAP HANA guide for details.
This is exactly the point I want to highlight in this article. At the same time, I’ve also explored some alternative approaches and validated them.
Case1: New Development
When developing a new CAP application, you have the following options:
Option A: Do not use CSV files to register initial data for tables that will be maintained by the application.Option B: If both initial data and user-generated data need to coexist, define filter conditions in the .hdbtabledata file.
Option A is the simplest, if feasible. However, in some cases — such as for master data — the ability to quickly load initial data from CSV files is attractive. Below, let’s look at Option B in more detail.
Option B: Define filter conditions in the .hdbtabledata
1. Add a column called dataCategory to the CSV file to distinguish between different types of data. (This is just an example; you can use any column as in identifier.)
entity Books {
key ID : Integer;
title : String;
stock : Integer;
dataCategory: String enum {
system = ‘S’;
user = ‘U’;
}
}
2. Run the command cds build –production to generate an .hdbtabledata file in the gen/db/src/data folder. Copy this file to the db/src/static/csv folder, and then add an include_filter as shown below. This ensures that the HDI container takes ownership only of the records specified by the filter. (Note: The values must be strings, even if the column’s data type is numeric.)
{
“format_version”: 1,
“imports”: [
{
…,
“import_settings”: {
“import_columns”: [
“ID”,
“TITLE”,
“STOCK”,
“DATACATEGORY”
],
“include_filter”: [
{ “DATACATEGORY” : “S” }
]
},
“column_mappings”: {
“ID”: “ID”,
“TITLE”: “title”,
“STOCK”: “stock”,
“DATACATEGORY”: “dataCategory”
}
}
]
}
3. Move the CSV file into the db/src/static/csv folder as well. Don’t forget to set the dataCategory to “S”.
ID,title,stock,dataCategory
1,Wuthering Heights,100,S
2,Jane Eyre,500,S
4. After the initial deployment, users update some of the records and insert one new entry, resulting in the following state (as shown in the result of a GET request):
{
“@odata.context”: “$metadata#Books”,
“value”: [
{ “ID”: 1, “dataCategory”: “S”, “stock”: 100, “title”: “Updated” },
{ “ID”: 2, “dataCategory”: “S”, “stock”: 500, “title”: “Jane Eyre” },
{ “ID”: 3, “dataCategory”: “U”, “stock”: 10, “title”: “New Book” }
]
}
5. When schema changes or updates to the initial data are applied, records with dataCategory = “S” are overwritten, while records with dataCategory = “U” remain untouched. For example, after adding a new column newField:
{
“@odata.context”: “$metadata#Books”,
“value”: [
{ “ID”: 1, “dataCategory”: “S”, “newField”: null, “stock”: 100, “title”: “Wuthering Heights” },
{ “ID”: 2, “dataCategory”: “S”, “newField”: null, “stock”: 500, “title”: “Jane Eyre” },
{ “ID”: 3, “dataCategory”: “U”, “newField”: null, “stock”: 10, “title”: “New Book” }
]
}
Case 2: Accidentally Mixed Data
In some cases, custom data and pre-delivered data (from a CSV file) may accidentally end up in the same table governed by an HDI Table Data artifact (.hdbtabledata) without filter conditions to separate them.
SAP Note 2922271 outlines how to handle this situation. Below, I’ll walk you through the steps.
Note: The procedure described here applies to single-tenant applications. For multitenant scenarios, please refer to the documentation, as well as @Sekar_Somasundaram‘s detailed blog post.
Steps at a Glance
Delete the CSV file.Make the .hdbtabledata artifact subject to undeploy.Ensure that existing data is preserved during undeploy.
Current State of the Data
The CSV file initially contains the following data:
ID,title,stock
1,Wuthering Heights,100
2,Jane Eyre,500
After the initial deployment, users update some of the records and insert one new entry, resulting in the following state:
{
“@odata.context”: “$metadata#Books”,
“value”: [
{ “ID”: 1, “stock”: 100, “title”: “Updated” },
{ “ID”: 2, “stock”: 500, “title”: “Jane Eyre” },
{ “ID”: 3, “stock”: 10, “title”: “New Book” }
]
}
Step-by-Step: Deleting the CSV File After Initial Deployment
1. Delete the CSV file.
2. Add “src/gen/**/*.hdbtabledata” to db/undeploy.json as shown below:
[
“src/gen/**/*.hdbview”,
“src/gen/**/*.hdbindex”,
“src/gen/**/*.hdbconstraint”,
“src/gen/**/*_drafts.hdbtable”,
“src/gen/**/*.hdbcalculationview”,
“src/gen/**/*.hdbtabledata”
]
3. Copy gen/db/package.json to the db folder, and add the following parameters to the start script.
–undeploy src/<mytable>.hdbtabledata –path-parameter src/<mytable>.hdbtabledata:skip_data_deletion=true
In my example, the package.json looks as follows:
{
“name”: “deploy”,
“dependencies”: {
“hdb”: “^0”,
“@sap/hdi-deploy”: “^5”
},
“engines”: {
“node”: “>=18”
},
“scripts”: {
“start”: “node node_modules/@sap/hdi-deploy/deploy.js –use-hdb –parameter com.sap.hana.di.table/try_fast_table_migration=true –undeploy src/gen/data/my.bookshop-Books.hdbtabledata –path-parameter src/gen/data/my.bookshop-Books.hdbtabledata:skip_data_deletion=true”
}
}
4. Deploy the project. In the db-deployer log, you will see a message indicating that the existing data was not deleted.
[APP/TASK/deploy/0] OUT The “skip_data_deletion” make parameter is set; no data will be deleted from the target table “MY_BOOKSHOP_BOOKS”
As a result, the updated user data remains intact.
{
“@odata.context”: “$metadata#Books”,
“value”: [
{ “ID”: 1, “stock”: 100, “title”: “Updated” },
{ “ID”: 2, “stock”: 500, “title”: “Jane Eyre” },
{ “ID”: 3, “stock”: 10, “title”: “New Book” }
]
}
5. Before the next deployment, delete the db/package.json file to restore the original state. From this point on, even if the schema changes, the data will no longer be overridden.
Other Ways to Register Data
Besides using CSV files during deployment, you can load data through the following approaches as well:
Insert data via HTTP requests: Insert data after deployment using HTTP requests.Upload CSV files through the Database Explorer: Upload CSV files directly from the Database Explorer.For local testing: Place CSV files in the test/data folder instead of db/data (see: Location of CSV Files | CAPire).
Closing
A clear strategy on how to provide initial data must be defined upfront. CSV files are convenient but must be used with caution, particularly in productive environments. Applying best practices such as separating system and user data, leveraging .hdbtabledata filters, or considering alternative loading options will help maintain data integrity and protect user input across deployments.
References
CSV Data Gets Overridden (capire) I removed sample data (.csv file) from my project. Still, the data is deployed and overwrites existing data. (capire)
IntroductionWhen you start learning CAP, running cds add tiny-sample generates all the necessary sample artifacts, including a CSV file inside the db/data folder. Once deployed, the data from this CSV file is loaded as initial data into SAP HANA Cloud.This mechanism seems convenient for loading initial data into SAP HANA Cloud for productive applications, doesn’t it?However, there’s a catch. If you change the CSV data or modify the schema, the data in SAP HANA Cloud will be reinitialized with the CSV content — including the data entered by users! Let’s take a look at what the documentation says:On SAP HANA, only use CSV files for configuration data that can’t be changed by application users. → See CSV data gets overridden in the SAP HANA guide for details.This is exactly the point I want to highlight in this article. At the same time, I’ve also explored some alternative approaches and validated them. Case1: New DevelopmentWhen developing a new CAP application, you have the following options:Option A: Do not use CSV files to register initial data for tables that will be maintained by the application.Option B: If both initial data and user-generated data need to coexist, define filter conditions in the .hdbtabledata file.Option A is the simplest, if feasible. However, in some cases — such as for master data — the ability to quickly load initial data from CSV files is attractive. Below, let’s look at Option B in more detail. Option B: Define filter conditions in the .hdbtabledata1. Add a column called dataCategory to the CSV file to distinguish between different types of data. (This is just an example; you can use any column as in identifier.) entity Books {
key ID : Integer;
title : String;
stock : Integer;
dataCategory: String enum {
system = ‘S’;
user = ‘U’;
}
}2. Run the command cds build –production to generate an .hdbtabledata file in the gen/db/src/data folder. Copy this file to the db/src/static/csv folder, and then add an include_filter as shown below. This ensures that the HDI container takes ownership only of the records specified by the filter. (Note: The values must be strings, even if the column’s data type is numeric.){
“format_version”: 1,
“imports”: [
{
…,
“import_settings”: {
“import_columns”: [
“ID”,
“TITLE”,
“STOCK”,
“DATACATEGORY”
],
“include_filter”: [
{ “DATACATEGORY” : “S” }
]
},
“column_mappings”: {
“ID”: “ID”,
“TITLE”: “title”,
“STOCK”: “stock”,
“DATACATEGORY”: “dataCategory”
}
}
]
}3. Move the CSV file into the db/src/static/csv folder as well. Don’t forget to set the dataCategory to “S”.ID,title,stock,dataCategory
1,Wuthering Heights,100,S
2,Jane Eyre,500,S4. After the initial deployment, users update some of the records and insert one new entry, resulting in the following state (as shown in the result of a GET request):{
“@odata.context”: “$metadata#Books”,
“value”: [
{ “ID”: 1, “dataCategory”: “S”, “stock”: 100, “title”: “Updated” },
{ “ID”: 2, “dataCategory”: “S”, “stock”: 500, “title”: “Jane Eyre” },
{ “ID”: 3, “dataCategory”: “U”, “stock”: 10, “title”: “New Book” }
]
}5. When schema changes or updates to the initial data are applied, records with dataCategory = “S” are overwritten, while records with dataCategory = “U” remain untouched. For example, after adding a new column newField:{
“@odata.context”: “$metadata#Books”,
“value”: [
{ “ID”: 1, “dataCategory”: “S”, “newField”: null, “stock”: 100, “title”: “Wuthering Heights” },
{ “ID”: 2, “dataCategory”: “S”, “newField”: null, “stock”: 500, “title”: “Jane Eyre” },
{ “ID”: 3, “dataCategory”: “U”, “newField”: null, “stock”: 10, “title”: “New Book” }
]
} Case 2: Accidentally Mixed DataIn some cases, custom data and pre-delivered data (from a CSV file) may accidentally end up in the same table governed by an HDI Table Data artifact (.hdbtabledata) without filter conditions to separate them. SAP Note 2922271 outlines how to handle this situation. Below, I’ll walk you through the steps.Note: The procedure described here applies to single-tenant applications. For multitenant scenarios, please refer to the documentation, as well as @Sekar_Somasundaram’s detailed blog post. Steps at a GlanceDelete the CSV file.Make the .hdbtabledata artifact subject to undeploy.Ensure that existing data is preserved during undeploy. Current State of the DataThe CSV file initially contains the following data:ID,title,stock
1,Wuthering Heights,100
2,Jane Eyre,500After the initial deployment, users update some of the records and insert one new entry, resulting in the following state:{
“@odata.context”: “$metadata#Books”,
“value”: [
{ “ID”: 1, “stock”: 100, “title”: “Updated” },
{ “ID”: 2, “stock”: 500, “title”: “Jane Eyre” },
{ “ID”: 3, “stock”: 10, “title”: “New Book” }
]
} Step-by-Step: Deleting the CSV File After Initial Deployment1. Delete the CSV file.2. Add “src/gen/**/*.hdbtabledata” to db/undeploy.json as shown below:[
“src/gen/**/*.hdbview”,
“src/gen/**/*.hdbindex”,
“src/gen/**/*.hdbconstraint”,
“src/gen/**/*_drafts.hdbtable”,
“src/gen/**/*.hdbcalculationview”,
“src/gen/**/*.hdbtabledata”
]3. Copy gen/db/package.json to the db folder, and add the following parameters to the start script. –undeploy src/<mytable>.hdbtabledata –path-parameter src/<mytable>.hdbtabledata:skip_data_deletion=trueIn my example, the package.json looks as follows:{
“name”: “deploy”,
“dependencies”: {
“hdb”: “^0”,
“@sap/hdi-deploy”: “^5”
},
“engines”: {
“node”: “>=18”
},
“scripts”: {
“start”: “node node_modules/@sap/hdi-deploy/deploy.js –use-hdb –parameter com.sap.hana.di.table/try_fast_table_migration=true –undeploy src/gen/data/my.bookshop-Books.hdbtabledata –path-parameter src/gen/data/my.bookshop-Books.hdbtabledata:skip_data_deletion=true”
}
}4. Deploy the project. In the db-deployer log, you will see a message indicating that the existing data was not deleted.[APP/TASK/deploy/0] OUT The “skip_data_deletion” make parameter is set; no data will be deleted from the target table “MY_BOOKSHOP_BOOKS”As a result, the updated user data remains intact.{
“@odata.context”: “$metadata#Books”,
“value”: [
{ “ID”: 1, “stock”: 100, “title”: “Updated” },
{ “ID”: 2, “stock”: 500, “title”: “Jane Eyre” },
{ “ID”: 3, “stock”: 10, “title”: “New Book” }
]
}5. Before the next deployment, delete the db/package.json file to restore the original state. From this point on, even if the schema changes, the data will no longer be overridden. Other Ways to Register DataBesides using CSV files during deployment, you can load data through the following approaches as well:Insert data via HTTP requests: Insert data after deployment using HTTP requests.Upload CSV files through the Database Explorer: Upload CSV files directly from the Database Explorer.For local testing: Place CSV files in the test/data folder instead of db/data (see: Location of CSV Files | CAPire). ClosingA clear strategy on how to provide initial data must be defined upfront. CSV files are convenient but must be used with caution, particularly in productive environments. Applying best practices such as separating system and user data, leveraging .hdbtabledata filters, or considering alternative loading options will help maintain data integrity and protect user input across deployments. ReferencesCSV Data Gets Overridden (capire) I removed sample data (.csv file) from my project. Still, the data is deployed and overwrites existing data. (capire) Read More Technology Blog Posts by Members articles
#SAP
#SAPTechnologyblog
