Presto select query fails on Hive ACID Table

[#1]

Hello Experts,

I am using presto (v 0.194) on AWS EMR (v 5.14.0) as querying layer.

Data is stored in Hadoop data nodes.

Issue : When querying Hive table with ACID property enabled, Presto query fails with below error ( attached document presto-query-errors.txt has full errors)

select * from default.poc_date_bucket limit 10

An error occurred while calling o163.next. : java.sql.SQLException: Query failed (#20180709_164933_00004_hgb6d): Hive table 'default.poc_date_bucket' is corrupt. Found sub-directory in bucket directory for partition: <UNPARTITIONED> at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1798) at 

Creating a new table with partitioning and bucketing enabled, query fails with similar error

select * from default.poc_date_partition limit 10

An error occurred while calling o169.next. : java.sql.SQLException: Query failed (#20180709_174041_00005_hgb6d): Hive table 'default.poc_date_partition' is corrupt. Found sub-directory in bucket directory for partition: year_start_date=2019-01-01 at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1798) at

Bucketing is required when enabling ACID property on a Hive table.

Has anyone encountered this issue? Appreciate any suggestions

Thanks again

[#2 · Accepted solution]

Hey @cskbhatt! When you use hive transactional table it seems to be an unsupported feature by presto.
https://github.com/prestodb/presto/issues/1970

https://github.com/prestodb/presto/issues/6666

Hope this helps!

[#3]

Thank you @Vinicius Higa Murakami for sharing the issues.

I am exploring alternatives to presto, for querying star schemas (built on hive ACID tables)

[#4]

So Presto now supports ACID tables, but only for Hive3. However, the subdirectory exception is from a configuration on the presto client side. In the hive.properties in presto's catalog directory, add "hive.recursive-directories=true"