Using the QuerySurge Data Staging Feature

Versions: 8.0+

Versions:5.0+

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. How can I do this in QuerySurge?
• I need to join between a flat file and a database table - how can I do that in QuerySurge?
• I need to compare incremental rows that have ETL'd in different well-defined time periods. How can I do this in QuerySurge?

In these examples, the data is derived from...

• ...different databases in your environment
• ...completely different technologies (i.e. flat file-to-database-table-join example),
• ...the same system but at different capture times (e.g. incremental ETL)

QuerySurge supports these types of tests with its 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.

Installation

You can see instructions to install or upgrade the Data Staging feature here (install, single machine), here (install, multi machine) or here (upgrade).

Note: The Data Staging feature is licensed separately, so if you are upgrading from a QuerySurge version earlier than 5.0 (<=4.6), you will need to obtain a new license that enables the feature. (There is no additional cost for this licensing change.)

Creating Staging Queries

After installing the Data Staging component you can create Staging Queries in the Design Library. In the Design view, in the left nav tree there is a tree node called Staging Queries.

You can create folders under this node level (as with other tree nodes) and you can create Staging Queries in your folders. You cannot create QueryPairs or Reusable Snippets 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 and most punctuation characters 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.  Enter your 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 a QueryPair, this tab is the main SQL editor for your Staging 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.

Properties tab

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.

Note that you can also create Indexes for your Staging table (requires QuerySurge 6.3+). For details on how to create indexes, see the section "Staging Table Indexes" below.

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.

History tab

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.

Using the Staging Tables

Once Staging Queries are created and executed, you can use the Staging tables 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. 

The example shows that the data from our "Customer" table (originating from an Oracle data source) and "Orders" table (originating from a SQL Server data source) are now both are loaded into the QuerySurge Data Staging area allowing for you to execute a join between those tables, as shown in the Source query.

Note: You can rename the Data Staging connection name 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.

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

(Version 12.0 Update: the Schedule Scenario modal has slightly changed and looks like the image below)

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 while it is being built.

Staging Table Indexes

You can create Indexes for your Staging table (requires QuerySurge 6.3+) in order to enhance query performance on a staging table.

Note: If you are unfamiliar with table indexes, it is advisable to learn about how indexes work before you use this feature.

You can:

• Create an index on a single column
• Create a multiple column index
• Create indexes on multiple columns
• Create multiple column indexes on multiple columns

In order to start with Staging table indexes, on an existing Staging query, open the Table Indexes section on the Properties tab by clicking on the carat icon. Enable the option for your Staging Query by clicking on the Enable Indexes radio button.

Single Index on a Single Column

Create your first index by clicking on the 'Add New Index' tool in the Indexes toolbar:

This will create an Index called "Index 0", and in the Index Columns panel, the columns in your query will appear, so that you can assign appropriate column choices to your index. 

Note: If, for any reason, your query cannot be parsed (this may happen as QuerySurge supports many SQL dialects, but may not be able to parse every syntax construct in every dialect), you can type in your column names manually in order to build your indexes.

Create an index on the Staging table that this query will generate by selecting a column via the checkbox under Include Column. Click the Save button in the lower right of the panel to save your index.

Note: Staging indexes must be unique, so all entries in the selected column must be unique.

If your column is a text or string data type, then you can also assign a size to the index. The size specifies the number of characters (counting from the left) that are used to compute the index. In the following example, an index of size = 4 is shown. 

Note: There is a tradeoff to be considered in choosing an index size. On one hand, the larger the number of characters you choose for an index size, the larger the computation required to calculate the index. On the other hand, you must choose enough characters to make your index unique.

A Multiple Column Index

You can create an index that spans multiple columns. This is done simply by selecting multiple columns under Include Column in the Index Columns panel:

Indexes on Multiple Columns

In addition to the options described above, you can create multiple indexes, each on a separate column. In order to add an index, just click on the 'Add New Index' tool in the Indexes toolbar. In the example below, we have two indexes, Index 0 (on column SKU) and Index 1 (on column Model).

Multiple Indexes on Multiple Columns

Using the same options that are described above, you can create multiple indexes that contain multiple columns. Again, as you create more complex indexing, it is important to consider the resources that will be consumed in calculating the indexes that you create.

A Few Additional Features

You can manage your Staging table indexes on the Properties tab, by using some of the other toolbar features:

Icon	Panel	Feature
	Indexes panel	Clear all indexes from a Staging table
	Indexes panel	Remove any individual index from a Staging table
	Index Columns panel	Generate (i.e. parse) the columns from your query
	Index Columns panel	Add a column to an index (manually)
	Index Columns panel	Clear all columns from an index
	Index Columns panel	Remove any individual column from an index

Note: If indexes are reconfigured, the Staging Query must be re-executed in order to build the Staging table with the new configuration.