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.

now

Prerequisites

What You'll Learn

What You'll Need

What You'll Build

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!

  1. Go to the ServiceNow® developer website, and create a developer user.
  2. Log on to the developer website with your newly created user and select Create an Instance.
  3. Choose an instance type. You receive an email with your instance URL, and admin user and password.

Deployment is usually pretty quick, around five minutes. But, while you wait, let's go to the next step and configure Snowflake!

Create the Snowflake Account

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.

Accept the Terms & Conditions

  1. Log on to your Snowflake account through the Snowsight web interface and change to the orgadmin role.
  2. Select "Admin -> Billing & Terms".
  3. In the "Snowflake Marketplace" section, review the Consumer Terms of Service.
  4. If you agree to the terms, select "Accept Terms & Conditions".

Set up a Virtual Warehouse

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.

  1. Navigate to Admin -> Warehouses and select + Warehouse.
  2. Name the virtual warehouse SERVICENOW_CONNECTOR_WH, size XS, and, leaving the defaults, select Create Warehouse.

Install the ServiceNow® connector

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.

  1. From the Snowflake Account Home page, select Marketplace.
  2. In the search window, enter ServiceNow and select the tile.
  3. Review the business needs and usage samples.
  4. Select Get.
  5. Select the warehouse you created above, SERVICENOW_CONNECTOR_WH.
  6. Select Options.
  7. For this lab, leave the default name for the installation database, Snowflake_Connector_for_ServiceNow. Do not select any additional roles.
  8. Select Get. You receive the following message, Snowflake Connector for SeviceNow is now ready to use in your account.Get ServiceNow Connector
  9. Select Done. We will manage it in the next section.

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.

installed

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.

On the Snowflake hand

Launch the Snowflake Connector for ServiceNow® from the Marketplace -> Snowflake Connector for ServiceNow.

  1. Select Manage.
  2. Select Connect.
  3. Fill in the ServiceNow® instance details. This is the first part of the ServiceNow® URL for your ServiceNow® account, without the trailing service-now.com.
  4. Select OAuth2 for the Authentication method.
  5. Copy the redirect URL. You will need it in the next section.

Now, open a new tab in your browser (without closing the above), and follow the steps in the next section.

On the ServiceNow® Other hand

  1. Log on to your ServiceNow® developer instance.
  2. From the main page, select All and search Application Registry.

Application Registry

  1. Select New in the upper right-hand side of the window.
  2. Select Create an OAuth API endpoint for external clients.
  3. Give the endpoint a name, such as Snowflake_connector. Leave the client secret blank. This will autofill.
  4. Paste in the redirect URL that was generated on the Snowflake hand.

Oauth

  1. Select Submit. The window closes.
  2. Select the registry you just created to re-open it.
  3. Note that the Client id and Client secret are auto-generated.
  4. Copy the Client id.

Now, time to jump back to the Snowflake configuration tab.

Now Let's Shake

  1. Paste the Client id from ServiceNow® into the Snowflake configure pop-up.
  2. Go back to the ServiceNow® tab and copy the Client secret and paste it into the Snowflake configure pop-up.
  3. No need to change the Advanced Settings, but feel free to check them out. Connect
  4. Select Connect. Your ServiceNow accounts pops up and requests to connect to Snowflake. check
  5. Select Allow. The connection is established between the two systems.

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.

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.

default config

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.
  1. In the Snowflake Connector for ServiceNow window, under the status for the connector, which displays "Start Data Sync", select Select Tables.
  2. To be able to run our test query later, we need to ingest a couple of tables. From the search window enter incident and check the box next to it and choose a 30 minute sync time.
  3. To choose other tables, clear the search, put the table name and select the checkbox. Do this for the following tables:
    • sys_audit_delete
    • task

Select

  1. Select Start Ingestion. The select windows closes and you get the message "Loading Data" from the main Connector window. In addition to the tables you choose, three system tables will also be loaded. These are necessary to build the views on the raw data: sys_dictionary, sys_db_object, and sys_glide_object.

load

You receive a message indicating success. It appears once at least one table has been fully ingested.

success

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.

Use this query to identify number of incidents raised by month and priority

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.

  1. In Snowsight, select the Snowflake Connector for ServiceNow tile.
  2. In the Snowflake Connector for ServiceNow window, select Stop Ingestion.

stop

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!

What You Learned

Related Resources