Skip to Content
Technical Articles
Author's profile photo Jacek Klatt

Table lifecycle (aka Schema Evolution) in SAP HANA (Cloud) with .hdbmigrationtable

Note – this blog is a result of collaboration between Lukas Schoemig and Jacek Klatt.

Note – this and other development topics will be discussed in the upcoming workshop series (September 28-30,2021) on Collaborative database development in SAP HANA Cloud, SAP HANA databaseRegistrations are now open!

 

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 is still in beta, respective notations as shown in the section “journal” may change 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:

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
`-- @sap/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:

  1. 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.
  2. Removal of .hdbtable artefact from project structure (db/src in my case).
  3. Creation of Undeploy Allowlist to allow undeployment of .hdbtable artefact.
  4. Deployment of the design-time artefacts to SAP HANA (Cloud).
  5. 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.

———

Notable materials utilised in preparation of this blog:

 

Assigned tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Christian Paulus
      Christian Paulus

      The CAP feature for schema evolution with .hdbmigrationtable is not yet publicly released for productive usage (but of course feel free to play with it). We plan to release it including official documentation on https://cap.cloud.sap in Q4 2021.

      Author's profile photo Jacek Klatt
      Jacek Klatt
      Blog Post Author

      Thank you Christian,
      I have added corresponding diclaimer to clarify that while usage of .hdbmigrationtable artefact is publicly released, it's integration with CAP and CDS is still in beta.