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 -
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 " }'
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 " }'
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.