# Not Getting data from Index pattern using SQL

**URL:** <https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031>\
**Category:** Kibana\
**Created:** [February 5, 2020, 5:11pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031 "2020-02-05T17:11:12Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![emad101](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emad101/32/82338_2.png) [@emad101](https://discuss.elastic.co/u/emad101)\
**Post date:** [February 5, 2020, 5:11pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/1 "2020-02-05T17:11:12Z")

</div>

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": ""
  }
}
```

---

<div class="post-metadata">

**Author:** ![thomasneirynck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thomasneirynck/32/23313_2.png) [@thomasneirynck](https://discuss.elastic.co/u/thomasneirynck)\
**Post date:** [February 5, 2020, 7:10pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/2 "2020-02-05T19:10:11Z")

</div>

@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](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](https://stackoverflow.com/questions/43120430/elasticsearch-mapping-rename-existing-field)

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [February 11, 2020, 8:40am UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/3 "2020-02-11T08:40:03Z")

</div>

@emad101 that restriction on dots being used as leading or trailing character has been introduced in 5.1.3 with the [this bug fix](https://github.com/elastic/elasticsearch/pull/22891).

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?

---

<div class="post-metadata">

**Author:** ![emad101](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emad101/32/82338_2.png) [@emad101](https://discuss.elastic.co/u/emad101)\
**Post date:** [February 13, 2020, 2:23pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/4 "2020-02-13T14:23:03Z")

</div>

@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

 ![Screen Shot 2020-02-13 at 9.19.58 AM](https://us1.discourse-cdn.com/elastic/original/3X/4/4/4422f99aa50957b92edb37665ed88f8269d0abe3.png)

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

 ![Screen Shot 2020-02-13 at 9.20.28 AM](https://us1.discourse-cdn.com/elastic/original/3X/b/9/b9eea4602676c9c681e4059ecc15c5e140d40a0e.png)

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

 ![Screen Shot 2020-02-13 at 9.41.53 AM](https://us1.discourse-cdn.com/elastic/original/3X/1/7/17f609217b32600438bff54bc65c4f8ae3280c98.png)

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [February 13, 2020, 2:43pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/5 "2020-02-13T14:43:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![emad101](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emad101/32/82338_2.png) [@emad101](https://discuss.elastic.co/u/emad101)\
**Post date:** [February 13, 2020, 2:48pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/6 "2020-02-13T14:48:49Z")

</div>

@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"  
> }  
> }  
> }  
> }

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [February 13, 2020, 2:55pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/7 "2020-02-13T14:55:01Z")

</div>

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*"`)

---

<div class="post-metadata">

**Author:** ![emad101](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emad101/32/82338_2.png) [@emad101](https://discuss.elastic.co/u/emad101)\
**Post date:** [February 13, 2020, 3:09pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/8 "2020-02-13T15:09:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [February 13, 2020, 4:33pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/9 "2020-02-13T16:33:30Z")

</div>

Ok, then do this from Dev tools in Kibana or from curl: GET /devops\_et/cTL7FXAB\_Q0Dl34wEK98

---

<div class="post-metadata">

**Author:** ![emad101](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emad101/32/82338_2.png) [@emad101](https://discuss.elastic.co/u/emad101)\
**Post date:** [February 13, 2020, 4:55pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/10 "2020-02-13T16:55:03Z")

</div>

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  
> }

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [February 13, 2020, 5:16pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/11 "2020-02-13T17:16:45Z")

</div>

My bad, try GET /devops\_et/\_doc/cTL7FXAB\_Q0Dl34wEK98

---

<div class="post-metadata">

**Author:** ![emad101](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emad101/32/82338_2.png) [@emad101](https://discuss.elastic.co/u/emad101)\
**Post date:** [February 13, 2020, 5:31pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/12 "2020-02-13T17:31:09Z")

</div>

Here is the result

> {  
> "\_index" : "devops\_et",  
> "\_type" : "\_doc",  
> "\_id" : "cTL7FXAB\_Q0Dl34wEK98",  
> "found" : false  
> }

---

<div class="post-metadata">

**Author:** ![Andrei\_Stefan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/andrei_stefan/32/47533_2.png) [@Andrei\_Stefan](https://discuss.elastic.co/u/Andrei_Stefan)\
**Post date:** [February 14, 2020, 1:26am UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/13 "2020-02-14T01:26:36Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![emad101](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emad101/32/82338_2.png) [@emad101](https://discuss.elastic.co/u/emad101)\
**Post date:** [February 19, 2020, 7:25pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/14 "2020-02-19T19:25:44Z")

</div>

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

![Screen Shot 2020-02-19 at 2.25.05 PM](https://us1.discourse-cdn.com/elastic/original/3X/6/3/63315f0c66b98372a24788d7bc6492ae459f75bb.png)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 18, 2020, 7:25pm UTC](https://discuss.elastic.co/t/not-getting-data-from-index-pattern-using-sql/218031/15 "2020-03-18T19:25:48Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.