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 "guaishushu1103@163.com" <gu...@163.com> on 2020/05/09 05:51:51 UTC

Flink-1.10-SQL TopN语法问题

hi 大家,我在使用TopN语法,往mysql写数据时,发现必须将rownum设置为数据库的主键,要不然会报错,有人遇到吗



guaishushu1103@163.com

Re: Flink-1.10-SQL TopN语法问题

Posted by Leonard Xu <xb...@gmail.com>.
+ user-zh

> 在 2020年5月20日,15:27,Leonard Xu <xb...@gmail.com> 写道:
> 
> Hi,guaishushu
> 
> 先说声抱歉邮件回复晚了,过了下你的sql,问题是1.10 中 对于upsertSink的primary key是通过query来推断的,部分query是推断不出来的,你的query刚好推断
> 不出来PK的,所以会提示:Exception in thread "main" org.apache.flink.table.api.TableException: UpsertStreamTableSink requires that Table has a full primary keys if it is updated.
> 
> 在1.10的临时解决方法是加一层group by,使得query可以推断出 primary key:
> INSERT INTO test_mysql_2
>  SELECT order_id,rss, start_time FROM(
>> SELECT vid,rss, start_time FROM (
>>   SELECT  vid,rss, start_time,
>>     ROW_NUMBER() OVER (PARTITION BY start_time ORDER BY rss desc) AS rownum
>>   FROM (
>> 	   SELECT vid,
>> 		DATE_FORMAT(TUMBLE_START(rowtime, INTERVAL '5' MINUTE),'yyyy-MM-dd HH:00') AS start_time,
>> 		SUM(response_size) AS rss
>> 		FROM user_log
>> 		GROUP BY vid, TUMBLE(rowtime, INTERVAL '5' MINUTE)
>> 	  )
>> )
>> WHERE rownum <= 10
> 
> 
> ) group by order_id,rss, start_time
> 
> 这确实是不完善的地方,即将到来的1.11中已经有了优雅的解决方案,可以在sink的表中声明primary key 而不是通过query推断PK[1].
> 
> 
> Best,
> Leonard
> [1] https://github.com/apache/flink/blob/master/flink-connectors/flink-connector-jdbc/src/test/java/org/apache/flink/connector/jdbc/table/JdbcDynamicTableSinkITCase.java#L184 <https://github.com/apache/flink/blob/master/flink-connectors/flink-connector-jdbc/src/test/java/org/apache/flink/connector/jdbc/table/JdbcDynamicTableSinkITCase.java#L184>
> 
> 
>> 在 2020年5月11日,10:26,guaishushu1103@163.com <ma...@163.com> 写道:
>> 
>> CREATE TABLE test_mysql_2 (
>> 	vid string,
>> 	rss BIGINT,
>> 	start_time string
>> ) with ( 
>> 	'connector.type' = 'jdbc',
>> 	'connector.url' = 'jdbc:mysql://xxx/xxx?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false' <mysql://xxx/xxx?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false'>,
>> 	'connector.username' = 'xxx',
>> 	'connector.password' = 'xxx',
>> 	'connector.table' = 'task_flink_table_3',
>> 	'connector.write.flush.max-rows' = '100'
>> );
>> 
>> 
>> 
>> 
>> INSERT INTO test_mysql_2
>> SELECT vid,rss, start_time FROM (
>>   SELECT  vid,rss, start_time,
>>     ROW_NUMBER() OVER (PARTITION BY start_time ORDER BY rss desc) AS rownum
>>   FROM (
>> 	   SELECT vid,
>> 		DATE_FORMAT(TUMBLE_START(rowtime, INTERVAL '5' MINUTE),'yyyy-MM-dd HH:00') AS start_time,
>> 		SUM(response_size) AS rss
>> 		FROM user_log
>> 		GROUP BY vid, TUMBLE(rowtime, INTERVAL '5' MINUTE)
>> 	  )
>> )
>> WHERE rownum <= 10;
>> 
>> guaishushu1103@163.com <ma...@163.com>
>>  
>> 发件人: Leonard Xu <ma...@gmail.com>
>> 发送时间: 2020-05-09 14:15
>> 收件人: user-zh <ma...@flink.apache.org>
>> 主题: Re: Flink-1.10-SQL TopN语法问题
>> Hi
>>  
>> 图挂了,可以用图床工具链接出来,或者可以把query贴出来看看嘛?
>>  
>> Best,
>> Leonard Xu
>>  
>> > 在 2020年5月9日,13:51,guaishushu1103@163.com <ma...@163.com> 写道:
>> >
>> > hi 大家,我在使用TopN语法,往mysql写数据时,发现必须将rownum设置为数据库的主键,要不然会报错,有人遇到吗
>> >
>> > guaishushu1103@163.com <ma...@163.com> <mailto:guaishushu1103@163.com <ma...@163.com>>
> 


Re: Flink-1.10-SQL TopN语法问题

Posted by Leonard Xu <xb...@gmail.com>.
Hi

图挂了,可以用图床工具链接出来,或者可以把query贴出来看看嘛?

Best,
Leonard Xu

> 在 2020年5月9日,13:51,guaishushu1103@163.com 写道:
> 
> hi 大家,我在使用TopN语法,往mysql写数据时,发现必须将rownum设置为数据库的主键,要不然会报错,有人遇到吗
> 
> guaishushu1103@163.com <ma...@163.com>