Scheduling QuerySurge with Jenkins and the QuerySurge REST API (Versions: 7.0+)

Versions: 8.0+

QuerySurge and Build Automation / Continuous Integration

Data Warehouse/Big Data teams often use Build Automation and/or Continuous Integration tools as part of their development and build processes. Frequently, as teams mature in their development processes, there is interest in including data testing and/or data verification in the automated build process. QuerySurge offers a REST API, which provides an easy integration for any Automated Build or Continuous Integration tool.

Build Automation with Jenkins and QuerySurge Testing

Integrating QuerySurge into a new or previously created build in Jenkins is generally a straightforward task, which is done by calling into the QuerySurge REST API from Jenkins. The simplest way to add calls to the QuerySurge REST API from Jenkins is by setting up a .bat or .sh file that contains the QuerySurge API calls, and launching the .bat or .sh file from Jenkins on a schedule of your choice. In this article, we describe how to set this up for deployments of the QuerySurge REST API on Windows and on Linux. (This article is based on Jenkins 2.7.4; steps may differ slightly for other versions.)

Pre-requisite: Create a Script to Schedule

You'll need a set of QuerySurge REST API calls specifying what you want to execute on your schedule - an API script of the execution steps. You may use the sample files attached to this article as a reference to create your own api calls. In this article, file_name.ps1and file_name.sh refer to API scripts that you have written to execute QuerySurge.

Pre-requisite: Running Jenkins as Admin

Note: Some issues may occur when running Jenkins without Admin privileges. It is usually best to restart your Jenkins service with these privileges. To do so on Windows:

1. Open an elevated command prompt, by right clicking command prompt and selecting Run as Administrator. You may need to enter Admin credentials.
2. Navigate to your Jenkins install directory; the default is C:\Program Files (x86)\Jenkins
3. In the command prompt type jenkins.exe stop. Once it stops type jenkins.exe start.

Pre-requisite: Installing the PowerShell Plugin for Jenkins

Note: The example scripts for Windows are written in PowerShell and an additional plugin will need to be installed in your Jenkins instance to accept running this file type:

1. Click Manage Jenkins in the left sidebar of the main screen.
   
2. Select the Plugins option under the list of items to manage.
   
3. Click on Available Plugins on the left sidebar, and search for "PowerShell"; select the check-box and click on the Install button once selected.
   

Create A New Project

Jenkins uses projects to collect build all of the settings and steps related to particular builds. In this example, we'll create a Freestyle Project to run and execute our PowerShell file. (If you have a previously created Freestyle Project, and would like to add this to it, skip to the next step.)

1. Click New Item in the left sidebar of the main screen.
2. Enter the name of your project and select Freestyle project. Click OK.
   
   

Configuring Your Build To Run A PowerShell Script

If you are running the QuerySurge REST API on a Windows machine:

1. In the newly created item click on Build in the top tabs or scroll down to the Build.

 

2. Click the Add build step button and select PowerShell. The step should add a new element above the button containing a textbox. 

 

3. If you are working with a file inside your items workspace, enter one of following commands (the former may cause the Jenkins build to hang on some systems):
   
   powershell -File %WORKSPACE%\file_name.ps1
   
   otherwise, use one of these commands:
   
   powershell -File <Absolute Path to file folder>\file_name.ps1
   
   
4. At the bottom of the webpage click the Save button.
   
   

If you are running the QuerySurge REST API on a Linux machine:

1. In the newly created item click on Build in the top tabs or scroll down to the Build.
   
   

   

2. Click the Add build step button and select Execute shell. The step should add a new element above the button containing a textbox.
   
   

   

3. If you are working with a file inside your items workspace, enter the following command:
   
   sh $WORKSPACE/file_name.sh
   
   otherwise:
   
   <Absolute Path to file folder>/file_name.sh
   
   
4. At the bottom of the webpage click the Save button.
   
   

Building the Item

Manually Build

The simplest way to initiate a build and invoke the QuerySurge REST API is to manually start the build. This can be used while testing the Jenkins project. To start a build manually, open the Jenkins project you created earlier, and click Build Now in the sidebar. You can see the progress under Build History in the sidebar. To view the command line output from the build (while it's running or after it completes), click the build number in the sidebar, and then click Console Output.

Scheduled Build

A scheduled build will run at the times you specify. On the configuration page for your project, scroll down to Build Triggers, and check the option Build periodically. In the Schedule text box, enter the schedule for your build. Jenkins uses a cron format with five space-separated values:

MINUTE (0-59), HOUR (0-23), DAY (1-31), MONTH (1-12), DAY OF THE WEEK (0-7)

Note: The DAY OF THE WEEK cron expression can take either 0 or 7 to denote Sunday; the values in between 0 and 7 denote the other days of the week in sequence.

Examples:

Start build daily at 08:30 in the morning, Monday - Fridays:

• 30 08 * * 1-5

Weekday daily build twice a day, at lunchtime 12:00 and midnight 00:00, Sunday to Thursday:

• 00 0,12 * * 0-4

Start build daily in the late afternoon between 4:00 p.m. - 4:59 p.m. or 16:00 -16:59 depending on the projects hash:

• H 16 * * 1-5

Start build at midnight (actually means some time between 12:00 AM and 2:59 AM):

• @midnight

Start build at midnight on Saturday:

• 59 23 * * 6

Build every first of every month between 2:00 a.m. - 02:30 a.m.:

• H(0,30) 02 01 * *

Note: The letter H, representing the command Hash can be inserted instead of any of the values; it will cause the parameter to be calculated based on the hash code of your project name. You can also specify the value to be in a range, i.e. H(0,30), which will return the hash code of the project where the possible hashes are in the range 0-30.

Note: The following convenience syntax is supported:
@yearly, @annually, @monthly, @weekly, @daily, @midnight, and @hourly

Jenkins Log Parsing

There are log-parser plugins available for Jenkins that parse the console output written by an Execution Library script so that Jenkins can use the parsed output. If your script uses custom tokens that you specify, Jenkins can parse the output for Scenario Pass/Fail outcome statuses.

Following are the main steps to set up parsing:

• Install a Log Parser Plugin
• Set up a Global Parsing Rules File
• Configure an Execution Library Script

Install a Log Parser Plugin into Jenkins

Note: This example uses the Jenkins Log Parser Plugin, which is available here. The parser is installed as a Jenkins plugin, which you can obtain from the Jenkins Plugin Manager.

Set up a Global Parsing Rules File

The Jenkins Log Parser uses a "parsing rules file" to determine which tokens to listen for and to associate with a Scenario outcome.

Parsing rules are set up in a text file. Once the parsing rules file has been created (more on this below), you can set the file in the "Console Output Parsing" section of the global Jenkins configs under "Post-build Actions".

The parsing rule syntax is simple. An error token is parsed with the following syntax:

error /<Token>/

So, if our error token were the word "QSJenkinsFail", the parsing rule would be:

error /QSJenkinsFail/

In this example, whenever our Execution Library script prints a line to the log containing "QSJenkinsFail", the parser will find the token, and Jenkins will flag the current Scenario as failed. Naturally, using well-defined, less-common strings as your token lowers the chances of false-positives. For a sample parsing rule file, you can download "jenkins_parsing_rules" from the Resources section at the end of this article.

Note: This parser supports other keywords (e.g. error, warn, ok) but does not support keywords 'pass' or 'fail'. A passing outcome status occurs if no error status is parsed from the output.

The parser supports Java regular expressions. You can find more information on this in the Log Parse Plugin documentation.

Configure an Execution Library Script

In order for Jenkins to find a custom token, the token must be part of the log output generated by the Execution Library script when a Scenario fails. You can set this up with QuerySurge by obtaining the outcome value in the JSON response from the QuerySurge REST API endpoint ​/project​/{projectId}​/results​/summary​/querypair​/{guid}, assigning the outcome to the "token" value and searching for "tokens=Failed" as a return value. If this is found in the return value, then the script outputs the custom QSJenkinsFail token to the log, where the Jenkins parser will parse it.

In the Resources section you can find sample scripts that illustrate how to write out tokens to the log that can be parsed with the setup described above (REST-run_test_suites_jenkins.bat, REST-run_test_suites_jenkins.sh). There is also an additional bash script (REST-run_test_suites_jenkins_w_jq.sh) that utilizes a third-party JSON parsing library, that would be easier to work with the JSON responses that are received from the REST API endpoints.

When implemented, Jenkins displays a circle next to your previous build runs with a color indicating the result status of its scenario:

	Success
	Aborted
	Failed

The circles are used in the main dashboard, build history, and individual job pages.

In a specific project, this setup will display each build status based on the log parsing scheme:

Resources