search

grafana-toolbox / panodata-map-panel

Map Panel for Grafana with improved convenience, robustness and features. Friendly fork of the original Grafana Worldmap Panel. Currently not maintained, but verified to work up to Grafana 9.
https://community.panodata.org/t/grafana-map-panel/121
GNU Affero General Public License v3.0
88 stars 31 forks source link

Problem with TimescaleDB and Grafana 8: DataError: Format mismatch: table data can not be interpreted as timeseries format #123

Open dolejska-daniel opened 3 years ago

dolejska-daniel commented 3 years ago

Hello, I'm having issues with using data format and mapping option Match metric name against key value of JSON record under Time series, resolve location from custom JSON. Until now I have been using grafana/worldmap-panel but after updating to the latest version I decided to use this plugin instead. However, I am unable to get it working correctly.

I am ending up with error message DataError: Format mismatch: table data can not be interpreted as timeseries format even though the Format as of the query is set to Time series in the Grafana edit panel view.

Partial example of query result: time geohash value
2021-05-31 09:04:09.374157 +00:00 France 1490
2021-05-31 09:04:10.507377 +00:00 United States 5421
2021-05-31 09:04:16.864141 +00:00 Spain 286

Thank you for your time, Daniel

amotl commented 3 years ago

Hi Daniel,

thanks for stepping by and thank you for testing this plugin with the most recent version of Grafana. First, let me begin by saying that I personally didn't have the chance to test it thoroughly with Grafana 8 yet. Further, we are only using InfluxDB on our systems, so solid support for other data sources is regularly validated by people from the community and we will be very happy to both have you on board on this matter and support you as good as we can.

So, may I humbly ask which data source (database) you are using?

Please note that there also have been problems with the vanilla Worldmap Panel and Grafana 8, where @hugohaggmark first chimed in with https://github.com/grafana/worldmap-panel/pull/318 and, after that, @mckn and @ryantxu contributed https://github.com/grafana/grafana/pull/36737 to Grafana core.

According to what I grok from this, some data sources might not yield data in "wide data frame format" yet and have to be supported by adding a respective "prepare time series" transformer. I haven't used those features of Grafana yet, so I want to apologize that I can't provide a detailed guideline how to achieve that correctly.

We will be happy to hear back from you if you can get it working based on this information by adding a "prepare time series" transformer in order to make your data source plugin yield its data in an appropriate format. The outcome from this will be very useful to improve the documentation in order to benefit the community which uses this plugin.

With kind regards, Andreas.

dolejska-daniel commented 3 years ago

Thank you for your swift and kind reply! I am using TimescaleDB as my data source.

As for the Prepare time series transformation:

I figured that the value prefix in the column names could potentially be a problem so I have applied one more transformation Rename by regex. Using Match value\s(.*) and Replace $1 successfully renamed all the columns in question. Even so this made no difference and no entries are displayed.

Data received: (19) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}]
0:
  alias: undefined
  datapoints: Array(19)
    0: (2) [null, 1626003238946]
    1: (2) [null, 1626003240157]
    2: (2) [null, 1626003243021]
    3: (2) [null, 1626003244380]
    4: (2) [null, 1626003246991]
    5: (2) [null, 1626003252549]
    6: (2) [null, 1626003252567]
    7: (2) [null, 1626003253978]
    8: (2) [null, 1626003266240]
    9: (2) [null, 1626003274131]
    10: (2) [null, 1626003311193]
    11: (2) [null, 1626003328905]
    12: (2) [null, 1626003364274]
    13: (2) [43, 1626003435156]
    14: (2) [null, 1626003607404]
    15: (2) [null, 1626003611786]
    16: (2) [null, 1626003612979]
    17: (2) [null, 1628594532389]
    18: (2) [null, 1628595214951]
    lastIndex: (...)
    lastItem: (...)
    length: 19
    [[Prototype]]: Array(0)
  meta: {executedQueryString: "SELECT MIN(ps.created_at) AS time,\r\n       product…'\r\nGROUP BY 
  product_origins.value\r\nORDER BY time;", transformations: Array(2587)}
  refId: "A"
  target: "Australia"
  unit: undefined
1: {alias: undefined, target: "Belgium", datapoints: Array(19), unit: undefined, refId: "A", …}
2: {alias: undefined, target: "Canada", datapoints: Array(19), unit: undefined, refId: "A", …}
...
amotl commented 3 years ago

Hi Daniel,

that looks sweet already:

worldmap_ctrl.ts:291 Data received: (18) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}]
worldmap_ctrl.ts:301 Processing data
worldmap_ctrl.ts:344 Received 18 records in timeseries format
core.ts:152 Interpreting data as timeseries format
worldmap_ctrl.ts:304 Updating color mode
worldmap.ts:188 Data points 0. Circles on map 0.
worldmap.ts:231 Updating circles

Yet, 0 points are drawn. While this output looks good to me already, there is still a chance for another less obvious data interpretation flaw, where we would need to keep digging.

Data received: (19) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}]
0:
  alias: undefined
  datapoints: Array(19)
    0: (2) [null, 1626003238946]
    1: (2) [null, 1626003240157]
    2: (2) [null, 1626003243021]
    3: (2) [null, 1626003244380]
[...]

Ah, the value of the datum is missing here, right? On this output, I can only spot the timestamp.

With kind regards, Andreas.

dolejska-daniel commented 3 years ago

Yet, 0 points are drawn. That might be a timing problem. Can you save your dashboard in this (apparently working) state and try to navigate from/to it for a few times or try it in a different browser? I know such a behavior should not exist, but IIRC, I occasionally experienced such behavior.

You are right indeed - it started working after saving everything and reloading a few times. I am sorry for wasting your time like this. 😅 Thank you for your help! 💖

Ah, the value of the datum is missing here, right? On this output, I can only spot the timestamp.

Also yes - some of the values are missing since not all of the records have values defined at the same timestamps. However, this does not seem to be source of any issues.

amotl commented 3 years ago

Hi Daniel,

It started working after saving everything.

Great to hear that you have been able to make it working. May I ask which steps you exactly had to take in order to make it work?

a) Did you have to apply the Prepare time series transformation using Wide time series or Multi-frame time series? b) Did you have to apply the Rename by regex transformation?

[I had to] save everything and reload a few times.

Hm, yeah, that's what I suggested. Thanks for letting us know. However, please report back to us if you will continuously receive such behavior. If this keeps happening, we will have to look into how to improve the situation.

With kind regards, Andreas.

P.S.: I will reopen this issue until we have this stuff documented. If you can add respective screenshots here, that would help tremendously. Thanks already!

dolejska-daniel commented 3 years ago

Okay, so this is what is necessary in order to make the map work.

Hopefully, this will help someone having similar issues! ✌🏽

amotl commented 3 years ago

Dear Daniel,

thank you for the excellent written guideline which reads like to be easy to follow through. I will still keep this issue open as a note until I can integrate this waltkthrough into the documentation in one way or another.

With kind regards, Andreas.