DEV Community

Krishna Kurtakoti
Krishna Kurtakoti

Posted on

Data for World Map Panel Plugin in Grafana from MySQL

Data for World Map Panel Plugin in Grafana:

Approach 1:

Using the InfluxDB data:

  1. Here, the data is pulled from the database(InfluxDB) from a single measurement(table).

Schema of the hdb7 table is:

Time DS_ID from graphStatement latitude longitude to totalPower value

Screenshot from 2021-01-07 13-34-28

a1.png

a2.png

The above is the data that is pushed to the measurement(hdb7) of the InfluxDB database. Here, the hdb7 tableโ€™s graphStatement field values will be shown on the world map.

Query:

SELECT * from "hdb7" GROUP BY * ORDER BY DESC LIMIT 4

col1 col2
Location Data table
Aggregation current

Map Data Options

Field Mapping

col1 col2
Table Query Format coordinates
Location Name Field graphStatement
Metric Field value
Latitude Field latitude
Longitude Field longitude

Screenshot from 2021-01-05 16-43-23p1d1

Screenshot from 2021-01-05 16-44-00p1d2

Field Mapping

Screenshot from 2021-01-05 16-44-09p1d3

DrawBack:

1.

The requirement specifically mentions having 2 tables like shown below.
JOIN statements are not supported in InfluxDB.

Table1
DS_ID, Lat, Long

Table2
DS_ID, Sum(pump),sum(light),..total value

Table1:

DS_ID latitude longitude value time

Table2:

DS_ID graphStatement totalPower value time

The graphStatement covers the fields sum(boosterPump), sum(lighting), sum(lift) which are to be displayed on the map.

โ€œJOIN is no longer a concept in 0.9. Series sharing a measurement can be queried together simply by omitting the differentiating tags from the WHERE clause.โ€
Link:
https://github.com/influxdata/influxdb/issues/624

However, there is one alternative called transformations in Grafana that can be used to join 2 tables of same datasource/mixed datasource as shown below.

Here, the data for Table1 (DS_ID, Lat, Long) will be queried from MySQL database. The data for Table2 (DS_ID, Sum(pump),sum(light),..total value) will be queried from InfluxDB database.

Implementation:

  1. We can implement the above by choosing the datasource as Mixed.
    The query 1 will be:
    SELECT * from hdb10

    The query 2 will be:
    SELECT * from hdb9

    The transform we are going to apply is :
    Outer join: DS_ID

All the pictures are shown below:

Screenshot from 2021-01-08 15-07-09p2d1

Screenshot from 2021-01-08 15-07-28p2d2

When we change the metric field to Total(field from hdb10) we get the value on the map as shown below:

Screenshot from 2021-01-08 15-15-02p2d3

Approach 2:

Using the MYSQL data:

  1. Here, the data is pulled from the database(MySQL) from a 2 tables.
// hidden setup JavaScript code goes in this preamble area const hiddenVar = 42 k@k-Lenovo-G50-70:~$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | database_name | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use database_name Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show tables; +-------------------------+ | Tables_in_database_name | +-------------------------+ | a_one | | total_power | | worldmap_latlng | | worldmap_latlng_a | +-------------------------+ 4 rows in set (0.00 sec) Table creation: mysql> INSERT INTO worldmap_latlng_a -> (lat, -> lng, -> DS_ID, -> value, -> timestamp) -> VALUES -> (1.3521, -> 103.8198, -> '0', -> 1.0, -> now()); Query OK, 1 row affected (0.13 sec) mysql> CREATE TABLE worldmap_latlng_a ( -> id int(11) NOT NULL AUTO_INCREMENT, -> lat FLOAT NOT NULL, -> lng FLOAT NOT NULL, -> DS_ID VARCHAR(20) NOT NULL, -> value FLOAT NOT NULL, -> timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> PRIMARY KEY (id) -> ) AUTO_INCREMENT=7 DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.65 sec) mysql> describe total_power; +-------------+-------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+-------------------+-----------------------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | Lift | float | NO | | NULL | | | Lighting | float | NO | | NULL | | | Total | float | NO | | NULL | | | BoosterPump | float | NO | | NULL | | | DS_ID | varchar(20) | NO | | NULL | | | value | float | NO | | NULL | | | timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------------+-------------+------+-----+-------------------+-----------------------------+ 8 rows in set (0.00 sec) mysql> describe worldmap_latlng_a; +-----------+-------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+-------------------+-----------------------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | lat | float | NO | | NULL | | | lng | float | NO | | NULL | | | DS_ID | varchar(20) | NO | | NULL | | | value | float | NO | | NULL | | | timestamp | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-----------+-------------+------+-----+-------------------+-----------------------------+ 6 rows in set (0.00 sec) Table1: worldmap_latlng_a DS_ID lat lng value timestamp Table2: total_power DS_ID Lift Lighting BoosterPump Total value timestamp INSERTING Records: mysql> INSERT INTO worldmap_latlng_a -> (lat, -> lng, -> DS_ID, -> value, -> timestamp) -> VALUES -> (1.3521, -> 103.8198, -> '0', -> 1.0, -> now()); Query OK, 1 row affected (0.13 sec) mysql> INSERT INTO total_power -> (Lift, -> Lighting, -> Total, -> BoosterPump, -> DS_ID, -> value, -> timestamp) -> VALUES -> ( 10474.1997022, -> 8.97861111111, -> 14987.6236142, -> 4504.44530083, -> '0', -> 1.0, -> now()); Query OK, 1 row affected (0.11 sec)

DB Connection in Grafana(Add Data sources MySQL):
Host localhost:3306
Database database_name
Screenshot from 2021-01-05 17-35-47

SELECT CONCAT( "-") AS Conca, worldmap_latlng_a.lat, worldmap_latlng_a.lng, worldmap_latlng_a.DS_ID, total_power.Total
FROM total_power
INNER JOIN worldmap_latlng_a
ON worldmap_latlng_a.DS_ID = total_power.DS_I

Screenshot from 2021-01-05 17-09-46p1d4

Screenshot from 2021-01-05 17-11-27p1d5

The data we get is:
Conca lat lng DS_ID Total

  • 1.4 104 0 14988

Here, we are getting both the data of the table1(worldmap_latlng_a) and the table2(total_power) from the join query executed above.

Screenshot from 2021-01-05 17-11-54p1d6

Top comments (0)