Plugins 〉kdb+
kdb+
Grafana KDB+ Backend Datasource
What is KDB+ Backend Datasource?
AquaQ's kdb+ Backend Datasource is a plugin that adds the ability to query kdb+ from Grafana. It supports a wide-range of Grafana's core features including static/query variables, alerting and support for Grafana's query
API.
Contents
Getting started for users
Adding a data source
- Navigate to settings -> datasources.
- Click add datasource and navigate to kdb-backend-datasource.
- Enter the URL and Port, along with the username and password of your KDB+ instance (if required - if not supplied these will default to
""
). - Enter a timeout value (in ms), default is
1000
miliseconds. - Click save & test.
- An alert at the bottom should display:
kdb+ connected succesfully
.
Creating a dashboard
- Navigate to
Create - Dashboard
from the toolbar on the left. - Create an empty panel. New dashboards will have an empty panel already present. New panels can be added with the
Add panel
button in the top-right taskbar. - Under the KDB+ Query field enter a valid KDB+ query e.g.
([] time:reverse .z.p-0D00:05*til 20;val:til 20)
. - Optionally a custom timeout can be defined in the
Timeout (ms)
entry field. The default is10 000
ms. - Click the
Refresh dashboard
button in the top right, above the Panel visualisation. The data should appear in the visualisation. - Click the
Go back (Esc)
button in the top-left of the page to exit the query editor and return to the dashboard. - The dashboard can be saved with the
Save dashboard
button in the top right. If required, an automated refresh-rate can be set for the dashboard, click the drop-down menu next to theRefresh dashboard
button in the top-right and set your desired refresh-rate. Custom refresh-rates can be added inDashboard settings
.
Variables
This plugin can handle static and query variables. It also allows the user to chain queries.
Static & Multi-Value Variables
These can be entered under the Custom
variable type using Grafana's standard format, (i.e. comma separated list). Static variables can then be used in queries in the form:
${variable_name}
If using Multi-value
variables where more than one value can be selected at a time, please refer to Grafana's documentation on formatting variables. We would recommend injecting Multi-value
variables in the csv
format, then splitting and casting these to a list of the required type in kdb+ with the sv
operator, such as in the following select
statement:
select from trade where exchange in `$"," vs "${multi_variable_name:csv}"
Temporal Variables
Temporal variables (e.g. ${__from}
, ${__to}
etc) are injected by Grafana as the number of miliseconds since the Unix epoch (e.g. 1594671549254
corresponds to Jul 13 2020 20:19:09
). To use temporal variables in kdb+ we need to manipulate these to match kdb+'s accepted formats.
This can be done by adjusting from miliseconds to seconds by dividing by 1000, converting to a decimal string with .Q.f
and then tokking
to a timestamp:
("P"$.Q.f[3] ${__from}%1000)
Alternatively Grafana-injected temporal variables can be formatted to the ISO-8601
format and this can be tokked
to a datetime
datatype in kdb+:
("Z"$"${__from:date}")
As of kdb+ version 4.0 ISO-8601
formatted date-times cannot be directly tokked
to timestamps.
If the timestamp
being referenced only requires accuracy to a single second, then they can be injected directly in unix-time and tokked
to timestamps:
("P"$"${__from:date:seconds}")
Query & Chained Variables
These can be entered under the Query
variable type. These variables run a query against the target datasource before the panel queries are run, and from this meta-query Grafana builds a variable/list of variables. The input query must return a flat table
(see Restrictions) from which the first column will be used to generate variables. In older versions of Grafana the output may be required to be either strings
or symbols
(in newer Grafana versions numeric datatypes are also supported).
There is an optional Timeout
field which if not defined will default to 10 000
ms. A preview of returned variables will be displayed at the bottom of this page after the variable has been updated by pressing the Update
button.
Query variables can also take in other variables as part of the query which they run (sometimes called Chained
variables). The format for this is the same as static & multi-value variables (${variable_name}
)
Security
By default we pass an empty ""
string for both the username and password, these can be overridden in the datasource settings.
TLS is also supported - enable with the TLS Client Auth
switch. Enter the client TLS key and client TLS cert into the fields provided. To skip server vertification of the TLS certificate use the Skip TLS Verify
switch. A custom Certificate Authority certificate can be used if the With CA Cert
switch is enabled - use if the kdb+ datasource is running a custom-signed certificate.
kdb+ Queries
The queries are passed to kdb+ as a two item synchronous query (will be evaluated by .z.pg
) in the following kdb+ form:
({[x] value x[`Query;`Query]};**QUERYDATA**)
The **QUERYDATA**
is a dictionary (kdb+ type 99
) with a nested structure as follows:
Key | Value (kdb+ type ) |
---|---|
AQUAQ_KDB_BACKEND_GRAF_DATASOURCE | Plugin Version (float atom ) |
Time | Query Timestamp (timestamp atom ) |
OrgID | Grafana Organisation ID (long atom ) |
Datasource | Datasource Info Object (dictionary ) |
User | User Info Object (dictionary ) |
Query | Query Info Object (dictionary ) |
Timeout | Grafana-side timeout duration in ms (long atom ) |
Datasource Info Object
Key | Value (kdb+ type ) |
---|---|
ID | Datasource ID assigned by the Grafana instance (long atom ) |
Name | Name of the datasource as assigned by the user (char list ) |
UID | Datasource UID assigned by the Grafana instance (char list ) |
Updated | Timestamp of when the datasource was last updated (timestamp atom ) |
URL | URL of the datasource (?) (char list ) |
User | UserName of User who created the datasource (char list ) |
User Info Object
Key | Value (kdb+ type ) |
---|---|
UserName | User's Grafana name (not login username) (char list ) |
UserEmail | User's Grafana email address (char list ) |
UserLogin | User's Grafana login username (char list ) |
UserRole | User's Grafana role (char list ) |
Query Info Object
N.B. The RefID
, MaxDataPoints
, Interval
and TimeRange
keys are not present in HEALTHCHECK
type queries.
Key | Value (kdb+ type ) |
---|---|
RefID | Ref ID of query (char list ) |
Query | Query string which is evaluated (char list ) |
QueryType | Query type (HEALTHCHECK or QUERY ) (symbol atom ) |
MaxDataPoints | Panel's defined max data-points (currently unused) (long atom ) |
Interval | Panel's defined interval (currently unused) (long atom ) |
TimeRange | __from and __to time range of query (2 item timestamp list ) |
Alerts
Before creating an alert, create a contact point under alerting -> contact points. Then create a notification policy under Alerting -> notification policy.
To create an alert on a panel, navigate to the relevant dashboard and choose the edit option from here navigate to the Alert menu. Fill out the relevant Rule name, type and folder. Enter your kdb+ query and run the queries. You will be able to use expressions to query the data from this query. Next of all set the alert conditions, making sure to select the expression and to set the evaluate duration. Finally set the custom label in Alert details.
Timezones
kdb+ stores its timestamps and datetimes in a time-zone agnostic form; these will be interpreted by Grafana as having no time-zone offset (UTC), therefore we advise users to set the time-zone of any dashboards using this plugin to UTC. This can be done in Dashboard settings - Time options - Timezone
.
Restrictions
All queries must return either a flat table
(kdb+ datatype 98) or a grouped table
(kdb+ datatype 99 where key
and value
of the dictionary are both congruent tables). If aggregation is used alongside grouping for grouped tables
then any aggregated columns will be projected to the same length as the rest of the data-frame.
Infinities and nulls in Grafana do not share same data type as in kdb+. An underlying string value representation is displayed rather than the null or infinity value held in kdb+. It is recommended Grafana send users handle null representations as per their data schema, data dictionary.
Columns
The columns must be a single, constant datatype - there cannot be mixed lists or nested lists as columns (excluding string
columns and grouped entries, see Grouped Tables Handling below).
Grouped Tables Handling
If the query evaluated returns a grouped table to Grafana, then each grouping will be returned by Grafana as a seperate frame. The name of each frame is a string representation of the key of each grouping (semicolon seperated if multiple keys are present).
Nulls and Infinities
The table below displays how nulls, infinities and zeroes are handled for each data type:
Field | Short | Int | Long | Chars | Symbols | Timestamps | Times | Datetimes | Timespans | Months | Dates | Minutes | Seconds |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Zero | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Null | -32768 | -2147483648 | -9223372036854776000 | -2147483648 | -2147483648 | -2147483648 | -2147483648 | -2147483648 | -2147483648 | -2147483648 | -2147483648 | -2147483648 | -2147483648 |
NegInf | -32767 | -2147483647 | -9223372036854776000 | -2147483647 | -2147483647 | -2147483647 | -2147483647 | -2147483647 | -2147483647 | -2147483647 | -2147483647 | -2147483647 | -2147483647 |
Inf | 32767 | 2147483647 | 9223372036854776000 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 | 2147483647 |
Grafana Cloud Free
- Free tier: Limited to 3 users
- Paid plans: $55 / user / month above included usage
- Access to all Enterprise Plugins
- Fully managed service (not available to self-manage)
Self-hosted Grafana Enterprise
- Access to all Enterprise plugins
- All Grafana Enterprise features
- Self-manage on your own infrastructure
Grafana Cloud Free
- Free tier: Limited to 3 users
- Paid plans: $55 / user / month above included usage
- Access to all Enterprise Plugins
- Fully managed service (not available to self-manage)
Self-hosted Grafana Enterprise
- Access to all Enterprise plugins
- All Grafana Enterprise features
- Self-manage on your own infrastructure
Grafana Cloud Free
- Free tier: Limited to 3 users
- Paid plans: $55 / user / month above included usage
- Access to all Enterprise Plugins
- Fully managed service (not available to self-manage)
Self-hosted Grafana Enterprise
- Access to all Enterprise plugins
- All Grafana Enterprise features
- Self-manage on your own infrastructure
Grafana Cloud Free
- Free tier: Limited to 3 users
- Paid plans: $55 / user / month above included usage
- Access to all Enterprise Plugins
- Fully managed service (not available to self-manage)
Self-hosted Grafana Enterprise
- Access to all Enterprise plugins
- All Grafana Enterprise features
- Self-manage on your own infrastructure
Grafana Cloud Free
- Free tier: Limited to 3 users
- Paid plans: $55 / user / month above included usage
- Access to all Enterprise Plugins
- Fully managed service (not available to self-manage)
Self-hosted Grafana Enterprise
- Access to all Enterprise plugins
- All Grafana Enterprise features
- Self-manage on your own infrastructure
Installing kdb+ on Grafana Cloud:
Installing plugins on a Grafana Cloud instance is a one-click install; same with updates. Cool, right?
Note that it could take up to 1 minute to see the plugin show up in your Grafana.
Installing plugins on a Grafana Cloud instance is a one-click install; same with updates. Cool, right?
Note that it could take up to 1 minute to see the plugin show up in your Grafana.
Installing plugins on a Grafana Cloud instance is a one-click install; same with updates. Cool, right?
Note that it could take up to 1 minute to see the plugin show up in your Grafana.
Installing plugins on a Grafana Cloud instance is a one-click install; same with updates. Cool, right?
Note that it could take up to 1 minute to see the plugin show up in your Grafana.
Installing plugins on a Grafana Cloud instance is a one-click install; same with updates. Cool, right?
Note that it could take up to 1 minute to see the plugin show up in your Grafana.
Installing plugins on a Grafana Cloud instance is a one-click install; same with updates. Cool, right?
Note that it could take up to 1 minute to see the plugin show up in your Grafana.
Installing plugins on a Grafana Cloud instance is a one-click install; same with updates. Cool, right?
Note that it could take up to 1 minute to see the plugin show up in your Grafana.
For more information, visit the docs on plugin installation.
Installing on a local Grafana:
For local instances, plugins are installed and updated via a simple CLI command. Plugins are not updated automatically, however you will be notified when updates are available right within your Grafana.
1. Install the Data Source
Use the grafana-cli tool to install kdb+ from the commandline:
grafana-cli plugins install
The plugin will be installed into your grafana plugins directory; the default is /var/lib/grafana/plugins. More information on the cli tool.
Alternatively, you can manually download the .zip file for your architecture below and unpack it into your grafana plugins directory.
Alternatively, you can manually download the .zip file and unpack it into your grafana plugins directory.
2. Configure the Data Source
Accessed from the Grafana main menu, newly installed data sources can be added immediately within the Data Sources section.
Next, click the Add data source button in the upper right. The data source will be available for selection in the Type select box.
To see a list of installed data sources, click the Plugins item in the main menu. Both core data sources and installed data sources will appear.
Changelog
1.0.0 (Unreleased)
Initial release.