ElasticSearch date series indicies

1

We are using Dremio to connect our ElasticSearch data to Tableau for visualisation.

We have connected the ElasticSearch source to Dremio, however ElasticSearch has date series indices. This means that for every ‘index’ in ElasticSearch, we have nearly 500 Dremio data sources (one for every day from when we started collecting data in 2016). It looks like this…
image

How can these date series indices be joined without using a long and nasty SQL query?

2

There are two good options here:

  1. an alias in elasticsearch.
  2. use a wildcard in Dremio

See this tutorial for more info: https://www.dremio.com/tutorials/elasticsearch-sql-query-multiple-indexes/

Also, note that we only support ES 2.x and 5.x, but hope to add support for 6.x this summer.

3

Thanks @kelly

We are having difficulty joining more than 4 indicies at a time. We get an error that says something along the lines of ‘you don’t have access to these tables’, which is a misleading error message. This happens with both a wildcard SQL statement as well as when creating an alias.

Is this a known issue?

We are using ES 2.3

4

If this is also happening when you create an alias in ES then it sounds like an issue there. Have you trying seeing if one of the more recent versions has the same issue?

5

You should be able to query all the folders at once in Dremio. Can you share a query profile of the failed attempt?

6

We have been able to complete queries with more folders with some increased provisioning of the ElasticSearch server. Thanks!