Versions:4.5+
The command line API for QuerySurge is deprecated. We do recommend switching existing API calls to the REST API. Information on our REST API can be found here
Introduction
One of the needs that QuerySurge users often have is to build multiple QuerySurge 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 Base API to automatically update a Reusable Query Snippet (available starting in QuerySurge 4.5.x). 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).
Prior to QuerySurge 4.5.x, users could employ a Parameterized Driver Table in either the Source or Target database (see Parameterizing your QueryPairs using a Table based Approach). With the extension of the Base API in the 4.5.x release, parameterized runs can be driven through an API script.
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 Base API to update the QueryPairs via updating their underlying Snippets. This approach allows us to write a 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 API can be utilized to parameterize your QueryPairs, but this approach is incredibly powerful and can be modified to fit your needs.
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 the following tasks:
- Read the CSV file contents by row and separate out the individual arguments for the start and end date values
Sample Batch Code:FOR /F "tokens=1-2 delims=," %%a IN (%inputFile%) DO (
)
- Modify the ${startDate} and ${endDate} Reusable Query Snippet values using the API
Sample Batch Code:call "%cliPath%" modifyReusableQueryTextByName --hostname %hostname% --port %port% --username %username% --snippetName "startDate" --snippetText "%%a"
call "%cliPath%" modifyReusableQueryTextByName --hostname %hostname% --port %port% --username %username% --snippetName "endDate" --snippetText "%%b"
- Execute the Test Suite containing the affected QueryPair
Sample Batch Code::RunSuite
echo Running Suite %suiteId%
FOR /F "tokens=*" %%a in ('call "%cliPath%" runTestSuiteById --hostname %hostname% --port %port% --username %username% %suiteId%') do (
SET scenarioIdList=%%a
)
echo %scenarioIdList%
- Wait for the Scenario to finish and loop the steps above for each row in the file
Sample Batch Code:
;: Loop until the Scenario has completed
:DoUntil:; Get the scenario outcome/status using the Scenario ID that was handed back
FOR /F "tokens=*" %%a in ('call "%cliPath%" getScenarioOutcome --hostname %hostname% --port %port% --username %username% %scenarioIdList%') do (
set outcome=%%a;: Wait for the outcome/status to be neither 'Not Run' nor 'Running' (meaning wait for completion)
IF "%%a"=="Not Run" GOTO DoUntil
IF "%%a"=="Running" GOTO DoUntil
)
You can download the Reference Files (links at the bottom) to see these features.
Reference Files: CLI-run_test_suites_advanced_snippets.bat, CLI-run_test_suites_advanced_snippets.sh
Execution Set-Up
We will create a script that modifies the start and end date Query Snippets (via the QuerySurge 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 or Test Suite name in the script to execute the Suite. In the example provided, the Test Suite Id is used (ID: 45). In the attached sample script (for Windows, CLI-run_test_suites_advanced_snippets.bat; for Linux, CLI-run_test_suites_advanced_snippets.sh), 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;
Comments
3 comments
Hi Martin,
Can you also explain how and from where can we submit the script for running the API's?
Hi
From the shell script,
status=`$cli getScenarioOutcome $cliOptions $scenarioId`
this gives the Scenario Outcome.
Is it possible to get the Querypair outcome from the scenario executed ?
I use the script to run Hundred's of table together. But to find the status of the querypair within the scenario , I need to check the scenario one at a time a find it which is time consuming.
I would like to see the status of the query pair from the batch script itself. Is this possible.
I have only one query pair inside my test suite.
Is there any Video tutorial for Reusable query ?
Article is closed for comments.