Compound Time interval in SQL queries

[Arujit Pradhan]

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

[贺小令(晓令)]

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 <[hidden email]>

发送时间：2019年11月21日(星期四) 16:23

收件人：user <[hidden email]>

主　题：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

[Arujit Pradhan]

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 贺小令(晓令) <[hidden email]> 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 <[hidden email]>

发送时间：2019年11月21日(星期四) 16:23

收件人：user <[hidden email]>

主　题：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

[贺小令(晓令)]

hi arujit,

Which Flink version are you using?

thanks, 

godfrey

------------------------------------------------------------------

发件人：Arujit Pradhan <[hidden email]>

发送时间：2019年11月21日(星期四) 17:21

收件人：贺小令(晓令) <[hidden email]>; user <[hidden email]>

主　题：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 贺小令(晓令) <[hidden email]> 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 <[hidden email]>

发送时间：2019年11月21日(星期四) 16:23

收件人：user <[hidden email]>

主　题：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

[贺小令(晓令)]

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 <[hidden email]>

发送时间：2019年11月21日(星期四) 17:31

收件人：贺小令(晓令) <[hidden email]>

主　题：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 贺小令(晓令) <[hidden email]> wrote:

hi arujit,

Which Flink version are you using?

thanks, 

godfrey

------------------------------------------------------------------

发件人：Arujit Pradhan <[hidden email]>

发送时间：2019年11月21日(星期四) 17:21

收件人：贺小令(晓令) <[hidden email]>; user <[hidden email]>

主　题：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 贺小令(晓令) <[hidden email]> 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 <[hidden email]>

发送时间：2019年11月21日(星期四) 16:23

收件人：user <[hidden email]>

主　题：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