Data for World Map Panel Plugin in Grafana from MySQL
Tags: mysql, grafana, sql, visualization
Published: 2024-05-28 10:09:07

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](https://dev-to-uploads.s3.amazonaws.com/i/gr9jj8p1i1u2rrpkw8gt.png)
 


![a1.png](https://res.cloudinary.com/dskzmamid/image/upload/v1610006956/a1.png)

![a2.png](https://res.cloudinary.com/dskzmamid/image/upload/v1610006954/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](https://dev-to-uploads.s3.amazonaws.com/i/byqhoevj4c9j6m0xn3tj.png)

![Screenshot from 2021-01-05 16-44-00p1d2](https://dev-to-uploads.s3.amazonaws.com/i/raq6rumu907qwas7fqav.png)
 
 Field Mapping

![Screenshot from 2021-01-05 16-44-09p1d3](https://dev-to-uploads.s3.amazonaws.com/i/bvrwz3ali445j0zzny12.png)
 
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](https://dev-to-uploads.s3.amazonaws.com/i/8b5h21f0fsb7t2a0lozt.png)
 
![Screenshot from 2021-01-08 15-07-28p2d2](https://dev-to-uploads.s3.amazonaws.com/i/4loxdrpw5tezt2ulklv6.png)

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](https://dev-to-uploads.s3.amazonaws.com/i/gznqj96ntgiy2h6qm95t.png)
 
 
**Approach 2:**

Using the MYSQL data:

1. Here, the data is pulled from the database(MySQL) from a 2 tables.


{% runkit
// 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)
{% endrunkit %}

DB Connection in Grafana(Add Data sources MySQL):
Host localhost:3306
Database database_name
![Screenshot from 2021-01-05 17-35-47](https://dev-to-uploads.s3.amazonaws.com/i/y3v3zwnfrd5mp693xx5c.png)
 
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](https://dev-to-uploads.s3.amazonaws.com/i/gwufg47ntytsla1ty47k.png)

![Screenshot from 2021-01-05 17-11-27p1d5](https://dev-to-uploads.s3.amazonaws.com/i/99jqlbnldnmy3g1f4o06.png)

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](https://dev-to-uploads.s3.amazonaws.com/i/m3svffq75puzc2wh2g9d.png)