User Tools

Site Tools


replicate_table_map

Custom Target Database Schema using Table Maps

v3.25.0

By default, data replicated from existing tables will automatically create the same schema at the replicated database. In certain situations, the targeted database schema of replication needs to be flexible and customized:

  • There is an existing schema that the replicated data need to fit in
  • There is a need to rename the field names or transform the field values using script at the source
  • The resulting field types need to be modified to a specific type or field length

This can all be done now using Outbound Table Maps in your Dynamic Share or Bulk Share.

Example

The following example takes you through replicating a common_incident schema populated with values from incident.

Defining a Table Map with Schema Support

Think of Outbound Table Maps as a way to create an output of formatted XML or JSON payload, kind of like an “Export Set” in ServiceNow. In a nutshell, you can select a single table or a Database Views (joined tables) as your source table in a Table Map and select fields or use scripts to generate the target fields.

The following example uses the Jira common_incident Table Map with Generate Schema selected, overriding the caller_email output field to 100 character length, and lastly specifying the number field as the “GUID”.

Note that specifying Field Type and Field Length is optional except for designating at least one field as “Sys ID (GUID)”. This is required for the target DB table to be created and queried for updates and deletes. The default Field Type is “string” and the default Field Length is “1000”

Accessing the Schema for a Table Map

In this example of common_incident if there exists a Table Map with the type “common_incident” and the Generate schema checkbox checked (true), when you browse to the following URL while authenticated, you will get the generated schema for the Table Map.

https://<instance name>.service-now.com/common_incident.do?PSP_SCHEMA
<common_incident>
    <element name="caller_full_name" internal_type="string" max_length="1000" choice_list="false"/>
    <element name="caller_email" internal_type="string" max_length="100" choice_list="false"/>
    <element name="caller_id" internal_type="string" max_length="1000" choice_list="false"/>
    <element name="category" internal_type="string" max_length="1000" choice_list="false"/>
    <element name="work_notes" internal_type="journal_input" max_length="4000" choice_list="false"/>
    <element name="attachments" internal_type="string" max_length="1000" choice_list="false"/>
    <element name="correlation_display" internal_type="string" max_length="100" choice_list="false"/>
    <element name="priority" internal_type="string" max_length="1000" choice_list="false"/>
    <element name="description" internal_type="string" max_length="4000" choice_list="false"/>
    <element name="short_description" internal_type="string" max_length="160" choice_list="false"/>
    <element name="number" internal_type="GUID" max_length="40" choice_list="false"/>
    <element name="correlation_id" internal_type="string" max_length="100" choice_list="false"/>
    <element name="comments" internal_type="journal_input" max_length="4000" choice_list="false"/>
    <element name="state" internal_type="string" max_length="1000" choice_list="false"/>
</common_incident>

Sharing Out Table Map Output

After the Table Map is defined and assigned into a a Dynamic or Bulk Share to use, the generated payload can be subscribed by an Agent, and the schema created dynamically. See Outbound Table Maps for more information on how to configure.

Resulting Database Schema

The common_incident example will result in the following table schema to be created if consumed by a DB agent for MySQL.

mysql> describe common_incident;
+---------------------+--------------+------+-----+---------+-------+
| Field               | Type         | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+-------+
| state               | mediumtext   | YES  |     | NULL    |       |
| short_description   | varchar(160) | YES  |     | NULL    |       |
| description         | mediumtext   | YES  |     | NULL    |       |
| priority            | mediumtext   | YES  |     | NULL    |       |
| attachments         | mediumtext   | YES  |     | NULL    |       |
| category            | mediumtext   | YES  |     | NULL    |       |
| correlation_id      | varchar(100) | YES  |     | NULL    |       |
| number              | varchar(40)  | YES  |     | NULL    |       |
| correlation_display | varchar(100) | YES  |     | NULL    |       |
| caller_id           | mediumtext   | YES  |     | NULL    |       |
| caller_email        | varchar(100) | YES  |     | NULL    |       |
| caller_full_name    | mediumtext   | YES  |     | NULL    |       |
| comments            | mediumtext   | YES  |     | NULL    |       |
| sys_id              | varchar(224) | NO   | PRI |         |       |
| work_notes          | mediumtext   | YES  |     | NULL    |       |
+---------------------+--------------+------+-----+---------+-------+
15 rows in set (0.00 sec)

Notice that even though we specified the number field as the required “Sys ID (GUID)” field in the Table Map, the number field does not become the GUID. Instead, the sys_id field is created as GUID (PRI key) and contains number values to be used for lookup during updates/deletes.

This is consistent with replicating Database Views.

replicate_table_map.txt · Last modified: 2017/07/20 00:11 by paul