Data for World Map Panel Plugin in Grafana:
Approach 1:
Using the InfluxDB data:
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
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
Field Mapping
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:
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:
When we change the metric field to Total(field from hdb10) we get the value on the map as shown below:
Approach 2:
Using the MYSQL data:
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
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
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.
