search

trinodb / trino

Official repository of Trino, the distributed SQL query engine for big data, formerly known as PrestoSQL (https://trino.io)
https://trino.io
Apache License 2.0
10.4k stars 2.99k forks source link

Support Hive view containing `current_date()`, `current_timestamp()` #5688

Open qianmoQ opened 4 years ago

qianmoQ commented 4 years ago

i'm Integrate Coral with Presto to enable querying hive views has error:

io.prestosql.spi.PrestoException: Failed to translate Hive view 'odv.monitor': Unhandled Hive AST token current_date, tree: 
current_date

    at io.prestosql.plugin.hive.ViewReaderUtil$HiveViewReader.decodeViewData(ViewReaderUtil.java:157)
    at io.prestosql.plugin.hive.HiveMetadata.lambda$getView$60(HiveMetadata.java:1860)
    at java.base/java.util.Optional.map(Optional.java:265)
    at io.prestosql.plugin.hive.HiveMetadata.getView(HiveMetadata.java:1858)
    at io.prestosql.plugin.base.classloader.ClassLoaderSafeConnectorMetadata.getView(ClassLoaderSafeConnectorMetadata.java:505)
    at io.prestosql.metadata.MetadataManager.getView(MetadataManager.java:1069)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitTable(StatementAnalyzer.java:1202)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitTable(StatementAnalyzer.java:322)
    at io.prestosql.sql.tree.Table.accept(Table.java:53)
    at io.prestosql.sql.tree.AstVisitor.process(AstVisitor.java:27)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:339)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.analyzeFrom(StatementAnalyzer.java:2483)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:1526)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:322)
    at io.prestosql.sql.tree.QuerySpecification.accept(QuerySpecification.java:144)
    at io.prestosql.sql.tree.AstVisitor.process(AstVisitor.java:27)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:339)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:349)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:1039)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:322)
    at io.prestosql.sql.tree.Query.accept(Query.java:107)
    at io.prestosql.sql.tree.AstVisitor.process(AstVisitor.java:27)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:339)
    at io.prestosql.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:308)
    at io.prestosql.sql.analyzer.Analyzer.analyze(Analyzer.java:83)
    at io.prestosql.sql.analyzer.Analyzer.analyze(Analyzer.java:75)
    at io.prestosql.execution.SqlQueryExecution.analyze(SqlQueryExecution.java:257)
    at io.prestosql.execution.SqlQueryExecution.<init>(SqlQueryExecution.java:183)
    at io.prestosql.execution.SqlQueryExecution$SqlQueryExecutionFactory.createQueryExecution(SqlQueryExecution.java:759)
    at io.prestosql.dispatcher.LocalDispatchQueryFactory.lambda$createDispatchQuery$0(LocalDispatchQueryFactory.java:123)
    at io.prestosql.$gen.Presto_344_62_gf11ee6e_dirty____20201022_070346_2.call(Unknown Source)
    at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)
    at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:69)
    at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.linkedin.coral.hive.hive2rel.parsetree.UnhandledASTTokenException: Unhandled Hive AST token current_date, tree: 
current_date

    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visit(AbstractASTVisitor.java:265)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.lambda$visitChildren$0(AbstractASTVisitor.java:284)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1654)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visitChildren(AbstractASTVisitor.java:285)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitFunctionInternal(ParseTreeBuilder.java:444)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitFunction(ParseTreeBuilder.java:436)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitFunction(ParseTreeBuilder.java:69)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visit(AbstractASTVisitor.java:121)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.lambda$visitChildren$0(AbstractASTVisitor.java:284)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1654)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visitChildren(AbstractASTVisitor.java:285)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitFunctionInternal(ParseTreeBuilder.java:444)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitFunction(ParseTreeBuilder.java:436)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitFunction(ParseTreeBuilder.java:69)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visit(AbstractASTVisitor.java:121)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.lambda$visitChildren$0(AbstractASTVisitor.java:284)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1654)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visitChildren(AbstractASTVisitor.java:285)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitFunctionInternal(ParseTreeBuilder.java:444)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitFunction(ParseTreeBuilder.java:436)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitFunction(ParseTreeBuilder.java:69)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visit(AbstractASTVisitor.java:121)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.lambda$visitChildren$0(AbstractASTVisitor.java:284)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1654)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visitChildren(AbstractASTVisitor.java:285)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visitChildren(AbstractASTVisitor.java:279)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitBinaryOperator(ParseTreeBuilder.java:390)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitOperator(ParseTreeBuilder.java:376)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitOperator(ParseTreeBuilder.java:69)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visit(AbstractASTVisitor.java:154)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitWhere(ParseTreeBuilder.java:333)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitWhere(ParseTreeBuilder.java:69)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visit(AbstractASTVisitor.java:106)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.lambda$visitChildren$0(AbstractASTVisitor.java:284)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1654)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visitChildren(AbstractASTVisitor.java:285)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visitChildren(AbstractASTVisitor.java:279)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visitInsert(AbstractASTVisitor.java:521)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visit(AbstractASTVisitor.java:82)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.lambda$visitChildren$0(AbstractASTVisitor.java:284)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1654)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visitChildren(AbstractASTVisitor.java:285)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visitChildren(AbstractASTVisitor.java:279)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitQueryNode(ParseTreeBuilder.java:601)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitQueryNode(ParseTreeBuilder.java:69)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visit(AbstractASTVisitor.java:64)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.lambda$visitChildren$0(AbstractASTVisitor.java:284)
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195)
    at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1654)
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484)
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474)
    at java.base/java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913)
    at java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
    at java.base/java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visitChildren(AbstractASTVisitor.java:285)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visitChildren(AbstractASTVisitor.java:279)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitSubquery(ParseTreeBuilder.java:549)
    at com.linkedin.coral.hive.hive2rel.parsetree.ParseTreeBuilder.visitSubquery(ParseTreeBuilder.java:69)
    at com.linkedin.coral.hive.hive2rel.parsetree.AbstractASTVisitor.visit(AbstractASTVisitor.java:48)
    at com.linkedi...</init>

i'm create view script 

CREATE VIEW odv.monitor AS
SELECT
  uid AS uid,
  str AS str,
  ymd AS ymd,
  key AS key
FROM
  (
    select
      cast(
        get_json_object(monitor.str, '$.userid') as bigint
      ) as uid,
      monitor.str,
      monitor.ymd,
      monitor.key
    from
      logs.monitor
    where
      monitor.ymd >= date_format(date_add(current_date(), -7), 'yyyyMMdd')
  ) odv.view_monitor_app
findepi commented 4 years ago

Per @laurachenyu 's https://github.com/prestosql/presto/issues/5669#issuecomment-715571382 view containing current_timestamp is not supported yet either.

wmoustafa commented 3 years ago

We are looking into this. Fix is underway (currently reviewing internal patch).

JamesRTaylor commented 3 years ago

Any progress on this one yet, @wmoustafa?

wmoustafa commented 3 years ago

We are planning to check it in this week. cc: @kxu1026

JamesRTaylor commented 3 years ago

Did this ever get checked in, @wmoustafa?

wmoustafa commented 3 years ago

The patch is here https://github.com/linkedin/coral/pull/41. Sorry for the delay, but a few priorities came up. The patch should be checked in very soon as we reviewed internally.

williesb commented 3 years ago

Is there an ETA on this?

ebyhr commented 3 years ago

I'm checking this locally. Hive view having current_date returns result without any error, but current_timestamp throws below exception. 

trino> select * from hive.default.current_timestamp_hive_view;
Query 20210429_030756_00001_rhtgq failed: line 1:15: View 'hive.default.current_timestamp_hive_view' is stale or in invalid state: column [cd] of type timestamp(3) with time zone projected from query view at position 0 cannot be coerced to column [cd] of type timestamp(3) stored in view definition
io.trino.spi.TrinoException: line 1:15: View 'hive.default.current_timestamp_hive_view' is stale or in invalid state: column [cd] of type timestamp(3) with time zone projected from query view at position 0 cannot be coerced to column [cd] of type timestamp(3) stored in view definition
    at io.trino.sql.analyzer.SemanticExceptions.semanticException(SemanticExceptions.java:48)
    at io.trino.sql.analyzer.SemanticExceptions.semanticException(SemanticExceptions.java:43)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.lambda$createScopeForView$21(StatementAnalyzer.java:1534)
    at java.base/java.util.Optional.ifPresent(Optional.java:183)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.createScopeForView(StatementAnalyzer.java:1534)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.createScopeForView(StatementAnalyzer.java:1495)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitTable(StatementAnalyzer.java:1304)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitTable(StatementAnalyzer.java:366)
    at io.trino.sql.tree.Table.accept(Table.java:53)
    at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:383)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.analyzeFrom(StatementAnalyzer.java:3157)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:1952)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:366)
    at io.trino.sql.tree.QuerySpecification.accept(QuerySpecification.java:155)
    at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:383)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:393)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:1152)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:366)
    at io.trino.sql.tree.Query.accept(Query.java:107)
    at io.trino.sql.tree.AstVisitor.process(AstVisitor.java:27)
    at io.trino.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:383)
    at io.trino.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:346)
    at io.trino.sql.analyzer.Analyzer.analyze(Analyzer.java:91)
    at io.trino.sql.analyzer.Analyzer.analyze(Analyzer.java:83)
    at io.trino.execution.SqlQueryExecution.analyze(SqlQueryExecution.java:267)
    at io.trino.execution.SqlQueryExecution.<init>(SqlQueryExecution.java:190)
    at io.trino.execution.SqlQueryExecution$SqlQueryExecutionFactory.createQueryExecution(SqlQueryExecution.java:800)
    at io.trino.dispatcher.LocalDispatchQueryFactory.lambda$createDispatchQuery$0(LocalDispatchQueryFactory.java:129)
    at io.trino.$gen.Trino_dev____20210429_030557_2.call(Unknown Source)
    at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)
    at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:69)
    at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)
findepi commented 3 years ago

The patch is here linkedin/coral#41. I'm checking this locally. Hive view having current_date returns result without any error, but current_timestamp throws below exception.

Note that "View ... is stale or in invalid state" is Trino's StatementAnalyzer's logic and cannot be effectively test-covered on Coral side alone. That's why i consider this imperative that we have end-to-end tests on the Trino side, additionally to unit tests in Coral.