Thanks, Seth. That did the trick!

On Fri, Jan 29, 2021 at 10:51 AM Seth Wiesman <sjwies...@gmail.com> wrote:

> You need to use TUMBLE_ROWTIME to extract a time attribute from a window,
> TUMBLE_END is just a timestamp.
>
>
> https://ci.apache.org/projects/flink/flink-docs-master/dev/table/sql/queries.html#selecting-group-window-start-and-end-timestamps
>
> Seth
>
> On Fri, Jan 29, 2021 at 9:14 AM Patrick Angeles <patrickange...@gmail.com>
> wrote:
>
>> Forgot to mention, I am using Flink 1.12.
>>
>> On Fri, Jan 29, 2021 at 10:11 AM Patrick Angeles <
>> patrickange...@gmail.com> wrote:
>>
>>> Fairly new to Flink here so this might be a newbie mistake, but here's
>>> the problem. I created the following table and view:
>>>
>>>
>>>> CREATE TABLE test (
>>>>
>>>>     event_time     TIMESTAMP(3),
>>>>
>>>>     symbol         STRING,
>>>>
>>>>     price          DOUBLE,
>>>>
>>>>     WATERMARK FOR event_time AS event_time - INTERVAL '1' MINUTE
>>>>
>>>> ) WITH (
>>>>
>>>>     ...
>>>>
>>>> ) ;
>>>>
>>>>
>>>>
>>>> CREATE VIEW test_view AS (
>>>>
>>>>     SELECT
>>>>
>>>>         symbol,
>>>>
>>>>         TUMBLE_START (event_time, INTERVAL '1' MINUTE) AS t_start,
>>>>
>>>>         AVG (price) AS avg_price,
>>>>
>>>>         MIN (price) AS min_price,
>>>>
>>>>         MAX (price) AS max_price
>>>>
>>>>     FROM
>>>>
>>>>         test
>>>>
>>>>     WHERE event_time IS NOT NULL
>>>>
>>>>     GROUP BY
>>>>
>>>>         TUMBLE(event_time, INTERVAL '1' MINUTE), symbol
>>>>
>>>> ) ;
>>>>
>>>
>>> Describe shows:
>>>
>>> Flink SQL> describe test ;
>>>>
>>>>
>>>> +------------+------------------------+------+-----+--------+------------------------------------+
>>>>
>>>> |       name |                   type | null | key | extras |
>>>>                 watermark |
>>>>
>>>>
>>>> +------------+------------------------+------+-----+--------+------------------------------------+
>>>>
>>>> | event_time | TIMESTAMP(3) *ROWTIME* | true |     |        |
>>>> `event_time` - INTERVAL '1' MINUTE |
>>>>
>>>> |     symbol |                 STRING | true |     |        |
>>>>                           |
>>>>
>>>> |      price |                 DOUBLE | true |     |        |
>>>>                           |
>>>>
>>>>
>>>> +------------+------------------------+------+-----+--------+------------------------------------+
>>>>
>>>> 3 rows in set
>>>>
>>>>
>>>> Flink SQL> describe test_view ;
>>>>
>>>> +-----------+------------------------+------+-----+--------+-----------+
>>>>
>>>> |      name |                   type | null | key | extras | watermark
>>>> |
>>>>
>>>> +-----------+------------------------+------+-----+--------+-----------+
>>>>
>>>> |    symbol |                 STRING | true |     |        |
>>>> |
>>>>
>>>> |   t_start | TIMESTAMP(3) *ROWTIME* | true |     |        |
>>>> |
>>>>
>>>> | avg_price |                 DOUBLE | true |     |        |
>>>> |
>>>>
>>>> | min_price |                 DOUBLE | true |     |        |
>>>> |
>>>>
>>>> | max_price |                 DOUBLE | true |     |        |
>>>> |
>>>>
>>>> +-----------+------------------------+------+-----+--------+-----------+
>>>>
>>>> 5 rows in set
>>>>
>>>
>>> When I run a query over the view, I get the following error:
>>>
>>> Flink SQL> SELECT
>>>>
>>>> >     symbol,
>>>>
>>>> >     t_start,
>>>>
>>>> >     avg_price,
>>>>
>>>> >     min_price,
>>>>
>>>> >     max_price,
>>>>
>>>> >     FIRST_VALUE (avg_price) OVER x AS prev_avg_price
>>>>
>>>> > FROM test_view
>>>>
>>>> > WINDOW x AS (
>>>>
>>>> >     PARTITION BY symbol
>>>>
>>>> >     ORDER BY t_start
>>>>
>>>> >     ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
>>>>
>>>> > ) ;
>>>>
>>>> >
>>>>
>>>> *[ERROR] Could not execute SQL statement. Reason:*
>>>>
>>>> *org.apache.flink.table.api.TableException: OVER windows' ordering in
>>>> stream mode must be defined on a time attribute.*
>>>>
>>>
>>> Right now, to get around this, I need to materialize the results off
>>> test table into a new table that matches the view. Seems that this ought to
>>> be doable doing everything in one job instead of the intermediate
>>> materialization step. Am I missing something?
>>>
>>> Thanks in advance.
>>>
>>> Patrick
>>>
>>>
>>>
>>>
>>

Reply via email to