SQL statement referencing Datalake table returns error failing to initiate an instance of HiveMetaStoreClient

References to a Datalake table requires accessing a Hive Metastore (HMS). If the Metastore is not accessible, an error can occur as described below.

Symptoms

The response from invoking the statement will look like this:
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL5105N  The statement failed because a Big SQL component encountered an
error.  Component receiving the error: "DDL".  Component returning the error:
"HIVE".  Log entry identifier: "[BSL-0-3419468eb]".  Reason: ""Unable to
instantiate org.a".  SQLSTATE=58040
Looking at the relevant bigsql log message in detail:
2023-10-18T06:58:47,147 WARN com.ibm.biginsights.catalog.translator.hive.HiveExceptionTranslator [Thread-1] : [BSL-0-3419468eb]: The statement failed because a Hadoop component encountered an error. Component receiving the error: "DDL". Component returning the error: "HIVE". Hadoop log identifier: "[BSL-0-3419468e5]'. Reason: "Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient".
com.ibm.biginsights.catalog.BIException: The statement failed because a Hadoop component encountered an error. Component receiving the error: "DDL". Component returning the error: "HIVE". Hadoop log identifier: "[BSL-0-3419468e5]'. Reason: "Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient".
        at sun.reflect.GeneratedConstructorAccessor8.newInstance(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:57) ~[?:1.8.0]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:437) ~[?:1.8.0]
        at com.ibm.biginsights.catalog.translator.hive.HiveExceptionTranslator.newGenericException(HiveExceptionTranslator.java:202) ~[commoncatalog.jar:?]
        at com.ibm.biginsights.catalog.translator.hive.HiveExceptionTranslator.newGenericException(HiveExceptionTranslator.java:166) ~[commoncatalog.jar:?]
        at com.ibm.biginsights.catalog.translator.hive.HiveExceptionTranslator.translate(HiveExceptionTranslator.java:99) ~[commoncatalog.jar:?]
        at com.ibm.biginsights.biga.udf.MetastorePool.get(MetastorePool.java:494) ~[bigsql-udf.jar:?]
        at com.ibm.biginsights.biga.udf.MetastorePool.get(MetastorePool.java:422) ~[bigsql-udf.jar:?]
        at com.ibm.biginsights.biga.udf.UdfTools.getClientFromMSPool(UdfTools.java:1120) ~[bigsql-udf.jar:?]
        at com.ibm.biginsights.biga.udf.BIGSQL_DDL.doDDLStatement(BIGSQL_DDL.java:983) ~[bigsql-udf.jar:?]
        at com.ibm.biginsights.biga.udf.BIGSQL_DDL.processDDL(BIGSQL_DDL.java:314) ~[bigsql-udf.jar:?]
Caused by: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient
        at org.apache.hadoop.hive.metastore.utils.JavaUtils.newInstance(JavaUtils.java:86) ~[hive-exec-4.0.0-alpha-2-13-IBM.jar:4.0.0-alpha-2]
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:101) ~[hive-exec-4.0.0-alpha-2-13-IBM.jar:4.0.0-alpha-2]
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:154) ~[hive-exec-4.0.0-alpha-2-13-IBM.jar:4.0.0-alpha-2]
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:110) ~[hive-exec-4.0.0-alpha-2-13-IBM.jar:4.0.0-alpha-2]
        at com.ibm.biginsights.biga.udf.MetastorePool.get(MetastorePool.java:475) ~[bigsql-udf.jar:?]
        ... 4 more
Caused by: java.lang.reflect.InvocationTargetException
        at sun.reflect.GeneratedConstructorAccessor6.newInstance(Unknown Source) ~[?:?]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:57) ~[?:1.8.0]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:437) ~[?:1.8.0]
        at org.apache.hadoop.hive.metastore.utils.JavaUtils.newInstance(JavaUtils.java:84) ~[hive-exec-4.0.0-alpha-2-13-IBM.jar:4.0.0-alpha-2]
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:101) ~[hive-exec-4.0.0-alpha-2-13-IBM.jar:4.0.0-alpha-2]
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:154) ~[hive-exec-4.0.0-alpha-2-13-IBM.jar:4.0.0-alpha-2]
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:110) ~[hive-exec-4.0.0-alpha-2-13-IBM.jar:4.0.0-alpha-2]
        at com.ibm.biginsights.biga.udf.MetastorePool.get(MetastorePool.java:475) ~[bigsql-udf.jar:?]
        ... 4 more
Caused by: org.apache.hadoop.hive.metastore.api.MetaException: Could not connect to meta store using any of the URIs provided. Most recent failure: org.apache.thrift.transport.TTransportException: java.net.ConnectException: Connection refused (Connection refused)
        at org.apache.thrift.transport.TSocket.open(TSocket.java:243)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.open(HiveMetaStoreClient.java:778)
        at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:277)
        at sun.reflect.GeneratedConstructorAccessor6.newInstance(Unknown Source)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:57)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:437)
        at org.apache.hadoop.hive.metastore.utils.JavaUtils.newInstance(JavaUtils.java:84)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:101)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:154)
        at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:110)
        at com.ibm.biginsights.biga.udf.MetastorePool.get(MetastorePool.java:475)
        at com.ibm.biginsights.biga.udf.MetastorePool.get(MetastorePool.java:422)
        at com.ibm.biginsights.biga.udf.UdfTools.getClientFromMSPool(UdfTools.java:1120)
        at com.ibm.biginsights.biga.udf.BIGSQL_DDL.doDDLStatement(BIGSQL_DDL.java:983)
        at com.ibm.biginsights.biga.udf.BIGSQL_DDL.processDDL(BIGSQL_DDL.java:314)

Causes

Invoking a SQL statement referencing a Datalake table requires access the hive metastore to determine table metadata. This exception is thrown when we attempt to make a connection to the Hive metastore and it is not accessible.

Resolving the problem

The solution is to restart the Big SQL scheduler, which will restart the Hive Metastore also as follows:

bigsql start -scheduler