The exception you shared is truncated so I am not sure the root cause of this exception (it only shows the top which says BeamSql cannot convert the query to a pipeline PTransforms).
Assuming you have basic knowledge of BeamSQL and Calcite. If you want to contribute to analytics function, there is a design doc that you can check to understand how query is mapped to Rel nodes: https://docs.google.com/document/d/1tJapdA7ZNwkU0NaK7p-em0XnpHqNE1pKIXw9hVJkIUg/edit?usp=sharing If you don't have basic knowledge of BeamSQL and Calcite, maybe better to start from smaller starter tasks(fix bugs or add operators). -Rui On Wed, May 12, 2021 at 10:30 AM Kyle Weaver <[email protected]> wrote: > If I'm understanding correctly, this query is taken from an existing test > [1], the only modification is the addition of "WHERE purchases > 3". > > This seems like a bug -- I'm guessing the planner/matching logic doesn't > handle filters properly. To really figure out what's going on, you'd have > to compare the plans for the same query with and without the filter. > > [1] > https://github.com/apache/beam/blob/9dbc7b7ceed7b744d90bbf771a9059ea91965353/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/BeamAnalyticFunctionsTest.java#L89-L95 > > On Tue, May 11, 2021 at 3:08 PM Burkay Gur <[email protected]> wrote: > >> Hi folks, >> >> When we try to run the following query on BeamSQL: >> >> SELECT item, purchases, category, sum(purchases) over (PARTITION BY >> category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT >> ROW) as total_purchases FROM PCOLLECTION WHERE purchases > 3 >> >> We are getting the following error: >> >> Unable to convert query >> org.apache.beam.sdk.extensions.sql.impl.SqlConversionException: Unable to >> convert query SELECT item, purchases, category, sum(purchases) over >> (PARTITION BY category ORDER BY purchases ROWS BETWEEN UNBOUNDED PRECEDING >> AND CURRENT ROW) as total_purchases FROM PCOLLECTION WHERE purchases > 3 >> at >> org.apache.beam.sdk.extensions.sql.impl.CalciteQueryPlanner.convertToBeamRel(CalciteQueryPlanner.java:212) >> at >> org.apache.beam.sdk.extensions.sql.impl.BeamSqlEnv.parseQuery(BeamSqlEnv.java:111) >> at >> org.apache.beam.sdk.extensions.sql.SqlTransform.expand(SqlTransform.java:171) >> at >> org.apache.beam.sdk.extensions.sql.SqlTransform.expand(SqlTransform.java:109) >> at org.apache.beam.sdk.Pipeline.applyInternal(Pipeline.java:548) at >> org.apache.beam.sdk.Pipeline.applyTransform(Pipeline.java:499) at >> org.apache.beam.sdk.values.PCollection.apply(PCollection.java:370) at >> >> We traced the issue back to this PR: >> https://github.com/apache/beam/pull/11975 specifically this line: >> https://github.com/apache/beam/pull/11975/files#diff-919be1e4bcc11c17b725cbf04168b583886ffe16286f9291893247954128ad81R43 >> >> What are the plans on a wider support for analytical functions? If I want >> to contribute, what are the best resources to learn more about how BeamSQL >> / Calcite integration is set up? >> >> Best, >> Burkay >> >>
