Canvas sql throwing syntax error

Elastic Stack Kibana
1

I am trying create canvas line chart with following sql

SELECT avg(beat.system.cpu.user.norm.pct) as avg1 ,
beat.hostname, MINUTE_OF_HOUR("@timestamp") as min   FROM "all_metric*" 
group by beat.hostname,
MINUTE_OF_HOUR("@timestamp");

but i am getting

Whoops! Expression failed

Expression failed with the message:

// [essql] > Couldn't parse Elasticsearch SQL query. You may need to add 
double quotes to names containing special characters. Check your query 
and try again. Error: [parsing_exception] line 5:29: extraneous input ';' 
expecting {<EOF>, ',', 'AND', 'BETWEEN', 'HAVING', 'IN', 'IS', 'LIKE', 'LIMIT',
'NOT', 'OR', 'ORDER', 'RLIKE', LIMIT_ESC, '=', '<=>', NEQ, '<', '<=', '>', '>=',
'+', '-', '*', '/', '%'} //

Anything missing here

2

In Elasticsearch sql you don't need a semicolon at the end of your query as you can only specify a single one per request anyway.

The rest of the query looks fine.

3

Indeed, I could run this query in canvas with similar data coming from metricbeat

select
  avg("system.load.norm.1") as avg,
  "agent.hostname",
  minute_of_hour("@timestamp") as min
from "metricbeat*" 
group by "agent.hostname", min

and as @flash1293 mentioned, adding the semicolon caused the same error you reported.

Note also that you can use the aliased result for the minutes for readability.