Error using Dremio combined with Elasticsearch and PowerBI

1

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?

2

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?