[DRILL-6923] Show schemas uses default(user defined) schema first for resolving table from information_schema

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 1.14.0
• Component/s: Storage - Hive
• Assignee: Igor Guzenko
• Reporter: Igor Guzenko
• Created: 21/Dec/18 17:34
• Updated: 04/Nov/19 12:48

Description

Show tables tries to find table `information_schema`.`schemata` in default (user defined) schema, and after failed attempt it resolves table successfully against root schema. Please check description below for details explained using example with hive plugin. 

Abstract 

When Drill used with enabled Hive SQL Standard authorization, execution of queries like,

USE hive.db_general;
SHOW SCHEMAS LIKE 'hive.%'; 

results in error DrillRuntimeException: Failed to use the Hive authorization components: Error getting object from metastore for Object [type=TABLE_OR_VIEW, name=db_general.information_schema] . 

Details 

Consider showSchemas() test similar to one defined in TestSqlStdBasedAuthorization : 

@Test
public void showSchemas() throws Exception {
  test("USE " + hivePluginName + "." + db_general);
  testBuilder()
      .sqlQuery("SHOW SCHEMAS LIKE 'hive.%'")
      .unOrdered()
      .baselineColumns("SCHEMA_NAME")
      .baselineValues("hive.db_general")
      .baselineValues("hive.default")
      .go();
}

Currently execution of such test will produce following stacktrace: 

Caused by: org.apache.drill.common.exceptions.DrillRuntimeException: Failed to use the Hive authorization components: Error getting object from metastore for Object [type=TABLE_OR_VIEW, name=db_general.information_schema]
at org.apache.drill.exec.store.hive.HiveAuthorizationHelper.authorize(HiveAuthorizationHelper.java:149)
at org.apache.drill.exec.store.hive.HiveAuthorizationHelper.authorizeReadTable(HiveAuthorizationHelper.java:134)
at org.apache.drill.exec.store.hive.DrillHiveMetaStoreClient$HiveClientWithAuthzWithCaching.getHiveReadEntry(DrillHiveMetaStoreClient.java:450)
at org.apache.drill.exec.store.hive.schema.HiveSchemaFactory$HiveSchema.getSelectionBaseOnName(HiveSchemaFactory.java:233)
at org.apache.drill.exec.store.hive.schema.HiveSchemaFactory$HiveSchema.getDrillTable(HiveSchemaFactory.java:214)
at org.apache.drill.exec.store.hive.schema.HiveDatabaseSchema.getTable(HiveDatabaseSchema.java:63)
at org.apache.calcite.jdbc.SimpleCalciteSchema.getImplicitTable(SimpleCalciteSchema.java:83)
at org.apache.calcite.jdbc.CalciteSchema.getTable(CalciteSchema.java:288)
at org.apache.calcite.sql.validate.EmptyScope.resolve_(EmptyScope.java:143)
at org.apache.calcite.sql.validate.EmptyScope.resolveTable(EmptyScope.java:99)
at org.apache.calcite.sql.validate.DelegatingScope.resolveTable(DelegatingScope.java:203)
at org.apache.calcite.sql.validate.IdentifierNamespace.resolveImpl(IdentifierNamespace.java:105)
at org.apache.calcite.sql.validate.IdentifierNamespace.validateImpl(IdentifierNamespace.java:177)
at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:967)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:943)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3032)
at org.apache.drill.exec.planner.sql.SqlConverter$DrillValidator.validateFrom(SqlConverter.java:274)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateFrom(SqlValidatorImpl.java:3014)
at org.apache.drill.exec.planner.sql.SqlConverter$DrillValidator.validateFrom(SqlConverter.java:274)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3284)
at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:967)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:943)
at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:225)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:918)
at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:628)
at org.apache.drill.exec.planner.sql.SqlConverter.validate(SqlConverter.java:192)
at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateNode(DefaultSqlHandler.java:664)
at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.validateAndConvert(DefaultSqlHandler.java:200)
at org.apache.drill.exec.planner.sql.handlers.DefaultSqlHandler.getPlan(DefaultSqlHandler.java:173)
at org.apache.drill.exec.planner.sql.DrillSqlWorker.getQueryPlan(DrillSqlWorker.java:155)
at org.apache.drill.exec.planner.sql.DrillSqlWorker.getPlan(DrillSqlWorker.java:90)
at org.apache.drill.exec.work.foreman.Foreman.runSQL(Foreman.java:584)
at org.apache.drill.exec.work.foreman.Foreman.run(Foreman.java:272)
at .......(:0)
Caused by: org.apache.hadoop.hive.ql.security.authorization.plugin.HiveAuthzPluginException: Error getting object from metastore for Object [type=TABLE_OR_VIEW, name=db_general.information_schema]
at org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLAuthorizationUtils.throwGetObjErr(SQLAuthorizationUtils.java:310)
at org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLAuthorizationUtils.isOwner(SQLAuthorizationUtils.java:272)
at org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLAuthorizationUtils.getPrivilegesFromMetaStore(SQLAuthorizationUtils.java:212)
at org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizationValidator.checkPrivileges(SQLStdHiveAuthorizationValidator.java:131)
at org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizationValidator.checkPrivileges(SQLStdHiveAuthorizationValidator.java:83)
at org.apache.hadoop.hive.ql.security.authorization.plugin.HiveAuthorizerImpl.checkPrivileges(HiveAuthorizerImpl.java:87)
at org.apache.drill.exec.store.hive.HiveAuthorizationHelper.authorize(HiveAuthorizationHelper.java:145)
... 36 more
Caused by: NoSuchObjectException(message:db_general.information_schema table not found)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$get_table_req_result$get_table_req_resultStandardScheme.read(ThriftHiveMetastore.java:55064)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$get_table_req_result$get_table_req_resultStandardScheme.read(ThriftHiveMetastore.java:55032)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$get_table_req_result.read(ThriftHiveMetastore.java:54963)
at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:86)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.recv_get_table_req(ThriftHiveMetastore.java:1563)
at org.apache.hadoop.hive.metastore.api.ThriftHiveMetastore$Client.get_table_req(ThriftHiveMetastore.java:1550)
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.getTable(HiveMetaStoreClient.java:1344)
at org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLAuthorizationUtils.isOwner(SQLAuthorizationUtils.java:269)
... 41 more

After analyzing of the stack trace you may notice that Drill tried to find table with name `information_schema` in hive.db_general schema. This `information_schema` appeared after call to ShowSchemasHandler.rewrite(sqlNode) method , where our original query

SHOW SCHEMAS LIKE 'hive.%' 

 was rewritten to 

SELECT `SCHEMA_NAME`
FROM `information_schema`.`SCHEMATA`
WHERE LOWER(`SCHEMA_NAME`) LIKE 'hive.%'

 After rewriting Drill initiates validation process mostly performed by Calcite. After some chain of validation method calls, performed validation of from section which results into call to Calcite's EmptyScope.resolveTable(...) method. This method tries to resolve table using following order: Look in the default schema, then default catalog, then root schema. Since, before execution of SHOW SCHEMAS default schema was set to hive.db_general , Calcite assumes that `information_schema` is table name and tries to find it using  hive.db_general schema instance. Usually this search mechanism works fine because this first attempt to find table in default schema results into null, after that Calcite tries to resolve path ["information_schema", "SCHEMATA"]  against root schema, which is like an empty string, schema name becomes information_schema and table name is schemata. Then as expected schema and table resolved successfully. 

Please notice, that this issue isn't specific to only hive storage plugin, there is high probability that under similar conditions it may be reproduced with other storage plugins. 

As a possible solution, we need to change table resolution to always search against root schema and ignore default schema selected by user when search performed for information_schema table. 

Issue Links

• relates to: DRILL-540 Allow querying hive views in Drill