Sql access query not working via curl

Elastic Stack Elasticsearch
1

we have 6.5 elk . and in dev tools following query works fine
POST /_xpack/sql?format=txt
{
"query": "select "@timestamp" from "mbeat-2020.01.23" where "@timestamp" >CAST('2020-01-23T05:00:00Z' AS TIMESTAMP)"
}

but the same is throwing error via curl

[root ~]# curl -XGET "http://localhost:9200/_xpack/sql?format=txt" -H 'Content-Type: application/json' -d'

{
"query": "select "@timestamp" from "mbeat-2020.01.23" where "@timestamp" >CAST("2020-01-23T05:00:00Z" AS TIMESTAMP)"
}'
{"error":{"root_cause":[{"type":"x_content_parse_exception","reason":"[3:14] [sql/query] failed to parse object"}],"type":"x_content_parse_exception","reason":"[3:14] [sql/query] failed to parse object","caused_by":{"type":"json_parse_exception","reason":"Unexpected character ('2' (code 50)): was expecting comma to separate Object entries\n at [Source: org.elasticsearch.transport.netty4.ByteBufStreamInput@1789e39b; line: 3, column: 102]"}},"status":400}[root@ukwysms00235 ~]#

it is not accepting CAST("2020-01-23T05:00:00Z" ,
i tried with single quotes , escaping with \ on both single /double quotes . but none is working

2

This has worked for me

POST /_xpack/sql?format=txt
{
"query": "select \"@timestamp\" from \"foo\" where \"@timestamp\" >CAST('2020-01-23T05:00:00Z' AS TIMESTAMP)"
}

maybe you can share the examples that you tried. Keep in mind that you have to create valid JSON.

3

Hi As i said earlier is working in Dev tools kibana
POST /_xpack/sql?format=txt
{
"query": "select "@timestamp" from "mbeat-2020.01.23" where "@timestamp" >CAST('2020-01-23T05:00:00Z' AS TIMESTAMP)"
}

I get only when i run in via curl

4

Hi @vijay_kaali

In dev tools you mentioned POST but if you see your curl request you are doing with GET method.

..........

Please use POST method in curl command also as below:
curl -X POST "localhost:9200/_sql?format=txt&pretty" -H 'Content-Type: application/json' -d'

Regards,
Harsh Bajaj

5

Post is not a problem . but in curl it is throwing error on cast timestamp literal value part

curl -XPOST "http://localhost:9200/_xpack/sql?format=txt" -H 'Content-Type: application/json' -d'

{
"query": "select "@timestamp" from "metricbeat.2020.01.23" where "@timestamp" >CAST('2020-01-23T05:00:00Z' AS TIMESTAMP)"
}'
{"error":{"root_cause":[{"type":"parsing_exception","reason":"line 1:88: identifiers must not start with a digit; please use double quotes"}],"type":"parsing_exception","reason":"line 1:88: identifiers must not start with a digit; please use double quotes"},"status":400}[root@ukwysms00235 ~]#

curl -XPOST "http://localhost:9200/_xpack/sql?format=txt" -H 'Content-Type: application/json' -d'

{
"query": "select "@timestamp" from "metricbeat.2020.01.23" where "@timestamp" >CAST("2020-01-23T05:00:00Z" AS TIMESTAMP)"}'
{"error":{"root_cause":[{"type":"verification_exception","reason":"Found 1 problem(s)\nline 1:80: Unknown column [2020-01-23T05:00:00Z]"}],"type":"verification_exception","reason":"Found 1 problem(s)\nline 1:80: Unknown column [2020-01-23T05:00:00Z]"},"status":400}[root@ukwysms00235 ~]#

if i remove full where it is working fine .

6

You need to escape the double quotes surrounding @timestamp. You look closely at this query - Sql access query not working via curl - there are backslashes there for the double quotes.

Also, CAST("2020-01-23T05:00:00Z" AS TIMESTAMP) should be used with single quotes: CAST('2020-01-23T05:00:00Z' AS TIMESTAMP).

Please, read here more about single quotes and double quotes in ES SQL: https://www.elastic.co/guide/en/elasticsearch/reference/7.x/sql-lexical-structure.html#sql-syntax-single-vs-double-quotes

8

2020 has to be replaced by single quote,backslash,double single quote 2020
CAST('''2020-01-28T05:00:00Z''' . backslash is removed in reply formatter here .

another problem is if i do max(@timestamp ) then , it returns double value instead of timestamp format both in using sql /json output . how to correct it .

And is there any way to extract json value via shell script or make json section in single line

9

max(date_field) indeed used to return a double value. But this bug is fixed in more recent versions: 6.6.3 is the earliest one that has the fix. For more details and more versions where the fix has been applied, have a look here: https://github.com/elastic/elasticsearch/pull/40377.

For json output (and on a single line), use format=json: /_xpack/sql?format=json

10

Thanks a lot .

11

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