Accessing the ServiceNow Data in Snowflake¶
This topic describes how to access ServiceNow data from your Snowflake account.
For each table in ServiceNow that is configured for synchronization, the connector creates the following table and views:
A table with the same name that contains the data in raw form, where each record is contained in a single VARIANT column.
A view named
table_name__view
that contains the data in flattened form, where the view contains a column for each column in the original table and a row for each record that is present in the original table.A view named
table_name__view_with_deleted
that contains the same data astable_name__view
as well as rows for records that have been deleted in ServiceNow.A table
table_name__event_log
that contains the history of changes made to records in ServiceNow.
Note
After you start the connector, it might take some time for the views to be created.
The creation of the views relies on data in the ServiceNow
sys_db_object
, sys_dictionary
and sys_glide_object
tables.
The connector loads metadata from these ServiceNow tables after you enable
any table for synchronization. It can take some time for the connector
to load this metadata.
The following sections explain how to grant the privileges to access this data and how to access the data from these tables and views.
Granting Privileges for Accessing the ServiceNow Data in Snowflake¶
After the Snowflake ServiceNow Connector synchronizes the data with Snowflake, any role with the following privileges can access the ServiceNow data:
USAGE privilege on the database and schema that contain the ServiceNow data in Snowflake, and
SELECT privilege on tables or views within this schema
Snowflake recommends creating a dedicated role with these privileges that can be granted to users who need access to the ingested ServiceNow data.
For example, if you configured the connector to store the ServiceNow data in the dest_db
database and
dest_schema
schema, you can create a role named servicenow_data_reader_role
and grant the privileges
for accessing the data to that role.
The following example shows how to grant these privileges:
CREATE ROLE servicenow_data_reader_role; GRANT USAGE ON DATABASE dest_db TO ROLE servicenow_data_reader_role; GRANT USAGE ON SCHEMA dest_db.dest_schema TO ROLE servicenow_data_reader_role; GRANT SELECT ON FUTURE TABLES IN SCHEMA dest_db.dest_schema TO ROLE servicenow_data_reader_role; GRANT SELECT ON FUTURE VIEWS IN SCHEMA dest_db.dest_schema TO ROLE servicenow_data_reader_role; GRANT SELECT ON ALL TABLES IN SCHEMA dest_db.dest_schema TO ROLE servicenow_data_reader_role; GRANT SELECT ON ALL VIEWS IN SCHEMA dest_db.dest_schema TO ROLE servicenow_data_reader_role;
Note
Do not run
GRANT OWNERSHIP ON FUTURE TABLES IN SCHEMA
on the schema that contains the ServiceNow data in Snowflake. Also, do not change the ownership of the tables that are already created by the connector. Changing the ownership prevents the connector from ingesting the data to the table.Do not change the ownership of the views in the schema that contains the ServiceNow data in Snowflake. Changing the ownership prevents the connector from updating the views when changes occur in the ServiceNow table schema.
Accessing the Raw Data¶
For each ServiceNow table that you synchronize, the Snowflake ServiceNow Connector creates a new table with the same name in the database and schema for the ServiceNow data in Snowflake.
For example, if you configured the connector to store the ServiceNow data in the dest_db
database and
dest_schema
schema, and if you configured the connector to synchronize the incident
table in
ServiceNow, the connector creates the table named dest_db.dest_schema.incident
.
This table contains raw data ingested from ServiceNow. This table contains the following columns:
Column |
Data Type |
Description |
---|---|---|
|
VARCHAR |
The value of the |
|
VARIANT |
The data for the record in raw form. |
|
BOOLEAN |
Specifies whether or not the record was deleted in ServiceNow. |
|
TIMESTAMP_NTZ |
The last time the record was updated in Snowflake. |
The following is an example of the output for a SELECT statement that retrieves the data for the
dest_db.dest_schema.incident
table:
SELECT * FROM DEST_DB.DEST_SCHEMA.INCIDENT LIMIT 5; +----------------------------------+-------------------------+-------------+--------------------------+ | SYS_ID | RAW:ACTIVE | IS_DELETED | LAST_UPDATE_DATE | +----------------------------------+-------------------------+-------------+--------------------------+ | caa04d36db8ba0106e9643c81396197b | {"active": "true", ...} | FALSE | 2021-08-24 12:59:23.932 | | cea045be1b03e010eac562c4bd4bcbb2 | {"active": "true", ...} | FALSE | 2021-08-24 12:59:23.932 | | caa0c9bedb8be010f9f19c41ba961934 | {"active": "true", ...} | FALSE | 2021-08-24 12:59:23.932 | | caa0c9bedb8be010f9f19c41ba961969 | {"active": "true", ...} | FALSE | 2021-08-24 12:59:23.932 | | b9a0c53adb436410d6fa2b691396190a | {"active": "true", ...} | FALSE | 2021-08-24 12:59:23.932 | +----------------------------------+-------------------------+-------------+--------------------------+
Accessing the Flattened Data¶
For each table that contains data, the connector creates two flattened views over the raw data.
The names of the views are the names of the table with the suffixes __view
and
__view_with_deleted
. For example, for the ServiceNow table named incident
, the connector creates
the following views:
dest_db.dest_schema.incident__view
dest_db.dest_schema.incident__view_with_deleted
The view with the __view
suffix contains the records that are in the ServiceNow table. The view with
the __view_with_deleted
suffix includes these same records as well as the records that were deleted
from the ServiceNow table.
Note the following:
The names of the columns in these views are in uppercase. You cannot use lowercase names to access these columns.
There are no views for empty tables. After data appears in the table in ServiceNow, the view is created.
Although the connector handles changes to the schema, the connector does not reload the data.
As a result, in the case of schema changes, records from the old schema are not updated.
The following is an example of the output for a SELECT statement that retrieves the data from the
dest_db.dest_schema.incident_view
view. In this example, the incident
table in ServiceNow has columns
named ACTIVE
, APPROVAL
, CATEGORY
, and ESCALATION
.
SELECT ACTIVE, APPROVAL, CATEGORY, ESCALATION FROM DEST_DB.DEST_SCHEMA.INCIDENT__VIEW LIMIT 5; +--------+----------------+------------------+------------+ | ACTIVE | APPROVAL | CATEGORY | ESCALATION | +--------+----------------+------------------+------------+ | TRUE | not requested | software | 0 | | TRUE | not requested | Cloud Management | 0 | | TRUE | not requested | software | 0 | | TRUE | not requested | network | 0 | | TRUE | not requested | database | 0 | +--------+----------------+------------------+------------+
Viewing the Event Logs for a Table¶
The Snowflake ServiceNow Connector can track the changes made to records in ServiceNow. This tracking information is stored in tables called event logs.
For every ServiceNow table enabled for synchronization, the connector creates an event log table within
Snowflake named <destination_db>.<destination_schema>.<table_name>__event_log
.
Each event log table has the following columns:
Column |
Data Type |
Description |
---|---|---|
|
VARCHAR |
The ServiceNow ID of the record event. |
|
VARCHAR |
The date the record was last updated in ServiceNow. |
|
TIMESTAMP_NTZ |
The date the event was inserted in the event log. |
|
VARIANT |
The current data of the record event. For DELETE events, this is the data of the record at the time of deletion. |
|
VARCHAR |
Specifies if the record was inserted, updated, or deleted from ServiceNow. |
The event log reflects the history of data changes in the corresponding ServiceNow table. For example, if a
new record is inserted into the u_ip_port
table in ServiceNow, a record with event_type
set to
INSERT
event type is added to the dest_db.dest_schema.u_ip_port__event_log
table in Snowflake.
Similarly, if a record is updated or deleted in a table in ServiceNow, a record with event_type
set to
UPDATE
or DELETE
is added to the dest_db.dest_schema.u_ip_port__event_log
table.
The tables in Snowflake that contain the raw data (dest_db.dest_schema.table_name
) are derived
from the corresponding event log tables (dest_db.dest_schema.table_name__event_log
). For example:
If a record for an
INSERT
event is added totable_name__event_log
, the connector adds a corresponding record to thetable_name
table.If an
UPDATE
event for the givensys_id
is added to the event log table, the connector updates the corresponding record with thesys_id
in thetable_name
table with new data.If a
DELETE
event occurs, theis_deleted
flag of the corresponding record intable_name
is set totrue
.
Getting the Display Value of a Reference Field¶
In ServiceNow tables, some fields are reference fields, which contain references to records in other tables.
In the example below, the field opened_by
in the incident
table is a reference field that
contains a reference to the record with the sys_id
<sys_id>
in another
table (sys_user
):
{ "link": "https://testingatt1.service-now.com/api/now/table/sys_user/<sys_id>", "value": "<sys_id>" }
If the table with the reference field and the table that the reference field points to have both been ingested, you can retrieve the record specified in the reference field by joining the two tables.
The following example demonstrates how to perform this join to return the name of the user associated with an
incident. The example joins the incident
and sys_user
tables on the opened_by
and
sys_id
columns. The example then retrieves the raw value of the reference field in the
incident
table and the name
field from the corresponding record in the sys_user
table.
SELECT INC.OPENED_BY AS RAW_VALUE, USERS.NAME AS DISPLAY_VALUE FROM DEST_DB.DEST_SCHEMA.INCIDENT__VIEW AS INC JOIN DEST_DB.DEST_SCHEMA.SYS_USER__VIEW AS USERS ON INC.OPENED_BY:value=USERS.SYS_ID; +--------------------------------------------------+---------------+ | RAW_VALUE | DISPLAY_VALUE | +--------------------------------------------------+---------------+ | {"link": "https://..."}, "value": "7aad6d00..."} | "JOHN" | +--------------------------------------------------+---------------+