SQL: support queries across indices with different mappings

[costin]

For ease of use, it should be possible to run SQL queries across multiple indices (which we support) with different mappings (which we do not).
This entails creating a virtual schema from merging the mapping and dealing with missing fields across the various indices.

The areas of investigation are dealing with the field source (especially for missing fields) and aggregations.

[elasticmachine]

Pinging @elastic/es-search-aggs

[bitkowitz]

This was the first major blocker that I encountered when trying to use SQL with the contents of my cluster.
Most of the indices in my cluster take advantage of elasticsearch's automatic mappings, so our log content
and schema can evolve overtime. It would be too onerous to enforce a common mapping across our daily log indices and we would lose one of the key benefits of elasticsearch for low-maintenance log ingestion.

[costin]

Hi,
It took a bit more time (with holidays and all) but this issue has been finally been resolved. Available in the upcoming 6.5.x and 7.x

[bitkowitz]

Thank you. I’m looking forward to trying it.

[costin]

Cheers and thanks for your patience!

[happybin92]

hi @costin thank you for your solve this problem, but i also find some cases can't do. I use es 6.8.1.
Could you help me?

1. the same field in 2 index with different doc_values.
   doc_values is for Sorting, aggregations.

PUT log-20190720, log-20190719
{
"aliases": {"log":{}},
"mappings": {
"log":{
"properties":
{
"age" : {
"type": "long",
"doc_values": true
}
}
}
}
}

PUT log-20190721
{
"aliases": {"log":{}},
"mappings": {
"log":{
"properties":
{
"age" : {
"type": "long",
"doc_values": false
}
}
}
}
}

and use sql:

SELECT * from log group by age

it will error with "Can't load fielddata on [age] because fielddata is unsupported on fields of type [long]. Use doc values instead."

I know it 's log-20190721 without doc_values. but the log-20190720, log-20190719 is ok
So, i think the better result is that, sql execute in that right indexes ( log-20190720, et), and ignore the wrong indexes.