Not Getting data from Index pattern using SQL

[emad101]

Hello,

I am trying to fetch all data in a field to a canvas in the elastic index, but for some reason I am receiving a weird error

here is the sql query I send
SELECT app_code FROM "devops_et*"

But I receive this error

Expression failed with the message:
[essql] > Unexpected error from Elasticsearch: [verification_exception] Found 1 problem(s) line 1:8: Unknown column [app_code], did you mean [app_code]?

This error is only specific to this field all other fields works perfectly fine. Does any one know what is the problem?

EDIT: upon inspection of the JSON file it seems that the app_code filed has a dot beside it and highlighted in red. Maybe that is the problem? How can I remove it it doesn't seem that I can delete it

{
  "_index": "devops_et",
  "_type": "type",
  "_id": "",
  "_version": 1,
  "_score": 1,
  "_source": {
    ".app_code": "",
    "github": "",
    "jenkins": "",
    "ucs": ""
  }
}

[thomasneirynck]

@emad101

it;s probably the . indeed.

The easiest way to rename a field in Elasticsearch is using the rename processor (https://www.elastic.co/guide/en/elasticsearch/reference/current/rename-processor.html)

An example on how to use it can be found here https://stackoverflow.com/questions/43120430/elasticsearch-mapping-rename-existing-field

[Andrei_Stefan]

@emad101 that restriction on dots being used as leading or trailing character has been introduced in 5.1.3 with the this bug fix.

Given that it's impossible to add such a leading dot field name in my tests on 7.x and the bug fix above, I'm assuming you are testing SQL on a 6.x or 7.x ES version, but with an index created in 5.x or earlier?

[emad101]

@Andrei_Stefan,
I have actually not added any leading dots on my field names, it was automatically done somehow when pushing data. I am also using the latest version.

If you see the below picture, there is a highlighted red dot in the appcode field in the JSON tab

However, in the table tab there is still no do in the app code field

And when I try to query through the field this is the error I get
"Unknown column [app_code], did you mean [app_code]"

This is the how I created the fields in the DevTools

[Andrei_Stefan]

Can you provide the mapping and settings of this index, please? GET /devops_et

[emad101]

@Andrei_Stefan here is the mappings of GET /devops_e

#! Deprecation: [types removal] The parameter include_type_name should be explicitly specified in get indices requests to prepare for 7.0. In 7.0 include_type_name will default to 'false', which means responses will omit the type name in mapping definitions.
{
"devops_et" : {
"aliases" : { },
"mappings" : {
"type" : {
"properties" : {
"github" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"jenkins" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"ucs" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"app_code" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
}
}
}
},
"settings" : {
"index" : {
"creation_date" : "1580826934374",
"number_of_shards" : "1",
"number_of_replicas" : "1",
"uuid" : "UG-o1DANRbeKSymed5-FAA",
"version" : {
"created" : "6080299"
},
"provided_name" : "devops_et"
}
}
}
}

[Andrei_Stefan]

Do you have other indices that have the name starting with devops_et ? (I see the query you are running is SELECT app_code FROM "devops_et*")

[emad101]

No This is the only one, when I created the index pattern I added "*", I can query through the other field without errors. Only the appcode field that gives the error.

[Andrei_Stefan]

Ok, then do this from Dev tools in Kibana or from curl: GET /devops_et/cTL7FXAB_Q0Dl34wEK98

[emad101]

This is the result that I got

{
"error": "Incorrect HTTP method for uri [/devops_et/cTL7FXAB_Q0Dl34wEK98?pretty] and method [GET], allowed: [POST]",
"status": 405
}

[Andrei_Stefan]

My bad, try GET /devops_et/_doc/cTL7FXAB_Q0Dl34wEK98

[emad101]

Here is the result

{
"_index" : "devops_et",
"_type" : "_doc",
"_id" : "cTL7FXAB_Q0Dl34wEK98",
"found" : false
}

[Andrei_Stefan]

I may have typed the document id incorrectly, the idea was to get the actual document from ES. Can you copy-paste the id from Kibana's JSON tab and get the document?

[emad101]

Sorry I did not see your response until now.
Here is the result. When I copy/paste the result the "dot" does not get copied

{
"_index" : "devops_et",
"_type" : "_doc",
"_id" : "cTL7FXAB_QODl34wEK98",
"_version" : 1,
"_seq_no" : 0,
"_primary_term" : 1,
"found" : true,
"_source" : {
"app_code" : "1000",
"github" : "10",
"jenkins" : "100%",
"ucs" : "0"
}
}

However on Devtool it is shown