You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Gavin Ray <ra...@gmail.com> on 2022/05/31 15:35:46 UTC
Implementing JSON_TABLE, even hackily as a UDF?
I'm interested in implementing JSON_TABLE functionality for Calcite
This opens up some neat usecases, like adding HTTP request UDF's
then using JSON_TABLE to convert the result into a table:
SELECT JSON_TABLE(
HTTP_GET('http://localhost:8080/api/v1/users/1'))
Adding support for all of the functionality seems difficult,
but I'm wondering whether this could be done as a "SqlTableFunction" UDF?
I'm thinking it might be possible if the JSON_TABLE udf
expects Map<String, Object> and does inference based on that?
Something like:
WITH users AS (
SELECT HTTP_GET('http://localhost:8080/api/v1/users')
) SELECT
id,
name
FROM
JSON_TABLE(JSON_PATH(users, "$.0"))
Does anyone have ideas or see issues with this approach?
Thank you =)
Re: Implementing JSON_TABLE, even hackily as a UDF?
Posted by Gavin Ray <ra...@gmail.com>.
I think you're probably right, the functionality for reading JSON must
exist in the File adapter already
Then I'd just need to figure out how to read from "HTTP_GET" UDF as the
JSON contents
Ty, will go look at the source for the adapter
On Tue, May 31, 2022 at 6:51 PM Julian Hyde <jh...@gmail.com> wrote:
> Is there any overlap with the file adapter? The file adapter can read
> using various transports (file, http, optional compression) and various
> formats (csv, json, html tables).
>
> > On May 31, 2022, at 12:47 PM, Gavin Ray <ra...@gmail.com> wrote:
> >
> > I don't think it's SQL Standard, but essentially it takes a text value
> > containing JSON data (one record or an array) and converts it into a
> > relational table:
> >
> > https://docs.oracle.com/database/121/SQLRF/functions092.htm
> >
> > I've managed to get most of this working, now I seem to be failing to put
> > the syntax together correctly for the query
> > I currently have:
> > - A UDF ("HTTP_GET") which can make HTTP calls and return the data as a
> > String
> > - A table type for Collection<Map<String, Object>> ("MapCollectionTable")
> > - A TableFunctionImpl ("JsonTableFunction"), which takes a JSON string,
> > reads it with Jackson, and converts it into a "MapCollectionTable"
> >
> > The following work:
> >
> > SELECT HTTP_GET('https://jsonplaceholder.typicode.com/posts')
> >
> > SELECT * FROM TABLE(
> > JSON_TABLE('[{ "id": "1", "name": "foo" }, { "id": "2", "name": "bar"
> > }]'))
> >
> > But trying to combine them fails =/
> >
> > SELECT * FROM TABLE(
> > JSON_TABLE(HTTP_GET('https://jsonplaceholder.typicode.com/posts')))
> >
> > The TableFunctionImpl is receiving "null" for the JSON value
> > I guess it's maybe expecting static data?
> >
> > Calcite JSON_TABLE wip 2 (github.com)
> > <
> https://gist.github.com/GavinRay97/fbd16dd2b893cb59c720e6d514c5e39a#file-main-java
> >
> >
> > On Tue, May 31, 2022 at 2:44 PM Julian Hyde <jh...@apache.org> wrote:
> >
> >> What is JSON_TABLE? Is it in the SQL standard? Can you give a simple
> >> example?
> >>
> >> On Tue, May 31, 2022 at 10:03 AM Gavin Ray <ra...@gmail.com>
> wrote:
> >>>
> >>> I dug up some examples from Github of using the "TableFunctionImpl"
> >> class:
> >>>
> >>> mat-calcite-plugin/TableFunctions.java at
> >>> a57a5ba80768066714c22bd9e8a9b529d4cb9a6b · vlsi/mat-calcite-plugin
> >>> (github.com)
> >>> <
> >>
> https://github.com/vlsi/mat-calcite-plugin/blob/a57a5ba80768066714c22bd9e8a9b529d4cb9a6b/MatCalcitePlugin/src/com/github/vlsi/mat/calcite/functions/TableFunctions.java
> >>>
> >>> Calcite_sql_driver/MazeTable.java at
> >>> 6819088123e67631367ca927b112b58e4eb90829 · dream001/Calcite_sql_driver
> >>> (github.com)
> >>> <
> >>
> https://github.com/dream001/Calcite_sql_driver/blob/6819088123e67631367ca927b112b58e4eb90829/src/test/java/com/yonyou/calcite/MazeTable.java
> >>>
> >>>
> >>> Currently trying to get a TableFunction that just returns a Map<String,
> >>> Object> as a relation to work
> >>> Have the below -- calling this shows that the function IS invoked, but
> >> the
> >>> data it gives back is garbage:
> >>>
> >>> Calcite JSON_TABLE WIP (github.com)
> >>> <https://gist.github.com/GavinRay97/b42c69992a0d0c3a01f1c7e1b9ab1999>
> >>>
> >>> This is the output of main()
> >>>
> >>> json = {"id":1, "name":"John"}
> >>> json = {"id":1, "name":"John"}
> >>> json = {"id":1, "name":"John"}
> >>> EXPR$0: id
> >>> EXPR$0: id
> >>>
> >>> I'm doing something wrong here for sure, lol.
> >>>
> >>>
> >>> On Tue, May 31, 2022 at 11:35 AM Gavin Ray <ra...@gmail.com>
> >> wrote:
> >>>
> >>>> I'm interested in implementing JSON_TABLE functionality for Calcite
> >>>>
> >>>> This opens up some neat usecases, like adding HTTP request UDF's
> >>>> then using JSON_TABLE to convert the result into a table:
> >>>>
> >>>> SELECT JSON_TABLE(
> >>>> HTTP_GET('http://localhost:8080/api/v1/users/1'))
> >>>>
> >>>> Adding support for all of the functionality seems difficult,
> >>>> but I'm wondering whether this could be done as a "SqlTableFunction"
> >> UDF?
> >>>>
> >>>> I'm thinking it might be possible if the JSON_TABLE udf
> >>>> expects Map<String, Object> and does inference based on that?
> >>>>
> >>>> Something like:
> >>>>
> >>>> WITH users AS (
> >>>> SELECT HTTP_GET('http://localhost:8080/api/v1/users')
> >>>> ) SELECT
> >>>> id,
> >>>> name
> >>>> FROM
> >>>> JSON_TABLE(JSON_PATH(users, "$.0"))
> >>>>
> >>>> Does anyone have ideas or see issues with this approach?
> >>>> Thank you =)
> >>>>
> >>>>
> >>>>
> >>>>
> >>
>
>
Re: Implementing JSON_TABLE, even hackily as a UDF?
Posted by Julian Hyde <jh...@gmail.com>.
Is there any overlap with the file adapter? The file adapter can read using various transports (file, http, optional compression) and various formats (csv, json, html tables).
> On May 31, 2022, at 12:47 PM, Gavin Ray <ra...@gmail.com> wrote:
>
> I don't think it's SQL Standard, but essentially it takes a text value
> containing JSON data (one record or an array) and converts it into a
> relational table:
>
> https://docs.oracle.com/database/121/SQLRF/functions092.htm
>
> I've managed to get most of this working, now I seem to be failing to put
> the syntax together correctly for the query
> I currently have:
> - A UDF ("HTTP_GET") which can make HTTP calls and return the data as a
> String
> - A table type for Collection<Map<String, Object>> ("MapCollectionTable")
> - A TableFunctionImpl ("JsonTableFunction"), which takes a JSON string,
> reads it with Jackson, and converts it into a "MapCollectionTable"
>
> The following work:
>
> SELECT HTTP_GET('https://jsonplaceholder.typicode.com/posts')
>
> SELECT * FROM TABLE(
> JSON_TABLE('[{ "id": "1", "name": "foo" }, { "id": "2", "name": "bar"
> }]'))
>
> But trying to combine them fails =/
>
> SELECT * FROM TABLE(
> JSON_TABLE(HTTP_GET('https://jsonplaceholder.typicode.com/posts')))
>
> The TableFunctionImpl is receiving "null" for the JSON value
> I guess it's maybe expecting static data?
>
> Calcite JSON_TABLE wip 2 (github.com)
> <https://gist.github.com/GavinRay97/fbd16dd2b893cb59c720e6d514c5e39a#file-main-java>
>
> On Tue, May 31, 2022 at 2:44 PM Julian Hyde <jh...@apache.org> wrote:
>
>> What is JSON_TABLE? Is it in the SQL standard? Can you give a simple
>> example?
>>
>> On Tue, May 31, 2022 at 10:03 AM Gavin Ray <ra...@gmail.com> wrote:
>>>
>>> I dug up some examples from Github of using the "TableFunctionImpl"
>> class:
>>>
>>> mat-calcite-plugin/TableFunctions.java at
>>> a57a5ba80768066714c22bd9e8a9b529d4cb9a6b · vlsi/mat-calcite-plugin
>>> (github.com)
>>> <
>> https://github.com/vlsi/mat-calcite-plugin/blob/a57a5ba80768066714c22bd9e8a9b529d4cb9a6b/MatCalcitePlugin/src/com/github/vlsi/mat/calcite/functions/TableFunctions.java
>>>
>>> Calcite_sql_driver/MazeTable.java at
>>> 6819088123e67631367ca927b112b58e4eb90829 · dream001/Calcite_sql_driver
>>> (github.com)
>>> <
>> https://github.com/dream001/Calcite_sql_driver/blob/6819088123e67631367ca927b112b58e4eb90829/src/test/java/com/yonyou/calcite/MazeTable.java
>>>
>>>
>>> Currently trying to get a TableFunction that just returns a Map<String,
>>> Object> as a relation to work
>>> Have the below -- calling this shows that the function IS invoked, but
>> the
>>> data it gives back is garbage:
>>>
>>> Calcite JSON_TABLE WIP (github.com)
>>> <https://gist.github.com/GavinRay97/b42c69992a0d0c3a01f1c7e1b9ab1999>
>>>
>>> This is the output of main()
>>>
>>> json = {"id":1, "name":"John"}
>>> json = {"id":1, "name":"John"}
>>> json = {"id":1, "name":"John"}
>>> EXPR$0: id
>>> EXPR$0: id
>>>
>>> I'm doing something wrong here for sure, lol.
>>>
>>>
>>> On Tue, May 31, 2022 at 11:35 AM Gavin Ray <ra...@gmail.com>
>> wrote:
>>>
>>>> I'm interested in implementing JSON_TABLE functionality for Calcite
>>>>
>>>> This opens up some neat usecases, like adding HTTP request UDF's
>>>> then using JSON_TABLE to convert the result into a table:
>>>>
>>>> SELECT JSON_TABLE(
>>>> HTTP_GET('http://localhost:8080/api/v1/users/1'))
>>>>
>>>> Adding support for all of the functionality seems difficult,
>>>> but I'm wondering whether this could be done as a "SqlTableFunction"
>> UDF?
>>>>
>>>> I'm thinking it might be possible if the JSON_TABLE udf
>>>> expects Map<String, Object> and does inference based on that?
>>>>
>>>> Something like:
>>>>
>>>> WITH users AS (
>>>> SELECT HTTP_GET('http://localhost:8080/api/v1/users')
>>>> ) SELECT
>>>> id,
>>>> name
>>>> FROM
>>>> JSON_TABLE(JSON_PATH(users, "$.0"))
>>>>
>>>> Does anyone have ideas or see issues with this approach?
>>>> Thank you =)
>>>>
>>>>
>>>>
>>>>
>>
Re: Implementing JSON_TABLE, even hackily as a UDF?
Posted by Gavin Ray <ra...@gmail.com>.
I don't think it's SQL Standard, but essentially it takes a text value
containing JSON data (one record or an array) and converts it into a
relational table:
https://docs.oracle.com/database/121/SQLRF/functions092.htm
I've managed to get most of this working, now I seem to be failing to put
the syntax together correctly for the query
I currently have:
- A UDF ("HTTP_GET") which can make HTTP calls and return the data as a
String
- A table type for Collection<Map<String, Object>> ("MapCollectionTable")
- A TableFunctionImpl ("JsonTableFunction"), which takes a JSON string,
reads it with Jackson, and converts it into a "MapCollectionTable"
The following work:
SELECT HTTP_GET('https://jsonplaceholder.typicode.com/posts')
SELECT * FROM TABLE(
JSON_TABLE('[{ "id": "1", "name": "foo" }, { "id": "2", "name": "bar"
}]'))
But trying to combine them fails =/
SELECT * FROM TABLE(
JSON_TABLE(HTTP_GET('https://jsonplaceholder.typicode.com/posts')))
The TableFunctionImpl is receiving "null" for the JSON value
I guess it's maybe expecting static data?
Calcite JSON_TABLE wip 2 (github.com)
<https://gist.github.com/GavinRay97/fbd16dd2b893cb59c720e6d514c5e39a#file-main-java>
On Tue, May 31, 2022 at 2:44 PM Julian Hyde <jh...@apache.org> wrote:
> What is JSON_TABLE? Is it in the SQL standard? Can you give a simple
> example?
>
> On Tue, May 31, 2022 at 10:03 AM Gavin Ray <ra...@gmail.com> wrote:
> >
> > I dug up some examples from Github of using the "TableFunctionImpl"
> class:
> >
> > mat-calcite-plugin/TableFunctions.java at
> > a57a5ba80768066714c22bd9e8a9b529d4cb9a6b · vlsi/mat-calcite-plugin
> > (github.com)
> > <
> https://github.com/vlsi/mat-calcite-plugin/blob/a57a5ba80768066714c22bd9e8a9b529d4cb9a6b/MatCalcitePlugin/src/com/github/vlsi/mat/calcite/functions/TableFunctions.java
> >
> > Calcite_sql_driver/MazeTable.java at
> > 6819088123e67631367ca927b112b58e4eb90829 · dream001/Calcite_sql_driver
> > (github.com)
> > <
> https://github.com/dream001/Calcite_sql_driver/blob/6819088123e67631367ca927b112b58e4eb90829/src/test/java/com/yonyou/calcite/MazeTable.java
> >
> >
> > Currently trying to get a TableFunction that just returns a Map<String,
> > Object> as a relation to work
> > Have the below -- calling this shows that the function IS invoked, but
> the
> > data it gives back is garbage:
> >
> > Calcite JSON_TABLE WIP (github.com)
> > <https://gist.github.com/GavinRay97/b42c69992a0d0c3a01f1c7e1b9ab1999>
> >
> > This is the output of main()
> >
> > json = {"id":1, "name":"John"}
> > json = {"id":1, "name":"John"}
> > json = {"id":1, "name":"John"}
> > EXPR$0: id
> > EXPR$0: id
> >
> > I'm doing something wrong here for sure, lol.
> >
> >
> > On Tue, May 31, 2022 at 11:35 AM Gavin Ray <ra...@gmail.com>
> wrote:
> >
> > > I'm interested in implementing JSON_TABLE functionality for Calcite
> > >
> > > This opens up some neat usecases, like adding HTTP request UDF's
> > > then using JSON_TABLE to convert the result into a table:
> > >
> > > SELECT JSON_TABLE(
> > > HTTP_GET('http://localhost:8080/api/v1/users/1'))
> > >
> > > Adding support for all of the functionality seems difficult,
> > > but I'm wondering whether this could be done as a "SqlTableFunction"
> UDF?
> > >
> > > I'm thinking it might be possible if the JSON_TABLE udf
> > > expects Map<String, Object> and does inference based on that?
> > >
> > > Something like:
> > >
> > > WITH users AS (
> > > SELECT HTTP_GET('http://localhost:8080/api/v1/users')
> > > ) SELECT
> > > id,
> > > name
> > > FROM
> > > JSON_TABLE(JSON_PATH(users, "$.0"))
> > >
> > > Does anyone have ideas or see issues with this approach?
> > > Thank you =)
> > >
> > >
> > >
> > >
>
Re: Implementing JSON_TABLE, even hackily as a UDF?
Posted by Julian Hyde <jh...@apache.org>.
What is JSON_TABLE? Is it in the SQL standard? Can you give a simple example?
On Tue, May 31, 2022 at 10:03 AM Gavin Ray <ra...@gmail.com> wrote:
>
> I dug up some examples from Github of using the "TableFunctionImpl" class:
>
> mat-calcite-plugin/TableFunctions.java at
> a57a5ba80768066714c22bd9e8a9b529d4cb9a6b · vlsi/mat-calcite-plugin
> (github.com)
> <https://github.com/vlsi/mat-calcite-plugin/blob/a57a5ba80768066714c22bd9e8a9b529d4cb9a6b/MatCalcitePlugin/src/com/github/vlsi/mat/calcite/functions/TableFunctions.java>
> Calcite_sql_driver/MazeTable.java at
> 6819088123e67631367ca927b112b58e4eb90829 · dream001/Calcite_sql_driver
> (github.com)
> <https://github.com/dream001/Calcite_sql_driver/blob/6819088123e67631367ca927b112b58e4eb90829/src/test/java/com/yonyou/calcite/MazeTable.java>
>
> Currently trying to get a TableFunction that just returns a Map<String,
> Object> as a relation to work
> Have the below -- calling this shows that the function IS invoked, but the
> data it gives back is garbage:
>
> Calcite JSON_TABLE WIP (github.com)
> <https://gist.github.com/GavinRay97/b42c69992a0d0c3a01f1c7e1b9ab1999>
>
> This is the output of main()
>
> json = {"id":1, "name":"John"}
> json = {"id":1, "name":"John"}
> json = {"id":1, "name":"John"}
> EXPR$0: id
> EXPR$0: id
>
> I'm doing something wrong here for sure, lol.
>
>
> On Tue, May 31, 2022 at 11:35 AM Gavin Ray <ra...@gmail.com> wrote:
>
> > I'm interested in implementing JSON_TABLE functionality for Calcite
> >
> > This opens up some neat usecases, like adding HTTP request UDF's
> > then using JSON_TABLE to convert the result into a table:
> >
> > SELECT JSON_TABLE(
> > HTTP_GET('http://localhost:8080/api/v1/users/1'))
> >
> > Adding support for all of the functionality seems difficult,
> > but I'm wondering whether this could be done as a "SqlTableFunction" UDF?
> >
> > I'm thinking it might be possible if the JSON_TABLE udf
> > expects Map<String, Object> and does inference based on that?
> >
> > Something like:
> >
> > WITH users AS (
> > SELECT HTTP_GET('http://localhost:8080/api/v1/users')
> > ) SELECT
> > id,
> > name
> > FROM
> > JSON_TABLE(JSON_PATH(users, "$.0"))
> >
> > Does anyone have ideas or see issues with this approach?
> > Thank you =)
> >
> >
> >
> >
Re: Implementing JSON_TABLE, even hackily as a UDF?
Posted by Gavin Ray <ra...@gmail.com>.
I dug up some examples from Github of using the "TableFunctionImpl" class:
mat-calcite-plugin/TableFunctions.java at
a57a5ba80768066714c22bd9e8a9b529d4cb9a6b · vlsi/mat-calcite-plugin
(github.com)
<https://github.com/vlsi/mat-calcite-plugin/blob/a57a5ba80768066714c22bd9e8a9b529d4cb9a6b/MatCalcitePlugin/src/com/github/vlsi/mat/calcite/functions/TableFunctions.java>
Calcite_sql_driver/MazeTable.java at
6819088123e67631367ca927b112b58e4eb90829 · dream001/Calcite_sql_driver
(github.com)
<https://github.com/dream001/Calcite_sql_driver/blob/6819088123e67631367ca927b112b58e4eb90829/src/test/java/com/yonyou/calcite/MazeTable.java>
Currently trying to get a TableFunction that just returns a Map<String,
Object> as a relation to work
Have the below -- calling this shows that the function IS invoked, but the
data it gives back is garbage:
Calcite JSON_TABLE WIP (github.com)
<https://gist.github.com/GavinRay97/b42c69992a0d0c3a01f1c7e1b9ab1999>
This is the output of main()
json = {"id":1, "name":"John"}
json = {"id":1, "name":"John"}
json = {"id":1, "name":"John"}
EXPR$0: id
EXPR$0: id
I'm doing something wrong here for sure, lol.
On Tue, May 31, 2022 at 11:35 AM Gavin Ray <ra...@gmail.com> wrote:
> I'm interested in implementing JSON_TABLE functionality for Calcite
>
> This opens up some neat usecases, like adding HTTP request UDF's
> then using JSON_TABLE to convert the result into a table:
>
> SELECT JSON_TABLE(
> HTTP_GET('http://localhost:8080/api/v1/users/1'))
>
> Adding support for all of the functionality seems difficult,
> but I'm wondering whether this could be done as a "SqlTableFunction" UDF?
>
> I'm thinking it might be possible if the JSON_TABLE udf
> expects Map<String, Object> and does inference based on that?
>
> Something like:
>
> WITH users AS (
> SELECT HTTP_GET('http://localhost:8080/api/v1/users')
> ) SELECT
> id,
> name
> FROM
> JSON_TABLE(JSON_PATH(users, "$.0"))
>
> Does anyone have ideas or see issues with this approach?
> Thank you =)
>
>
>
>