Create a Query Form

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

With Alation, query writers can turn their queries into runnable forms by changing hard-coded values into variables. Then other users can visit the resulting query form, enter their own values into the form, and run the query without editing the SQL. This topic explains how query writers can add variables to their queries.

For help interacting with query forms, see the Use Query Forms topic.

The basic syntax for a query form variable is:

${variable name | eg: example value | default: default_value | help: help text | type: type}

For example, if an original query reads:

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF001.ORDERS
WHERE o_orderdate = '2021-01-01';

You could change the hard-coded date into a variable like this:

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF001.ORDERS
WHERE o_orderdate = ${order date | eg: 2021-01-01 | type: date | default: 2021-01-01 | help: enter order date in YYYY-MM-DD format};

The corresponding query form would look like this:

In this example, order date is a variable which will be supplied by users running the query form.

Create Variables

Variables define what kind of values can be changed in the query filter. A variable can be changed every time a query is rerun without rewriting the SQL code of the query.

Important

To define a variable, wrap it in ${}, for example: ${order date}

For example, in the query given below, the expression ${order date} is the variable that can be changed by users when they run the query:

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF001.ORDERS
WHERE o_orderdate <= ${order date};

The query form that corresponds to this query, will include the order date filter:

The filter on this query form is an input field for the variable defined in the query (order date). The name of the filter field that users see when working with the query form is the variable defined in the query. You can use the actual column name as the variable or make it descriptive.

Note

To use a Hive variable, use a backslash to escape the variable reference, for example:

SET x=777; SELECT ${\hivevar:x}

The backslash character tells Compose that this is not a query form variable. The backslash will be stripped from the query before it’s sent to Hive for execution.

The variable can be further defined using a number of parameters, as described below.

Query Parameters

Parameters are helpful in providing a prompt of what kind of value should be entered into the input field for a variable.

Important

The variable name and each parameter should be separated with the pipe symbol: |

The parameter name and its value should be separated with a colon:

type: raw

For example:

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF001.ORDERS
WHERE o_orderpriority IN (${Priority | eg: '1-URGENT', '2-HIGH' | type: raw});

The query form that corresponds to this query will look as follows:

The available parameters are:

• col Parameter

• eg Parameter

• help Parameter

• type Parameter

• default Parameter

col Parameter

Starting in Alation version 2023.3.2, the col parameter can be used to create a dynamic multi-select picker whose options are supplied by a column that you specify. The column may be from any table that already exists, including the target table for the current query. Provide the name of the column as the parameter value, for example: col: schema_name.table_name.column_name.

Important

When using the col parameter:

• The type parameter is required.

• You must be using the IN or NOT IN operator in your query’s filter.

After you add the col parameter to the query, the multi-select picker on the query form will be disabled initially. To use the query form, you must run a pre-query to populate the multi-select picker’s options. The pre-query will select the distinct values that are present in the specified column at that moment. You can hover over the multi-select picker button to see the pre-query that will be run.

To run the pre-query and enable the multi-select picker, click the Load Query button. The multi-select picker will become enabled, and its options will be populated from the results of the pre-query. You can then select options from the multi-picker and run the query form.

To create a multi-select picker using static, predefined options that aren’t available in a column in your database, consider using the eg parameter instead.

Syntax

col: schema_name.table_name.column_name

SQL Example

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF001.ORDERS
WHERE o_orderpriority IN (${Priority | col: SNOWFLAKE_SAMPLE_DATA.PRIORITIES.PRIORITY_NAME});

Limitations

• You can’t use both col and eg parameters in the same variable.

• If the pre-query has more than 100 results, only the first 100 will be available in the multi-select picker. When this happens, a message will appear on the query form to warn you.

eg Parameter

The eg parameter can be used for:

• Defining example values to be displayed in the filter field on the query form

• Creating a multi-select picker with static predefined options

An alternative syntax for eg is e.g.. You can’t use both col and eg parameters in the same variable.

To define example values that will be displayed in the filter field, provide the desired text as the parameter value, for example: eg: example text.

To create a multi-select picker, provide multiple example values in single quotes, separated by a comma, for example: eg: 'example1', 'example2'. You must be using the IN or NOT IN operator in your query’s filter. The query form will display a multi-select dropdown list along with the manual input field:

The multi-select picker is a convenience for selecting predefined options. You can still enter values into the query form manually if desired.

To provide a multi-select picker with dynamic options that are defined by a column in your database, consider using the col parameter instead.

Syntax

• A single value:

  eg: exampleValue
  e.g.: exampleValue
  

• Multiple values:

  eg: 'exampleValue1', 'exampleValue2'
  e.g.: 'exampleValue1', 'exampleValue2'
  

SQL Example 1

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF001.ORDERS
WHERE o_orderdate <= ${order date | eg: 2021-09-12};

SQL Example 2

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF001.ORDERS
WHERE o_orderpriority IN (${Priority | eg: '1-URGENT',  '2-HIGH'});

Limitations

• You can’t use both col and eg parameters in the same variable.

help Parameter

The help parameter is useful for displaying a prompt for the query form filter in the Alation UI. If the help parameter is present for a variable, then an Info icon will be displayed next to the input field. On hover-over on this icon, users will see the text of the prompt.

Syntax

help: help text

SQL Example

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF001.ORDERS
WHERE o_orderdate <= ${order date | help: Enter a value for "order date"};

type Parameter

The type parameter can be used for defining the data type of the variable. type accepts the following values:

• string (str)

• integer (int)

• date

• raw

Use type to specify the type of the variable for improved parsing, although you can leave it out. type: raw tells Alation to take the variable input as is.

Syntax

type: typeValue

SQL Example

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF001.ORDERS
WHERE o_orderdate <= ${order date | type: date};

When the type is defined to be date, it will change the input field to be a date picker:

The other types will result in a manual input field.

Note

When the type parameter is not present, Alation uses the type raw.

default Parameter

The default parameter can be used for declaring a default value for the input field of the variable. This value will prepopulate the filter on the query form. It can be used in place of the eg parameter. If both are present, the default value will take precedence.

Syntax

default: defaultValue

SQL Example

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF001.ORDERS
WHERE o_orderpriority IN (${Priority | default: 1-URGENT});

Query Parameter Examples

Find more examples of parameterized queries in the table below:

single value	SELECT * from order_history WHERE customer_id = ${customer_id};
single value with like	SELECT * from order_history WHERE col LIKE '${string}';
passing multiple values	SELECT * from order_history WHERE customer_id in (${customer_id | type: raw});
dates	SELECT top 10 * from order_history WHERE my_date = ${my_date | type: date};
dates multiple values	SELECT top 10 * from order_history WHERE my_date BETWEEN (${start_date | type: date}) and (${end_date | type: date});
string values	SELECT * from order_history WHERE customer = ${customer};
string multiple values	SELECT * from order_history WHERE customer IN ${customer | type: raw};
static multi-select picker	SELECT * FROM snowflake_prod.rand_001.orders WHERE o_orderdestination IN (${State | eg: 'New York', 'Oregon', 'Texas', 'Utah'});
dynamic multi-select picker	SELECT * FROM snowflake_prod.xyz_001.orders WHERE o_orderdestination IN (${state | col: snowflake_prod.xyz_001.states}) and senderzip IN (${zip code | col: snowflake_prod.abc_002.zipcodes});

Sharing Query Forms

To share the SQL-free, runnable form of your query with other users, choose Share > Share as Form in Compose: