how to retrieve the index name when translating SQL query in elasticsearch/opensearch

Question

According to the official doc of elasticsearch/opendistro, the SQL translate API could translate SQL query into elasticsearch DSL. However, it only returns columns, filters, size and sorting operators without any index/table name.

E.g., given sql query SELECT * FROM library LIMIT 2, the SQL translate API will respond:

{
  "size": 2,
  "_source": false,
  "fields": [
    {
      "field": "author"
    },
    {
      "field": "name"
    },
    {
      "field": "page_count"
    },
    {
      "field": "release_date",
      "format": "strict_date_optional_time_nanos"
    }
  ]
}

And the index name (library) is missing. I tried opendistro and it is the same.

Notice that for aggregated query across two table/index, it will return Physical Plan and Logical Plan that contains TableScan attributes.

score 2 · Accepted Answer · answered Mar 11 '22 at 04:57

Currently Elasticsearch not support to return index name for Translate API. There is already open Github issue #41856.

It will return you only query source and you need to execute translate source with same index name which you have used in SQL otherwise it will be throw error or return unexpected result as mentioned in #34594 GitHub issue.

how to retrieve the index name when translating SQL query in elasticsearch/opensearch

1 Answers1