JDBC Driver for ServiceNow

The JDBC driver for ServiceNow runs SQL queries against ServiceNow for any Java/JDBC application. The driver supports large tables that cause timeouts with the ServiceNow SOAP API which allows for easy extraction using Informatica, SAP BusinessObjects, Talend, or other BI/ETL software. SQL support includes functions, expressions, aggregation, and joins.


Simple Licensing

Terms and Agreement

Download

ServiceNow JDBC driver

Getting Started

Get started in 5 minutes



Features

  • Access data in ServiceNow from any Java/JDBC program.
  • Manipulate and control data using standard SQL functions not natively supported by ServiceNow.
  • Perform SQL joins across ServiceNow tables. Join and merge data with other relational and NoSQL systems.
  • Handles extraction of large data sets into other databases. Compatible with SAP, Talend, Informatica, and major BI/ETL software.
  • Simplifies querying and extracting data compared to using the ServiceNow SOAP API. Increases your development efficiency and simplifies maintenance.

Customer Testimonial


"The driver works perfectly for our needs to extract data from ServiceNow to our corporate data warehouse and is easy to integrate with our Oracle ELT tool ODI (Oracle Data Integrator 11g). Your support team is incredible with deep knowledge on JDBC drivers and the way ServiceNow works. I would definitely recommend this tool for folks who want to extract data from ServiceNow using JDBC."– Laxman Malladi, Manager - Enterprise Applications, Herbalife


How it works

  • The SQL query is validated and translated into a ServiceNow SOAP request.
  • Metadata is exposed to relational systems through JDBC by building a schema by parsing WSDL table descriptions provided by ServiceNow.
  • SQL features not natively supported by ServiceNow are executed using the UnityJDBC virtualization engine.

Getting Started

Start using the JDBC Driver for ServiceNow in 5 minutes:

  1. Download the UnityJDBC Driver distribution which contains the ServiceNow JDBC Driver and sample JDBC code for querying ServiceNow. The sample programs can be run directly against a live ServiceNow instance. There is also code to show how the ServiceNow JDBC Driver supports ETL load/refresh/update by extracting large data tables from ServiceNow which can then be inserted into other database systems.
  2. Configure the connection. The JDBC Driver for ServiceNow can be used with any query or reporting software that supports JDBC. The connection information is:
    	JDBC Driver class name:  snow.jdbc.SNowDriver
    	URL format:              jdbc:snow://<serverName>
    As part of the connection string, connection arguments can be passed into the driver that will affect its behavior.
    AttributeValuesDescription
    debug true, false The debug property will cause the driver to print out debug information to the console during its operation.

    user <username> User name for connection.

    password <password> Password for connection.

    encoding utf-8 Character encoding used.

    schema <file_location> File location of schema. Default schema location is snow_schema.xml in the current directory.

    rebuildschema true, false If true, rebuilds schema for connection. If false, uses existing cached schema if available. Uses location provided in schema property.

    tables comma-separated list of tables Instead of using a fixed schema, dynamically build a schema using only the tables specified (comma-separated). Example: tables=alm_asset,incident

    batchsize 100 to 500000 Limits requests to ServiceNow API to the given size (default 1000). Used to handle issues with timeouts in ServiceNow SOAP API when dealing with large tables. A large table request is automatically divided into smaller requests of maximum size equal to batchsize.

    threads 5 Performs parallel requests to ServiceNow API to retrieve large tables in batches. Provides higher performance for retrieving large tables in parallel.
  3. Connect to a database and run your queries. To take the JDBC Driver for ServiceNow for a test drive, use the ServiceNow public ODBC instance at demoodbc.service-now.com. The connection information is:
    URL:     jdbc:snow://demoodbc.service-now.com
    Driver:  snow.jdbc.SNowDriver

    If the submitted query cannot be natively handled by the JDBC Driver for ServiceNow (such as for queries containing JOIN, GROUP BY or HAVING clauses), the query will be promoted to UnityJDBC for processing. UnityJDBC will parse the query into subqueries that will then be run on the specific ServiceNow tables and process the intermediate results to produce the final result.

    The trial version has no expiration date and is fully functioning except that it is limited to returning up to 100 results. If your query produces more than 100 results, purchase a ServiceNow JDBC license here for access to the complete result set.

Example Code Walkthrough

  1. Create a new instance of the JDBC Driver for ServiceNow (optional).
  2. 	Class.forName("snow.jdbc.SNowDriver");
  3. Connect to the URL. A schema will be created and cached. Control the schema location using the schema URL parameter. Note that caching schemas is recommended for good performance. Note that it is also possible to only access a selected list of tables using the tables URL parameter.
  4. 	String url="jdbc:snow://demoodbc.service-now.com?schema=snow_schema.xml";
    	con = DriverManager.getConnection(url, "admin", "admin");
  5. Create a statement and submit a query.
  6. 	stmt = con.createStatement();
    	String sql = "SELECT number, caller_id, priority, active FROM incident";
    	              " WHERE active = 1 ORDER BY number DESC LIMIT 100 OFFSET 5;";
    	rst = stmt.executeQuery(sql);
  7. Print out your results.
  8. 	ResultSetMetaData meta = rst.getMetaData();
    	int numColumns = meta.getColumnCount();
    	System.out.print(meta.getcolumnName(1));
    	for (int j = 2; j <= meta.getColumnCount(); j++)
    		System.out.print(", " + meta.getColumnName(j));
    	System.out.println();
    	while (rst.next())
    	{
    		System.out.print(rst.getObject(1));
    		for (int j = 2; j <= numColumns; j++)
    			System.out.print(", " + rst.getObjects(j));
    		System.out.println();
    	}
  9. Close the statement and connection.
  10. 	rst.close();
    	stmt.close();
    	con.close();

Features and SQL Support for the JDBC Driver for ServiceNow

The following table shows which SQL queries can be handled directly by the JDBC driver for ServiceNow. Queries that cannot be run natively through the JDBC driver for ServiceNow will be automatically promoted to the UnityJDBC virtualization engine. Note: The trial version will return up to 100 results. If your query produces more than 100 results, purchase a license here. Note that the ODBC Driver released by ServiceNow does not support the JDBC-ODBC bridge or access from any Java programs.

Important Notes:

Feature SQL Example JDBC Driver for ServiceNow UnityJDBC Virtualization Support
SELECT Clause
Select all fields from one table
SELECT *
FROM   incident
Select a list of fields from one table
SELECT number, caller_id
FROM   incident
Select with field aliases
SELECT number n, caller_id AS cid
FROM   incident
Select expressions
SELECT number+1 n, 'Caller: '+caller_id AS cid
FROM   incident
Select distinct
SELECT DISTINCT caller_id
FROM   incident
FROM Clause
One table query
SELECT *
FROM   incident
Multiple table query
SELECT I.number, I.caller_id, U.sys_id
FROM   incident I, sys_user U
WHERE  I.caller_id = U.sys_id
Inner join clause
SELECT I.number, I.caller_id, U.sys_id
FROM   incident I INNER JOIN sys_user U
       ON I.caller_id = U.sys_id
Left outer join clause
SELECT I.number, I.caller_id, U.sys_id
FROM   incident I LEFT OUTER JOIN sys_user U
       ON I.caller_id = U.sys_id
Right outer join clause
SELECT I.number, I.caller_id, U.sys_id
FROM   incident I RIGHT OUTER JOIN sys_user U
       ON I.caller_id = U.sys_id
Full outer join clause
SELECT I.number, I.caller_id, U.sys_id
FROM   incident I FULL OUTER JOIN sys_user U
       ON I.caller_id = U.sys_id
WHERE Clause
Equality (=) comparison
SELECT number, caller_id
FROM   incident
WHERE  number = 'INC0000006'
Greater than equal (>=) comparison
SELECT number, caller_id
FROM   incident
WHERE  number >= 'INC0000006'
Greater than (>) comparison
SELECT number, caller_id
FROM   incident
WHERE  number > 'INC0000006'
Less than equal (<=) comparison
SELECT number, caller_id
FROM   incident
WHERE  number <= 'INC0000006'
Less than (<) comparison
SELECT number, caller_id
FROM   incident
WHERE  number < 'INC0000006'
Not equal (!= or <>) comparison
SELECT number, caller_id
FROM   incident
WHERE  number != 'INC0000006'
Like comparison
SELECT number, caller_id
FROM   incident
WHERE  number LIKE 'INC00000%'
Two attribute comparison
SELECT number, opened_at, sys_created_on
FROM   incident
WHERE  opened_at = sys_created_on
Negation
SELECT number, caller_id
FROM   incident
WHERE  NOT (number = 'INC00000%')
AND and OR
SELECT number, caller_id
FROM   incident
WHERE  priority = 1 AND (severity = 1 OR severity=3)
NOT LIKE comparison
SELECT number, caller_id
FROM   incident
WHERE  number NOT LIKE 'INC%'
Date and Timestamp Support
DateTime comparison with Current Time
SELECT number, caller_id, opened_at
FROM   incident
WHERE  opened_at < CURRENT_TIMESTAMP()
DateTime comparison with Date Constant
SELECT number, caller_id, opened_at
FROM   incident
WHERE  opened_at > '2014-05-28 13:35:00'
DateTime comparison for use with ETL/Data Load
SELECT number, caller_id, sys_updated_on
FROM   incident
WHERE  sys_updated_on > '2014-05-28 13:35:00'

Details

Details
group by Clause
Group by one attribute
SELECT   caller_id, COUNT(*) AS cnt
FROM     incident
GROUP BY caller_id
Group by multiple attributes
SELECT   caller_id, priority, COUNT(*) AS cnt
FROM     incident
GROUP BY caller_id, priority
Aggregate Functions
Count(*)
SELECT COUNT(*)
FROM   incident
Count(attribute)
SELECT COUNT(caller_id)
FROM   incident
Maximum
SELECT MAX(number)
FROM   incident
Minimum
SELECT MIN(number)
FROM   incident
Summation
SELECT SUM(reopen_count)
FROM   incident
Average
SELECT AVG(reopen_count)
FROM   incident
Having Clause
Having clause
SELECT   caller_id, COUNT(*) AS cnt
FROM     incident
GROUP BY caller_id
HAVING   COUNT(*) > 1
Order by Clause
Order by one attribute
SELECT   number, caller_id
FROM     incident
ORDER BY number ASC
Order by multiple attributes
SELECT   number, caller_id
FROM     incident
ORDER BY priority ASC, number ASC
Order by expression
SELECT   number, caller_id
FROM     incident
ORDER BY priority+1 DESC
Limit/Offset Clause
Limit clause
SELECT number, caller_id
FROM   incident
LIMIT  10
Offset clause
SELECT number, caller_id
FROM   incident
LIMIT  10
OFFSET 5
Union Clause
Two query union
SELECT caller_id
FROM   incident
WHERE  severity = 3
UNION
SELECT caller_id
FROM   incident
WHERE  priority = 1

Setup Instructions