(Glue-2408) App API

Table of Contents:

Table management

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.

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

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.

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

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

call core.copy_external_table(<Original_DB>, <Original_Schema>, <Table>, <App_DB>, <App_Source>);

Merge

Resumes all merges inside a source.

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

Resumes merge on a specific table.

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

Resumes merge on an array of tables.

call core.resume_merge(<Database>, <Source>, <Array_of_tables>);

Suspends all merges inside a source.

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

Suspends merge on a specific table.

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

Suspend merge on an array of tables.

call core.suspend_merge(<Database>, <Source>, <Array_of_tables>);

Alter merge schedule to current schema settings.

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

Alter merge schedule for specific tables.

call core.alter_merge_schedule(<Database>, <Source>, <Table>); -- Single table
call core.alter_merge_schedule(<Database>, <Source>, <Array_of_tables>); -- Multiple tables

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

call core.deploy_merge(); -- All tables
call core.deploy_merge(<Database>); -- All tables within one database
call core.deploy_merge(<Database>, <Source>); -- All tables within one schema
call core.deploy_merge(<Database>, <Source>, <Table>); -- One table

Settings

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

call core.get_settings(<Scope>);

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

call core.update_merge(<Scope>, to_variant(parse_json('<Settings in json formatting>')));

Example: call core.update_settings('<Scope>',  to_variant(parse_json('
                                              {"warehouse": "<Warehouse>",
                                               "merge_schedule_time": "<Merge schedule time>"})));

Set settings completely overwrite all existing values.

call core.set_settings(<Source>, to_variant(parse_json('<Settings in json formatting>')));

Example: call core.set_settings('<Source>','{"warehouse": "<Warehouse>",
                                             "merge_schedule_time": "<Merge schedule time>"});

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.

call core.create_db(<Database>)

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

call core.delete_db(<Database>);

Source management

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

call core.register_source(<Database>, <Source>)

Remove source, if it exists.

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

Housekeeping

You can pause and resume housekeeping task by executing:

call core.resume_housekeeping();
call core.suspend_housekeeping();

Cleanup

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

call core.drop_glue_object(<Database>, <Source>, <Type>, <Object>);

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

call core.drop_glue_object(<Database>, <Source>, <Type>);
call core.drop_glue_object(<Database>, <Source>);

Drop Glue tasks

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

Drop Glue views

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

Drop Glue streams

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