Problem with SQL Query with special character in field and table name

[stef35340 (Stéphane BRITO)]

Hello,

I've an issue when I try to use fields and table names with some special characters.
My indexes are named like the following xxxxxx-prod-2018.08.28 and the fact that there is a "dash" in the index name. As workaround, I searched like the following query: "SELECT * FROM xxxxxx*28 ORDER BY 1". Any trick to use tables with special characters in their name?

Another issue I have and I don't found the solution is when I want to use the @timestamp field: "SELECT @timestamp FROM xxxxxx*28 ORDER BY 1", I obtain the following error:

{
"error": {
"root_cause": [
{
"type": "parsing_exception",
"reason": "line 1:8: mismatched input '@timestamp' expecting {'(', 'ANALYZE', 'ANALYZED', 'CAST', 'CATALOGS', 'COLUMNS', 'DEBUG', 'EXECUTABLE', 'EXISTS', 'EXPLAIN', 'EXTRACT', 'FALSE', 'FORMAT', 'FUNCTIONS', 'GRAPHVIZ', 'LEFT', 'MAPPED', 'MATCH', 'NOT', 'NULL', 'OPTIMIZED', 'PARSED', 'PHYSICAL', 'PLAN', 'RIGHT', 'RLIKE', 'QUERY', 'SCHEMAS', 'SHOW', 'SYS', 'TABLES', 'TEXT', 'TRUE', 'TYPE', 'TYPES', 'VERIFY', '{FN', '{D', '{T', '{TS', '{GUID', '+', '-', '', '?', STRING, INTEGER_VALUE, DECIMAL_VALUE, IDENTIFIER, DIGIT_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}"
}
],
"type": "parsing_exception",
"reason": "line 1:8: mismatched input '@timestamp' expecting {'(', 'ANALYZE', 'ANALYZED', 'CAST', 'CATALOGS', 'COLUMNS', 'DEBUG', 'EXECUTABLE', 'EXISTS', 'EXPLAIN', 'EXTRACT', 'FALSE', 'FORMAT', 'FUNCTIONS', 'GRAPHVIZ', 'LEFT', 'MAPPED', 'MATCH', 'NOT', 'NULL', 'OPTIMIZED', 'PARSED', 'PHYSICAL', 'PLAN', 'RIGHT', 'RLIKE', 'QUERY', 'SCHEMAS', 'SHOW', 'SYS', 'TABLES', 'TEXT', 'TRUE', 'TYPE', 'TYPES', 'VERIFY', '{FN', '{D', '{T', '{TS', '{GUID', '+', '-', '', '?', STRING, INTEGER_VALUE, DECIMAL_VALUE, IDENTIFIER, DIGIT_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}",
"caused_by": {
"type": "input_mismatch_exception",
"reason": null
}
},
"status": 400
}

Someone have any trick to use this field in SQL queries?

Thank you for you cooperation!

Stephane

[Andrei_Stefan]

Hi @stef35340,
You simply need to put double-quotes for both the index name with a dash and the field name with @:

select "@timestamp" from "xxx-123"

[NerdSec (Nachiket)]

Hi Andrei,

This does not seem to work for me. I have an ELK 6.3.2 setup. Here is the query that I am trying to run on the Dev console.

POST _xpack/sql?format=txt
{
  "query": "select * from my-alarm"
}

The output i get is as follows:

{
  "error": {
    "root_cause": [
      {
        "type": "parsing_exception",
        "reason": "line 1:19: mismatched input '-' expecting {<EOF>, ',', 'FULL', 'GROUP', 'HAVING', 'INNER', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'ORDER', 'RIGHT', 'WHERE'}"
      }
    ],
    "type": "parsing_exception",
    "reason": "line 1:19: mismatched input '-' expecting {<EOF>, ',', 'FULL', 'GROUP', 'HAVING', 'INNER', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'ORDER', 'RIGHT', 'WHERE'}",
    "caused_by": {
      "type": "input_mismatch_exception",
      "reason": null
    }
  },
  "status": 400
}

I then tried to escape them using a single quote as escaping a double quote using a double quote simply terminated the query earlier than expected.

Here is the query:

POST _xpack/sql?format=txt
{
  "query": "select * from 'my-alarm'"
}

I received the following error:

{
  "error": {
    "root_cause": [
      {
        "type": "parsing_exception",
        "reason": "line 1:15: mismatched input ''my-alarm'' expecting {'(', 'ANALYZE', 'ANALYZED', 'CATALOGS', 'COLUMNS', 'DEBUG', 'EXECUTABLE', 'EXPLAIN', 'FORMAT', 'FUNCTIONS', 'GRAPHVIZ', 'MAPPED', 'OPTIMIZED', 'PARSED', 'PHYSICAL', 'PLAN', 'RLIKE', 'QUERY', 'SCHEMAS', 'SHOW', 'SYS', 'TABLES', 'TEXT', 'TYPE', 'TYPES', 'VERIFY', IDENTIFIER, DIGIT_IDENTIFIER, TABLE_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}"
      }
    ],
    "type": "parsing_exception",
    "reason": "line 1:15: mismatched input ''my-alarm'' expecting {'(', 'ANALYZE', 'ANALYZED', 'CATALOGS', 'COLUMNS', 'DEBUG', 'EXECUTABLE', 'EXPLAIN', 'FORMAT', 'FUNCTIONS', 'GRAPHVIZ', 'MAPPED', 'OPTIMIZED', 'PARSED', 'PHYSICAL', 'PLAN', 'RLIKE', 'QUERY', 'SCHEMAS', 'SHOW', 'SYS', 'TABLES', 'TEXT', 'TYPE', 'TYPES', 'VERIFY', IDENTIFIER, DIGIT_IDENTIFIER, TABLE_IDENTIFIER, QUOTED_IDENTIFIER, BACKQUOTED_IDENTIFIER}",
    "caused_by": {
      "type": "input_mismatch_exception",
      "reason": null
    }
  },
  "status": 400
}

This is a major issue as we create indices on a daily basis and they all dates are separated by '-'. Is it possible to query indices that have special characters?

Regards,
Nachiket

[NerdSec (Nachiket)]

OK. Got it to work. Andrei was right, you will have to escape it. I ran this on the console as follows:

POST _xpack/sql
{
  "query": "select \"@timestamp\" from \"my-alarms-2018.08.28\""
}

Apparently, you had to escape the double quotes.

[stef35340 (Stéphane BRITO)]

So cool! Thanks NerdSec and Andrei_Stefan! The solution is indeed to to escape double quotes before the name of the table or field with specific characters.

Have a nice day!