How to work with ServiceNow Data in Apache Spark using SQL

Apache Spark is a fast and general engine for large-scale data processing. When paired with the CData JDBC Driver for ServiceNow, Spark can work with live ServiceNow data. This article describes how to connect to and query ServiceNow data from a Spark shell.

The CData JDBC Driver offers unmatched performance for interacting with live ServiceNow data due to optimized data processing built into the driver. When you issue complex SQL queries to ServiceNow, the driver pushes supported SQL operations, like filters and aggregations, directly to ServiceNow and utilizes the embedded SQL engine to process unsupported operations (often SQL functions and JOIN operations) client-side. With built-in dynamic metadata querying, you can work with and analyze ServiceNow data using native data types.

Install the CData JDBC Driver for ServiceNow

Download the CData JDBC Driver for ServiceNow installer, unzip the package, and run the JAR file to install the driver.

Start a Spark Shell and Connect to ServiceNow Data

1. Open a terminal and start the Spark shell with the CData JDBC Driver for ServiceNow JAR file as the jars parameter: $ spark-shell --jars /CData/CData JDBC Driver for ServiceNow/lib/cdata.jdbc.servicenow.jar
2. With the shell running, you can connect to ServiceNow with a JDBC URL and use the SQL Context load() function to read a table.

   ServiceNow uses the OAuth 2.0 authentication standard. To authenticate using OAuth, you will need to register an OAuth app with ServiceNow to obtain the OAuthClientId and OAuthClientSecret connection properties. In addition to the OAuth values, you will need to specify the Instance, Username, and Password connection properties.

   See the "Getting Started" chapter in the help documentation for a guide on connecting to ServiceNow.

   Built-in Connection String Designer

   For assistance in constructing the JDBC URL, use the connection string designer built into the ServiceNow JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

   java -jar cdata.jdbc.servicenow.jar

   Fill in the connection properties and copy the connection string to the clipboard.

   Configure the connection to ServiceNow, using the connection string generated above.

   scala> val servicenow_df = spark.sqlContext.read.format("jdbc").option("url", "jdbc:servicenow:OAuthClientId=MyOAuthClientId;OAuthClientSecret=MyOAuthClientSecret;Username=MyUsername;Password=MyPassword;Instance=MyInstance;").option("dbtable","incident").option("driver","cdata.jdbc.servicenow.ServiceNowDriver").load()
3. Once you connect and the data is loaded you will see the table schema displayed.

4. Register the ServiceNow data as a temporary table:

   scala> servicenow_df.registerTable("incident")

5. Perform custom SQL queries against the Data using commands like the one below:

   scala> servicenow_df.sqlContext.sql("SELECT sys_id, priority FROM incident WHERE category = request").collect.foreach(println)

   You will see the results displayed in the console, similar to the following:

Using the CData JDBC Driver for ServiceNow in Apache Spark, you are able to perform fast and complex analytics on ServiceNow data, combining the power and utility of Spark with your data. Download a free, 30 day trial of any of the 200+ CData JDBC Drivers and get started today.