Connect to an SAP HANA data source

SAP HANAis a high-performance, in-memory database that speeds up data-driven, real-time decisions and \ actions. It is developed and marketed by SAP. The SAP HANA data source plugin helps you to connect your SAP HANA instance with Grafana.

With the SAP HANA Grafana Enterprise plugin, you can visualize your SAP HANA data alongside all of your other data sources in Grafana as well as log and metric data in context. This plugin includes a built-in query editor, supports annotations, and it allows you to set alerting thresholds, control access, set permissions, and more.

Features

Adding the data source

Access and permissions

To connect Grafana to SAP HANA, use dedicated credentials. Only provide required permissions to the user. First, create a restricted user with username and password. The following query is an example to create a restricted user. This query also disables the force password change.

CREATE RESTRICTED USER <USER> PASSWORD <PASSWORD> NO FORCE_FIRST_PASSWORD_CHANGE;

Next, allow the the user to connect the system through clients such as Grafana with the following:

ALTER USER <USER> ENABLE CLIENT CONNECT;

Finally, give the user access to the necessary views, tables, and schemas.

ALTER USER <USER> GRANT ROLE PUBLIC;
GRANT SELECT ON SCHEMA <SCHEMA> TO <USER>;

User level permissions

Limit access to SAP HANA by clicking on the Permissions tab in the data source configuration page to enable data source permissions. On the permission page, Admins can enable permissions and restrict query permissions to specific Users and Teams.

Query editor

The SAP HANA Grafana plugin comes with an SQL query editor where you can enter any HANA queries. If your query return timeseries data, you can format it as timeseries for visualizing them in a graph panel. The query editor provides auto completion for supported Grafana macros and syntax highlighting of your SQL query.

Annotations

You can use SAP HANA queries as the sources of Grafana annotations. Your annotation query should return at least one time column and one text column. For more information about annotations, see Annotations.

Templates and variables

To add a new SAP HANA query variable, see Adding a query variable. Use your SAP HANA data source as your data source.

The following example query returns the distinct list of username from the users table.

select distinct("username") from "users"

Templates and variables

You can use any Grafana variable in your query. The following examples shows how to use the single / multi variable in your query.

-- For example, following query
select * from "users" where "city" = ${city}
-- will be translated into
select * from "users" where "city" = 'london'
--- where you can see ${city} variable translated into actual value in the variable

Similar to text, variables also works for numeric fields. In the below example, ${age} is a text box variable where it accepts numbers and then compares against the numeric field in the table.

select * from "users" where "age" > ${age}
--- wil be translated into
select * from "users" where "age" > '36'

If your variable returns multiple values, then you can use it in SAP HANA query's in condition like below. Note the brackets surrounding the variable to make the where in condition valid in SAP HANA.

select * from "users" where "city" in (${cities})
--- will be translated into
select * from "users" where "city" in ('london','perth','delhi')
--- where you can see ${cities} turned into a list of grafana variables selected.
--- You can also write the same query using shorthand notation as shown below
select * from "users" where "city" in ($cities)

Macros

$__timeFilter(<time_column>) macro

The following example explains the $__timeFilter(<time_column>) macro:

- In the following example, the query
select ts, temperature from weather where $__timeFilter(ts)
--- will be translated into
select ts, temperature from weather where ts > '2021-02-24T12:52:48Z' AND ts < '2021-03-24T12:52:48Z'
--- where you can see the grafana dashboard's time range is applied to the column ts in the query.

$__timeFilter(<time_column>, <format>) macro

In some cases, time columns in the database are stored in custom formats. The following example explains the $__timeFilter(<time_column>, <format>) macro, which helps to filter custom timestamps based on grafana's time picker:

SELECT TO_TIMESTAMP("TS",'YYYYMMDDHH24MISS') AS METRIC_TIME , "VALUE" FROM "SCH"."TBL" WHERE $__timeFilter("TS","YYYYMMDDHH24MISS") -- TS is in 20210421162012 format
SELECT TO_TIMESTAMP("TS",'YYYY-MON-DD') AS METRIC_TIME , "VALUE" FROM "SCH"."TBL" WHERE $__timeFilter("TS","YYYY-MON-DD") -- TS is in 2021-JAN-15 format

In the macro, the format can be one of the valid HANA formats matchting your timestamp column. For example, YYYYMMDDHH24MISS is a valid format when your data is stored in 20210421162012 format.

$__timeFilter(<time_column>, "epoch" <format>) macro

In some cases, timestamps are stored as epoch timestamps in your DB. The following example explains the $__timeFilter(<time_column>, "epoch" <format>) macro which helps to filter epoch timestamps based on grafana's time picker. In the macro, format can be one of ms,s or ns. If not specified, s will be treated as default format.

SELECT ADD_SECONDS('1970-01-01', "TIMESTAMP") AS "METRIC_TIME", "VALUE" FROM "SCH"."TBL" WHERE $__timeFilter("TIMESTAMP","epoch") -- Example : TIMESTAMP field stored in epoch_second format 1257894000
SELECT ADD_SECONDS('1970-01-01', "TIMESTAMP") AS "METRIC_TIME", "VALUE" FROM "SCH"."TBL" WHERE $__timeFilter("TIMESTAMP","epoch","s") -- Example : TIMESTAMP field stored in epoch_second format 1257894000
SELECT ADD_SECONDS('1970-01-01', "TIMESTAMP"/1000) AS "METRIC_TIME", "VALUE" FROM "SCH"."TBL" WHERE $__timeFilter("TIMESTAMP","epoch","ms") -- Example : TIMESTAMP field stored in epoch_ms format 1257894000000
SELECT ADD_SECONDS('1970-01-01', "TIMESTAMP"/1000000000) AS "METRIC_TIME", "VALUE" FROM "SCH"."TBL" WHERE $__timeFilter("TIMESTAMP","epoch","ns") -- Example : TIMESTAMP field stored in epoch_nanoseconds format 1257894000000000000

Instead of using third argument to the $__timeFilter, you can use one of epoch_s, epoch_ms or epoch_ns as your second argument..

SELECT ADD_SECONDS('1970-01-01', "TIMESTAMP"/1000) AS "METRIC_TIME", "VALUE" FROM "SCH"."TBL" WHERE $__timeFilter("TIMESTAMP","epoch","ms")
-- is same as
SELECT ADD_SECONDS('1970-01-01', "TIMESTAMP"/1000) AS "METRIC_TIME", "VALUE" FROM "SCH"."TBL" WHERE $__timeFilter("TIMESTAMP","epoch_ms")

$__fromTimeFilter() and $__toTimeFilter() macros

The $__fromTimeFilter() macro expands to a condition over a time field based on Grafana time picker's from time.

This accepts three parameters. First parameter is time field name. You can pass comparison_predicate or format of the time column as second argument. If you want to pass both, then format is second parameter and use comparison_predicate as your third parameter.

<format> If the format is not specified, plugin wil assume that the time column is of timestamp/date type. If your time column is stored in any other format than timestamp/date, then pass the format as second argument. <format> can be one of epoch_s, epoch_ms,epoch_ns or any other custom format like YYYY-MM-DD.

<comparison_predicate> optional parameter. If not passed, plugin will use > as comparison predicate. <comparison_predicate> can be one of =, !=, <>, <, <=, >, >=

$__toTimeFilter() works the same as $__fromTimeFilter(). Instead of using Grafana's from time, it will use to time. Also the default comparison predicate will be <.

$__timeGroup(<time_column>, <interval>)

For example, the macro $__timeGroup(timecol,1h) is expanded to SERIES_ROUND("timecol", 'INTERVAL 1 HOUR') in the query.

The following example explains the $__timeGroup(<time_column>, <interval>) 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

This is translated into the following query where $__timeGroup(timestamp,1h) is expanded into SERIES_ROUND("timestamp", 'INTERVAL 1 HOUR').

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

If you don't want to hard code the interval in $__timeGroup() function, then you can leave that to Grafana by specifying $__interval as your interval. Grafana will calculate that interval from dashboard time range. Example query:

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

That query is translated into the followin query based on the dashboard time range.

SELECT SERIES_ROUND("timestamp", 'INTERVAL 1 MINUTE'), sum("value") as "value"
FROM "salesdata"
WHERE "timestamp" > '2019-12-31T23:09:14Z' AND "timestamp" < '2020-01-01T23:17:54Z'
GROUP BY SERIES_ROUND("timestamp", 'INTERVAL 1 MINUTE')
ORDER BY SERIES_ROUND("timestamp", 'INTERVAL 1 MINUTE') ASC

Alerting

Alerting on non-timeseries data

To alert on non-timeseries data, use the TO_TIMESTAMP('${__to:date}') macro to make non-timeseries metrics into timeseries. This will convert your metric into single point time series query. The format of the query is given below

SELECT TO_TIMESTAMP('${__to:date}'),  <METRIC> FROM <TABLE≶ WHERE <YOUR CONDITIONS>

In the following example, a table has four fields called username, age, city and role. This table doesn't have any time field. We want to notify when the number of users with dev role is less than three.

SELECT  TO_TIMESTAMP('${__to:date}'), count(*) as "count" FROM (
   SELECT 'John' AS "username", 32 AS "age", 'Chennai' as "city", 'dev' as "role" FROM dummy
   UNION ALL SELECT 'Jacob' AS "username", 32 AS "age", 'London' as "city", 'accountant' as "role" FROM dummy
   UNION ALL SELECT 'Ali' AS "username", 42 AS "age", 'Delhi' as "city", 'admin' as "role" FROM dummy
   UNION ALL SELECT 'Raja' AS "username", 12 AS "age", 'New York' as "city", 'ceo' as "role" FROM dummy
   UNION ALL SELECT 'Sara' AS "username", 35 AS "age", 'Cape Town' as "city", 'dev' as "role" FROM dummy
   UNION ALL SELECT 'Ricky' AS "username", 25 AS "age", 'London' as "city", 'accountant' as "role" FROM dummy
   UNION ALL SELECT 'Angelina' AS "username", 31 AS "age", 'London' as "city", 'cxo' as "role" FROM dummy
) WHERE "role" = 'dev'