(Glue-2305) 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^TM. Drop table procedure may be called on tables not created by SNP Glue^TM.

Create an SNP Glue^TM table in the specified source, name, and table definition.

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

Alter an SNP Glue^TM table in the specified source, name, and table definition

call core.alter_glue_table(<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^TM table by specifying the 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

Start the merge on a specific source using its settings.

Start merge by providing all the necessary information.

Resumes all merges inside a source. This only applies to MERGE_<table> tasks and not the 'START_CONTROL' task. Call ‘CORE.RESUME_CONTROL’ for resuming the 'START_CONTROL’ task.

Resumes merge on a specific table.

Do not call in a loop! In need of resuming multiple merges, provide 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.

Resume ‘START_CONTROL’ task.

Suspend the ‘START_CONTROL’ task.

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.
metadata_table → SNP Glue^TM table that specifies which tables were replicated.
stream_name → Name of the stream to be created on the metadata table.
historical_tab_suffix → Suffix of the table the replicated table is going to be merged into.
control_schedule_time → Defines a period how often 'START_CONTROL' task should run.
control_schedule_type → Defines the time unit for the 'control_schedule_time'.
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'.

merge_status → Flag if a merge was initiated and is set by the ‘CORE.START_MERGE' procedure. Not present in 'DEFAULT’ settings.

control_status → Flag if the 'START_CONTROL' task exists, is running, or is suspended. Not present in 'DEFAULT’ settings.

Application settings:

app_name → Name of the application.

Source management

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

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

Cleanup

Remove control drops 'START_CONTROL' task, thus preventing from starting merges on newly replicated tables.

Drop an SNP Glue^TM by specifying its source, type (view, stream, or task), and its 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