You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@arrow.apache.org by "alamb (via GitHub)" <gi...@apache.org> on 2023/10/25 16:27:50 UTC
[I] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
alamb opened a new issue, #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926
### Is your feature request related to a problem or challenge?
It is sometimes helpful to have a custom table functions to extend DataFusion's functionality. As we continue to get more feature requests, such as #7859 it is important to support such usecases without having to add everything to the DataFusion core.
For example in the following query `my_custom_fun` is a table function
```sql
SELECT foo, bar FROM my_custom_fun(city='NYC', year=2020)
```
A specific example might be a function that fetches the contents of remote csv file and parses it into a table.
```sql
SELECT date, value FROM parse_remote_csv('https://data.wa.gov/api/views/f6w7-q2d2/rows.csv?accessType=DOWNLOAD')
```
You can do something similar to this with a [TableProvider](https://docs.rs/datafusion/latest/datafusion/datasource/provider/trait.TableProvider.html), but the main differences are:
1. A `TableProvider` has no way to pass parameters
2. A `TableProvider's schema is fixed (it can't be a function of the parameters)
## Prior Art
Other examples include the [`read_parquet` etc functions in DuckDB](https://duckdb.org/docs/data/parquet/overview.html)
```sql
SELECT * FROM read_parquet(['folder1/*.parquet', 'folder2/*.parquet']);
...
SELECT * FROM parquet_schema('test.parquet');
```
### Describe the solution you'd like
I would like to be able to have a table function that supported everything a TableProvider does, including filter and projection pushdown. One way to do so would be to return a TableProvder:
# Option 1: Add to `FunctionRegistry`:
We could add Table Functions to the [datafusion](https://docs.rs/datafusion/latest/datafusion/index.html)::[execution](https://docs.rs/datafusion/latest/datafusion/execution/index.html)::[FunctionRegistry](https://docs.rs/datafusion/latest/datafusion/execution/trait.FunctionRegistry.html#) along with the UDFs, UDAs, etc which arguably would make them easier to discover
Something like
```rust
trait FunctionRegistry {
...
/// Return a `TableProvider` for executing the `name` table function
fn udtf(name: &str, args: &[Expr]) -> Result<Arc<dyn TableProvider>>;
}
```
We would probably also need a
```rust
trait TableUDF {
/// Return a `TableProvider` for executing this table function, given the specified
/// arguments
fn invoke(name: &str, args: &[Expr]) -> Result<Arc<dyn TableProvider>>;
}
```
### Describe alternatives you've considered
This API is very powerful and would allow Table Functions to do anything a table provider does. We could also offer a stripped down version of the API potentially
We can probably add something like [datafusion](https://docs.rs/datafusion/latest/datafusion/index.html)::[logical_expr](https://docs.rs/datafusion/latest/datafusion/logical_expr/index.html)::[create_udf](https://docs.rs/datafusion/latest/datafusion/logical_expr/fn.create_udf.html#) to make it easier to construct basic table functions (e.g that produce a single `SendableRecordBatchStream`)
# : Add to `SchemaProvider`:
We could also adding Table Functions to [datafusion](https://docs.rs/datafusion/latest/datafusion/index.html)::[catalog](https://docs.rs/datafusion/latest/datafusion/catalog/index.html)::[schema](https://docs.rs/datafusion/latest/datafusion/catalog/schema/index.html)::[SchemaProvider](https://docs.rs/datafusion/latest/datafusion/catalog/schema/trait.SchemaProvider.html#)
This might make sense given how similar `TableFunction`s are to `TableProvider`s
```rust
trait SchemaProvider {
...
/// Return a `TableProvider` for executing the `name` table function, given the specified
/// arguments
fn table_function(name: &str) -> Result<Arc<dyn TableProvider>>;
/// Register the `TableFunction` with the specified name, returning the previously registered function, if any
fn register_table_function(name: &str) -> Result<Option<Arc<dyn TableProvider>>>;
...
}
```
### Additional context
I thought there was an existing ticket for this, but I can not find one
This came up several times, including:
* https://github.com/apache/arrow-datafusion/issues/6518
* On discord: https://discord.com/channels/885562378132000778/1166447479609376850/1166702178916892793
--
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] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb closed issue #7926: Support User Defined Table Functions / Table Value Functions
URL: https://github.com/apache/arrow-datafusion/issues/7926
--
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] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "Veeupup (via GitHub)" <gi...@apache.org>.
Veeupup commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1810461601
Before User Defined Table Functions, I can not find Internal Table Function either.
So I suppose that maybe we can start by implmenting the Internal Table Functions first, just like
```sql
SELECT * FROM read_parquet(['folder1/*.parquet', 'folder2/*.parquet']);
```
how do you think @alamb : )
--
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] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "Veeupup (via GitHub)" <gi...@apache.org>.
Veeupup commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1807130464
I'm interested by this topic! Seems like a very powerful feature! : )
And I want to study the related codes and figure it out to learn more about `datafusion`!
Now it looks a little difficult for me, but maybe I can help in future weeks if I can handle 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: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1810921112
I think implementing something like `read_parquet` woudl be great (as an example perhaps in https://github.com/apache/arrow-datafusion/tree/main/datafusion-examples/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@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "yukkit (via GitHub)" <gi...@apache.org>.
yukkit commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1812390523
> I would like to be able to have a table function that supported everything a `TableProvider` does, including filter and projection pushdown. One way to do so would be to actually return a `TableProvider`:
>
Agreed, that sounds like a feasible approach.
>
> ```rust
> trait TableUDF {
> /// Return a `TableProvider` for executing this table function, given the specified
> /// arguments
> fn invoke(name: &str, args: &[Expr]) -> Result<Arc<dyn TableProvider>>;
> }
> ```
>
FYI. Should table-valued function inputs allow variables or only constants (like 'literal value' or 'now()')?
> ###### Add to `SchemaProvider`:
> We could also add Table Functions to [datafusion](https://docs.rs/datafusion/latest/datafusion/index.html)::[catalog](https://docs.rs/datafusion/latest/datafusion/catalog/index.html)::[schema](https://docs.rs/datafusion/latest/datafusion/catalog/schema/index.html)::[SchemaProvider](https://docs.rs/datafusion/latest/datafusion/catalog/schema/trait.SchemaProvider.html#)
>
> This might make sense given how similar `TableFunction`s are to `TableProvider`s
>
> ```rust
> trait SchemaProvider {
> ...
> /// Return a `TableProvider` for executing the `name` table function, given the specified
> /// arguments
> fn table_function(name: &str) -> Result<Arc<dyn TableProvider>>;
>
> /// Register the `TableFunction` with the specified name, returning the previously registered function, if any
> fn register_table_function(name: &str) -> Result<Option<Arc<dyn TableProvider>>>;
> ...
> }
> ```
I prefer this approach because table-valued functions resemble a table more and the processing logic is also more similar.
--
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] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1812987913
> FYI. Should table-valued function inputs allow variables or only constants (like 'literal value' or 'now()')?
It depends on what you mean by 'variables'. I think it would be very reasonable to accept `@@var` type variables from the VariableProvider
However, I think it would be much harder to accept things like `Expr::Column` as it is not clear to me from where such input would come? Would it be other tables?
--
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] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "Jesse-Bakker (via GitHub)" <gi...@apache.org>.
Jesse-Bakker commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1825642666
A valuable specialization for table-valued function is being able to use them as de-aggregating expressions, if the output is single-column. An example of this is, for example, the [`unnest`](https://www.postgresql.org/docs/16/functions-array.html#ARRAY-FUNCTIONS-TABLE) function in postgres. The first form takes a single argument and returns `setof anyelement` and can therefore be used in a select list to deaggregate:
```
# select 1, unnest(ARRAY[1,2,3]);
?column? | unnest
----------+--------
1 | 1
1 | 2
1 | 3
```
The second form takes multiple arrays as arguments and returns records. Therefore, this form can only be used in table-factor position
```
select * from unnest(array[1,2], array['a', 'b']);
unnest | unnest
--------+--------
1 | a
2 | b
(2 rows)
```
--
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] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "Veeupup (via GitHub)" <gi...@apache.org>.
Veeupup commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1821199588
I'm going to make a draft PR this week : )
--
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] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1834684681
Follow on work is tracked in https://github.com/apache/arrow-datafusion/issues/8383
--
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] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1829681867
@Veeupup has a nice PR up that I think looks pretty close to implementing TableFunctions -- https://github.com/apache/arrow-datafusion/pull/8306 if you are interested please take a look
--
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] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1815122785
> Sorry for not being clear earlier, what I meant is exactly how you understood it.
I saw some use cases for correlated subquery in the [PostgreSQL documentation](https://www.postgresql.org/docs/7.3/xfunc-tablefunctions.html)
That is interesting -- I am not sure how we would make this work - maybe sketching out what the plan would look like would help. It seems like it would effectively have to make a `TableProvider` instance for each matching row 🤔 Our typical trick of rewriting to a join probably would not work in this case)
--
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] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "lewiszlw (via GitHub)" <gi...@apache.org>.
lewiszlw commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1821983060
This is a very useful feature. Really looking forward it can be landed next release.
--
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] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "alamb (via GitHub)" <gi...@apache.org>.
alamb commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1808618241
> I'm interested by this topic! Seems like a very powerful feature! : )
Yes, I think so
>
> And I want to study the related codes and figure it out to learn more about `datafusion`!
Thank you! It is very much appreciated.
>
> Now it looks a little difficult for me, but maybe I can help in future weeks if I can handle it.
Thank you.
My suggestion is to first make a RFC / proposal PR (for example like https://github.com/apache/arrow-datafusion/pull/8046) that sketches out the main APIs and identifies any potential problems / areas that need additional work prior to trying implement a fully mergeable PR. This would allow us to solidify the design without wasted effort on polishing code / tests
--
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] Support User Defined Table Functions / Table Value Functions [arrow-datafusion]
Posted by "yukkit (via GitHub)" <gi...@apache.org>.
yukkit commented on issue #7926:
URL: https://github.com/apache/arrow-datafusion/issues/7926#issuecomment-1813692801
> However, I think it would be much harder to accept things like `Expr::Column` as it is not clear to me from where such input would come? Would it be other tables?
Sorry for not being clear earlier, what I meant is exactly how you understood it.
I saw some use cases for correlated subquery in the [PostgreSQL documentation](https://www.postgresql.org/docs/7.3/xfunc-tablefunctions.html)
```
CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE FUNCTION getfoo(int) RETURNS setof foo AS '
SELECT * FROM foo WHERE fooid = $1;
' LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
SELECT * FROM foo
WHERE foosubid in (select foosubid from getfoo(foo.fooid) z
where z.fooid = foo.fooid);
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
```
--
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