Hi,
Sorry for being unclear. We have tried to generate SQL similar to the three
examples using the RelBuilder and the Uncollect expression. Something like
this.
relBuilder.scan("table");
final Holder<RexCorrelVariable> correlVar = Holder.empty();
relBuilder.variable(correlVar);
relBuilder.values(new String[] {"dummy"}, "");
relBuilder.project(
relBuilder.alias(
relBuilder.dot(relBuilder.field(correlVar.get(), "history"),
"all_of_history"),
"unnest_all_of_history"));
relBuilder.uncollect(List.of("unnest_all_of_history"), false);
relBuilder.correlate(JoinRelType.INNER, correlVar.get().id);
RexNode userIdNode = relBuilder.alias(relBuilder.field("id"), "id");
RexNode isActive =
relBuilder.alias(
relBuilder.dot(relBuilder.field("unnest_all_of_history"),
"is_active"),
"is_active");
relBuilder.project(userIdNode, reportingRegion);
RelNode result = relBuilder.build();
We can generate SQL using the CalciteSqlDialect but we get an exception
when using the BigQuerySqlDialect. We wonder what the best way to work with
UNNEST in relational algebra using RelBuilder for BigQuery.
Thanks.
On Tue, Nov 16, 2021 at 2:49 PM Thomas Rebele <[email protected]>
wrote:
> Hello,
>
> I don't understand what you want to do here. There's an UNNEST operator in
> Calcite, see the comment at the bottom of this section:
> https://calcite.apache.org/docs/reference.html#collection-functions.
>
> Cordialement / Best Regards,
> *Thomas Rebele, PhD* | R&D Developer | Germany | www.tibco.com
>
>
> On Tue, Nov 16, 2021 at 2:30 PM Daniel (Spotify) Ståhl
> <[email protected]> wrote:
>
> > Hi
> > We are trying to generate relational algebra for SQL that uses UNNEST of
> > arrays in BigQuery. Here are three examples of SQL (in Big Query standard
> > query syntax) that we want to be able to handle and generate from
> > relational algebra. Any tips on how you would do that in Calcite?
> >
> > SELECT id,
> > (SELECT h.is_active FROM UNNEST(history.all_of_history) h
> > WHERE start_date <= "2021-06-01" AND (end_date >= "2021-06-01" OR
> > end_date IS NULL))
> > FROM `table`;
> >
> > SELECT id, h.is_active
> > FROM `table`
> > CROSS JOIN UNNEST(history.all_of_history) h;
> >
> > SELECT id, count(h) as h
> > from `table`
> > CROSS JOIN UNNEST(history.all_of_history) h
> > GROUP BY id;
> >
> > The table "table" has two columns: "id" and "history". history is a
> record
> > and "history.all_of_history"
> > is a repeated record with three fields ("is_active", "start_date" and
> > "end_date").
> >
> > Thanks,
> > Daniel Ståhl
> >
>
