No description, website, or topics provided.
Switch branches/tags
Clone or download
Pull request Compare This branch is 13 commits ahead of sraoss:master.
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
dist
src/public
.gitignore
Gruntfile.js
LICENSE
NOTICE
README.md
package.json
plugin.json

README.md

grafana-sqldb-datasource

SQL DB database is the datasource of Grafana 3.0, which provides the support of MySQL, PostgreSQL and ClickHouse as a backend database.

Features

1. Query Editor

Forked from InfluxDB plugin, this datasource frontend has been implemented. Therefore, you can issue SQL queries in the same manner as in influxDB.

(Defining with query editor)
Query Editor

Each parts (schema, table, column name and data type) refer to information_schema in RDB.

2. Text Editor Mode (support raw SQL)

You can switch to raw query mode by clicking icon.

(Toggling edit mode)
Query Editor

Raw queries are generated referring to inputs of query editor. You can modify them (add JOIN another tables, sub queries in WHERE clause, and so on).

Macros

If you tries to modify a raw query or define it by yourself without choosing parts in query editor, you can use these macros.

macro detail
$timeColumn This is replaced to "TIME" from [TIME : TYPE] in query editor.
$dateColumn This is replaced to "DATE" from [DATE] in query editor (optional).
$timeFilter This is replaced to "$timeColumn < $from AND $timeColumn > $to".
$from This is replaced to "from" word of the time range for panels, and this is casted as "TYPE" from [TIME : TYPE] in query editor.
$to This is replaced to "to" of the time range for panels, and this is casted as "TYPE" from [TIME : TYPE] in query editor.
$unixFrom This is replaced to "from" of the time range for panels, and this is casted as number of unix timestamp.
$unixTo This is replaced to "to" of the time range for panels, and this is casted as number of unix timestamp.
$timeFrom This is replaced to "from" of the time range for panels, and this is casted as timestamp.
$timeTo This is replaced to "to" of the time range for panels, and this is casted as timestamp.
$dateFrom This is replaced to "from" of the time range for panels, and this is casted as date.
$dateTo This is replaced to "to" of the time range for panels, and this is casted as date.

3. Templating

You can create a template variable in Grafana and have that variable filled with values from any SQL Database metric exploration query. Then, You can use this variable in your SQL Database metric queries.

(Defining a template)
Template Editor

(Use a template vartiable in query editor)
Query Editor

4. Annotations

Annotaions is also supported. You can issue SQL queries and add event information above graphes. 

(Defining an annotation)
Annotations Editor

(Annotations in a graph)
Annotations Graph

5. Using timestamp and unixtimestamp as a time-serise column

You can choose a time-series column from the table definition.

(Choosing the column for time series)
time series

Data types

The supported data types for time-series are:

category PostgreSQL MySQL ClickHouse
timestamp type timestamp without time zone
timestamp with time zone
timestamp
datetime
number type
(if you use unixtimesamp as a time-serise column)
bigint
integer (int)
float
real
double precision
decimal
numeric
bigint
integer (int)
float
real
double precision
decimal
numeric
DateTime, any numeric type
date type Date, any numeric type

Example

Query with variables

SELECT $unixtimeColumn * 1000 AS time_msec,
       avg(cpu_usr)
       FROM myschema.dstat
       WHERE tag ~* '/^$host_t$/' AND
             $timeFilter
GROUP BY $unixtimeColumn
ORDER BY $unixtimeColumn

Actual query in PostgreSQL

timestamp type

SELECT round(extract(epoch from coltime::timestamptz) / 1200) * 1200 * 1000 AS time_msec,
       avg(cpu_usr)
       FROM myschema.dstat
       WHERE tag ~* '/^webserver123$/' AND
coltime > (now() - '7d'::interval)
GROUP BY round(extract(epoch from coltime::timestamptz) / 1200) * 1200
ORDER BY round(extract(epoch from coltime::timestamptz) / 1200) * 1200

number type

SELECT round(coltime / 1200) * 1200 * 1000 AS time_msec,
       avg(cpu_usr)
       FROM myschema.dstat
       WHERE tag ~* '/^webserver123$/' AND
coltime > extract(epoch from (now() - '7d'::interval)::timestamptz)
GROUP BY round(coltime / 1200) * 1200
ORDER BY round(coltime / 1200) * 1200

Actual query in MySQL

timestamp type

SELECT (UNIX_TIMESTAMP(coltime) DIV 1200) * 1200 * 1000 AS time_msec,
       avg(cpu_usr)
       FROM myschema.dstat
       WHERE tag REGEXP '^webserver123$' AND
             coltime > DATE_SUB(CURDATE(), INTERVAL 7 DAY)
             GROUP BY (UNIX_TIMESTAMP(coltime) DIV 1200) * 1200
             ORDER BY (UNIX_TIMESTAMP(coltime) DIV 1200) * 1200;

number type

SELECT (coltime DIV 1200) * 1200 * 1000 AS time_msec,
       avg(cpu_usr)
       FROM myschema.dstat
       WHERE tag REGEXP '^webserver123$' AND
coltime > UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 7 DAY))
GROUP BY (coltime DIV 1200) * 1200
ORDER BY (coltime DIV 1200) * 1200

Tested versions of RDBMS

  • PostgreSQL
9.5 9.4 9.3 9.2 9.1
OK OK (not yet) OK (not yet)
  • MySQL
5.7 5.6 5.5
(not yet) (not yet) OK
  • ClickHouse
1.1.54164
OK

References