Documentation Plugins SAP HANA data source Query editor

SAP HANA query editor

Grafana provides a query editor for the SAP HANA data source, which is located on the Explore page. You can also access the SAP HANA query editor from a dashboard panel. Click the menu in the upper right of the panel and select Edit.

This document explains querying specific to the SAP HANA data source. For general documentation on querying data sources in Grafana, refer to Query and transform data.

Key concepts

If you’re new to SAP HANA, the following are key terms used in this documentation:

Query editor features

The SAP HANA query editor provides the following features:

• SQL query editor: Enter any HANA SQL queries directly.
• Syntax highlighting: SQL syntax is highlighted for readability.
• Auto-completion: Grafana macros and template variables are auto-completed as you type.
• Format options: Format query results as time series, table, or logs.
• Keyboard shortcut: Press Ctrl+S (or Cmd+S on Mac) to re-run the query while editing.

Create a query

To create a query:

1. Select the SAP HANA data source.
2. Enter your SQL query in the query editor.
3. Select the Format option:
   • Time series: For visualizing data in graph panels.
   • Table: For displaying data in table panels.
   • Logs: For viewing data in the Logs panel in Explore.
4. Click Run query to execute.

Time series queries

For time series visualizations, your query must return at least one time column and one numeric value column. The time column should be named time or use a timestamp data type.

Example:

SQL Copy

SELECT ts AS time, temperature AS value
FROM weather
WHERE $__timeFilter(ts)
ORDER BY ts ASC

Table queries

For table visualizations, your query can return any columns. The results display in a tabular format.

Example:

SQL Copy

SELECT username, city, age
FROM users
WHERE city = 'London'

Macros

Macros are placeholders that Grafana expands into SQL expressions based on the dashboard time range and other context. The query editor auto-completes supported macros.

Available macros

$__timeFilter macro

The $__timeFilter(<time_column>) macro applies the dashboard time range to a column.

Example:

SQL Copy

-- Query with macro
SELECT ts, temperature FROM weather WHERE $__timeFilter(ts)

-- Expands to
SELECT ts, temperature FROM weather WHERE "ts" > '2021-02-24T12:52:48Z' AND "ts" < '2021-03-24T12:52:48Z'

$__timeFilter with custom format

If your time column is stored in a custom format, specify the format as the second argument.

Example:

SQL Copy

-- Time column in YYYYMMDDHH24MISS format (e.g., 20210421162012)
SELECT TO_TIMESTAMP("TS",'YYYYMMDDHH24MISS') AS METRIC_TIME, "VALUE"
FROM "SCH"."TBL"
WHERE $__timeFilter("TS","YYYYMMDDHH24MISS")

-- Time column in YYYY-MON-DD format (e.g., 2021-JAN-15)
SELECT TO_TIMESTAMP("TS",'YYYY-MON-DD') AS METRIC_TIME, "VALUE"
FROM "SCH"."TBL"
WHERE $__timeFilter("TS","YYYY-MON-DD")

$__timeFilter with epoch timestamps

For epoch timestamps, use the "epoch" format with a precision specifier.

Example:

SQL Copy

-- Epoch seconds (e.g., 1257894000)
SELECT ADD_SECONDS('1970-01-01', "TIMESTAMP") AS "METRIC_TIME", "VALUE"
FROM "SCH"."TBL"
WHERE $__timeFilter("TIMESTAMP","epoch","s")

-- Epoch milliseconds (e.g., 1257894000000)
SELECT ADD_SECONDS('1970-01-01', "TIMESTAMP"/1000) AS "METRIC_TIME", "VALUE"
FROM "SCH"."TBL"
WHERE $__timeFilter("TIMESTAMP","epoch","ms")

-- Epoch nanoseconds (e.g., 1257894000000000000)
SELECT ADD_SECONDS('1970-01-01', "TIMESTAMP"/1000000000) AS "METRIC_TIME", "VALUE"
FROM "SCH"."TBL"
WHERE $__timeFilter("TIMESTAMP","epoch","ns")

You can also use shorthand notation:

SQL Copy

-- These are equivalent
$__timeFilter("TIMESTAMP","epoch","ms")
$__timeFilter("TIMESTAMP","epoch_ms")

$__fromTimeFilter and $__toTimeFilter macros

These macros return conditions based on Grafana’s From or To time.

Parameters:

• <time_column>: The time field name.
• <format>: Optional. Format of the time column. Can be epoch_s, epoch_ms, epoch_ns, or a custom format like YYYY-MM-DD.
• <comparison_predicate>: Optional. Comparison operator. Default is > for $__fromTimeFilter and < for $__toTimeFilter. Can be =, !=, <>, <, <=, >, or >=.

Examples:

SQL Copy

$__fromTimeFilter("TS")                         -- "TS" > '2014-11-12T11:45:26Z'
$__fromTimeFilter("TS",">=")                    -- "TS" >= '2014-11-12T11:45:26Z'
$__fromTimeFilter("TS","epoch_ms")              -- ADD_SECONDS('1970-01-01', ("TS" / 1000)) > '2014-11-12T11:45:26Z'
$__fromTimeFilter("TS","YYYYMMDDHH24MISS",">=") -- TO_TIMESTAMP("TS",'YYYYMMDDHH24MISS') >= '2014-11-12T11:45:26Z'
$__toTimeFilter("TS")                           -- "TS" < '2015-11-12T11:45:26Z'

$__fromTimeStamp and $__toTimeStamp macros

These macros return SAP HANA timestamp literals for the dashboard’s From and To times.

Example:

SQL Copy

-- Use timestamps directly in expressions
SELECT * FROM "events"
WHERE "event_time" BETWEEN $__fromTimeStamp() AND $__toTimeStamp()

-- Expands to
SELECT * FROM "events"
WHERE "event_time" BETWEEN timestamp'2014-11-12T11:45:26Z' AND timestamp'2015-11-12T11:45:26Z'

$__timeGroup macro

The $__timeGroup(<time_column>,<interval>) macro groups time into interval buckets using SAP HANA’s SERIES_ROUND function.

You can also specify an optional third argument for the rounding mode:

$__timeGroup(<time_column>,<interval>,<round_mode>)

The round mode is passed directly to SAP HANA’s SERIES_ROUND function. Common values include ROUND_HALF_UP, ROUND_HALF_DOWN, ROUND_UP, ROUND_DOWN, ROUND_CEILING, and ROUND_FLOOR.

Example with rounding mode:

SQL Copy

-- Round timestamps down to the nearest hour
SELECT $__timeGroup(timestamp, 1h, ROUND_DOWN), avg("value") as "value"
FROM "salesdata"
WHERE $__timeFilter("timestamp")
GROUP BY $__timeGroup(timestamp, 1h, ROUND_DOWN)

Valid intervals:

Example:

SQL Copy

-- Query with macro
SELECT $__timeGroup(timestamp,1h), "user", sum("value") as "value"
FROM "salesdata"
WHERE $__timeFilter("timestamp")
GROUP BY $__timeGroup(timestamp,1h), "user"
ORDER BY $__timeGroup(timestamp,1h) ASC

-- Expands to
SELECT SERIES_ROUND("timestamp", 'INTERVAL 1 HOUR') as "timestamp", "user", sum("value") as "value"
FROM "salesdata"
WHERE "timestamp" > '2020-01-01T00:00:00Z' AND "timestamp" < '2020-01-01T23:00:00Z'
GROUP BY SERIES_ROUND("timestamp", 'INTERVAL 1 HOUR'), "user"
ORDER BY "timestamp" ASC

Use dynamic intervals

Instead of hardcoding the interval, use $__interval to let Grafana calculate the interval based on the dashboard time range:

SQL Copy

SELECT $__timeGroup(timestamp, $__interval), sum("value") as "value"
FROM "salesdata"
WHERE $__timeFilter("timestamp")
GROUP BY $__timeGroup(timestamp, $__interval)
ORDER BY $__timeGroup(timestamp, $__interval) ASC

For more information about global variables, refer to Global variables.

Use interval values in calculations

Use $__intervalSeconds or $__intervalMs when you need the interval as a number for calculations:

SQL Copy

-- Calculate rate per second using the interval
SELECT $__timeGroup(timestamp, $__interval), 
       sum("bytes") / $__intervalSeconds as "bytes_per_second"
FROM "network_traffic"
WHERE $__timeFilter("timestamp")
GROUP BY $__timeGroup(timestamp, $__interval)

Macro examples

The following examples show macro expansions when the dashboard has 2014-11-12T11:45:26.371Z as the From time and 2015-11-12T11:45:26.371Z as the To time.

Visualize data as logs

To visualize data in the Logs viewer in Explore, select the Logs format for your query.

Requirements

Your log query must include:

• At least one time column (timestamp)
• At least one string column for the log content

Optionally, you can include a level column to set the log level for each row. For supported log levels and their keywords, refer to Logs in Explore.

Any additional columns in your query are treated as additional fields or detected fields in the logs.

Example log query

SQL Copy

SELECT 'hello foo' AS "content", timestamp'2021-12-31 23:59:59' AS "start_time", 'warn'  AS "level" FROM DUMMY UNION
SELECT 'hello bar' AS "content", timestamp'2021-12-30 14:12:59' AS "start_time", 'error' AS "level" FROM DUMMY UNION
SELECT 'hello baz' AS "content", timestamp'2021-12-30 23:59:59' AS "start_time", 'warn' AS "level" FROM DUMMY UNION
SELECT 'hello qux' AS "content", timestamp'2021-12-29 23:59:59' AS "start_time", 'info' AS "level" FROM DUMMY UNION
SELECT 'hello quux' AS "content", timestamp'2021-12-28 23:59:59' AS "start_time", 'unknown' AS "level" FROM DUMMY UNION
SELECT 'hello quuz' AS "content", timestamp'2021-12-27 23:59:59' AS "start_time", 'info' AS "level" FROM DUMMY;

Additional resources

• SAP HANA SQL Reference
• SAP HANA SERIES_ROUND Function
• SAP HANA Series Data Reference