BigQuery Connector for SAP: Handle table structure changes like a pro ! (Part 1)

The BigQuery Connector for SAP offered by Google cloud is a tool to connect SAP SLT with Big Query and achieve near real time data-replication.

There might come a requirement to change the structure of a table for which there is an active SLT replication. This might be the case for custom tables or when custom fields are added to standard SAP tables.

In some cases, the replication needs to be stopped and started again. This means that the initial load will also be performed again. The target table in BigQuery also needs to be deleted before the reload happens.

For other cases, if the appropriate steps are performed, there is no need to reload the table. The table structure changes are replicated automatically when the next set of CDC(Change Data Capture) records are replicated to the target table.

This blog explains how to modify the SAP source table structure, for which an existing SLT replication is active without the need to reload the table.

The changes to a table structure are divided into 3 categories:

• Supported : Changes can be done while a replication is in progress, and are automatically replicated to the target table. Reloading the table is not required.
• Supported with additional flags reset steps in LTRC: Changes can be done while a replication is in progress. Additional steps need to be performed in the LTRC transaction. After that, the table structure changes are automatically replicated to the target table.
• Not supported: The table needs to be reloaded. Before reloading, the target table needs to be deleted, so that the BigQuery Connector for SAP can create a new target table with updated structure.

Below is the list of possible structure changes to a table in source system:

• Primary key and Non Primary key field changes
• Add/Delete a column to the table
• Change the data type of an existing column

Primary Key Column Changes

For any primary key related changes, follow these steps:

1. Stop the replication in the SLT system using transaction LTRC.
2. In BigQuery, delete the target table.
3. Change the data type in the source system.
4. Start the replication using transaction LTRC.

Non Primary Key Column Changes

Add a column

1. Add the new column in the table using Transaction SE11.
2. The existing Replication will go into status: ‘Load /Replication blocked’ as the structure of the table has been changed.

3. Reset the Block Data transfer flag. (Reference SAP Note: 2204955 )

Go to your mass transfer id in transaction LTRC -> ‘Expert Functions’ -> Select ‘Reset Load and Replication status’.

Check ‘Reset “Block Data Trans.” Flag’ and execute.

Status changed for the replication.

5. Add/Change/Delete a record in the source table.

6. Validate that the record is replicated to the target table in BigQuery. Also validate that the new column has been added to the target table in BigQuery.

Delete a column

1. Suspend the replication in the SLT system.

2. Delete the column from the table in the source system.

3. Delete the column from the BigQuery table using the DDL statement mentioned in BigQuery documentation.

4. Resume the replication in the SLT system.

5. As a result of this step, the existing SLT triggers are either deleted or changed to an inconsistent state. To validate the triggers:

Go to your mass transfer id in transaction LTRC -> ‘Expert Functions’ -> View Trigger Source Code’ -> Enter mass transfer id and table name and execute

If there is no code in the output screen, this means the triggers have been deleted.

6. If the triggers are deleted, recreate the triggers. (Reference: SAP Note: 2254376).

6.1. Deactivate the SLT configuration.

6.2. Reset the status for triggers and logging tables.

‘Expert Functions’ -> Select ‘Reset Status for Triggers and Logging Tables’.

Check ‘Reset “Failed” Flag’ and ‘Reset “Trigger Created” Flag’. Execute.

6.3. Create database triggers.

Go to ‘Expert Functions’ -> ‘Create Database Triggers’

6.4. Activate the SLT configuration.

7. The replication may go into ‘Load /Replication blocked’. Reset the Block Data transfer flag. (Reference SAP Note: 2204955 )

Go to ‘Expert Functions’ -> Select ‘Reset Load and Replication status’.

Check ‘Reset “Block Data Trans.” Flag’ and execute.

Status changed for the replication.

8. Clear the old errors from the log. Go to ‘View Errors’-> Click on ’Clear Log’

9. Add/Change/Delete a record in the source table.

10. Validate that the record is replicated to the target table in BigQuery. Also validate that the column is deleted from the target table in BigQuery.

Change the data type of an existing column

When the data type of an existing column in the SAP source table is changed, specific steps need to be followed depending on whether the data type is being changed to a compatible or a non-compatible data type with the target BigQuery data type.

A data type is compatible with the data type in BigQuery, when the existing data type and new data type map to the same BigQuery data type. For example, if the data type of a column is changed from INT1 to INT2 in a source table, then both the data types are compatible with the data type INTEGER in BigQuery.

BigQuery Connector for SAP documentation lists the Data type mapping.

Change to a compatible data type

1. Change the data type to a compatible data type in the source system
2. As a result of this step, the existing SLT triggers are either deleted or changed to an inconsistent state.

Go to your mass transfer id in transaction LTRC -> ‘Expert Functions’ -> View Trigger Source Code’ -> Enter mass transfer id and table name and execute

If there is no code in the output screen, this means the triggers have been deleted.

3. If the triggers are deleted, recreate the triggers. (Reference: SAP Note: 2254376).

3.1. Deactivate the SLT configuration.

3.2. Reset the status for triggers and logging tables.

Go to ‘Expert Functions’ -> Select ‘Reset Status for Triggers and Logging Tables’.

Check ‘Reset “Failed” Flag’ and ‘Reset “Trigger Created” Flag’. Execute.

3.3. Create database triggers.

Go to ‘Expert Functions’ -> ‘Create Database Triggers’

3.4. Activate the SLT configuration.

4. The replication may go into ‘Load /Replication blocked’. Reset the Block Data transfer flag. (Reference SAP Note: 2204955 )

Go to ‘Expert Functions’ -> Select ‘Reset Load and Replication status’.

Check ‘Reset “Block Data Trans.” Flag’ and execute.

Status changed for the replication.

5. Clear the old errors from the log. Go to ‘View Errors’-> Click on ’Clear Log’

6. Add/Change/Delete an entry in the source table.

7. Validate that the record is replicated to the target table in BigQuery. There will be no change in the data type in the target table.

Change to a non compatible data type

1. Stop the replication in the SLT system using transaction LTRC.
2. In BigQuery, delete the target table.
3. Change the data type in the source system.
4. Start the replication using transaction LTRC.

Further Reading

Blog continues with other options for structural changes and relevant details.