Column not found for Elasticsearch as a source

Dremio University
1

Hello Everyone,

We are using 3 nodes Dremio cluster (2 slaves + 1 master) and we have configured Elasticsearch(v6.8) as one of the data sources.

we have below index mapping in ES,

{ test-alias:{
index-1:{
source:{
type:keyword
}
},
index-2:{
source:{
type:keyword
}
},
index-2:{
source:{
type:keyword
}
}
}

while executing select * from test-alias we get proper results and where as sometimes we face below exception,

(org.apache.calcite.runtime.CalciteContextException) From line 26, column 19 to line 26, column 24: Column ‘source’ not found in any table
sun.reflect.GeneratedConstructorAccessor264.newInstance():-1
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
java.lang.reflect.Constructor.newInstance():423
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.sql.SqlUtil.newContextException():803
org.apache.calcite.sql.SqlUtil.newContextException():788
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4470
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify():253
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit():5332
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit():5314
org.apache.calcite.sql.SqlIdentifier.accept():344
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild():134
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild():101
org.apache.calcite.sql.SqlOperator.acceptCall():859
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped():5367
org.apache.calcite.sql.validate.SqlScopedShuttle.visit():50
org.apache.calcite.sql.validate.SqlScopedShuttle.visit():33
org.apache.calcite.sql.SqlCall.accept():138
org.apache.calcite.sql.validate.SqlValidatorImpl.expand():4921
org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause():3659
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3200
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():943
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():924
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2971
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2956
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3197
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():943
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():924
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2971
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2956
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3197
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():943
org.apache.calcite.sql.validate.AbstractNamespace.getRowType():115
org.apache.calcite.sql.validate.SelectNamespace.getRowType():30
org.apache.calcite.sql.validate.AbstractNamespace.getType():126
org.apache.calcite.sql.validate.SelectNamespace.getType():30
org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeTypeIfKnown():1538
org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeType():1523
org.apache.calcite.sql.validate.SqlValidatorImpl.validateWithItem():3449
org.apache.calcite.sql.validate.WithNamespace.validateImpl():53
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():943
org.apache.calcite.sql.validate.SqlValidatorImpl.validateWith():3433
org.apache.calcite.sql.SqlWith.validate():71
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():899
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():609
com.dremio.exec.planner.sql.SqlConverter.validate():199
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():178
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():167
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():163
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():68
com.dremio.exec.work.foreman.AttemptManager.plan():383
com.dremio.exec.work.foreman.AttemptManager.lambda$run$0():292
com.dremio.service.commandpool.CommandWrapper.run():62
java.util.concurrent.ThreadPoolExecutor.runWorker():1149
java.util.concurrent.ThreadPoolExecutor$Worker.run():624
java.lang.Thread.run():748
Caused By (org.apache.calcite.sql.validate.SqlValidatorException) Column ‘source’ not found in any table
sun.reflect.GeneratedConstructorAccessor263.newInstance():-1
sun.reflect.DelegatingConstructorAccessorImpl.newInstance():45
java.lang.reflect.Constructor.newInstance():423
org.apache.calcite.runtime.Resources$ExInstWithCause.ex():463
org.apache.calcite.runtime.Resources$ExInst.ex():572
org.apache.calcite.sql.SqlUtil.newContextException():803
org.apache.calcite.sql.SqlUtil.newContextException():788
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError():4470
org.apache.calcite.sql.validate.DelegatingScope.fullyQualify():253
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit():5332
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visit():5314
org.apache.calcite.sql.SqlIdentifier.accept():344
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild():134
org.apache.calcite.sql.util.SqlShuttle$CallCopyingArgHandler.visitChild():101
org.apache.calcite.sql.SqlOperator.acceptCall():859
org.apache.calcite.sql.validate.SqlValidatorImpl$Expander.visitScoped():5367
org.apache.calcite.sql.validate.SqlScopedShuttle.visit():50
org.apache.calcite.sql.validate.SqlScopedShuttle.visit():33
org.apache.calcite.sql.SqlCall.accept():138
org.apache.calcite.sql.validate.SqlValidatorImpl.expand():4921
org.apache.calcite.sql.validate.SqlValidatorImpl.validateWhereClause():3659
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3200
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():943
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():924
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2971
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2956
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3197
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():943
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery():924
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2971
org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom():2956
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect():3197
org.apache.calcite.sql.validate.SelectNamespace.validateImpl():60
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():943
org.apache.calcite.sql.validate.AbstractNamespace.getRowType():115
org.apache.calcite.sql.validate.SelectNamespace.getRowType():30
org.apache.calcite.sql.validate.AbstractNamespace.getType():126
org.apache.calcite.sql.validate.SelectNamespace.getType():30
org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeTypeIfKnown():1538
org.apache.calcite.sql.validate.SqlValidatorImpl.getValidatedNodeType():1523
org.apache.calcite.sql.validate.SqlValidatorImpl.validateWithItem():3449
org.apache.calcite.sql.validate.WithNamespace.validateImpl():53
org.apache.calcite.sql.validate.AbstractNamespace.validate():84
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace():943
org.apache.calcite.sql.validate.SqlValidatorImpl.validateWith():3433
org.apache.calcite.sql.SqlWith.validate():71
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression():899
org.apache.calcite.sql.validate.SqlValidatorImpl.validate():609
com.dremio.exec.planner.sql.SqlConverter.validate():199
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateNode():178
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():167
com.dremio.exec.planner.sql.handlers.PrelTransformer.validateAndConvert():163
com.dremio.exec.planner.sql.handlers.query.NormalHandler.getPlan():43
com.dremio.exec.planner.sql.handlers.commands.HandlerToExec.plan():68
com.dremio.exec.work.foreman.AttemptManager.plan():383
com.dremio.exec.work.foreman.AttemptManager.lambda$run$0():292
com.dremio.service.commandpool.CommandWrapper.run():62
java.util.concurrent.ThreadPoolExecutor.runWorker():1149
java.util.concurrent.ThreadPoolExecutor$Worker.run():624
java.lang.Thread.run():748

on rebooting dremio cluster it works for several attempts and after that, it fails with the same exception.

can anyone please help us on this?

2

@murtazahzaveri53

Are you doing a run or preview? Can you please share the query profile?

3

Hi Balaji, thanks for your quick response.

I am doing a run. and above logs are taken from job profile console.

4

@murtazahzaveri53

The above snippet does not show the full issue, want to check the profile and see why Dremio says “Column ‘source’ not found in any table”, in particular want to see if metadata found it, which will be under the planning tab

Thanks
Bali

5

Hi Balaji,

Thanks for your quick response. i have found one interesting point here, when we query to individual indices in apache dremio it gets result with all the columns whereas if we query to alias(pointing to multiple indices) it throws this exception.

note : all the indices containing in aliases has column ‘source’ in it.

6

Thanks for the update @murtazahzaveri53, do send us the complete profile and we will review and get back to you

7

Hi Balaji,

I cant share an actual profile with you as we have few of PHI details in it but I can assure you that all the indices of ES have source fields in it.

Also, the same dremio query works sometimes without any errors.

Under the planning tab, we see below things,
elastic.: Permission_cache_hit(0ms)
elastic.: Permission_cache_hit(0ms)
elastic.: Permission_cached_metadata(0ms)

Non-default options :
[{
kind : LONG,
type : SYSTEM,
name : store.plugin.max_metadata_leaf_columns
num_val:1800
}
]

Job Summary
State : FAILED
co-ordinator : dremio_node_url
Threads : 0
Command pool wait : 0ms
Planning time : still planning

Please let me know incase of any additional details is required.

Thanks,
Murtaza Zaveri

8

@murtazahzaveri53

I believe you that the columns exists, I want to examine the planning information on what Dremio found. Are you able to send the profile to a Dremio email?

Thanks
Bali

9

Hi Balaji,

Kindly find attached dremio profile_attempt.

Many thanks,
Murtaza Zaveriprofile_attempt_0.zip (3.8 KB)

10

Hi Balaji,

Any updates on this?

Please let me know in case any more details are required.

Thanks and regards,
Murtaza Zaveri

11

@murtazahzaveri53

Download the zip file from the Dremio jobs page and send us the zip file as is, this will help us render the profile and see what is going on