I use Grafana Cloud with Athena plugin - and my query somehow converts NULLs to zeros (0). When I run the same query in Athena I get the NULL values as expected, but in Grafana they are COALESCEd for me.
- What Grafana version and what operating system are you using?
Grafana Cloud
Plugins: Amazon Athena v.1.0.1
- What are you trying to achieve?
Disconnect dots in a time series → retrieve NULL values from athena.
- How are you trying to achieve it?
My query selects a “value” field which contains NULL
- What happened?
The data retrieved converts my NULL to zero (0)
- What did you expect to happen?
That the NULL will remain NULL
- Can you copy/paste the configuration(s) that you are having problems with?
From Athena:
SELECT COUNT(*) FROM wall.wall_data WHERE value IS NULL # I get the records I expect
Highlights from the query inspector JSON:
{
"request": {
"url": "api/ds/query",
"method": "POST",
"data": {
"queries": [
{
"column": "value",
"connectionArgs": {
"catalog": "__default",
"database": "__default",
"region": "__default"
},
"datasource": {
"type": "grafana-athena-datasource"
},
"format": 0,
"hide": false,
"rawSQL": "SELECT date_parse(\"timestamp-utc\", '%Y-%m-%d %H:%i:%s UTC') AS dt,\r\n\"home-name\", \"wall-name\", \"sensor-name\", \r\nvalue \r\nFROM wall_data \r\nWHERE \"measurement-type\"='temp_c'\r\nORDER BY 1 ASC;",
"refId": "Temperature",
"table": "wall_data",
"datasourceId": 20,
"intervalMs": 30000,
"maxDataPoints": 848
},
],
"range": {
"from": "2021-12-26T03:05:46.200Z",
"to": "2021-12-26T09:05:46.200Z",
"raw": {
"from": "now-6h",
"to": "now"
}
},
"from": "1640487946200",
"to": "1640509546200"
},
"hideFromInspector": false
},
"response": {
"results": {
"Temperature": {
"frames": [
{
"schema": {
"name": "Temperature",
"refId": "Temperature",
"meta": {
"type": "timeseries-wide",
"executedQueryString": "SELECT date_parse(\"timestamp-utc\", '%Y-%m-%d %H:%i:%s UTC') AS dt,\r\n\"home-name\", \"wall-name\", \"sensor-name\", \r\nvalue \r\nFROM wall_data \r\nWHERE \"measurement-type\"='temp_c'\r\nORDER BY 1 ASC;"
},
"fields": [
{
"name": "dt",
"type": "time",
"typeInfo": {
"frame": "time.Time"
}
},
{
"name": "value",
"type": "number",
"typeInfo": {
"frame": "float32",
"nullable": true
},
"labels": {
"home-name": "erez-home",
"sensor-name": "air-in",
"wall-name": "research-wall"
}
},
{
"name": "value",
"type": "number",
"typeInfo": {
"frame": "float32",
"nullable": true
},
"labels": {
"home-name": "erez-home",
"sensor-name": "air-out",
"wall-name": "research-wall"
}
},
{
"name": "value",
"type": "number",
"typeInfo": {
"frame": "float32",
"nullable": true
},
"labels": {
"home-name": "erez-home",
"sensor-name": "wall-down-deeper",
"wall-name": "research-wall"
}
},
{
"name": "value",
"type": "number",
"typeInfo": {
"frame": "float32",
"nullable": true
},
"labels": {
"home-name": "erez-home",
"sensor-name": "wall-up-less-deep",
"wall-name": "research-wall"
}
}
]
},
"data": {
"values": [
[
1639942092000,
1639942107000,
1639942201000,
1639942802000,
1640501941000,
1640502001000,
1640502602000,
1640503201000,
1640503802000,
1640504401000,
1640505002000,
1640505602000,
1640506202000,
1640506801000,
1640507401000,
1640508002000,
1640508601000,
1640509202000
],
[
12.375,
12.348,
12.332,
12.319,
12.319,
12.292,
12.332,
0, // <<<< Should have been NULL
8.7,
9.039,
9.475,
9.8,
10.051,
10.278,
10.489,
10.727,
10.967,
11.178,
11.347,
11.485,
11.656
]
]
}
}
],
"refId": "Temperature"
}
}
}
}
- Did you receive any errors in the Grafana UI or in related logs? If so, please tell us exactly what they were.
No
- Did you follow any online instructions? If so, what is the URL?
I couldn’t find any. Most online resources are about the opposite problem i.e. how to COALESCE - I just want my NULLs to stay NULLs.
