{"type":"doc","content":[{"type":"paragraph","content":[{"text":"Table of Contents:","type":"text","marks":[{"type":"strong"}]}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{"minLevel":{"value":"1"},"maxLevel":{"value":"6"},"outline":{"value":"false"},"type":{"value":"list"},"printable":{"value":"false"}},"macroMetadata":{"macroId":{"value":"3eff9c76-e5e3-4a82-8f01-4c770bc2489f"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"ec937959-e446-40a2-b047-df982ef6a398"}},{"type":"heading","attrs":{"level":2},"content":[{"text":"Table management","type":"text"}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"Both create and alter table procedures should only be called from SNP Glue","type":"text"},{"text":"TM","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]},{"text":". Drop table procedure may be called on tables not created by SNP Glue","type":"text"},{"text":"TM","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]},{"text":".","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Create an SNP Glue","type":"text"},{"text":"TM","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]},{"text":" table in the specified source, name, and table definition.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.create_glue_table(, ,

);","type":"text"}]},{"type":"paragraph","content":[{"text":"Alter an SNP Glue","type":"text"},{"text":"TM","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]},{"text":" table in the specified source, name, and table definition","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.alter_glue_table(,

);","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"Table definition formatting:\n{\n-- Example of column definition, each column should have said definition\n{\"fieldname\": \"\", \"type\": , \"key\": , \"comment\": \"\" }\n}\n-- Fieldname = Name of the column\n-- Type = Data type of the column\n-- Key = If the column is part of the primary key or not\n-- Comment = Column comment ","type":"text"}]},{"type":"paragraph","content":[{"text":"Drop an SNP Glue","type":"text"},{"text":"TM","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]},{"text":" 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

_BAK_ e.g. 'ZZVBAK_BAK_20230606084229'.","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelIconText":":warning:","panelColor":"#FFEBE6","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"This action may lead to data loss! Use with caution.","type":"text"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.drop_glue_table(,

);","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Merge","type":"text"}]},{"type":"paragraph","content":[{"text":"Start the merge on a specific source using its settings.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.start_merge();","type":"text"}]},{"type":"paragraph","content":[{"text":"Start merge by providing all the necessary information.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"core.start_merge(, \n ,\n ,\n ,\n ,\n ,\n , \n 'MINUTE',\n , \n 'MINUTE');","type":"text"}]},{"type":"paragraph","content":[{"text":"Resumes all merges inside a source. This only applies to MERGE_

tasks and not the 'START_CONTROL' task. Call ‘CORE.RESUME_CONTROL’ for resuming the 'START_CONTROL’ task.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.resume_merge(); ","type":"text"}]},{"type":"paragraph","content":[{"text":"Resumes merge on a specific table.","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelIconText":":warning:","panelColor":"#FFEBE6","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"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.","type":"text"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.resume_merge(,

);","type":"text"}]},{"type":"paragraph","content":[{"text":"Resumes merge on an array of tables.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.resume_merge(, );","type":"text"}]},{"type":"paragraph","content":[{"text":"Suspends all merges inside a source. This only applies to MERGE_

tasks and not the ‘START_CONTROL' task. Call ‘CORE.SUSPEND_CONTROL’ for suspending the 'START_CONTROL’ task.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.suspend_merge();","type":"text"}]},{"type":"paragraph","content":[{"text":"Suspends merge on a specific table.","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelIconText":":warning:","panelColor":"#FFEBE6","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Do not call in a loop! In need of suspending multiple merges, provide array of tables instead of calling the stored procedure one by one.","type":"text"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.suspend_merge(,

);","type":"text"}]},{"type":"paragraph","content":[{"text":"Suspend merge on an array of tables.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.suspend_merge(, );","type":"text"}]},{"type":"paragraph","content":[{"text":"Resume ‘START_CONTROL’ task.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.resume_control();","type":"text"}]},{"type":"paragraph","content":[{"text":"Suspend the ‘START_CONTROL’ task.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.suspend_control();","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Settings","type":"text"}]},{"type":"paragraph","content":[{"text":"Get settings of specified scope → 'APP' for application settings, 'DEFAULT' for default (template) source settings, for specific source settings.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.get_settings();","type":"text"}]},{"type":"paragraph","content":[{"text":"Update settings overwrite existing settings with the same key and appended new ones.","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.update_merge(, to_variant(parse_json('')));\n\nExample: call core.update_settings('', to_variant(parse_json('\n {\"warehouse\": \"\",\n \"metadata_table\": \"\",\n \"stream_name\": \"\"})));","type":"text"}]},{"type":"paragraph","content":[{"text":"Set settings completely overwrite all existing values.","type":"text"}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"Not specified settings already present for the scope will be lost!","type":"text"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.set_settings(, to_variant(parse_json('')));\n\nExample: call core.set_settings('','{\"warehouse\": \"\",\n \"metadata_table\": \"\",\n \"stream_name\": \"\"});","type":"text"}]},{"type":"paragraph","content":[{"text":"List of all source settings:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"warehouse → Warehouse under which merge task runs.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"metadata_table → SNP Glue","type":"text"},{"text":"TM","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]},{"text":" table that specifies which tables were replicated.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"stream_name → Name of the stream to be created on the metadata table.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"historical_tab_suffix → Suffix of the table the replicated table is going to be merged into.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"control_schedule_time → Defines a period how often 'START_CONTROL' task should run.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"control_schedule_type → Defines the time unit for the 'control_schedule_time'.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"merge_schedule_time → Defines a period of how often merge tasks should run.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"merge_schedule_type → Defines the time unit for the 'merge_schedule_time'.","type":"text"}]}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"merge_status → Flag if a merge was initiated and is set by the ‘CORE.START_MERGE' procedure. Not present in 'DEFAULT’ settings.","type":"text"}]}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelIconText":":warning:","panelColor":"#FFEBE6","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Do not set ","type":"text"},{"text":"control_status","type":"text","marks":[{"type":"strong"}]},{"text":" manually! ","type":"text"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"control_status → Flag if the 'START_CONTROL' task exists, is running, or is suspended. Not present in 'DEFAULT’ settings.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Application settings:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"app_name → Name of the application.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Source management","type":"text"}]},{"type":"paragraph","content":[{"text":"Register a new source. It creates a new schema if it does not already exists and ","type":"text"},{"text":"set default","type":"text","marks":[{"type":"strong"}]},{"text":" source settings.","type":"text"}]},{"type":"panel","attrs":{"panelType":"warning"},"content":[{"type":"paragraph","content":[{"text":"In case an already existing source is registered again, its settings will be overwritten, and objects inside it will not be affected.","type":"text"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.register_source()","type":"text"}]},{"type":"paragraph","content":[{"text":"Remove source drops source, its schema, if it exists. ","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelIconText":":warning:","panelColor":"#FFEBE6","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"This action may lead to data loss! Use with caution.","type":"text"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.remove_source();","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Cleanup","type":"text"}]},{"type":"paragraph","content":[{"text":"Remove control drops 'START_CONTROL' task, thus preventing from starting merges on newly replicated tables.","type":"text"}]},{"type":"panel","attrs":{"panelType":"info"},"content":[{"type":"paragraph","content":[{"text":"The ‘START_CONTROL' task can be recreated by running the 'CORE.START_MERGE’ stored procedure.","type":"text"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.remove_control();","type":"text"}]},{"type":"paragraph","content":[{"text":"Drop an SNP Glue","type":"text"},{"text":"TM","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]},{"text":" by specifying its source, type (view, stream, or task), and its name.","type":"text"}]},{"type":"panel","attrs":{"panelType":"info"},"content":[{"type":"paragraph","content":[{"text":"This stored procedure cannot drop tables. Use 'CORE.DROP_GLUE_TABLE' for dropping tables.","type":"text"}]}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"call core.drop_glue_object(, ,

Browser not supported