Skip to main content
Skip table of contents

Replication and Materialization Procedures

You are looking at an older version of the documentation. The latest version is found here.

These procedures relate to replication and materialization.

SYSADMIN.batchUpdate

SQL
SYSADMIN.batchUpdate(IN dwh_table string, IN source_table string, IN identity_expression string)

It takes the following parameters:

ParameterDescription
dwh_tableArbitrary name of the history table in the data warehouse
source_tableTable name in the source
identity_expression Identity field name or identity expression in SQL used to determine whether existing data should be corrected in the Analytical Storage

Example

SQL
CALL SYSADMIN.BatchUpdate('"dwh"."batchtest"', '"test_tables_or"."salesperson"', 'SALESPERSONID')

SYSADMIN.historyUpdate

This system procedure returns the number of records that have been added/modified in the history table (i.e. the total number of records in the source that have been added/edited/deleted).

SQL
SYSADMIN.historyUpdate(IN dwh_table string, IN source_table string, IN keyColumnsArray object, IN columnsToCheckArray object)

It takes the following parameters:

ParameterDescription
dwh_tableArbitrary name of the history table in the data warehouse
source_tableTable name in the source
keyColumnsArrayDefined as an object, but this is actually an array containing the keys in the source table
columnsToCheckArrayArray containing the columns to be checked if something has changed

Example

SQL
CALL SYSADMIN.historyUpdate('dwh.historyUpdateSCD2', 'my_ds.scd2',array('Supplier_Key_1', 'Supplier_Key_2'), array('Supplier_State', 'Supplier_Name')) ;;

SYSADMIN.getReplicatedTableDiff

This procedure creates ALTER COLUMN queries to run over the target table as a native query or directly in the source DBMS to conform to the source table.

SQL
SYSADMIN.getReplicatedTableDiff(IN targetTable string NOT NULL, IN sourceTable string NOT NULL, IN alterColumnTemplate string, OUT alterQuery string)

It takes the following parameters:

ParameterDescription
targetTableTarget table schema and name
sourceTableSource table schema and name
alterColumnTemplate

Alter column template; optional. If not specified, the default value is used (default: PostgreSQL)

For different DBMS, there are different alterColumnTemplate formats:

DBMS alterColumnTemplate format
Microsoft SQL Server ALTER TABLE %s ALTER COLUMN %s %s
MySQL ALTER TABLE %s MODIFY %s %s
Oracle ALTER TABLE %s MODIFY ("%s" %s
Snowflake ALTER TABLE %s ALTER COLUMN "%s" SET DATA TYPE %s
Vertica ALTER TABLE %s ALTER COLUMN "%s" SET DATA TYPE %s

Example

SQL
BEGIN
    LOOP ON (SELECT * FROM (CALL SYSADMIN.getReplicatedTableDiff('dwh.target_table_name', 'ds.source_table_name')) AS z) AS cc
	BEGIN
        CALL dwh.native(cc.alterQuery);
    END
END ;;
CALL SYSADMIN.refreshDataSource('dwh') ;;

SYSADMIN.refreshMatView

deprecated

SYSADMIN.refreshMatViewRow

deprecated


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.