(Glue-2405) App API

Table of Contents:

1 Table management
2 Merge
3 Settings
4 Database management
5 Source management
6 Housekeeping
7 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.

To truncate a table specify database, source and table name. Backup table is created from the merge table as <table name>_BAK_<current timestamp> e.g. ZZVBAK_BAK_20230606084229, and then both merge and staging tables are truncated.

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

To move external, meaning not managed by the app, tables into the application run following command.

Merge

Resumes all merges inside a source.

Resumes merge on a specific table.

Resumes merge on an array of tables.

Suspends all merges inside a source.

Suspends merge on a specific table.

Suspend merge on an array of tables.

Alter merge schedule to current schema settings.

call core.alter_merge_schedule(<Database>, <Source>);

Alter merge schedule for specific tables.

To re-deploy merge objects, merge task and view.

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.

Database management

Create new database for the application.

Drop table and everything within. All objects will be gracefully deleted.

Source management

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

Remove source, if it exists.

Housekeeping

You can pause and resume housekeeping task by executing:

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