Issues while using SQL queries with curl

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}

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" }'

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.

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.

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 " }'

@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!

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