[YAML] Aggregations

[Robert Bradshaw via dev <de...@beam.apache.org>]

Beam Yaml has good support for IOs and mappings, but one key missing
feature for even writing a WordCount is the ability to do Aggregations
[1]. While the traditional Beam primitive is GroupByKey (and
CombineValues), we're eschewing KVs in the notion of more schema'd
data (which has some precedence in our other languages, see the links
below). The key components the user needs to specify are (1) the key
fields on which the grouping will take place, (2) the fields
(expressions?) involved in the aggregation, and (3) what aggregating
fn to use.

A straw-man example could be something like

type: Aggregating
config:
  key: [field1, field2]
  aggregating:
    total_cost:
      fn: sum
      value: cost
    max_cost:
      fn: max
      value: cost

This would basically correspond to the SQL expression

"SELECT field1, field2, sum(cost) as total_cost, max(cost) as max_cost
from table GROUP BY field1, field2"

(though I'm not requiring that we use this as an implementation
strategy). I do not think we need a separate (non aggregating)
Grouping operation, this can be accomplished by having a concat-style
combiner.

There are still some open questions here, notably around how to
specify the aggregation fns themselves. We could of course provide a
number of built-ins (like SQL does). This gets into the question of
how and where to document this complete set, but some basics should
take us pretty far. Many aggregators, however, are parameterized (e.g.
quantiles); where do we put the parameters? We could go with something
like

fn:
  type: ApproximateQuantiles
  config:
    n: 10

but others are even configured by functions themselves (e.g. LargestN
that wants a comparator Fn). Maybe we decide not to support these
(yet?)

One thing I think we should support, however, is referencing custom
CombineFns. We have some precedent for this with our Fns from
MapToFields, where we accept things like inline lambdas and external
references. Again the topic of how to configure them comes up, as
these custom Fns are more likely to be parameterized than Map Fns
(though, to be clear, perhaps it'd be good to allow parameterizatin of
MapFns as well). Maybe we allow

language: python. # like MapToFields (and here it'd be harder to mix
and match per Fn)
fn:
  type: ???
  # should these be nested as config?
  name: fully.qualiied.name
  path: /path/to/defining/file
  args: [...]
  kwargs: {...}

which would invoke the constructor.

I'm also open to other ways of naming/structuring these essential
parameters if it makes things more clear.

- Robert


Java: https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
Python: https://beam.apache.org/documentation/transforms/python/aggregation/groupby
Typescript: https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html

[1] One can of course use SqlTransform for this, but I'm leaning
towards offering something more native.

Re: [YAML] Aggregations

[Kenneth Knowles <ke...@apache.org>]

Automatically dereferencing, basically. It is nice. Especially for
many-to-many relationships like the example. I don't know if the
aggregation is any different though, is it?

Kenn

On Sun, Oct 29, 2023 at 1:12 PM Robert Burke <ro...@frantil.com> wrote:

> I came across Edge DB, and it has a novel syntax moving away from SQL with
> their EdgeQL.
>
> https://www.edgedb.com/
>
> Eg. Heere are two equivalent "nested" queries.
>
>
> # EdgeQL
>
> select Movie {
>   title,
>   actors: {
>    name
>   },
>   rating := math::mean(.reviews.score)
> } filter "Zendaya" in .actors.name;
>
>
> # SQL
>
> SELECT
>   title,
>   Actors.name AS actor_name,
>   (SELECT avg(score)
>     FROM Movie_Reviews
>     WHERE movie_id = Movie.id) AS rating
> FROM
>   Movie
>   LEFT JOIN Movie_Actors ON
>     Movie.id = Movie_Actors.movie_id
>   LEFT JOIN Person AS Actors ON
>     Movie_Actors.person_id = Person.id
> WHERE
>   'Zendaya' IN (
>     SELECT Person.name
>     FROM
>       Movie_Actors
>       INNER JOIN Person
>         ON Movie_Actors.person_id = Person.id
>     WHERE
>       Movie_Actors.movie_id = Movie.id)
>
>
> The key observations here are specifics around join kinds and stuff don't
> often need to be directly expressed in the query.
>
> I'd need to dig deeper around it (such as do they share... ) but it does
> do a nice first impression of demos.
>
>
> On Mon, Oct 23, 2023, 7:00 AM XQ Hu via dev <de...@beam.apache.org> wrote:
>
>> +1 on your proposal.
>>
>> On Fri, Oct 20, 2023 at 4:59 PM Robert Bradshaw via dev <
>> dev@beam.apache.org> wrote:
>>
>>> On Fri, Oct 20, 2023 at 11:35 AM Kenneth Knowles <ke...@apache.org>
>>> wrote:
>>> >
>>> > A couple other bits on having an expression language:
>>> >
>>> >  - You already have Python lambdas at places, right? so that's quite a
>>> lot more complex than SQL project/aggregate expressions
>>> >  - It really does save a lot of pain for users (at the cost of
>>> implementation complexity) when you need to "SUM(col1*col2)" where
>>> otherwise you have to Map first. This could be viewed as desirable as well,
>>> of course.
>>> >
>>> > Anyhow I'm pretty much in agreement with all your reasoning as to why
>>> *not* to use SQL-like expressions in strings. But it does seem odd when
>>> juxtaposed with Python snippets.
>>>
>>> Well, we say "here's a Python expression" when we're using a Python
>>> string. But "SUM(col1*col2)" isn't as transparent. (Agree about the
>>> niceties of being able to provide an expression rather than a column.)
>>>
>>> > On Thu, Oct 19, 2023 at 4:00 PM Robert Bradshaw via dev <
>>> dev@beam.apache.org> wrote:
>>> >>
>>> >> On Thu, Oct 19, 2023 at 12:53 PM Reuven Lax <re...@google.com> wrote:
>>> >> >
>>> >> > Is the schema Group transform (in Java) something along these lines?
>>> >>
>>> >> Yes, for sure it is. It (and Python's and Typescript's equivalent) are
>>> >> linked in the original post. The open question is how to best express
>>> >> this in YAML.
>>> >>
>>> >> > On Wed, Oct 18, 2023 at 1:11 PM Robert Bradshaw via dev <
>>> dev@beam.apache.org> wrote:
>>> >> >>
>>> >> >> Beam Yaml has good support for IOs and mappings, but one key
>>> missing
>>> >> >> feature for even writing a WordCount is the ability to do
>>> Aggregations
>>> >> >> [1]. While the traditional Beam primitive is GroupByKey (and
>>> >> >> CombineValues), we're eschewing KVs in the notion of more schema'd
>>> >> >> data (which has some precedence in our other languages, see the
>>> links
>>> >> >> below). The key components the user needs to specify are (1) the
>>> key
>>> >> >> fields on which the grouping will take place, (2) the fields
>>> >> >> (expressions?) involved in the aggregation, and (3) what
>>> aggregating
>>> >> >> fn to use.
>>> >> >>
>>> >> >> A straw-man example could be something like
>>> >> >>
>>> >> >> type: Aggregating
>>> >> >> config:
>>> >> >>   key: [field1, field2]
>>> >> >>   aggregating:
>>> >> >>     total_cost:
>>> >> >>       fn: sum
>>> >> >>       value: cost
>>> >> >>     max_cost:
>>> >> >>       fn: max
>>> >> >>       value: cost
>>> >> >>
>>> >> >> This would basically correspond to the SQL expression
>>> >> >>
>>> >> >> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as
>>> max_cost
>>> >> >> from table GROUP BY field1, field2"
>>> >> >>
>>> >> >> (though I'm not requiring that we use this as an implementation
>>> >> >> strategy). I do not think we need a separate (non aggregating)
>>> >> >> Grouping operation, this can be accomplished by having a
>>> concat-style
>>> >> >> combiner.
>>> >> >>
>>> >> >> There are still some open questions here, notably around how to
>>> >> >> specify the aggregation fns themselves. We could of course provide
>>> a
>>> >> >> number of built-ins (like SQL does). This gets into the question of
>>> >> >> how and where to document this complete set, but some basics should
>>> >> >> take us pretty far. Many aggregators, however, are parameterized
>>> (e.g.
>>> >> >> quantiles); where do we put the parameters? We could go with
>>> something
>>> >> >> like
>>> >> >>
>>> >> >> fn:
>>> >> >>   type: ApproximateQuantiles
>>> >> >>   config:
>>> >> >>     n: 10
>>> >> >>
>>> >> >> but others are even configured by functions themselves (e.g.
>>> LargestN
>>> >> >> that wants a comparator Fn). Maybe we decide not to support these
>>> >> >> (yet?)
>>> >> >>
>>> >> >> One thing I think we should support, however, is referencing custom
>>> >> >> CombineFns. We have some precedent for this with our Fns from
>>> >> >> MapToFields, where we accept things like inline lambdas and
>>> external
>>> >> >> references. Again the topic of how to configure them comes up, as
>>> >> >> these custom Fns are more likely to be parameterized than Map Fns
>>> >> >> (though, to be clear, perhaps it'd be good to allow
>>> parameterizatin of
>>> >> >> MapFns as well). Maybe we allow
>>> >> >>
>>> >> >> language: python. # like MapToFields (and here it'd be harder to
>>> mix
>>> >> >> and match per Fn)
>>> >> >> fn:
>>> >> >>   type: ???
>>> >> >>   # should these be nested as config?
>>> >> >>   name: fully.qualiied.name
>>> >> >>   path: /path/to/defining/file
>>> >> >>   args: [...]
>>> >> >>   kwargs: {...}
>>> >> >>
>>> >> >> which would invoke the constructor.
>>> >> >>
>>> >> >> I'm also open to other ways of naming/structuring these essential
>>> >> >> parameters if it makes things more clear.
>>> >> >>
>>> >> >> - Robert
>>> >> >>
>>> >> >>
>>> >> >> Java:
>>> https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
>>> >> >> Python:
>>> https://beam.apache.org/documentation/transforms/python/aggregation/groupby
>>> >> >> Typescript:
>>> https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
>>> >> >>
>>> >> >> [1] One can of course use SqlTransform for this, but I'm leaning
>>> >> >> towards offering something more native.
>>>
>>

Re: [YAML] Aggregations

[Robert Burke <ro...@frantil.com>]

I came across Edge DB, and it has a novel syntax moving away from SQL with
their EdgeQL.

https://www.edgedb.com/

Eg. Heere are two equivalent "nested" queries.


# EdgeQL

select Movie {
  title,
  actors: {
   name
  },
  rating := math::mean(.reviews.score)
} filter "Zendaya" in .actors.name;


# SQL

SELECT
  title,
  Actors.name AS actor_name,
  (SELECT avg(score)
    FROM Movie_Reviews
    WHERE movie_id = Movie.id) AS rating
FROM
  Movie
  LEFT JOIN Movie_Actors ON
    Movie.id = Movie_Actors.movie_id
  LEFT JOIN Person AS Actors ON
    Movie_Actors.person_id = Person.id
WHERE
  'Zendaya' IN (
    SELECT Person.name
    FROM
      Movie_Actors
      INNER JOIN Person
        ON Movie_Actors.person_id = Person.id
    WHERE
      Movie_Actors.movie_id = Movie.id)


The key observations here are specifics around join kinds and stuff don't
often need to be directly expressed in the query.

I'd need to dig deeper around it (such as do they share... ) but it does do
a nice first impression of demos.


On Mon, Oct 23, 2023, 7:00 AM XQ Hu via dev <de...@beam.apache.org> wrote:

> +1 on your proposal.
>
> On Fri, Oct 20, 2023 at 4:59 PM Robert Bradshaw via dev <
> dev@beam.apache.org> wrote:
>
>> On Fri, Oct 20, 2023 at 11:35 AM Kenneth Knowles <ke...@apache.org> wrote:
>> >
>> > A couple other bits on having an expression language:
>> >
>> >  - You already have Python lambdas at places, right? so that's quite a
>> lot more complex than SQL project/aggregate expressions
>> >  - It really does save a lot of pain for users (at the cost of
>> implementation complexity) when you need to "SUM(col1*col2)" where
>> otherwise you have to Map first. This could be viewed as desirable as well,
>> of course.
>> >
>> > Anyhow I'm pretty much in agreement with all your reasoning as to why
>> *not* to use SQL-like expressions in strings. But it does seem odd when
>> juxtaposed with Python snippets.
>>
>> Well, we say "here's a Python expression" when we're using a Python
>> string. But "SUM(col1*col2)" isn't as transparent. (Agree about the
>> niceties of being able to provide an expression rather than a column.)
>>
>> > On Thu, Oct 19, 2023 at 4:00 PM Robert Bradshaw via dev <
>> dev@beam.apache.org> wrote:
>> >>
>> >> On Thu, Oct 19, 2023 at 12:53 PM Reuven Lax <re...@google.com> wrote:
>> >> >
>> >> > Is the schema Group transform (in Java) something along these lines?
>> >>
>> >> Yes, for sure it is. It (and Python's and Typescript's equivalent) are
>> >> linked in the original post. The open question is how to best express
>> >> this in YAML.
>> >>
>> >> > On Wed, Oct 18, 2023 at 1:11 PM Robert Bradshaw via dev <
>> dev@beam.apache.org> wrote:
>> >> >>
>> >> >> Beam Yaml has good support for IOs and mappings, but one key missing
>> >> >> feature for even writing a WordCount is the ability to do
>> Aggregations
>> >> >> [1]. While the traditional Beam primitive is GroupByKey (and
>> >> >> CombineValues), we're eschewing KVs in the notion of more schema'd
>> >> >> data (which has some precedence in our other languages, see the
>> links
>> >> >> below). The key components the user needs to specify are (1) the key
>> >> >> fields on which the grouping will take place, (2) the fields
>> >> >> (expressions?) involved in the aggregation, and (3) what aggregating
>> >> >> fn to use.
>> >> >>
>> >> >> A straw-man example could be something like
>> >> >>
>> >> >> type: Aggregating
>> >> >> config:
>> >> >>   key: [field1, field2]
>> >> >>   aggregating:
>> >> >>     total_cost:
>> >> >>       fn: sum
>> >> >>       value: cost
>> >> >>     max_cost:
>> >> >>       fn: max
>> >> >>       value: cost
>> >> >>
>> >> >> This would basically correspond to the SQL expression
>> >> >>
>> >> >> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as
>> max_cost
>> >> >> from table GROUP BY field1, field2"
>> >> >>
>> >> >> (though I'm not requiring that we use this as an implementation
>> >> >> strategy). I do not think we need a separate (non aggregating)
>> >> >> Grouping operation, this can be accomplished by having a
>> concat-style
>> >> >> combiner.
>> >> >>
>> >> >> There are still some open questions here, notably around how to
>> >> >> specify the aggregation fns themselves. We could of course provide a
>> >> >> number of built-ins (like SQL does). This gets into the question of
>> >> >> how and where to document this complete set, but some basics should
>> >> >> take us pretty far. Many aggregators, however, are parameterized
>> (e.g.
>> >> >> quantiles); where do we put the parameters? We could go with
>> something
>> >> >> like
>> >> >>
>> >> >> fn:
>> >> >>   type: ApproximateQuantiles
>> >> >>   config:
>> >> >>     n: 10
>> >> >>
>> >> >> but others are even configured by functions themselves (e.g.
>> LargestN
>> >> >> that wants a comparator Fn). Maybe we decide not to support these
>> >> >> (yet?)
>> >> >>
>> >> >> One thing I think we should support, however, is referencing custom
>> >> >> CombineFns. We have some precedent for this with our Fns from
>> >> >> MapToFields, where we accept things like inline lambdas and external
>> >> >> references. Again the topic of how to configure them comes up, as
>> >> >> these custom Fns are more likely to be parameterized than Map Fns
>> >> >> (though, to be clear, perhaps it'd be good to allow parameterizatin
>> of
>> >> >> MapFns as well). Maybe we allow
>> >> >>
>> >> >> language: python. # like MapToFields (and here it'd be harder to mix
>> >> >> and match per Fn)
>> >> >> fn:
>> >> >>   type: ???
>> >> >>   # should these be nested as config?
>> >> >>   name: fully.qualiied.name
>> >> >>   path: /path/to/defining/file
>> >> >>   args: [...]
>> >> >>   kwargs: {...}
>> >> >>
>> >> >> which would invoke the constructor.
>> >> >>
>> >> >> I'm also open to other ways of naming/structuring these essential
>> >> >> parameters if it makes things more clear.
>> >> >>
>> >> >> - Robert
>> >> >>
>> >> >>
>> >> >> Java:
>> https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
>> >> >> Python:
>> https://beam.apache.org/documentation/transforms/python/aggregation/groupby
>> >> >> Typescript:
>> https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
>> >> >>
>> >> >> [1] One can of course use SqlTransform for this, but I'm leaning
>> >> >> towards offering something more native.
>>
>

Re: [YAML] Aggregations

[XQ Hu via dev <de...@beam.apache.org>]

+1 on your proposal.

On Fri, Oct 20, 2023 at 4:59 PM Robert Bradshaw via dev <de...@beam.apache.org>
wrote:

> On Fri, Oct 20, 2023 at 11:35 AM Kenneth Knowles <ke...@apache.org> wrote:
> >
> > A couple other bits on having an expression language:
> >
> >  - You already have Python lambdas at places, right? so that's quite a
> lot more complex than SQL project/aggregate expressions
> >  - It really does save a lot of pain for users (at the cost of
> implementation complexity) when you need to "SUM(col1*col2)" where
> otherwise you have to Map first. This could be viewed as desirable as well,
> of course.
> >
> > Anyhow I'm pretty much in agreement with all your reasoning as to why
> *not* to use SQL-like expressions in strings. But it does seem odd when
> juxtaposed with Python snippets.
>
> Well, we say "here's a Python expression" when we're using a Python
> string. But "SUM(col1*col2)" isn't as transparent. (Agree about the
> niceties of being able to provide an expression rather than a column.)
>
> > On Thu, Oct 19, 2023 at 4:00 PM Robert Bradshaw via dev <
> dev@beam.apache.org> wrote:
> >>
> >> On Thu, Oct 19, 2023 at 12:53 PM Reuven Lax <re...@google.com> wrote:
> >> >
> >> > Is the schema Group transform (in Java) something along these lines?
> >>
> >> Yes, for sure it is. It (and Python's and Typescript's equivalent) are
> >> linked in the original post. The open question is how to best express
> >> this in YAML.
> >>
> >> > On Wed, Oct 18, 2023 at 1:11 PM Robert Bradshaw via dev <
> dev@beam.apache.org> wrote:
> >> >>
> >> >> Beam Yaml has good support for IOs and mappings, but one key missing
> >> >> feature for even writing a WordCount is the ability to do
> Aggregations
> >> >> [1]. While the traditional Beam primitive is GroupByKey (and
> >> >> CombineValues), we're eschewing KVs in the notion of more schema'd
> >> >> data (which has some precedence in our other languages, see the links
> >> >> below). The key components the user needs to specify are (1) the key
> >> >> fields on which the grouping will take place, (2) the fields
> >> >> (expressions?) involved in the aggregation, and (3) what aggregating
> >> >> fn to use.
> >> >>
> >> >> A straw-man example could be something like
> >> >>
> >> >> type: Aggregating
> >> >> config:
> >> >>   key: [field1, field2]
> >> >>   aggregating:
> >> >>     total_cost:
> >> >>       fn: sum
> >> >>       value: cost
> >> >>     max_cost:
> >> >>       fn: max
> >> >>       value: cost
> >> >>
> >> >> This would basically correspond to the SQL expression
> >> >>
> >> >> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as
> max_cost
> >> >> from table GROUP BY field1, field2"
> >> >>
> >> >> (though I'm not requiring that we use this as an implementation
> >> >> strategy). I do not think we need a separate (non aggregating)
> >> >> Grouping operation, this can be accomplished by having a concat-style
> >> >> combiner.
> >> >>
> >> >> There are still some open questions here, notably around how to
> >> >> specify the aggregation fns themselves. We could of course provide a
> >> >> number of built-ins (like SQL does). This gets into the question of
> >> >> how and where to document this complete set, but some basics should
> >> >> take us pretty far. Many aggregators, however, are parameterized
> (e.g.
> >> >> quantiles); where do we put the parameters? We could go with
> something
> >> >> like
> >> >>
> >> >> fn:
> >> >>   type: ApproximateQuantiles
> >> >>   config:
> >> >>     n: 10
> >> >>
> >> >> but others are even configured by functions themselves (e.g. LargestN
> >> >> that wants a comparator Fn). Maybe we decide not to support these
> >> >> (yet?)
> >> >>
> >> >> One thing I think we should support, however, is referencing custom
> >> >> CombineFns. We have some precedent for this with our Fns from
> >> >> MapToFields, where we accept things like inline lambdas and external
> >> >> references. Again the topic of how to configure them comes up, as
> >> >> these custom Fns are more likely to be parameterized than Map Fns
> >> >> (though, to be clear, perhaps it'd be good to allow parameterizatin
> of
> >> >> MapFns as well). Maybe we allow
> >> >>
> >> >> language: python. # like MapToFields (and here it'd be harder to mix
> >> >> and match per Fn)
> >> >> fn:
> >> >>   type: ???
> >> >>   # should these be nested as config?
> >> >>   name: fully.qualiied.name
> >> >>   path: /path/to/defining/file
> >> >>   args: [...]
> >> >>   kwargs: {...}
> >> >>
> >> >> which would invoke the constructor.
> >> >>
> >> >> I'm also open to other ways of naming/structuring these essential
> >> >> parameters if it makes things more clear.
> >> >>
> >> >> - Robert
> >> >>
> >> >>
> >> >> Java:
> https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
> >> >> Python:
> https://beam.apache.org/documentation/transforms/python/aggregation/groupby
> >> >> Typescript:
> https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
> >> >>
> >> >> [1] One can of course use SqlTransform for this, but I'm leaning
> >> >> towards offering something more native.
>

Re: [YAML] Aggregations

[Robert Bradshaw via dev <de...@beam.apache.org>]

On Fri, Oct 20, 2023 at 11:35 AM Kenneth Knowles <ke...@apache.org> wrote:
>
> A couple other bits on having an expression language:
>
>  - You already have Python lambdas at places, right? so that's quite a lot more complex than SQL project/aggregate expressions
>  - It really does save a lot of pain for users (at the cost of implementation complexity) when you need to "SUM(col1*col2)" where otherwise you have to Map first. This could be viewed as desirable as well, of course.
>
> Anyhow I'm pretty much in agreement with all your reasoning as to why *not* to use SQL-like expressions in strings. But it does seem odd when juxtaposed with Python snippets.

Well, we say "here's a Python expression" when we're using a Python
string. But "SUM(col1*col2)" isn't as transparent. (Agree about the
niceties of being able to provide an expression rather than a column.)

> On Thu, Oct 19, 2023 at 4:00 PM Robert Bradshaw via dev <de...@beam.apache.org> wrote:
>>
>> On Thu, Oct 19, 2023 at 12:53 PM Reuven Lax <re...@google.com> wrote:
>> >
>> > Is the schema Group transform (in Java) something along these lines?
>>
>> Yes, for sure it is. It (and Python's and Typescript's equivalent) are
>> linked in the original post. The open question is how to best express
>> this in YAML.
>>
>> > On Wed, Oct 18, 2023 at 1:11 PM Robert Bradshaw via dev <de...@beam.apache.org> wrote:
>> >>
>> >> Beam Yaml has good support for IOs and mappings, but one key missing
>> >> feature for even writing a WordCount is the ability to do Aggregations
>> >> [1]. While the traditional Beam primitive is GroupByKey (and
>> >> CombineValues), we're eschewing KVs in the notion of more schema'd
>> >> data (which has some precedence in our other languages, see the links
>> >> below). The key components the user needs to specify are (1) the key
>> >> fields on which the grouping will take place, (2) the fields
>> >> (expressions?) involved in the aggregation, and (3) what aggregating
>> >> fn to use.
>> >>
>> >> A straw-man example could be something like
>> >>
>> >> type: Aggregating
>> >> config:
>> >>   key: [field1, field2]
>> >>   aggregating:
>> >>     total_cost:
>> >>       fn: sum
>> >>       value: cost
>> >>     max_cost:
>> >>       fn: max
>> >>       value: cost
>> >>
>> >> This would basically correspond to the SQL expression
>> >>
>> >> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as max_cost
>> >> from table GROUP BY field1, field2"
>> >>
>> >> (though I'm not requiring that we use this as an implementation
>> >> strategy). I do not think we need a separate (non aggregating)
>> >> Grouping operation, this can be accomplished by having a concat-style
>> >> combiner.
>> >>
>> >> There are still some open questions here, notably around how to
>> >> specify the aggregation fns themselves. We could of course provide a
>> >> number of built-ins (like SQL does). This gets into the question of
>> >> how and where to document this complete set, but some basics should
>> >> take us pretty far. Many aggregators, however, are parameterized (e.g.
>> >> quantiles); where do we put the parameters? We could go with something
>> >> like
>> >>
>> >> fn:
>> >>   type: ApproximateQuantiles
>> >>   config:
>> >>     n: 10
>> >>
>> >> but others are even configured by functions themselves (e.g. LargestN
>> >> that wants a comparator Fn). Maybe we decide not to support these
>> >> (yet?)
>> >>
>> >> One thing I think we should support, however, is referencing custom
>> >> CombineFns. We have some precedent for this with our Fns from
>> >> MapToFields, where we accept things like inline lambdas and external
>> >> references. Again the topic of how to configure them comes up, as
>> >> these custom Fns are more likely to be parameterized than Map Fns
>> >> (though, to be clear, perhaps it'd be good to allow parameterizatin of
>> >> MapFns as well). Maybe we allow
>> >>
>> >> language: python. # like MapToFields (and here it'd be harder to mix
>> >> and match per Fn)
>> >> fn:
>> >>   type: ???
>> >>   # should these be nested as config?
>> >>   name: fully.qualiied.name
>> >>   path: /path/to/defining/file
>> >>   args: [...]
>> >>   kwargs: {...}
>> >>
>> >> which would invoke the constructor.
>> >>
>> >> I'm also open to other ways of naming/structuring these essential
>> >> parameters if it makes things more clear.
>> >>
>> >> - Robert
>> >>
>> >>
>> >> Java: https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
>> >> Python: https://beam.apache.org/documentation/transforms/python/aggregation/groupby
>> >> Typescript: https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
>> >>
>> >> [1] One can of course use SqlTransform for this, but I'm leaning
>> >> towards offering something more native.

Re: [YAML] Aggregations

[Kenneth Knowles <ke...@apache.org>]

A couple other bits on having an expression language:

 - You already have Python lambdas at places, right? so that's quite a lot
more complex than SQL project/aggregate expressions
 - It really does save a lot of pain for users (at the cost of
implementation complexity) when you need to "SUM(col1*col2)" where
otherwise you have to Map first. This could be viewed as desirable as well,
of course.

Anyhow I'm pretty much in agreement with all your reasoning as to why *not*
to use SQL-like expressions in strings. But it does seem odd when
juxtaposed with Python snippets.

Kenn

On Thu, Oct 19, 2023 at 4:00 PM Robert Bradshaw via dev <de...@beam.apache.org>
wrote:

> On Thu, Oct 19, 2023 at 12:53 PM Reuven Lax <re...@google.com> wrote:
> >
> > Is the schema Group transform (in Java) something along these lines?
>
> Yes, for sure it is. It (and Python's and Typescript's equivalent) are
> linked in the original post. The open question is how to best express
> this in YAML.
>
> > On Wed, Oct 18, 2023 at 1:11 PM Robert Bradshaw via dev <
> dev@beam.apache.org> wrote:
> >>
> >> Beam Yaml has good support for IOs and mappings, but one key missing
> >> feature for even writing a WordCount is the ability to do Aggregations
> >> [1]. While the traditional Beam primitive is GroupByKey (and
> >> CombineValues), we're eschewing KVs in the notion of more schema'd
> >> data (which has some precedence in our other languages, see the links
> >> below). The key components the user needs to specify are (1) the key
> >> fields on which the grouping will take place, (2) the fields
> >> (expressions?) involved in the aggregation, and (3) what aggregating
> >> fn to use.
> >>
> >> A straw-man example could be something like
> >>
> >> type: Aggregating
> >> config:
> >>   key: [field1, field2]
> >>   aggregating:
> >>     total_cost:
> >>       fn: sum
> >>       value: cost
> >>     max_cost:
> >>       fn: max
> >>       value: cost
> >>
> >> This would basically correspond to the SQL expression
> >>
> >> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as max_cost
> >> from table GROUP BY field1, field2"
> >>
> >> (though I'm not requiring that we use this as an implementation
> >> strategy). I do not think we need a separate (non aggregating)
> >> Grouping operation, this can be accomplished by having a concat-style
> >> combiner.
> >>
> >> There are still some open questions here, notably around how to
> >> specify the aggregation fns themselves. We could of course provide a
> >> number of built-ins (like SQL does). This gets into the question of
> >> how and where to document this complete set, but some basics should
> >> take us pretty far. Many aggregators, however, are parameterized (e.g.
> >> quantiles); where do we put the parameters? We could go with something
> >> like
> >>
> >> fn:
> >>   type: ApproximateQuantiles
> >>   config:
> >>     n: 10
> >>
> >> but others are even configured by functions themselves (e.g. LargestN
> >> that wants a comparator Fn). Maybe we decide not to support these
> >> (yet?)
> >>
> >> One thing I think we should support, however, is referencing custom
> >> CombineFns. We have some precedent for this with our Fns from
> >> MapToFields, where we accept things like inline lambdas and external
> >> references. Again the topic of how to configure them comes up, as
> >> these custom Fns are more likely to be parameterized than Map Fns
> >> (though, to be clear, perhaps it'd be good to allow parameterizatin of
> >> MapFns as well). Maybe we allow
> >>
> >> language: python. # like MapToFields (and here it'd be harder to mix
> >> and match per Fn)
> >> fn:
> >>   type: ???
> >>   # should these be nested as config?
> >>   name: fully.qualiied.name
> >>   path: /path/to/defining/file
> >>   args: [...]
> >>   kwargs: {...}
> >>
> >> which would invoke the constructor.
> >>
> >> I'm also open to other ways of naming/structuring these essential
> >> parameters if it makes things more clear.
> >>
> >> - Robert
> >>
> >>
> >> Java:
> https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
> >> Python:
> https://beam.apache.org/documentation/transforms/python/aggregation/groupby
> >> Typescript:
> https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
> >>
> >> [1] One can of course use SqlTransform for this, but I'm leaning
> >> towards offering something more native.
>

Re: [YAML] Aggregations

[Robert Bradshaw via dev <de...@beam.apache.org>]

On Thu, Oct 19, 2023 at 12:53 PM Reuven Lax <re...@google.com> wrote:
>
> Is the schema Group transform (in Java) something along these lines?

Yes, for sure it is. It (and Python's and Typescript's equivalent) are
linked in the original post. The open question is how to best express
this in YAML.

> On Wed, Oct 18, 2023 at 1:11 PM Robert Bradshaw via dev <de...@beam.apache.org> wrote:
>>
>> Beam Yaml has good support for IOs and mappings, but one key missing
>> feature for even writing a WordCount is the ability to do Aggregations
>> [1]. While the traditional Beam primitive is GroupByKey (and
>> CombineValues), we're eschewing KVs in the notion of more schema'd
>> data (which has some precedence in our other languages, see the links
>> below). The key components the user needs to specify are (1) the key
>> fields on which the grouping will take place, (2) the fields
>> (expressions?) involved in the aggregation, and (3) what aggregating
>> fn to use.
>>
>> A straw-man example could be something like
>>
>> type: Aggregating
>> config:
>>   key: [field1, field2]
>>   aggregating:
>>     total_cost:
>>       fn: sum
>>       value: cost
>>     max_cost:
>>       fn: max
>>       value: cost
>>
>> This would basically correspond to the SQL expression
>>
>> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as max_cost
>> from table GROUP BY field1, field2"
>>
>> (though I'm not requiring that we use this as an implementation
>> strategy). I do not think we need a separate (non aggregating)
>> Grouping operation, this can be accomplished by having a concat-style
>> combiner.
>>
>> There are still some open questions here, notably around how to
>> specify the aggregation fns themselves. We could of course provide a
>> number of built-ins (like SQL does). This gets into the question of
>> how and where to document this complete set, but some basics should
>> take us pretty far. Many aggregators, however, are parameterized (e.g.
>> quantiles); where do we put the parameters? We could go with something
>> like
>>
>> fn:
>>   type: ApproximateQuantiles
>>   config:
>>     n: 10
>>
>> but others are even configured by functions themselves (e.g. LargestN
>> that wants a comparator Fn). Maybe we decide not to support these
>> (yet?)
>>
>> One thing I think we should support, however, is referencing custom
>> CombineFns. We have some precedent for this with our Fns from
>> MapToFields, where we accept things like inline lambdas and external
>> references. Again the topic of how to configure them comes up, as
>> these custom Fns are more likely to be parameterized than Map Fns
>> (though, to be clear, perhaps it'd be good to allow parameterizatin of
>> MapFns as well). Maybe we allow
>>
>> language: python. # like MapToFields (and here it'd be harder to mix
>> and match per Fn)
>> fn:
>>   type: ???
>>   # should these be nested as config?
>>   name: fully.qualiied.name
>>   path: /path/to/defining/file
>>   args: [...]
>>   kwargs: {...}
>>
>> which would invoke the constructor.
>>
>> I'm also open to other ways of naming/structuring these essential
>> parameters if it makes things more clear.
>>
>> - Robert
>>
>>
>> Java: https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
>> Python: https://beam.apache.org/documentation/transforms/python/aggregation/groupby
>> Typescript: https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
>>
>> [1] One can of course use SqlTransform for this, but I'm leaning
>> towards offering something more native.

Re: [YAML] Aggregations

[Reuven Lax via dev <de...@beam.apache.org>]

Or are you specifically referring to the declarative YAML pipelines?

On Thu, Oct 19, 2023 at 12:53 PM Reuven Lax <re...@google.com> wrote:

> Is the schema Group transform (in Java) something along these lines?
>
> On Wed, Oct 18, 2023 at 1:11 PM Robert Bradshaw via dev <
> dev@beam.apache.org> wrote:
>
>> Beam Yaml has good support for IOs and mappings, but one key missing
>> feature for even writing a WordCount is the ability to do Aggregations
>> [1]. While the traditional Beam primitive is GroupByKey (and
>> CombineValues), we're eschewing KVs in the notion of more schema'd
>> data (which has some precedence in our other languages, see the links
>> below). The key components the user needs to specify are (1) the key
>> fields on which the grouping will take place, (2) the fields
>> (expressions?) involved in the aggregation, and (3) what aggregating
>> fn to use.
>>
>> A straw-man example could be something like
>>
>> type: Aggregating
>> config:
>>   key: [field1, field2]
>>   aggregating:
>>     total_cost:
>>       fn: sum
>>       value: cost
>>     max_cost:
>>       fn: max
>>       value: cost
>>
>> This would basically correspond to the SQL expression
>>
>> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as max_cost
>> from table GROUP BY field1, field2"
>>
>> (though I'm not requiring that we use this as an implementation
>> strategy). I do not think we need a separate (non aggregating)
>> Grouping operation, this can be accomplished by having a concat-style
>> combiner.
>>
>> There are still some open questions here, notably around how to
>> specify the aggregation fns themselves. We could of course provide a
>> number of built-ins (like SQL does). This gets into the question of
>> how and where to document this complete set, but some basics should
>> take us pretty far. Many aggregators, however, are parameterized (e.g.
>> quantiles); where do we put the parameters? We could go with something
>> like
>>
>> fn:
>>   type: ApproximateQuantiles
>>   config:
>>     n: 10
>>
>> but others are even configured by functions themselves (e.g. LargestN
>> that wants a comparator Fn). Maybe we decide not to support these
>> (yet?)
>>
>> One thing I think we should support, however, is referencing custom
>> CombineFns. We have some precedent for this with our Fns from
>> MapToFields, where we accept things like inline lambdas and external
>> references. Again the topic of how to configure them comes up, as
>> these custom Fns are more likely to be parameterized than Map Fns
>> (though, to be clear, perhaps it'd be good to allow parameterizatin of
>> MapFns as well). Maybe we allow
>>
>> language: python. # like MapToFields (and here it'd be harder to mix
>> and match per Fn)
>> fn:
>>   type: ???
>>   # should these be nested as config?
>>   name: fully.qualiied.name
>>   path: /path/to/defining/file
>>   args: [...]
>>   kwargs: {...}
>>
>> which would invoke the constructor.
>>
>> I'm also open to other ways of naming/structuring these essential
>> parameters if it makes things more clear.
>>
>> - Robert
>>
>>
>> Java:
>> https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
>> Python:
>> https://beam.apache.org/documentation/transforms/python/aggregation/groupby
>> Typescript:
>> https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
>>
>> [1] One can of course use SqlTransform for this, but I'm leaning
>> towards offering something more native.
>>
>

Re: [YAML] Aggregations

[Reuven Lax via dev <de...@beam.apache.org>]

Is the schema Group transform (in Java) something along these lines?

On Wed, Oct 18, 2023 at 1:11 PM Robert Bradshaw via dev <de...@beam.apache.org>
wrote:

> Beam Yaml has good support for IOs and mappings, but one key missing
> feature for even writing a WordCount is the ability to do Aggregations
> [1]. While the traditional Beam primitive is GroupByKey (and
> CombineValues), we're eschewing KVs in the notion of more schema'd
> data (which has some precedence in our other languages, see the links
> below). The key components the user needs to specify are (1) the key
> fields on which the grouping will take place, (2) the fields
> (expressions?) involved in the aggregation, and (3) what aggregating
> fn to use.
>
> A straw-man example could be something like
>
> type: Aggregating
> config:
>   key: [field1, field2]
>   aggregating:
>     total_cost:
>       fn: sum
>       value: cost
>     max_cost:
>       fn: max
>       value: cost
>
> This would basically correspond to the SQL expression
>
> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as max_cost
> from table GROUP BY field1, field2"
>
> (though I'm not requiring that we use this as an implementation
> strategy). I do not think we need a separate (non aggregating)
> Grouping operation, this can be accomplished by having a concat-style
> combiner.
>
> There are still some open questions here, notably around how to
> specify the aggregation fns themselves. We could of course provide a
> number of built-ins (like SQL does). This gets into the question of
> how and where to document this complete set, but some basics should
> take us pretty far. Many aggregators, however, are parameterized (e.g.
> quantiles); where do we put the parameters? We could go with something
> like
>
> fn:
>   type: ApproximateQuantiles
>   config:
>     n: 10
>
> but others are even configured by functions themselves (e.g. LargestN
> that wants a comparator Fn). Maybe we decide not to support these
> (yet?)
>
> One thing I think we should support, however, is referencing custom
> CombineFns. We have some precedent for this with our Fns from
> MapToFields, where we accept things like inline lambdas and external
> references. Again the topic of how to configure them comes up, as
> these custom Fns are more likely to be parameterized than Map Fns
> (though, to be clear, perhaps it'd be good to allow parameterizatin of
> MapFns as well). Maybe we allow
>
> language: python. # like MapToFields (and here it'd be harder to mix
> and match per Fn)
> fn:
>   type: ???
>   # should these be nested as config?
>   name: fully.qualiied.name
>   path: /path/to/defining/file
>   args: [...]
>   kwargs: {...}
>
> which would invoke the constructor.
>
> I'm also open to other ways of naming/structuring these essential
> parameters if it makes things more clear.
>
> - Robert
>
>
> Java:
> https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
> Python:
> https://beam.apache.org/documentation/transforms/python/aggregation/groupby
> Typescript:
> https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
>
> [1] One can of course use SqlTransform for this, but I'm leaning
> towards offering something more native.
>

Re: [YAML] Aggregations

[Byron Ellis via dev <de...@beam.apache.org>]

On Thu, Oct 19, 2023 at 9:28 AM Robert Bradshaw <ro...@google.com> wrote:

> On Thu, Oct 19, 2023 at 9:00 AM Byron Ellis <by...@google.com> wrote:
> >
> > Rill is definitely SQL-oriented but I think that's going to be the most
> common. Dataframes are explicitly modeled on the relational approach so
> that's going to look a lot like SQL,
>
> I think pretty much any approach that fits here is going to be
> relational, meaning you choose a set of columns to group on, a set of
> columns to aggregate, and how to aggregate. The big open question is
> what syntax to use for the "how."
>
> Dataframe aggregation is probably a good example to look at. Here we
> have panda and R in particular as concrete instances. It should also
> be easy to support different aggregations over different (or the same)
> columns. Pandas can take a list of (or mapping to) functions in its
> groupby().agg(). R doesn't seem to make this very easy...
>

In R it's pretty similar if you're using "the tidyverse" (a colloquial name
for the R packages on which so far as I know pandas is based). Using dplyr
it would be something along the lines of

table %>% group_by(ColumnName) %>% summarize(OtherColumnAvg =
mean(OtherColumn))

(R is much more lazily evaluated than Python so the "mean(OtherColumn)" is
evaluated in the context of "table" by summarize. You are not wrong in
thinking "this is difficult to compile" :-)



>
> > which leaves us with S-style formulas (which I like but are pretty niche)
>
> I'm curious, what are these?
>

The other way of doing the above in R, which comes from S-PLUS is to use
formula constructs. It would look something like this:

aggregate(OtherColumn ~ ColumnName,data=table,mean)

~ is the "formula operator" so it creates something of type formula.

There are even older pivot table approaches as well, but that's probably
the most common pattern outside of the tidyverse. It would be technically
feasible to also do aggregate(mean(OtherColumn) + median(YetAnotherColumn)
~ ColumnName,data=table) though I can't remember if there's a variant that
supports that construct






>
> >  and I guess pivot tables coming from the spreadsheet world. Does make
> me wonder what Rails' ORM looks like these days (I last used v4), it had
> some aggregation support and was pretty declarative...
> >
> > On Wed, Oct 18, 2023 at 6:06 PM Robert Bradshaw <ro...@google.com>
> wrote:
> >>
> >> On Wed, Oct 18, 2023 at 5:06 PM Byron Ellis <by...@google.com>
> wrote:
> >> >
> >> > Is it worth taking a look at similar prior art in the space?
> >>
> >> +1. Pointers welcome.
> >>
> >> > The first one that comes to mind is Transform, but with the dbt labs
> acquisition that spec is a lot harder to find. Rill is pretty similar
> though.
> >>
> >> Rill seems to be very SQL-based.
> >>
> >> > On Wed, Oct 18, 2023 at 1:12 PM Robert Bradshaw via dev <
> dev@beam.apache.org> wrote:
> >> >>
> >> >> Beam Yaml has good support for IOs and mappings, but one key missing
> >> >> feature for even writing a WordCount is the ability to do
> Aggregations
> >> >> [1]. While the traditional Beam primitive is GroupByKey (and
> >> >> CombineValues), we're eschewing KVs in the notion of more schema'd
> >> >> data (which has some precedence in our other languages, see the links
> >> >> below). The key components the user needs to specify are (1) the key
> >> >> fields on which the grouping will take place, (2) the fields
> >> >> (expressions?) involved in the aggregation, and (3) what aggregating
> >> >> fn to use.
> >> >>
> >> >> A straw-man example could be something like
> >> >>
> >> >> type: Aggregating
> >> >> config:
> >> >>   key: [field1, field2]
> >> >>   aggregating:
> >> >>     total_cost:
> >> >>       fn: sum
> >> >>       value: cost
> >> >>     max_cost:
> >> >>       fn: max
> >> >>       value: cost
> >> >>
> >> >> This would basically correspond to the SQL expression
> >> >>
> >> >> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as
> max_cost
> >> >> from table GROUP BY field1, field2"
> >> >>
> >> >> (though I'm not requiring that we use this as an implementation
> >> >> strategy). I do not think we need a separate (non aggregating)
> >> >> Grouping operation, this can be accomplished by having a concat-style
> >> >> combiner.
> >> >>
> >> >> There are still some open questions here, notably around how to
> >> >> specify the aggregation fns themselves. We could of course provide a
> >> >> number of built-ins (like SQL does). This gets into the question of
> >> >> how and where to document this complete set, but some basics should
> >> >> take us pretty far. Many aggregators, however, are parameterized
> (e.g.
> >> >> quantiles); where do we put the parameters? We could go with
> something
> >> >> like
> >> >>
> >> >> fn:
> >> >>   type: ApproximateQuantiles
> >> >>   config:
> >> >>     n: 10
> >> >>
> >> >> but others are even configured by functions themselves (e.g. LargestN
> >> >> that wants a comparator Fn). Maybe we decide not to support these
> >> >> (yet?)
> >> >>
> >> >> One thing I think we should support, however, is referencing custom
> >> >> CombineFns. We have some precedent for this with our Fns from
> >> >> MapToFields, where we accept things like inline lambdas and external
> >> >> references. Again the topic of how to configure them comes up, as
> >> >> these custom Fns are more likely to be parameterized than Map Fns
> >> >> (though, to be clear, perhaps it'd be good to allow parameterizatin
> of
> >> >> MapFns as well). Maybe we allow
> >> >>
> >> >> language: python. # like MapToFields (and here it'd be harder to mix
> >> >> and match per Fn)
> >> >> fn:
> >> >>   type: ???
> >> >>   # should these be nested as config?
> >> >>   name: fully.qualiied.name
> >> >>   path: /path/to/defining/file
> >> >>   args: [...]
> >> >>   kwargs: {...}
> >> >>
> >> >> which would invoke the constructor.
> >> >>
> >> >> I'm also open to other ways of naming/structuring these essential
> >> >> parameters if it makes things more clear.
> >> >>
> >> >> - Robert
> >> >>
> >> >>
> >> >> Java:
> https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
> >> >> Python:
> https://beam.apache.org/documentation/transforms/python/aggregation/groupby
> >> >> Typescript:
> https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
> >> >>
> >> >> [1] One can of course use SqlTransform for this, but I'm leaning
> >> >> towards offering something more native.
>

Re: [YAML] Aggregations

[Robert Bradshaw via dev <de...@beam.apache.org>]

On Thu, Oct 19, 2023 at 11:12 AM Kenneth Knowles <ke...@apache.org> wrote:
>
> Using SQL expressions in strings is maybe OK given we are all
> relational all the time. Either way you have to define what the
> universe of `fn` is. Here's a compact possibility:
>
> type: Combine
> config:
>   group_by: [field1, field2]
>   aggregates:
>     max_cost: "MAX(cost)"
>     total_cost: "SUM(cost)"
>
> Just a thought to get it closer to SQL concision.

So I'm a bit wary of having to parse these strings (unless a language
parameter is passed in which case we defer to that language's syntax).
It's also messy the other way around, if a tool generates the YAML I'd
rather it not have to generate strings like this (i.e. string literals
should either be identifiers or opaque blobs).

Pandas achieves consciousness by allowing one to just specify, say,
'sum' and implicitly summing over all (numeric) fields, and allowing
more verbose, precise specification as well.

> I also used the word
> "Combine" just to connect it to other Beam writings and whatnot.

+1

> On Thu, Oct 19, 2023 at 1:41 PM Robert Bradshaw via dev
> <de...@beam.apache.org> wrote:
> >
> > On Thu, Oct 19, 2023 at 10:25 AM Jan Lukavský <je...@seznam.cz> wrote:
> > >
> > > On 10/19/23 18:28, Robert Bradshaw via dev wrote:
> > > > On Thu, Oct 19, 2023 at 9:00 AM Byron Ellis <by...@google.com> wrote:
> > > >> Rill is definitely SQL-oriented but I think that's going to be the most common. Dataframes are explicitly modeled on the relational approach so that's going to look a lot like SQL,
> > > > I think pretty much any approach that fits here is going to be
> > > > relational, meaning you choose a set of columns to group on, a set of
> > > > columns to aggregate, and how to aggregate. The big open question is
> > > > what syntax to use for the "how."
> > > This might be already answered, if so, pardon my ignorance, but what is
> > > the goal this declarative approach is trying to solve? Is it meant to be
> > > more expressive or equally expressive than SQL? And if more, how much more?
> >
> > I'm not sure if you're asking about YAML in general, or the particular
> > case of aggregation, but I can answer both.
> >
> > For the larger Beam YAML project, it's trying to solve the problem
> > that SQL is (and I'll admit this is somewhat subjective here) good at
> > expressing the T part of ETL, but not the other parts. For example,
> > the simple data movent usecase of (say) reading from PubSub and
> > dumping into BigQuery is not well expressed in terms of SQL. SQL is
> > also fairly awkward when it comes to defining UDFs and TDFs and
> > non-linear pipelines (especially those with fanout). There are of
> > course other tools in this space (dbt comes to mind, and there's been
> > some investigation on how to make dbt play well with Beam). The other
> > niche it is trying to solve is that installing and learning a full SDK
> > is heavyweight and overkill for creating pipelines that are simply
> > wiring together pre-defined transforms.
> >
> > As for the more narrow case of aggregations, I think being similarly
> > expressive as SQL is fine, though it'd be good to make custom UADFs
> > more natural. Originally I was thinking that just having SqlTransform
> > might be sufficient, but it feels like a big hammer to reach for every
> > time I just want to sum over one or two columns.

Re: [YAML] Aggregations

[Kenneth Knowles <ke...@apache.org>]

Using SQL expressions in strings is maybe OK given we are all
relational all the time. Either way you have to define what the
universe of `fn` is. Here's a compact possibility:

type: Combine
config:
  group_by: [field1, field2]
  aggregates:
    max_cost: "MAX(cost)"
    total_cost: "SUM(cost)"

Just a thought to get it closer to SQL concision. I also used the word
"Combine" just to connect it to other Beam writings and whatnot.

Kenn

On Thu, Oct 19, 2023 at 1:41 PM Robert Bradshaw via dev
<de...@beam.apache.org> wrote:
>
> On Thu, Oct 19, 2023 at 10:25 AM Jan Lukavský <je...@seznam.cz> wrote:
> >
> > On 10/19/23 18:28, Robert Bradshaw via dev wrote:
> > > On Thu, Oct 19, 2023 at 9:00 AM Byron Ellis <by...@google.com> wrote:
> > >> Rill is definitely SQL-oriented but I think that's going to be the most common. Dataframes are explicitly modeled on the relational approach so that's going to look a lot like SQL,
> > > I think pretty much any approach that fits here is going to be
> > > relational, meaning you choose a set of columns to group on, a set of
> > > columns to aggregate, and how to aggregate. The big open question is
> > > what syntax to use for the "how."
> > This might be already answered, if so, pardon my ignorance, but what is
> > the goal this declarative approach is trying to solve? Is it meant to be
> > more expressive or equally expressive than SQL? And if more, how much more?
>
> I'm not sure if you're asking about YAML in general, or the particular
> case of aggregation, but I can answer both.
>
> For the larger Beam YAML project, it's trying to solve the problem
> that SQL is (and I'll admit this is somewhat subjective here) good at
> expressing the T part of ETL, but not the other parts. For example,
> the simple data movent usecase of (say) reading from PubSub and
> dumping into BigQuery is not well expressed in terms of SQL. SQL is
> also fairly awkward when it comes to defining UDFs and TDFs and
> non-linear pipelines (especially those with fanout). There are of
> course other tools in this space (dbt comes to mind, and there's been
> some investigation on how to make dbt play well with Beam). The other
> niche it is trying to solve is that installing and learning a full SDK
> is heavyweight and overkill for creating pipelines that are simply
> wiring together pre-defined transforms.
>
> As for the more narrow case of aggregations, I think being similarly
> expressive as SQL is fine, though it'd be good to make custom UADFs
> more natural. Originally I was thinking that just having SqlTransform
> might be sufficient, but it feels like a big hammer to reach for every
> time I just want to sum over one or two columns.

Re: [YAML] Aggregations

[Robert Bradshaw via dev <de...@beam.apache.org>]

On Thu, Oct 19, 2023 at 11:42 AM Jan Lukavský <je...@seznam.cz> wrote:
>
> On 10/19/23 19:41, Robert Bradshaw via dev wrote:
> > On Thu, Oct 19, 2023 at 10:25 AM Jan Lukavský <je...@seznam.cz> wrote:
> >> On 10/19/23 18:28, Robert Bradshaw via dev wrote:
> >>> On Thu, Oct 19, 2023 at 9:00 AM Byron Ellis <by...@google.com> wrote:
> >>>> Rill is definitely SQL-oriented but I think that's going to be the most common. Dataframes are explicitly modeled on the relational approach so that's going to look a lot like SQL,
> >>> I think pretty much any approach that fits here is going to be
> >>> relational, meaning you choose a set of columns to group on, a set of
> >>> columns to aggregate, and how to aggregate. The big open question is
> >>> what syntax to use for the "how."
> >> This might be already answered, if so, pardon my ignorance, but what is
> >> the goal this declarative approach is trying to solve? Is it meant to be
> >> more expressive or equally expressive than SQL? And if more, how much more?
> > I'm not sure if you're asking about YAML in general, or the particular
> > case of aggregation, but I can answer both.
> >
> > For the larger Beam YAML project, it's trying to solve the problem
> > that SQL is (and I'll admit this is somewhat subjective here) good at
> > expressing the T part of ETL, but not the other parts. For example,
> > the simple data movent usecase of (say) reading from PubSub and
> > dumping into BigQuery is not well expressed in terms of SQL. SQL is
> > also fairly awkward when it comes to defining UDFs and TDFs and
> > non-linear pipelines (especially those with fanout). There are of
> > course other tools in this space (dbt comes to mind, and there's been
> > some investigation on how to make dbt play well with Beam). The other
> > niche it is trying to solve is that installing and learning a full SDK
> > is heavyweight and overkill for creating pipelines that are simply
> > wiring together pre-defined transforms.
>
> I think FlinkSQL solves the problem of E and L in SQL via CREATE TABLE
> and INSERT statements. I agree with the fanout part, though it could be
> possible to use CREATE (TEMPORARY) TABLE AS SELECT ... could solve that
> as well.

Yeah, Beam uses the CREATE TABLE for referring to external data too.
(I don't remember about INSERT statements). This is where (IMHO of
course) SQL starts to get very messy (and non-standard).

> > As for the more narrow case of aggregations, I think being similarly
> > expressive as SQL is fine, though it'd be good to make custom UADFs
> > more natural. Originally I was thinking that just having SqlTransform
> > might be sufficient, but it feels like a big hammer to reach for every
> > time I just want to sum over one or two columns.
>
> Yes, defining UDFs and UDAFs is painful, that was the motivation of my
> question. It also defines how the syntax for such UDAF would need to
> look like. It would require to break UDAFs down to several primitive
> UDFs and then use a functional style to declare them. Most of the time
> it would be probably sufficient to use simplified CombineFn semantics
> with accumulator being limited to a primitive type (long, double,
> string, maybe array?). I suppose declaring a full-blown stateful DoFn
> (timers, generic state, ...) is out of scope.

Other than the possible totally-commutative-associative V* -> V case,
I'm probably fine with referencing existing CombineFns (including ones
defined in files like we do with mapping fns) rather than providing a
full YAML syntax for defining them. But we do need to be able to
parameterize them.

Re: [YAML] Aggregations

[Jan Lukavský <je...@seznam.cz>]

On 10/19/23 19:41, Robert Bradshaw via dev wrote:
> On Thu, Oct 19, 2023 at 10:25 AM Jan Lukavský <je...@seznam.cz> wrote:
>> On 10/19/23 18:28, Robert Bradshaw via dev wrote:
>>> On Thu, Oct 19, 2023 at 9:00 AM Byron Ellis <by...@google.com> wrote:
>>>> Rill is definitely SQL-oriented but I think that's going to be the most common. Dataframes are explicitly modeled on the relational approach so that's going to look a lot like SQL,
>>> I think pretty much any approach that fits here is going to be
>>> relational, meaning you choose a set of columns to group on, a set of
>>> columns to aggregate, and how to aggregate. The big open question is
>>> what syntax to use for the "how."
>> This might be already answered, if so, pardon my ignorance, but what is
>> the goal this declarative approach is trying to solve? Is it meant to be
>> more expressive or equally expressive than SQL? And if more, how much more?
> I'm not sure if you're asking about YAML in general, or the particular
> case of aggregation, but I can answer both.
>
> For the larger Beam YAML project, it's trying to solve the problem
> that SQL is (and I'll admit this is somewhat subjective here) good at
> expressing the T part of ETL, but not the other parts. For example,
> the simple data movent usecase of (say) reading from PubSub and
> dumping into BigQuery is not well expressed in terms of SQL. SQL is
> also fairly awkward when it comes to defining UDFs and TDFs and
> non-linear pipelines (especially those with fanout). There are of
> course other tools in this space (dbt comes to mind, and there's been
> some investigation on how to make dbt play well with Beam). The other
> niche it is trying to solve is that installing and learning a full SDK
> is heavyweight and overkill for creating pipelines that are simply
> wiring together pre-defined transforms.

I think FlinkSQL solves the problem of E and L in SQL via CREATE TABLE 
and INSERT statements. I agree with the fanout part, though it could be 
possible to use CREATE (TEMPORARY) TABLE AS SELECT ... could solve that 
as well.

> As for the more narrow case of aggregations, I think being similarly
> expressive as SQL is fine, though it'd be good to make custom UADFs
> more natural. Originally I was thinking that just having SqlTransform
> might be sufficient, but it feels like a big hammer to reach for every
> time I just want to sum over one or two columns.

Yes, defining UDFs and UDAFs is painful, that was the motivation of my 
question. It also defines how the syntax for such UDAF would need to 
look like. It would require to break UDAFs down to several primitive 
UDFs and then use a functional style to declare them. Most of the time 
it would be probably sufficient to use simplified CombineFn semantics 
with accumulator being limited to a primitive type (long, double, 
string, maybe array?). I suppose declaring a full-blown stateful DoFn 
(timers, generic state, ...) is out of scope.

Re: [YAML] Aggregations

[Robert Bradshaw via dev <de...@beam.apache.org>]

On Thu, Oct 19, 2023 at 10:25 AM Jan Lukavský <je...@seznam.cz> wrote:
>
> On 10/19/23 18:28, Robert Bradshaw via dev wrote:
> > On Thu, Oct 19, 2023 at 9:00 AM Byron Ellis <by...@google.com> wrote:
> >> Rill is definitely SQL-oriented but I think that's going to be the most common. Dataframes are explicitly modeled on the relational approach so that's going to look a lot like SQL,
> > I think pretty much any approach that fits here is going to be
> > relational, meaning you choose a set of columns to group on, a set of
> > columns to aggregate, and how to aggregate. The big open question is
> > what syntax to use for the "how."
> This might be already answered, if so, pardon my ignorance, but what is
> the goal this declarative approach is trying to solve? Is it meant to be
> more expressive or equally expressive than SQL? And if more, how much more?

I'm not sure if you're asking about YAML in general, or the particular
case of aggregation, but I can answer both.

For the larger Beam YAML project, it's trying to solve the problem
that SQL is (and I'll admit this is somewhat subjective here) good at
expressing the T part of ETL, but not the other parts. For example,
the simple data movent usecase of (say) reading from PubSub and
dumping into BigQuery is not well expressed in terms of SQL. SQL is
also fairly awkward when it comes to defining UDFs and TDFs and
non-linear pipelines (especially those with fanout). There are of
course other tools in this space (dbt comes to mind, and there's been
some investigation on how to make dbt play well with Beam). The other
niche it is trying to solve is that installing and learning a full SDK
is heavyweight and overkill for creating pipelines that are simply
wiring together pre-defined transforms.

As for the more narrow case of aggregations, I think being similarly
expressive as SQL is fine, though it'd be good to make custom UADFs
more natural. Originally I was thinking that just having SqlTransform
might be sufficient, but it feels like a big hammer to reach for every
time I just want to sum over one or two columns.

Re: [YAML] Aggregations

[Jan Lukavský <je...@seznam.cz>]

On 10/19/23 18:28, Robert Bradshaw via dev wrote:
> On Thu, Oct 19, 2023 at 9:00 AM Byron Ellis <by...@google.com> wrote:
>> Rill is definitely SQL-oriented but I think that's going to be the most common. Dataframes are explicitly modeled on the relational approach so that's going to look a lot like SQL,
> I think pretty much any approach that fits here is going to be
> relational, meaning you choose a set of columns to group on, a set of
> columns to aggregate, and how to aggregate. The big open question is
> what syntax to use for the "how."
This might be already answered, if so, pardon my ignorance, but what is 
the goal this declarative approach is trying to solve? Is it meant to be 
more expressive or equally expressive than SQL? And if more, how much more?
>
> Dataframe aggregation is probably a good example to look at. Here we
> have panda and R in particular as concrete instances. It should also
> be easy to support different aggregations over different (or the same)
> columns. Pandas can take a list of (or mapping to) functions in its
> groupby().agg(). R doesn't seem to make this very easy...
>
>> which leaves us with S-style formulas (which I like but are pretty niche)
> I'm curious, what are these?
>
>>   and I guess pivot tables coming from the spreadsheet world. Does make me wonder what Rails' ORM looks like these days (I last used v4), it had some aggregation support and was pretty declarative...
>>
>> On Wed, Oct 18, 2023 at 6:06 PM Robert Bradshaw <ro...@google.com> wrote:
>>> On Wed, Oct 18, 2023 at 5:06 PM Byron Ellis <by...@google.com> wrote:
>>>> Is it worth taking a look at similar prior art in the space?
>>> +1. Pointers welcome.
>>>
>>>> The first one that comes to mind is Transform, but with the dbt labs acquisition that spec is a lot harder to find. Rill is pretty similar though.
>>> Rill seems to be very SQL-based.
>>>
>>>> On Wed, Oct 18, 2023 at 1:12 PM Robert Bradshaw via dev <de...@beam.apache.org> wrote:
>>>>> Beam Yaml has good support for IOs and mappings, but one key missing
>>>>> feature for even writing a WordCount is the ability to do Aggregations
>>>>> [1]. While the traditional Beam primitive is GroupByKey (and
>>>>> CombineValues), we're eschewing KVs in the notion of more schema'd
>>>>> data (which has some precedence in our other languages, see the links
>>>>> below). The key components the user needs to specify are (1) the key
>>>>> fields on which the grouping will take place, (2) the fields
>>>>> (expressions?) involved in the aggregation, and (3) what aggregating
>>>>> fn to use.
>>>>>
>>>>> A straw-man example could be something like
>>>>>
>>>>> type: Aggregating
>>>>> config:
>>>>>    key: [field1, field2]
>>>>>    aggregating:
>>>>>      total_cost:
>>>>>        fn: sum
>>>>>        value: cost
>>>>>      max_cost:
>>>>>        fn: max
>>>>>        value: cost
>>>>>
>>>>> This would basically correspond to the SQL expression
>>>>>
>>>>> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as max_cost
>>>>> from table GROUP BY field1, field2"
>>>>>
>>>>> (though I'm not requiring that we use this as an implementation
>>>>> strategy). I do not think we need a separate (non aggregating)
>>>>> Grouping operation, this can be accomplished by having a concat-style
>>>>> combiner.
>>>>>
>>>>> There are still some open questions here, notably around how to
>>>>> specify the aggregation fns themselves. We could of course provide a
>>>>> number of built-ins (like SQL does). This gets into the question of
>>>>> how and where to document this complete set, but some basics should
>>>>> take us pretty far. Many aggregators, however, are parameterized (e.g.
>>>>> quantiles); where do we put the parameters? We could go with something
>>>>> like
>>>>>
>>>>> fn:
>>>>>    type: ApproximateQuantiles
>>>>>    config:
>>>>>      n: 10
>>>>>
>>>>> but others are even configured by functions themselves (e.g. LargestN
>>>>> that wants a comparator Fn). Maybe we decide not to support these
>>>>> (yet?)
>>>>>
>>>>> One thing I think we should support, however, is referencing custom
>>>>> CombineFns. We have some precedent for this with our Fns from
>>>>> MapToFields, where we accept things like inline lambdas and external
>>>>> references. Again the topic of how to configure them comes up, as
>>>>> these custom Fns are more likely to be parameterized than Map Fns
>>>>> (though, to be clear, perhaps it'd be good to allow parameterizatin of
>>>>> MapFns as well). Maybe we allow
>>>>>
>>>>> language: python. # like MapToFields (and here it'd be harder to mix
>>>>> and match per Fn)
>>>>> fn:
>>>>>    type: ???
>>>>>    # should these be nested as config?
>>>>>    name: fully.qualiied.name
>>>>>    path: /path/to/defining/file
>>>>>    args: [...]
>>>>>    kwargs: {...}
>>>>>
>>>>> which would invoke the constructor.
>>>>>
>>>>> I'm also open to other ways of naming/structuring these essential
>>>>> parameters if it makes things more clear.
>>>>>
>>>>> - Robert
>>>>>
>>>>>
>>>>> Java: https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
>>>>> Python: https://beam.apache.org/documentation/transforms/python/aggregation/groupby
>>>>> Typescript: https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
>>>>>
>>>>> [1] One can of course use SqlTransform for this, but I'm leaning
>>>>> towards offering something more native.

Re: [YAML] Aggregations

[Robert Bradshaw via dev <de...@beam.apache.org>]

On Thu, Oct 19, 2023 at 9:00 AM Byron Ellis <by...@google.com> wrote:
>
> Rill is definitely SQL-oriented but I think that's going to be the most common. Dataframes are explicitly modeled on the relational approach so that's going to look a lot like SQL,

I think pretty much any approach that fits here is going to be
relational, meaning you choose a set of columns to group on, a set of
columns to aggregate, and how to aggregate. The big open question is
what syntax to use for the "how."

Dataframe aggregation is probably a good example to look at. Here we
have panda and R in particular as concrete instances. It should also
be easy to support different aggregations over different (or the same)
columns. Pandas can take a list of (or mapping to) functions in its
groupby().agg(). R doesn't seem to make this very easy...

> which leaves us with S-style formulas (which I like but are pretty niche)

I'm curious, what are these?

>  and I guess pivot tables coming from the spreadsheet world. Does make me wonder what Rails' ORM looks like these days (I last used v4), it had some aggregation support and was pretty declarative...
>
> On Wed, Oct 18, 2023 at 6:06 PM Robert Bradshaw <ro...@google.com> wrote:
>>
>> On Wed, Oct 18, 2023 at 5:06 PM Byron Ellis <by...@google.com> wrote:
>> >
>> > Is it worth taking a look at similar prior art in the space?
>>
>> +1. Pointers welcome.
>>
>> > The first one that comes to mind is Transform, but with the dbt labs acquisition that spec is a lot harder to find. Rill is pretty similar though.
>>
>> Rill seems to be very SQL-based.
>>
>> > On Wed, Oct 18, 2023 at 1:12 PM Robert Bradshaw via dev <de...@beam.apache.org> wrote:
>> >>
>> >> Beam Yaml has good support for IOs and mappings, but one key missing
>> >> feature for even writing a WordCount is the ability to do Aggregations
>> >> [1]. While the traditional Beam primitive is GroupByKey (and
>> >> CombineValues), we're eschewing KVs in the notion of more schema'd
>> >> data (which has some precedence in our other languages, see the links
>> >> below). The key components the user needs to specify are (1) the key
>> >> fields on which the grouping will take place, (2) the fields
>> >> (expressions?) involved in the aggregation, and (3) what aggregating
>> >> fn to use.
>> >>
>> >> A straw-man example could be something like
>> >>
>> >> type: Aggregating
>> >> config:
>> >>   key: [field1, field2]
>> >>   aggregating:
>> >>     total_cost:
>> >>       fn: sum
>> >>       value: cost
>> >>     max_cost:
>> >>       fn: max
>> >>       value: cost
>> >>
>> >> This would basically correspond to the SQL expression
>> >>
>> >> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as max_cost
>> >> from table GROUP BY field1, field2"
>> >>
>> >> (though I'm not requiring that we use this as an implementation
>> >> strategy). I do not think we need a separate (non aggregating)
>> >> Grouping operation, this can be accomplished by having a concat-style
>> >> combiner.
>> >>
>> >> There are still some open questions here, notably around how to
>> >> specify the aggregation fns themselves. We could of course provide a
>> >> number of built-ins (like SQL does). This gets into the question of
>> >> how and where to document this complete set, but some basics should
>> >> take us pretty far. Many aggregators, however, are parameterized (e.g.
>> >> quantiles); where do we put the parameters? We could go with something
>> >> like
>> >>
>> >> fn:
>> >>   type: ApproximateQuantiles
>> >>   config:
>> >>     n: 10
>> >>
>> >> but others are even configured by functions themselves (e.g. LargestN
>> >> that wants a comparator Fn). Maybe we decide not to support these
>> >> (yet?)
>> >>
>> >> One thing I think we should support, however, is referencing custom
>> >> CombineFns. We have some precedent for this with our Fns from
>> >> MapToFields, where we accept things like inline lambdas and external
>> >> references. Again the topic of how to configure them comes up, as
>> >> these custom Fns are more likely to be parameterized than Map Fns
>> >> (though, to be clear, perhaps it'd be good to allow parameterizatin of
>> >> MapFns as well). Maybe we allow
>> >>
>> >> language: python. # like MapToFields (and here it'd be harder to mix
>> >> and match per Fn)
>> >> fn:
>> >>   type: ???
>> >>   # should these be nested as config?
>> >>   name: fully.qualiied.name
>> >>   path: /path/to/defining/file
>> >>   args: [...]
>> >>   kwargs: {...}
>> >>
>> >> which would invoke the constructor.
>> >>
>> >> I'm also open to other ways of naming/structuring these essential
>> >> parameters if it makes things more clear.
>> >>
>> >> - Robert
>> >>
>> >>
>> >> Java: https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
>> >> Python: https://beam.apache.org/documentation/transforms/python/aggregation/groupby
>> >> Typescript: https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
>> >>
>> >> [1] One can of course use SqlTransform for this, but I'm leaning
>> >> towards offering something more native.

Re: [YAML] Aggregations

[Byron Ellis via dev <de...@beam.apache.org>]

Rill is definitely SQL-oriented but I think that's going to be the most
common. Dataframes are explicitly modeled on the relational approach so
that's going to look a lot like SQL, which leaves us with S-style formulas
(which I like but are pretty niche) and I guess pivot tables coming from
the spreadsheet world. Does make me wonder what Rails' ORM looks like these
days (I last used v4), it had some aggregation support and was pretty
declarative...

On Wed, Oct 18, 2023 at 6:06 PM Robert Bradshaw <ro...@google.com> wrote:

> On Wed, Oct 18, 2023 at 5:06 PM Byron Ellis <by...@google.com> wrote:
> >
> > Is it worth taking a look at similar prior art in the space?
>
> +1. Pointers welcome.
>
> > The first one that comes to mind is Transform, but with the dbt labs
> acquisition that spec is a lot harder to find. Rill is pretty similar
> though.
>
> Rill seems to be very SQL-based.
>
> > On Wed, Oct 18, 2023 at 1:12 PM Robert Bradshaw via dev <
> dev@beam.apache.org> wrote:
> >>
> >> Beam Yaml has good support for IOs and mappings, but one key missing
> >> feature for even writing a WordCount is the ability to do Aggregations
> >> [1]. While the traditional Beam primitive is GroupByKey (and
> >> CombineValues), we're eschewing KVs in the notion of more schema'd
> >> data (which has some precedence in our other languages, see the links
> >> below). The key components the user needs to specify are (1) the key
> >> fields on which the grouping will take place, (2) the fields
> >> (expressions?) involved in the aggregation, and (3) what aggregating
> >> fn to use.
> >>
> >> A straw-man example could be something like
> >>
> >> type: Aggregating
> >> config:
> >>   key: [field1, field2]
> >>   aggregating:
> >>     total_cost:
> >>       fn: sum
> >>       value: cost
> >>     max_cost:
> >>       fn: max
> >>       value: cost
> >>
> >> This would basically correspond to the SQL expression
> >>
> >> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as max_cost
> >> from table GROUP BY field1, field2"
> >>
> >> (though I'm not requiring that we use this as an implementation
> >> strategy). I do not think we need a separate (non aggregating)
> >> Grouping operation, this can be accomplished by having a concat-style
> >> combiner.
> >>
> >> There are still some open questions here, notably around how to
> >> specify the aggregation fns themselves. We could of course provide a
> >> number of built-ins (like SQL does). This gets into the question of
> >> how and where to document this complete set, but some basics should
> >> take us pretty far. Many aggregators, however, are parameterized (e.g.
> >> quantiles); where do we put the parameters? We could go with something
> >> like
> >>
> >> fn:
> >>   type: ApproximateQuantiles
> >>   config:
> >>     n: 10
> >>
> >> but others are even configured by functions themselves (e.g. LargestN
> >> that wants a comparator Fn). Maybe we decide not to support these
> >> (yet?)
> >>
> >> One thing I think we should support, however, is referencing custom
> >> CombineFns. We have some precedent for this with our Fns from
> >> MapToFields, where we accept things like inline lambdas and external
> >> references. Again the topic of how to configure them comes up, as
> >> these custom Fns are more likely to be parameterized than Map Fns
> >> (though, to be clear, perhaps it'd be good to allow parameterizatin of
> >> MapFns as well). Maybe we allow
> >>
> >> language: python. # like MapToFields (and here it'd be harder to mix
> >> and match per Fn)
> >> fn:
> >>   type: ???
> >>   # should these be nested as config?
> >>   name: fully.qualiied.name
> >>   path: /path/to/defining/file
> >>   args: [...]
> >>   kwargs: {...}
> >>
> >> which would invoke the constructor.
> >>
> >> I'm also open to other ways of naming/structuring these essential
> >> parameters if it makes things more clear.
> >>
> >> - Robert
> >>
> >>
> >> Java:
> https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
> >> Python:
> https://beam.apache.org/documentation/transforms/python/aggregation/groupby
> >> Typescript:
> https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
> >>
> >> [1] One can of course use SqlTransform for this, but I'm leaning
> >> towards offering something more native.
>

Re: [YAML] Aggregations

[Robert Burke <ro...@frantil.com>]

MongoDB has its own concept of aggregation pipelines as well.

https://www.mongodb.com/docs/manual/core/aggregation-pipeline/#std-label-aggregation-pipeline


On Wed, Oct 18, 2023, 6:07 PM Robert Bradshaw via dev <de...@beam.apache.org>
wrote:

> On Wed, Oct 18, 2023 at 5:06 PM Byron Ellis <by...@google.com> wrote:
> >
> > Is it worth taking a look at similar prior art in the space?
>
> +1. Pointers welcome.
>
> > The first one that comes to mind is Transform, but with the dbt labs
> acquisition that spec is a lot harder to find. Rill is pretty similar
> though.
>
> Rill seems to be very SQL-based.
>
> > On Wed, Oct 18, 2023 at 1:12 PM Robert Bradshaw via dev <
> dev@beam.apache.org> wrote:
> >>
> >> Beam Yaml has good support for IOs and mappings, but one key missing
> >> feature for even writing a WordCount is the ability to do Aggregations
> >> [1]. While the traditional Beam primitive is GroupByKey (and
> >> CombineValues), we're eschewing KVs in the notion of more schema'd
> >> data (which has some precedence in our other languages, see the links
> >> below). The key components the user needs to specify are (1) the key
> >> fields on which the grouping will take place, (2) the fields
> >> (expressions?) involved in the aggregation, and (3) what aggregating
> >> fn to use.
> >>
> >> A straw-man example could be something like
> >>
> >> type: Aggregating
> >> config:
> >>   key: [field1, field2]
> >>   aggregating:
> >>     total_cost:
> >>       fn: sum
> >>       value: cost
> >>     max_cost:
> >>       fn: max
> >>       value: cost
> >>
> >> This would basically correspond to the SQL expression
> >>
> >> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as max_cost
> >> from table GROUP BY field1, field2"
> >>
> >> (though I'm not requiring that we use this as an implementation
> >> strategy). I do not think we need a separate (non aggregating)
> >> Grouping operation, this can be accomplished by having a concat-style
> >> combiner.
> >>
> >> There are still some open questions here, notably around how to
> >> specify the aggregation fns themselves. We could of course provide a
> >> number of built-ins (like SQL does). This gets into the question of
> >> how and where to document this complete set, but some basics should
> >> take us pretty far. Many aggregators, however, are parameterized (e.g.
> >> quantiles); where do we put the parameters? We could go with something
> >> like
> >>
> >> fn:
> >>   type: ApproximateQuantiles
> >>   config:
> >>     n: 10
> >>
> >> but others are even configured by functions themselves (e.g. LargestN
> >> that wants a comparator Fn). Maybe we decide not to support these
> >> (yet?)
> >>
> >> One thing I think we should support, however, is referencing custom
> >> CombineFns. We have some precedent for this with our Fns from
> >> MapToFields, where we accept things like inline lambdas and external
> >> references. Again the topic of how to configure them comes up, as
> >> these custom Fns are more likely to be parameterized than Map Fns
> >> (though, to be clear, perhaps it'd be good to allow parameterizatin of
> >> MapFns as well). Maybe we allow
> >>
> >> language: python. # like MapToFields (and here it'd be harder to mix
> >> and match per Fn)
> >> fn:
> >>   type: ???
> >>   # should these be nested as config?
> >>   name: fully.qualiied.name
> >>   path: /path/to/defining/file
> >>   args: [...]
> >>   kwargs: {...}
> >>
> >> which would invoke the constructor.
> >>
> >> I'm also open to other ways of naming/structuring these essential
> >> parameters if it makes things more clear.
> >>
> >> - Robert
> >>
> >>
> >> Java:
> https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
> >> Python:
> https://beam.apache.org/documentation/transforms/python/aggregation/groupby
> >> Typescript:
> https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
> >>
> >> [1] One can of course use SqlTransform for this, but I'm leaning
> >> towards offering something more native.
>

Re: [YAML] Aggregations

[Robert Bradshaw via dev <de...@beam.apache.org>]

On Wed, Oct 18, 2023 at 5:06 PM Byron Ellis <by...@google.com> wrote:
>
> Is it worth taking a look at similar prior art in the space?

+1. Pointers welcome.

> The first one that comes to mind is Transform, but with the dbt labs acquisition that spec is a lot harder to find. Rill is pretty similar though.

Rill seems to be very SQL-based.

> On Wed, Oct 18, 2023 at 1:12 PM Robert Bradshaw via dev <de...@beam.apache.org> wrote:
>>
>> Beam Yaml has good support for IOs and mappings, but one key missing
>> feature for even writing a WordCount is the ability to do Aggregations
>> [1]. While the traditional Beam primitive is GroupByKey (and
>> CombineValues), we're eschewing KVs in the notion of more schema'd
>> data (which has some precedence in our other languages, see the links
>> below). The key components the user needs to specify are (1) the key
>> fields on which the grouping will take place, (2) the fields
>> (expressions?) involved in the aggregation, and (3) what aggregating
>> fn to use.
>>
>> A straw-man example could be something like
>>
>> type: Aggregating
>> config:
>>   key: [field1, field2]
>>   aggregating:
>>     total_cost:
>>       fn: sum
>>       value: cost
>>     max_cost:
>>       fn: max
>>       value: cost
>>
>> This would basically correspond to the SQL expression
>>
>> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as max_cost
>> from table GROUP BY field1, field2"
>>
>> (though I'm not requiring that we use this as an implementation
>> strategy). I do not think we need a separate (non aggregating)
>> Grouping operation, this can be accomplished by having a concat-style
>> combiner.
>>
>> There are still some open questions here, notably around how to
>> specify the aggregation fns themselves. We could of course provide a
>> number of built-ins (like SQL does). This gets into the question of
>> how and where to document this complete set, but some basics should
>> take us pretty far. Many aggregators, however, are parameterized (e.g.
>> quantiles); where do we put the parameters? We could go with something
>> like
>>
>> fn:
>>   type: ApproximateQuantiles
>>   config:
>>     n: 10
>>
>> but others are even configured by functions themselves (e.g. LargestN
>> that wants a comparator Fn). Maybe we decide not to support these
>> (yet?)
>>
>> One thing I think we should support, however, is referencing custom
>> CombineFns. We have some precedent for this with our Fns from
>> MapToFields, where we accept things like inline lambdas and external
>> references. Again the topic of how to configure them comes up, as
>> these custom Fns are more likely to be parameterized than Map Fns
>> (though, to be clear, perhaps it'd be good to allow parameterizatin of
>> MapFns as well). Maybe we allow
>>
>> language: python. # like MapToFields (and here it'd be harder to mix
>> and match per Fn)
>> fn:
>>   type: ???
>>   # should these be nested as config?
>>   name: fully.qualiied.name
>>   path: /path/to/defining/file
>>   args: [...]
>>   kwargs: {...}
>>
>> which would invoke the constructor.
>>
>> I'm also open to other ways of naming/structuring these essential
>> parameters if it makes things more clear.
>>
>> - Robert
>>
>>
>> Java: https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
>> Python: https://beam.apache.org/documentation/transforms/python/aggregation/groupby
>> Typescript: https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
>>
>> [1] One can of course use SqlTransform for this, but I'm leaning
>> towards offering something more native.

Re: [YAML] Aggregations

[Byron Ellis via dev <de...@beam.apache.org>]

Is it worth taking a look at similar prior art in the space? The first one
that comes to mind is Transform, but with the dbt labs acquisition that
spec is a lot harder to find. Rill
<https://docs.rilldata.com/example-projects> is pretty similar though.

On Wed, Oct 18, 2023 at 1:12 PM Robert Bradshaw via dev <de...@beam.apache.org>
wrote:

> Beam Yaml has good support for IOs and mappings, but one key missing
> feature for even writing a WordCount is the ability to do Aggregations
> [1]. While the traditional Beam primitive is GroupByKey (and
> CombineValues), we're eschewing KVs in the notion of more schema'd
> data (which has some precedence in our other languages, see the links
> below). The key components the user needs to specify are (1) the key
> fields on which the grouping will take place, (2) the fields
> (expressions?) involved in the aggregation, and (3) what aggregating
> fn to use.
>
> A straw-man example could be something like
>
> type: Aggregating
> config:
>   key: [field1, field2]
>   aggregating:
>     total_cost:
>       fn: sum
>       value: cost
>     max_cost:
>       fn: max
>       value: cost
>
> This would basically correspond to the SQL expression
>
> "SELECT field1, field2, sum(cost) as total_cost, max(cost) as max_cost
> from table GROUP BY field1, field2"
>
> (though I'm not requiring that we use this as an implementation
> strategy). I do not think we need a separate (non aggregating)
> Grouping operation, this can be accomplished by having a concat-style
> combiner.
>
> There are still some open questions here, notably around how to
> specify the aggregation fns themselves. We could of course provide a
> number of built-ins (like SQL does). This gets into the question of
> how and where to document this complete set, but some basics should
> take us pretty far. Many aggregators, however, are parameterized (e.g.
> quantiles); where do we put the parameters? We could go with something
> like
>
> fn:
>   type: ApproximateQuantiles
>   config:
>     n: 10
>
> but others are even configured by functions themselves (e.g. LargestN
> that wants a comparator Fn). Maybe we decide not to support these
> (yet?)
>
> One thing I think we should support, however, is referencing custom
> CombineFns. We have some precedent for this with our Fns from
> MapToFields, where we accept things like inline lambdas and external
> references. Again the topic of how to configure them comes up, as
> these custom Fns are more likely to be parameterized than Map Fns
> (though, to be clear, perhaps it'd be good to allow parameterizatin of
> MapFns as well). Maybe we allow
>
> language: python. # like MapToFields (and here it'd be harder to mix
> and match per Fn)
> fn:
>   type: ???
>   # should these be nested as config?
>   name: fully.qualiied.name
>   path: /path/to/defining/file
>   args: [...]
>   kwargs: {...}
>
> which would invoke the constructor.
>
> I'm also open to other ways of naming/structuring these essential
> parameters if it makes things more clear.
>
> - Robert
>
>
> Java:
> https://beam.apache.org/releases/javadoc/current/org/apache/beam/sdk/schemas/transforms/Group.html
> Python:
> https://beam.apache.org/documentation/transforms/python/aggregation/groupby
> Typescript:
> https://beam.apache.org/releases/typedoc/current/classes/transforms_group_and_combine.GroupBy.html
>
> [1] One can of course use SqlTransform for this, but I'm leaning
> towards offering something more native.
>