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
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
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).
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
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.
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
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