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

[dolejska-daniel]

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.

• JSON URL is set correctly and its content format matches with countries data from examples
• Received data as logged by the plugin: https://pastebin.com/raw/FwNNJDUb
• Using latest Grafana version v8.1.1 (90c87a52f7)
• Using latest plugin version 0.16.0

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]

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 grafana#318 and, after that, @mckn and @ryantxu contributed grafana/grafana#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]

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

As for the Prepare time series transformation:

• when using format Wide time series no change in behaviour is apparent and the same error DataError: Format mismatch: table data can not be interpreted as timeseries format is shown
• when using Multi-frame time series format no error is displayed or shown anywhere; however, no points are displayed either. It seems that the graph receives the data in the correct format yet is still unable to display them properly for some reason. Chrome JavaScript console output:

Refreshing panel. initializing= false
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

Format of the data as shown in Grafana's query inspector:

Time	value Lithuania	value Spain	value United States
2021-07-11 13:33:58	0
2021-07-11 13:34:00		60
2021-07-11 13:34:03			635

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]

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]

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]

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]

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

• Use Data format and mapping option: Time series, resolve location from custom JSON » Match metric name against key value of JSON record
• Data containing time, geohash and value columns
• Query Format as set to Time series, otherwise the Prepare time series transformation does not help
• Correctly configured, formatted and accessible JSON file with the corresponding locations like this one
• Results sorted by time in ascending order, otherwise "failed to convert long to wide series when converting from dataframe" error is shown
• Prepare time series transformation with Format set to Multi-frame time series, otherwise the "DataError: Format mismatch: table data can not be interpreted as timeseries format" error is shown
• Rename by regex transformation after the Prepare time series transformation with Match set to value (.*) and Replace to $1, otherwise no locations from JSON file are matched and displayed because they are prefixed with value
• Save the dashboard and reload

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

[amotl]

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.