Note - this and other development topics have been discussed in workshop series (September 28-30,2021) on Collaborative database development in SAP HANA Cloud, SAP HANA database.
In this blog, I would like to share my experience with using migration table CDS artefact to facilitate efficient database schema lifecycle.
Concept of schema evolution
In the context of this article, I use the term schema to denote the data model – both conceptual (aka logical) and physical. In the conceptual manifestation, the schema is the definition of a data model in an abstract manner. It focuses on data types, structures, and relationships – preferably in database agnostic manner. We will be relying on conceptual schema during design time – you guessed it – to build design-time representations of database objects.
Physical manifestation is a result of deployment of the conceptual one into a database. We often refer to resulting objects as runtime representations.
In practice, conceptual data model may exist on paper or in somebody’s mind. In such case, implementation of such model as runtime would mean manual execution of SQL Data Definition Language (DDL) statements directly on the database. While this may work for ad-hoc schemas, which do not require much in the way of lifecycle management, go for it.
But if you are serious about developing database models, you will want to do things differently – you will want to maintain design-time definition and track its changes (ideally through software change management tool like Git), so deployment into runtime can be automated and ensure proper transitioning from original to new state.
And, equally importantly, you will want the transition to be as painless as possible. And this is where introduction of Migration Tables is of paramount importance.
Introduction of Migration Tables
Migration tables are available with HANA Development Infrastructure (HDI) on both SAP HANA Platform (on-premise, as of 2.0 SPS04) and SAP HANA Cloud, SAP HANA Database (also applies to SAP HANA Cloud database embedded in SAP Data Warehouse Cloud):
The migration-table plugin transforms a design-time table resource into a table database object.
In contrast to the table plug-in (.hdbtable), the migration-table plug-in (.hdbmigrationtable) uses explicit versioning and migration tasks, which means that the modifications of the database table are explicitly specified in the design-time file and carried out on the database table exactly as specified, without incurring the cost of an internal table-copy operation such as the one performed by the .hdbtable plug-in. This behaviour makes the .hdbmigrationtable plug-in especially useful for tables that contain a lot of data. When a new version of an already existing table is deployed, the plug-in performs the migration statements that are missing for the new version.
This extract from documentation really says it all, so let’s check what it looks like in practice.
Nota bene – throughout remainder of the blog, I will use links to documentation for SAP HANA Cloud, but remember that equivalent capability is available on SAP HANA Platform as well).
Important disclaimer:
The .hdbmigrationtable artefact - as described in the documentation in the links above - has been available and generally supported for a while. Integration with Cloud Application Programming model and Core Data Services - as described in my scenario and shown in the demo - is not yet publicly released for productive usage. It is planned for release in Q4'2021 (and will be documented accordingly on https://cap.cloud.sap).
Sample scenario
I am using Cloud Application Programming (CAP) model with SAP Business Application Studio. For schema definition I use Core Data Services (CDS).
As per disclaimer above, integration of CAP with migration table is not yet released for productive use. However, use of .hdbmigrationtable is generally available. Without integration with CAP, you can still maintain the .hdbmigrationtable artefact manually (rather than having CAP integration do it for you).
Let’s consider a scenario where we need to update table structure due to changed requirements. Type of change may be very diverse – from simple change in column length to more complex change of column name. Well, the latter is complex when using .hdbtable artefacts, but not so when using .hdbmigrationtable.
I will use simple schema with 2 tables. I will duplicate that schema (using different object names) so I can show the difference in behaviour when deploying .hdbtable and .hdbmigrationtable artefacts to SAP HANA (Cloud).
I am using subset of objects I used in the demo and blog on BTP software lifecycle:
| Schema object | Description | Name for .hdbmigrationtable artefact | Name for .hdbtable artefact |
| STATIONS | Master data table with details of petrol stations | CSM_EVOLUTION_STATIONS | CSM_EVOLUTION_STATIONS_TAB |
| PRICES | Prices for various fuel types across stations and timeline | CSM_EVOLUTION_PRICES | CSM_EVOLUTION_PRICES_TAB |
Definition of corresponding objects is identical across the lifecycle – I will apply same changes to show differences in deployment behaviour. To illustrate impact on runtime of the deployment step using .hdbtable and .hdbmigrationtable, I have populated the PRICES tables with ca. 90 million records.
I started with following structure (file db/schema_evolution.cds😞
context csm.evolution {
type UUID : String(50);
@cds.persistence.journal
Entity ![STATIONS] {
key ![UUID]: UUID @title: 'UUID' ;
![NAME]: String(1000) @title: 'NAME' ;
![BRAND]: String(1000) @title: 'BRAND' ;
![CITY]: String(34) @title: 'CITY' ;
};
@cds.persistence.journal
Entity ![PRICES] {
key ![DATE_TIME]: Timestamp @title: 'DATE_TIME' ;
key ![STATION_UUID]: UUID @title: 'STATION_UUID' ;
![DIESEL]: Double @title: 'DIESEL' ;
![E5]: Double @title: 'E5' ;
![E10]: Double @title: 'E10' ;
};
Entity ![STATIONS_TAB] {
key ![UUID]: UUID @title: 'UUID' ;
![NAME]: String(1000) @title: 'NAME' ;
![BRAND]: String(1000) @title: 'BRAND' ;
![CITY]: String(34) @title: 'CITY' ;
};
Entity ![PRICES_TAB] {
key ![DATE_TIME]: Timestamp @title: 'DATE_TIME' ;
key ![STATION_UUID]: UUID @title: 'STATION_UUID' ;
![DIESEL]: Double @title: 'DIESEL' ;
![E5]: Double @title: 'E5' ;
![E10]: Double @title: 'E10' ;
};
}
In the first section, I define the PRICES and STATIONS tables for which I want to generate .hdbmigration artefact – therefore I use the following annotation:
@cds.persistence.journal
As per CAP changelog (Feb’21):
Model entities annotated with @cds.persistence.journal will be deployed as hdbmigrationtable artifacts instead of hdbtable.
For tables PRICES_TAB and STATIONS_TAB I do not use any annotations, thus by default they will be deployed as .hdbtable artefacts.
After deploying the initial schema to my SAP HANA Cloud tenant, I will then perform couple of changes to illustrate the process:
- change length of a field,
- change name of a field.
Through these changes I will show the benefits of hdbmigrationtable and how it supports the schema evolution.
I will also show transition from .hdbtable to .hdbmigrationtable design-time artefacts for an existing project and artefact.
Some mechanics of the build and deployment using .hdbmigrationtable
Let’s have a look at the package.json content in my CAP project – just a subset related to CDS build and deployment:
"cds": {
"requires": {
"db": {
"kind": "hana"
}
},
"hana": {
"deploy-format": "hdbtable",
"journal": {
"enable-drop": false,
"change-mode": "alter"
}
}
}
Please note that since the CAP/CDS integration with .hdbmgirationtable was still in beta during creation of this blog, respective notations as shown in the section "journal" may have changed upon final release. Make sure you check relevant documentation before using productively.
As mentioned earlier the default deploy-format is set to .hdbtable. At present, this cannot be set to .hdbmigrationtable as default, therefore you selectively choose which CDS artefacts will be deployed as .hdbmigrationtable using the annotation @cds.persistence.journal .
Then we have “journal” section, where you can specify additional options controlling generation of the .hdbmigrationtable artefacts:
- The "enable-drop" option determines whether incompatible model changes are rendered as is (true) or manual resolution is required (false). The default value is false.
- The change-mode option determines whether ALTER TABLE ... ALTER ("alter") or ALTER TABLE ... DROP ("drop") statements are rendered for data type related changes. To ensure that any kind of model change can be successfully deployed to the database, you can switch the "change-mode" to "drop", keeping in mind that any existing data will be deleted for the corresponding column. The default value is "alter".
Next important piece of information is about how we track the delta between previous and the new schema state. This needs to be done on two levels:
- Design-time – within the CAP project, there must be a mechanism which tracks changes between CDS build events.
- Run-time – within the SAP HANA where the artefacts are deployed, there must be a mechanism to compare previously deployed version with the new target state.
Design-time change tracking
CDS build activity generates .hdbmigrationtable source files for annotated entities as well as a last-dev/csn.json source file representing the CDS model state of the last build.
It is important to remember to include both these files in your source code management (like Git repository in my example) as otherwise you would lose continuity of changes (and end up with missing link(s) in the schema evolution chain).
When model changes are made in the schema definition file, respective migration versions are generated including the required schema update statements to accomplish the new target state.
Run-time change tracking
Information about deployed state of a table is maintained in control table maintained by associated HDI plugin – as we can see in db/src/.hdiconfig:
"hdbtable": {
"plugin_name": "com.sap.hana.di.table"
},
The control table of interest is com.sap.hana.di.table#VERSIONS with just two fields – TABLE_NAME and VERSION. During deployment, information about last deployed version of the artefact is checked and compared against version submitted for deployment – if the last deployed version is N and submitted is N+2 for example, then migration steps associated with versions N+1 and N+2 are executed during deployment.
As a result of these tracking mechanisms, with .hdbmigrationtable we are able to provide migration steps from any version to any subsequent version as illustrated below:
Benefits include:
- Full traceability of changes in both design-time and run-time artefacts.
- Ability to influence transition approach (migration steps) between versions.
- Clear transition path from any version to any (higher) version.
- Table changes in-place - without a need to use temporary tables and copy data. This saves deployment time and database resources.
If I captured your interest, please read on - you will find detailed recording and walkthrough of the sample scenario.
Practical walkthrough - video
Further below you will find the write-up with selected inputs and outputs of the end-to-end demonstration.
However, if you prefer to watch it in action, please check the video:
Table lifecycle with .hdbmigrationtable artefacts - in pictures
Practical walkthrough - write-up
I set up my environment as per Set Up SAP HANA Cloud and CAP Project tutorial.
Initial state
As mentioned earlier, our starting point is CDS schema definition as follows:
context csm.evolution {
type UUID : String(50);
@cds.persistence.journal
Entity ![STATIONS] {
key ![UUID]: UUID @title: 'UUID' ;
![NAME]: String(1000) @title: 'NAME' ;
![BRAND]: String(1000) @title: 'BRAND' ;
![CITY]: String(34) @title: 'CITY' ;
};
@cds.persistence.journal
Entity ![PRICES] {
key ![DATE_TIME]: Timestamp @title: 'DATE_TIME' ;
key ![STATION_UUID]: UUID @title: 'STATION_UUID' ;
![DIESEL]: Double @title: 'DIESEL' ;
![E5]: Double @title: 'E5' ;
![E10]: Double @title: 'E10' ;
};
Entity ![STATIONS_TAB] {
key ![UUID]: UUID @title: 'UUID' ;
![NAME]: String(1000) @title: 'NAME' ;
![BRAND]: String(1000) @title: 'BRAND' ;
![CITY]: String(34) @title: 'CITY' ;
};
Entity ![PRICES_TAB] {
key ![DATE_TIME]: Timestamp @title: 'DATE_TIME' ;
key ![STATION_UUID]: UUID @title: 'STATION_UUID' ;
![DIESEL]: Double @title: 'DIESEL' ;
![E5]: Double @title: 'E5' ;
![E10]: Double @title: 'E10' ;
};
}
Content of the CAP project at this stage:
There are no .hdbtable or .hdbmigrationtable artefacts yet.
Initial build and deployment
I perform first Core Data Services build int he CAP project with:
user: schema_evolution $ cds build
Following artefacts are generated in my project:
- db/src/csm.evolution.PRICES.hdbmigrationtable
== version=1
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_PRICES (
DATE_TIME TIMESTAMP NOT NULL,
STATION_UUID NVARCHAR(50) NOT NULL,
DIESEL DOUBLE,
E5 DOUBLE,
E10 DOUBLE,
PRIMARY KEY(DATE_TIME, STATION_UUID)
)
- db/src/csm.evolution.PRICES_TAB.hdbtable
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_PRICES_TAB (
DATE_TIME TIMESTAMP NOT NULL,
STATION_UUID NVARCHAR(50) NOT NULL,
DIESEL DOUBLE,
E5 DOUBLE,
E10 DOUBLE,
PRIMARY KEY(DATE_TIME, STATION_UUID)
)
- db/src/csm.evolution.STATIONS.hdbmigrationtable
== version=1
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_STATIONS (
UUID NVARCHAR(50) NOT NULL,
NAME NVARCHAR(1000),
BRAND NVARCHAR(1000),
CITY NVARCHAR(34),
PRIMARY KEY(UUID)
)
- db/src/csm.evolution.STATIONS_TAB.hdbtable
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_STATIONS_TAB (
UUID NVARCHAR(50) NOT NULL,
NAME NVARCHAR(1000),
BRAND NVARCHAR(1000),
CITY NVARCHAR(34),
PRIMARY KEY(UUID)
)
- db/last-dev/csn.json
{
"definitions": {
"csm.evolution.UUID": {
"kind": "type",
"type": "cds.String",
"length": 50,
"@cds.persistence.name": "CSM_EVOLUTION_UUID"
},
"csm.evolution.STATIONS": {
"kind": "entity",
"@cds.persistence.journal": true,
"elements": {
"UUID": {
"@title": "UUID",
"key": true,
"type": "cds.String",
"length": 50,
"@cds.persistence.name": "UUID"
},
"NAME": {
"@title": "NAME",
"type": "cds.String",
"length": 1000,
"@cds.persistence.name": "NAME"
},
"BRAND": {
"@title": "BRAND",
"type": "cds.String",
"length": 1000,
"@cds.persistence.name": "BRAND"
},
"CITY": {
"@title": "CITY",
"type": "cds.String",
"length": 34,
"@cds.persistence.name": "CITY"
}
},
"@cds.persistence.name": "CSM_EVOLUTION_STATIONS"
},
"csm.evolution.PRICES": {
"kind": "entity",
"@cds.persistence.journal": true,
"elements": {
"DATE_TIME": {
"@title": "DATE_TIME",
"key": true,
"type": "cds.UTCTimestamp",
"@cds.persistence.name": "DATE_TIME"
},
"STATION_UUID": {
"@title": "STATION_UUID",
"key": true,
"type": "cds.String",
"length": 50,
"@cds.persistence.name": "STATION_UUID"
},
"DIESEL": {
"@title": "DIESEL",
"type": "cds.Double",
"@cds.persistence.name": "DIESEL"
},
"E5": {
"@title": "E5",
"type": "cds.Double",
"@cds.persistence.name": "E5"
},
"E10": {
"@title": "E10",
"type": "cds.Double",
"@cds.persistence.name": "E10"
}
},
"@cds.persistence.name": "CSM_EVOLUTION_PRICES"
},
"csm.evolution.STATIONS_TAB": {
"kind": "entity",
"elements": {
"UUID": {
"@title": "UUID",
"key": true,
"type": "cds.String",
"length": 50,
"@cds.persistence.name": "UUID"
},
"NAME": {
"@title": "NAME",
"type": "cds.String",
"length": 1000,
"@cds.persistence.name": "NAME"
},
"BRAND": {
"@title": "BRAND",
"type": "cds.String",
"length": 1000,
"@cds.persistence.name": "BRAND"
},
"CITY": {
"@title": "CITY",
"type": "cds.String",
"length": 34,
"@cds.persistence.name": "CITY"
}
},
"@cds.persistence.name": "CSM_EVOLUTION_STATIONS_TAB"
},
"csm.evolution.PRICES_TAB": {
"kind": "entity",
"elements": {
"DATE_TIME": {
"@title": "DATE_TIME",
"key": true,
"type": "cds.UTCTimestamp",
"@cds.persistence.name": "DATE_TIME"
},
"STATION_UUID": {
"@title": "STATION_UUID",
"key": true,
"type": "cds.String",
"length": 50,
"@cds.persistence.name": "STATION_UUID"
},
"DIESEL": {
"@title": "DIESEL",
"type": "cds.Double",
"@cds.persistence.name": "DIESEL"
},
"E5": {
"@title": "E5",
"type": "cds.Double",
"@cds.persistence.name": "E5"
},
"E10": {
"@title": "E10",
"type": "cds.Double",
"@cds.persistence.name": "E10"
}
},
"@cds.persistence.name": "CSM_EVOLUTION_PRICES_TAB"
}
},
"meta": {
"creator": "CDS Compiler v2.5.2"
},
"$version": "2.0"
}
Deploy to previously created (and bound) HDI container from SAP Business Application Studio:
The deployment log:
> Executing task: echo 'Prepared environment for deployment' && (npm ls --depth 0 --production || npm install) && (npm start -- --exit) <
Prepared environment for deployment
deploy@ /home/user/projects/schema_evolution/db
`-- /hdi-deploy@4.1.0
npm WARN lifecycle The node binary used for scripts is /extbin/bin/node but npm is using /opt/nodejs/node-v14.16.0-linux-x64/bin/node itself. Use the `--scripts-prepend-node-path` option to include the path for the node binary npm was executed with.
> deploy@ start /home/user/projects/schema_evolution/db
> node node_modules/@sap/hdi-deploy/deploy.js --auto-undeploy "--exit"
@sap/hdi-deploy, version 4.1.0 (mode default), server version 4.00.000.00.1625656841 (4.0.0.0), node version 14.16.0, HDI version 1006, container API version 1004
Deployment started at 2021-08-20 01:31:25
Using default environment variables from file "default-env.json"
No ignore file at /home/user/projects/schema_evolution/db/.hdiignore.
Collecting files...
Collecting files... ok (0s 3ms)
1 directories collected
5 files collected
0 reusable modules collected
Target service: schema_evolution
Session variable APPLICATION is set to "SAP_HDI//".
Could not determine status of last build: Could not find any information about the previous deployment.
Processing revoke files...
Processing revoke files... ok (0s 0ms)
Processing grants files...
Processing grants files... ok (0s 0ms)
Preprocessing files...
Preprocessing files... ok (0s 0ms)
Connecting to the container "EVOLUTION_2"...
Connecting to the container "EVOLUTION_2"... ok (0s 110ms)
Locking the container "EVOLUTION_2"...
Locking the container "EVOLUTION_2"... ok (0s 333ms)
Synchronizing files with the container "EVOLUTION_2"...
Deleting files...
Deleting files... ok
Writing files...
Writing files... ok
Synchronizing files with the container "EVOLUTION_2"... ok (0s 508ms)
5 modified or added files are scheduled for deploy based on delta detection
0 deleted files are scheduled for undeploy based on delta detection (filtered by undeploy allowlist)
0 files are scheduled for deploy based on explicit specification
0 files are scheduled for undeploy based on explicit specification
Deploying to the container "EVOLUTION_2"...
Polling messages for request id: 6597
Starting make in the container "EVOLUTION_2" with 5 files to deploy, 0 files to undeploy...
Disabling table replication for the container schema "EVOLUTION_2"...
Disabling table replication for the container schema "EVOLUTION_2"... ok (0s 101ms)
Migrating libraries...
Migrating libraries... ok (0s 8ms)
Making...
Preparing...
Preparing the make transaction...
Deploying the configuration file "src/.hdiconfig"...
Deploying the configuration file "src/.hdiconfig"... ok (0s 52ms)
Adding "src/csm.evolution.PRICES.hdbmigrationtable" for deploy...
Adding "src/csm.evolution.PRICES.hdbmigrationtable" for deploy... ok (0s 18ms)
Adding "src/csm.evolution.PRICES_TAB.hdbtable" for deploy...
Adding "src/csm.evolution.PRICES_TAB.hdbtable" for deploy... ok (0s 0ms)
Adding "src/csm.evolution.STATIONS.hdbmigrationtable" for deploy...
Adding "src/csm.evolution.STATIONS.hdbmigrationtable" for deploy... ok (0s 0ms)
Adding "src/csm.evolution.STATIONS_TAB.hdbtable" for deploy...
Adding "src/csm.evolution.STATIONS_TAB.hdbtable" for deploy... ok (0s 0ms)
Preparing... ok (0s 167ms)
Preparing the make transaction... ok (0s 206ms)
Calculating dependencies...
Expanding...
Expanding "src/csm.evolution.PRICES.hdbmigrationtable"...
Expanding "src/csm.evolution.PRICES_TAB.hdbtable"...
Expanding "src/csm.evolution.STATIONS.hdbmigrationtable"...
Expanding "src/csm.evolution.STATIONS_TAB.hdbtable"...
Expanding "src/csm.evolution.PRICES_TAB.hdbtable"... ok (0s 6ms)
Expanding "src/csm.evolution.STATIONS.hdbmigrationtable"... ok (0s 6ms)
Expanding "src/csm.evolution.STATIONS_TAB.hdbtable"... ok (0s 6ms)
Expanding "src/csm.evolution.PRICES.hdbmigrationtable"... ok (0s 8ms)
Expanding... ok (0s 40ms)
Precompiling...
Precompiling "src/csm.evolution.PRICES.hdbmigrationtable"...
Precompiling "src/csm.evolution.PRICES_TAB.hdbtable"...
Precompiling "src/csm.evolution.STATIONS.hdbmigrationtable"...
Precompiling "src/csm.evolution.STATIONS_TAB.hdbtable"...
Precompiling "src/csm.evolution.PRICES.hdbmigrationtable"... ok (0s 6ms)
Precompiling "src/csm.evolution.PRICES_TAB.hdbtable"... ok (0s 6ms)
Precompiling "src/csm.evolution.STATIONS_TAB.hdbtable"... ok (0s 5ms)
Precompiling "src/csm.evolution.STATIONS.hdbmigrationtable"... ok (0s 5ms)
Precompiling... ok (0s 17ms)
Merging...
Merging... ok (0s 21ms)
Calculating dependencies... ok (0s 135ms)
Processing work list...
Deploying "src/csm.evolution.PRICES.hdbmigrationtable"...
Deploying "src/csm.evolution.PRICES_TAB.hdbtable"...
Deploying "src/csm.evolution.STATIONS.hdbmigrationtable"...
Deploying "src/csm.evolution.PRICES_TAB.hdbtable"... ok (0s 19ms)
Deploying "src/csm.evolution.STATIONS_TAB.hdbtable"...
Deploying "src/csm.evolution.PRICES.hdbmigrationtable"... ok (0s 108ms)
Deploying "src/csm.evolution.STATIONS_TAB.hdbtable"... ok (0s 88ms)
Deploying "src/csm.evolution.STATIONS.hdbmigrationtable"... ok (0s 109ms)
Processing work list... ok (0s 121ms)
Finalizing...
Finalizing... ok (0s 60ms)
Make succeeded (0 warnings): 5 files deployed (effective 5), 0 files undeployed (effective 0), 0 dependent files redeployed
Making... ok (0s 561ms)
Enabling table replication for the container schema "EVOLUTION_2"...
Enabling table replication for the container schema "EVOLUTION_2"... ok (0s 88ms)
Starting make in the container "EVOLUTION_2" with 5 files to deploy, 0 files to undeploy... ok (0s 770ms)
Deploying to the container "EVOLUTION_2"... ok (0s 995ms)
No default-access-role handling needed; global role "EVOLUTION_2::access_role" will not be adapted
Unlocking the container "EVOLUTION_2"...
Unlocking the container "EVOLUTION_2"... ok (0s 0ms)
Deployment to container EVOLUTION_2 done [Deployment ID: none].
Deployment ended at 2021-08-20 01:31:28
(2s 978ms)
From SAP HANA Database Explorer I log on to the HDI container and check whether tables have been deployed:
Take note of the TABLE_OID values:
| Name (TABLE_NAME) | Object ID (TABLE_OID) |
| CSM_EVOLUTION_PRICES | 267890 |
| CSM_EVOLUTION_PRICES_TAB | 267909 |
| CSM_EVOLUTION_STATIONS | 267900 |
| CSM_EVOLUTION_STATIONS_TAB | 267942 |
I have also populated the tables with some sample data (ca. 90 million records in the PRICES tables):
Schema evolution #1 - increase field length
I want to increase the size of field associated with Station Identifier - since it is declared as data type, I will just change the data type definition, which will be propagated to all the tables (as they all use the field) - the change is made in db/src/schema_evolution.cds:
// Change#001: New field length
type UUID : String(51);
// Change#001: Previous definition:
// type UUID : String(50);
I perform Core Data Services build int he CAP project with:
user: schema_evolution $ cds build
Existing artefacts are changed as follows:
- db/src/csm.evolution.PRICES.hdbmigrationtable
== version=2
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_PRICES (
DATE_TIME TIMESTAMP NOT NULL,
STATION_UUID NVARCHAR(51) NOT NULL,
DIESEL DOUBLE,
E5 DOUBLE,
E10 DOUBLE,
PRIMARY KEY(DATE_TIME, STATION_UUID)
)
== migration=2
-- generated by cds-compiler version 2.5.2
ALTER TABLE csm_evolution_PRICES ALTER (STATION_UUID NVARCHAR(51) NOT NULL);
- db/src/csm.evolution.PRICES_TAB.hdbtable
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_PRICES_TAB (
DATE_TIME TIMESTAMP NOT NULL,
STATION_UUID NVARCHAR(51) NOT NULL,
DIESEL DOUBLE,
E5 DOUBLE,
E10 DOUBLE,
PRIMARY KEY(DATE_TIME, STATION_UUID)
)
- db/src/csm.evolution.STATIONS.hdbmigrationtable
== version=2
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_STATIONS (
UUID NVARCHAR(51) NOT NULL,
NAME NVARCHAR(1000),
BRAND NVARCHAR(1000),
CITY NVARCHAR(34),
PRIMARY KEY(UUID)
)
== migration=2
-- generated by cds-compiler version 2.5.2
ALTER TABLE csm_evolution_STATIONS ALTER (UUID NVARCHAR(51) NOT NULL);
- db/src/csm.evolution.STATIONS_TAB.hdbtable
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_STATIONS_TAB (
UUID NVARCHAR(51) NOT NULL,
NAME NVARCHAR(1000),
BRAND NVARCHAR(1000),
CITY NVARCHAR(34),
PRIMARY KEY(UUID)
)
Also db/last-dev/csn.json is updated to reflect the state of last build operation.
Time to re-deploy changed artefacts:
The deployment log (removed some irrelevant entries):
Synchronizing files with the container "EVOLUTION_2"... ok (0s 486ms)
4 modified or added files are scheduled for deploy based on delta detection
0 deleted files are scheduled for undeploy based on delta detection (filtered by undeploy allowlist)
0 files are scheduled for deploy based on explicit specification
0 files are scheduled for undeploy based on explicit specification
Deploying to the container "EVOLUTION_2"...
Polling messages for request id: 6605
Starting make in the container "EVOLUTION_2" with 4 files to deploy, 0 files to undeploy...
Making...
Preparing the make transaction...
Adding "src/csm.evolution.PRICES.hdbmigrationtable" for deploy...
Adding "src/csm.evolution.PRICES.hdbmigrationtable" for deploy... ok (0s 48ms)
Adding "src/csm.evolution.PRICES_TAB.hdbtable" for deploy...
Adding "src/csm.evolution.PRICES_TAB.hdbtable" for deploy... ok (0s 0ms)
Adding "src/csm.evolution.STATIONS.hdbmigrationtable" for deploy...
Adding "src/csm.evolution.STATIONS.hdbmigrationtable" for deploy... ok (0s 0ms)
Adding "src/csm.evolution.STATIONS_TAB.hdbtable" for deploy...
Adding "src/csm.evolution.STATIONS_TAB.hdbtable" for deploy... ok (0s 0ms)
Preparing the make transaction... ok (0s 151ms)
Calculating dependencies...
Expanding...
Expanding "src/csm.evolution.PRICES.hdbmigrationtable"... ok (0s 7ms)
Expanding "src/csm.evolution.STATIONS.hdbmigrationtable"... ok (0s 6ms)
Expanding "src/csm.evolution.STATIONS_TAB.hdbtable"... ok (0s 6ms)
Expanding "src/csm.evolution.PRICES_TAB.hdbtable"... ok (0s 7ms)
Expanding... ok (0s 44ms)
Precompiling...
Precompiling "src/csm.evolution.PRICES_TAB.hdbtable"... ok (0s 6ms)
Precompiling "src/csm.evolution.STATIONS_TAB.hdbtable"... ok (0s 6ms)
Precompiling "src/csm.evolution.PRICES.hdbmigrationtable"... ok (0s 10ms)
Precompiling "src/csm.evolution.STATIONS.hdbmigrationtable"... ok (0s 9ms)
Precompiling... ok (0s 22ms)
Merging...
Merging... ok (0s 23ms)
Calculating dependencies... ok (0s 163ms)
Processing work list...
Undeploying "src/csm.evolution.PRICES.hdbmigrationtable"...
Keeping table "CSM_EVOLUTION_PRICES" for new owner "src/csm.evolution.PRICES.hdbmigrationtable"
Undeploying "src/csm.evolution.PRICES_TAB.hdbtable"...
Keeping table "CSM_EVOLUTION_PRICES_TAB" for new owner "src/csm.evolution.PRICES_TAB.hdbtable"
Undeploying "src/csm.evolution.STATIONS.hdbmigrationtable"...
Keeping table "CSM_EVOLUTION_STATIONS" for new owner "src/csm.evolution.STATIONS.hdbmigrationtable"
Undeploying "src/csm.evolution.STATIONS_TAB.hdbtable"...
Keeping table "CSM_EVOLUTION_STATIONS_TAB" for new owner "src/csm.evolution.STATIONS_TAB.hdbtable"
Undeploying "src/csm.evolution.PRICES.hdbmigrationtable"... ok (0s 2ms)
Deploying "src/csm.evolution.PRICES.hdbmigrationtable"...
Using existing table "CSM_EVOLUTION_PRICES" from former owner "src/csm.evolution.PRICES.hdbmigrationtable"
Undeploying "src/csm.evolution.PRICES_TAB.hdbtable"... ok (0s 10ms)
Deploying "src/csm.evolution.PRICES_TAB.hdbtable"...
Undeploying "src/csm.evolution.STATIONS.hdbmigrationtable"... ok (0s 9ms)
Deploying "src/csm.evolution.STATIONS.hdbmigrationtable"...
Using existing table "CSM_EVOLUTION_PRICES_TAB" from former owner "src/csm.evolution.PRICES_TAB.hdbtable"
Undeploying "src/csm.evolution.STATIONS_TAB.hdbtable"... ok (0s 17ms)
Deploying "src/csm.evolution.STATIONS_TAB.hdbtable"...
Using existing table "CSM_EVOLUTION_STATIONS_TAB" from former owner "src/csm.evolution.STATIONS_TAB.hdbtable"
"CSM_EVOLUTION_STATIONS_TAB": the table structure was changed; running migration
"CSM_EVOLUTION_PRICES": Performing migration step to version 2
"CSM_EVOLUTION_PRICES_TAB": the table structure was changed; running migration
"CSM_EVOLUTION_STATIONS": Performing migration step to version 2
Deploying "src/csm.evolution.PRICES.hdbmigrationtable"... ok (0s 262ms)
Deploying "src/csm.evolution.STATIONS.hdbmigrationtable"... ok (0s 258ms)
"CSM_EVOLUTION_STATIONS_TAB": the table contains data; recreating it and copying the data
"CSM_EVOLUTION_PRICES_TAB": the table contains data; recreating it and copying the data
Working ...
Working ...
Deploying "src/csm.evolution.STATIONS_TAB.hdbtable"... ok (286s 598ms)
Deploying "src/csm.evolution.PRICES_TAB.hdbtable"... ok (286s 607ms)
Processing work list... ok (286s 630ms)
Finalizing...
Finalizing... ok (0s 48ms)
Make succeeded (0 warnings): 4 files deployed (effective 4), 0 files undeployed (effective 0), 0 dependent files redeployed
Making... ok (287s 58ms)
Enabling table replication for the container schema "EVOLUTION_2"...
Enabling table replication for the container schema "EVOLUTION_2"... ok (0s 89ms)
Starting make in the container "EVOLUTION_2" with 4 files to deploy, 0 files to undeploy... ok (287s 255ms)
Deploying to the container "EVOLUTION_2"... ok (287s 478ms)
Deployment to container EVOLUTION_2 done [Deployment ID: none].
Deployment ended at 2021-08-20 03:08:46
(289s 105ms)
From the above, let's consider this small extract:
Deploying "src/csm.evolution.PRICES.hdbmigrationtable"... ok (0s 262ms)
Deploying "src/csm.evolution.STATIONS.hdbmigrationtable"... ok (0s 258ms)
"CSM_EVOLUTION_STATIONS_TAB": the table contains data; recreating it and copying the data
"CSM_EVOLUTION_PRICES_TAB": the table contains data; recreating it and copying the data
Deploying "src/csm.evolution.STATIONS_TAB.hdbtable"... ok (286s 598ms)
Deploying "src/csm.evolution.PRICES_TAB.hdbtable"... ok (286s 607ms)
So, deployment for tables using .hdbmigrationtable took ca. 250ms - even for PRICES table with 90 million records. This is because ALTER statement is used to transition from version 1 to 2 and no data movement is involved.
But in case of .hdbtable artefacts, the deployment process performs:
- creation of new table with desired structure and temporary name, say Tnew,
- data copy from original table Told to the new Tnew,
- drop table Told,
- rename table Tnew to Told.
Proof of the above:
Take note of the TABLE_OID values:
| Name (TABLE_NAME) | Original Object ID (TABLE_OID) | Current Object ID (TABLE_OID) |
| CSM_EVOLUTION_PRICES | 267890 | 267890 |
| CSM_EVOLUTION_PRICES_TAB | 267909 | 268024 |
| CSM_EVOLUTION_STATIONS | 267900 | 267900 |
| CSM_EVOLUTION_STATIONS_TAB | 267942 | 270125 |
Object ID for the tables deployed with .hdbtable has changed - these are newly created tables.
Schema evolution #2 - change field name
I want to increase the size of field associated with Station Identifier - since it is declared as data type, I will just change the data type definition, which will be propagated to all the tables (as they all use the field) - the change is made in db/src/schema_evolution.cds (only changed structures shown):
@cds.persistence.journal
Entity ![STATIONS] {
key ![STATION_UUID]: UUID @title: 'UUID' ; // Change#002: UUID renamed to STATION_UUID
![NAME]: String(1000) @title: 'NAME' ;
![BRAND]: String(1000) @title: 'BRAND' ;
![CITY]: String(34) @title: 'CITY' ;
};
Entity ![STATIONS_TAB] {
key ![STATION_UUID]: UUID @title: 'UUID' ; // Change#002: UUID renamed to STATION_UUID
![NAME]: String(1000) @title: 'NAME' ;
![BRAND]: String(1000) @title: 'BRAND' ;
![CITY]: String(34) @title: 'CITY' ;
};
I perform Core Data Services build int he CAP project with:
user: schema_evolution $ cds build
This time, an error is produced:
[ERROR] Current model changes require manual resolution.
See migration file db/src/csm.evolution.STATIONS.hdbmigrationtable for further details.
Existing artefacts are changed as follows (only STATIONS tables are affected):
- db/src/csm.evolution.STATIONS.hdbmigrationtable
== version=3
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_STATIONS (
STATION_UUID NVARCHAR(51) NOT NULL,
NAME NVARCHAR(1000),
BRAND NVARCHAR(1000),
CITY NVARCHAR(34),
PRIMARY KEY(STATION_UUID)
)
== migration=3
>>>>> Manual resolution required - DROP statements causing data loss are disabled by default.
>>>>> You may either:
>>>>> uncomment statements to allow incompatible changes, or
>>>>> refactor statements, e.g. replace DROP/ADD by single RENAME statement
>>>>> After manual resolution delete all lines starting with >>>>>
-- generated by cds-compiler version 2.5.2
ALTER TABLE csm_evolution_STATIONS ADD (STATION_UUID NVARCHAR(51) NOT NULL);
-- ALTER TABLE csm_evolution_STATIONS DROP PRIMARY KEY;
-- ALTER TABLE csm_evolution_STATIONS ADD PRIMARY KEY(STATION_UUID)
-- ALTER TABLE csm_evolution_STATIONS DROP (UUID);
== migration=2
-- generated by cds-compiler version 2.5.2
ALTER TABLE csm_evolution_STATIONS ALTER (UUID NVARCHAR(51) NOT NULL);
- db/src/csm.evolution.STATIONS_TAB.hdbtable
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_STATIONS_TAB (
STATION_UUID NVARCHAR(51) NOT NULL,
NAME NVARCHAR(1000),
BRAND NVARCHAR(1000),
CITY NVARCHAR(34),
PRIMARY KEY(STATION_UUID)
)
What happened? As per configuration in the package.json file (refer earlier in the blog), we have prohibited use of DROP statements in .hdbmigrationtable. At present, CDS builder tried to define the transition strategy similar to that used by .hdbtable - that is:
- create new field,
- re-create primary key (as the field is part of it),
- drop original field.
There is a problem with this strategy - there is data in the table and the above would result in loss of data in the changed field. Luckily, SAP HANA offers column rename statement, so we can use this one instead. So, I modify the migration=3 step as follows:
== version=3
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_STATIONS (
STATION_UUID NVARCHAR(51) NOT NULL,
NAME NVARCHAR(1000),
BRAND NVARCHAR(1000),
CITY NVARCHAR(34),
PRIMARY KEY(STATION_UUID)
)
== migration=3
-- Manually entered to avoid column add and data copy
RENAME COLUMN csm_evolution_STATIONS.UUID to STATION_UUID;
== migration=2
-- generated by cds-compiler version 2.5.2
ALTER TABLE csm_evolution_STATIONS ALTER (UUID NVARCHAR(51) NOT NULL);
Time to re-deploy changed artefacts - will do it one-by one this time, starting with csm.evolution.STATIONS.hdbmigrationtable:
The deployment log (removed some irrelevant entries):
Processing work list...
Undeploying "src/csm.evolution.STATIONS.hdbmigrationtable"...
Keeping table "CSM_EVOLUTION_STATIONS" for new owner "src/csm.evolution.STATIONS.hdbmigrationtable"
Undeploying "src/csm.evolution.STATIONS.hdbmigrationtable"... ok (0s 2ms)
Deploying "src/csm.evolution.STATIONS.hdbmigrationtable"...
Using existing table "CSM_EVOLUTION_STATIONS" from former owner "src/csm.evolution.STATIONS.hdbmigrationtable"
"CSM_EVOLUTION_STATIONS": Performing migration step to version 3
Deploying "src/csm.evolution.STATIONS.hdbmigrationtable"... ok (0s 78ms)
Processing work list... ok (0s 91ms)
Finalizing... ok (0s 26ms)
Make succeeded (0 warnings): 1 files deployed (effective 1), 0 files undeployed (effective 0), 0 dependent files redeployed
Making... ok (0s 287ms)
Starting make in the container "EVOLUTION_2" with 1 files to deploy, 0 files to undeploy... ok (0s 482ms)
Deploying to the container "EVOLUTION_2"... ok (0s 713ms)
Deployment to container EVOLUTION_2 done [Deployment ID: none].
Deployment ended at 2021-08-20 03:41:06
(2s 238ms)
Checking the result:
The TABLE_OOID and COLUMN_ID remained unchanged.
Let's try to deploy the csm.evolution.STATIONS.hdbtable:
"CSM_EVOLUTION_STATIONS_TAB": the table structure was changed; running migration
"CSM_EVOLUTION_STATIONS_TAB": the table contains data; recreating it and copying the data
Error: com.sap.hana.di.table: "CSM_EVOLUTION_STATIONS_TAB": could not migrate the database object [8250019]
at "src/csm.evolution.STATIONS_TAB.hdbtable" (0:0)
Error: com.sap.hana.di.table: Database error 287: cannot insert NULL or update to NULL: STATION_UUID: line 1 col 1 (at pos 0) [8201003]
at "src/csm.evolution.STATIONS_TAB.hdbtable" (0:0)
Error: Worker 1 has encountered an error; all remaining jobs will be canceled [8214600]
Error: Processing work list... failed [8212102]
Make failed (4 errors, 1 warnings): tried to deploy 1 files, undeploy 0 files, redeploy 0 dependent files
Error: Making... failed [8211605]
Deployment to container EVOLUTION_2 failed - error: HDI make failed [Deployment ID: none].
Deployment ended at 2021-08-20 03:46:48
Error: HDI make failed
(2s 590ms)
CDS deployer tried to perform the transition strategy as mentioned earlier:
- create new field,
- re-create primary key (as the field is part of it),
- drop original field.
It fails on 1st step already as this particular field is NOT NULL and cannot be created in a table with existing data. Manual steps would be required to successfully perform transition to new schema version.
Or… is there a better way?
Transition from .hdbtable to .hdbmigrationtable
The problem I faced above is a perfect opportunity to consider transitioning to the .hdbmigrationtable artefact.
The process to do so is remarkably simple. There is only one condition:
During the transition from .hdbtable to .hdbmigrationtable you have to deploy version=1 of the .hdbmigrationtable artifact which may not include any migration steps.
At the same time, since HDI already has information about the fact that the table has been deployed as .hdbtable artefact, the existing .hdbtable design-time artifact needs to be undeployed.
Bearing the above in mind, I perform the following steps:
- Generation of 1st version of the .hdbmigrationtable artefact for the table csm_evolution_STATIONS_TAB - the structure in this version must be identical to last deployed state of corresponding .hdbtable artefact.
- Removal of .hdbtable artefact from project structure (db/src in my case).
- Creation of Undeploy Allowlist to allow undeployment of .hdbtable artefact.
- Deployment of the design-time artefacts to SAP HANA (Cloud).
- Applying desired structural changes in .hdbmigrationtable artefact as 2nd or higher version.
Below the details of the steps mentioned above.
Generation of 1st version of the .hdbmigrationtable artefact for the table csm_evolution_STATIONS_TAB
The last successfully deployed version of the schema definition for csm_evolution_STATIONS_TAB is:
Entity ![STATIONS_TAB] {
key ![UUID]: UUID @title: 'UUID' ;
![NAME]: String(1000) @title: 'NAME' ;
![BRAND]: String(1000) @title: 'BRAND' ;
![CITY]: String(34) @title: 'CITY' ;
};
And corresponding .hdbtable artefact:
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_STATIONS_TAB (
UUID NVARCHAR(51) NOT NULL,
NAME NVARCHAR(1000),
BRAND NVARCHAR(1000),
CITY NVARCHAR(34),
PRIMARY KEY(UUID)
)
I now change design-time artefact for the table to .hdbmigrationtable by adding @cds.persistence.journal annotation:
@cds.persistence.journal
Entity ![STATIONS_TAB] {
key ![UUID]: UUID @title: 'UUID' ;
![NAME]: String(1000) @title: 'NAME' ;
![BRAND]: String(1000) @title: 'BRAND' ;
![CITY]: String(34) @title: 'CITY' ;
};
Now, I run CDS build:
user: schema_evolution $ cds build
Following new artefact is generated in my project:
db/src/csm.evolution.STATIONS_TAB.hdbmigrationtable
== version=1
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_STATIONS_TAB (
UUID NVARCHAR(51) NOT NULL,
NAME NVARCHAR(1000),
BRAND NVARCHAR(1000),
CITY NVARCHAR(34),
PRIMARY KEY(UUID)
)
Removal of .hdbtable artefact from project structure
After the build step above, the old .hdbtable artefact will likely still exist in your project structure – delete the file manually:
Creation of Undeploy Allowlist to allow undeployment of .hdbtable artefact.
In the db folder, I created undeploy.json file:
[
"src/csm.evolution.STATIONS_TAB.hdbtable"
]
This will allow HDI to perform undeployment of the old artefact (.hdbtable) to “make space” for the new artefact (.hdbmigrationtable).
Deployment of the design-time artefacts to SAP HANA (Cloud)
I am ready for deployment – below extract of the deployment log:
Adding "src/csm.evolution.STATIONS_TAB.hdbmigrationtable" for deploy... ok (0s 9ms)
Adding "src/csm.evolution.STATIONS_TAB.hdbtable" for undeploy... ok (0s 0ms)
Preparing the make transaction... ok (0s 79ms)
Calculating dependencies...
Expanding "src/csm.evolution.STATIONS_TAB.hdbmigrationtable"... ok (0s 8ms)
Precompiling "src/csm.evolution.STATIONS_TAB.hdbmigrationtable"... ok (0s 5ms)
Merging... ok (0s 6ms)
Calculating dependencies... ok (0s 64ms)
Processing work list...
Undeploying "src/csm.evolution.STATIONS_TAB.hdbtable"...
Keeping table "CSM_EVOLUTION_STATIONS_TAB" for new owner "src/csm.evolution.STATIONS_TAB.hdbmigrationtable"
Undeploying "src/csm.evolution.STATIONS_TAB.hdbtable"... ok (0s 2ms)
Deploying "src/csm.evolution.STATIONS_TAB.hdbmigrationtable"...
Using existing table "CSM_EVOLUTION_STATIONS_TAB" from former owner "src/csm.evolution.STATIONS_TAB.hdbtable"
"CSM_EVOLUTION_STATIONS_TAB": The run-time table already matches the specified definition; skipping migrations
Deploying "src/csm.evolution.STATIONS_TAB.hdbmigrationtable"... ok (0s 75ms)
Make succeeded (0 warnings): 1 files deployed (effective 1), 1 files undeployed (effective 1), 0 dependent files redeployed
Deploying to the container "EVOLUTION_2"... ok (0s 746ms)
Deployment ended at 2021-08-24 00:39:04
(2s 471ms)
And voila, the table CSM_EVOLUTION_STATIONS_TAB is now under “control” of .hdbmigration design-time artefact. And the process happened without any migration steps, so original table (and data) remained untouched.
Applying desired structural changes in .hdbmigrationtable artefact as 2nd or higher version
Now, I can change the field name as originally desired.
Change db/schema_evolution.cds:
@cds.persistence.journal
Entity ![STATIONS_TAB] {
key ![STATION_UUID]: UUID @title: 'UUID' ;
![NAME]: String(1000) @title: 'NAME' ;
![BRAND]: String(1000) @title: 'BRAND' ;
![CITY]: String(34) @title: 'CITY' ;
};
Resulting db/src/csm.evolution.STATIONS_TAB.hdbmigrationtable after manual adjustment:
== version=2
-- generated by cds-compiler version 2.5.2
COLUMN TABLE csm_evolution_STATIONS_TAB (
STATION_UUID NVARCHAR(51) NOT NULL,
NAME NVARCHAR(1000),
BRAND NVARCHAR(1000),
CITY NVARCHAR(34),
PRIMARY KEY(STATION_UUID)
)
== migration=2
-- Manually entered to avoid column add and data copy
RENAME COLUMN csm_evolution_STATIONS.UUID to STATION_UUID;
Last remarks on the transition and choice between .hdbtable and .hdbmigrationtable
The process above can also be executed in reverse order – that is transition from .hdbmigrationtable to .hdbtable.
You may wonder why would I still use .hdbtable at all? For example, during initial development, when there are multiple structural changes happening and you work with small data sets, you may want to avoid generating new version every time there is a change. Once your model is relatively stable, you can transition to .hdbmigrationtable.
Summary
I hope that with this simple example I was able to showcase benefits of .hdbmigrationtable CDS artefact as compared to .hdbtable. As previously stated, the benefits include:
- Full traceability of changes in both design-time and run-time artefacts.
- Ability to influence transition approach (migration steps) between versions.
- Clear transition path from any version to any (higher) version.
- Table changes in-place - without a need to use temporary tables and copy data. This saves deployment time and database resources.
---------
Useful reference materials:
Documentation:
- HDI Reference - Migration Tables (.hdbmigrationtable)
- SAP Cloud Application Programming Model (Feb'21 changelog)
- SAP Cloud Application Programming - Schema Evolution
- Configure an SAP CAP Application to Work with Native SAP HANA Artifacts
- DevOps Community
- openSAP: Efficient DevOps with SAP
Developer’s guides:
- Create an SAP Cloud Application Programming Model Project for SAP HANA Cloud
- Change Data Structures Using HDBTABLE and HDBMIGRATIONTABLE Objects
Blogs:
SAP HANA Cloud Workshops:
- March 2021 - refer to The highlights from the SAP HANA Cloud Workshops
This workshop included cotnent on hdbmigrationtable - you can watch the workshop on-demand and access the workbook with detailed instructions. - September 2021 - refer to Collaborative database development
- SAP Managed Tags:
10 Comments
