# 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.

![variables_query.png](https://imagedelivery.net/xZXo0QFi-1_4Zimer-T0XQ/17bf30bf-e433-44c3-a489-725895fc2e00/public =1015x373)

[info]
## Using variables in PromQL?
Make sure to enclose the variable in quotes. For example:
`http_requests_total{method="{{method}}", status="{{status_code}}"}`
[/info]

## 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.

[info]
## Default values
Set default values for your variables in the right sidebar under **Dashboard variables**. The default value will be used if the variable is empty.
[/info]

```sql
[label Required & optional variables example]
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.

[success]
#### Time variables

The time range you select on your dashboard is automatically available as `{{time}}`, `{{start_time}}`, and `{{end_time}}` variables in your queries.
[/success]

## 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:

[code-tabs]
```sql
[label Select multiple values with SQL]
SELECT {{time}} AS time, count(*) as value
FROM {{source}}
WHERE match(service, {{service}})
```
```sql
[label Optional multiselect variable]
SELECT {{time}} AS time, count(*) as value
FROM {{source}}
WHERE TRUE [[ AND match(service, {{service}}) ]]
```
[/code-tabs]

- **Select predefined SQL filters**: When you pick an option from the
dropdown, the result is injected into chart queries as an array (e.g. `('abc', '123', 'def')`).

[code-tabs]
```sql
[label Filter SQL option example]
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
```
```sql
[label Using the filter in a chart query]
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
```
[/code-tabs]

[info]
#### Referencing sources directly

You can use `{{source:source_id}}` to access your sources directly without selecting them in source select.
Find ID for your source in: [Sources](https://telemetry.betterstack.com/team/0/sources ";blank") → **Configure**
[/info]

## 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](#getting-started), or remove it. 
 
2. **UnknownVariableTypeError**: This is triggered when a variable type is not recognized. Please refer to the [Types of variables](/docs/logs/dashboards/variables/#types-of-variables).

## Need help?

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