Issues while using SQL queries with curl

[tarund (Tarun)]

Hi there,

I am trying to export data out of Elasticsearch into csv files using SQL queries through curl. Most of the basic queries work fine, but when I try to use LIKE operator or CASE WHEN operators, I get exceptions. Same queries work fine from Kibana dev console.
Can you please help ?
Please find below queries & exceptions recvd -

1. curl -XGET "http://20.62.27.19:9200/_sql?format=csv" -H 'Content-Type: application/json' -d'{ "query": "\n SELECT HISTOGRAM("@timestamp", INTERVAL 1 HOUR) AS t,COUNT(*) AS count\n FROM "rtransactions"\n WHERE event LIKE "mp%trof%"\n GROUP BY t\n " }'

"error":{"root_cause":[{"type":"parsing_exception","reason":"line 1:152: mismatched input '"mp%trof%"' expecting {'?', STRING}"}],"type":"parsing_exception","reason":"line 1:152: mismatched input '"mp%trof%"' expecting {'?', STRING}","caused_by":{"type":"input_mismatch_exception","reason":null}},"status":400}

[matriv (Marios Trivyzas)]

You need single quotes around the string literals and you need to escape them properly:

curl -XGET "http://20.62.27.19:9200/_sql?format=csv" -H 'Content-Type: application/json' -d'{ "query": "SELECT HISTOGRAM(\"@timestamp\", INTERVAL 1 HOUR) AS t,COUNT(*) AS count FROM \"rtransactions\" WHERE event LIKE '"'"'mp%trof%'"'"' GROUP BY t" }'

[tarund (Tarun)]

Thanks @matriv. it worked. I had tried both single & double quotes but I was using \ to escape.
Can you help me understand the syntax for escaping string literals.
I tried your solution inside a CASE WHEN clause, but that didn't work. It didn't throw an error, but the docs were not filtered and went in the ELSE block.

[matriv (Marios Trivyzas)]

If it was SQL statement on the CLI for example it would simply be:

SELECT HISTOGRAM("@timestamp", INTERVAL 1 HOUR) AS t,COUNT(*) AS count FROM "rtransactions" WHERE event LIKE 'mp%trof%' GROUP BY t

Now since you are inside a json document you have to escape the " as \".
The single quotes wouldn't need escaping but because you are in the shell and you have an open ' for the body you have to escape them as shown.

[tarund (Tarun)]

But it still doesn't work inside a CASE WHEN clause. It didn't throw an error, but the docs were not filtered and went in the ELSE block. Please suggest what am I missing ?

curl -XGET "http://20.62.27.19:9200/_sql?format=csv" -H 'Content-Type: application/json' -d'{ "query": " SELECT HISTOGRAM("@timestamp", INTERVAL 1 HOUR) AS t,COUNT(*) AS count, CASE WHEN labels.event LIKE '"'"'c%ato%'"'"' THEN 1 WHEN labels.event LIKE '"'"'c%rot%'"'"' THEN 2 ELSE 4 END AS type FROM "atransaction" GROUP BY t,type " }'

[matriv (Marios Trivyzas)]

@tarund,

• You still need to escape the " around @timestamp and atransaction with \.
• There is a bug regarding the usage of LIKE inside another scalar function (in your case: CASE WHEN ... ELSE ... END).

Thanks for catching that!