Elasticsearch SQL Support for UNIONs

[bsandell]

Describe the feature: Since Elasticsearch now has a JDBC driver and a pretty feature rich SQL dialect, I would love to test it and see if we could use it as a data source for our Looker Business Intelligence software. For all of the SQL dialect that we support, we have a test suite of about 1400 queries that we run against them. Although the product itself does not require support for UNIONS, we have a lot (more than 100) tests in our test suite that assemble test data sets using syntax similar to select 'a' as col1 UNION select 'b' as col1 UNION select'c' as col1 so without UNION support, it's currently not possible to run our test suite against it. If there is any way that you could implement UNION support, that would be fantastic. If it's not a feature that's on your roadmap, even a terrible, slow implementation that was enabled using some undocumented flag would be fine, as long as we can get the tests to run against it.

Our current solution for customers that want to use Looker with their Elasticsearch data is to have them use one of our ETL partners to move data into a supported data store, or to use a data virtualization platform like Data Virtuality or Dremio. It would be fantastic is customers could just connect directly to Elasticsearch and run SQL queries on it directly from Looker.

[elasticmachine]

Pinging @elastic/es-search-aggs

[jasontedor]

I can not comment on the specifics of this. However:

If it's not a feature that's on your roadmap, even a terrible, slow implementation that was enabled using some undocumented flag would be fine, as long as we can get the tests to run against it.

We do not build features like this. We build features with purpose that scale.

[paulcarey]

Expressing my interest in support for union too, and wondering if its earmarked for a particular version?

[costin]

UNION ALL is already supported for the same query by using index patterns: SELECT * FROM "index-*" which is the equivalent to:

SELECT * FROM "index-A"
UNION ALL
SELECT * FROM "index-B"

[paulcarey]

Thanks. I'm interested in union or union all for the ability to aggregate / normalize fields at query time. For example

select notional as amount from trades where product = 'IRS'
union
select exchangeCurrencyAmount as amount from trades where product = 'SPOT'

The reason this kind of aggregation cannot be performed at indexing time is that there's no consensus as to how fields should be aggregated - this determination is dependent on the perspective of the client executing the query.

[aspyct]

Hi,

Just wanted to chime in for this union all request. I'm currently building reports with Kibana Canvas, and the support of union would be a great help for stacked charts that need multiple data series.

That would allow something in the spirit of:

SELECT SUM(active_accounts), 'active' FROM "reporting-*" GROUP BY client
UNION ALL
SELECT SUM(inactive_accounts), 'inactive' FROM "reporting-*" GROUP BY client

Otherwise, without the union, it seems my only option is modifying the data I have in reporting-*, which is a heavier task.

[maltoze]

any workaround?

[matriv]

@maltoze If using an index wildcard to query multiple indices simultaneously doesn't work for your usecase, I'm afraid there is no workaround currently.

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[wchaparro]

Closing as not planned, superceded by our work in ES|QL. Please reopen if you have concerns.