You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "universalmind303 (via GitHub)" <gi...@apache.org> on 2024/04/16 20:40:59 UTC
[I] select multiple columns in a single `Expr` [arrow-datafusion]
universalmind303 opened a new issue, #10102:
URL: https://github.com/apache/arrow-datafusion/issues/10102
### Is your feature request related to a problem or challenge?
I want to create a udf that can select multiple functions at once, such as a `COLUMNS(<regex>)` function.
`select COLUMNS('number\d+') from my_table`.
Currently this is not possible due to the fact that udfs can only ever output a single `Expr`.
### Describe the solution you'd like
Since it would be quite a massive overhaul to refactor all of the planning and udf logic to return `Vec<Expr>`, I propose adding a new variant to `Expr`.
`Expr::Columns(Vec<Column>)`.
This seems like the least invasive way to support selecting multiple columns in a single expr.
### Describe alternatives you've considered
I'm open for alternatives, but I am not aware of any.
### Additional context
Polars has this variant in their `Expr`
https://github.com/pola-rs/polars/blob/9fec2ecb6d4295969e1d155b386ee82db08745a1/crates/polars-plan/src/dsl/expr.rs#L72
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2077598602
> @alamb by the way, how does the struct udf works?
I think https://datafusion.apache.org/user-guide/sql/scalar_functions.html#struct has some pretty good examples
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "edmondop (via GitHub)" <gi...@apache.org>.
edmondop commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2076070730
```
> select struct(column1, column2) from abc;
+---------------------------------+
| struct(abc.column1,abc.column2) |
+---------------------------------+
| {c0: 1, c1: 2} |
| {c0: 4, c1: 5} |
+---------------------------------+
2 row(s) fetched.
Elapsed 0.004 seconds.
```
that works, but isn't this unrelated to this change? I suppose it was working before too...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2073688926
> My current wip has a struct since it supports different data types and field names, can a List work as well?
No, you are right, a List can't really support multiple different types
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2114676662
> However, in the case of COLUMNS('number\d+'), you need to have all the columns, and only return few of them from the function
I agree, we can't get all the columns by the current design of function, it is quite challenging than I thought 🤔
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2076054990
I wonder if `struct(col1, col2)` or `named_struct('a', col1, 'b', col2)` would work for your usecase 🤔
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2100049278
> > > How does the selection happens ?
> >
> >
> > I think you can select with something like `my_struct_col['foo']` which returns the 'foo' field.
> > However now re-reading this ticket, I think it would be possible to create a user defined function like `COLUMN` by Implemening [`return_type_from_exprs`](https://docs.rs/datafusion/latest/datafusion/logical_expr/trait.ScalarUDFImpl.html#method.return_type_from_exprs) to 🤔
>
> What I was trying to say is that looking at that UDF isn't clear for me how the other fields are dropped. It seems that Datafusion performs an intersection between the field names returned by the return type and the ones returned by array_struct ?
@edmondop
By default the column name is named c0, c1 ... which defined in `array_struct`, so in `return_type` we also find the associated column with name c0, c1 ...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "edmondop (via GitHub)" <gi...@apache.org>.
edmondop commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2073586262
My current wip has a struct since it supports different data types and field names, can a List work as well?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "edmondop (via GitHub)" <gi...@apache.org>.
edmondop commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2068274441
This would require also modifying the protobuf definitions, are we ok with it?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2116401821
> sql_to_expr
`sql_to_expr` returns `Result<Expr>`, we can extend it to `Result<Vec<Expr>>`. Which function are you mentioning?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "edmondop (via GitHub)" <gi...@apache.org>.
edmondop commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2076011028
@universalmind303 I think it would be interesting to test it, do you have an example / have you written the UDF?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2116402130
> > @jayzhan211 that function already returns a Vec<Expr>, or am I missing something? Do we still need to have udtf?
>
> `sql_to_expr` returns `Result<Expr>`, we can extend it to `Result<Vec<Expr>>`. Which function are you mentioning?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "edmondop (via GitHub)" <gi...@apache.org>.
edmondop commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2113997737
I want to create a udf that can select multiple functions at once, such as a COLUMNS(<regex>) function.
```sql
select COLUMNS('number\d+') from my_table.
```
however, looking at the struct UDF, it seems that it only receives the columns that are passed to function invocation and doesn't have access to other columns, i.e. in the `struct` udf, all columns are returned as a struct, which suggests that the engine only pass a subset of the available columns
However, in the case of COLUMNS('number\d+'), you need to have all the columns, and only return few of them from the function. In my understanding neither `udf` nor `udaf` or `udwf` from what I have seen. We would need maybe a fourth type of udf something like `udgf` which is invoked on all the columns of a logical plan?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "universalmind303 (via GitHub)" <gi...@apache.org>.
universalmind303 commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2081815788
FWIW, https://github.com/apache/datafusion/issues/10102 seems related as multi selection could be implemented via `struct` and `unnest`.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "universalmind303 (via GitHub)" <gi...@apache.org>.
universalmind303 commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2068258720
> @universalmind303 are you already working on this?
no, feel free to go ahead and work on it if you'd like.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "edmondop (via GitHub)" <gi...@apache.org>.
edmondop commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2076124167
@alamb by the way, how does the `struct` udf works? I am not sure I understand how this select only a subset of the fields, does Datafusion drop from the resulting dataset the fields that are not in the `return_type` ? https://github.com/apache/datafusion/blob/main/datafusion/functions/src/core/struct.rs#L97
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [arrow-datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #10102:
URL: https://github.com/apache/arrow-datafusion/issues/10102#issuecomment-2066638258
It looks good to me, and maybe we can gradually deprecate single column in the long-term
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "edmondop (via GitHub)" <gi...@apache.org>.
edmondop commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2116387764
@jayzhan211 that function already returns a `Vec<Expr>`, or am I missing something? Do we still need to have udtf?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "edmondop (via GitHub)" <gi...@apache.org>.
edmondop commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2077611758
Apologies I wasn't precise in my question. The code of the struct udf doesn't explicitly drop fields but returns all of them.
How does the selection happens ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2077825989
> How does the selection happens ?
I think you can select with something like `my_struct_col['foo']` which returns the 'foo' field.
However now re-reading this ticket, I think it would be possible to create a user defined function like `COLUMN` by Implemening [`return_type_from_exprs`](https://docs.rs/datafusion/latest/datafusion/logical_expr/trait.ScalarUDFImpl.html#method.return_type_from_exprs) to
🤔
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "jayzhan211 (via GitHub)" <gi...@apache.org>.
jayzhan211 commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2119060296
I didn't find equivalent behavior in postgres. I'm not sure should we support this kind of `returns subset of columns based on column name matching`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "edmondop (via GitHub)" <gi...@apache.org>.
edmondop commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2067786656
@universalmind303 are you already working on this?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@datafusion.apache.org
For additional commands, e-mail: commits-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2073564437
How about returning `DataType::Struct` or `DataType::List` or `DataType::FixedSizeList`?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [I] select multiple columns in a single `Expr` [datafusion]
Posted by "edmondop (via GitHub)" <gi...@apache.org>.
edmondop commented on issue #10102:
URL: https://github.com/apache/datafusion/issues/10102#issuecomment-2080824673
> > How does the selection happens ?
>
> I think you can select with something like `my_struct_col['foo']` which returns the 'foo' field.
>
> However now re-reading this ticket, I think it would be possible to create a user defined function like `COLUMN` by Implemening [`return_type_from_exprs`](https://docs.rs/datafusion/latest/datafusion/logical_expr/trait.ScalarUDFImpl.html#method.return_type_from_exprs) to 🤔
What I was trying to say is that looking at that UDF isn't clear for me how the other fields are dropped. It seems that Datafusion performs an intersection between the field names returned by the return type and the ones returned by array_struct ?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org