Stream ServiceNow Data to Google BigQuery

Introduction

This recipe shows how you can build a data pipeline to read data from ServiceNow and write to BigQuery.  Striim’s ServiceNow Reader will first read the existing tables from the configured ServiceNow dataset and then write them to the target BigQuery project using the BigQuery Writer, a process called “initial load” in Striim and “historical sync” or “initial snapshot” by others.  After completing the initial load, the ServiceNow Reader will automatically transition to continuously reading updates from the configured ServiceNow datasets, and then writing these source updates to the target BigQuery project using the BigQuery Writer. You can use the recipe to write to any of Striim’s supported targets.

Benefits

• Striim’s unified data streaming platform empowers organizations to infuse real-time data into AI, analytics, customer experiences and operations.
• Leverage Striim’s wide range of application adapters to read multiple application data and consolidate in a single data warehouse to create custom reports for enhanced analytics.

Step – 1 – Prep Work

• Setting up ServiceNow as source

  • To generate the Client ID and Client secret properties, set up an OAuth application endpoint as directed in the ServiceNow documentation.
  • See Access control list rules in ServiceNow’s documentation for details on how to create access privileges for users.
    • For full table access, the ServiceNow user account must have the admin and snc_read_only roles. 
    • For per-table access, the ServiceNow user account must have the sys_db_object and sys_glide_object roles at the row level and field level ACL as well as the personalize_dictionary role.

• Setting up BigQuery as Target

  • Create an account in BigQuery where you can access the transferred data.
  • You will require a service account to be created. Service account needs to be created with the following permissions: https://www.striim.com/docs/en/bigquery-writer-initial-setup.html#create-a-service-account-with-the-necessary-bigquery-permissions

• Striim setup details

• • Get started on your journey with Striim by signing up for free on Striim’s Developer Edition.

Step – 2 – Create Striim Application

In Striim, App (application) is the component that holds the details of the data pipeline – source & target details, other logical components organized into one or more flows.

Below steps will help you create an application (refer – Screenshot-1):

1. Click on Apps (left-hand panel) to create your application. 
2. Enter Source:ServiceNow Target:BigQuery (as shown in the screenshots-1,2,3 below)
3. Click on the “Get Started” button.(Screenshot- 1 – Create App)
   (Screenshot- 2 – Select the ServiceNow Reader )
   (Screenshot- 3 – Target selected is BigQuery )
   
4. Provide a name for your application
5. Select a namespace
6. Click the “Next” button(Screenshot- 4 – Striim App Creation)

Step – 3 – Configuring ServiceNow as Source

Before we jump into the connection to the source, lets understand Connection Profile and Target schema creation that are required for our pipeline creation.

1. Connection Profile – Connection profile allows you to specify the properties required to connect to an external data source once and use that set of properties in multiple sources and/or targets in multiple applications. The authentication types supported by Connection profiles are OAuth for ServiceNow and ServiceAccount for Google BigQuery. For ServiceNow, to generate the Client ID and Client secret properties, set up an OAuth application endpoint as directed in the ServiceNow documentation. (same as the steps given in Prep work section)
2. Target Schema creation – This is enabled to experience the power of Striim where all the required schemas and tables are created for you by Striim (if they don’t exist already). If you do not want the automatic target schema creation, disable it after the pipeline is created. Note – Permissions are the only key requirement that you need to make sure of. For this recipe you will need to provide the service account key which is also mentioned in the next step.

1. Enable “Use Connection Profile” (Screenshot-5)(Screenshot- 5 Gathering source details to connect)
   
2. In the “New ServiceNow Connection Profile” dialog (Screenshot-6):
   1. Connection Profile Name – provide a name to identify this connection
   2. Namespace –  Select the namespace. In this case we have used the namespace where the App is created and you can do the same.
   3. Host – Provide the host (for example: <ddd.com>). Note: Please do not specify https:// in the host field.(Screenshot- 6 Connection Profile creation)

3. Click on “Sign in using OAuth”
   1. You will be redirected to the ServiceNow login page where you click on “Allow”. (Screenshot-7)
   2. After successfully logging in you will see the below – (Screenshot-8)(Screenshot-7- Creation of Connection Profile for ServiceNow)(Screenshot- 8 – ServiceNow account authenticated)

4. The Connection profile dialog should have success messages for the connection and Test. (refer Screenshot-9 below)
5. Click on Save.(Screenshot- 9 –  Successful creation of Connection profile for Source)

6. Striim will check on the source and environment access and then enable the “Next” button.
   (Screenshot- 10 –  Source connection validation)

7. In the next screen, select the ServiceNow object(s) that you want to move into Google BigQuery and click “Next”.(Screenshot- 11 –  Source Object selection)

Step – 4 – Configuring BigQuery as Target

1. Choose the service account key.
2. The “Project ID” will get auto-populated from the service account key. (Screenshot-12, 13)(Screenshot-12 – BigQuery credential upload)
3. Either select an existing data set or create a new one. For this recipe we have created a new data set.
4. Click on “Next” button(Screenshot-13 – Target Configuration)
5. Striim will validate the target connectivity and enable the “Next” button.(Screenshot- 14 – Target checks and validation)
6. Review all the details of the data pipeline that we just created and click on “Save & Start”.(Screenshot- 15 – Pipeline Review)
7. You have successfully created an app that will move your ServiceNow data into Google BigQuery!
   
   (Screenshot- 16 – Successful App Creation)

Step – 5 – Running and Monitoring your application

As the Striim App starts running,the dashboards and monitors (screenshot- 17) show the real-time data movement. Refer to our documentation for details on the metrics that are monitored for ServiceNow Reader.

(Screenshot- 17 – Monitoring Dashboard)

Related Information

• Use the Polling Interval property to control the incremental updates from ServiceNow data. For more real-time updates, set the value in seconds. In the above recipe, the polling interval has been set to 5s.
• Learn more about data streaming using Striim through our other Tutorials and Recipes.
• More details about increasing throughput using parallel threads and recovery are here.

Demo Video

Check out the recipe in action:

Conclusion

While this recipe has provided you steps to create a pipeline for your ServiceNow data, do check all the application adapters that Striim supports to read from and write to.

If you have any questions regarding ServiceNow adapter or any other application adapters reach out to us at applicationadapters_support@striim.com.