Variables in SQL queries

Insert selected source, current time range, and other custom values into your SQL queries to customize your charts.

Getting started

Type {{variable_name}} to use existing or create new variables in your queries.

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.

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.
• Source: Special type representing a data source.

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