QuerySurge users may have a need to stage external data in QuerySurge in order to use it during test execution. Examples of where this might be useful include the following use cases:
- I can't set up heterogeneous connectivity between databases, but I need to join across tables in different databases. Does QuerySurge support this?
- I need to join between a flat file and a database table - can I do that in QuerySurge?
- I need to compare incremental rows that have ETL'd in different well-defined time periods. Can I do this in QuerySurge?
In these examples, the data derives:
- from different databases in your environment
- from completely different technologies (i.e. flat file-to-database-table-join example),
- from the same system but at different capture times (e.g. incremental ETL)
QuerySurge supports these types of tests with the Data Staging feature. This feature allows the user to create Staging Queries that bring the identified data into Staging Tables in QuerySurge, where you can then access it in your QueryPairs, as a regular Source or Target.
This article shows the basics of the setup for the Data Staging feature and its' use.
You can see instructions to install or upgrade the Data Staging feature here (install, single machine), here (install, multi machine) or here (upgrade). If you are upgrading, see the procedure to upgrade to the Data Staging feature here.
Note: The Data Staging feature is licensed separately, so if you are upgrading, you will need to obtain a new license that enables the feature.
Creating Staging Queries
After installing the Data Staging component you can create Staging Queries in the Design Library. In the Design view tree (at the left) there is a tree node called Staging Queries. You can create Folders under this level (as with other tree nodes) and you can create Staging Queries in your folders. You cannot create QueryPairs in this branch.
Create a Staging Query by right-clicking either on the tree or the grid. Enter the Staging Query Name. This will generate a Staging Table Name (with spaces removed). You can edit the Staging Table Name after it has been generated. Note that the Staging Table Name is important, because it is the table name that you will use in your QueryPair SQL.
Note: A naming convention for Staging tables should help avoid confusion when writing QueryPairs against Staging tables.
You may also enter a Description for the Staging Query. When you are finished, click Save. The Staging Query will be created, and the editor will open. Type in or copy a query, then select a Connection from the Connection dropdown.
Staging Queries have a similar set of tabs to QueryPairs (Staging Query, Properties, Design-Time Run, History), with similar information on each one:
Staging Query tab
As with QueryPairs, this tab is the main SQL editor for your query. Additionally, you can use Reusable Query Snippets in a Staging Query, just as you can with a QueryPair. The Connection dropdown on the tab shows all the Connections that you have created to data stores in your environment.
The Properties shows the Staging Query properties, which include:
- the Staging Query Name
- the Staging Table Name
- the Overwrite Table behavior
You can edit the Staging Query Name and the Staging Table Name. Note that if you edit the Staging Table Name, a Staging Table with the new name will only be created when the Staging Query is re-executed. The Overwrite Table behavior defaults to Enable Table Overwrite, which means that the Staging Table for this query will be overwritten each time it is run. If you select Do Not Overwrite, then if no Staging Table for this Staging Query exists, one will be created, but if one exists, an error condition will occur and no table will be created.
Design-Time Run tab
This gives you the option to run your Staging Query to show that it conforms to the expected behavior in QuerySurge.
This tab shows the revisions to the Staging Query, the date of the revision and who modified the query. As with QueryPairs, you can track all modifications to the Staging Query on this tab.
View All Staging Queries
In the Design Menu, as with QueryPairs, you have the option to View All Staging Queries. You can see all your Staging Queries listed, along with table names, Connections and the path.
Suites with Staging Queries
As with QueryPairs, you create Suites of Staging Queries for formal execution. Suites containing Staging Queries are created just like other Suites:
- In the Scheduling Menu, click Create Test Suite, or in the Scheduling View, click Create Suite.
- Enter the Test Suite Name and click Save
Drag the Staging Queries you want into the Test Suite from the Design tree. Note that once you have dragged a Staging Query into a Suite, it is "defined" as a "Staging" Suite, and you cannot drag QueryPairs into the Suite. (The same is true if you have dragged a QueryPair into a Suite.) Once you have the Staging Queries that you want in the Suite, click the Save icon.
Scenarios With "Staging" Suites
To create a Scenario with a "Staging" Suite, click Schedule Scenario on the left. Enter the Scenario Name and Add the Suite created for Staging Queries and click Save. Note that you can combine "Staging" Suites and "QueryPair" Suites in a single Scenario. However, you should make sure (via the Scheduling feature) that the "Staging" Suites complete before the "QueryPair" Suites execute.
Note: If a QueryPair executes against a Staging Table while the Staging Query is still executing, it will wait for the Staging Query to complete, as the Staging Table will be locked until that point.
Using the Staging Tables
Once Staging Queries are created and executed, you can use the Staging tables created by the queries as Sources and/or Targets in QueryPairs. When you installed your Data Staging feature, a new Connection was automatically created called Data Staging. It appears as an option in the Connection dropdown like the other Connections that you have created. You can rename it in the usual way, in the Connection Wizard.
Note: Staging queries may cause performance degradation because QuerySurge may be running these queries alongside your QueryPairs. You may notice that Scenarios take longer to execute than previously. For this reason, it is best to start using the Staging feature in a measured way, and build up as you see how your system responds.