# Canvas sql throwing syntax error

**URL:** <https://discuss.elastic.co/t/canvas-sql-throwing-syntax-error/237642>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [June 18, 2020, 1:18pm UTC](https://discuss.elastic.co/t/canvas-sql-throwing-syntax-error/237642 "2020-06-18T13:18:24Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![vijay\_kaali](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/vijay_kaali/32/59998_2.png) [@vijay\_kaali](https://discuss.elastic.co/u/vijay_kaali)\
**Post date:** [June 18, 2020, 1:18pm UTC](https://discuss.elastic.co/t/canvas-sql-throwing-syntax-error/237642/1 "2020-06-18T13:18:24Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [July 15, 2020, 1:30pm UTC](https://discuss.elastic.co/t/canvas-sql-throwing-syntax-error/237642/2 "2020-07-15T13:30:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\
**Post date:** [July 15, 2020, 1:43pm UTC](https://discuss.elastic.co/t/canvas-sql-throwing-syntax-error/237642/3 "2020-07-15T13:43:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 12, 2020, 1:44pm UTC](https://discuss.elastic.co/t/canvas-sql-throwing-syntax-error/237642/4 "2020-08-12T13:44:00Z")

</div>

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