0

I am using match recognize query to my stream and order by (event_time) where event_time field is of type timestamp. I have added assignTimestampsAndWatermarks() to the field in stream and then create table followed by the query but i get the error rowtime or proctime should be first in order by. Here is my code snippet:

mapTimestampStream = mapStream.assignTimestampsAndWatermarks(new AscendingTimestampExtractor<Tuple10<String, String, String, String, String, String, Timestamp, String, String, Timestamp>>() {
    private static final long serialVersionUID = 1L;
        @Override
        public long extractAscendingTimestamp(Tuple10<String, String, String, String, String, String, Timestamp, String, String, Timestamp> element) {
            long timestamp = Timestamp.valueOf(element.getField(6).toString()).getTime();
            return timestamp;
        }
});


    
Table state_Table = bsTableEnv.fromDataStream(mapTimestampStream, XAASUtil.EB_JOURNEY_STATE_TABLE_SCHEMA);
bsTableEnv.registerTable("eb_user_journey", state_Table);
    Table matchResult = bsTableEnv.sqlQuery(
        "Select Id, StartTime, EndTime from eb_user_journey MATCH_RECOGNIZE( PARTITION BY user_id ORDER BY event_time MEASURES A.user_id AS Id, FIRST(A.event_time) AS StartTime, LAST(A.event_time) AS EndTime"
            + " AFTER MATCH SKIP TO NEXT ROW " + "  PATTERN (A+ B) "
            + " DEFINE A as A.user_id = 1001 )");
Jiayi Liao
  • 999
  • 4
  • 15
IraSantuManu lifeblog
  • 1
  • 2
  • I think it'd be better if you can give the error stack here. – Jiayi Liao Jul 01 '20 at 11:22
  • Hi below is the error : – IraSantuManu lifeblog Jul 01 '20 at 11:55
  • --- ERROR ---- You must specify either rowtime or proctime for order by as the first one. org.apache.flink.table.api.ValidationException: You must specify either rowtime or proctime for order by as the first one. – IraSantuManu lifeblog Jul 01 '20 at 11:56
  • at org.apache.flink.table.planner.delegation.StreamPlanner.translateToPlan(StreamPlanner.scala:59) at org.apache.flink.table.planner.delegation.PlannerBase.translate(PlannerBase.scala:153) at org.apache.flink.table.api.java.internal.StreamTableEnvironmentImpl.toDataStream(StreamTableEnvironmentImpl.java:351) at org.apache.flink.table.api.java.internal.StreamTableEnvironmentImpl.toAppendStream(StreamTableEnvironmentImpl.java:259) at com.xaas.flink.widget.StateWidgetFlinkWithMatchRec.main(StateWidgetFlinkWithMatchRec.java:109) – IraSantuManu lifeblog Jul 01 '20 at 11:58
  • what are steps we need to follow to use the field as rowtime in order by clause ? 1.I have assignedtimestamps and the ensured that the field is of type timestamp in the stream . 2. I have used field (event_time) as first field in order by clause. – IraSantuManu lifeblog Jul 01 '20 at 12:00
  • Can you show us XAASUtil.EB_JOURNEY_STATE_TABLE_SCHEMA? That's where you might indicate that event_time is the rowtime attribute. – David Anderson Jul 01 '20 at 14:00
  • public static final String EB_JOURNEY_STATE_TABLE_SCHEMA = "user_id,journey_id,journey_name,ctx_one,action,relation,event_time,event_name,recipe_name,proctime"; – IraSantuManu lifeblog Jul 02 '20 at 04:39
  • If I use public static final String EB_JOURNEY_STATE_TABLE_SCHEMA = "user_id,journey_id,journey_name,ctx_one,action,relation,event_time.rowtime ,event_name,recipe_name,proctime"; i get the error as A rowtime attribute requires an EventTime time characteristic in stream environment. But is: ProcessingTime – IraSantuManu lifeblog Jul 02 '20 at 04:40
  • The error is gone after adding streamEnv.setStreamTimeCharacteristic(TimeCharacteristic.EventTime); But I do not see the results from the query, where query is very simple : Table matchResult = bsTableEnv.sqlQuery( "Select Id, StartTime, EndTime from eb_user_journey MATCH_RECOGNIZE( PARTITION BY user_id ORDER BY event_time MEASURES A.user_id AS Id, FIRST(A.event_time) AS StartTime, FIRST(A.event_time) AS EndTime" + " AFTER MATCH SKIP TO NEXT ROW " + " PATTERN (A B) " + " DEFINE A as A.event_name = 'Entered' )"); – IraSantuManu lifeblog Jul 02 '20 at 11:28
  • Have you set up watermarking? Without valid watermarking match_recognize can not produce any results, since it must first sort the stream. – David Anderson Jul 03 '20 at 21:16
  • I tried using two ways to assign watermarks , one by AssignerWithPeriodicWatermarks , where currentmaxtimstamp is always set to 0 public Watermark getCurrentWatermark() { return new Watermark(currentMaxTimestamp - allowedlatetime); } – IraSantuManu lifeblog Jul 06 '20 at 09:08
  • other way i tried with AscendingTimestampExtractor() , in both approach i see low watermark in flink client. – IraSantuManu lifeblog Jul 06 '20 at 09:11
  • @DavidAnderson Thanks for suggestions , it worked with watermarks and proper windowing – IraSantuManu lifeblog Jul 07 '20 at 06:17

0 Answers0