Unexpected behaviour with clickhouse datasource (v2.3.1) and single stat panel

[toni-moreno]

Hello Guys. I'm trying to create a Grafana 7.5.5 Dashboard with single stat panel like in others dashboarsd we have already working fine, but with unexpected behaviour with clickhouse datasource.

Data visualization with flux/influxdb

With other datasources (flux/influxdb) we have created a singlestat that we can feed with a table like that (from chronograf) with 2 entries.

with this singlestat result

or this other with 1 entry

And this result.

Data visualization with clickhouse

when result for the query is that ( assuming that we should add a fake time column)

┌─t─┬─cat─┬─errors─┐
│ 1 │   3 │     24 │
│ 1 │   4 │     84 │
└───┴─────┴────────┘

visualization is ok

when result for the query is this other.

┌─t─┬─cat─┬─errors─┐
│ 1 │   4 │     84 │
└───┴─────┴────────┘

the category column information /(tag in influxdb) disappeared.

There is any way to fix this behaviour difference with other datasources?

[Slach]

Could you try a different approach?

┌─t─┬─cat─┬─errors─┐
│ 1 │   Category 4 │     84 │
└───┴─────┴────────┘

I mean select cat field as string which not contain only numbers

[toni-moreno]

Hello @Slach I've replaced my original .

SELECT
    1 AS t, /* fake timestamp value */
    cat,
    count(idx) as errors
FROM
(
....( another big subselect) 
)
WHERE 
GROUP BY t,cat

by

SELECT
    1 AS t, /* fake timestamp value */
    concat('Category ',toString(cat)) as cat,
    count(idx) as errors
FROM
(
....( another big subselect) 
)
WHERE 
GROUP BY t,cat

now Grafana is getting strings en cat column

┌─t─┬─cat────────┬─errors─┐
│ 1 │ Category 3 │     24 │
│ 1 │ Category 4 │     84 │
└───┴────────────┴────────┘

┌─t─┬─cat────────┬─errors─┐
│ 1 │ Category 3 │     24 │
└───┴────────────┴────────┘

And still not working when only one row.

[Slach]

Could you try to replace your clickhouse query format from time series to table? (look drop-down menu on bottom)

or try to replace your query and apply $columns macro

$columns(
    concat('Category ',toString(cat)) as cat,
    count(idx) as errors
) FROM
(
....( another big subselect) 
)
GROUP BY t,cat

[toni-moreno]

Hello again @Slach . I've tested both solutions

1) With timeseries format

Option A

I've tested 2 options, and neither of them are valid because main information "Category" has disappeared

SELECT
    1 AS t, /* fake timestamp value */
    concat('Category ',toString(cat)) as cat,
    count(idx) as errors
FROM
(
    ....( another big subselect) 
)
GROUP BY t,cat

this table

┌─t─┬─cat────────┬─errors─┐
│ 1 │ Category 3 │     24 │
│ 1 │ Category 4 │     84 │
└───┴────────────┴────────┘

will become in

Option B ( without t)

SELECT
    concat('Category ',toString(cat)) as cat,
    count(idx) as errors
FROM
(
    ....( another big subselect) 
)
GROUP BY cat

this table

┌─cat────────┬─errors─┐
│ Category 3 │     24 │
│ Category 4 │     84 │
└────────────┴────────┘

will become in

2.- with $columns macro

this query

$columns(
    concat('Category ',toString(cat)) as cat,
    count(idx) as errors
) FROM
(
....( another big subselect) 
)
GROUP BY t,cat

will transform to this other.

SELECT t, groupArray((cat, errors)) AS groupArr FROM ( SELECT (intDiv(toUInt32(time), 2) * 2) * 1000 AS t, concat('Category ', toString(cat)) as cat, count(idx) as errors FROM
(
....( another big subselect) 
)
GROUP BY t,cat GROUP BY t, cat ORDER BY t, cat) GROUP BY t ORDER BY t

I'm not sure what is supposed to do this macro but it is failing with this error

Code: 62. DB::Exception: Syntax error: failed at position 685 ('GROUP') (line 20, col 16): GROUP BY t, cat ORDER BY t, cat) GROUP BY t ORDER BY t FORMAT JSON . Expected one of: token, Comma, Arrow, Dot, UUID, DoubleColon, MOD, DIV, NOT, BETWEEN, LIKE, ILIKE, NOT LIKE, NOT ILIKE, IN, NOT IN, GLOBAL IN, GLOBAL NOT IN, IS, AND, OR, QuestionMark, alias, AS, WITH, HAVING, WINDOW, ORDER BY, LIMIT, OFFSET, SETTINGS, UNION, EXCEPT, INTERSECT. (SYNTAX_ERROR) (version 21.12.3.32 (official build))

any other suggestion ?

[Slach]

Let's try remove intDIv and double group by something like that

SELECT t, groupArray((cat, errors)) AS groupArr FROM ( 

  SELECT 
     1 AS t, 
     concat('Category ', toString(cat)) as cat, 
     count(idx) as errors 
  FROM (

....( another big subselect) 

  )
  GROUP BY t, cat ORDER BY t, cat
) 
GROUP BY t 

Did you try to replace your clickhouse query format from time series to table? (look drop-down menu on bottom)

[toni-moreno]

Yes @Slach while this query.

 SELECT 
     1 AS t, 
     concat('Category ', toString(cat)) as cat, 
     count(idx) as errors 
  FROM (

....( another big subselect) 

  )
  GROUP BY t, cat ORDER BY t, cat

I'm getting this table.

┌─t─┬─cat────────┬─error─┐
│ 1 │ Category 3 │    24 │
│ 1 │ Category 4 │    84 │
└───┴────────────┴───────┘

with this result ( Formatted as Table)

When adding the groupArray extension

SELECT t, groupArray((cat, error)) AS groupArr FROM ( 

  SELECT 
     1 AS t, 
     concat('Category ', toString(cat)) as cat, 
     count(idx) as error
FROM
(
....( another big subselect) 

  )
  GROUP BY t, cat ORDER BY t, cat
) 
GROUP BY t 

I got this table

┌─t─┬─groupArr──────────────────────────────┐
│ 1 │ [('Category 3',24),('Category 4',84)] │
└───┴───────────────────────────────────────┘

And This as single stat.

Configuring Numeric Fields

Configuring All fields.

And configuring only GroupArr field

Expected

All previous queries and configurations never got expected result.

Maybe we won't be able to configure single stats as expected if no way to get data as dataframes , see related issue (https://github.com/Vertamedia/clickhouse-grafana/issues/404)

[Slach]

I ask third time!!! Did you try to replace your clickhouse query format from time series to table? (look drop-down menu on bottom)?

[toni-moreno]

Sorry @Slach , yes I did In all examples. See above again

Here another evidence.

[Slach]

ok ;) clear, thanks a lot for reporting, i will try to figure wrong behavior ASAP

[toni-moreno]

Thank you very much for your time @Slach .

As I said in my first entry in this issue, we have currently working with single stats panel, and I think all this behaviour could be fixed by only getting data in grafana dataframe format ( needed for Grafana 7.0+ ). Thank you very much again for your work.

[Slach]

@lunaticusgreen need to figure out how works result set with category with single stat visualization in Grafana 10

[lunaticusgreen]

@Slach I checked the behavior in Grafana 10, please review and close issue if needed:

If you set Text Mode to "Value and name" it shows correctly for a single stat panel with click house data source:

With Text Mode "Auto" which is the default, the name is indeed missing:

[Slach]

@toni-moreno sorry for late reply is issue still relevant for you?

[Slach]

@lunaticusgreen let's add ./docker/grafana/dashboards/singlestat.json which will reproduce (or not) behavior in https://github.com/Altinity/clickhouse-grafana/issues/403#issuecomment-1008998631