Page History
To enhance your DataSync integration for Snowflake, you can configure the Snowflake Bulk Load Meshlet with the additional features available below!
Anchor | ||||
---|---|---|---|---|
|
Panel | ||||||
---|---|---|---|---|---|---|
| ||||||
|
Add custom fields to Snowflake
There are currently 3 types of custom columns supported: IO insert columns, IO update columns, and a string field populated with the ServiceNow instance key. IO insert fields will be populated with theYou can add custom fields to Snowflake database tables to represent actions take by the meshlet as follows:
- IO insert columns - Columns populated with the timestamp of the record insert into Snowflake
- IO update
- columns - Columns populated with the timestamp of the record insert into Snowflake and updated subsequently on record update into Snowflake
- key column - A string field populated with the ServiceNow instance's key where this record came from. This field is based on the key field of the Perspectium Message (which by default populates with your ServiceNow instance's name such as acmeqa, dev12345, etc.) that is sent from the ServiceNow app into the Integration Mesh and consumed by the meshlet.
- IO delete column - Column populated with the timestamp when the meshlet receives a message indicating a record is to be deleted (i.e. the record has been deleted from ServiceNow and this is being replicated over to Snowflake). The record will not be deleted from the Snowflake database table. See Soft Deletes for more information.
To add your custom column names in your Snowflake tables, you can configure the databaseConfig.json file by following these steps:
UI Steps | ||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||||||||||||||||||||||
|
UI Text Box | ||
---|---|---|
| ||
By default, IO columns will behave as they do in ServiceNow; for rows inserted into Snowflake tables with a custom IO update column, the IO update column will be populated with the insert timestamp. This setting can be turned off with updateTimeOnInsert yml configuration. If the table does not exist in Snowflake, the table will be created with the custom columns when the meshlet is run. If the table already exists, the table will be altered to add the custom columns into the table. |
Compare tables from ServiceNow to Snowflake
Table Compare allows you to compare tables from one ServiceNow instance to Snowflake. This is useful because you can see each table's record count, as well as a list of record discrepancies by sys_id between the two tables you're comparing—in other words, you can see which records exist in one table but not the other. To get started with comparing your ServiceNow tables to Snowflake, see Table Compare: ServiceNow to database table compare.
NOTE: This requires version Helium and above Perspectium Core update set.
Load Schema Files
This feature allows you to load your ServiceNow table schemas to the meshlet when a connection cannot be made to the originating ServiceNow instance. Schema files are used to create and update tables in Snowflake. Enabling this feature will force the meshlet to read schemas via files exclusively from the local file storage and disable all calls to ServiceNow. Any new tables shared will require table schemas to be added to the local file storage.
UI Steps | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
| |||||||||||
|
Azure External Storage
To start using Microsoft Azure as an external stage for bulk loading, follow these steps:
UI Steps | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||||||||||||||
|
Soft Deletes
Anchor SnowflakeSoftDeletes SnowflakeSoftDeletes
This feature allows you to add columns to a database table that gets updated when the meshlet receives a message indicating a record is to be deleted. The meshlet will update said columns appropriately and will not delete the record in Snowflake.
NOTE: At least one of the following table columns are required to achieve soft deletes, if neither column has been added to the configurations but softDelete is set to true, no delete will occur. This is to prevent deletion of records when there is an intention of using soft deletes.
UI Steps | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
| ||||||||||||
|