for Table pluggin, fields that are not Integer are typed as string

[Stephane-Thales]

Hi all,

Only for the Table plugin (so far it work with the Stat object)
With a query format as Table (not Time series)

If a clickhouse Field is Nullable(Float64), Float64, Decimal ... it's typed as String.
So the plugin can't be configured to apply numeric related formatting (%, Threshold ...)

To check, compare types between

• Query inspector / Query / Object / Response / Meta / Object / type
• Query inspector / JSON / Dataframe structure / Array[1] / 0:Object / fields: / 0:Object

Workaround is to apply in the query a
toInt16(ifNull( MyField ,0)) as MyField
But that prevent to display decimals.

Is that a Table plugin issue or a clickhouse-grafana plugin issue ?
Can that be fixed in clickhouse-grafana plugin ?

thanks

[Slach]

Dear @Stephane-Thales, could you help me reproduce issue?

I tried to reproduce with following steps:

git checkout https://github.com/Vertamedia/clickhouse-grafana
cd clickhouse-grafana
git checkout master
docker-compose up -d grafana
docker-compose exec clickhouse clickhouse-client -mn

run following queries inside clickhouse-client

DROP TABLE IF EXISTS default.test_grafana;
CREATE TABLE IF NOT EXISTS default.test_grafana(event_time DateTime, service_name LowCardinality(String), too_big_value Float64)
ENGINE=MergeTree()
    PARTITION BY toYYYYMM(event_time)
    ORDER BY (event_time, service_name);

INSERT INTO default.test_grafana(event_time, service_name, too_big_value) SELECT toDateTime(now()-(number*10)) AS event_time, if(rand() % 2 = 1,'mysql','postgresql') AS service_name, 1000000000.05 AS too_big_value FROM numbers(1000);
INSERT INTO default.test_grafana(event_time, service_name, too_big_value) SELECT toDateTime(now()+(number*10)) AS event_time, 'mysql' AS service_name, 1000000000.05 AS too_big_value FROM numbers(1000);
INSERT INTO default.test_grafana(event_time, service_name, too_big_value) SELECT toDateTime(now()+((500+number)*10)) AS event_time, 'postgresql' AS service_name, 1000000000.05 AS too_big_value FROM numbers(1000);
\q;

open grafana at http://localhost:3000/dashboard/new and add the following query:

SELECT sum(too_big_value) AS v, service_name FROM $table WHERE $timeFilter
GROUP BY service_name
ORDER BY v DESC

and got the following results:

[Stephane-Thales]

Hi @Slach ,

for reference, i'm using

Grafana v7.0.3 (00ee734baf)
clickhouse server 20.4.3.16
pluggin	ClickHouse datasource for Grafana 4.6+   1.9.5

to reproduce my issue I've used the same table than you :

DROP TABLE IF EXISTS default.test_grafana;
CREATE TABLE IF NOT EXISTS default.test_grafana(event_time DateTime, service_name LowCardinality(String), too_big_value Float64)
ENGINE=MergeTree()
    PARTITION BY toYYYYMM(event_time)
    ORDER BY (event_time, service_name);

INSERT INTO default.test_grafana(event_time, service_name, too_big_value) SELECT toDateTime(now()-(number*10)) AS event_time, if(rand() % 2 = 1,'mysql','postgresql') AS service_name, 1000000000.05 AS too_big_value FROM numbers(1000);
INSERT INTO default.test_grafana(event_time, service_name, too_big_value) SELECT toDateTime(now()+(number*10)) AS event_time, 'mysql' AS service_name, 1000000000.05 AS too_big_value FROM numbers(1000);
INSERT INTO default.test_grafana(event_time, service_name, too_big_value) SELECT toDateTime(now()+((500+number)*10)) AS event_time, 'postgresql' AS service_name, 1000000000.05 AS too_big_value FROM numbers(1000);

select count(*) from default.test_grafana;
-- 3000

select * from default.test_grafana order by event_time, service_name ;
/*
event_time			service_name	too_big_value
2020-06-26 06:10:41	postgresql		1000000000.05
2020-06-26 06:10:51	postgresql		1000000000.05
2020-06-26 06:11:01	mysql			1000000000.05
2020-06-26 06:11:11	mysql			1000000000.05 
 */

then New dashboard, new pannel
first query like yours

SELECT sum(too_big_value) AS v, service_name FROM $table WHERE $timeFilter
GROUP BY service_name
ORDER BY v DESC

in clickhouse 👍

SELECT sum(too_big_value) AS v, service_name FROM default.test_grafana WHERE event_time >= toDateTime(1593111854) GROUP BY service_name ORDER BY v DESC;
/*
1522000000076.1428	mysql
1478000000073.9407	postgresql
 */	

Note the change of option "Cell display mode" to "Color text" for visualisation of the text vs number.

The data is well provided as float by clickhouse, with the decimals

But Grafana think it's string

If I change the query to that :

SELECT toInt64(sum(too_big_value)) AS v, service_name FROM $table WHERE $timeFilter
GROUP BY service_name
ORDER BY v DESC

Then the fiel is recognized as a number (color coding is applied):

Thanks.

[Slach]

I catch it

please use latest master branch or wait when 2.0.2 will be released on grafana-plugin-repository

[mzealey]

I have pulled the latest master but this is not fixed for me

SELECT
    splitByChar('.', Path)[3] Server,
    splitByChar('.', Path)[5] User,
    avg(Average) Average,
    max(Maximum) Maximum
FROM cloudlinux_faults
WHERE (Date BETWEEN toDate($from) AND toDate($to))
GROUP BY Path
ORDER BY Average DESC
LIMIT $total

still thinks these Float64-based cols are strings.

[Slach]

@mzealey could you share SHOW CREATE TABLE cloudlinux_faults?
did you try to redefine decimals character in Average or Maximum columns?
Which Grafana version do you use?

[mzealey]

CREATE TABLE IF NOT EXISTS cloudlinux_faults (
  Date Date,
  Path String,
  Average Float64,
  Maximum Float64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(Date)
ORDER BY (Date, Path);

[mzealey]

latest grafana docker image so 7-series

[Slach]

did you try to redefine decimals in "Field" tab for in Average and Maximum columns?

[mzealey]

I am not sure what you mean by this?

[Slach]

I mean Decimals on "Fields" tab on right side in panel edit

[mzealey]

Ah yes then they work as numbers. Is there no way to do this automatically in the plugin ?

[Slach]

look like it's a not a plugin scope
please look at 94ebf76#diff-dd6ae047951d2bef1b8eec9a65765b4eR175
plugin already convert ClickHouse numeric related types to JavaScript "number"
after that visualization plugins continue to convert it to visual representations