Error using Dremio combined with Elasticsearch and PowerBI

Hey All,

I came across Dremio today because I was looking for a way to connect Microsoft Power BI to our Elasticsearch logging database. I got Dremio up and running, connected to Elasticsearch and i got the powerbi connector running.

In Power BI, when i select the Dremio plugin at “Get Data”, i get a nice list of all my Elasticsearch indices. Each index contains a “default” type and a “logevent” type table (they are all logstash entries). I want to do powerbi on my logstash (logevent) entries, but when i want to drill down the data (I click on “Table” in the Data column I get the following error:

(I’m usign Elasticsearch 5.6, Dremio 1.4.4 and Power BI 2.52.4921.581 64-bit (November 2017))

DataSource.Error: ODBC: ERROR [HY000] [Dremio][Connector] (1040) Dremio failed to execute the query: select “@timestamp”,
“level”,
“fields”,
“message”,
“exceptions”,
“messageTemplate”,
"_index",
"_type",
"_uid"
from “Elasticsearch.bamboo-logstash-2018.01.22”.“logevent”
[30029]Query execution error. Details:[
VALIDATION ERROR: Table ‘Elasticsearch.bamboo-logstash-2018.01.22.logevent’ not found

Sql Query select “@timestamp”,
“level”,
“fields”,
“message”,
“exceptions”,
“messageTemplate”,
"_index",
"_type",
"_uid"
from “Elasticsearch.bamboo-logstash-2018.01.22”."logevent"
startLine 10
startColumn 6
endLine 10
endColumn 47

[Error Id: f7df1e81-b0de-4b9d-a6ba-f528af14d6c4 on ECG-PC007.mshome.net:31010]

(org.apache.calcite.runtime.CalciteContextException) From line 10, column 6 to line 10, column 47: Table ‘Elasticsearch.bamboo-logstash-2018.01.22.logevent’ not found
sun.reflect.GeneratedConstructorAccessor142.newInstance():-1
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():-1
java.lang.reflect.Constructor.newInstance():-1
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.sql.SqlUtil.newContextException():798
org.apache.calcite.sql.SqlUtil.newContextException():786
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4080
org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():106
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2859
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2844
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3077
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
org.apache.calcite.sql.SqlSelect.validate():208
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():866
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():577
com.dremio.exec.planner.sql.SqlConverter.validate():188
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():165
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():153
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():66
com.dremio.exec.work.foreman.AttemptManager.run():293
java.util.concurrent.ThreadPoolExecutor.runWorker():-1
java.util.concurrent.ThreadPoolExecutor$Worker.run():-1
java.lang.Thread.run():-1
Caused By (org.apache.calcite.sql.validate.SqlValidatorException) Table ‘Elasticsearch.bamboo-logstash-2018.01.22.logevent’ not found
sun.reflect.GeneratedConstructorAccessor141.newInstance():-1
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():-1
java.lang.reflect.Constructor.newInstance():-1
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.runtime.Resources$ExInst.ex():572
org.apache.calcite.sql.SqlUtil.newContextException():798
org.apache.calcite.sql.SqlUtil.newContextException():786
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4080
org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl():106
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2859
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2844
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3077
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():910
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():891
org.apache.calcite.sql.SqlSelect.validate():208
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():866
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():577
com.dremio.exec.planner.sql.SqlConverter.validate():188
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():165
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():153
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():66
com.dremio.exec.work.foreman.AttemptManager.run():293
java.util.concurrent.ThreadPoolExecutor.runWorker():-1
java.util.concurrent.ThreadPoolExecutor$Worker.run():-1
java.lang.Thread.run():-1

]
Details:
DataSourceKind=Dremio
DataSourcePath=localhost
OdbcErrors=Table

Can you help me with this or point me out what other information you require to assist?

My first idea is that the dots in your index are confusing PowerBI when generating the query. As a workaround, can you try to save your index as a virtual dataset (with no dot in its name), and then query it in PowerBI?