SQL aggregation functions inside the Table API

[orips]

Hi,

Some functions only exist in the SQL interface and are missing from the Table API. For example LAST_VALUE(expression) [1]

I still want to use this function in my aggregation, and I don't want to implement a user-defined function. Can I combine an SQL expression inside my Table API?

For example:

val table = tenv
      .fromDataStream(stream)
      .groupBy($"name")
      .select($"name", $"products".count(), $"LAST_VALUE(age)")

If not - how can I get the last value of a column inside an aggregation?

Thanks.

[1] https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/functions/systemFunctions.html#aggregate-functions

[Timo Walther]

Hi Ori,

we might support SQL expressions soon in Table API. However, we might
not support aggregate functions immediately. I would recommend to use
`sqlQuery` for now.

The following is supported:

val table = tenv.fromDataStream(stream)

val newTable = tenv.sqlQuery(s"SELECT ... FROM $table")

So switching between Table API and SQL can be done fluently.

I hope this helps.

Regards,
Timo


On 09.11.20 14:33, Ori Popowski wrote:

> Hi,
>
> Some functions only exist in the SQL interface and are missing from the
> Table API. For example LAST_VALUE(expression) [1]
>
> I still want to use this function in my aggregation, and I don't want to
> implement a user-defined function. Can I combine an SQL expression
> inside my Table API?
>
> For example:
>
> val table = tenv
>        .fromDataStream(stream)
>        .groupBy($"name")
>        .select($"name", $"products".count(), $"LAST_VALUE(age)")
>
> If not - how can I get the last value of a column inside an aggregation?
>
> Thanks.
>
> [1]
> https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/functions/systemFunctions.html#aggregate-functions 
> <https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/functions/systemFunctions.html#aggregate-functions>

[orips]

Thanks

On Mon, Nov 9, 2020 at 4:50 PM Timo Walther <[hidden email]> wrote:

Hi Ori,

we might support SQL expressions soon in Table API. However, we might
not support aggregate functions immediately. I would recommend to use
`sqlQuery` for now.

The following is supported:

val table = tenv.fromDataStream(stream)

val newTable = tenv.sqlQuery(s"SELECT ... FROM $table")

So switching between Table API and SQL can be done fluently.

I hope this helps.

Regards,
Timo


On 09.11.20 14:33, Ori Popowski wrote:
> Hi,
>
> Some functions only exist in the SQL interface and are missing from the
> Table API. For example LAST_VALUE(expression) [1]
>
> I still want to use this function in my aggregation, and I don't want to
> implement a user-defined function. Can I combine an SQL expression
> inside my Table API?
>
> For example:
>
> val table = tenv
>        .fromDataStream(stream)
>        .groupBy($"name")
>        .select($"name", $"products".count(), $"LAST_VALUE(age)")
>
> If not - how can I get the last value of a column inside an aggregation?
>
> Thanks.
>
> [1]
> https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/functions/systemFunctions.html#aggregate-functions
> <https://ci.apache.org/projects/flink/flink-docs-release-1.11/dev/table/functions/systemFunctions.html#aggregate-functions>