Documentation Plugins Grafana data source for SAP HANA®

Grafana data source for SAP HANA®

SAP HANA® is a high-performance, in-memory database that speeds up data-driven, real-time decisions and actions. It is developed and marketed by SAP®. Grafana’s 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. This plugin is available to customers with a Grafana Enterprise license.

Making the most of SAP HANA® and Grafana Enterprise

Visualize SAP HANA data without moving it: Grafana queries data directly where it lives rather than moving it, which often requires you to pay for redundant storage and ingestion.

Compose panels from multiple sources: With pre-built and customized dashboards, present data from many different data sources within a single dashboard.

Transform and compute at the user level: With less data preparation, a user can transform data and run various computations.

Combine, compute, and visualize within panels: Create mixed data source panels that display related data from SAP HANA and other sources.

Requirements

This plugin has the following requirements:

• SAP HANA® URL, username, and password, along with the necessary permissions
• Connectivity between a Grafana server and a SAP HANA® server, which might be protected by firewall rules, in which case you need to allow Grafana’s IP to connect
• One of the following account types:
  • Any free or paid Grafana Cloud plan or an activated on-prem Grafana Enterprise license. Contracted Cloud customers should refer to their agreement.

Features

Query editor: The plugin comes with an built-in SQL query editor with syntax highlighting that allows you to visualize time series or table data and auto completes basic Grafana macros.

Data source permissions: Control who can view or query SAP HANA data in Grafana.

Annotations: Overlay SAP HANA events or data on any Grafana graph to correlate events with other graph data.

Alerting: Set alerts-based metrics stores in SAP HANA.

Variables for queries: Create template variables in Grafana, which are based on SAP HANA data, and include variables in SAP HANA queries to make dashboards interactive.

Tenant database: Connect to a tenant either by host and port or with host, database name and instance number.

Query editor

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, refer to Annotations.

Create annotations from SAP HANA

1. Click the Dashboard settings gear icon.
2. From the left-hand menu, click Annotations and click the New.
3. From the Data source drop-down menu, select your SAP HANA data source instance.
4. In the Query field, enter a SAP HANA query that returns at least one time field and one text field.
5. From the Format as drop-down menu, select Time Series.
6. For each annotation, configure the From fields.

Note: The annotations feature is available in Grafana 7.2 or higher.

Annotations editor

Annotations in a graph panel

Templates and variables

For more information about variables, refer to Templates and variables.

Creating Grafana variables from an SAP HANA query

To add a new SAP HANA query variable, refer to Add a query variable.

1. From the Dashboard settings gear icon, click Variables, and then click the New button.
2. Using the Query variable type, select the SAP HANA data source as the Data source.
3. Enter the SAP HANA query that returns list of items.

Example :

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

sql Copy

select distinct("username") from "users"

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 value should be unique (if it is not unique then the first value is used). The options in the dropdown will have a text and value that allows you to have a friendly name as text and an id as the value. An example query with hostname as the text and id as the value:

sql Copy

SELECT host_name AS "__text", host_id AS "__value" FROM hosts_list_table

Be sure to only select 1 column in your variable query. If your query returns 2 columns, make sure they have __text and __value columns set correctly. Otherwise, first column will be used as display value and 2nd column will be used as the actual value of the variable. If your query returns more than 2 columns, only the first column will be used and remaining columns will be ignored.

Using Grafana variables in SAP HANA Query

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

sql Copy

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

sql Copy

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.

sql Copy

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 in SAP HANA® Query

$__timeFilter(<time_column>) macro

Below example explains the $__timeFilter(<time_column>) macro.

sql Copy

-- 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 may stored in custom formats. Below example explains the $__timeFilter(<time_column>,<format>) macro which helps to filter custom timestamps based on the time picker.

sql Copy

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, format can be one of valid HANA formats matching your timestamp column. Example: YYYYMMDDHH24MISS is a valid format when your data is stored in 20210421162012 format.

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

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

sql Copy

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.

sql Copy

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() & $__toTimeFilter() macros

$__fromTimeFilter() macro expands to a condition over a time field based on the 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 same as $__fromTimeFilter(). Instead of using Grafana’s from time, it will use to time. Also the default comparison predicate will be <.

See macro examples below for examples.

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

For example, the macro $__timeGroup(timecol,1h) is expanded to SERIES_ROUND("timecol", 'INTERVAL 1 HOUR') in the query. For more information about SERIES_ROUND function, refer to SAP HANA Documentation. Following intervals are valid within SAP HANA interval query.

Example:

Below example explains the $__timeGroup(<time_column>,<interval>) macro. Interval should be in one of the format 1h, 3m etc.

sql Copy

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

will be translated into below query where $__timeGroup(timestamp,1h) is expanded into SERIES_ROUND("timestamp", 'INTERVAL 1 HOUR')

sql Copy

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

Note: When using group by with $__timeGroup macro, make sure that your select, sort by fields follows the same name as your group by field. Otherwise, HANA may not recognize the query.

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 given below.

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

will be translated into the following query based on dashboard time range.

sql Copy

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

You can also use any other macros as described here.

Macro examples

Below are examples of different macros when Grafana has 2014-11-12T11:45:26.371Z as from time and 2015-11-12T11:45:26.371Z as to time.

Visualizing data as logs

To visualize the data in the Logs viewer in explorer, use the Logs format in your query.

When you query using the Logs format, include at least one time column, and one string column, or content column in your query. Optionally, you can set the log level of the particular row, by using a third column called level. For information about the supported log levels and their keywords, see Logs in Explore. If the query returns any additional columns, they will be treated as additional fields or detected fields in the logs.

Here is an example of a valid 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;

Alerting

Easily set up alerts on specific SAP HANA metrics or alert on queries you’ve created:

Steps to create a SAP HANA alert in Grafana

1. Create a graph panel in your dashboard.
2. Create a SAP HANA query in time series format.
3. Click the Alert tab and specify the alerting criteria.
4. Click Test Rule button to test your alert query.
5. Specify the alert recipients, message and error handling.
6. Save the dashboard.

Alerting on non-timeseries data

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

sql Copy

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

Example:

In the below example, table have 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 3.

sql Copy

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'

Configuration

Once the SAP HANA® Grafana plugin is installed, follow the steps below to configure a data source:

• Login as admin user.
• Navigate to Configuration from the Grafana menu on the left.
• Click Data Sources > Add data source and select SAP HANA.
• Provide the necessary information as explained in config editor section.

Config Editor

Config editor requires the following information:

Once you filled the above information, Click Save & Test. This action will validate the connection settings user credentials.

Note: Save & Test validation doesn’t check any access permissions to the schemas. You may need to explicitly grant schema read permissions to the user.

Find the right port to connect

When creating the data source config, You need to specify the correct port number of the SAP HANA® instance. For SAP HANA® cloud instance, usually it will be 443. For on-prem/multi-tenanted instances, you can find the respective port number by following the guidance given in SAP HANA® document

You can determine the ports used by a particular tenant database by querying the M_SERVICES system view, either from the tenant database itself or from the system database.

• From the tenant database: SELECT SERVICE_NAME, PORT, SQL_PORT, (PORT + 2) HTTP_PORT FROM SYS.M_SERVICES WHERE ((SERVICE_NAME='indexserver' and COORDINATOR_TYPE= 'MASTER') or (SERVICE_NAME='xsengine'))
• From the system database: SELECT DATABASE_NAME, SERVICE_NAME, PORT, SQL_PORT, (PORT + 2) HTTP_PORT FROM SYS_DATABASES.M_SERVICES WHERE DATABASE_NAME='<DBNAME>' and ((SERVICE_NAME='indexserver' and COORDINATOR_TYPE= 'MASTER') or (SERVICE_NAME='xsengine'))

Access & Permissions

To connect Grafana to SAP HANA®, use dedicated credentials. Only provide required permissions to the user.

Step 1:

Create a restricted user with username and password. The below query is an example to create a restricted user. This query also disables the force password change:

sql Copy

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

Step 2:

Allow the the user to connect the system through clients such as Grafana:

sql Copy

ALTER USER <USER> ENABLE CLIENT CONNECT;

Step 3:

Give user access to necessary views, tables, and schemas:

sql Copy

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.

Provisioning

Instead of the Grafana UI, you can provision the data source using Grafana provisioning. For an example, refer to the following YAML code block:

yml Copy

apiVersion: 1
datasources:
  - name: SAP HANA
    type: grafana-saphana-datasource
    uid: sap_hana
    access: proxy
    orgId: 1
    jsonData:
      server: xxxxxx-xxxxxx-xxxxxx-xxxxxx-xxxxxx.hana.trial-us10.hanacloud.ondemand.com
      port: 443
      username: GRAFANA_HANA_USERNAME
      tlsSkipVerify: false
      tlsAuth: false
      tlsAuthWithCACert: false
      defaultSchema: EXAMPLE
      databaseName: DATABASE
      instance: "01"
    secureJsonData:
      password: $SECURE_HANA_PASSWORD_FROM_ENVIRONMENT_VAR
      tlsCACert:
      tlsClientCert:
      tlsClientKey:
    version: 1
    editable: false

Learn More

• Add Annotations.
• Configure and use Templates and variables.
• Add Transformations.
• Set up alerting; refer to Alerts overview.
• SAP HANA®.
• SAP HANA® Documentation.
• SAP HANA® SQL Reference.
• SAP HANA® Series Data Reference.

SAP HANA® is the trademark or registered trademark of SAP SE or its affiliates in Germany and in several other countries.