Documentation Grafana Cloud Connect to data sources Data sources MySQL MySQL query editor

MySQL query editor

Grafana’s query editors are unique for each data source. For general information on Grafana query editors, refer to Query editors. For general information on querying data sources in Grafana, refer to Query and transform data.

The MySQL query editor is located on the Explore page. You can also access the MySQL query editor from a dashboard panel. Click the ellipsis in the upper right of the panel and select Edit.

MySQL query editor components

The MySQL query editor has two modes: Builder and Code.

Builder mode helps you build a query using a visual interface. Code mode allows for advanced querying and offers support for complex SQL query writing.

MySQL Builder mode

The following components will help you build a MySQL query:

• Format - Select a format response from the drop-down for the MySQL query. The default is Table. If you use the Time series format option, one of the columns must be time.

• Dataset - Select a database to query from the drop-down.

  • Table - Select a table from the drop-down. Tables correspond to the chosen database.

  • Data operations - Optional Select an aggregation from the drop-down. You can add multiple data operations by clicking the + sign. Click the X to remove a data operation. Click the garbage can icon to remove the entire column.

  • Column - Select a column on which to run the aggregation.

  • Alias - Optional Add an alias from the drop-down. You can also add your own alias by typing it in the box and clicking Enter. Remove an alias by clicking the X.

• Filter - Toggle to add filters.

  • Filter by column value - Optional If you toggle Filter you can add a column to filter by from the drop-down. To filter on more columns, click the + sign to the right of the condition drop-down. You can choose a variety of operators from the drop-down next to the condition. When multiple filters are added you can add an AND operator to display all true conditions or an OR operator to display any true conditions. Use the second drop-down to choose a filter. To remove a filter, click the X button next to that filter’s drop-down. After selecting a date type column, you can choose Macros from the operators list and select timeFilter which will add the $\_\_timeFilter macro to the query with the selected date column.

• Group - Toggle to add Group by column.

  • Group by column - Select a column to filter by from the drop-down. Click the + sign to filter by multiple columns. Click the X to remove a filter.

• Order - Toggle to add an ORDER BY statement.

  • Order by - Select a column to order by from the drop-down. Select ascending (ASC) or descending (DESC) order.
  • Limit - You can add an optional limit on the number of retrieved results. Default is 50.

• Preview - Toggle for a preview of the SQL query generated by the query builder. Preview is toggled on by default.

MySQL Code mode

To create advanced queries, switch to Code mode by clicking Code in the upper right of the editor window. Code mode supports the auto-completion of tables, columns, SQL keywords, standard SQL functions, Grafana template variables, and Grafana macros. Columns cannot be completed before a table has been specified.

Select Table or Time Series as the format. Click the {} in the bottom right to format the query. Click the downward caret to expand the Code mode editor. CTRL/CMD + Return serves as a keyboard shortcut to execute the query.

Macros

You can add macros to your queries to simplify the syntax and enable dynamic elements, such as date range filters.

Table SQL queries

If the Format option is set to Table, you can execute virtually any type of SQL query. The Table panel will automatically display the resulting columns and rows from your query.

You can change or customize the name of a Table panel column by using the SQL keyword AS syntax.

sql Copy

SELECT
  title as 'Title',
  user.login as 'Created By' ,
  dashboard.created as 'Created On'
 FROM dashboard
INNER JOIN user on user.id = dashboard.created_by
WHERE $__timeFilter(dashboard.created)

Table panel results:

Time series queries

Set the Format option to Time series to create and run time series queries.

The examples in this section refer to the data in the following table:

text Copy

+---------------------+--------------+---------------------+----------+
| time_date_time      | value_double | CreatedAt           | hostname |
+---------------------+--------------+---------------------+----------+
| 2020-01-02 03:05:00 | 3.0          | 2020-01-02 03:05:00 | 10.0.1.1 |
| 2020-01-02 03:06:00 | 4.0          | 2020-01-02 03:06:00 | 10.0.1.2 |
| 2020-01-02 03:10:00 | 6.0          | 2020-01-02 03:10:00 | 10.0.1.1 |
| 2020-01-02 03:11:00 | 7.0          | 2020-01-02 03:11:00 | 10.0.1.2 |
| 2020-01-02 03:20:00 | 5.0          | 2020-01-02 03:20:00 | 10.0.1.2 |
+---------------------+--------------+---------------------+----------+

A time series query result is returned in a wide data frame format. Any column except time or of type string transforms into value fields in the data frame query result. Any string column transforms into field labels in the data frame query result.

Example with metric column:

sql Copy

SELECT
  $__timeGroupAlias(time_date_time,'5m'),
  min(value_double),
  'min' as metric
FROM test_data
WHERE $__timeFilter(time_date_time)
GROUP BY time
ORDER BY time

Data frame result:

text Copy

+---------------------+-----------------+
| Name: time          | Name: min       |
| Labels:             | Labels:         |
| Type: []time.Time   | Type: []float64 |
+---------------------+-----------------+
| 2020-01-02 03:05:00 | 3               |
| 2020-01-02 03:10:00 | 6               |
| 2020-01-02 03:20:00 | 5               |
+---------------------+-----------------+

To customize the default series name formatting (optional), refer to Standard options definitions.

Example using the fill parameter in the $__timeGroupAlias macro to convert null values to be zero instead:

sql Copy

SELECT
  $__timeGroupAlias(createdAt,'5m',0),
  sum(value_double) as value,
  hostname
FROM test_data
WHERE
  $__timeFilter(createdAt)
GROUP BY time, hostname
ORDER BY time

Given the data frame result in the following example and using the graph panel, you will get two series named value 10.0.1.1 and value 10.0.1.2. To render the series with a name of 10.0.1.1 and 10.0.1.2 , use a Standard options definitions display value of ${__field.labels.hostname}.

Data frame result:

text Copy

+---------------------+---------------------------+---------------------------+
| Name: time          | Name: value               | Name: value               |
| Labels:             | Labels: hostname=10.0.1.1 | Labels: hostname=10.0.1.2 |
| Type: []time.Time   | Type: []float64           | Type: []float64           |
+---------------------+---------------------------+---------------------------+
| 2020-01-02 03:05:00 | 3                         | 4                         |
| 2020-01-02 03:10:00 | 6                         | 7                         |
| 2020-01-02 03:15:00 | 0                         | 0                         |
| 2020-01-02 03:20:00 | 0                         | 5                         |
+---------------------+---------------------------+---------------------------+

Example with multiple columns:

sql Copy

SELECT
  $__timeGroupAlias(time_date_time,'5m'),
  min(value_double) as min_value,
  max(value_double) as max_value
FROM test_data
WHERE $__timeFilter(time_date_time)
GROUP BY time
ORDER BY time

Data frame result:

text Copy

+---------------------+-----------------+-----------------+
| Name: time          | Name: min_value | Name: max_value |
| Labels:             | Labels:         | Labels:         |
| Type: []time.Time   | Type: []float64 | Type: []float64 |
+---------------------+-----------------+-----------------+
| 2020-01-02 03:05:00 | 3               | 4               |
| 2020-01-02 03:10:00 | 6               | 7               |
| 2020-01-02 03:20:00 | 5               | 5               |
+---------------------+-----------------+-----------------+

Templating

Instead of hardcoding values like server, application, or sensor names in your metric queries, you can use variables. Variables appear as drop-down select boxes at the top of the dashboard. These drop-downs make it easy to change the data being displayed in your dashboard.

Refer to Templates for an introduction to creating template variables as well as the different types.

Query variable

If you add a Query template variable you can write a MySQL query to retrieve items such as measurement names, key names, or key values, which will be displayed in the drop-down menu.

For example, you can use a variable to retrieve all the values from the hostname column in a table by creating the following query in the templating variable Query setting.

sql Copy

SELECT hostname FROM my_host

A query can return multiple columns, and Grafana will automatically generate a list based on the query results. For example, the following query returns a list with values from hostname and hostname2.

sql Copy

SELECT my_host.hostname, my_other_host.hostname2 FROM my_host JOIN my_other_host ON my_host.city = my_other_host.city

To use time range dependent macros like $__timeFilter(column) in your query,you must set the template variable’s refresh mode to On Time Range Change.

sql Copy

SELECT event_name FROM event_log WHERE $__timeFilter(time_column)

Another option is a query that can create a key/value variable. The query should return two columns that are named __text and __value. The __text column must contain unique values (if not, only the first value is used). This allows the drop-down options to display a text-friendly name as the text while using an ID as the value. For example, a query could use hostname as the text and id as the value:

sql Copy

SELECT hostname AS __text, id AS __value FROM my_host

You can also create nested variables. For example, if you have a variable named region, you can configure the hosts variable to display only the hosts within the currently selected region as shown in the following example. If region is a multi-value variable, use the IN operator instead of = to match multiple values.

sql Copy

SELECT hostname FROM my_host  WHERE region IN($region)

Use __searchFilter to filter results in a query variable

Using __searchFilter in the query field allows the query results to be filtered based on the user’s input in the drop-down selection box. If you do not enter anything, the default value for __searchFilter is %

Note that you must enclose the __searchFilter expression in quotes as Grafana does not add them automatically.

The following example demonstrates how to use __searchFilter in the query field to enable real-time searching for hostname as the user type in the drop-down selection box.

sql Copy

SELECT hostname FROM my_host  WHERE hostname LIKE '$__searchFilter'

Using variables in queries

Template variable values are only quoted when the template variable is a multi-value.

If the variable is a multi-value variable, use the IN comparison operator instead of = to match against multiple values.

You can use two different syntaxes:

$<varname> Example with a template variable named hostname:

sql Copy

SELECT
  UNIX_TIMESTAMP(atimestamp) as time,
  aint as value,
  avarchar as metric
FROM my_table
WHERE $__timeFilter(atimestamp) and hostname in($hostname)
ORDER BY atimestamp ASC

[[varname]] Example with a template variable named hostname:

sql Copy

SELECT
  UNIX_TIMESTAMP(atimestamp) as time,
  aint as value,
  avarchar as metric
FROM my_table
WHERE $__timeFilter(atimestamp) and hostname in([[hostname]])
ORDER BY atimestamp ASC

Disabling quoting for multi-value variables

Grafana automatically creates a quoted, comma-separated string for multi-value variables. For example: if server01 and server02 are selected then it will be formatted as: 'server01', 'server02'. To disable quoting, use the csv formatting option for variables:

Grafana automatically formats multi-value variables as a quoted, comma-separated string. For example, if server01 and server02 are selected, they are formatted as 'server01', 'server02'. To remove the quotes, enable the CSV formatting option for the variables.

${servers:csv}

Read more about variable formatting options in the Variables documentation.

Annotations

Annotations allow you to overlay rich event information on top of graphs. You add annotation queries via the Dashboard settings > Annotations view.

Example query using atime column with epoch values:

sql Copy

SELECT
  epoch_time as time,
  metric1 as text,
  CONCAT(tag1, ',', tag2) as tags
FROM
  public.test_data
WHERE
  $__unixEpochFilter(epoch_time)

Example region query using time and timeend columns with epoch values:

sql Copy

SELECT
  epoch_time as time,
  epoch_timeend as timeend,
  metric1 as text,
  CONCAT(tag1, ',', tag2) as tags
FROM
  public.test_data
WHERE
  $__unixEpochFilter(epoch_time)

Example query using a time column with a native SQL date/time data type:

sql Copy

SELECT
  native_date_time as time,
  metric1 as text,
  CONCAT(tag1, ',', tag2) as tags
FROM
  public.test_data
WHERE
  $__timeFilter(native_date_time)

Alerting

Use time series queries to create alerts. Table formatted queries aren’t yet supported in alert rule conditions.

For more information regarding alerting refer to the following:

• Alert rules
• Template annotations and labels