Articles in this section

See more

Parameterizing your QueryPairs using Reusable Query Snippets and the REST API (Versions: 7.1+)

Introduction

One of the needs that QuerySurge users often have is to build multiple QueryPairs that differ only in the parameters that the queries use. In this article, we show how to use Reusable Query Snippets as containers for parameters with the QuerySurge REST Base API to automatically update a Reusable Query Snippet (available starting in QuerySurge 7.1). We will work through an example that uses the QuerySurge tutorial data (which can be installed when you install QuerySurge; if you don't have the tutorial data installed, see the last section).

 

Parameterized Query Snippets

In this example, we will read the parameter values from a CSV file and use the Query Snippet "modify" feature of the QuerySurge REST Base API to update the QueryPairs via modifying their underlying Snippets. In this approach we will write a PowerShell script, to iterate through each row of the flat file and execute a new Scenario for each execution of the QueryPair. The following example is just one way in which the REST API can be utilized to parameterize your QueryPairs. This approach is incredibly powerful and can be modified to utilize any scripting language or tool that can make REST calls to QuerySurge's endpoint. 

 

The Setup: CSV Driver Table

For the purposes of this example, the parameterized data that we need for a series of test queries is a date range that consists of a start date and an end date. The data file will just be a simple CSV file with two columns, one for the start dates and one for the end dates. See the example parameter table and file contents below:  

Start Date

End Date

2005-01-01

2005-01-15

2005-01-15

2005-01-30

2005-01-30

2005-02-15

 

Create a CSV file with the following data and save it to a file called input_data.csv:

'2005-01-01','2005-01-15'
'2005-01-15','2005-01-30'
'2005-01-30','2005-02-15'

 Reference File: input_data.csv

Using Reusable Query Snippets in a QueryPair

To work with the input arguments above, we are going to modify a QueryPair from the tutorial data to work with Query Snippets.  To start, create a copy of the following QueryPair. 

  • QueryPairs/ZCITY/CUSTOMER_DIM/ZCITY - JOINED_DT

In this example, I have named the copy ZCITY - JOINED_DT - Snippet.

Now we can add the additional date filters to the WHERE clause by modifying the Source and Target queries to the example below: 

Source:

select
  p.IDPURCHASER as source_id,
  p.DATEJOINED as joined_dt
from
  PURCHASER as p
WHERE CAST(p.DATEJOINED as date) BETWEEN DATE('2005-01-30')
AND DATE('2005-02-15')
order by
source_id;

Target:

select
  c.SOURCE_ID as source_id,
  cast(d.DATE as char(12)) as joined_dt
from
  CUSTOMER_DIM as c,
  DATE_DIM as d
where
  c.JOINED_DT_ID = d.ID
and
  c.SOURCE_SYSTEM = 'ZCITY'
and
 d.DATE BETWEEN DATE('2005-01-30')
and
  DATE('2005-02-15')
order by
  source_id;

We now have the start and end date arguments added to the queries, so we can replace them with Reusable Query Snippets (see the following article for more information on Working with Reusable Query Snippets). The updated Source and Target queries can be seen below, once we replace the start and end dates with Snippets:

Source:

select
  p.IDPURCHASER as source_id,
  p.DATEJOINED as joined_dt
from
  PURCHASER as p
WHERE CAST(p.DATEJOINED as date) BETWEEN DATE(${startDate})
AND DATE(${endDate})
order by
source_id;

Target:

select
  c.SOURCE_ID as source_id,
  cast(d.DATE as char(12)) as joined_dt
from
  CUSTOMER_DIM as c,
  DATE_DIM as d
where
  c.JOINED_DT_ID = d.ID
and
  c.SOURCE_SYSTEM = 'ZCITY'
and
 d.DATE BETWEEN DATE(${startDate})
and
  DATE(${endDate})
order by
  source_id;

 

How it Works: The API Script

In order to drive the QueryPairs using the arguments in the flat file, the script needs to perform several tasks. 

  1. The script starts by defining key variables like the API URL, login credentials, and IDs for the project, test suites, and snippets.
    # Base URL for the QuerySurge API (Replace with your QuerySurge server HostName or IP address)
    $QuerySurgeAPIURL = "http://<YourQuerySurgeHostNameOrIPAddress>/QuerySurge/api"

    # Login credentials for QuerySurge (replace these with your QuerySurge credentials
    $Username = "admin"
    $Password = "admin"

    # Project ID and Test Suites to use (replace these with your own IDs)
    $ProjectId = 1
    $TestSuiteList = @("1234") # List of Test Suite IDs to Run
    $ScenarioNameBase = "API_Scenario" # Base name for the scenario

    # IDs of the snippets to be updated (replace these with your own IDs)
    $SnippetIDs = @("123", "456")

    # Path to the file with the new snippet values ((replace these with the path to your file)
    $SnippetFilePath = "C:\<PathToYourFile>\input_data.csv"
  2. Functions are then defined to perform the various tasks:

    a. Get-SessionId

    Logs into the QuerySurge API and retrieves a session ID for authentication.

    # This function logs into QuerySurge and gets a session ID for authentication.
    function Get-SessionId {
    param ($Username, $Password)

    # Prepare login details
    $LoginParams = @{
    username = $Username
    password = $Password
    }

    # Send login request to the API
    $APIResponse = Invoke-RestMethod -Method "Post" -Uri "$QuerySurgeAPIURL/auth/login" -ContentType "application/json" -Body ($LoginParams | ConvertTo-Json)

    return $APIResponse.sessionId # Return the session ID
    }

    b. Create-HeaderDictionary

    Creates a header dictionary using the session ID, necessary for API requests.

    # This function creates the necessary headers for API requests using the session ID.
    function Create-HeaderDictionary {
       param ($SessionId)

        # Create a dictionary to hold the headers
        $headerDictionary = @{}
        $headerDictionary['X-QS-AUTH'] = $SessionId
        $headerDictionary['Accept'] = "application/json"

        return $headerDictionary
    }


    c. Update-Snippet

    Updates a specific query snippet with new text.

    # This function updates a snippet with new text.
    function Update-Snippet {
        param ($ProjectId, $SnippetID, $SnippetText, $HeaderDictionary)

        # Prepare the new snippet text
        $Body = @{ text = $SnippetText } | ConvertTo-Json

        # Send the update request to the API
        Invoke-RestMethod -Method "Put" -Uri "$QuerySurgeAPIURL/project/$ProjectId/snippet/$SnippetID" -ContentType "application/json" -Headers $HeaderDictionary -Body $Body

        Write-Host "Snippet ID: $SnippetID updated to value: $SnippetText"
    }


    d. Create-TestScenario

    Creates and executes a new test scenario.

    # This function creates and runs a new test scenario using the test suites.
    function Create-TestScenario {
        param ($ProjectId, $TestSuiteList, $ScenarioName, $HeaderDictionary)

        # Create a URL string from the list of test suite IDs
        $TestSuiteURLString = $TestSuiteList -join "&id="

        # Send the request to create and run the scenario
        $Response = Invoke-RestMethod -Method "Post" -Uri "$QuerySurgeAPIURL/project/$ProjectId/execute/suite?id=$TestSuiteURLString&name=$ScenarioName" -ContentType "application/json" -Headers $HeaderDictionary

        return $Response.guid # Return the unique ID of the scenario
    }


    e. Wait-ForScenarioCompletion

    Monitors the status of the test scenario until it completes.

    # This function checks the status of the test scenario until it completes.
    function Wait-ForScenarioCompletion {
        param ($ProjectId, $ScenarioGUID, $HeaderDictionary)

        do {
            # Check the status of the scenario
            $Response = Invoke-RestMethod -Method "Get" -Uri "$QuerySurgeAPIURL/project/$ProjectId/status/scenario/$ScenarioGUID" -ContentType "application/json" -Headers $HeaderDictionary
            Write-Host $Response.message
            Start-Sleep -Seconds 1 # Wait for a second before checking again
        } while ($Response.message -eq "Not Run" -or $Response.message -eq "Running")
    }


  3. The Main Script section then ties all of these functions together to read the snippets values in from the csv file, update the reusable query snippets and execute a scenario. These steps are repeated for all snippet values in the csv file.  


    Step 1: Get Session ID

    $SessionId = Get-SessionId -Username $Username -Password $Password


    Step 2: Create Headers

    $HeaderDictionary = Create-HeaderDictionary -SessionId $SessionId


    Step 3: Read the content of the snippet file

    $FileContent = Get-Content -Path $SnippetFilePath


    Step 4: Update each snippet and run the test scenario

    foreach ($line in $FileContent) {
        $SnippetText = $line -split ',' # Split the line into individual snippet texts

        # Update each snippet with the corresponding text
        for ($i = 0; $i -lt $SnippetText.Length; $i++) {
            Update-Snippet -ProjectId $ProjectId -SnippetID $SnippetIDs[$i] -SnippetText $SnippetText[$i] -HeaderDictionary $HeaderDictionary
        }

        # Create a unique scenario name with a timestamp
        $TimeStamp = Get-Date -Format "yyyyMMdd_HHmmss"
        $ScenarioName = "$ScenarioNameBase_$TimeStamp"

        # Create and run the test scenario
        $ScenarioGUID = Create-TestScenario -ProjectId $ProjectId -TestSuiteList $TestSuiteList -ScenarioName $ScenarioName -HeaderDictionary $HeaderDictionary

        # Wait for the scenario to complete
        Wait-ForScenarioCompletion -ProjectId $ProjectId -ScenarioGUID $ScenarioGUID -HeaderDictionary $HeaderDictionary
    }

     

 You can download the Reference Files (links at the bottom) to see these features.

Reference File: REST-run_test_suites_advanced_snippets.ps1

Execution Set-Up

We will create a script that modifies the start and end date Query Snippets (via the QuerySurge REST Base API) and then runs a Test Suite containing the QueryPair. The script loops through each line in the CSV file, so in this example, it will run the QueryPair three times, each time with a different set of dates. In order to schedule the Test Suite to run, we need a Test Suite containing the new QueryPair we created. 

 

Once the Test Suite has been created, you can use the Test Suite Id in the script to execute the Suite. In the example provided, the Test Suite Id is used (ID: 45). In the attached sample script you will need to modify the configuration variables to match your QuerySurge environment (e.g., the QuerySurge server and port information) and use the Test Suite Id that for the Test Suite that you created. Once these values have been input, you can run your script. In the QuerySurge Run Dashboard, you should see a different Scenario created for each iteration of the date parameters.

 

If You don't have the Tutorial Data

If you're working on an instance of QuerySurge which doesn't have the Tutorial Data installed, you can still go through the example in this article, by using flat files. We provide two CSV file downloads (PURCHASER.csv, CUST_DATE_DIM.csv) for the data so that you can run the examples discussed above.

Setup the Source file (PURCHASER.csv)
Setup for this file follows the regular path. Choose Headers in File, and indicate that the file has 15 columns. Make all the column types varchar types. You'll need to use a modified query for the Source query for the example:

select
p.IDPURCHASER as source_id,
p.DATEJOINED as joined_dt
from
PURCHASER as p
WHERE CAST(p.DATEJOINED as date)
BETWEEN '2005-01-30' AND '2005-02-15'
order by
source_id;

Setup the Target file (CUST_DATE_DIM.csv)
Setup for this file also follows the regular path. Choose Headers in File, and indicate that the file has 18 columns. Make all the column types varchar types. You'll need to use a modified query for the Source query for the example:

select 
c.SOURCE_ID as source_id, 
c.DATE as joined_dt
from
CUST_DATE_DIM as c
where
c.DATE BETWEEN '2005-01-30' 
and
'2005-02-15'
order by
source_id;

Related articles