SNDML 3 – ServiceNow Data Mart Loader

Introducing SNDML version 3

I am pleased to announce SNDML3, a command line utility to load SQL databases from ServiceNow. SNDML3 is a good place to start if you are considering a data warehouse for your ServiceNow data, or if you want to quickly download some data for analysis. SNDML3 is written in Java and is completely open source. It will run on Linux, Unix, Windows and Mac OS. The initial release of SNDML3 supports MySQL, PostgreSQL, Oracle and Microsoft SQL Server. SNDML3 is hosted on GitHub. You can download the files here.

Although much of the SNDML 2.6 code has been recycled, SNDML3 is essentially a rewrite. The SOAP Direct Web Services API has been replaced with the REST Table API for improved performance. The “script file” syntax used for SNDML2 has been replaced with a simpler and more powerful YAML configuration. The new code uses Apache HttpClient for ServiceNow communications, JDBC for database communications, and Log4j2 for logging.

SNDML3 includes many new features, some of which I have highlighted here.

Quick Load

The quick load feature allows you load a single table without creating a YAML file. Simply specify the connection profile and the table name on the command line using the -t option as in this example.

java -jar sndml-3.0.1-mysql.jar -p mydev.profile -t cmdb_ci_service

This feature is intended for testing connectivity or loading small tables. It does permit specification of additional options available via YAML.

Partition Load

The partition load feature is designed for backloading of large task based tables or other transactional tables in your ServiceNow instance. This feature logically partitions the source table based on sys_created_on and loads the target table in reverse chronological order starting with the most recent partition. For example, to load all incident records by month, use the following.

tables:
- {source: incident, partition: month}

The load can be split up by quarter, month or week. For a table with a high volume of inserts, you can even partition by day or hour. Use the following to load all records from sys_email which were created on March 16.

tables:
- {source: sys_email, created: [2018-03-16, 2018-03-17], partition: hour}

Incremental Load

A job to load an extremely large table could take many hours to complete. Once the load finishes, you will likely want to keep the target table up to date. We can rely on the sys_updated_on field in ServiceNow to find any records which have been inserted or updated since our last load.

SNDML3 uses a metrics file to keep track of timestamps. The metrics file is simply a text file (actually a Java properties file) which is written out at the completion of an SNDML3 execution. We could modify the above load of the incident table as follows to create the file incident.metrics when the load completes.

metrics: incident.metrics
tables:
- {source: incident, partition: month}

The following script performs an incremental load. It will update or insert to the SQL target table any incident records updated or inserted in the source since the last load.

metrics: incident.metrics
tables:
- {source: incident, since: last}

When this load completes it will overwrite the file incident.metrics with fresh metrics. We could schedule this last job to run repeatedly (perhaps via cron), and with each execution it will update or insert records since the prior run.

Prune

The above process will handle updates and inserts, but what about deletes? For this we use action: prune. The prune action scans sys_audit_delete for any records which have been recently deleted. We can modify the above script as follows to handle inserts, updates and deletes.

metrics: incident.metrics
tables:
- {name: incident_update, source: incident, target: incident,
action: update, since: last}
- {name: incident_prune,  source: incident, target: incident,
action: prune,  since: last}

Sync

Another available action is sync. This action begins by extracting all values of sys_updated_on from the source (ServiceNow) table and comparing them with sys_updated_on in the target (SQL) table. Based on the compare it determines which rows in the target table need to be inserted, updated or deleted. Sync is not available with the since directive; but it can be used with partition and with a created date range. This script will sync all incident records created in the last 60 days.

tables:
- {source: incident, action: sync, partition: week, created: -60d}

Conclusion

The SNDML3 code is new, so please test it out in your non-production ServiceNow instance. Post a comment regarding your experience to this blog, or report any issues via GitHub.

SNDML Debugging

This post contains instructions for obtaining runtime diagnostics from SNDML 2.6.5.

Create a Log4J configuration file

SNDML 2.6.5 is built using Log4J 1.2. Create a file named log4j_debug.properties similar to the following:

# This file contains the Log4J configuration for the ServiceNow Datamart Loader
#
# The names of loggers in the "soap" package will be prefixed with "ServiceNow".
# The names of loggers in the "datamart" package will be prefixed with "Datamart".
#
# Most loggers for the "soap" package are NOT based on the class name.
# Instead, there are two loggers for each table, named as follows:
#   ServiceNow.Request.<tablename>
#   ServiceNow.Response.<tablename>
# Diagnostic information prior to the SOAP call will be written to the Request logger.
# Diagnostic information after the SOAP call will be written to the Response logger.
# 
# Logger levels are as follows:
# ERROR - Serious error that will result in termination of the program
# WARN  - Truncation or recoverable data conversion errors
# INFO  - One line per SOAP or Database call
# DEBUG - Display XML sent and received
# TRACE - Field level diagnostics (extremely verbose)

log4j.rootLogger=INFO, Console

log4j.appender.Console=org.apache.log4j.ConsoleAppender
log4j.appender.Console.layout=org.apache.log4j.PatternLayout
log4j.appender.Console.layout.ConversionPattern=%d{HH:mm:ss} [%t] %p %c %m%n

log4j.logger.Datamart=DEBUG
log4j.logger.ServiceNow=DEBUG
log4j.logger.ServiceNow.Request=DEBUG
log4j.logger.ServiceNow.Response=INFO
log4j.logger.ServiceNow.Request.sys_db_object=WARN
log4j.logger.ServiceNow.Response.sys_db_object=WARN
log4j.logger.ServiceNow.Request.sys_dictionary=WARN
log4j.logger.ServiceNow.Response.sys_dictionary=WARN

Note that you can set the logging at different levels for different tables.

For general information on Log4J configuration files refer to http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/PropertyConfigurator.html.

Make the Log4J config file available in the class path

The Log4J configuration needs to be in the class path so that Log4J can load it as a resource. One way to do this is to place the file in the current directory and prepend the current directory (“.”) to the class path. You can specify the class path on the Java command line using the “-cp” option.

For Unix the use a colon for the path separator:

-cp .:sndml-2.6.5-mysql.jar

For Windows use a semicolon for the path separator:

-cp .;sndml-2.6.5-mysql.jar

Set log4j.configuration to the name of the config file

Use the “-D” option to set the system property log4j.configuration to the the name of the file.

-Dlog4j.configuration=log4j_debug.properties

Put it all together

You cannot use the “-jar” option with “-cp”. Instead you must specify the main class on the command line. The main class is servicenow.common.datamart.Loader.

The full Java command looks like this:

java -cp .:sndml-2.6.5-mysql.jar -Dlog4j.configuration=log4j_debug.properties servicenow.common.datamart.Loader -p <propfile> -f <scriptfile>

Where <propfile> is the SNDML properties file and <scriptfile> is the SNDML script file.

Time Zones and Date Formats

There is an inconsistent and somewhat unexpected behavior in how the Web Service API handles date-time fields.  This behavior is documented in the wiki: http://wiki.servicenow.com/index.php?title=Introduction_to_Time-Related_Functionality, and summarized as follows:

• When reading data using Web Services:  all date and time fields are returned in GMT.
• For updates, inserts and encoded queries:  date and time fields are assumed to be in local time.

To avoid time-zone problems, follow these two simple rules whenever using the Direct Web Services API.

1. Create dedicated Web Services accounts in the sys_user table.  Do not use normal accounts which are assigned to real users.
2. Set the time zone to GMT and the date format to yyyy-MM-dd for all Web Services accounts.

The inconsistent behavior is easily verified using a small perl script.  To reproduce the test, follow these instructions:

1. Obtain a developer instance at https://developer.servicenow.com.
2. Create a new user named “soap.test” with password “password”.
3. Grant “soap.test” the following roles:
   • itil
   • soap_query_update
   • soap_create
4. This script uses the ServiceNow::SOAP perl module.
5. Edit the script, changing the instance to match your instance in step 1.
6. Set the timezone for “soap.test” to anything other than GMT and run the script. Note that the dates do not match.
7. Set the timezone for “soap.test” to GMT and run the script again.  Note that this time the dates match.

#!/usr/bin/perl
use strict;
use ServiceNow::SOAP;

my $instance = 'devxxxxx';
my $username = 'soap.test';
my $password = 'password';

my $sn = ServiceNow($instance, $username, $password);

# insert a change_request
my $change_request = $sn->table('change_request');

my $start_date = "2015-06-01 12:00:00";

my %result = $change_request->insert(
    short_description => 'Timezone change_request test',
    start_date => $start_date
    );

my $sys_id = $result{sys_id};
my $number = $result{number};

print "inserted number=$number sys_id=$sys_id\n";

# read the record back
my $newrec = $change_request->getRecord($sys_id);

# compare the times
print "expected start_date=", $start_date, "\n";
print "actual   start_date=", $newrec->{start_date}, "\n";

Note: This post from May 2013 was updated in May 2015 to use ServiceNow::SOAP.

Reading Large Tables

There are enhancements in the most recent release of the DataPump to better support loading from large tables, or I should say, unfiltered loading from large tables.  It is okay to frequently load data from large tables as long as your filter restricts the number of records.  However, unfiltered loads from large tables should happen rarely.  If you have a well designed load strategy then you should normally only load those records which have changed since your last load.

Nevertheless, there are times when it is necessary to perform large loads.  The most obvious situation is when a new data mart table is being seeded for the first time.  The DataPump philosophy is that it is okay for these loads to run a long time, because they are one-time (or at least rare) events.  You can kick off the load on a weekend when activity is at an ebb, and check back some hours later to see if it has finished.

ServiceNow limits the number of records that can be retrieved in a single Web Service request to 250.  If you need to read more than 250 records, there are two techniques:

• getKeys – Use the getKeys method to return a complete list of sys_ids.  Then use repeated getRecords calls, each requesting 250 or fewer records, with an __encoded_query of the form sys_idINvalue,value,…
• windowing – Use the extended query parameters __first_row and __last_row to fetch the records in chunks.

The advantage of the getKeys technique is that it is faster.  There is an up-front hit because you require the extra call to retrieve the list of keys.  However, the subsequent getRecords calls are much faster.  The disadvantage is that getKeys may encounter a timeout error if there are too many records.  I am not sure what the practical limit is here, but somewhere north of 100,000 rows, there is a risk that the getKeys call may never return.  So the windowing technique appears to be slower, but safer.

A few weeks ago I realized that getKeys can, itself, be windowed.  As a result, the DataPump now supports a new property: servicenow.getkeys.size.  If the property is 0 or omitted, then no getKeys windowing will be performed (i.e. the application will attempt to get all the keys in a single request).  This is the same as the behavior prior to this release.  However, if the property value is greater than zero the application will make repeated calls to getKeys until all keys have been fetched.

For the purpose of performance testing I chose to load a table with 3.1 million rows.  (The table was the cmdb_ci table.)  The property servicenow.chunksize was set to 100, meaning that records would be fetched 100 at a time.  The property servicenow.getkeys.size was set to 10000.  You can get better performance by setting these properties to higher values, but my objective was test the relative performance of using getKeys verses windowing.  The chunksize can be set as high as 250 and servicenow.getkeys.size should probably be set to 20000 or higher for normal operations.

I loaded the table twice.  Actually, I started two jobs at the same time, writing to different target tables but reading the same table.  As expected, the windowing job got off to a good start, and immediately began extracting and loading records at a rate of about 750 per minute.  The getKeys job had to make more than 300 Web Services calls to retrieve the 3.1 million keys at 10,000 a pop.  This took 2 hours and 18 minutes by which time the windowing job had already loaded 90,000 records.  Then the getKeys job took off.  It began extracting and loading records at a rate of about 10,000 per minute.  7 hours and 12 minutes after starting, the getKeys job had loaded all 3.1 million records.  The load rate for the getKeys job remained constant at about 10,000 per minute.  However, the windowing job slowed down as the __first_row parameter grew larger.  By the 7 hour mark it’s load rate had slowed to 400 rows per minute, and it was less than 8% of the way through the file.  The moral of this story is clear:  do not waste your time with windowing.

Post Script: Two weeks later

Since the slow job was running on a non-production instance, I allowed it to continue.  After a week it was 70% complete and the extract rate had dropped to 100 records per minute.  I finally killed the job because I could not bear to watch its tortured progress.  I was amazed that it would continue running for so long without some sort of “expired session” or other HTTP error.  Should I protect the innocent and simply remove the windowing option from the DataPump?  No.  I think not.  That would prevent someone else from reproducing my test results.