I wrote some tests on nested row selection in BeamSQL[1]. Those test cases test some behaviors of nested row selection that BeamSQL supports(but it's not a complete list).
You could check what are tested so that are supported. Also it's welcome to extend those tests to cover more behaviors. [1]: https://github.com/apache/beam/blob/master/sdks/java/extensions/sql/src/test/java/org/apache/beam/sdk/extensions/sql/BeamSqlDslNestedRowsTest.java -Rui On Thu, Jun 20, 2019 at 4:25 AM Andrew Pilloud <apill...@google.com> wrote: > Hi Alex, > > Unfortunately you are receiving an UnsupportedOperationException because > selecting nested rows is not supported by Calcite. You select fields out of > the nested row but not a row itself. There are some recent bug fixes in > this area in Calcite 1.20, so it might be worth trying that. There has been > a lot of work on this area in Calcite in the past year so it is also > possible the work that remains is in Beam. We have a bug open on the issue > in Beam: https://issues.apache.org/jira/browse/BEAM-5189 > > Beam is repackaging Calcite by copying and relocating the code using > the gradle relocate rule. The code is unchanged, but > 'org.apache.beam.repackaged.beam_sdks_java_extensions_sql' is added to the > class path so it won't conflict with Calcite in use by some of the Beam > runners. If you want to change the version to a local snapshot of calcite > for development you can modify it here: > https://github.com/apache/beam/blob/master/sdks/java/extensions/sql/build.gradle#L72 > > Andrew > > On Thu, Jun 20, 2019 at 3:02 AM alex goos <ag...@mac.com> wrote: > >> Beam 2.13, I'm trying to do SqlTransform on Row records with hierarchical >> structures. Records having (nullable) arrays of (nullabke) sub-records: >> >> "select p.recordType, p.listOfServiceData.seqOf[0].ratingGroup as >> ratingGroup, p.abcdAddress.seqOf[0] as ABCD_IP FROM PCOLLECTION " >> >> The code resposible for the Error when accessing subsctructures seems to >> come from a repackaged Calcite Library: Where do I start asking >> around/fixing the issue ?! Here? at the Apache Calcite mailing list? How is >> Beam "repackageing" Calcite-core ? Manually,by importing code ?! >> >> Exception in thread "main" java.lang.UnsupportedOperationException: class >> org.apache.beam.repackaged.beam_sdks_java_extensions_sql.org.apache.calcite.rex.RexFieldAccess: >> ITEM($28.seqOf, 0).ratingGroup >> at >> org.apache.beam.repackaged.beam_sdks_java_extensions_sql.org.apache.calcite.util.Util.needToImplement(Util.java:955) >> at >> org.apache.beam.repackaged.beam_sdks_java_extensions_sql.org.apache.calcite.sql2rel.RelStructuredTypeFlattener.flattenProjection(RelStructuredTypeFlattener.java:662) >> at >> org.apache.beam.repackaged.beam_sdks_java_extensions_sql.org.apache.calcite.sql2rel.RelStructuredTypeFlattener.flattenProjections(RelStructuredTypeFlattener.java:587) >> at >> org.apache.beam.repackaged.beam_sdks_java_extensions_sql.org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewriteRel(RelStructuredTypeFlattener.java:501) >> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) >> at >> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) >> at >> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) >> at java.lang.reflect.Method.invoke(Method.java:498) >> at >> org.apache.beam.repackaged.beam_sdks_java_extensions_sql.org.apache.calcite.util.ReflectUtil.invokeVisitorInternal(ReflectUtil.java:257) >> at >> org.apache.beam.repackaged.beam_sdks_java_extensions_sql.org.apache.calcite.util.ReflectUtil.invokeVisitor(ReflectUtil.java:214) >> at >> org.apache.beam.repackaged.beam_sdks_java_extensions_sql.org.apache.calcite.util.ReflectUtil$1.invokeVisitor(ReflectUtil.java:464) >> at >> org.apache.beam.repackaged.beam_sdks_java_extensions_sql.org.apache.calcite.sql2rel.RelStructuredTypeFlattener$RewriteRelVisitor.visit(RelStructuredTypeFlattener.java:768) >> at >> org.apache.beam.repackaged.beam_sdks_java_extensions_sql.org.apache.calcite.sql2rel.RelStructuredTypeFlattener.rewrite(RelStructuredTypeFlattener.java:195) >> at >> org.apache.beam.repackaged.beam_sdks_java_extensions_sql.org.apache.calcite.sql2rel.SqlToRelConverter.flattenTypes(SqlToRelConverter.java:468) >> at >> org.apache.beam.repackaged.beam_sdks_java_extensions_sql.org.apache.calcite.prepare.PlannerImpl.rel(PlannerImpl.java:236) >> at >> org.apache.beam.sdk.extensions.sql.impl.CalciteQueryPlanner.convertToBeamRel(CalciteQueryPlanner.java:129) >> at >> org.apache.beam.sdk.extensions.sql.impl.BeamSqlEnv.parseQuery(BeamSqlEnv.java:87) >> at >> org.apache.beam.sdk.extensions.sql.SqlTransform.expand(SqlTransform.java:122) >> at >> org.apache.beam.sdk.extensions.sql.SqlTransform.expand(SqlTransform.java:82) >> at org.apache.beam.sdk.Pipeline.applyInternal(Pipeline.java:537) >> at org.apache.beam.sdk.Pipeline.applyTransform(Pipeline.java:471) >> >> Many thanks! >> >
