Use this quickstart lab to configure and understand the Snowflake Connector for ServiceNow® using the Snowsight wizard, select some tables, ingest data, and run an example query. This quickstart is not meant to be exhaustive. Please check the Snowflake Connector for ServiceNow® documentation for full functionality and limitations.
A ServiceNow® to Snowflake ingestion data flow.
If you do not want to test this connector on your ServiceNow® account, no problem, this step explains how to set up a developer instance!
Deployment is usually pretty quick, around five minutes. But, while you wait, let's go to the next step and configure Snowflake!
If you do not have a Snowflake account, no problem, you can get a free trial at snowflake.com.Select Start for Free and follow the instructions.
You'll need some compute for the connector, so let's set up a virtual warehouse to do that. A second virtual warehouse will be created automatically in the configure section.
Change to the accountadmin role.
The connector, the first of its kind to be deployed on Snowflake's native apps framework, is delivered through the Snowflake Marketplace, and is available to all Snowflake customers instantly. Once chosen, it is installed into your account as a database with several views, and stored procedures.
Let's check that the connector was installed. From Snowsight, go to Data -> Databases. You will see a new database with the name Snowflake_Connector_for_ServiceNow. Open the Public schema and views to see the Global_Config view. Some of the Procedures have also been installed. Others will appear after the installation finishes.
This section shows how to set up the Oauth handshake using the Snowsight user interface, which is massively simpler than managing all the bits through code.
Please have two tabs in your browser open for the next part, as you will have to copy some data from Snowflake to ServiceNow® and vice-versa.
Launch the Snowflake Connector for ServiceNow® from the Marketplace -> Snowflake Connector for ServiceNow.
Now, open a new tab in your browser (without closing the above), and follow the steps in the next section.
Now, time to jump back to the Snowflake configuration tab.
To verify the connection, select the three dots [...] and View Details. At the top of the pop-up you will see the date ServiceNow Authenticated.
Select Done.
Under the status for the connector, which displays "Choose Resources", select Configure.
This displays the Configure Connector dialog. By default, the fields are set to the names of objects that are created when you configure the connector.
Check out Configuring the Snowflake Connector for ServiceNow® for more information on these fields.
Select Configure. The dialog box closes and the status of the connector changes to Provisioning. It can take a few minutes for the configuration process to complete.
You receive a message indicating success. It appears once at least one table has been fully ingested.
In the connector interface, choose Query Sync History. A worksheet opens with several SQL queries you can execute to get monitoring information. Here are some examples:
// Get general information about all ingestions
SELECT * FROM SNOWFLAKE_CONNECTOR_FOR_SERVICENOW.public.connector_stats;
// Search for information about particular table ingestions
SELECT * FROM SNOWFLAKE_CONNECTOR_FOR_SERVICENOW.public.connector_stats WHERE table_name = '<table_name>';
// Check connector configuration
SELECT * FROM SNOWFLAKE_CONNECTOR_FOR_SERVICENOW.public.global_config;
// Calculate ingested data volume
WITH d as (
SELECT
table_name,
last_value(totalrows) OVER (PARTITION BY table_name ORDER BY run_end_time) AS row_count
FROM SNOWFLAKE_CONNECTOR_FOR_SERVICENOW.public.connector_stats
)
SELECT table_name, max(row_count) as row_count FROM d GROUP BY table_name ORDER BY table_name;
// Connector runtime (minutes from start)
SELECT timediff('minute', min(run_start_time), max(run_end_time)) AS connector_runtime_in_minutes
FROM SNOWFLAKE_CONNECTOR_FOR_SERVICENOW.public.connector_stats;
Now that you have ingested some data, let's create the servicenow_reader_role to give it access to the database, schema, future tables, future views, and virtual warehouse.
USE ROLE accountadmin;
USE DATABASE SERVICENOW_DEST_DB;
CREATE ROLE IF NOT EXISTS servicenow_reader_role;
GRANT USAGE ON DATABASE SERVICENOW_DEST_DB TO ROLE servicenow_reader_role;
GRANT USAGE ON SCHEMA DEST_SCHEMA TO ROLE servicenow_reader_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA DEST_SCHEMA TO ROLE servicenow_reader_role;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA DEST_SCHEMA TO ROLE servicenow_reader_role;
GRANT SELECT ON ALL TABLES IN SCHEMA DEST_SCHEMA TO ROLE servicenow_reader_role;
GRANT SELECT ON ALL VIEWS IN SCHEMA DEST_SCHEMA TO ROLE servicenow_reader_role;
GRANT USAGE ON WAREHOUSE SERVICENOW_WAREHOUSE TO ROLE servicenow_reader_role;
Check out the tables that the connector has created under the DEST_SCHEMA of the SERVICENOW_DEST_DB database. For each table in ServiceNow® that is configured for synchronization, the connector creates the following table and views:
To query from the raw data, check out Accessing the raw data. To query the views (recommended), check out Accessing the flattened data.
Here's a little test query for you to identify the number of incidents raised by month and priority. Other example queries are provided on the Snowflake Connector for ServiceNow® page in the Marketplace.
USE ROLE SERVICENOW_READER_ROLE;
USE DATABASE SERVICENOW_DEST_DB;
USE SCHEMA DEST_SCHEMA;
WITH T1 AS (
SELECT
DISTINCT
T.NUMBER AS TICKET_NUMBER
,T.SHORT_DESCRIPTION
,T.DESCRIPTION
,T.PRIORITY
,T.SYS_CREATED_ON AS CREATED_ON
,T.SYS_UPDATED_ON AS UPDATED_ON
,T.CLOSED_AT
FROM
TASK__VIEW T
LEFT JOIN
INCIDENT__VIEW I
ON I.SYS_ID = T.SYS_ID -- ADDITIONAL INCIDENT DETAIL
LEFT JOIN
SYS_AUDIT_DELETE__VIEW DEL
ON T.SYS_ID = DEL.DOCUMENTKEY -- THIS JOIN HELPS IDENTIFY DELETED TICKETS
WHERE
DEL.DOCUMENTKEY IS NULL -- THIS CONDITION HELPS KEEP ALL DELETED RECORDS OUT
AND
I.SYS_ID IS NOT NULL -- THIS CONDITION HELPS KEEP JUST THE INCIDENT TICKETS
)
SELECT
YEAR(CREATED_ON) AS YEAR_CREATED
,MONTH(CREATED_ON) AS MONTH_CREATED
,PRIORITY
,COUNT(DISTINCT TICKET_NUMBER) AS NUM_INCIDENTS
FROM
T1
GROUP BY
YEAR_CREATED
,MONTH_CREATED
,PRIORITY
ORDER BY
YEAR_CREATED
,MONTH_CREATED
,PRIORITY
;
If you would like to monitor errors, run stats, connector stats, enabled tables, you can set up a ServiceNow® monitoring role that allows access to the views in the connector database. For example, run the following in a worksheet (and then use the role):
USE ROLE accountadmin;
CREATE ROLE IF NOT EXISTS servicenow_monitor_role;
GRANT IMPORTED PRIVILEGES ON DATABASE SNOWFLAKE_CONNECTOR_FOR_SERVICENOW TO ROLE servicenow_monitor_role;
GRANT USAGE ON WAREHOUSE SERVICENOW_WAREHOUSE TO ROLE servicenow_monitor_role;
During this lab, we're only ingesting the data, so it makes sense to stop the ingestion after that initial load. However, in an operational environment, you would keep it running.
Read the warning and select Stop Ingestion.
If you are using the public preview connector, make sure to check out the limitations, one of which is during the preview period, before the connector is generally available, Snowflake will release an update that requires you to uninstall and reinstall the connector from the Snowflake Marketplace.
To drop the connector you need to drop the connector database:
DROP DATABASE SNOWFLAKE_CONNECTOR_FOR_SERVICENOW;
Congratulations! You've successfully installed and configured the Snowflake Connector for ServiceNow®, ingested data and ran a query to get some insights on incidents and priority!