Replication
This service replicates data from an RDBMS to OpenSearch.
|
Impact on System Performance Replication has a direct impact on system performance. In particular, the number of threads (Replication → Settings → Replication_Threads) and the interval (replicationInterval) play a role here. The more jobs that run in a short period of time, the higher the CPU utilization. This is especially true when large amounts of data are being replicated. Although |
Preparing for Data Record Timeliness
Before setting up replication, a few prerequisites should be met. Replication requires a column containing exclusively increasing timestamps for each table (both Log and Childlog), as this timestamp is used to subsequently identify newer or updated records. If different processes, servers, etc., log data to the database in parallel—and thus potentially with a time lag (even if only by milliseconds)—it may happen that individual records are not displayed in BPC because they were committed with a timestamp older than the most recent replicated one.
It is therefore recommended:
-
to log the database server’s time rather than the logging process’s server time, since different process servers may have slightly different times
-
to ensure that this column is actually updated with every INSERT or UPDATE
Both can be easily achieved using an additional (hidden) column with a default value and an associated trigger. This has the advantage that nothing changes at all for the logging process)
Adding a technical TIMESTAMP column for replication
Oracle
For PM-Log and Childlog (without a prefix) in Oracle, the procedure for creating them looks like this, for example:
--LOG:
--Add a column (with 6 decimal places of precision); set the default value for inserts to the current time in UTC (this eliminates the need for adjustments in the logging process)
ALTER TABLE LOG ADD (DB_UPDATE_TS TIMESTAMP DEFAULT SYSTIMESTAMP AT TIME ZONE 'UTC' NOT NULL);
--“Redistribute” existing log entries; otherwise, replication won't handle it well:
--Either put them all in one block (!not recommended for tables with more than 1 million entries!):
UPDATE LOG SET DB_UPDATE_TS = TIMESTAMP;
Commit;
--or for large datasets and live systems that also need to perform operations on the tables, as an anonymous PL/SQL block with minimal undo tablespace requirements:
--In this case, an index should also be created on the timestamp column to avoid full table scans!
declare begin
FOR counter IN 0 .. 3650 LOOP
--dbms_output.put_line(to_char(to_date('2010-01-01', 'YYYY-MM-DD') + counter, 'YYYY-MM-DD') || ' - ' || to_char(to_date('2010-01-01', 'YYYY-MM-DD') + 1 + counter, 'YYYY-MM-DD'));
--LOG:
update log set DB_UPDATE_TS = TIMESTAMP where TIMESTAMP between to_date('2010-01-01', 'YYYY-MM-DD') + counter and to_date('2010-01-01', 'YYYY-MM-DD') + 1 + counter;
commit;
--CHILDLOG:
update childlog set DB_UPDATE_TS = TIMESTAMP where TIMESTAMP between to_date('2010-01-01', 'YYYY-MM-DD') + counter and to_date('2010-01-01', 'YYYY-MM-DD') + 1 + counter;
commit;
END LOOP;
end;
--Create a trigger that resets the column with every update (this eliminates the need for adjustments to the logging process)
CREATE OR REPLACE
TRIGGER LOG_DB_UPDATE_TS
BEFORE UPDATE ON LOG
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
:NEW.DB_UPDATE_TS := SYSTIMESTAMP AT TIME ZONE 'UTC';
END;
/
--Create Index:
CREATE INDEX IDX_LOG_DBLU ON LOG(DB_UPDATE_TS) COMPUTE STATISTICS;
--Here's the whole thing again for Childlog:
ALTER TABLE CHILDLOG ADD (DB_UPDATE_TS TIMESTAMP DEFAULT SYSTIMESTAMP AT TIME ZONE 'UTC' NOT NULL);
--See the PL/SQL block above, if applicable!
UPDATE CHILDLOG SET DB_UPDATE_TS = TIMESTAMP;
commit;
CREATE OR REPLACE
TRIGGER CHILDLOG_DB_UPDATE_TS
BEFORE UPDATE ON CHILDLOG
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
:NEW.DB_UPDATE_TS := SYSTIMESTAMP AT TIME ZONE 'UTC';
END;
/
CREATE INDEX IDX_CHILDLOG_DBLU ON CHILDLOG(DB_UPDATE_TS) COMPUTE STATISTICS;
--FinishedMSSQL
|
|
|
The trigger for Childlog is missing. However, this is usually not needed, since entries are always only added and never updated. |
/* Add columns. Use DATETIME2 for higher precision than DATETIME. Since MSSQL does not support millisecond precision, use the SYSDATETIME() function instead of current_timestamp; this function provides nanosecond precision. */
ALTER TABLE [LOG] ADD DB_UPDATE_TS DATETIME2 DEFAULT SYSDATETIME() NOT NULL;
GO
ALTER TABLE [CHILDLOG] ADD DB_UPDATE_TS DATETIME2 DEFAULT SYSDATETIME() NOT NULL;
GO
/* Initialize columns */
BEGIN
UPDATE [LOG] SET DB_UPDATE_TS = [timestamp];
END
GO
BEGIN
UPDATE [CHILDLOG] SET DB_UPDATE_TS = [timestamp];
END
GO
/* Trigger */
CREATE TRIGGER LOG_DB_UPDATE_TS
ON [LOG]
AFTER UPDATE
AS
BEGIN
IF NOT UPDATE(DB_UPDATE_TS)
BEGIN
UPDATE t
SET t.DB_UPDATE_TS = SYSDATETIME()
FROM [LOG] AS t
INNER JOIN inserted AS i
ON t.PROCESSID = i.PROCESSID;
END
END
GO
/* Indices */
CREATE INDEX IDX_LOG_DBLU ON LOG (DB_UPDATE_TS);
CREATE INDEX IDX_CHILDLOG_DBLU ON CHILDLOG (DB_UPDATE_TS);MySQL
and for MySQL, it’s very simple:
/* Adds a sufficiently precise timestamp managed by the database to the table */
ALTER TABLE LOG ADD DB_UPDATE_TS TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
ALTER TABLE CHILDLOG ADD DB_UPDATE_TS TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)PostgreSQL
and for PostgreSQL, also via triggers:
ALTER TABLE log ADD db_update_ts timestamp NOT null DEFAULT (timezone('UTC', now()));
ALTER TABLE childlog ADD db_update_ts timestamp NOT null DEFAULT (timezone('UTC', now()));
CREATE OR REPLACE FUNCTION update_db_update_ts_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.db_update_ts = timezone('UTC', now());
RETURN NEW;
END;
$$ language 'plpgsql';
CREATE TRIGGER update_log_db_update_ts BEFORE INSERT OR UPDATE ON log FOR EACH ROW EXECUTE PROCEDURE update_db_update_ts_column();
CREATE TRIGGER update_childlog_db_update_ts BEFORE INSERT OR UPDATE ON childlog FOR EACH ROW EXECUTE PROCEDURE update_db_update_ts_column();
CREATE INDEX idx_log_dblu ON log (db_update_ts);
CREATE INDEX idx_childlog_dblu ON childlog (db_update_ts);Configuration
Data Sources
Data sources are connections to individual databases. The individual replication jobs then use/reference these. These are set up under Backend Connections as type "data_source" and referenced by their component ID.
Interface
A dedicated interface is available for setting up individual replication jobs under Settings → Replication → Components → Editor. Through this interface, entries can be created, deleted, duplicated, and also enabled or disabled individually.
Configuration Parameters of the Replication Module
The various Parameters and their associated functions are described below. These can be found under BPC Administration → Replication → General.
Unresolved include directive in modules/core/pages/admin/modules/replication.adoc - include::partial$generated/replication/module-settings.adoc[]
Configuration Parameters of a Replication Job
The various parameters and their associated functions are described below. These can be found under BPC Administration → Replication → Components. It is recommended to use the specialized interface: BPC Administration → Replication → Editor
Unresolved include directive in modules/core/pages/admin/modules/replication.adoc - include::partial$generated/replication/instance-settings.adoc[]
Basic Settings
| Setting (Key) | Data Type | Description |
|---|---|---|
Replication_Enabled |
boolean |
Enable/disable the replication job |
Replication_StartDate |
String |
Replicate records that are newer than this date. or as a relative value in the format: Examples
|
Source
| Setting (Key) | Data Type | Description | ||
|---|---|---|---|---|
Source_DataSource |
String |
Data source to be used (ID of the Backend Connections of type |
||
Source_Table |
String |
Table name of the source or name of the CTE if the optional 'Source_CommonTableExpressionQuery' is used. |
||
Source_Timezone |
String |
Time zone of the date fields used in the source database table (internally uses TimeZone.getTimeZone). Applies only to the actual data and not to the 'lastUpdateColumn' column. Examples
|
||
Source_IdColumns |
String |
Columns used to form a unique key in OpenSearch.
For example: |
||
Source_LastUpdateColumn |
String |
This column is used to determine the age of the record
|
||
Source_LastUpdateColumnTimezone |
String |
Time zone used in the source database table for the data in the
Examples
|
||
Source_QueryTimeoutInSeconds |
Integer |
Specifies how long the JDBC driver waits for a response from the database. See also JDBC java.sql.Statement.setQueryTimeout() |
||
Source_CommonTableExpressionQuery |
String |
Can be used instead of database views.
Must contain only the SELECT statement of a Common Table Expression (CTE).
The |
Target
| Setting (Key) | Data Type | Description |
|---|---|---|
Target_Index |
String |
Target index in OpenSearch. |
Target_CaseSensitivityOfFields |
String |
Specifies how the fields should be created in OpenSearch (case sensitivity).
|
Target_IndexCreationSettings |
JSON |
Assign different settings to the target index during creation.
Example with "Index Sorting" added
If "Index Sorting" is to be used, OpenSearch mappings must also be created immediately for the specified sorting fields (LASTUPDATE in the example) (see Target_IndexMappings). |
Target_IndexMappings |
JSON |
Assign a mapping to the target index upon creation. This should only be necessary in specific cases. The mapping for the “Index Sorting” example above.
|
Target_IndexDynamicTemplates |
JSON |
Assign a custom mapping to the target index. If this is set, the global setting (see Core Settings → Core_IndexDynamicTemplates) is not used. The Elasticsearch documentation (link provided until the OpenSearch documentation is on par) contains more information about the capabilities of dynamic templates. In the following example, all fields that OpenSearch recognizes as text fields (strings) are assigned a mapping (“alle_textfelder”) in which the content is not analyzed (this saves storage space, and the data can still be displayed). Plus one exception (“special_case”): For all text fields with the name suffix ‘name’, our default mapping is used. Example:
To specify the OpenSearch type for a database field. Occasionally, OpenSearch may make a mistake with the mapping and use an inappropriate type. Specific example: The Oracle column named ‘ZAHL’ with the data type ‘NUMBER(10,2)’ is created in the OpenSearch mapping as type ‘long’ instead of ‘float’. This can be corrected using the example below. Example
|
Advanced Settings / Advanced
Snapshots
During a snapshot, all documents (created after the specified 'replicationStartDate') from the OpenSearch index (see 'targetIndex') are copied to a new index at the specified time. Finally, the alias is redirected to the new index, and the previous index is deleted. This can be performed, for example, once a week to restore a lean index.
Additional note: Documents marked as deleted in OpenSearch (either manually deleted or deleted by Tail Sync) are, of course, not included in the new index. Caution: The associated replication run is suspended until the shadow copy has been created.
| Setting (Key) | Data Type | Description |
|---|---|---|
ShadowCopy_Enabled |
boolean |
Enable the creation of shadow copies. |
ShadowCopy_CronPattern |
String |
Cron-like pattern (following Quartz scheduler syntax) to specify when the shadow copies should be created. Examples
Additional examples and documentation can be found on the Quartz scheduler website. |
ShadowCopy_KeepCopiesCount |
Integer |
Number of shadow copies to be retained.
|
Tail Sync
The 'Tail Sync' operates on the current index (see 'targetIndex') and synchronizes older data (new/modified/deleted database records) with OpenSearch so that it matches the database again. This can be performed, for example, once every night. Documents older than the replication start date (see 'replicationStartDate') are first deleted from the OpenSearch index. It then goes through the database in blocks (default: 10-day increments) and synchronizes it with OpenSearch. In the process, new or modified database records are added to the OpenSearch index, and documents that no longer exist in the database are deleted from OpenSearch. Tail Sync works only with the specified 'idColumns' and 'lastUpdateColumn' fields (Hint: a suitable DB index works wonders!). The complete data is read only for new or modified database records.
As an alternative or in addition to a scheduled Tail Sync, it can also be started manually.
For this purpose, there is a button at Replication → Jobs for each replication job that starts a Tail Sync.
For manual starts, the settings replicationTailSyncEnabled and replicationTailSyncCronPattern have no effect; all other settings are taken into account.
|
The sync does not run up to the current date (see |
| Setting (Key) | Data Type | Description | ||
|---|---|---|---|---|
TailSync_Enabled |
boolean |
Enable 'Tail Sync' |
||
TailSync_CronPattern |
String |
Cron-like pattern (following Quartz scheduler syntax) to specify when Tail Sync should start. Examples
Additional examples and documentation can be found on the Quartz scheduler website. |
||
TailSync_BlockSize |
Integer |
Block size of the database driver.
Note the information regarding the |
||
TailSync_RelativeStartDate |
String |
The relative start date. Synchronization should begin from this point onward. Data that predates the replication start date will continue to be deleted. This date is only used if it falls after the regular start date and before the end date.
|
||
TailSync_RelativeEndDate |
String |
The relative end date. Synchronization should be performed up to this point and no further. |
||
TailSync_BlockDayRange |
Integer |
Number of days (block) during which the data should be processed.
See also |
||
TailSync_RelativeDeleteOlderThanDate |
String |
The relative deletion date to be specified. All documents older than this will be deleted. If this option is not set, all documents older than the replication start date will be deleted (see |
||
TailSync_LoggingEnabled |
boolean |
The relative deletion date to be specified.
Specifies whether Tail Sync runs in the Additionally, the global setting |
Consistency Check
A simple consistency check is performed after the replication runs. This compares the number of documents in the source and the destination. Specifically, this comparison covers the period between 'replicationStartDate' and the latest date present in the destination (OpenSearch).
|
With large amounts of data (tens of millions of records), the system may experience increased load, and the initial replication may be significantly slowed down. Enable the consistency check only if you are certain that the data will be fully replicated. |
| Setting (Key) | Data Type | Description |
|---|---|---|
ConsistencyCheck_Frequency |
Integer |
The frequency at which the consistency check is performed. Examples:
|
Lookup Joins
Can be used to enrich documents to be replicated with additional data. For example, if the data to be replicated contains only a partner ID and the partner’s name, etc., is still required in the monitor.
OpenSearch uses denormalization, which means this data is included in the document and is available just like all other data (high-performance search, aggregation, etc.). Prerequisite: The lookup “tables” must be available as separate indexes and can be imported, for example, via an additional replication from a database table.
The lookup data can be updated manually (BPC Settings → Overview → Status → Replication → Jobs → Job → Synchronize Lookup Joins) or automatically using our OpenSearch BPC plugin (os-BPC-plugin).
Multiple lookup tables can be referenced; the possible values for an ENTRY are described below. Structure: "join": [ { ENTRY }, { ENTRY }, … ]
|
For the value comparison to work, the column types in the database tables must be identical. For string fields, “*.raw” should be used as the lookupKeyField; for numeric fields, however, use the field name without the “.raw” suffix |
[
{
"keyField": "PARTNER",
"lookupIndex": "lookup-partner",
"lookupKeyField": "ID.raw",
"resultFieldsPrefix": "partner_",
"resultFieldsExcluded": [ "ID", "LASTUPDATE" ]
},
{
"keyField": "MESSAGETYPE",
"lookupIndex": "lookup-messagetype",
"lookupKeyField": "ID.raw",
"resultFieldsPrefix": "messagetype_",
"resultFieldsExcluded": [ "ID", "LASTUPDATE" ]
}
]| Field | Data Type | Description |
|---|---|---|
keyField |
String |
The key field in the table to be replicated. The data is retrieved from the lookup index based on the value of this field. Example: PARTNER_ID |
keyFieldValuesSeparator |
String |
If the keyField contains multiple values separated by a delimiter, this delimiter can be specified here. The lookup join is then performed on the individual values. Example: The 'lookupIndex' contains the following data:
If the keyFieldValuesSeparator is '%%', the resultFieldsPrefix is 'MENGENEINHEIT_', and the passed keyValue is '42%%20%%30', then the following field is created: MENGENHEIT_LONGNAME = Zentimeter%%Gramm%%Kilogramm |
lookupIndex |
String |
The OpenSearch index containing the lookup data. Example: lookup-partner |
lookupKeyField |
String |
Example: ID.raw |
resultFieldsPrefix |
String |
The fields to be retrieved from the lookup index must be prefixed with a unique prefix to avoid conflicts with existing fields. Example: partner_ |
resultFieldsIncluded |
String Array |
If not all fields from the lookup index are to be included, the fields to be included can be specified here. Example: [ "FIRST_NAME", "LAST_NAME" ] |
resultFieldsExcluded |
String Array |
If almost all fields are to be included, the fields to be excluded can be specified here. Example: [ "ID", "UPDATED" ] |
Logging Replications
Replication job runs can be logged and displayed, for example, via the automatically created “Replication Jobs Monitor.”
Similarly, tail sync runs can also be logged and viewed in the “Tail Sync Logs Monitor.”
These monitors are created at startup if they do not already exist.
They cannot be permanently deleted.
Likewise, the indexes bpc-replicationjobs-log and bpc-tailsync-log are automatically created when a run needs to be logged.
Logging can be enabled or disabled globally as well as for each replication job (see the corresponding settings above). By default, logging is enabled globally for both replications and tail syncs, but disabled in the individual replication jobs. Therefore, logging is not enabled by default.
The log entries for replication runs are written in bulk to the aforementioned index after some time. This has little impact on the system’s overall performance. However, a very large number of entries can be generated in a very short time (millions in a few hours), so you might want to run the cleanup often enough and keep an eye on the number of documents it contains: Settings → Core Services → Indexes
Since tail syncs occur much less frequently, the bpc-tailsync-log index should not grow too quickly during tail sync logging; nevertheless, it is regularly purged of old entries (see configuration above).
