Canvas sql throwing syntax error

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

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.

1 Like

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.

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.