clickhouse-grafana
clickhouse-grafana copied to clipboard
Published
20 hours ago •
Altinity
Altinity
Map is not expanded on timeseries format - [object Object]
Hi,
We have a CH schema with a field defined as Map(String,String). When we try to perform a simple query from Grafana on the TimeSeries panel and as TimeSeries format, the Map value is not expanded and not included as part of the serie. It is working as "Table format" mode.
The idea is to be able to retrieve the value as a dict (or map[string]string) and use the Extract Field Grafana transformation to generate the desired columns, but the generated dataframe seems to concat the string values with an object Object result, being unable to use it.
The following snippet is the "Support" feature that extracts the result Dataframe processed by the backend, with no transformations. There you can check that the generated dataframe is built with Name - Value - Time, with the non-expanded map is set as [object Object] on the dataframes name
| Key | Value |
|---|---|
| Panel | timeseries @ 10.0.3 (eb8dd72637) |
| Grafana | 10.0.3 (eb8dd72637) // Open Source |
Panel debug snapshot dashboard
{
"panels": [
{
"datasource": {
"type": "grafana",
"uid": "grafana"
},
"fieldConfig": {
"defaults": {
"custom": {
"drawStyle": "line",
"lineInterpolation": "linear",
"barAlignment": 0,
"lineWidth": 1,
"fillOpacity": 0,
"gradientMode": "none",
"spanNulls": false,
"showPoints": "auto",
"pointSize": 5,
"stacking": {
"mode": "none",
"group": "A"
},
"axisPlacement": "auto",
"axisLabel": "",
"axisColorMode": "text",
"scaleDistribution": {
"type": "linear"
},
"axisCenteredZero": false,
"hideFrom": {
"tooltip": false,
"viz": false,
"legend": false
},
"thresholdsStyle": {
"mode": "off"
}
},
"color": {
"mode": "palette-classic"
},
"mappings": [],
"thresholds": {
"mode": "absolute",
"steps": [
{
"color": "green",
"value": null
},
{
"color": "red",
"value": 80
}
]
}
},
"overrides": []
},
"gridPos": {
"h": 13,
"w": 15,
"x": 0,
"y": 0
},
"id": 2,
"options": {
"tooltip": {
"mode": "single",
"sort": "none"
},
"legend": {
"showLegend": true,
"displayMode": "list",
"placement": "bottom",
"calcs": []
}
},
"targets": [
{
"refId": "A",
"datasource": {
"type": "grafana",
"uid": "grafana"
},
"queryType": "snapshot",
"snapshot": [
{
"schema": {
"name": "id1, [object Object]",
"fields": [
{
"name": "Time",
"type": "time",
"config": {}
},
{
"name": "Value",
"type": "number",
"config": {}
}
]
},
"data": {
"values": [
[
1694517223000,
1694517237000
],
[
1,
null
]
]
}
},
{
"schema": {
"name": "id2, [object Object]",
"fields": [
{
"name": "Time",
"type": "time",
"config": {}
},
{
"name": "Value",
"type": "number",
"config": {}
}
]
},
"data": {
"values": [
[
1694517223000,
1694517237000
],
[
null,
1
]
]
}
},
{
"schema": {
"name": "id3, [object Object]",
"fields": [
{
"name": "Time",
"type": "time",
"config": {}
},
{
"name": "Value",
"type": "number",
"config": {}
}
]
},
"data": {
"values": [
[
1694517223000,
1694517237000,
1694517250000
],
[
null,
null,
1
]
]
}
}
]
}
],
"title": "Reproduced with embedded data",
"transformations": [],
"type": "timeseries"
},
{
"gridPos": {
"h": 7,
"w": 9,
"x": 15,
"y": 0
},
"id": 5,
"options": {
"content": "<table width=\"100%\">\n <tr>\n <th width=\"2%\">Panel</th>\n <td >timeseries @ 10.0.3 (eb8dd72637)</td>\n </tr>\n <tr>\n <th>Queries</th>\n <td>A[vertamedia-clickhouse-datasource]</td>\n </tr>\n <tr>\n <th>Transform</th>\n <td></td>\n </tr>\n <tr><th>Data</th><td> 3 frames, 6 fields, 7 rows </td></tr>\n \n <tr>\n <th>Grafana</th>\n <td>10.0.3 (eb8dd72637) // Open Source</td>\n </tr>\n </table>",
"mode": "html"
},
"title": "Debug info",
"type": "text"
},
{
"id": 6,
"title": "Original Panel JSON",
"type": "text",
"gridPos": {
"h": 13,
"w": 9,
"x": 15,
"y": 7
},
"options": {
"content": "{\n \"datasource\": {\n \"type\": \"vertamedia-clickhouse-datasource\",\n \"uid\": \"caf89ca7-12da-4a25-9974-5fc93dc4e57a\"\n },\n \"fieldConfig\": {\n \"defaults\": {\n \"custom\": {\n \"drawStyle\": \"line\",\n \"lineInterpolation\": \"linear\",\n \"barAlignment\": 0,\n \"lineWidth\": 1,\n \"fillOpacity\": 0,\n \"gradientMode\": \"none\",\n \"spanNulls\": false,\n \"showPoints\": \"auto\",\n \"pointSize\": 5,\n \"stacking\": {\n \"mode\": \"none\",\n \"group\": \"A\"\n },\n \"axisPlacement\": \"auto\",\n \"axisLabel\": \"\",\n \"axisColorMode\": \"text\",\n \"scaleDistribution\": {\n \"type\": \"linear\"\n },\n \"axisCenteredZero\": false,\n \"hideFrom\": {\n \"tooltip\": false,\n \"viz\": false,\n \"legend\": false\n },\n \"thresholdsStyle\": {\n \"mode\": \"off\"\n }\n },\n \"color\": {\n \"mode\": \"palette-classic\"\n },\n \"mappings\": [],\n \"thresholds\": {\n \"mode\": \"absolute\",\n \"steps\": [\n {\n \"color\": \"green\",\n \"value\": null\n },\n {\n \"color\": \"red\",\n \"value\": 80\n }\n ]\n }\n },\n \"overrides\": []\n },\n \"gridPos\": {\n \"h\": 8,\n \"w\": 12,\n \"x\": 0,\n \"y\": 0\n },\n \"id\": 1,\n \"options\": {\n \"tooltip\": {\n \"mode\": \"single\",\n \"sort\": \"none\"\n },\n \"legend\": {\n \"showLegend\": true,\n \"displayMode\": \"list\",\n \"placement\": \"bottom\",\n \"calcs\": []\n }\n },\n \"targets\": [\n {\n \"database\": \"test\",\n \"datasource\": {\n \"type\": \"vertamedia-clickhouse-datasource\",\n \"uid\": \"caf89ca7-12da-4a25-9974-5fc93dc4e57a\"\n },\n \"dateColDataType\": \"\",\n \"dateLoading\": false,\n \"dateTimeColDataType\": \"time\",\n \"dateTimeType\": \"DATETIME\",\n \"datetimeLoading\": false,\n \"extrapolate\": true,\n \"format\": \"time_series\",\n \"formattedQuery\": \"SELECT $timeSeries as t, count() FROM $table WHERE $timeFilter GROUP BY t ORDER BY t\",\n \"intervalFactor\": 1,\n \"query\": \"SELECT\\n $timeSeries as t,\\n count(),\\n id,\\n attributes\\nFROM $table\\n\\nWHERE $timeFilter\\n\\nGROUP BY\\n t,\\n id,\\n attributes\\nORDER BY t\\n\",\n \"rawQuery\": \"SELECT\\n (intDiv(toUInt32(time), 1) * 1) * 1000 as t,\\n count(),\\n id,\\n attributes\\nFROM test.map_table\\n\\nWHERE time >= toDateTime(1694516084) AND time <= toDateTime(1694517884)\\n\\nGROUP BY\\n t,\\n id,\\n attributes\\nORDER BY t\",\n \"refId\": \"A\",\n \"round\": \"0s\",\n \"skip_comments\": true,\n \"table\": \"map_table\",\n \"tableLoading\": false\n }\n ],\n \"title\": \"Panel Title\",\n \"transformations\": [],\n \"type\": \"timeseries\"\n}",
"mode": "code",
"code": {
"language": "json",
"showLineNumbers": true,
"showMiniMap": true
}
}
},
{
"id": 3,
"title": "Data from panel above",
"type": "table",
"datasource": {
"type": "datasource",
"uid": "-- Dashboard --"
},
"gridPos": {
"h": 7,
"w": 15,
"x": 0,
"y": 13
},
"options": {
"showTypeIcons": true
},
"targets": [
{
"datasource": {
"type": "datasource",
"uid": "-- Dashboard --"
},
"panelId": 2,
"withTransforms": true,
"refId": "A"
}
]
}
],
"schemaVersion": 37,
"title": "Debug: Panel Title // 2023-09-12 13:26:31",
"tags": [
"debug",
"debug-timeseries"
],
"time": {
"from": "2023-09-12T10:56:31.582Z",
"to": "2023-09-12T11:26:31.582Z"
}
}
Repro case:
- Create DB and table with Map(String,String) as column
> CREATE DATABASE test;
> CREATE TABLE test.map_table
(
`time` DateTime,
`id` String,
`attributes` Map(String, String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(time)
PRIMARY KEY (toDate(time), id)
ORDER BY (toDate(time), id)
TTL toStartOfMonth(time) + toIntervalMonth(12 * 5)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1;
- Insert values to mock a TimeSeries:
> INSERT INTO test.map_table values (now(), 'id1', {'key1': 'value1', 'key2':'value2'})
> INSERT INTO test.map_table values (now(), 'id2', {'key1': 'value1', 'key2':'value2'})
> INSERT INTO test.map_table values (now(), 'id2', {'key1': 'value1', 'key2':'value2'})
- Create a simple Grafana graph:
SELECT
$timeSeries as t,
count(),
id,
attributes
FROM $table
WHERE $timeFilter
GROUP BY
t,
id,
attributes
ORDER BY t
- Check that the attributes field is being set as an string: object Object and part of the name (as dataframe):
