Setting Up Data Ingestion for Your ServiceNow Data¶
This topic describes how to set up data ingestion for the Snowflake Connector for ServiceNow.
Note
The Snowflake Connector for ServiceNow ingests data from ServiceNow tables into Snowflake. Data ingestion
depends on v2
of the ServiceNow table API.
Strategies for Ingesting ServiceNow Tables¶
Note
The connector can only ingest tables with
sys_id
columns present.ServiceNow views are not supported. Instead of ingesting these views, you should synchronize all tables for the underlying view and join the synchronized tables in Snowflake.
The connector uses different ingestion strategies, depending on the table schema. The connector uses three ingestion modes:
The initial load of data occurs for each table when the table is enabled for synchronization.
In this mode, the table is ingested by iterating through the records identified by the IDs in the
sys_id
column. Once all records are ingested, the initial load phase is completed.Incremental updates occur only for tables with
sys_updated_on
orsys_created_on
columns.Incremental updates begin after the initial load is done and occur on a regular schedule that you can configure. In this mode, the connector ingests only the records that were added, updated or deleted since last synchronization.
For tables that do not have
sys_updated_on
orsys_created_on
columns, the connector uses truncate and load mode.In this mode, the table is always ingested using the initial load approach, and newly ingested data replaces the old data. The connector replaces the data by running the
INSERT OVERWRITE
command.
Note
In the “incremental updates” mode, the connector uses the
sys_updated_on
column, if that column is present.If the column is not present, the connector uses the
sys_created_on
column instead.For rotated tables, the connector always uses the
sys_created_on
column. If the table is rotated using a different column thansys_created_on
, the ingestion of that table might cause performance issues.
Parallel Ingestion of ServiceNow Tables¶
The connector ingests a few tables in parallel, but the ingestion of each individual table is a synchronous process. This means that ingesting large tables might block the connector from updating other tables. This issue is more likely to occur during the initial load phase than in the other phases.
Note
If the
sys_updated_on
orsys_created_on
fields are not updated when the record is modified in ServiceNow, those modifications won’t be propagated to Snowflake, which results in data inconsistency. Snowflake recommends that you avoid disabling the update of system fields.If a record deletion is not audited, information about deleted records won’t be propagated to Snowflake, resulting in a data inconsistency.
Note
Due to restrictions on the Snowflake and ServiceNow REST APIs, the connector cannot ingest a table if a single row exceeds 10 MB of data. In that case, the connector tries to ingest data with the frequency defined in the table schedule. If a row exceeds the limit, the connector generates an error message and continues ingesting another table.
Setting Up Data Ingestion Using Snowsight¶
To set up data ingestion using Snowsight, do the following:
Sign in to Snowsight as a user with the ACCOUNTADMIN role.
In the left navigation, select Marketplace.
Search for the Snowflake Connector for ServiceNow, then select the tile for the connector.
In the page for the Snowflake Connector for ServiceNow, select Select Tables.
This displays a list of all the ServiceNow tables.
Note
The connector can only ingest tables with
sys_id
columns present.Select the tables you want to ingest:
Search for the table you want to ingest.
Select the checkbox in the Status column next to the table you want to select.
Under Synch Schedule, select how frequently you want to synchronize the table between Snowflake and ServiceNow.
Repeat these steps for each table you want to ingest into Snowflake.
Select the heading of the Status column to see the tables you have currently selected.
Select Start Ingestion to begin ingesting data into your Snowflake account.
The connector status changes to Loading Data. When at least one of the tables is ingested successfully, the connector status changes to Last Successful Load: just now.
Refer to Monitoring the Connector for information on how to view the contents of the tables in Snowflake.
Modifying Data Ingestion Using Snowsight¶
To modify the ServiceNow tables to be ingested or the synchronization schedule for the tables, do the following:
Sign in to Snowsight as a user with the ACCOUNTADMIN role.
In the left navigation, select Marketplace.
Search for the Snowflake Connector for ServiceNow, then select the tile for the connector.
In the page for the Snowflake Connector for ServiceNow, select Edit.
Modify the tables you want to ingest:
Search for the table you want to ingest.
Select the checkbox in the Status column next to the table you want to select or deselect.
Under Synch Schedule, select how frequently you want to synchronize the table between Snowflake and ServiceNow.
Select Update.
Setting Up Data Ingestion Using SQL Statements¶
To set up data ingestion using SQL Statements, do the following:
Note
To configure these settings, you use stored procedures that are defined in the PUBLIC schema of the database that serves as an instance of the connector.
Before calling these stored procedures, select that database as the database to use for the session.
For example, if that database is named my_connector_servicenow
, run the following command:
USE DATABASE my_connector_servicenow;
Specifying the Synchronization Schedule¶
The Snowflake Connector for ServiceNow synchronizes data from all ServiceNow tables to Snowflake on a specified schedule. By default, all of the tables are synchronized once every hour (1h).
To change the default synchronization schedule for all tables, call the CONFIGURE_CONNECTOR
stored procedure
with the following arguments:
CALL CONFIGURE_CONNECTOR('data_ingestion_schedule', '<schedule>');
Where:
data_ingestion_schedule
(the string literal)Specifies that you want to configure the schedule for synchronization.
schedule
Specifies the frequency of the synchronization. You can specify one of the following string values:
'30m'
'1h'
'3h'
'6h'
'12h'
'1d'
The connector also allows you to specify a different schedule for each table that is enabled for
synchronization. To change the synchronization schedule for a selected set of tables, call the
CONFIGURE_CONNECTOR_TABLES
stored procedure with the following arguments:
CALL CONFIGURE_CONNECTOR_TABLES('schedule_interval', '<schedule>', '<table_names>');
Where:
schedule
Specifies the frequency of the synchronization. You can specify one of the following string values:
'30m'
'1h'
'3h'
'6h'
'12h'
'1d'
table_names
Specifies a comma-delimited list of the table names.
For these tables, the schedule that you specify in the
schedule
parameter overrides the default schedule that you set by calling theCONFIGURE_CONNECTOR('data_ingestion_schedule', 'schedule')
stored procedure.
Enabling or Disabling the Synchronization of a Table¶
To enable the synchronization of data for a specific table in ServiceNow, call the ENABLE_TABLES
stored procedure with the following arguments:
CALL ENABLE_TABLES(<tables_to_enable>);
Where:
tables_to_enable
Specifies an array of ServiceNow table names.
Use the table name, not the label displayed in the ServiceNow UI. You can find the table name in the data dictionary tables in ServiceNow. In the ServiceNow UI, go to System Definition » Tables. The Name column displays the name of the table.
For example, to enable the synchronization of the tables named table1
, table2
, and table3
, run
the following command:
CALL ENABLE_TABLES(['table1', 'table2', 'table3']);
To disable the synchronization of data for a specific table in ServiceNow, call the DISABLE_TABLES
stored procedure with the following arguments:
CALL DISABLE_TABLES(<tables_to_disable>);
Where:
tables_to_disable
Specifies an array of ServiceNow table names.
Use the table name, not the label displayed in the ServiceNow UI. You can find the table name in the data dictionary tables in ServiceNow. In the ServiceNow UI, go to System Definition » Tables. The Name column displays the name of the table.
For example, to disable the synchronization of the tables named table1
and table2
, run
the following command:
CALL DISABLE_TABLES(['table1', 'table2']);
Disabling the table stops its synchronization. When the table synchronization is re-enabled, ingestion resumes from where it was paused.
Note
Disabling all tables from synchronization does not mean that the Snowflake Connector for ServiceNow will stop generating costs. Some tasks may run in the background, such as those related to notifications.
The connector exposes deprecated version of ENABLE_TABLES
procedure that takes two arguments:
CALL ENABLE_TABLES('<tables_to_configure>', <enable>);
Where:
tables_to_configure
Specifies a comma-delimited list of ServiceNow table names.
enable
Specifies whether synchronization should be enabled or disabled for these tables. Specify
TRUE
to enable orFALSE
to disable.
This procedure is deprecated and will be removed in one of the future connector releases.
We recommend using ENABLE_TABLES
and DISABLE_TABLES
with a single argument.
The ENABLE_TABLES
and DISABLE_TABLES
procedures add the specified table names to the ENABLED_TABLES
view.
If you want to add all tables available in ServiceNow to the ENABLED_TABLES
view, call the
PREFILL_CONFIG_TABLE
stored procedure.
Note
In order for you to call this procedure, the ServiceNow user used by the connector must have access to the
sys_db_object
table.
To call this procedure, run the following command:
CALL PREFILL_CONFIG_TABLE();
This procedure adds all ServiceNow tables to the ENABLED_TABLES
view and disables the tables for ingestion by default.
To enable these newly added tables for synchronization:
Run the following commands to produce a comma-delimited list of the tables in
ENABLED_TABLES
view:SELECT LISTAGG(TABLE_NAME, ',') FROM ENABLED_TABLES;
In the list returned by this command, remove any tables that should not be synchronized.
Call the
ENABLE_TABLES
stored procedure, and pass in this list.
If new tables have been added recently to ServiceNow, you can identify the new tables and enable those tables for
synchronization by using this same approach (i.e. generating the list of tables, editing the list, and passing the
list to the ENABLE_TABLES
stored procedure).
Note
The connector does not support roll backs or delete recoveries in ServiceNow.
Using the roll back and delete recovery features may result in data inconsistency. Records that are recovered in ServiceNow may still be marked as deleted in Snowflake. To resolve it you can use the RELOAD_TABLE stored procedure.
Enabling the Synchronization of a Table with Column Filtering¶
If you do not need all columns from a ServiceNow table in Snowflake, you can skip them. For example, you might want to skip the columns if a single row exceeds the maximum row size of 10 MB.
To enable table ingestion with specified columns run the following command:
CALL ENABLE_TABLE_WITH_COLUMNS('<table_to_enable>', <include_columns>, <exclude_columns>);
Where:
table_to_enable
Specifies a ServiceNow table name.
include_columns
Specifies an array of column names to be ingested. If
sys_id
,sys_created_on
, andsys_updated_on
exist, they are always included, even though they are not mentioned in this array.exclude_columns
Specifies an array of column names to be excluded from ingestion. You cannot exclude
sys_id
,sys_created_on
, orsys_updated_on
columns, as the connector uses them in the ingestion process.
Note
You cannot provide both include_columns
and exclude_columns
. If you want to list include_columns
, you must leave the exclude_columns
empty, and vice versa.
If you provide an empty array for both include_columns
and exclude_columns
, all the available columns are ingested.
The connector validates the provided columns and rejects the enablement request if any of the columns is not available in ServiceNow.
As ServiceNow API supports only include mode, the connector transforms provided column arrays into included columns list and sends it with each request to ServiceNow.
The URL with included columns might be too long to handle by ServiceNow. The connector validates this limitation when the ENABLE_TABLE_WITH_COLUMNS
is invoked.
Included columns configuration for each table can be found in the INCLUDED_COLUMNS column of the ENABLED_TABLES view.
To modify the list of ingested columns, you need to disable the specific table first.
If column filtering is configured for a table, you can enable the columns only using the ENABLE_TABLE_WITH_COLUMNS
procedure. You cannot use the ENABLE_TABLES
in this case.
The config change does not affect the already ingested data. Column filtering applies only to the newly ingested records.
Flattened views only include the columns specified when the table was enabled. They are updated every time the list of included columns changes. If no column filtering is configured, a view consists of all the available columns.
Reloading Data in a Table¶
To reload data in particular table, call the RELOAD_TABLE
stored procedure:
CALL RELOAD_TABLE('<table_name>');
Where:
table_name
Specifies the name of the table to reload.
When you call the RELOAD_TABLE
stored procedure, the connector performs the following example:
The connector suspends the original table for ingestion temporarily.
Note
While the table is being reloaded, you cannot re-enable the table for ingestion.
The connector creates a separate temporary table for ingestion.
The connector ingests the data into this new temporary table. This table is visible in the CONNECTOR_STATS view as a table named with a
__tmp
suffix).After the data is ingested, the connector replaces the data in the original table with the data in the temporary table.
The connector deletes the temporary table.
The connector re-enables the original table for ingestion.
During this process, you can continue to query the existing data in the original table. However, changes to the data in the ServiceNow table won’t be reflected in the Snowflake table until the ingestion process completes.
To avoid overloading your ServiceNow instance, reload only one table at time.
Canceling Table Reload¶
To cancel the process of reloading the data in a table, use the CANCEL_RELOAD_TABLE
stored procedure as
shown in the following example:
CALL CANCEL_RELOAD_TABLE('<table_name>');
Where:
table_name
Specifies the name of the table whose reload you want to cancel.
When you cancel the reload, the connector drops all temporary objects created during the reload. The table is then available for ingestion as part of the normal synchronization schedule.
Configuring the Size of a Single Page Fetch for a Table¶
The connector fetches data from a table by dividing it into smaller chunks called pages. Each API request to ServiceNow fetches one page. Due to limitations on the Snowflake and ServiceNow REST APIs, the size of the response from the ServiceNow API cannot exceed 10 MB and the response should be returned in less than one minute.
To account for this, the connector limits the number of rows fetched within a single API request. This limit is the page size.
The connector uses the following process to determine the page size:
Initially, the default page size is set to 10,000 rows.
If the fetch request fails during ingestion because the response size is exceeded, the page size is gradually decreased by 1000, 100, 10 and 1 until the request succeeds or the final page size is set to 1.
The successful page size is saved in the connector state and this value is used by subsequent requests.
The current page size for a table is available in the ENABLED_TABLES
view. To see the page size, run
the following command:
SELECT PAGE_SIZE FROM ENABLED_TABLES WHERE TABLE_NAME = '<table_name>';
Where:
table_name
Specifies the name of the ServiceNow table being ingested.
The process the connector uses for determining the page size may lead to inefficiencies. This process only reduces the page size. It does not increase the page size. This can happen in situations where a table has a single large row that causes the page size to be set to a lower value.
To avoid this situation, you can manually set the page size by calling the RESET_PAGE_SIZE
stored
procedure as shown in the following example:
CALL RESET_PAGE_SIZE('<table_name>');
Where:
table_name
Specifies the name of the ServiceNow table being ingested.