Variables in SQL queries

Insert selected source, current time range, and other custom values into your queries to customize your charts. Variables can be used in the Log filtering, SQL and PromQL query types.

Getting started

Type {{variable_name}} into the query to use existing variable or create new variables.

Variables are either required or optional:

• Required variables are necessary for the SQL query to run. If not provided, an error will be raised. They are denoted using the double curly braces: {{variable_name}}.

• Optional variables are denoted by double square brackets enclosing a block that contains a variable: [[ ... {{variable_name}} ... ]]. If the variable inside the optional block is not provided, the whole block is removed from the query. Optional variable can only be used in an SQL query.

SELECT {{time}} AS time, 
   countMerge(rows) as count
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}} 
   AND request_user_agent ILIKE '%' || {{user_agent}} || '%'   
[[ AND level = {{log_level}} ]]
GROUP BY time

In this code block, {{time}}, {{source}}, and {{user_agent}} are required variables, while {{log_level}} is an optional variable. The SQL query will still run if no value is selected for {{log_level}}, but it will further filter the results if it is specified.

Types of variables

• String: String value wrapped in quotes when inserted into the query.
• Number: Numeric value directly inserted into the query.
• Date: Converted to a date format.
• DateTime: Converted to a DateTime format with microseconds precision.
• Boolean: For TRUE, FALSE, or NULL values.
• Source: Special type representing a data source.
• SQL expression: String value inserted into the query without any escaping.
• Select predefined values: Allows you to choose from multiple predefined String values. Separate multiple values by commas (,).
• Select values with SQL: Allows you to choose from multiple String values based on an SQL expression. For example:
  • Use toString(level) to select from any level metric value within {{source}}.
  • Use metricTag('environment') to select all environment metric tags.
  • Use IF(name='duration', metricTag('env'), NULL) to select all env metric tags present in your duration metric.
• Select multiple values with SQL: Same as "Select values with SQL" but allows selecting multiple values. The selected values are combined into a regex pattern (e.g. '(nginx|caddy)').
  • In Drag & drop, use the variable with the =~ operator.
  • In SQL, use the variable with the match function:

SELECT {{time}} AS time, count(*) as value
FROM {{source}}
WHERE match(service, {{service}})

SELECT {{time}} AS time, count(*) as value
FROM {{source}}
WHERE TRUE [[ AND match(service, {{service}}) ]]

Common obstacles

1. MissingVariableError: This error will be raised if a required variable is missing its value. Make sure to select a value for your variable, make it optional, or remove it.

2. UnknownVariableTypeError: This is triggered when a variable type is not recognized. Please refer to the Types of variables.

Need help?

Please let us know at hello@betterstack.com.
We're happy to help! 🙏