SAP HANA CDC with Gluesync: Setup & Configuration

Source data from SAP HANA

Prerequisites

To have Gluesync working on your SAP HANA instance you will need to have:

Valid user credentials with permission to read from source tables;
Valid permissions to create tables in the source schema for shadow CDC tables;
Valid permissions to create the GLUESYNC schema (used for CDC metadata and staging, schema name is configurable);
SAP HANA 2.0 SPS04 or above, or SAP HANA Cloud;

To create a valid user for Gluesync on your SAP HANA database with the minimum required privileges, you can run/adapt the following SQL statements:

CREATE USER GLUESYNC_USER PASSWORD 'your_secure_password' NO FORCE_FIRST_PASSWORD_CHANGE;
GRANT SELECT ON SCHEMA YOUR_SOURCE_SCHEMA TO GLUESYNC_USER;
GRANT CREATE ANY ON SCHEMA YOUR_SOURCE_SCHEMA TO GLUESYNC_USER;
GRANT CREATE SCHEMA TO GLUESYNC_USER;

These permissions allow the user to connect, read data, create shadow tables for CDC, and manage the CDC schema. For production environments, consider granting only the specific permissions needed.

Setup via Web UI

Hostname / IP Address: DNS record or IP Address of your SAP HANA server;
Port: Optional, defaults to 30015 (single tenant) or 30013 (multi-tenant with MDC);
Database name: Name of your SAP HANA tenant database (for multi-tenant configurations);
Username: Username with read access to source tables and permission to create shadow tables;
Password: Password belonging to the given username;
Max connections count: Maximum number of connections the pool can instantiate;
Enable TLS: Optional, defaults to false. SAP HANA Cloud requires TLS/SSL.

Specific configuration

Source change retention in days: Configurable retention period for CDC shadow table records (default: 5 days)
Query parallelism: Number of parallel queries for CDC data extraction (default: 4)

Setup via Rest APIs

Here following an example of calling the Core Hub’s Rest API via curl to setup the connection for this Agent.

Connect the agent

curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
        "hostCredentials": {
        "connectionName": "myAgentNickName",
        "host": "host-address",
        "port": 30015,
        "databaseName": "HXE",
        "username": "GLUESYNC_SOURCE_USER",
        "password": "your_secure_password",
        "maxConnectionsCount": 100,
        "enableTls": true
      }
}'

Setting up for CDC

Gluesync uses a shadow table CDC approach for SAP HANA. When you configure a table for CDC, the agent will:

Create a shadow audit table in the GLUESYNC schema to track changes
Shadow tables capture changes with special columns (OPERATION, COMMIT_ID, COMMIT_TIME)
Poll the audit table for new changes at regular intervals
Automatically delete processed records based on the configured retention period (default 5 days)

How shadow table CDC works

-- Example: For a source table named CUSTOMERS, Gluesync creates:
-- 1. A shadow audit table: GLUESYNC.CUSTOMERS_AUDIT
-- 2. The shadow table has columns: OPERATION (I/U/D), COMMIT_ID, COMMIT_TIME, plus the data columns

The audit table captures:

The operation type (I=INSERT, U=UPDATE, D=DELETE)
The commit ID from SAP HANA transaction history
The timestamp of the change
The primary key values
Before and after images of the changed data (for UPDATE operations)

Required permissions for CDC

The database user needs additional permissions to create and manage shadow tables:

-- Grant table creation permissions on source schema for shadow tables
GRANT CREATE ANY ON SCHEMA YOUR_SOURCE_SCHEMA TO GLUESYNC_USER;

-- Grant full access to the GLUESYNC CDC schema
GRANT ALL PRIVILEGES ON SCHEMA GLUESYNC TO GLUESYNC_USER;

SAP HANA Cloud considerations

When connecting to SAP HANA Cloud:

Use port 443 for TLS connections
Enable TLS/SSL in the agent configuration
The HDI (HANA Deployment Infrastructure) container approach may require additional configuration for shadow table creation permissions

Working with Before & After images

Before & after images are a feature that allows Gluesync to track the changes that have occurred in your database and comparing them with their previous values. This enables Gluesync to apply only the changes that have occurred, saving bandwidth and processing time.

This feature is automatically enabled and available for use in this agent for UPDATE operations.

Troubleshooting

Audit table growth: If audit tables grow too large, check that the Gluesync agent is running and successfully processing changes. The agent automatically deletes processed records older than the retention period (default 5 days).
Shadow table creation issues: If you encounter issues with shadow table creation, verify that:
- The database user has CREATE ANY permission on the source schema
- The GLUESYNC schema exists and the user has full privileges on it
Performance considerations: For high-volume tables, consider:
- Adjusting the CDC polling interval in Core Hub configuration
- Monitoring the audit table size and cleanup frequency
- Increasing query parallelism for faster CDC data extraction