Ingest ServiceNow Data into Databricks

[Isa Carotti]

Purpose

This article explains how to extract data from ServiceNow and load it into Databricks for analytics or reporting.

Databricks can retrieve data using the REST Table API performing a GET on a specific table. 

One of the main challenges is

1. extracting from ServiceNow a subset of data derived from joins between tables,
2. including dot.walk values
3. exporting records from sc_req_item along with additional variables associated with a specific cat_item.

The primary solution is to create a remote table incorporating the necessary logic to handle this.

Before You Begin

1. ServiceNow instance with API access + new ServiceNow Application Registry for DataBricks
2. Databricks workspace with “Data Ingestion” capability
3. Basic knowledge of REST APIs and Remote Table.

Implementation Steps

Step 1: New Application Registry in ServiceNow for Databricks

Use the link to set up the Application Registry in ServiceNow for Databricks.

For Redirect URL enter https://<databricks-workspace-url>/login/oauth/servicenow.html

Below is an example

Step 2: Access ServiceNow Data via REST API

1. Create new Remote Table with following simple script to get columns/variables.

   Example Code

   (function executeQuery(v_table, v_query) {

   // inizialization

   var rec = {};

   var row = [];

   // Retrieve data and its associated variables defined in the cat_item

   var gr = new GlideRecord("sc_req_item");

   gr.addQuery("cat_item", "<<cat_item_SysID>>");

   gr.query();

   while (gr.next()) {

   rec.sys_id = gr.getValue('sys_id');

   rec.u_number = gr.getValue('number');

   rec.u_short_description = gr.getValue('short_description');

   rec.u_field1 = gr.variables.fields1;  //your variables for Cat Item to be exported

   rec.u_field2 = gr.variables.field2;  //your variables for Cat Item to be exported

   v_table.addRow(rec);

   }

   })(v_table, v_query);

2. Test your Rest Table API via Rest API Explorer
   GET https://<instance>.service-now.com/api/now/table/<remote_table_name>?sysparm_limit=3

Step 3: Extract Data in Databricks

"Data ingestion" in Databricks enables retrieval of data from the ServiceNow REST Table API.

To configure Databricks refer this link: https://docs.databricks.com/gcp/en/ingestion/lakeflow-connect/servicenow-pipeline

Step 4: Optional - Incremental Data Load

Filter only updated records using sys_updated_on.

Best Practices

• Limit records per API call to avoid timeouts.
• Use ServiceNow sys_updated_on for incremental loads.
• Store API credentials securely in Databricks Secrets.
• Validate data after each ingestion.

References

• ServiceNow Remote Table to manage complex data
• Databricks “Data Ingestion”