Accessing the ServiceNow® data in Snowflake¶

Granting Privileges for Accessing the ServiceNow® Data in Snowflake¶

After the Snowflake Connector for ServiceNow® synchronizes the data with Snowflake, to access the ServiceNow® data a role needs:

• USAGE privilege on the database and schema that contain the ServiceNow® data in Snowflake, and

• a DATA_READER application role.

Snowflake recommends creating a dedicated role with these privileges that can be granted to users who need access to the ingested ServiceNow® data. If the connector has been installed with Snowsight then the role provided during Configure step already has the necessary privileges.

For example, if you configured the connector application called my_connector_servicenow 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 APPLICATION ROLE my_connector_servicenow.DATA_READER to role servicenow_data_reader_role;

Copy

Accessing the Raw Data¶

For each ServiceNow® table that you synchronize, the Snowflake Connector for ServiceNow® 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:

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 |
+----------------------------------+-------------------------+-------------+--------------------------+

Copy

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.

• Columns with time and timestamps are always saved using the UTC timezone, regardless of the timezone set in the ServiceNow instance. As a result, depending on the ServiceNow instance configuration, their displayed values may differ from the values displayed in the ServiceNow instance. The difference relates only to displayed values, timestamps both in ServiceNow and Snowflake are referring to the same point in time.

• 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          |
+--------+----------------+------------------+------------+

Copy

Viewing the Event Logs for a Table¶

The Snowflake Connector for ServiceNow® 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:

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 to table_name__event_log, the connector adds a corresponding record to the table_name table.

• If an UPDATE event for the given sys_id is added to the event log table, the connector updates the corresponding record with the sys_id in the table_name table with new data.

• If a DELETE event occurs, the is_deleted flag of the corresponding record in table_name is set to true.

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://myinstance.service-now.com/api/now/table/sys_user/<sys_id>",
  "value": "<sys_id>"
}

Copy

To show the reference fields in the table, call the SHOW_REFERENCES_OF_TABLE stored procedure with the following argument:

CALL SHOW_REFERENCES_OF_TABLE('<table_name>');

Copy

Where:

table_name

Specifies the name of the table you want to show the reference fields for.

This stored procedure inspects the schema of the table and returns a JSON list of objects containing the following properties:

CALL CREATE_VIEW_WITH_DISPLAY_VALUES('<table_name>');

SELECT OPENED_BY, OPENED_BY__METADATA
  FROM DEST_DB.DEST_SCHEMA.INCIDENT__VIEW_WITH_REFERENCES;

+-----------+------------------------------------+
| OPENED_BY | OPENED_BY__METADATA                |
+-----------+------------------------------------+
| "JOHN"    | {                                  |
|           |   "reference_field": {             |
|           |     "display_value": "JOHN",       |
|           |     "key": "b177...",              |
|           |     "link": "https://...",         |
|           |     "reference_table": "sys_user", |
|           |     "resolved": true               |
|           |   }                                |
|           | }                                  |
+-----------+------------------------------------+