Error with table sql query - No match found for function signature TUMBLE(<TIME>, <INTERVAL_DAY_TIME>)

[PedroMrChaves]

Hello,

I am trying to build an query using the StreamTableEnvironment API. I Am trying to build this queries with tableEnvironment.sql("QUERY") so that I can in the future load those queries from a file.

Code source:

Table accesses = tableEnvironment.sql
                                ("SELECT STREAM TUMBLE_END(rowtime, INTERVAL '1' HOUR) AS rowtime, user,ip "
                                                + "FROM eventData "
                                                + "WHERE action='denied' "
                                                + "GROUP BY TUMBLE(rowtime, INTERVAL '1' HOUR) user,ip"
                                                + " HAVING COUNT(user,ip) > 5");

But I always get the following error:

Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1, column 120 to line 1, column 153: No match found for function signature TUMBLE(<TIME>, <INTERVAL_DAY_TIME>)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
        at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:765)
        at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:753)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3929)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1544)
        at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:278)
        at org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:222)
        at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4266)
        at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4253)
        at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:135)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1462)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1445)
        at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:233)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateGroupClause(SqlValidatorImpl.java:3305)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2959)
        at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
        at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:845)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:831)
        at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:208)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:807)
        at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:523)
        at org.apache.flink.api.table.FlinkPlannerImpl.validate(FlinkPlannerImpl.scala:84)
        ... 10 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match found for function signature TUMBLE(<TIME>, <INTERVAL_DAY_TIME>)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
        at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:514)


What am I doing wrong?

I've attached my pom.xml file with all the dependencies.
pom.xml

Regards.

[Fabian Hueske-2]

Hi Pedro,

support for window aggregations in SQL and Table API is currently work in progress.
We have a pull request for the Table API and will add this feature for the next release.
For SQL we depend on Apache Calcite to include the TUMBLE keyword in its parser and optimizer.

At the moment the only way to do window aggregations is by using the DataStream API.

Best, Fabian

2016-10-12 18:51 GMT+02:00 PedroMrChaves <[hidden email]>:

Hello,

I am trying to build an query using the StreamTableEnvironment API. I Am
trying to build this queries with tableEnvironment.sql("QUERY") so that I
can in the future load those queries from a file.

Code source:

Table accesses = tableEnvironment.sql
                                ("SELECT STREAM TUMBLE_END(rowtime, INTERVAL '1' HOUR) AS rowtime,
user,ip "
                                                + "FROM eventData "
                                                + "WHERE action='denied' "
                                                + "GROUP BY TUMBLE(rowtime, INTERVAL '1' HOUR) user,ip"
                                                + " HAVING COUNT(user,ip) > 5");

But I always get the following error:

/Caused by: org.apache.calcite.runtime.CalciteContextException: From line 1,
column 120 to line 1, column 153: No match found for function signature
TUMBLE(<TIME>, <INTERVAL_DAY_TIME>)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
        at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at
org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
        at
org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:765)
        at
org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:753)
        at
org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:3929)
        at
org.apache.calcite.sql.validate.SqlValidatorImpl.handleUnresolvedFunction(SqlValidatorImpl.java:1544)
        at
org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:278)
        at
org.apache.calcite.sql.SqlFunction.deriveType(SqlFunction.java:222)
        at
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4266)
        at
org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:4253)
        at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:135)
        at
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1462)
        at
org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1445)
        at org.apache.calcite.sql.SqlNode.validateExpr(SqlNode.java:233)
        at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateGroupClause(SqlValidatorImpl.java:3305)
        at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:2959)
        at
org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
        at
org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:86)
        at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:845)
        at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:831)
        at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:208)
        at
org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:807)
        at
org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:523)
        at
org.apache.flink.api.table.FlinkPlannerImpl.validate(FlinkPlannerImpl.scala:84)
        ... 10 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: No match
found for function signature TUMBLE(<TIME>, <INTERVAL_DAY_TIME>)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native
Method)
        at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at
org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:405)
        at
org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:514)
/

What am I doing wrong?

Regards.



--
View this message in context: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/Error-with-table-sql-query-No-match-found-for-function-signature-TUMBLE-TIME-INTERVAL-DAY-TIME-tp9497.html
Sent from the Apache Flink User Mailing List archive. mailing list archive at Nabble.com.

[PedroMrChaves]

Hi,

Thanks for the response.
What would be the easiest way to do this query using the DataStream API?

Thank you.

[Fabian Hueske-2]

Hi Pedro,

the DataStream program would like this:

val eventData: DataStream[?] = ???

val result = eventData

  .filter("action = denied")

  .keyBy("user", "ip")

  .timeWindow(Time.hours(1))

  .apply("window.end, user, ip, count(*)")

  .filter("count > 5")

  .map("windowEnd, user, ip")

Note, this is just the overall structure. You need to implement a couple of user functions (basically all operators require a function).

Cheers, Fabian

2016-10-13 12:45 GMT+02:00 PedroMrChaves <[hidden email]>:

Hi,

Thanks for the response.
What would be the easiest way to do this query using the DataStream API?

Thank you.





--
View this message in context: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/Error-with-table-sql-query-No-match-found-for-function-signature-TUMBLE-TIME-INTERVAL-DAY-TIME-tp9497p9523.html

Sent from the Apache Flink User Mailing List archive. mailing list archive at Nabble.com.

[PedroMrChaves]

I have this so far:

result = eventData
                                .filter(new FilterFunction<Event>(){
                                        public boolean filter(Event event){
                                                return event.action.equals("denied");
                                        }
                                })
                                .keyBy(0)
                                .timeWindow(Time.seconds(10))
                                .apply("???")
                                .filter(new FilterFunction<Integer>(){
                                        public boolean filter(Integer count){
                                                return count.intValue() > 5
                                        }
                                });

I don't know what can I put in the apply function in order to the count of pairs (user,ip ) whose action="denied.

Is there any documentation other than https://ci.apache.org/projects/flink/flink-docs-release-1.1/apis/streaming/index.html that I can use in order to get more information? I Just started with Flink and I still have difficulties with the API.

Regards.

[Fabian Hueske-2]

apply() accepts a WindowFunction which is essentially the same as a GroupReduceFunction, i.e., you have an iterator over all events in the window.

If you only want to count, you should have a look at incremental window aggregation with a ReduceFunction or FoldFunction [1].

Best, Fabian

[1] https://ci.apache.org/projects/flink/flink-docs-release-1.1/apis/streaming/windows.html#windowfunction-with-incremental-aggregation

2016-10-13 14:37 GMT+02:00 PedroMrChaves <[hidden email]>:

I have this so far:

result = eventData
                                .filter(new FilterFunction<Event>(){
                                        public boolean filter(Event event){
                                                return event.action.equals("denied");
                                        }
                                })
                                .keyBy(0)
                                .timeWindow(Time.seconds(10))
                                .apply("???")
                                .filter(new FilterFunction<Integer>(){
                                        public boolean filter(Integer count){
                                                return count.intValue() > 5
                                        }
                                });

I don't know what can I put in the apply function in order to the count of
pairs (user,ip ) whose action="denied.

Regards.



--
View this message in context: http://apache-flink-user-mailing-list-archive.2336050.n4.nabble.com/Error-with-table-sql-query-No-match-found-for-function-signature-TUMBLE-TIME-INTERVAL-DAY-TIME-tp9497p9526.html

Sent from the Apache Flink User Mailing List archive. mailing list archive at Nabble.com.