Connect Trino to a local S3 with bucket at root path

[crtvmn]

Hello, I'm trying to connect Trino to a local S3 in a Kubernetes cluster. The connection works with Hive 4.0.1 but when I try to read data in my S3 cluster (located at the S3 root path in the /test/ bucket), I got the following error:

[trino@trino-coordinator-bd5c6ccc6-7kvnp /]$ trino --debug
trino> CREATE TABLE hive.default.test (
    ->         name varchar,
    ->         description varchar,
    ->         address varchar,
    ->         job varchar
    ->     )
    ->     WITH (
    ->         external_location = 's3a://test/',
    ->         format = 'JSON'
    ->     );
Query 20241112_194620_00158_v92x3 failed: test location must not be root path
io.trino.spi.TrinoException: test location must not be root path
  at io.trino.plugin.hive.metastore.thrift.ThriftHiveMetastore.createTable(ThriftHiveMetastore.java:869)
  at io.trino.plugin.hive.metastore.thrift.BridgingHiveMetastore.createTable(BridgingHiveMetastore.java:217)
  at io.trino.metastore.tracing.TracingHiveMetastore.lambda$createTable$13(TracingHiveMetastore.java:210)
  at io.trino.metastore.tracing.Tracing.lambda$withTracing$0(Tracing.java:31)
  at io.trino.metastore.tracing.Tracing.withTracing(Tracing.java:39)
  at io.trino.metastore.tracing.Tracing.withTracing(Tracing.java:30)
  at io.trino.metastore.tracing.TracingHiveMetastore.createTable(TracingHiveMetastore.java:210)
  at io.trino.plugin.hive.metastore.cache.CachingHiveMetastore.createTable(CachingHiveMetastore.java:623)
  at io.trino.plugin.hive.metastore.cache.CachingHiveMetastore.createTable(CachingHiveMetastore.java:623)
  at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore$CreateTableOperation.run(SemiTransactionalHiveMetastore.java:2967)
  at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore$Committer.executeAddTableOperations(SemiTransactionalHiveMetastore.java:2076)
  at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore.commitShared(SemiTransactionalHiveMetastore.java:1517)
  at io.trino.plugin.hive.metastore.SemiTransactionalHiveMetastore.commit(SemiTransactionalHiveMetastore.java:1257)
  at io.trino.plugin.hive.HiveMetadata.commit(HiveMetadata.java:3904)
  at io.trino.plugin.hive.HiveTransactionManager.lambda$commit$0(HiveTransactionManager.java:58)
  at java.base/java.util.Optional.ifPresent(Optional.java:178)
  at io.trino.plugin.hive.HiveTransactionManager.commit(HiveTransactionManager.java:56)
  at io.trino.plugin.hive.HiveConnector.commit(HiveConnector.java:227)
  at io.trino.metadata.CatalogTransaction.commit(CatalogTransaction.java:86)
  at io.trino.metadata.CatalogMetadata.commit(CatalogMetadata.java:153)
  at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
  at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:131)
  at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:76)
  at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:82)
  at io.airlift.concurrent.BoundedExecutor.drainQueue(BoundedExecutor.java:79)
  at io.trino.$gen.Trino_464____20241112_165624_2.run(Unknown Source)
  at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
  at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
  at java.base/java.lang.Thread.run(Thread.java:1575)
Caused by: io.trino.hive.thrift.metastore.InvalidObjectException: test location must not be root path
  at io.trino.hive.thrift.metastore.ThriftHiveMetastore$create_table_result$create_table_resultStandardScheme.read(ThriftHiveMetastore.java:60697)
  at io.trino.hive.thrift.metastore.ThriftHiveMetastore$create_table_result$create_table_resultStandardScheme.read(ThriftHiveMetastore.java:60673)
  at io.trino.hive.thrift.metastore.ThriftHiveMetastore$create_table_result.read(ThriftHiveMetastore.java:60598)
  at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:93)
  at io.trino.hive.thrift.metastore.ThriftHiveMetastore$Client.recvCreateTable(ThriftHiveMetastore.java:1535)
  at io.trino.hive.thrift.metastore.ThriftHiveMetastore$Client.createTable(ThriftHiveMetastore.java:1522)
  at io.trino.plugin.hive.metastore.thrift.ThriftHiveMetastoreClient.createTable(ThriftHiveMetastoreClient.java:240)
  at io.trino.plugin.hive.metastore.thrift.FailureAwareThriftMetastoreClient.lambda$createTable$5(FailureAwareThriftMetastoreClient.java:120)
  at io.trino.plugin.hive.metastore.thrift.FailureAwareThriftMetastoreClient.runWithHandle(FailureAwareThriftMetastoreClient.java:476)
  at io.trino.plugin.hive.metastore.thrift.FailureAwareThriftMetastoreClient.createTable(FailureAwareThriftMetastoreClient.java:120)
  at io.trino.plugin.hive.metastore.thrift.ThriftHiveMetastore.lambda$createTable$31(ThriftHiveMetastore.java:846)
  at io.trino.plugin.hive.metastore.thrift.ThriftMetastoreApiStats.lambda$wrap$0(ThriftMetastoreApiStats.java:41)
  at io.trino.plugin.hive.util.RetryDriver.run(RetryDriver.java:117)
  at io.trino.plugin.hive.metastore.thrift.ThriftHiveMetastore.createTable(ThriftHiveMetastore.java:844)
  ... 28 more

I didn't have this error with Hive 3.x.

I found this issue in the Hive Jira https://issues.apache.org/jira/plugins/servlet/mobile#issue/HIVE-25912 (and Github changes apache/hive#3009) but we can not contact them with "discussion" or "issue" in their Github repository.

I'm asking if you have any information on this problem or maybe an alternative with Hive or another component.

Thank you for your help!

[hashhar]

the limitation seems to come from HMS so nothing to be done on Trino's end. Please file a Jira with the Hive project.

It's stupid IMO. Instead of fixing drop table to not throw NPE they decided to disallow creating tables at root of location?