QueryPair Queries
A couple of general comments about QueryPair Queries are in order:
- QuerySurge places no limitations on the query syntax that you use - you are free to use any syntax (standard, proprietary or custom) that your data source understands.
- There are a few QuerySurge-specific naming limitations. See Reserved Words and Terms, Reusable Query Snippet Names, Snippet References.
- For QueryPair- and Query-related sizes, see For sizes, see Sizes of QuerySurge Things.
Creating QueryPairs
All QueryPairs are stored in the QueryPairs Folder or in one of its subfolders. You are free to create folders and subfolders as needed. Reusable Query Snippets are stored in the Query Snippets Folder or one of its subfolders.
Create a new QueryPair by right-clicking on a folder and selecting Create New QueryPair. Aside from a name for your new QueryPair, you can provide an optional description and an optional Mapping ID. Once you have created your QueryPair, a side-by-side set of editors opens for the Source and Target queries. In addition to inputting and editing your queries, you can also assign a Connection to each Query in the QueryPair editor, which indicates the data source against which the Query is to be run (more about where connections come from in the Administration section).
Source and Target Queries
When you open an existing QueryPair, you see the Source and Target editors in the QueryPair tab, followed by the QueryPair ID:
QueryPair Properties
The Properties tab shows the QueryPair Name, Description and Mapping properties, as well as any assigned Asset Tags:
Optional settings for QueryPair analysis also appear on this tab. All of these may be edited and saved at any time.
The optional settings that you may configure are:
-
Data Type Checking - QuerySurge will check data types within broad parameters - meaning it will ignore differences between all types of 'string' data, all types of numeric data, all types of 'date' data, etc., but will error out if Source and Target columns are different in these broad categories. If you select Data Type Checking Off, then QuerySurge will view all data as a string type.
-
Row Count Options - Row count differences between Source and Target may be scored as failing, warning or ignored (the default is to score this condition as failing). See Row Count Mismatches for more information.
-
Failure Tolerance Options (Version 14.0+) - one or more Failure Tolerance can be set to allow for a set number or percentage of failures to occur without impacting the overall outcome of the QueryPair. Failure tolerance can be defined at the row-, cell-, and/or column-level. Check out our Failure Tolerance in Analysis for more details on configuring and using this feature.
Note: If you're using a Failure Tolerance on one or more columns, those columns can't also be defined as Shared Keys, configured with a Column Threshold, or marked as Ignorable.
-
Shared Key Column Definition - Using defined Shared Key column(s), QuerySurge can detect whether data differences between Source and Target ResultSets are due to true data mismatches or are due to true nonmatching rows between Source and Target. Otherwise, QuerySurge must operate on assumptions about your data sets, which may not always hold. See Key Column Specification for more information. Whenever possible, use of Shared Keys is recommended.Note: If Shared Keys are used, the Data Type Checking On option should also be used.
-
Column Threshold Options - Thresholds can be set for the comparison between numeric columns - either based on percentage ("If Source column 2 is within a set % of Target column 2, then the QueryPair passes") or on an absolute threshold value ("If Source column 3 is within a fixed absolute value of Target column 3, then the QueryPair passes"). For full details of this feature, see this article.
-
Ignorable Columns (Version 10.0+) - A users defined list of columns to not include when comparing source and target datasets. For full details of this feature, see this article.
-
Timestamp-with-Timezone Column (Version 10.0+) - Some datastores such as Oracle have complex Timestamp datatypes which also store time zone data. Storing time zones with a Timestamp ensures that the data is always interpreted in the same manner. If you have Timestamp columns with time zones you will need to inform QuerySurge of their presents to ensure an accurate comparison of your datasets. For additional details of this feature, see this article.
-
Duplicate Row Options - If you know that your data has duplicate rows, then QuerySurge's standard or default comparison algorithm will not work - it may return spurious results. If you deal with data that has duplicate rows, then you'll need to configure your QueryPair to Enable Analysis Support for Duplicates. This switches the comparison algorithm to an procedure that can handle duplicate rows. For additional details, see this article.
Design-Time Run
After opening a QueryPair, you can click on the Design-Time Run tab. The Design-Time Run tab displays detailed results for each QueryPair. If the QueryPair has previously been run in Design-Time mode, the previous run results will be showing. If a Query Pair has never been run, the following icon appears:
In order to execute a Design-Time run, click on the Run button. Remember, you must have an Agent up and connected to QuerySurge for a Run to execute. Once a Run completes, a display showing the results appears.
You may use the View Query Results button to inspect the detailed results for each Query, and the Run Again button to re-execute the Design-Time run.
Note: You can only see results for the previous Design-Time run, and that once you start a Design-Time run, any previous Design-Time run for the Query are deleted.
The Design-Time Run tab displays detailed results for each QueryPair. To view further information, you can click the “View Query Results” button at the bottom of the page. This view shows the Source and Target data as well as the comparison data, Source query, and Target query.
SOURCE / TARGET DATA (SORT, FILTER)
The Source and Target data can be sorted by an ascending or descending sort. The columns can also be filtered by a keyword or phrase of the user’s choice. To display all of the data, uncheck the filter checkbox.
COMPARISON DATA (EXPORTING, # OF ITEMS)
The comparison tab (third tab after Source and Target tabs) is named according to the results of the run (“Failures”, “No Failures”). In this tab, the user can also sort and filter the information. This data can be displayed in stacked view or side by side view using the icons in the upper left of the tab (
). In the Stacked View, the data is shown in a single column with corresponding rows stacked on each other. In the Side By Side View, the data is shown in two columns, with the Source data son the left and the Target data on the right.