You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-zh@flink.apache.org by "guoliubin85@foxmail.com" <gu...@foxmail.com> on 2020/12/14 02:41:12 UTC
回复: Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
TUMBLE第一个参数需要的就是bigint，你这边time_local 直接用就好，不用转另外TIMESTAMP
guoliubin85@foxmail.com
发件人： kandy.wang
发送时间： 2020-12-14 10:28
收件人： user-zh
主题： Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
[ERROR] Could not execute SQL statement. Reason:org.apache.flink.table.api.TableException: Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
SQL 如下：
create temporary view expose as
select
mid
,time_local
,TO_TIMESTAMP(FROM_UNIXTIME(time_local / 1000, 'yyyy-MM-dd HH:mm:ss')) as log_ts
,proctime
from hive.temp.kafka_table
;
time_local 是bigint
select TUMBLE_START(log_ts, INTERVAL '1' MINUTE) as log_minute,count(1) pv
from expose
group by TUMBLE(log_ts, INTERVAL '1' MINUTE);
window agg的字段报错，如何解决。
Re:回复: Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
Posted by hailongwang <18...@163.com>.
Hi，
Window agg 使用可以参考[1]，其中 first argument 可以是 Process time 或者 Eventime。
[1] https://ci.apache.org/projects/flink/flink-docs-release-1.12/dev/table/sql/queries.html#group-windows
Best,
Hailong
在 2020-12-14 09:41:12，"guoliubin85@foxmail.com" <gu...@foxmail.com> 写道：
>TUMBLE第一个参数需要的就是bigint，你这边time_local 直接用就好，不用转另外TIMESTAMP
>
>
>
>guoliubin85@foxmail.com
>
>发件人： kandy.wang
>发送时间： 2020-12-14 10:28
>收件人： user-zh
>主题： Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
>[ERROR] Could not execute SQL statement. Reason:org.apache.flink.table.api.TableException: Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
>
>
>SQL 如下：
>create temporary view expose as
>
>select
>
>mid
>
>,time_local
>
>,TO_TIMESTAMP(FROM_UNIXTIME(time_local / 1000, 'yyyy-MM-dd HH:mm:ss')) as log_ts
>
>,proctime
>
>from hive.temp.kafka_table
>
>;
>time_local 是bigint
>
>
>
>select TUMBLE_START(log_ts, INTERVAL '1' MINUTE) as log_minute,count(1) pv
>
>from expose
>
>group by TUMBLE(log_ts, INTERVAL '1' MINUTE);
>
>
>window agg的字段报错，如何解决。
Re:回复: Re: Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
Posted by "kandy.wang" <ka...@163.com>.
@ guoliubin85
感谢回复，和你说的差不多，问题已经搞定。
在 2020-12-14 13:02:54，"guoliubin85@foxmail.com" <gu...@foxmail.com> 写道：
>不好意思我没说清楚。
>我这边用的是这样的SQL可以运作，你可以参考下。
>CREATE TABLE `someTable` (
> eventTime TIMESTAMP(3),
> WATERMARK FOR eventTime AS eventTime
>)
>eventTime是java的Long类型，包含毫秒，SQL里可以直接转成TIMESTAMP
>select someFunc(field)
>from `someTable`
>group by TUMBLE(eventTime, INTERVAL '1' SECOND)
>
>
>
>guoliubin85@foxmail.com
>
>发件人： kandy.wang
>发送时间： 2020-12-14 11:23
>收件人： user-zh
>主题： Re:回复: Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
>hi guoliubin85：
>一样的报错：
>
>Flink SQL> select mid,code,floor_id,TUMBLE_START(time_local/1000, INTERVAL '1' MINUTE) as log_minute,count(1) pv
>
>> from lightart_expose
>
>> where code is not null and floor_id is not null
>
>> group by mid,code,floor_id,TUMBLE(time_local/1000, INTERVAL '1' MINUTE);[ERROR] Could not execute SQL statement. Reason:
>
>org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply '$TUMBLE' to arguments of type '$TUMBLE(<BIGINT>, <INTERVAL MINUTE>)'. Supported form(s): '$TUMBLE(<DATETIME>, <DATETIME_INTERVAL>)'
>
>'$TUMBLE(<DATETIME>, <DATETIME_INTERVAL>, <TIME>)'
>
>
>
>> group by mid,code,floor_id,TUMBLE(time_local/1000, INTERVAL '1' MINUTE);[ERROR] Could not execute SQL statement. Reason:
>org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply '$TUMBLE' to arguments of type '$TUMBLE(<BIGINT>, <INTERVAL MINUTE>)'. Supported form(s): '$TUMBLE(<DATETIME>, <DATETIME_INTERVAL>)'
>'$TUMBLE(<DATETIME>, <DATETIME_INTERVAL>, <TIME>)'
>
>在 2020-12-14 10:41:12，"guoliubin85@foxmail.com" <gu...@foxmail.com> 写道：
>>TUMBLE第一个参数需要的就是bigint，你这边time_local 直接用就好，不用转另外TIMESTAMP
>>
>>
>>
>>guoliubin85@foxmail.com
>>
>>发件人： kandy.wang
>>发送时间： 2020-12-14 10:28
>>收件人： user-zh
>>主题： Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
>>[ERROR] Could not execute SQL statement. Reason:org.apache.flink.table.api.TableException: Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
>>
>>
>>SQL 如下：
>>create temporary view expose as
>>
>>select
>>
>>mid
>>
>>,time_local
>>
>>,TO_TIMESTAMP(FROM_UNIXTIME(time_local / 1000, 'yyyy-MM-dd HH:mm:ss')) as log_ts
>>
>>,proctime
>>
>>from hive.temp.kafka_table
>>
>>;
>>time_local 是bigint
>>
>>
>>
>>select TUMBLE_START(log_ts, INTERVAL '1' MINUTE) as log_minute,count(1) pv
>>
>>from expose
>>
>>group by TUMBLE(log_ts, INTERVAL '1' MINUTE);
>>
>>
>>window agg的字段报错，如何解决。
回复: Re: Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
Posted by "guoliubin85@foxmail.com" <gu...@foxmail.com>.
不好意思我没说清楚。
我这边用的是这样的SQL可以运作，你可以参考下。
CREATE TABLE `someTable` (
eventTime TIMESTAMP(3),
WATERMARK FOR eventTime AS eventTime
)
eventTime是java的Long类型，包含毫秒，SQL里可以直接转成TIMESTAMP
select someFunc(field)
from `someTable`
group by TUMBLE(eventTime, INTERVAL '1' SECOND)
guoliubin85@foxmail.com
发件人： kandy.wang
发送时间： 2020-12-14 11:23
收件人： user-zh
主题： Re:回复: Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
hi guoliubin85：
一样的报错：
Flink SQL> select mid,code,floor_id,TUMBLE_START(time_local/1000, INTERVAL '1' MINUTE) as log_minute,count(1) pv
> from lightart_expose
> where code is not null and floor_id is not null
> group by mid,code,floor_id,TUMBLE(time_local/1000, INTERVAL '1' MINUTE);[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply '$TUMBLE' to arguments of type '$TUMBLE(<BIGINT>, <INTERVAL MINUTE>)'. Supported form(s): '$TUMBLE(<DATETIME>, <DATETIME_INTERVAL>)'
'$TUMBLE(<DATETIME>, <DATETIME_INTERVAL>, <TIME>)'
> group by mid,code,floor_id,TUMBLE(time_local/1000, INTERVAL '1' MINUTE);[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply '$TUMBLE' to arguments of type '$TUMBLE(<BIGINT>, <INTERVAL MINUTE>)'. Supported form(s): '$TUMBLE(<DATETIME>, <DATETIME_INTERVAL>)'
'$TUMBLE(<DATETIME>, <DATETIME_INTERVAL>, <TIME>)'
在 2020-12-14 10:41:12，"guoliubin85@foxmail.com" <gu...@foxmail.com> 写道：
>TUMBLE第一个参数需要的就是bigint，你这边time_local 直接用就好，不用转另外TIMESTAMP
>
>
>
>guoliubin85@foxmail.com
>
>发件人： kandy.wang
>发送时间： 2020-12-14 10:28
>收件人： user-zh
>主题： Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
>[ERROR] Could not execute SQL statement. Reason:org.apache.flink.table.api.TableException: Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
>
>
>SQL 如下：
>create temporary view expose as
>
>select
>
>mid
>
>,time_local
>
>,TO_TIMESTAMP(FROM_UNIXTIME(time_local / 1000, 'yyyy-MM-dd HH:mm:ss')) as log_ts
>
>,proctime
>
>from hive.temp.kafka_table
>
>;
>time_local 是bigint
>
>
>
>select TUMBLE_START(log_ts, INTERVAL '1' MINUTE) as log_minute,count(1) pv
>
>from expose
>
>group by TUMBLE(log_ts, INTERVAL '1' MINUTE);
>
>
>window agg的字段报错，如何解决。
Re:回复: Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
Posted by "kandy.wang" <ka...@163.com>.
hi guoliubin85：
一样的报错：
Flink SQL> select mid,code,floor_id,TUMBLE_START(time_local/1000, INTERVAL '1' MINUTE) as log_minute,count(1) pv
> from lightart_expose
> where code is not null and floor_id is not null
> group by mid,code,floor_id,TUMBLE(time_local/1000, INTERVAL '1' MINUTE);[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply '$TUMBLE' to arguments of type '$TUMBLE(<BIGINT>, <INTERVAL MINUTE>)'. Supported form(s): '$TUMBLE(<DATETIME>, <DATETIME_INTERVAL>)'
'$TUMBLE(<DATETIME>, <DATETIME_INTERVAL>, <TIME>)'
> group by mid,code,floor_id,TUMBLE(time_local/1000, INTERVAL '1' MINUTE);[ERROR] Could not execute SQL statement. Reason:
org.apache.calcite.sql.validate.SqlValidatorException: Cannot apply '$TUMBLE' to arguments of type '$TUMBLE(<BIGINT>, <INTERVAL MINUTE>)'. Supported form(s): '$TUMBLE(<DATETIME>, <DATETIME_INTERVAL>)'
'$TUMBLE(<DATETIME>, <DATETIME_INTERVAL>, <TIME>)'
在 2020-12-14 10:41:12，"guoliubin85@foxmail.com" <gu...@foxmail.com> 写道：
>TUMBLE第一个参数需要的就是bigint，你这边time_local 直接用就好，不用转另外TIMESTAMP
>
>
>
>guoliubin85@foxmail.com
>
>发件人： kandy.wang
>发送时间： 2020-12-14 10:28
>收件人： user-zh
>主题： Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
>[ERROR] Could not execute SQL statement. Reason:org.apache.flink.table.api.TableException: Window aggregate can only be defined over a time attribute column, but TIMESTAMP(3) encountered.
>
>
>SQL 如下：
>create temporary view expose as
>
>select
>
>mid
>
>,time_local
>
>,TO_TIMESTAMP(FROM_UNIXTIME(time_local / 1000, 'yyyy-MM-dd HH:mm:ss')) as log_ts
>
>,proctime
>
>from hive.temp.kafka_table
>
>;
>time_local 是bigint
>
>
>
>select TUMBLE_START(log_ts, INTERVAL '1' MINUTE) as log_minute,count(1) pv
>
>from expose
>
>group by TUMBLE(log_ts, INTERVAL '1' MINUTE);
>
>
>window agg的字段报错，如何解决。