row_number in Apache Beam SQL

Question

I'm trying to generate row_number using Apache Beam SQL with below code:

PCollection<Row> rwrtg =
        PCollectionTuple.of(new TupleTag<>("trrtg"), rrtg)
                        .apply(SqlTransform.query("select appId, row_number() over (partition by appId order by rating asc) as issue_rank from trrtg"));

But getting below error:

java.lang.RuntimeException: cannot translate call ROW_NUMBER() OVER (PARTITION BY $t0 ORDER BY $t1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

Could you please advise how to fix this?

Answer 1

Beam SQL supports two Dialects:-

1. Beam Calcite - Details can be found here regarding supported operators.
2. ZetaSQL - Details can be found here regarding supported operators

Both these dialects today don't support row_number() analytics function. Hence you are getting the error.