You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@flink.apache.org by Arujit Pradhan <ar...@gojek.com> on 2019/11/21 08:16:34 UTC
Compound Time interval in SQL queries
Hi all,
Is there a way to define a compound time interval(that can consist of both
HOUR and MINUTE) in windows in a Flink SQL query.
For example, we want to do something like this:
SELECT count(1) AS event_count ,
TUMBLE_END(rowtime,
INTERVAL '7' HOUR
AND '30' MINUTE) AS window_timestamp
FROM `data_stream`
GROUP BY TUMBLE ( rowtime, INTERVAL '7' HOUR '30' MINUTE )
We can not even convert this to Minutes as we are getting this error :
*Interval field value <xxx> exceeds precision of MINUTE(2) field*
We were going through Calcite documentation and could not find any
workaround on this.
Thanks and regards,
arujit
回复：Compound Time interval in SQL queries
Posted by "贺小令(晓令)" <xi...@alibaba-inc.com>.
hi arujit,
blink planner with flink-1.9 supports this query.
the reason is both planners do not support complex expressions like INTERVAL '7' HOUR + INTERVAL '30' MINUTE when transforming window to LogicalWindowAggregate node now.
why blink planner supports this query?
the optimization order between two planners are different, Flink planner (a.k.a. old planner) will transform window to LogicalWindowAggregate node first, and then simplify the constant expressions (like INTERVAL '7' HOUR + INTERVAL '30' MINUTE, which could be simplified to 27000000:INTERVAL HOUR TO MINUTE). While blink planner's approach is just the opposite. (simplify expression first, and then transform window).
so, you could try blink planner.
thanks,
godfrey
------------------------------------------------------------------
发件人：Arujit Pradhan <ar...@gojek.com>
发送时间：2019年11月21日(星期四) 17:31
收件人：贺小令(晓令) <xi...@alibaba-inc.com>
主 题：Re: Compound Time interval in SQL queries
Hi, godfrey,
We are using flink-1.6.2. But when working with flink-1.9 I am still getting this error.
Exception in thread "main" org.apache.flink.client.program.ProgramInvocationException: org.apache.flink.table.api.TableException: Only constant window intervals with millisecond resolution are supported.
Thanks and regards,
arujit
On Thu, Nov 21, 2019 at 2:53 PM 贺小令(晓令) <xi...@alibaba-inc.com> wrote:
hi arujit,
Which Flink version are you using?
thanks,
godfrey
------------------------------------------------------------------
发件人：Arujit Pradhan <ar...@gojek.com>
发送时间：2019年11月21日(星期四) 17:21
收件人：贺小令(晓令) <xi...@alibaba-inc.com>; user <us...@flink.apache.org>
主 题：Re: Compound Time interval in SQL queries
Hi, godfrey,
Thanks for your reply. But now I am getting this error :
Exception in thread "main" org.apache.flink.client.program.ProgramInvocationException: org.apache.flink.table.api.TableException: Only constant window descriptors are supported.
at com.gojek.daggers.KafkaProtoSQLProcessor.main(KafkaProtoSQLProcessor.java:30)
Caused by: org.apache.flink.table.api.TableException: Only constant window descriptors are supported.
at org.apache.flink.table.api.TableException$.apply(exceptions.scala:57)
at org.apache.flink.table.plan.rules.datastream.DataStreamLogicalWindowAggregateRule.getOperandAsLong$1(DataStreamLogicalWindowAggregateRule.scala:72)
at org.apache.flink.table.plan.rules.datastream.DataStreamLogicalWindowAggregateRule.translateWindowExpression(DataStreamLogicalWindowAggregateRule.scala:88)
at org.apache.flink.table.plan.rules.common.LogicalWindowAggregateRule.onMatch(LogicalWindowAggregateRule.scala:65)
at org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:317)
at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:556)
at org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:415)
at org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:252)
at org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:127)
Any reason why this may be happening.
Thanks and regards,
arujit
On Thu, Nov 21, 2019 at 2:37 PM 贺小令(晓令) <xi...@alibaba-inc.com> wrote:
please try this approach: interval + interval
like this:
SELECT count(1) AS event_count ,
TUMBLE_END(rowtime, INTERVAL '7' HOUR + INTERVAL '30' MINUTE) AS window_timestamp
FROM `data_stream`
GROUP BY TUMBLE ( rowtime, INTERVAL '7' HOUR + INTERVAL '30' MINUTE)
thanks,
godfrey
------------------------------------------------------------------
发件人：Arujit Pradhan <ar...@gojek.com>
发送时间：2019年11月21日(星期四) 16:23
收件人：user <us...@flink.apache.org>
主 题：Compound Time interval in SQL queries
Hi all,
Is there a way to define a compound time interval(that can consist of both HOUR and MINUTE) in windows in a Flink SQL query.
For example, we want to do something like this:
SELECT count(1) AS event_count ,
TUMBLE_END(rowtime,
INTERVAL '7' HOUR
AND '30' MINUTE) AS window_timestamp
FROM `data_stream`
GROUP BY TUMBLE ( rowtime, INTERVAL '7' HOUR '30' MINUTE )
We can not even convert this to Minutes as we are getting this error :
Interval field value <xxx> exceeds precision of MINUTE(2) field
We were going through Calcite documentation and could not find any workaround on this.
Thanks and regards,
arujit
回复：Compound Time interval in SQL queries
Posted by "贺小令(晓令)" <xi...@alibaba-inc.com>.
hi arujit,
Which Flink version are you using?
thanks,
godfrey
------------------------------------------------------------------
发件人：Arujit Pradhan <ar...@gojek.com>
发送时间：2019年11月21日(星期四) 17:21
收件人：贺小令(晓令) <xi...@alibaba-inc.com>; user <us...@flink.apache.org>
主 题：Re: Compound Time interval in SQL queries
Hi, godfrey,
Thanks for your reply. But now I am getting this error :
Exception in thread "main" org.apache.flink.client.program.ProgramInvocationException: org.apache.flink.table.api.TableException: Only constant window descriptors are supported.
at com.gojek.daggers.KafkaProtoSQLProcessor.main(KafkaProtoSQLProcessor.java:30)
Caused by: org.apache.flink.table.api.TableException: Only constant window descriptors are supported.
at org.apache.flink.table.api.TableException$.apply(exceptions.scala:57)
at org.apache.flink.table.plan.rules.datastream.DataStreamLogicalWindowAggregateRule.getOperandAsLong$1(DataStreamLogicalWindowAggregateRule.scala:72)
at org.apache.flink.table.plan.rules.datastream.DataStreamLogicalWindowAggregateRule.translateWindowExpression(DataStreamLogicalWindowAggregateRule.scala:88)
at org.apache.flink.table.plan.rules.common.LogicalWindowAggregateRule.onMatch(LogicalWindowAggregateRule.scala:65)
at org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:317)
at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:556)
at org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:415)
at org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:252)
at org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:127)
Any reason why this may be happening.
Thanks and regards,
arujit
On Thu, Nov 21, 2019 at 2:37 PM 贺小令(晓令) <xi...@alibaba-inc.com> wrote:
please try this approach: interval + interval
like this:
SELECT count(1) AS event_count ,
TUMBLE_END(rowtime, INTERVAL '7' HOUR + INTERVAL '30' MINUTE) AS window_timestamp
FROM `data_stream`
GROUP BY TUMBLE ( rowtime, INTERVAL '7' HOUR + INTERVAL '30' MINUTE)
thanks,
godfrey
------------------------------------------------------------------
发件人：Arujit Pradhan <ar...@gojek.com>
发送时间：2019年11月21日(星期四) 16:23
收件人：user <us...@flink.apache.org>
主 题：Compound Time interval in SQL queries
Hi all,
Is there a way to define a compound time interval(that can consist of both HOUR and MINUTE) in windows in a Flink SQL query.
For example, we want to do something like this:
SELECT count(1) AS event_count ,
TUMBLE_END(rowtime,
INTERVAL '7' HOUR
AND '30' MINUTE) AS window_timestamp
FROM `data_stream`
GROUP BY TUMBLE ( rowtime, INTERVAL '7' HOUR '30' MINUTE )
We can not even convert this to Minutes as we are getting this error :
Interval field value <xxx> exceeds precision of MINUTE(2) field
We were going through Calcite documentation and could not find any workaround on this.
Thanks and regards,
arujit
Re: Compound Time interval in SQL queries
Posted by Arujit Pradhan <ar...@gojek.com>.
Hi, godfrey,
Thanks for your reply. But now I am getting this error :
*Exception in thread "main"
org.apache.flink.client.program.ProgramInvocationException:
org.apache.flink.table.api.TableException: Only constant window descriptors
are supported. at
com.gojek.daggers.KafkaProtoSQLProcessor.main(KafkaProtoSQLProcessor.java:30)Caused
by: org.apache.flink.table.api.TableException: Only constant window
descriptors are supported. at
org.apache.flink.table.api.TableException$.apply(exceptions.scala:57)
at
org.apache.flink.table.plan.rules.datastream.DataStreamLogicalWindowAggregateRule.getOperandAsLong$1(DataStreamLogicalWindowAggregateRule.scala:72)
at
org.apache.flink.table.plan.rules.datastream.DataStreamLogicalWindowAggregateRule.translateWindowExpression(DataStreamLogicalWindowAggregateRule.scala:88)
at
org.apache.flink.table.plan.rules.common.LogicalWindowAggregateRule.onMatch(LogicalWindowAggregateRule.scala:65)
at
org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:317)
at
org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:556)
at
org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:415)
at
org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:252)
at
org.apache.calcite.plan.hep.HepInstruction$RuleInstance.execute(HepInstruction.java:127)*
Any reason why this may be happening.
Thanks and regards,
arujit
On Thu, Nov 21, 2019 at 2:37 PM 贺小令(晓令) <xi...@alibaba-inc.com>
wrote:
> please try this approach: interval + interval
>
> like this:
> SELECT count(1) AS event_count ,
> TUMBLE_END(rowtime, INTERVAL '7' HOUR + INTERVAL '30' MINUTE) AS
> window_timestamp
> FROM `data_stream`
> GROUP BY TUMBLE ( rowtime, INTERVAL '7' HOUR + INTERVAL '30' MINUTE)
>
> thanks,
> godfrey
>
> ------------------------------------------------------------------
> 发件人：Arujit Pradhan <ar...@gojek.com>
> 发送时间：2019年11月21日(星期四) 16:23
> 收件人：user <us...@flink.apache.org>
> 主 题：Compound Time interval in SQL queries
>
> Hi all,
>
> Is there a way to define a compound time interval(that can consist of both
> HOUR and MINUTE) in windows in a Flink SQL query.
>
> For example, we want to do something like this:
> SELECT count(1) AS event_count ,
> TUMBLE_END(rowtime,
> INTERVAL '7' HOUR
> AND '30' MINUTE) AS window_timestamp
> FROM `data_stream`
> GROUP BY TUMBLE ( rowtime, INTERVAL '7' HOUR '30' MINUTE )
>
> We can not even convert this to Minutes as we are getting this error :
> *Interval field value <xxx> exceeds precision of MINUTE(2) field*
>
> We were going through Calcite documentation and could not find any
> workaround on this.
>
> Thanks and regards,
> arujit
>
>
回复：Compound Time interval in SQL queries
Posted by "贺小令(晓令)" <xi...@alibaba-inc.com>.
please try this approach: interval + interval
like this:
SELECT count(1) AS event_count ,
TUMBLE_END(rowtime, INTERVAL '7' HOUR + INTERVAL '30' MINUTE) AS window_timestamp
FROM `data_stream`
GROUP BY TUMBLE ( rowtime, INTERVAL '7' HOUR + INTERVAL '30' MINUTE)
thanks,
godfrey
------------------------------------------------------------------
发件人：Arujit Pradhan <ar...@gojek.com>
发送时间：2019年11月21日(星期四) 16:23
收件人：user <us...@flink.apache.org>
主 题：Compound Time interval in SQL queries
Hi all,
Is there a way to define a compound time interval(that can consist of both HOUR and MINUTE) in windows in a Flink SQL query.
For example, we want to do something like this:
SELECT count(1) AS event_count ,
TUMBLE_END(rowtime,
INTERVAL '7' HOUR
AND '30' MINUTE) AS window_timestamp
FROM `data_stream`
GROUP BY TUMBLE ( rowtime, INTERVAL '7' HOUR '30' MINUTE )
We can not even convert this to Minutes as we are getting this error :
Interval field value <xxx> exceeds precision of MINUTE(2) field
We were going through Calcite documentation and could not find any workaround on this.
Thanks and regards,
arujit