Replication and Materialization Procedures

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:

Parameter	Description
`dwh_table`	Arbitrary name of the history table in the data warehouse
`source_table`	Table 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:

Parameter	Description
`dwh_table`	Arbitrary name of the history table in the data warehouse
`source_table`	Table name in the source
`keyColumnsArray`	Defined as an object, but this is actually an array containing the keys in the source table
`columnsToCheckArray`	Array 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:

Parameter	Description
`targetTable`	Target table schema and name
`sourceTable`	Source 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