User Guide

July 4, 2019 · View on GitHub

Introduction

Setup environments

Step 1: GridDB server setup

Step 2: GridDB server data

Step 3: GridDB Plugin setup

How to use

Adding the data source

Create variable

Create annotation

Create query to draw Graph

Create query with Explore

Appendix

Query special expression

Introduction

This Plugin will be used to get data from GridDB database and display as graph or table base on Grafana platform.

Setup environments

OS for this user guide is CentOS 7.6

Step 1: GridDB server setup

Step 2: GridDB server data

Insert data to 3 containers with schema as below (or using sample data creation tool on https://github.com/griddb/griddb-datasource/blob/master/sample/SampleData)

  • Container 1:

    • Container name: one_thousand_rows

    • Container type: TIME_SERIES

    • Container schema:

      | Name    	| Type      	| Constraints 	|
|---------	|-----------	|-------------	|
| time    	| TIMESTAMP 	| NOT NULL    	|
| column1 	| INTEGER   	|             	|
| column2 	| INTEGER   	|             	|
| column3 	| INTEGER   	|             	|
| column4 	| INTEGER   	|             	|
| column5 	| INTEGER   	|             	|

  • Container 2:

    • Container name: Multiple_Time_Columns

    • Container type: COLLECTION

    • Container schema:

      | Name    	| Type      	| Constraints 	|
|---------	|-----------	|-------------	|
| time_1  	| TIMESTAMP 	| NOT NULL    	|
| time_2  	| TIMESTAMP 	|             	|
| column1 	| STRING    	|             	|
| column2 	| INTEGER   	|             	|

  • Container 3:

    • Container name: Multiple_Type

    • Container type: TIME_SERIES

    • Container schema:

      | Name    	| Type      	| Constraints 	|
|---------	|-----------	|-------------	|
| time    	| TIMESTAMP 	| NOT NULL    	|
| column1 	| INTEGER   	|             	|
| column2 	| STRING    	|             	|
| column3 	| BOOL      	|             	|
| column4 	| DOUBLE    	|             	|
| column5 	| FLOAT     	|             	|

Step 3: GridDB Plugin setup

  1. Go to $GRAFANA_HOME/data/plugins, create a new folder "GridDB-plugin"
    • Note: This folder is created after run Grafana server the first time.
  2. Copy folder "dist" to folder "GridDB-plugin"
  3. Restart your Grafana server

Note: The folder "dist" includes files created by building with the following method.

Precondition: npm is installed

Go to source include file package.json and run the command below:

    $ npm install -g yarn
    $ yarn install
    $ yarn build

How to use

Adding the data source

Step 1: In the side menu select Configuration>Data Sources

Figure1

Figure 1: Select Data Sources

Step 2: Click the Add data source button

Step 3: Select GridDB from data source list

Step 4: Input valid value as example figure below

Step 5: Click Save & Test button to check the connection to datasource. System uses basic authentication to authenticate to GridDB server

Note: Check appendix about $_minInterval for Min time interval input.

Figure2

Figure 2: Datasource when connecting to GridDB server successfully

Create Variable

Step 1: Open a Dashboard

Step 2: Click to the dashboard Settings button on the top screen

Figure3

Figure 3: Select Dashboard Settings

Step 3: In the side menu under the Settings and select Variables

Step 4: Click the Add variable button in the center screen

Step 5: Input data for variable screen. User may be use one of three formats below to create variable:

  1. To get all containers
$griddb_container_list
  1. To get all columns of a specific container
$griddb_column_list({container name})
  1. To get data of a specific container
$griddb_query_data({container name}, {columns}, {TQL})

Step 6: Focus to other field to show preview data of variable

Figure4

Figure 4: Preview value of the variable

Step 7. Click Add/Update and select Save As then click Save button in confirm popup to save new a variable

After creating variable successfully, variable will be displayed on the query data screen

Figure5

Figure 5: All variables display on the query data screen

Figure6

Figure 6: Options for the specific variables

Variable automatic added into container list box

Figure7

Figure 7: Variables is displayed on the container list select box

Step 8: Use variable in Select box mode

Choose $container from container select box

Figure8

Figure 8: Use variable from Container select box

Select container “one_thousand_rows” from **container∗∗.Grafanawillbeautoreplacecontainer**. Grafana will be auto replace container by selected container

Figure9

Figure 9: Select container “one_thousand_rows”

Figure10

Figure 10: When the variable is changed, query auto executed with the selected container

Create annotation

Step 1: Open a dashboard and click to the Settings button on the top screen.

Step 2: In the side menu select Settings>Annotations

Step 3: Click the Add Annotation Query button in the center screen.

Step 4: Currently, GridDB plugin is supporting two annotation types are normal annotation and regions annotation. The first, let input data below to create a normal annotation

Figure11

Figure 11: Create a normal annotation

Note: Add “where _timeFilter”clausetolimitthedatainsidethedashboardtimerange.Checkappendixformoreinformationabout\_timeFilter” clause to limit the data inside the dashboard time range. Check appendix for more information about _timeFilter.

Step 5: Click Add and Save to add a new annotation. After that, back to query data screen to check annotation.

Figure12

Figure 12: Normal annotation on query data screen

Annotation name will be displayed on top of query data screen. The result of annotation display on the graph. Hover to annotation line to display information of annotation on a tooltip

Figure13

Figure 13: Tooltip of annotation

Step 6: To create a regions annotation, let checked the regions events checkbox and input data below:

Figure14

Figure 14: Create regions annotation

Note: Regions annotation only support for a container has multiple time columns. Add “where __rangeFilter” to limit the data inside the dashboard time range. Check appendix for more information about __rangeFilter.

Step 7: Click Add and Save to create new regions annotation. After that, back to query data screen to check annotation. Annotation name will be display on top of query data screen. Result of annotation display on graph

Figure15

Figure 15: Regions annotation display on query data screen

Hover to annotation line to display information of annotation on tooltip

Figure16

Figure 16: Tooltip of regions annotation

ItemDescription
1Region annotation
2This is the dash board time range.

Create query to draw Graph

Precondition:

Variables list below:

Variable nameSelected optionDescription
Containerone_thousand_rowsGet container list in GridDB database
Columncolumn1Get column list in one_thousand_rows container
Query13Get all value of column1 in one_thousand_rows container

Select time range in UI. In actually, Grafana just display respond data which has time in selected time range so GridDB plugin auto add selected time range into TQL query to limit respond data. So graph just display when the user selects the appropriated time range.

Figure17

Figure 17: Quick select time range

Figure18

Figure 18: Input time range

Item Description
1 Grafana provides some Quick ranges for a user. In this case chose Last 90 days.
2

After selected Last 90 days, the value will auto change to:

From: now-90d

To: now

3 Grafana provides input specific time by manual

Create query

Step 1: Select GridDB datasource to show default GUI of create query function

Figure19

Figure 19: Default GUI when create new query

Step 2: Select container, time column and list column to view data. “Choose container”, “Time column”, “View column” are default values.

Figure20

Figure 20: List containers are displayed on container dropdown

Figure21

Figure 21: View column of container are displayed on view dropdown

Item Description
1

List of all variable name and container name.

  • In this case have 3 variables which have defined in pre-condition: All variable will be add ‘$’ at start: $Container, $Column, $Query. Although dropdown display all variable but just variable which select container list have meaning in this field.

  • After list variables will be list container name.

In this example select ‘$Container’
2

List of column which have type ‘TIMESTAMP’ in selected container.

In this case selected option of $Container is ‘one_thousand_rows’ so it will display all time column in ‘one_thousand_rows’ container, select ‘time’
3

List of all numeric column in selected container.

In this case is all numeric column in ‘one_thousand_rows’ container, select ‘column1’ and ‘column2’

After select ‘Choose container’, ‘Time column’, ‘View column’ Query panel as below picture.

Figure22

Figure 22: Choose container, time column and view column for query

Step 3: Add one or more conditions for query

Click to Plus button to add condition. Item 1 in below picture is list in all suggest column in ‘one_thousand_rows’ container, select ‘column4’ option

Figure23

Figure 23: List columns of container is suggested for condition

Item 2 in below picture is list of all supported operator, select ‘>=’ operator.

Figure24

Figure 24: List operators are displayed in compare dropdown

Item 3 in below picture is input field.

Figure25

Figure 25: Use enter value by manual for condition clause

To add more condition, click Plus button. In item in below picture, can input by manual variable name or column name. In this example, select ‘time’ column in dropdown as a key and input ‘$__timeFrom’ as a value.

Figure26

Figure 26: User may be add more condition clause

Do same as previous step, all condition as item 5 in below picture.

Figure27

Figure 27: Multiple conditions are added

After that Graph as below picture

Figure28

Figure 28: Data is displayed on Graph

ItemDescription
6The Graph were draw by Grafana base on respond data. In this panel have 2 graph, one green, one yellow
Line green: ‘one_thousand_rows.column1’ display data for column1
Line yellow: ‘one_thousand_rows.column2’ display data for column2
7Graph legend

Grafana also support user to see the real query which were sent to server. Let take a look:

Figure29

Figure 29: Query inspector

With item 8, refer table below to explain query

Select box query Query inspector
$Container = one_thousand_rows SELECT * FROM one_thousand_rows
WHERE column4 >= 400 WHERE column4 >= 400
time >= $__timeFrom
$__timeFrom = TIMESTAMP('2018-11-15T02:55:03.871Z') 		"time" >= TIMESTAMP('2018-11-15T02:55:03.871Z')
time <= $__timeFrom
$__timeFrom = TIMESTAMP('2018-11-15T02:55:03.871Z') 		"time" <= TIMESTAMP('2018-11-15T02:56:00.899Z')
$Column = column1
$Query = 13
$Column >= $Query 		column1 >= 13 AND
this item add into end of TQL query by automatically base on selected time range (time > TIMESTAMP('2018-11-15T02:55:03.871Z') AND time < TIMESTAMP('2018-11-15T02:56:00.899Z'))
LIMIT 10000 LIMIT = 10000
OFFSET 0 OFFSET = 0

Note: Check appendix for more information about “$” special expression.

From Figure 30, User also limit respond records by specify value for Limit and change name for graph by define Alias

Figure30

Figure 30: Change alias of graph

ItemDescription
9Both of graph green and yellow just have 2 points data
11Update limit to 2
12Update ALIAS BY to ‘$_col’. After update item 11, 12, then item 9, 10 will be changed as above picture
10Legend change to ‘column1’ for green line and ‘column2’ for yellow line

From Figure 31, User also change to TIME_SAMPLING by click to TIME_SAMPLING checkbox (item 14). The Graph will be changed as item 13:

Figure31

Figure 31: Using timesampling in query

From Figure 32, User can change to Manual Input Mode by click into menu button after that select ‘Toggle Edit Mode’ as below picture

Figure32

Figure 32: Toggle edit mode

After that Manual Input Mode as below picture

Figure33

Figure 33: Manual input mode

ItemDescription
16GridDB Plugin will auto render from Select Box Mode
17Update ALIAS BY into ‘$__container’

After that Graph as below picture

Figure34

Figure 34: Graph is displayed in manual input mode

ItemDescription
18There are 5 graphs
19Legend will be changed, name for all line graph in item 18 will be same is ‘one_thousand_rows’

It will look into real query send to server

Figure35

Figure 35: Query inspector

Item 20 Explanation
$__timeSampling(*, $__interval)

Replace with:

TIME_SAMPLING(*, TIMESTAMP('2018-11-15T02:55:01.312Z'), TIMESTAMP('2018-11-15T02:56:39.229Z'), 1000, MILLISECOND)

Note: Check appendix for more information about “$” special expression.

Create Query With Explore

Start Exploring

There are two method to open Explore:

  • Method 1:

Open new Explore icon on the menu bar to the left.

Figure36

Figure 36: Open Explore on menu bar

  • Method 2:

Open new Explore tab with the query from the panel.

Figure37

Figure 37: Open Explore from the panel

The interface of Explore Grafana

Figure38 Figure 38: The interface of Explore Grafana

Query data of Explore

Please refer to Create query to draw Graph

Other feature of Explore

  1. Create/add/delete query in explore:

There are three buttons beside the query field, a clear button (X), an add query button (+) and the remove query button (-).

Figure39 Figure 39: Create/add/delete query in explore

  1. Split and Compare:

The Split feature is an easy way to compare graphs and tables side-by-side or to look at related data together on one page. Click the split button to duplicate the current query and split the page into two side-by-side queries.

Figure40 Figure 40: Open split in explore

Allows you to compare the same query for two different servers or to compare the staging environment to the production environment.

Figure41 Figure 41: Compare two query the same

Appendix

Query special expression

The following expression can be used in query input to create dynamic query.

Name Description
Special variables: The follow syntax will be replace by a special value define by GridDB Plugin.
$__timeFrom

The start of the current time range of Dash board.

Replace with below:

TIMESTAMP({Dash board start time})

*Note: The current time range is in the top right corner of the dash board.
$__timeTo

The end of the current time range of Dash board.

Replace with below:

TIMESTAMP({Dash board end time})

*Note: The current time range is in the top right corner of the dash board.
$__col Use in Alias patterns. It will be replaced with the specified column name
$__container Use in Alias patterns. It will be replaced with the specified container name
$__interval

This variable only used in $__timeSampling macro.

Value of this variable will scale with the dash board time range.

Example:

when the dashboard show data in 1 year, $__interval = 1 day

when the dashboard show data in 1 day, $__interval = 1 minute

Lower limit of this variable is specified by $__minInterval variable.

When use in query it will be replace with Grafana time interval notion.

*Note: Grafana interval time is a special auto option that will change depending on the current time range in the top right corner of the dash board.
$__minInterval Lower limit of $__interval variable. Defined in Create Data Source screen.
Variables: The follow syntax will be replace by value of a custom variable define by user.
${variable name} Replace with current variable value.
Macro: The follow syntax will be replace by a TQL expression.
$__timeFilter

Use to limit data.

Replace with below condition:

{time column} > $__timeFrom AND {time column} < $__timeTo
$__rangeFilter

Use to limit regions annotation data.

Only use in regions annotation. Replace with below condition:

({start time column} > $__timeFrom AND {start time column} < $__timeTo) OR

({end time column} > $__timeFrom AND {end time column} < $__timeTo)
$__timeSampling({column name}, {time interval})

Replace with below expression:

TIME_SAMPLING ({column name}, $__timeFrom, $__timeTo, {time interval value}, {time interval unit})

The input {time interval} follow Grafana time interval notion:

Below show the possible usage of special expression in query input scenario.

Expression Query data Query data - alias Query annotation data
$__timeFilter - - x (*)
$__rangeFilter - - x (**)
$__timeSampling({column name}, {time interval}) x - x
$__interval x - x
$__minInterval x - x
$__timeFrom x - x
$__timeTo x - x
$__col - x -
$__container - x -

Variables are created by user:

/^${variable name}$/

 x - -

Note:
(*) Use in case query single annotation. Should always use this expression to limit data.
(**) Use in case query range annotation. Should always use this expression to limit data.