(Glue-2311) App API

Table of Contents:

1 Table management
2 Merge
3 Settings
4 Source management
5 Cleanup

Table management

Both create and alter table procedures should only be called from SNP Glue™. Drop table procedure may be called on tables not created by SNP Glue™.

Create an SNP Glue™ table in the specified database, source, name, and table definition.

call core.create_glue_table(<Database>, <Source>, <Table>, <Table definition>);

Alter an SNP Glue™ table in the specified database, source, name, and table definition.

call core.alter_glue_table(<Database>,<Source>, <Table>, <Table definition>);

Table definition formatting:
{
-- Example of column definition, each column should have said definition
{"fieldname": "<fieldname>", "type": <type>, "key": <true/false>, "comment": "<comment>" }
}
-- Fieldname = Name of the column
-- Type      = Data type of the column
-- Key       = If the column is part of the primary key or not
-- Comment   = Column comment

Drop an SNP Glue™ table by specifying the database and source where it is located and its name. Its merge-related artifacts are dropped as well → stream, view, task, and historical table. Before the historical table is deleted it is backed up as <table name>_BAK_<current timestamp> e.g. ZZVBAK_BAK_20230606084229.

This action may lead to data loss! Use with caution.

Merge

Resumes all merges inside a source. This only applies to MERGE_<table> tasks.

Resumes merge on a specific table.

Do not call in a loop! In need of resuming multiple merges, provide an array of tables instead of calling the stored procedure one by one.

Resumes merge on an array of tables.

Suspends all merges inside a source. This only applies to MERGE_<table> tasks and not the START_CONTROL task. Call CORE.SUSPEND_CONTROL for suspending the START_CONTROL task.

Suspends merge on a specific table.

Suspend merge on an array of tables.

Settings

Get settings of specified scope → APP for application settings, DEFAULT for default (template) source settings, <Source> for specific source settings.

Update settings overwrite existing settings with the same key and appended new ones.

Set settings completely overwrite all existing values.

List of all source settings:

warehouse: Warehouse under which merge task runs.
historical_tab_suffix: Suffix of the table the replicated table is going to be merged into.
merge_schedule_time: Defines a period of how often merge tasks should run.
merge_schedule_type: Defines the time unit for the merge_schedule_time.
insert_flag: Defines what identifier in metadata should represent inserts, can insert multiple values if they are separated by a comma, use '<empty>' placeholder for space
update_flag: Defines what identifier in metadata should represent updates, can insert multiple values if they are separated by a comma, use '<empty>' placeholder for space
delete_flag: Defines what identifier in metadata should represent deletes, can insert multiple values if they are separated by a comma
retention_period: Retention period for backups created after table deactivation in days
metadata_timestamp_field: Name of the field used to determine the last record of a specific key
metadata_change_field: Type of change of specific field

Application settings:

app_name: Name of the application.

Source management

Register a new source. It creates a new schema if it does not already exist and sets default source settings.

Remove source drops source, its schema, if it exists.

Cleanup

Drop an SNP Glue™ by specifying its source, type (view, stream, or task), and name.

By not specifying the object name, all objects of said type will be dropped in the specified source.

Drop Glue tasks

Drop Glue views

Drop Glue streams