How to pass PROCTIME through an aggregate

[Rex Fenley]

Hello,

Using the table api, I have a CREATE DDL which adds a PROCTIME() column and I need to use it deep (like 10 operators deep) in our plan. However, I explicitly do not want to use it before that point.

The problem I'm running into is for any group by UDAF I use I then lose the proctime column. I do not want to group by proctime there (it is unwindowed at that point) nor do I want to agg over proctime, I just want to pass the proctime column through until later when I actually use it in a window.

My question is, how do I forward proctime through group-by UDAFs without interfering with my plan?

Thanks!

--

[Arvid Heise-4]

Hi Rex,

just an idea, wouldn't it be possible to just add

UNIX_TIMESTAMP()

right before your window operation?

On Sat, Feb 20, 2021 at 2:14 AM Rex Fenley <[hidden email]> wrote:

Hello,

Using the table api, I have a CREATE DDL which adds a PROCTIME() column and I need to use it deep (like 10 operators deep) in our plan. However, I explicitly do not want to use it before that point.

The problem I'm running into is for any group by UDAF I use I then lose the proctime column. I do not want to group by proctime there (it is unwindowed at that point) nor do I want to agg over proctime, I just want to pass the proctime column through until later when I actually use it in a window.

My question is, how do I forward proctime through group-by UDAFs without interfering with my plan?

Thanks!

--

Rex Fenley  |  Software Engineer - Mobile and Backend

Remind.com |  BLOG  |  FOLLOW US  |  LIKE US

[Timo Walther]

Hi Rex,

as far as I know, we recently allowed PROCTIME() also at arbitrary
locations in the query. So you don't have to pass it through the
aggregate but you can call it afterwards again.

Does that work in your use case? Something like:

SELECT i, COUNT(*) FROM customers GROUP BY i, TUMBLE(PROCTIME(),
INTERVAL '5' SECOND)

Regards,
Timo




On 24.02.21 14:20, Arvid Heise wrote:

> Hi Rex,
>
> just an idea, wouldn't it be possible to just add
>
> |UNIX_TIMESTAMP()
> |
>
> |right before your window operation?|
>
> |
> |
>
>
> On Sat, Feb 20, 2021 at 2:14 AM Rex Fenley <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     Hello,
>
>     Using the table api, I have a CREATE DDL which adds a PROCTIME()
>     column and I need to use it deep (like 10 operators deep) in our
>     plan. However, I explicitly do not want to use it before that point.
>
>     The problem I'm running into is for any group by UDAF I use I then
>     lose the proctime column. I do not want to group by proctime there
>     (it is unwindowed at that point) nor do I want to agg over proctime,
>     I just want to pass the proctime column through until later when I
>     actually use it in a window.
>
>     My question is, how do I forward proctime through group-by UDAFs
>     without interfering with my plan?
>
>     Thanks!
>
>     --
>
>     Rex Fenley|Software Engineer - Mobile and Backend
>
>
>     Remind.com <https://www.remind.com/>| BLOG
>     <http://blog.remind.com/> | FOLLOW US
>     <https://twitter.com/remindhq> | LIKE US
>     <https://www.facebook.com/remindhq>
>

[Rex Fenley]

This is great Timo. Maybe it only works in SQL but not Table API in the middle of a plan, which is fine. We'll give this a shot, thank you so much.

On Fri, Feb 26, 2021 at 2:00 AM Timo Walther <[hidden email]> wrote:

Hi Rex,

as far as I know, we recently allowed PROCTIME() also at arbitrary
locations in the query. So you don't have to pass it through the
aggregate but you can call it afterwards again.

Does that work in your use case? Something like:

SELECT i, COUNT(*) FROM customers GROUP BY i, TUMBLE(PROCTIME(),
INTERVAL '5' SECOND)

Regards,
Timo




On 24.02.21 14:20, Arvid Heise wrote:
> Hi Rex,
>
> just an idea, wouldn't it be possible to just add
>
> |UNIX_TIMESTAMP()
> |
>
> |right before your window operation?|
>
> |
> |
>
>
> On Sat, Feb 20, 2021 at 2:14 AM Rex Fenley <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     Hello,
>
>     Using the table api, I have a CREATE DDL which adds a PROCTIME()
>     column and I need to use it deep (like 10 operators deep) in our
>     plan. However, I explicitly do not want to use it before that point.
>
>     The problem I'm running into is for any group by UDAF I use I then
>     lose the proctime column. I do not want to group by proctime there
>     (it is unwindowed at that point) nor do I want to agg over proctime,
>     I just want to pass the proctime column through until later when I
>     actually use it in a window.
>
>     My question is, how do I forward proctime through group-by UDAFs
>     without interfering with my plan?
>
>     Thanks!
>
>     --
>
>     Rex Fenley|Software Engineer - Mobile and Backend
>
>
>     Remind.com <https://www.remind.com/>| BLOG
>     <http://blog.remind.com/> | FOLLOW US
>     <https://twitter.com/remindhq> | LIKE US
>     <https://www.facebook.com/remindhq>
>

--