ElasticSearch: Opendistro SQL: Failed to parse query due to offending symbol [.11]

Question

I have an ElasticSearch index which has a name with . (Example: my_index-2020.11.06-001). When I use SQL to get the count of all documents, I am getting the following error

curl --location --request POST '127.0.0.1:9200/_opendistro/_sql' 
--header 'Content-Type: application/json'
--data-raw '{
  "query": "SELECT count(*) FROM my_index-2020.11.06-001"
}'

Failed to parse query due to offending symbol [.11] at: 'SELECT count(*) FROM my_index-2020.11.06-001 ...

I have also tried to use backtick (`) and single quotes (') in the index name, that is not helping either

curl --location --request POST '127.0.0.1:9200/_opendistro/_sql' 
--header 'Content-Type: application/json'
--data-raw '{
  "query": "SELECT count(*) FROM `my_index-2020.11.06-001`"
}'

{
  "error": {
    "reason": "Invalid SQL query",
    "details": "Field [my_index-2020.11.06-001] cannot be found or used here.",
    "type": "SemanticAnalysisException"
    ...

Is there any other way to resolve this issue?

score 1 · Answer 1 · answered Mar 05 '21 at 03:51

It is a bug but there is a workaround Disable semantic analyzer

curl --location --request PUT 'http://localhost:9200/_cluster/settings' \
--header 'Content-Type: application/json' \
--data-raw '{
    "transient": {
        "opendistro.sql.query.analysis.enabled": "false"
    }
}'

ElasticSearch: Opendistro SQL: Failed to parse query due to offending symbol [.11]

1 Answers1