Error using SQL Translate API

[sanjaysubramanian]

My index name is called fan_messages

curl -XPUT "http://localhost:9200/fan_messages" -H 'Content-Type: application/json' -d'
{
    "mappings" : {
        "properties": {
                    "email": { "type": "keyword" },
                    "yyyy_mm_dd": { "type": "keyword" }
        }
    }
}
'

Translate API
-------------------
POST
--------
http://localhost:9200/fan_messages/translate

BODY 
--------
{
    "query": "SELECT yyyy_mm_dd , count(distinct email) FROM fan_messages group by yyyy_mm_dd",
    "fetch_size": 10
}

RESPONSE
---------------
{
    "error": {
        "root_cause": [
            {
                "type": "illegal_argument_exception",
                "reason": "Rejecting mapping update to [fan_messages] as the final mapping would have more than 1 type: [_doc, translate]"
            }
        ],
        "type": "illegal_argument_exception",
        "reason": "Rejecting mapping update to [fan_messages] as the final mapping would have more than 1 type: [_doc, translate]"
    },
    "status": 400
}

[dadoonet (David Pilato)]

Here is how to fix it:

DELETE /fan_messages
PUT /fan_messages
{
  "mappings": {
    "properties": {
      "email": {
        "type": "keyword"
      },
      "yyyy_mm_dd": {
        "type": "keyword"
      }
    }
  }
}

POST /_sql/translate
{
  "query": "SELECT yyyy_mm_dd , count(distinct email) FROM fan_messages group by yyyy_mm_dd",
  "fetch_size": 10
}

Translate documentation is here: https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-translate.html

[sanjaysubramanian]

Thanks @dadoonet (David Pilato) . That worked thank you