Integer or String?

[ghuname]

My grafana [Grafana v6.6.2 (3fa63cfc34)] clickhouse
(ClickHouse client version 20.3.12.112., ClickHouse server version 20.3.12 revision 54433.) datasource has the following select:

SELECT crmCity
	,crmAddress
	,multiIf(resultSeverity = 'noActionRequired', 1, resultSeverity = 'highMonitoringFrequency', 100, resultSeverity = 'immediateActionRequired', 500, 1000) Severity
	,corrGroupUnpacked
	,count() modem_cnt
FROM (
	SELECT crmCity
		,crmAddress
		,resultSeverity
		,crmCmMacAddress
		,arrayJoin(corrGroup) corrGroupUnpacked
	FROM pre_eq.pre_eq
	WHERE bulkId >= toDateTime(1594112255)
		AND cmts IN ('bla')
		AND fiberNode IN ('E358')
		AND txFreq IN (55000000)
		AND bulkId = '2020-07-14 06:00:00'
		AND resultSeverity IN (
			'immediateActionRequired'
			,'highMonitoringFrequency'
			,'noActionRequired'
			)
	)
WHERE corrGroupUnpacked != 'all-modems'
GROUP BY crmCity
	,crmAddress
	,Severity
	,corrGroupUnpacked
ORDER BY modem_cnt DESC

describe [describe (select above) ] of the select looks like this:

name             |type            |default_type|default_expression|comment|codec_expression|ttl_expression|
-----------------|----------------|------------|------------------|-------|----------------|--------------|
crmCity          |Nullable(String)|            |                  |       |                |              |
crmAddress       |Nullable(String)|            |                  |       |                |              |
Severity         |UInt16          |            |                  |       |                |              |
corrGroupUnpacked|String          |            |                  |       |                |              |
modem_cnt        |UInt64          |            |                  |       |                |              |

As you can see, modem_cnt is of UInt64 type.

For presenting the select I am using official grafana table plugin. In this plugin, modem_cnt column is treated as a string instead of integer (number).

If I look in the output of the Query Inspector, one row looks like this:

0:Object
crmCity:"city_name"
crmAddress:"address line"
Severity:100
corrGroupUnpacked:"device_OM-55MHz_1"
modem_cnt:"12"

As you can see modem_cnt is "12" - string type.

What can I do in order to treat modem_cnt column as number?
I have to present modem_cnt desc, in table but numbers go as 14, 1, 2...

Who is making transformation of the UInt64 to the String?
Is it fault of the clickhouse-grafana data source, or...?

I tried to add a rule:

but modem_cnt is still sorted as sting:

What are my options?

[Slach]

@ghuname do you use Table as visualization plugin?
which format do you use for your query ? "time series" or "table"?

could you click "Query Inspector" -> "Query" -> copy to clipboard
and share result here?

[ghuname]

Yes I am using Table as visualization plugin and Format as: Table.
Here is output of the Query Inspector:

{
  "xhrStatus": "complete",
  "request": {
    "method": "GET",
    "url": "api/datasources/proxy/1/?query=select%20%20%20%20%20%20%20%20%20%20%20%20crmAddress%2C%20%20%20%20%20%20%20%20%20multiIf(resultSeverity%20%3D%20'noActionRequired'%2C%201%2C%20resultSeverity%20%3D%20'highMonitoringFrequency'%2C%20100%2C%20resultSeverity%20%3D%20'immediateActionRequired'%2C%20500%2C%201000)%20Severity%2C%20%20%20%20%20%20%20%20%20%20corrGroupUnpacked%2C%20%20%20%20%20%20%20%20%20%20count()%20modem_cnt%20from%20%20%20%20(%20%20%20%20%20%20%20%20%20select%20crmCity%2C%20crmAddress%2C%20resultSeverity%2C%20crmCmMacAddress%2C%20arrayJoin(corrGroup)%20corrGroupUnpacked%20%20%20%20%20%20%20%20%20from%20pre_eq.pre_eq%20%20%20%20%20%20%20%20%20%20WHERE%20bulkId%20%3E%3D%20toDateTime(1594556455)%20%20%20%20%20%20%20%20%20and%20cmts%20in%20('kahe1ubr1')%20%20%20%20%20%20%20%20%20and%20fiberNode%20in%20('-')%20%20%20%20%20%20%20%20%20and%20txFreq%20in%20(34000000)%20%20%20%20%20%20%20%20%20and%20bulkId%20%3D%20%20'2020-07-14%2012%3A00%3A00'%20%20%20%20%20%20%20%20%20and%20resultSeverity%20in%20%20('highMonitoringFrequency'%2C'noActionRequired')%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%20where%20%20%20corrGroupUnpacked%20!%3D%20'all-modems'%20group%20by%20%20crmAddress%2C%20Severity%2C%20corrGroupUnpacked%20order%20by%20modem_cnt%20desc%20FORMAT%20JSON"
  },
  "response": {
    "meta": [
      {
        "name": "crmAddress",
        "type": "Nullable(String)"
      },
      {
        "name": "Severity",
        "type": "UInt16"
      },
      {
        "name": "corrGroupUnpacked",
        "type": "String"
      },
      {
        "name": "modem_cnt",
        "type": "UInt64"
      }
    ],
    "data": [
      {
        "crmAddress": "Kneza Trpimira 1",
        "Severity": 1,
        "corrGroupUnpacked": "non-correlated",
        "modem_cnt": "10"
      },
      {
        "crmAddress": "Radoslava Lopašića 4",
        "Severity": 1,
        "corrGroupUnpacked": "non-correlated",
        "modem_cnt": "1"
      }
    ],
    "rows": 2,
    "statistics": {
      "elapsed": 0.018627837,
      "rows_read": 374335,
      "bytes_read": 10783555
    }
  }
}

[Slach]

@ghuname did you upgrade your vertamedia-clickhouse-grafana to 2.x version?

[Slach]

@ghuname issue still actual for you after an upgrade to 2.x?
could you share grafana dashboard JSON and clickhouse table schema?

[ghuname]

Yes I have upgraded to 2.0.1, and the issue is still present.

[wordsappadmin]

I fixed same issue by converting count() to UInt32.
In your case:

toUInt32(count()) modem_cnt

Seems like Grafana v6.6.2 or Clickhouse plugin can't understand type UInt64.

[Slach]

@ghuname
look like every *Int64 field type converted by clickhouse server to string when used FORMAT JSON clause, cause JSON

it's required for compatible to JavaScript
please look details here ClickHouse/ClickHouse#114

I tried to reproduce UInt64 + table plugin grafana dashboard with clickhouse-grafana 2.0.2 version
and as I see, table plugin in grafana 6.6.2 works fine with "string" represented as "UInt64"

I just add only one thing for that

so just use /modern_cnt/ instead of modern_cnt in table Visualization