I am using Grafana with a MySQL data source and I’d like to use the Worldmap panel. Unfortunately, the documentation of the panel doesn’t cover MySQL at all. It emphasizes how to use the various other data sources but never really explains what kind of data the panel expects to receive, so I can’t figure out what query to issue.
I tried using a query that returns a two-column table, with a timestamp in the first column and a two-character country code in the second column but it doesn’t seem to work. Do I need time stamps at all? Do I need to count the number of rows on which each country appears or will the panel do it? Maybe I have to sort or group the countries somehow? Could somebody give me an example of what kind of data the panel expects?
So I should be using table instead of time series? OK, I’ll give that a try, although I think I remember seeing here somebody saying that when MySQL is used as a data source, Worldmap will work with time series but he wasn’t certain about table data.
I just tested with a MySQL query in the Time Series format and it worked fine for me.
With this table:
CREATE TABLE `worldmap` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`country_code` varchar(3) NOT NULL,
`value` varchar(45) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
and with data like this:
INSERT INTO `testdata`.`worldmap`
(`country_code`,
`value`,
`timestamp`)
VALUES
('IE',
3.0,
now());
and this query (although it is probably better to do a GROUP BY and sum here rather than using Grafana’s aggregation):
SELECT
UNIX_TIMESTAMP(`timestamp`) as time_sec,
`value` as value,
country_code as metric
FROM worldmap
WHERE $__timeFilter(`timestamp`)
ORDER BY `timestamp` ASC
Value column must have numeric datatype, column: value type: string value: 3
After I change the datatype to int ,the error disappear.
grafana:5.2.4:
grin:
I also have the same question, it looks like change “Map Data Options”-> “Location Data” to “table”, and change “Field Mapping”->“Table Query Format” to “coordinates”, then it receives latitude and longitude.
I tried, but not work…
Can you give same example on “Sql Server”, in my scenario it is not working.
SELECT
UNIX_TIMESTAMP(‘timestamp’) as time_sec,
CountryNumber as value,
ISOAlpha2Code as metric
FROM country
WHERE $__timeFilter(‘timestamp’)
ORDER BY ‘timestamp’ ASC
SELECT
UNIX_TIMESTAMP(`timestamp`) as time_sec,
`value` as value,
`lat` as latitude,
`lng` as longitude,
`name` as name
FROM worldmap_latlng
WHERE $__timeFilter(`timestamp`)
ORDER BY `timestamp` ASC
