Working with Query Forms in the Catalog

Applies from release 2021.2

Compose queries, both Published and Unpublished, that are saved in the Catalog can be viewed and executed as Query Forms directly in the Catalog.

A Query Form is an interface between the query object and a Catalog user that allows running the query directly in the Data Catalog without the need to work with SQL code in Compose. If a query is parameterized, the corresponding query form makes it possible for users to adjust the result set by applying filters to data without rewriting the query SQL. A Catalog user who does not work with SQL code but still needs to analyze data can retrieve the data by running query forms prepared and shared by data analysts.

All Alation roles except Viewer can view queries as query forms. Only users who have access to the data source of the query can execute query forms and retrieve and view results. The query form results are only visible to users who retrieved them.

Users with the Viewer role cannot open the query form page; however, they can view published queries.

Open Query as Form

To open a query as a query form,

  1. Open the Catalog page of the query.

  2. On the top right of the query page, click Run Form:

    ../../_images/QueryForms_01.png
  3. The page of the query form that corresponds to the given query will open. The query form page displays the Catalog field values from the query page, such as the Title, Description, Authors, other built-in and custom fields, and the Data Quality Flags:

    ../../_images/QueryForms_02.png

Understanding the Query Form Page

The Catalog fields you see on the query form page are in fact the fields of the corresponding query object. When you change a field value on the query form page, you modify this value for the query, and the changes will apply to the Catalog page of the query. A query form is not an independent Catalog object.

The default fields on the query form page are:

Field Name

Description

Title

The Title of the query.

Description

The Description of the query.

Authors

The Authors of the query.

Tags

List of Tags added to the query.

Relevant Articles

List of Articles where the query is referenced using @-mentions.

Properties

  • Data Source: data source for which the query is written

  • Status: query status (Published or Unpublished)

  • Created: date the query was created

  • Last Saved: time the query was touched by the user last: either edited or published

  • Run Info: number of times the query form has been executed and the user and time of when it was last executed

Domains

The Domain information for the query.

Note that you may see more fields on the query form page as your Catalog Admins may have added other custom fields to the query page template.

The ability to modify a field on the query form page depends on the role of the user and their access level to the query. The query Owner and Authors can edit the Title and all fields on the page. Server Admins can edit the Authors field even when they are not Authors. The query Owner, Authors, and Stewards and above can update the Description, add or remove tags, and set Data Quality Flags. All users who can open the query form page can view the fields, such as Properties, Relevant Articles, and assigned Domains.

Actions on the Query Form Page

The actions on the query form page are actions over the corresponding query:

../../_images/QueryForms_09.png

1 - Star - You can favorite the query by clicking the Star option. Starring an object accelerates finding it in the Catalog: when using Alation Search, you can filter the search results to only show Starred objects, which will give you a list of your favourites in the Catalog.

2 - Share - You can share the form with other Catalog users. If you have the Author access level to the query, you can share the query with either Viewer or Author access level.

3 - View Query Page - You can return to the Catalog page of the query.

4 - Open in Compose - If your role has permissions to use Compose, you will be able to open the query in Compose.

5 - Endorse, Warn, Deprecate - If your role has permissions to set Data Quality Flags, you can add and Endorsement, Warning, or Deprecation on the query.

Run Form

You can run a query form and retrieve the results into the Catalog when:

  • You have access to the required objects in the data source

  • You have your own database credentials

To run a query form starting with 2022.1:

  1. On the query form page, if you see a Filter Results section, enter any filters you would like to apply to the form. If there’s an information icon, hover over it for information about the filters. You can use the Clear Form button to remove any data you have entered and revert back to the form’s default values.

    ../../_images/ConnectionSettings_QueryForm.png
  2. By default, the form will run with the last connection settings you used in Compose. Hover over the information icon next to the Connection Settings button to see the current settings. To use different connection settings:

    1. Click the Connection Settings button. The Connection Settings dialog opens.

    2. Click the menu button next to Choose a Connection and choose the connection you want to use. When you hover over the connection name, you’ll see the connection URI in a popup so you can be sure you’re selecting the right connection.

      Note

      If you want to add or edit connection information, see Working with Data Source Connections.

    3. Click the menu button next to Connect as (Select User) and select the user you want to connect with. If you’re using SSO to connect, click the pencil icon next to the user. This will open the login screen for your identity provider in a new tab.

    4. Click the Set Active Connection button. Alation will attempt to connect using the selected connection and user.

  3. Click the Run button to run the form. The query form will start running, and you will see the Running indicator:

    ../../_images/QueryForms_05_ConnectionSettings.png

    The results will be displayed in the Results area.

To run a query form before 2022.1:

  1. On the query form page, under Parameters (or Filter Results for some versions), specify filters if they are present and then click Run Form:

    ../../_images/QueryForms_03.png
  2. The Connect to Data Source dialog will pop up:

    ../../_images/QueryForms_04.png
  3. Select a Connection from saved connections or enter the URI manually.

    Note

    Saved connections are configured and saved in Compose when analysts create queries and are inherited by the Catalog. New connections cannot be created in the Connect to Data Source dialog.

  4. Select or enter your username and password manually.

    Note

    If this data source is configured to use SSO, instead of providing credentials in the dialog you will need to authenticate with your Identity Provider that is external to Alation. In this case the ability to select saved credentials or to provide credentials manually will be disabled. Follow the prompts in the dialog to authenticate.

  5. Click Test Connection to ensure that the connection can be established with the credentials you have provided. In case of SSO authentication, this button will be named Test Authorization. Test Authorization will check if your authentication token is valid. If it has expired, you will be taken to the login page of your Identity Provider to authenticate.

  6. After testing the connection or authorization, click Submit. The query form will start running, and you will see the Running indicator in the UI:

    ../../_images/QueryForms_05.png
  7. The results will be displayed in the Results area.

View the Results

All results retrieved by a user are displayed as a list under Workspace. The Workspace area stores the execution history of the query form. Every time you hit the Run Form button, Alation retrieves a new result set.

../../_images/QueryForms_06.png

You can view previous results by clicking on the corresponding execution timestamp in the list of results. If a query form run fails, there will be a red warning indicator next to the failed result. The currently displayed result set will be highlighted:

../../_images/QueryForms_07.png

If you click the Run Form button again after executing the form, it will reuse the entered credentials or token and will not require re-authentication.

Pivot, Export, Expand

The results can be sent to a Pivot table, downloaded as a CSV file or expanded to view in full screen mode. Find the corresponding controls on the top right of the result set:

../../_images/QueryForms_08.png