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.
• Static list: Allows you to choose from predefined String values. Separate multiple values by commas (,).
• Dynamic list: Allows you to choose from String values based on an SQL expression. For example:
  • Use label('environment') to select all environment metric tags.
  • Use IF(name='duration', label('env'), NULL) to select all env metric tags present in your duration metric.
  • Use toString(level) to select from a level column on sources with plain columns instead of metric tags.
• Query per value: When you pick an option from the dropdown, the result is injected into chart queries as an array (e.g. ('abc', '123', 'def')).

SELECT label('_container')
FROM {{source}}
WHERE dt BETWEEN {{start_time}} AND {{end_time}}
  AND name = 'container_resources_cpu_usage_seconds_total'
GROUP BY label('_container')
ORDER BY avgMerge(rate_avg) DESC
LIMIT 100

SELECT {{time}} AS time,
  label('_container') AS series,
  avgMerge(value_avg) AS value
FROM {{source}}
WHERE time BETWEEN {{start_time}} AND {{end_time}}
  AND label('_container') IN {{top_containers}}
  AND name = 'container_resources_memory_rss_bytes'
GROUP BY time, series
ORDER BY time, series

Allow selecting multiple values

Static list and Dynamic list variables have an Allow selecting multiple values toggle. It's on by default whenever you pick one of these types in the dropdown, though existing variables keep their current setting. With it on, the dashboard toolbar renders the variable as a multi-select with checkboxes instead of a single select.

{{variable}} interpolates differently depending on the selection:

For example, selecting nginx and caddy produces '(nginx|caddy)' for the variable. With the toggle off, a single selected value interpolates the same way, as an escaped scalar 'value'.

In Drag & drop, use a multi-value variable with the =~ operator. In SQL, use 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}}) ]]

Query per value doesn't have this toggle. It always interpolates as a parenthesised list, ('a','b'), for IN.

Filter options with another variable

Dynamic list or Query per value variables can reference other variables in their SQL using the {{name}} syntax. For example this SQL will filter the result with the {{prefix}} variable:

  IF(
    TRUE [[AND label('queue') LIKE {{prefix}} || '%']],
    label('queue'),
    NULL
  )

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! 🙏