Elastic SQL and cURL not playing nice

[rdesanno (Rob)]

I'm having the hardest time trying to figure out what i'm doing wrong here and hope that someone else has some pointers

I can run the following query in Dev Tools

POST _sql
{
  "query": "SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \"test*\" WHERE agent.hostname = 'host-1-1' AND TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 250 MINUTES GROUP BY TIME,HOST,TEMP ORDER BY TIME,HOST DESC LIMIT 5"
}

but the curl command that it gives me to use

curl -XPOST "http://localhost:9200/_sql" -H 'Content-Type: application/json' -d'{ "query": "SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \"test*\" WHERE agent.hostname = \"host-1-1\" AND TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 250 MINUTES GROUP BY TIME,HOST,TEMP ORDER BY TIME,HOST DESC LIMIT 5"}'

results in an error. I'm confident its related to this part not being able to get parsed correctly:

agent.hostname = \"host-1-1\"

Depending on how I escape it, I get either an Unexpected character error or Found 1 problem(s)\nline 1:127: Unknown column [host-1-1]" but never the results I am looking for. I can query it the following way but its not optimal

./bin/elasticsearch-sql-cli -d http://user:password@localhost:9200 < test.sql

Anyone have any experience with something similar?

[stephenb (Stephen Brown)]

Hmm yes interesting....

I have constructed some similar data and queries and having same issues with the WHERE the - seems to be an issue, then when I compared to a value without an - then I am getting an Unknown column error.

What I did get to work is put the query in a data.json file then use this syntax

curl -XPOST "http://localhost:9200/_sql?format=txt" -H 'Content-Type: application/json' -d @data.json

And that worked.

But now I am curious too, suspect it is a shell + curl parsing issue

[DavidTurner]

SQL distinguishes "double-quoted-strings" (which are things like column names possibly containing funny characters) from 'single-quoted-strings' (which are string literals).

Your original query says WHERE agent.hostname = 'host-1-1' where host-1-1 is a string literal since it uses single quotes.

The curl command says WHERE agent.hostname = "host-1-1" where host-1-1 is a column name since it uses double quotes.

I think you want this:

curl 'http://localhost:9200/_sql' -H 'Content-type: application/json' --data-binary "{\"query\":\"SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \\\"test*\\\" WHERE agent.hostname = 'host-1-1' AND TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 250 MINUTES GROUP BY TIME,HOST,TEMP ORDER BY TIME,HOST DESC LIMIT 5\"}"

Using double-quotes means you can include single-quotes in the string, but also means you must escape all the double-quotes. Alternatively you can use this:

curl 'http://localhost:9200/_sql' -H 'Content-type: application/json' --data-binary $'{"query":"SELECT agent.hostname AS HOST, timestamp AS TIME, round(TEMPERATURE_A,1) AS TEMP FROM \\\"test*\\\" WHERE agent.hostname = \'host-1-1\' AND TEMP is not null AND TIME < NOW() AND TIME > NOW() - INTERVAL 250 MINUTES GROUP BY TIME,HOST,TEMP ORDER BY TIME,HOST DESC LIMIT 5"}'

A $'dollar-single-quoted-string' works like a "double-quoted-string" except double-quotes are no longer special so do not need escaping. You still need to escape the double-quotes inside the JSON string, however, because that's how JSON works, and then you need to escape the escape characters too, hence FROM \\\"test*\\\".

However really I would do what @stephenb did and put the JSON in a file, because this avoids all the shell quoting issues.

[DavidTurner]

By the way @rdesanno if the curl command coming from the Dev Tools doesn't match what happens in Dev Tools itself then I think that's a Kibana bug. Please would you open an issue on Github about it?

[rdesanno (Rob)]

THIS IS THE SOLUTION!!

Wow, I was triple-escaping the hostname but never thought to do just the index. Thanks for taking the time to explain this to me! You just made my day!

[rdesanno (Rob)]

Will do