Format data as table

[Perlovka]

HI,

WorldMap plugin for grafana allows to map geohash data formatted as a table (using InfluxDb datasource)
https://github.com/grafana/worldmap-panel#table-data-as-the-data-source

(grafana/worldmap-panel#109)

It would be nice to have this feature, if possible.

Thanks.

[hagen1778]

Hi @Perlovka
Actually, I have no experience with geohash and InfluxDB.
If CH-datasource plugin can be used with Table plugins, why worldmap-panel can't use same formatiing?
One of our panels also using worldmap-panel but with country codes, not geohashes. Example of query:

SELECT 
    1, 
    groupArray((c, Reqs)) AS groupArr
FROM 
(
 SELECT 
    CountryCode AS c, 
    sum(requests) AS Reqs
FROM requests 
GLOBAL ANY INNER JOIN 
(
    SELECT Country country, CountryCode
    FROM countries 
) USING (country)
WHERE $timeFilter
GROUP BY 
    c
ORDER BY Reqs DESC
)

[Perlovka]

Hi,
We do have country and city maps also, and yes, it works as expected.

But for geohash data there are only two options:

• Geohashes (https://github.com/grafana/worldmap-panel#geohashes-as-the-data-source):
  Data should be in ElasticSearch format.
  I suppose, something like this:
  {"_shards": {"failed": 0, "successful": 5, "total": 5}, "aggregations": {"accidents_geohash_grid": {"buckets": [{"doc_count": 4372, "key": "9q8yysu"}, {"doc_count": 1546, "key": "9q8yy96"}]}}, "hits": {"hits": [], "max_score": 0.0, "total": 137100}, "timed_out": false, "took": 449}

Found this from http://elasticsearch.demos.tryolabs.com/
As I understand, clickhouse can not build associative arrays in response.

• Table:
  I could not understand what exactly format is used, it does not shows up in Grafana QueryInspector for InfluxDB.
  I'll try to set up InfluxDB with geohashes to test.

Here is test data for CH:
CREATE TABLE stats.geo ( event_date Date, event_time DateTime, country_code FixedString(2), region String, city String, lat Float64, lon Float64, geohash String) ENGINE = ReplicatedMergeTree(\'/clickhouse/tables/01/geo\', \'clickhouse-ue1-01.ue1.lzn\', event_date, (event_date, geohash), 8192)

"2017-11-02","2017-11-02 17:59:36","CL","BI","Concepción",-36.8333,-73.05,"63kn3www" "2017-11-02","2017-11-02 15:32:32","CL","RM","Santiago",-33.462500000000006,-70.6682,"66j9xukf" "2017-11-02","2017-11-02 17:07:01","AR","M","Lujan de Cuyo",-33.066700000000004,-68.8333,"66p5csmh" "2017-11-02","2017-11-02 14:24:39","AR","U","Boca de la Zanja",-43.4185,-66.01110000000001,"6830czuh" "2017-11-02","2017-11-02 20:10:16","AR","B","Villa Bosch",-34.5708,-58.624300000000005,"69y73fx4" "2017-11-02","2017-11-02 17:08:37","AR","","",-34.603300000000004,-58.381699999999995,"69y7pkxt" "2017-11-02","2017-11-02 15:39:31","AR","C","Buenos Aires",-34.603300000000004,-58.38159999999999,"69y7pkxv" "2017-11-02","2017-11-02 16:29:28","PY","ASU","Asunción",-25.2939,-57.6111,"6ex0152u" "2017-11-02","2017-11-02 17:26:50","PY","ASU","Asunción",-25.2939,-57.6111,"6ex0152u"

[hagen1778]

Hi! Check https://github.com/Vertamedia/clickhouse-grafana/releases/tag/1.1.0
This is how it can be used:

[Perlovka]

Awesome! Thanks =)