Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Format data as table #17

Closed
Perlovka opened this issue Nov 5, 2017 · 4 comments

Comments

Projects
None yet
2 participants
@Perlovka
Copy link

commented Nov 5, 2017

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 hagen1778 self-assigned this Nov 5, 2017

@hagen1778

This comment has been minimized.

Copy link
Collaborator

commented Nov 7, 2017

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

This comment has been minimized.

Copy link
Author

commented Nov 9, 2017

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

This comment has been minimized.

Copy link
Collaborator

commented Nov 13, 2017

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

@hagen1778 hagen1778 closed this Nov 13, 2017

@Perlovka

This comment has been minimized.

Copy link
Author

commented Nov 14, 2017

Awesome! Thanks =)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.