Does presto supports querying Hive tables with underlying files in Avro format

[Phaneendra Peddi]

Does presto supports querying Hive tables with underlying files in Avro format

I create an external table as below.

CREATE EXTERNAL TABLE table_avro
ROW FORMAT
SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
WITH SERDEPROPERTIES (
    'avro.schema.literal'='{
      "namespace": "example.dwpoc",
      "name": "table_avro",
      "type": "record",
      "fields": [
       .
       .
      ]
    }')
STORED AS
INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
LOCATION 's3://XXXXXXX/table_avro/';

insert into table table_avro from select * from table;

When i run some queries on this table form presto i am facing some issues. FYI.. i am able to run queries on this table from Hive.

-- Count(*) is failing with below error
       
presto:default> select count(*) from table_avro;

Query 20160222_131613_00025_8z3eq, FAILED, 2 nodes
http://ip-10-0-0-18.ec2.internal:8889/v1/query/20160222_131613_00025_8z3eq?pretty
Splits: 2 total, 0 done (0.00%)
CPU Time: 0.0s total,     0 rows/s,     0B/s, 40% active
Per Node: 0.0 parallelism,     0 rows/s,     0B/s
Parallelism: 0.0
0:00 [0 rows, 0B] [0 rows/s, 0B/s]

Query 20160222_131613_00025_8z3eq failed: org/apache/avro/io/DatumReader
java.lang.NoClassDefFoundError: org/apache/avro/io/DatumReader
        at org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat.getRecordReader(AvroContainerInputFormat.java:51)
        at com.facebook.presto.hive.HiveUtil.lambda$createRecordReader$2(HiveUtil.java:160)
        at com.facebook.presto.hive.RetryDriver.run(RetryDriver.java:136)
        at com.facebook.presto.hive.HiveUtil.createRecordReader(HiveUtil.java:160)
        at com.facebook.presto.hive.GenericHiveRecordCursorProvider.createHiveRecordCursor(GenericHiveRecordCursorProvider.java:47)
        at com.facebook.presto.hive.HivePageSourceProvider.getHiveRecordCursor(HivePageSourceProvider.java:128)
        at com.facebook.presto.hive.HivePageSourceProvider.createPageSource(HivePageSourceProvider.java:106)
        at com.facebook.presto.spi.classloader.ClassLoaderSafeConnectorPageSourceProvider.createPageSource(ClassLoaderSafeConnectorPageSourceProvider.java:43)
        at com.facebook.presto.split.PageSourceManager.createPageSource(PageSourceManager.java:48)
        at com.facebook.presto.operator.TableScanOperator.createSourceIfNecessary(TableScanOperator.java:268)
        at com.facebook.presto.operator.TableScanOperator.isFinished(TableScanOperator.java:210)
        at com.facebook.presto.operator.Driver.processInternal(Driver.java:377)
        at com.facebook.presto.operator.Driver.processFor(Driver.java:303)
        at com.facebook.presto.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:573)
        at com.facebook.presto.execution.TaskExecutor$PrioritizedSplitRunner.process(TaskExecutor.java:505)
        at com.facebook.presto.execution.TaskExecutor$Runner.run(TaskExecutor.java:640)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.ClassNotFoundException: org.apache.avro.io.DatumReader
        at com.facebook.presto.server.PluginClassLoader.loadClass(PluginClassLoader.java:106)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
        ... 19 more
       
-- Select with limit failing with below error

presto:default> select * from table_avro limit 10;
Query 20160222_131638_00027_8z3eq failed: line 1:8: SELECT * not allowed in queries without FROM clause
com.facebook.presto.sql.analyzer.SemanticException: line 1:8: SELECT * not allowed in queries without FROM clause
        at com.facebook.presto.sql.analyzer.StatementAnalyzer.analyzeSelect(StatementAnalyzer.java:1548)
        at com.facebook.presto.sql.analyzer.StatementAnalyzer.visitQuerySpecification(StatementAnalyzer.java:939)
        at com.facebook.presto.sql.analyzer.StatementAnalyzer.visitQuerySpecification(StatementAnalyzer.java:209)
        at com.facebook.presto.sql.tree.QuerySpecification.accept(QuerySpecification.java:125)
        at com.facebook.presto.sql.tree.AstVisitor.process(AstVisitor.java:22)
        at com.facebook.presto.sql.analyzer.StatementAnalyzer.visitQuery(StatementAnalyzer.java:735)
        at com.facebook.presto.sql.analyzer.StatementAnalyzer.visitQuery(StatementAnalyzer.java:209)
        at com.facebook.presto.sql.tree.Query.accept(Query.java:103)
        at com.facebook.presto.sql.tree.AstVisitor.process(AstVisitor.java:22)
        at com.facebook.presto.sql.analyzer.Analyzer.analyze(Analyzer.java:60)
        at com.facebook.presto.execution.SqlQueryExecution.doAnalyzeQuery(SqlQueryExecution.java:254)
        at com.facebook.presto.execution.SqlQueryExecution.analyzeQuery(SqlQueryExecution.java:240)
        at com.facebook.presto.execution.SqlQueryExecution.start(SqlQueryExecution.java:204)
        at com.facebook.presto.execution.QueuedExecution.lambda$start$282(QueuedExecution.java:68)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at java.lang.Thread.run(Thread.java:745)       
       
-- describe table shows nothing

presto:default> desc table_avro;
 Column | Type | Null | Partition Key | Comment
--------+------+------+---------------+---------
(0 rows)

-- but it is showing me when i run show tables..

presto:default> show tables;
                 Table
---------------------------------------
 table_avro

Can some one help me in this...

[Kamil Bajda-Pawlikowski]

No built-in support yet but try this: https://groups.google.com/forum/#!searchin/presto-users/avro/presto-users/D4pjOqhoHYo/k8zHHnYICwAJ