Thank you for the comprehensive response, Jing =)
For example, We often need to consume a CSV file.
> Generally, the first line of the file contains a list of column names, and
> subsequent lines of the file contain data.
> However, different CSV files may have different schemas.
> We could offer a table function named CSVreader which interprets this file
FROM TABLE (
> CSVreader (File => 'abc.csv')
> )
Ah yes this seems about what I was hoping for!
I assume somebody could write custom function to do something like this:
SELECT *
FROM TABLE (
HttpReader(
method => 'GET'
url => 'https://api.mysite.com/users/1'
)
)
Where "https://api.mysite.com/users/1"; returns JSON data like:
{ "id": 1, "name": "Some Person", "is_registered": true }
And then we can infer table definition:
TABLE (
id INT
name TEXT
is_registered BOOLEAN
)
And read JSON as row(s):
id | name | is_registered
1 | some | true
On Sun, Jul 31, 2022 at 10:37 AM Jing Zhang <[email protected]> wrote:
> Hi,
> Thanks for your attention.
> Yes, we hope to provide full support for polymorphic table functions.
> We have made some progress in version 1.31.0 which will be released soon.
> Any feedback is welcome.
>
> I'm not sure whether the polymorphic table function could satisfy your
> requirement because I don't know your demands very well yet.
> It would be very helpful if you could provide some more detailed
> description.
>
> Here, I would like to share something about polymorphic table function.
> A polymorphic table function is a function that returns a table whose row
> type is not declared when the function is created.
> Rather, the row type of the result may depend on the function arguments in
> the invocation of a PTF,
> and therefore may vary depending on the precise syntax containing the PTF
> invocation.
> In addition, a PTF may have generic table parameters (i.e., no row type
> declared when the PTF is created),
> and the row type of the result might depend on the row type(s) of the input
> tables.
>
> For example, We often need to consume a CSV file.
> Generally, the first line of the file contains a list of column names, and
> subsequent lines of the file contain data.
> However, different CSV files may have different schemas.
> We could offer a table function named CSVreader which interprets this file
> as a table.
> For example, there is a csv file named abc.csv with the following contents:
>
> docno,name,due_date,principle,interest
> 123,Mary,01/01/2014,234.56,345.67
> 234,Edgar,01/01/2014,654.32,543.21
>
>
> the query author may write a query such as the following:
>
>
> SELECT *
> FROM TABLE (
> CSVreader (
> File => 'abc.csv',
> Floats => DESCRIPTOR ("principle", "interest")
> Dates => DESCRIPTOR ("due_date")
> )
> ) AS S
>
>
> The result will be
> docnonamedue_dateprincipleinterest
> 123 Mary 01/01/2014 234.56 345.67
> 234 Edgar 01/01/2014 654.32 543.21
>
>
> More examples could be found in the description of CALCITE-4865[1].
> Users could define a custom polymorphic table function by inheriting a
> related interface[2].
>
> Best,
> Jing Zhang
>
> [1] https://issues.apache.org/jira/browse/CALCITE-4865
> [2]
>
> https://github.com/apache/calcite/blob/main/core/src/main/java/org/apache/calcite/sql/SqlTableFunction.java
>
> Gavin Ray <[email protected]> 于2022年7月30日周六 07:27写道：
>
> > I see there's a recent commit which references this:
> > [CALCITE-4864] Supports Polymorphic Table function - ASF JIRA (
> apache.org)
> > <https://issues.apache.org/jira/browse/CALCITE-4864>
> >
> > Something Teiid [0] did was to use JSON_TABLE() from dynamic data at
> > runtime.
> >
> > I had an opportunity to speak to the maintainers, and they said this was
> a
> > key strategy in writing adapters
> > for non-relational sources like Couchbase, MongoDB, and the Salesforce
> API,
> > etc.
> >
> > If I am understanding correctly, does this new polymorphic table
> > functionality unlock the
> > ability to have Calcite consume runtime data and use it with table-valued
> > functions?
> >
> > If so, that's huge! Would love to whip up a demo of an HTTP request UDF
> > that does a JSON_TABLE on the output.
> >
> > If not, I'd still love to learn what this functionality is all about and
> > what you might do with it =)
> >
> > ----
> >
> > [0]: teiid/teiid: Teiid is a data virtualization system that allows
> > applications to use data from multiple, heterogenous data stores.
> > (github.com) <https://github.com/teiid/teiid>
> >
>
