Change Schema in SAP HANA
Overview
BladePipe captures data changes in a source SAP HANA instance through triggers. DDL synchronization is not supported. If there are DDL changes, please handle them as follows.
Prerequisites
Make sure that in the DataJob configuration, the CDC table mode is set to table-level CDC table (that is, make sure that the parameter value of incrTableMode is TABLE_LEVEL_CDC_TABLE on the source DataSource Configuration).
Procedure
Add a Column
When adding a column in a source SAP HANA instance, follow the steps below:
- Execute the following statements at the source table, CDC table and target table respectively to add a column.
-- Add a column at the source table
ALTER TABLE "SYSTEM"."TEST"
ADD ("COL_4" INTEGER NOT NULL);
-- Add a column at the CDC table (CDC table name format: ${DB_NAME}_${SCHEMA_NAME}_${TABLE_NAME}_CDC_TABLE)
ALTER TABLE "SYSTEM"."SYSTEMDB_SYSTEM_TEST_CDC_TABLE"
ADD ("COL_4" INTEGER NOT NULL);
-- Add a column at the target table (taking MySQL as an example)
ALTER TABLE `SYSTEMDB`.`TEST`
ADD COLUMN `COL_5` INT NOT NULL;
- Modify the subscription to delete the source table with the added column.
- Modify the subscription again to add the source table with the added column, and select Full Data.
Delete a Column
When deleting a column in a source SAP HANA instance, follow the steps below:
- Execute the following statements at the source table, CDC table and target table respectively to delete a column.
-- Delete a column at the source table
ALTER TABLE "SYSTEM".TEST
DROP ("COL_4");
-- Delete a column at the CDC table (CDC table name format: ${DB_NAME}_${SCHEMA_NAME}_${TABLE_NAME}_CDC_TABLE)
ALTER TABLE "SYSTEM"."SYSTEMDB_SYSTEM_TEST_CDC_TABLE"
DROP ("COL_4");
-- Delete a column at the target table (taking MySQL as an example)
ALTER TABLE `SYSTEMDB`.`TEST`
DROP COLUMN `COL_5`;
- Modify the subscription to delete the source table with the deleted column.
- Modify the subscription again to add the source table with the deleted column, and select Full Data.