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
Here, we are getting both the data of the table1(worldmap_latlng_a) and the table2(total_power) from the join query executed above.
