By default, data replicated from existing tables will automatically create the same schema at the replicated database. The targeted database schema of replication needs to be flexible and customized in certain situations, such as:
- An existing schema that the replicated data need to fit in
- 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
Customizing target database schema can all be done by using table maps in your dynamic share or bulk share.
Prerequisites
You will need to install DataSync for ServiceNow, run the Finish Install scripts, and set your initial Perspectium & Replicator properties.
Procedure
To customize your target database schema using table maps, complete the procedures below:
Define a table map with schema support
Log into your sharing ServiceNow instance and navigate to Perspectium > Control and Configuration > Table Maps. Select the table map you want to use, in this example, select Incident to Common Incident. Then, check the Generate schema box.
NOTE:
- Checking the Generate schema box is required in the initial sharing of your records.
- 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 database table to be created and queried for updates and deletes.
- The value specified in the Target table name field will be used as the name of the table in the database.
WARNING: If there are multiple table maps with the same Target table name, the agent will return the first record when querying against ServiceNow for the schema.
Share the table map output
Create a dynamic share or bulk share and select the table map you altered in the table map field. The generated payload can be subscribed by an agent and the schema created dynamically.
The common incident example will result in the following table schema to be created if consumed by a database 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)
NOTE: 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.
