Change data capture (delta loads)

You can use change data capture techniques to identify changes in a source table which occur between two points in time. For example, to identify changes between the end point of the initial or last load and the current date.

SAP HANA Cloud Integration allows you to create a task with delta load capability by providing two functions which can be used to track the timestamp. In a process, the data flow logic must be set to accomodate change data capture.

Example

Consider two tasks, one for an initial load and a second task for delta loads. You can use preload and postload scripts in each task to call the necessary functions. The functions set values for global variables that can be used to filter data by date range.

The following global variables are used and must be defined with the type datetime:

$G_SDATE to specify the start date of the date range
$G_EDATE to specify the end date of the date range

Initial load task

For applications that support it, check the data flow options () to ensure that the Delete data from table before loading check box is selected for all data flows in the task.

Create the following preload script:

 
#Set $G_SDATE to sysutcdate() - 1 if the global variable value is not set.  
#
if ($G_SDATE is null) 
  $G_SDATE = sysutcdate()-1; 

# Set $G_EDATE to current date if the global variable value is not set.
# 
if ($G_EDATE is null)
  $G_EDATE = sysutcdate();

Create the following postload script:

save_data('<task_name>',to_char($G_EDATE,'yyyy-mm-dd hh24:mi:ss'));

Delta load task

Create the following preload script:

 
if (get_data('<task_name>') = ",
  $G_SDATE = sysutcdate()- 1
else
  $G_SDATE = to_date(get_data('<task_name>'),'yyyy-mm-dd hh24:mi:ss');
#
if ($G_SDATE is null) 
  $G_SDATE = sysutcdate()-1;

Create the following postload script:

save_data('<task_name>',to_char($G_EDATE,'yyyy-mm-dd hh24:mi:ss'));

Change data capture (delta loads)

Functions

Example