Hint: Readable SQL Queries to elasticsearch via curl

[Skeeve]

I wanted to do some tests using SQL queries to elasticsearch from commandline. I ended up with a really messy looking curl command:

curl -XPOST http://ACCOUNT:APIKEY@elasticsearch:9200/_sql?format=tsv -H 'Content-Type: application/json' -d '{"query":"select customer_number, complaint_address, tenant_name from "ip-owner" where dumptime > CURDATE() - INTERVAL 1 DAY and complaint_address is not null and complaint_address != '"''"' and customer_number = '"'0004'"' group by customer_number , complaint_address , tenant_name"}'

So I fiddled around a bit to make the real query a bit more readable. I thought the tip might be useful for others too, so here it is:

curl -XPOST http://ACCOUNT:APIKEY@elasticsearch:9200/_sql?format=tsv \
-H 'Content-Type: application/json' \
-d "{\"query\":\"$( while read line ; do echo -n $line' ' ; done <<'QUERY'
  select customer_number
      , complaint_address
      , tenant_name
  from \\"ip-owner\\"
  where dumptime > CURDATE() - INTERVAL 1 DAY
  and complaint_address is not null
  and complaint_address != ''
  and customer_number = '0004'
  group by customer_number
        , complaint_address
        , tenant_name
QUERY
)\"}"

The first 3 lines have to be adjusted to one's own requirements. The last 2 lines have to be used as they are. In between one can simply put the query as one wants except for double quotes which have to be escaped by 2 backslashes.

I hope this tip is useful.

[Andrei_Stefan]

Why not using the SQL CLI? https://www.elastic.co/guide/en/elasticsearch/reference/7.x/sql-cli.html

[Skeeve]

Simply because it's sometimes not possible.

On almost all computers you'll find curl but the SQL CLI will only be available (usually) on your elasticsearch machine.

In my case it was because of some tests I needed. I access elasticsearch from perl and had issues with encoding. In order to pinpoint the part where the encoding went wrong, I required "untouched" output. As I do not know what is done to the data by the CLI I needed something where I know that nothing is done to it and something where I can redirect the output to a file.