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 existing query forms, see the Use Query Forms topic.
Create Variables¶
Variables define what kind of values you can change in the query filter. You can change the value of a variable every time you run the query without rewriting the SQL code of the query.
Define a Variable¶
To define a variable, wrap it in ${}, for example: ${order date}. The variable name goes within the curly braces and becomes the name of the filter field that users see when working with the query form. You can use an actual column name as the variable name or make it descriptive.
Use Parameters¶
You can use variable parameters to help the user know how to use the variable. Separate the variable name and each parameter with the pipe symbol: |. Separate the parameter name and its value with a colon: type: raw. For example:
${variable name | eg: example value | default: default_value | help: help text | type: type}
For details on the available parameters, see the Query Parameters section below.
Supported Locations for Variables¶
You can use variables in the following locations in a query.
Filter Values¶
You can use variables as filter values in the WHERE clause, for example:
SELECT * FROM tbl WHERE a = ${value1} AND b = ${value2};
Column Names¶
In Alation version 2025.1.1 and later, you can use variables for column names in the SELECT statement, ORDER BY, and GROUP BY clauses. Variables for column names must have the type:raw parameter.
Examples:
SELECT d.${col_name | type:raw} FROM information_schema.domains AS d;
SELECT a.${col_name | type:raw}, COUNT(*) FROM tbl AS a GROUP BY a.${col_name | type:raw} ORDER BY a.${col_name | type:raw} DESC;
Column name variables are supported for the following data sources:
Amazon EMR Presto
Amazon Redshift
Databricks
Google BigQuery
Hive and Hive 2
IBM Db2
MySQL
Oracle
PostgreSQL
SAP ASE (Sybase ASE)
SAP HANA
Snowflake
SQL Server
Teradata
Detailed Example¶
As an example, say you have the following regular query:
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF001.ORDERS
WHERE o_orderdate = '2021-01-01';
You could change the hard-coded date into a variable so other Alation users can enter their own date and run the query using that date. You could define the 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:
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 name defined in the query.
Use Hive Variables¶
Hive variables use the same syntax as query form variables. 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 isn’t a query form variable. Compose will strip the backslash from the query before sending it to Hive for execution.
Query Parameters¶
Parameters help the user know what kind of value to enter for a variable.
Important
Separate the variable name and each parameter with the pipe symbol: |.
Separate the parameter name and its value with a colon: type: raw.
The available parameters are:
col Parameter¶
Starting in Alation version 2023.3.2, you can use the col parameter 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, 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 is initially disabled. 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.
To run the pre-query and enable the multi-select picker, click the Load Query button. The multi-select picker will become enabled, and the pre-query will populate the multi-picker options. 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
colandegparameters 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¶
Use the eg parameter to:
Define example values to display in the filter field on the query form
Create 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
colandegparameters 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 the input field will have an Info icon displayed next to it. 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 is optional but can improve parsing and help the user know what type of value to enter for the variable.
The type parameter accepts the following values:
string(str)integer(int)dateraw
The raw type tells Alation to take the variable input as is.
Note
When the type parameter isn’t present, Alation uses the type raw.
Important
Variables for column names must have the type:raw parameter. Column name variables are available in Alation version 2025.1.1 and later.
The date type will change the input field to be a date picker, for example:
The other types will result in a manual input field.
Syntax¶
type: typeValue
SQL Example¶
SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF001.ORDERS
WHERE o_orderdate <= ${order date | type: date};
default Parameter¶
Use the default parameter to specify a default value for the input field of the variable. This value will prepopulate the filter on the query form. You can use the default parameter 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:
