You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Shuyi Chen <su...@gmail.com> on 2018/03/28 06:59:56 UTC

Supporting named row construction in Calcite SQL

Hi community,

I am thinking of adding the following support in Calcite to support named
row construction, e.g.

SELECT (a as first_name, b as last_name, (c as zip code, d as street, e as
state) as address) as record FROM example_table

The output will be struct with field names specified in the SQL. The usage
scenario is that say, in streaming SQL, the downstream sink's schema can
not be changed, so we will need to use SQL to construct a struct with the
proper naming according to the schema in order to write to the downstream
sinks. Thanks a lot.

Shuyi

-- 
"So you have to trust that the dots will somehow connect in your future."

Re: Supporting named row construction in Calcite SQL

Posted by Julian Hyde <jh...@apache.org>.
Yes. “Ask Julian”.

The picture is in a Google Slides document. I don’t want to share it publicly, and I don’t want to check in a massive .SVG file.

I don’t think it’s worth the effort to make this small part of the process scalable.


> On Apr 16, 2018, at 5:22 PM, Shuyi Chen <su...@gmail.com> wrote:
> 
> Thanks a lot, Julian. Do we have instructions on how to update the website
> and the picture documented?
> 
> On Mon, Apr 16, 2018 at 4:42 AM, Michael Mior <mmior@uwaterloo.ca <ma...@uwaterloo.ca>> wrote:
> 
>> Thanks Julian!
>> 
>> --
>> Michael Mior
>> mmior@apache.org <ma...@apache.org>
>> 
>> --
>> Michael Mior
>> mmior@uwaterloo.ca <ma...@uwaterloo.ca>
>> 
>> 2018-04-16 2:26 GMT-04:00 Julian Hyde <jhyde@apache.org <ma...@apache.org>>:
>> 
>>> Beam and HerdDB are now on “powered by”: https://calcite.apache.org/ <https://calcite.apache.org/>
>>> docs/powered_by.html <https://calcite.apache.org/docs/powered_by.html <https://calcite.apache.org/docs/powered_by.html>>
>>> 
>>>> On Apr 12, 2018, at 5:33 AM, Michael Mior <mmior@uwaterloo.ca <ma...@uwaterloo.ca>> wrote:
>>>> 
>>>> FYI, I talked to Julian earlier this week and he will be adding Beam to
>>> the
>>>> powered by page since he has a doc for generating the image with the
>>> logos.
>>>> 
>>>> --
>>>> Michael Mior
>>>> mmior@apache.org <ma...@apache.org>
>>>> 
>>>> 2018-04-12 4:06 GMT-04:00 Shuyi Chen <suez1224@gmail.com <ma...@gmail.com>>:
>>>> 
>>>>> @Andrew, great to see BEAM is also using Calcite streaming SQL. Maybe
>>> you
>>>>> can help adding an entry in the Calcite powered_by page
>>>>> <https://calcite.apache.org/docs/powered_by.html <https://calcite.apache.org/docs/powered_by.html>> for BEAM  by
>> editing
>>>>> site/_docs/powered_by.md
>>>>> <https://github.com/apache/calcite/pull/657#diff- <https://github.com/apache/calcite/pull/657#diff->
>>>>> 1aa810bc92051b46555ee4caf24bd6c3>.
>>>>> Also,
>>>>> can you explain a bit more on how you plan to use streaming SQL to
>>>>> transform
>>>>> arbitrary JSON objects with and w/o the AS STRUCT syntax?
>>>>> For adding DDL in BEAM, please take a look at the server module and
>> the
>>>>> TYPE DDL (https://issues.apache.org/jira/browse/CALCITE-2045 <https://issues.apache.org/jira/browse/CALCITE-2045>) I am
>>> adding.
>>>>> Let me know if you have any comments and need any help.
>>>>> 
>>>>> @Rong, I think it's better to conform with the ROW SQL standard, and
>> add
>>>>> new grammar to handle named struct construction.
>>>>> This should work with the TYPE DDL, and we should be able to CAST the
>>>>> created STRUCT as a custom type defined using DDL.
>>>>> 
>>>>> @Julian, thanks for the suggestions. I think the AS STRUCT addition
>>> should
>>>>> do it.
>>>>> 
>>>>> Shuyi
>>>>> 
>>>>> On Mon, Apr 9, 2018 at 4:36 AM, Julian Hyde <jhyde.apache@gmail.com <ma...@gmail.com>>
>>>>> wrote:
>>>>> 
>>>>>> For what it’s worth, ROW is standard SQL. If it does what you need,
>> we
>>>>>> should use it.
>>>>>> 
>>>>>> Reading your case quickly, I perceived that you needed a concise way
>> to
>>>>>> assign field names, and AS STRUCT seemed to do that.
>>>>>> 
>>>>>> But staying within the standard is always preferred. BigQuery isn’t
>>>>> always
>>>>>> good at that.
>>>>>> 
>>>>>> Julian
>>>>>> 
>>>>>>> On Apr 5, 2018, at 10:18, Rong Rong <walterddr@gmail.com <ma...@gmail.com>> wrote:
>>>>>>> 
>>>>>>> Thanks for the fantastic proposal @shuyi. I think the STRUCT idea is
>>>>>> great
>>>>>>> considering ROW is not standard SQL either. As a user of calcite I
>>>>> have a
>>>>>>> couple questions.
>>>>>>> 
>>>>>>> Since ROW constructor is so similar with STRUCT, would it be a good
>>>>> idea
>>>>>> to
>>>>>>> consolidate the two syntax? Or have a clear distinction between?
>>>>>>> 
>>>>>>> Whats the relationship going forward with DDL, for example
>>>>> CALCITE-2045.
>>>>>>> DDL seems more flexible in terms of defining the structure not just
>> on
>>>>>>> field names but also field types. Maybe @andrew can share more on
>> the
>>>>> use
>>>>>>> cases on calcite on beam steaming integration?
>>>>>>> 
>>>>>>> Thanks,
>>>>>>> Rong
>>>>>>> 
>>>>>>> 
>>>>>>> On Thu, Apr 5, 2018, 10:05 AM Andrew Pilloud
>>>>> <apilloud@google.com.invalid <ma...@google.com.invalid>
>>>>>>> 
>>>>>>> wrote:
>>>>>>> 
>>>>>>>> As a user of Calcite working on adding streaming SQL to Apache Beam
>>>>> this
>>>>>>>> sounds like a fantastic proposal. Our initial goal is to be able to
>>>>> run
>>>>>> SQL
>>>>>>>> queries that transform arbitrary JSON objects. Without this syntax
>>>>>> objects
>>>>>>>> must be flattened when they pass through the transform. Is this
>>>>>> something
>>>>>>>> that might make it into 1.17?
>>>>>>>> 
>>>>>>>> We have also had some discussion about adding DDL to Beam so a user
>>>>> can
>>>>>>>> describe the schema of a stream of JSON in pure SQL. Our current
>>>>> though
>>>>>> is
>>>>>>>> to use Big Query compatible STRUCT and ARRAY syntax. Big Query is a
>>>>>> popular
>>>>>>>> sink for our users. Syntax compatible with Big Query would be a big
>>>>> plus
>>>>>>>> for us.
>>>>>>>> 
>>>>>>>> Andrew
>>>>>>>> 
>>>>>>>>> On Thu, Apr 5, 2018 at 12:43 AM Shuyi Chen <suez1224@gmail.com <ma...@gmail.com>>
>>>>> wrote:
>>>>>>>>> 
>>>>>>>>> @Michael, @Albert, yes, I dont think it is SQL standard. But I
>> think
>>>>>> it's
>>>>>>>>> very useful in the context of streaming SQL, e.g. Flink SQL, where
>>>>> the
>>>>>>>>> sinks can be a database or endpoints with defined protobuf/thrift
>>>>>> schema.
>>>>>>>>> They usually have complex structure. Supporting complex structure
>> in
>>>>>> SQL
>>>>>>>>> output will make it much easier to write to different sinks with
>>>>>>>> predefined
>>>>>>>>> schemas in a unified way,
>>>>>>>>> 
>>>>>>>>> @julian, that's great suggestion, I think instead of extending the
>>>>> ROW
>>>>>>>>> constructor, which is not SQL standard, adding a new extension
>> might
>>>>> be
>>>>>>>> the
>>>>>>>>> right way to go. Looking at the STRUCT big query syntax, we can
>>>>>> implement
>>>>>>>>> something like the following:
>>>>>>>>> 
>>>>>>>>> SELECT STRUCT(a as first_name, b as last_name, STRUCT(c as zip
>> code,
>>>>> d
>>>>>> as
>>>>>>>>> street, e as state) as address) as record FROM example_table
>>>>>>>>> 
>>>>>>>>> On Wed, Apr 4, 2018 at 5:51 PM, Julian Hyde <
>> jhyde.apache@gmail.com
>>>> 
>>>>>>>>> wrote:
>>>>>>>>> 
>>>>>>>>>> If I recall correctly, Google BigQuery has SELECT AS STRUCT. It’s
>>>>> not
>>>>>>>>>> standard, but if it does what you need we could consider adopting
>>>>> that
>>>>>>>>>> syntax.
>>>>>>>>>> 
>>>>>>>>>> Julian
>>>>>>>>>> 
>>>>>>>>>>> On Apr 4, 2018, at 10:23 AM, Albert <zi...@gmail.com> wrote:
>>>>>>>>>>> 
>>>>>>>>>>> if it is not SQL standard, it's just a matter of categorizing it
>>> to
>>>>>>>>> some
>>>>>>>>>>> dialect ?
>>>>>>>>>>> 
>>>>>>>>>>>> On Wed, Apr 4, 2018 at 10:19 AM, Michael Mior <
>>> mmior@uwaterloo.ca
>>>>>> 
>>>>>>>>>> wrote:
>>>>>>>>>>>> 
>>>>>>>>>>>> Apologies for my silence. I don't really have thoughts on the
>>>>> matter
>>>>>>>>> at
>>>>>>>>>>>> this point. It might be helpful if you can give an example of
>>> what
>>>>>>>>>> you're
>>>>>>>>>>>> proposing. Unless I'm missing something (very possible), it's
>> not
>>>>>>>> part
>>>>>>>>>> of
>>>>>>>>>>>> the SQL standard.
>>>>>>>>>>>> 
>>>>>>>>>>>> --
>>>>>>>>>>>> Michael Mior
>>>>>>>>>>>> mmior@apache.org
>>>>>>>>>>>> 
>>>>>>>>>>>> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:
>>>>>>>>>>>> 
>>>>>>>>>>>>> Friendly ping, any thoughts? Much appreciated.
>>>>>>>>>>>>> 
>>>>>>>>>>>>> Shuyi
>>>>>>>>>>>>> 
>>>>>>>>>>>>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <
>>>>> suez1224@gmail.com>
>>>>>>>>>> wrote:
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> Hi community,
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> I am thinking of adding the following support in Calcite to
>>>>>>>> support
>>>>>>>>>>>> named
>>>>>>>>>>>>>> row construction, e.g.
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> SELECT (a as first_name, b as last_name, (c as zip code, d as
>>>>>>>>> street,
>>>>>>>>>> e
>>>>>>>>>>>>> as
>>>>>>>>>>>>>> state) as address) as record FROM example_table
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> The output will be struct with field names specified in the
>>> SQL.
>>>>>>>> The
>>>>>>>>>>>>> usage
>>>>>>>>>>>>>> scenario is that say, in streaming SQL, the downstream sink's
>>>>>>>> schema
>>>>>>>>>>>> can
>>>>>>>>>>>>>> not be changed, so we will need to use SQL to construct a
>>> struct
>>>>>>>>> with
>>>>>>>>>>>> the
>>>>>>>>>>>>>> proper naming according to the schema in order to write to
>> the
>>>>>>>>>>>> downstream
>>>>>>>>>>>>>> sinks. Thanks a lot.
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> Shuyi
>>>>>>>>>>>>>> 
>>>>>>>>>>>>>> --
>>>>>>>>>>>>>> "So you have to trust that the dots will somehow connect in
>>> your
>>>>>>>>>>>> future."
>>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> 
>>>>>>>>>>>>> --
>>>>>>>>>>>>> "So you have to trust that the dots will somehow connect in
>> your
>>>>>>>>>> future."
>>>>>>>>>>>>> 
>>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> 
>>>>>>>>>>> --
>>>>>>>>>>> ~~~~~~~~~~~~~~~
>>>>>>>>>>> no mistakes
>>>>>>>>>>> ~~~~~~~~~~~~~~~~~~
>>>>>>>>>> 
>>>>>>>>> 
>>>>>>>>> 
>>>>>>>>> 
>>>>>>>>> --
>>>>>>>>> "So you have to trust that the dots will somehow connect in your
>>>>>> future."
>>>>>>>>> 
>>>>>>>> 
>>>>>> 
>>>>> 
>>>>> 
>>>>> 
>>>>> --
>>>>> "So you have to trust that the dots will somehow connect in your
>>> future."
>>>>> 
>>> 
>>> 
>> 
> 
> 
> 
> -- 
> "So you have to trust that the dots will somehow connect in your future."


Re: Supporting named row construction in Calcite SQL

Posted by Shuyi Chen <su...@gmail.com>.
Thanks a lot, Julian. Do we have instructions on how to update the website
and the picture documented?

On Mon, Apr 16, 2018 at 4:42 AM, Michael Mior <mm...@uwaterloo.ca> wrote:

> Thanks Julian!
>
> --
> Michael Mior
> mmior@apache.org
>
> --
> Michael Mior
> mmior@uwaterloo.ca
>
> 2018-04-16 2:26 GMT-04:00 Julian Hyde <jh...@apache.org>:
>
> > Beam and HerdDB are now on “powered by”: https://calcite.apache.org/
> > docs/powered_by.html <https://calcite.apache.org/docs/powered_by.html>
> >
> > > On Apr 12, 2018, at 5:33 AM, Michael Mior <mm...@uwaterloo.ca> wrote:
> > >
> > > FYI, I talked to Julian earlier this week and he will be adding Beam to
> > the
> > > powered by page since he has a doc for generating the image with the
> > logos.
> > >
> > > --
> > > Michael Mior
> > > mmior@apache.org
> > >
> > > 2018-04-12 4:06 GMT-04:00 Shuyi Chen <su...@gmail.com>:
> > >
> > >> @Andrew, great to see BEAM is also using Calcite streaming SQL. Maybe
> > you
> > >> can help adding an entry in the Calcite powered_by page
> > >> <https://calcite.apache.org/docs/powered_by.html> for BEAM  by
> editing
> > >> site/_docs/powered_by.md
> > >> <https://github.com/apache/calcite/pull/657#diff-
> > >> 1aa810bc92051b46555ee4caf24bd6c3>.
> > >> Also,
> > >> can you explain a bit more on how you plan to use streaming SQL to
> > >> transform
> > >> arbitrary JSON objects with and w/o the AS STRUCT syntax?
> > >> For adding DDL in BEAM, please take a look at the server module and
> the
> > >> TYPE DDL (https://issues.apache.org/jira/browse/CALCITE-2045) I am
> > adding.
> > >> Let me know if you have any comments and need any help.
> > >>
> > >> @Rong, I think it's better to conform with the ROW SQL standard, and
> add
> > >> new grammar to handle named struct construction.
> > >> This should work with the TYPE DDL, and we should be able to CAST the
> > >> created STRUCT as a custom type defined using DDL.
> > >>
> > >> @Julian, thanks for the suggestions. I think the AS STRUCT addition
> > should
> > >> do it.
> > >>
> > >> Shuyi
> > >>
> > >> On Mon, Apr 9, 2018 at 4:36 AM, Julian Hyde <jh...@gmail.com>
> > >> wrote:
> > >>
> > >>> For what it’s worth, ROW is standard SQL. If it does what you need,
> we
> > >>> should use it.
> > >>>
> > >>> Reading your case quickly, I perceived that you needed a concise way
> to
> > >>> assign field names, and AS STRUCT seemed to do that.
> > >>>
> > >>> But staying within the standard is always preferred. BigQuery isn’t
> > >> always
> > >>> good at that.
> > >>>
> > >>> Julian
> > >>>
> > >>>> On Apr 5, 2018, at 10:18, Rong Rong <wa...@gmail.com> wrote:
> > >>>>
> > >>>> Thanks for the fantastic proposal @shuyi. I think the STRUCT idea is
> > >>> great
> > >>>> considering ROW is not standard SQL either. As a user of calcite I
> > >> have a
> > >>>> couple questions.
> > >>>>
> > >>>> Since ROW constructor is so similar with STRUCT, would it be a good
> > >> idea
> > >>> to
> > >>>> consolidate the two syntax? Or have a clear distinction between?
> > >>>>
> > >>>> Whats the relationship going forward with DDL, for example
> > >> CALCITE-2045.
> > >>>> DDL seems more flexible in terms of defining the structure not just
> on
> > >>>> field names but also field types. Maybe @andrew can share more on
> the
> > >> use
> > >>>> cases on calcite on beam steaming integration?
> > >>>>
> > >>>> Thanks,
> > >>>> Rong
> > >>>>
> > >>>>
> > >>>> On Thu, Apr 5, 2018, 10:05 AM Andrew Pilloud
> > >> <apilloud@google.com.invalid
> > >>>>
> > >>>> wrote:
> > >>>>
> > >>>>> As a user of Calcite working on adding streaming SQL to Apache Beam
> > >> this
> > >>>>> sounds like a fantastic proposal. Our initial goal is to be able to
> > >> run
> > >>> SQL
> > >>>>> queries that transform arbitrary JSON objects. Without this syntax
> > >>> objects
> > >>>>> must be flattened when they pass through the transform. Is this
> > >>> something
> > >>>>> that might make it into 1.17?
> > >>>>>
> > >>>>> We have also had some discussion about adding DDL to Beam so a user
> > >> can
> > >>>>> describe the schema of a stream of JSON in pure SQL. Our current
> > >> though
> > >>> is
> > >>>>> to use Big Query compatible STRUCT and ARRAY syntax. Big Query is a
> > >>> popular
> > >>>>> sink for our users. Syntax compatible with Big Query would be a big
> > >> plus
> > >>>>> for us.
> > >>>>>
> > >>>>> Andrew
> > >>>>>
> > >>>>>> On Thu, Apr 5, 2018 at 12:43 AM Shuyi Chen <su...@gmail.com>
> > >> wrote:
> > >>>>>>
> > >>>>>> @Michael, @Albert, yes, I dont think it is SQL standard. But I
> think
> > >>> it's
> > >>>>>> very useful in the context of streaming SQL, e.g. Flink SQL, where
> > >> the
> > >>>>>> sinks can be a database or endpoints with defined protobuf/thrift
> > >>> schema.
> > >>>>>> They usually have complex structure. Supporting complex structure
> in
> > >>> SQL
> > >>>>>> output will make it much easier to write to different sinks with
> > >>>>> predefined
> > >>>>>> schemas in a unified way,
> > >>>>>>
> > >>>>>> @julian, that's great suggestion, I think instead of extending the
> > >> ROW
> > >>>>>> constructor, which is not SQL standard, adding a new extension
> might
> > >> be
> > >>>>> the
> > >>>>>> right way to go. Looking at the STRUCT big query syntax, we can
> > >>> implement
> > >>>>>> something like the following:
> > >>>>>>
> > >>>>>> SELECT STRUCT(a as first_name, b as last_name, STRUCT(c as zip
> code,
> > >> d
> > >>> as
> > >>>>>> street, e as state) as address) as record FROM example_table
> > >>>>>>
> > >>>>>> On Wed, Apr 4, 2018 at 5:51 PM, Julian Hyde <
> jhyde.apache@gmail.com
> > >
> > >>>>>> wrote:
> > >>>>>>
> > >>>>>>> If I recall correctly, Google BigQuery has SELECT AS STRUCT. It’s
> > >> not
> > >>>>>>> standard, but if it does what you need we could consider adopting
> > >> that
> > >>>>>>> syntax.
> > >>>>>>>
> > >>>>>>> Julian
> > >>>>>>>
> > >>>>>>>> On Apr 4, 2018, at 10:23 AM, Albert <zi...@gmail.com> wrote:
> > >>>>>>>>
> > >>>>>>>> if it is not SQL standard, it's just a matter of categorizing it
> > to
> > >>>>>> some
> > >>>>>>>> dialect ?
> > >>>>>>>>
> > >>>>>>>>> On Wed, Apr 4, 2018 at 10:19 AM, Michael Mior <
> > mmior@uwaterloo.ca
> > >>>
> > >>>>>>> wrote:
> > >>>>>>>>>
> > >>>>>>>>> Apologies for my silence. I don't really have thoughts on the
> > >> matter
> > >>>>>> at
> > >>>>>>>>> this point. It might be helpful if you can give an example of
> > what
> > >>>>>>> you're
> > >>>>>>>>> proposing. Unless I'm missing something (very possible), it's
> not
> > >>>>> part
> > >>>>>>> of
> > >>>>>>>>> the SQL standard.
> > >>>>>>>>>
> > >>>>>>>>> --
> > >>>>>>>>> Michael Mior
> > >>>>>>>>> mmior@apache.org
> > >>>>>>>>>
> > >>>>>>>>> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:
> > >>>>>>>>>
> > >>>>>>>>>> Friendly ping, any thoughts? Much appreciated.
> > >>>>>>>>>>
> > >>>>>>>>>> Shuyi
> > >>>>>>>>>>
> > >>>>>>>>>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <
> > >> suez1224@gmail.com>
> > >>>>>>> wrote:
> > >>>>>>>>>>>
> > >>>>>>>>>>> Hi community,
> > >>>>>>>>>>>
> > >>>>>>>>>>> I am thinking of adding the following support in Calcite to
> > >>>>> support
> > >>>>>>>>> named
> > >>>>>>>>>>> row construction, e.g.
> > >>>>>>>>>>>
> > >>>>>>>>>>> SELECT (a as first_name, b as last_name, (c as zip code, d as
> > >>>>>> street,
> > >>>>>>> e
> > >>>>>>>>>> as
> > >>>>>>>>>>> state) as address) as record FROM example_table
> > >>>>>>>>>>>
> > >>>>>>>>>>> The output will be struct with field names specified in the
> > SQL.
> > >>>>> The
> > >>>>>>>>>> usage
> > >>>>>>>>>>> scenario is that say, in streaming SQL, the downstream sink's
> > >>>>> schema
> > >>>>>>>>> can
> > >>>>>>>>>>> not be changed, so we will need to use SQL to construct a
> > struct
> > >>>>>> with
> > >>>>>>>>> the
> > >>>>>>>>>>> proper naming according to the schema in order to write to
> the
> > >>>>>>>>> downstream
> > >>>>>>>>>>> sinks. Thanks a lot.
> > >>>>>>>>>>>
> > >>>>>>>>>>> Shuyi
> > >>>>>>>>>>>
> > >>>>>>>>>>> --
> > >>>>>>>>>>> "So you have to trust that the dots will somehow connect in
> > your
> > >>>>>>>>> future."
> > >>>>>>>>>>>
> > >>>>>>>>>>
> > >>>>>>>>>>
> > >>>>>>>>>>
> > >>>>>>>>>> --
> > >>>>>>>>>> "So you have to trust that the dots will somehow connect in
> your
> > >>>>>>> future."
> > >>>>>>>>>>
> > >>>>>>>>>
> > >>>>>>>>
> > >>>>>>>>
> > >>>>>>>>
> > >>>>>>>> --
> > >>>>>>>> ~~~~~~~~~~~~~~~
> > >>>>>>>> no mistakes
> > >>>>>>>> ~~~~~~~~~~~~~~~~~~
> > >>>>>>>
> > >>>>>>
> > >>>>>>
> > >>>>>>
> > >>>>>> --
> > >>>>>> "So you have to trust that the dots will somehow connect in your
> > >>> future."
> > >>>>>>
> > >>>>>
> > >>>
> > >>
> > >>
> > >>
> > >> --
> > >> "So you have to trust that the dots will somehow connect in your
> > future."
> > >>
> >
> >
>



-- 
"So you have to trust that the dots will somehow connect in your future."

Re: Supporting named row construction in Calcite SQL

Posted by Michael Mior <mm...@uwaterloo.ca>.
Thanks Julian!

--
Michael Mior
mmior@apache.org

--
Michael Mior
mmior@uwaterloo.ca

2018-04-16 2:26 GMT-04:00 Julian Hyde <jh...@apache.org>:

> Beam and HerdDB are now on “powered by”: https://calcite.apache.org/
> docs/powered_by.html <https://calcite.apache.org/docs/powered_by.html>
>
> > On Apr 12, 2018, at 5:33 AM, Michael Mior <mm...@uwaterloo.ca> wrote:
> >
> > FYI, I talked to Julian earlier this week and he will be adding Beam to
> the
> > powered by page since he has a doc for generating the image with the
> logos.
> >
> > --
> > Michael Mior
> > mmior@apache.org
> >
> > 2018-04-12 4:06 GMT-04:00 Shuyi Chen <su...@gmail.com>:
> >
> >> @Andrew, great to see BEAM is also using Calcite streaming SQL. Maybe
> you
> >> can help adding an entry in the Calcite powered_by page
> >> <https://calcite.apache.org/docs/powered_by.html> for BEAM  by editing
> >> site/_docs/powered_by.md
> >> <https://github.com/apache/calcite/pull/657#diff-
> >> 1aa810bc92051b46555ee4caf24bd6c3>.
> >> Also,
> >> can you explain a bit more on how you plan to use streaming SQL to
> >> transform
> >> arbitrary JSON objects with and w/o the AS STRUCT syntax?
> >> For adding DDL in BEAM, please take a look at the server module and the
> >> TYPE DDL (https://issues.apache.org/jira/browse/CALCITE-2045) I am
> adding.
> >> Let me know if you have any comments and need any help.
> >>
> >> @Rong, I think it's better to conform with the ROW SQL standard, and add
> >> new grammar to handle named struct construction.
> >> This should work with the TYPE DDL, and we should be able to CAST the
> >> created STRUCT as a custom type defined using DDL.
> >>
> >> @Julian, thanks for the suggestions. I think the AS STRUCT addition
> should
> >> do it.
> >>
> >> Shuyi
> >>
> >> On Mon, Apr 9, 2018 at 4:36 AM, Julian Hyde <jh...@gmail.com>
> >> wrote:
> >>
> >>> For what it’s worth, ROW is standard SQL. If it does what you need, we
> >>> should use it.
> >>>
> >>> Reading your case quickly, I perceived that you needed a concise way to
> >>> assign field names, and AS STRUCT seemed to do that.
> >>>
> >>> But staying within the standard is always preferred. BigQuery isn’t
> >> always
> >>> good at that.
> >>>
> >>> Julian
> >>>
> >>>> On Apr 5, 2018, at 10:18, Rong Rong <wa...@gmail.com> wrote:
> >>>>
> >>>> Thanks for the fantastic proposal @shuyi. I think the STRUCT idea is
> >>> great
> >>>> considering ROW is not standard SQL either. As a user of calcite I
> >> have a
> >>>> couple questions.
> >>>>
> >>>> Since ROW constructor is so similar with STRUCT, would it be a good
> >> idea
> >>> to
> >>>> consolidate the two syntax? Or have a clear distinction between?
> >>>>
> >>>> Whats the relationship going forward with DDL, for example
> >> CALCITE-2045.
> >>>> DDL seems more flexible in terms of defining the structure not just on
> >>>> field names but also field types. Maybe @andrew can share more on the
> >> use
> >>>> cases on calcite on beam steaming integration?
> >>>>
> >>>> Thanks,
> >>>> Rong
> >>>>
> >>>>
> >>>> On Thu, Apr 5, 2018, 10:05 AM Andrew Pilloud
> >> <apilloud@google.com.invalid
> >>>>
> >>>> wrote:
> >>>>
> >>>>> As a user of Calcite working on adding streaming SQL to Apache Beam
> >> this
> >>>>> sounds like a fantastic proposal. Our initial goal is to be able to
> >> run
> >>> SQL
> >>>>> queries that transform arbitrary JSON objects. Without this syntax
> >>> objects
> >>>>> must be flattened when they pass through the transform. Is this
> >>> something
> >>>>> that might make it into 1.17?
> >>>>>
> >>>>> We have also had some discussion about adding DDL to Beam so a user
> >> can
> >>>>> describe the schema of a stream of JSON in pure SQL. Our current
> >> though
> >>> is
> >>>>> to use Big Query compatible STRUCT and ARRAY syntax. Big Query is a
> >>> popular
> >>>>> sink for our users. Syntax compatible with Big Query would be a big
> >> plus
> >>>>> for us.
> >>>>>
> >>>>> Andrew
> >>>>>
> >>>>>> On Thu, Apr 5, 2018 at 12:43 AM Shuyi Chen <su...@gmail.com>
> >> wrote:
> >>>>>>
> >>>>>> @Michael, @Albert, yes, I dont think it is SQL standard. But I think
> >>> it's
> >>>>>> very useful in the context of streaming SQL, e.g. Flink SQL, where
> >> the
> >>>>>> sinks can be a database or endpoints with defined protobuf/thrift
> >>> schema.
> >>>>>> They usually have complex structure. Supporting complex structure in
> >>> SQL
> >>>>>> output will make it much easier to write to different sinks with
> >>>>> predefined
> >>>>>> schemas in a unified way,
> >>>>>>
> >>>>>> @julian, that's great suggestion, I think instead of extending the
> >> ROW
> >>>>>> constructor, which is not SQL standard, adding a new extension might
> >> be
> >>>>> the
> >>>>>> right way to go. Looking at the STRUCT big query syntax, we can
> >>> implement
> >>>>>> something like the following:
> >>>>>>
> >>>>>> SELECT STRUCT(a as first_name, b as last_name, STRUCT(c as zip code,
> >> d
> >>> as
> >>>>>> street, e as state) as address) as record FROM example_table
> >>>>>>
> >>>>>> On Wed, Apr 4, 2018 at 5:51 PM, Julian Hyde <jhyde.apache@gmail.com
> >
> >>>>>> wrote:
> >>>>>>
> >>>>>>> If I recall correctly, Google BigQuery has SELECT AS STRUCT. It’s
> >> not
> >>>>>>> standard, but if it does what you need we could consider adopting
> >> that
> >>>>>>> syntax.
> >>>>>>>
> >>>>>>> Julian
> >>>>>>>
> >>>>>>>> On Apr 4, 2018, at 10:23 AM, Albert <zi...@gmail.com> wrote:
> >>>>>>>>
> >>>>>>>> if it is not SQL standard, it's just a matter of categorizing it
> to
> >>>>>> some
> >>>>>>>> dialect ?
> >>>>>>>>
> >>>>>>>>> On Wed, Apr 4, 2018 at 10:19 AM, Michael Mior <
> mmior@uwaterloo.ca
> >>>
> >>>>>>> wrote:
> >>>>>>>>>
> >>>>>>>>> Apologies for my silence. I don't really have thoughts on the
> >> matter
> >>>>>> at
> >>>>>>>>> this point. It might be helpful if you can give an example of
> what
> >>>>>>> you're
> >>>>>>>>> proposing. Unless I'm missing something (very possible), it's not
> >>>>> part
> >>>>>>> of
> >>>>>>>>> the SQL standard.
> >>>>>>>>>
> >>>>>>>>> --
> >>>>>>>>> Michael Mior
> >>>>>>>>> mmior@apache.org
> >>>>>>>>>
> >>>>>>>>> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:
> >>>>>>>>>
> >>>>>>>>>> Friendly ping, any thoughts? Much appreciated.
> >>>>>>>>>>
> >>>>>>>>>> Shuyi
> >>>>>>>>>>
> >>>>>>>>>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <
> >> suez1224@gmail.com>
> >>>>>>> wrote:
> >>>>>>>>>>>
> >>>>>>>>>>> Hi community,
> >>>>>>>>>>>
> >>>>>>>>>>> I am thinking of adding the following support in Calcite to
> >>>>> support
> >>>>>>>>> named
> >>>>>>>>>>> row construction, e.g.
> >>>>>>>>>>>
> >>>>>>>>>>> SELECT (a as first_name, b as last_name, (c as zip code, d as
> >>>>>> street,
> >>>>>>> e
> >>>>>>>>>> as
> >>>>>>>>>>> state) as address) as record FROM example_table
> >>>>>>>>>>>
> >>>>>>>>>>> The output will be struct with field names specified in the
> SQL.
> >>>>> The
> >>>>>>>>>> usage
> >>>>>>>>>>> scenario is that say, in streaming SQL, the downstream sink's
> >>>>> schema
> >>>>>>>>> can
> >>>>>>>>>>> not be changed, so we will need to use SQL to construct a
> struct
> >>>>>> with
> >>>>>>>>> the
> >>>>>>>>>>> proper naming according to the schema in order to write to the
> >>>>>>>>> downstream
> >>>>>>>>>>> sinks. Thanks a lot.
> >>>>>>>>>>>
> >>>>>>>>>>> Shuyi
> >>>>>>>>>>>
> >>>>>>>>>>> --
> >>>>>>>>>>> "So you have to trust that the dots will somehow connect in
> your
> >>>>>>>>> future."
> >>>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>>
> >>>>>>>>>> --
> >>>>>>>>>> "So you have to trust that the dots will somehow connect in your
> >>>>>>> future."
> >>>>>>>>>>
> >>>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>> --
> >>>>>>>> ~~~~~~~~~~~~~~~
> >>>>>>>> no mistakes
> >>>>>>>> ~~~~~~~~~~~~~~~~~~
> >>>>>>>
> >>>>>>
> >>>>>>
> >>>>>>
> >>>>>> --
> >>>>>> "So you have to trust that the dots will somehow connect in your
> >>> future."
> >>>>>>
> >>>>>
> >>>
> >>
> >>
> >>
> >> --
> >> "So you have to trust that the dots will somehow connect in your
> future."
> >>
>
>

Re: Supporting named row construction in Calcite SQL

Posted by Julian Hyde <jh...@apache.org>.
Beam and HerdDB are now on “powered by”: https://calcite.apache.org/docs/powered_by.html <https://calcite.apache.org/docs/powered_by.html>

> On Apr 12, 2018, at 5:33 AM, Michael Mior <mm...@uwaterloo.ca> wrote:
> 
> FYI, I talked to Julian earlier this week and he will be adding Beam to the
> powered by page since he has a doc for generating the image with the logos.
> 
> --
> Michael Mior
> mmior@apache.org
> 
> 2018-04-12 4:06 GMT-04:00 Shuyi Chen <su...@gmail.com>:
> 
>> @Andrew, great to see BEAM is also using Calcite streaming SQL. Maybe you
>> can help adding an entry in the Calcite powered_by page
>> <https://calcite.apache.org/docs/powered_by.html> for BEAM  by editing
>> site/_docs/powered_by.md
>> <https://github.com/apache/calcite/pull/657#diff-
>> 1aa810bc92051b46555ee4caf24bd6c3>.
>> Also,
>> can you explain a bit more on how you plan to use streaming SQL to
>> transform
>> arbitrary JSON objects with and w/o the AS STRUCT syntax?
>> For adding DDL in BEAM, please take a look at the server module and the
>> TYPE DDL (https://issues.apache.org/jira/browse/CALCITE-2045) I am adding.
>> Let me know if you have any comments and need any help.
>> 
>> @Rong, I think it's better to conform with the ROW SQL standard, and add
>> new grammar to handle named struct construction.
>> This should work with the TYPE DDL, and we should be able to CAST the
>> created STRUCT as a custom type defined using DDL.
>> 
>> @Julian, thanks for the suggestions. I think the AS STRUCT addition should
>> do it.
>> 
>> Shuyi
>> 
>> On Mon, Apr 9, 2018 at 4:36 AM, Julian Hyde <jh...@gmail.com>
>> wrote:
>> 
>>> For what it’s worth, ROW is standard SQL. If it does what you need, we
>>> should use it.
>>> 
>>> Reading your case quickly, I perceived that you needed a concise way to
>>> assign field names, and AS STRUCT seemed to do that.
>>> 
>>> But staying within the standard is always preferred. BigQuery isn’t
>> always
>>> good at that.
>>> 
>>> Julian
>>> 
>>>> On Apr 5, 2018, at 10:18, Rong Rong <wa...@gmail.com> wrote:
>>>> 
>>>> Thanks for the fantastic proposal @shuyi. I think the STRUCT idea is
>>> great
>>>> considering ROW is not standard SQL either. As a user of calcite I
>> have a
>>>> couple questions.
>>>> 
>>>> Since ROW constructor is so similar with STRUCT, would it be a good
>> idea
>>> to
>>>> consolidate the two syntax? Or have a clear distinction between?
>>>> 
>>>> Whats the relationship going forward with DDL, for example
>> CALCITE-2045.
>>>> DDL seems more flexible in terms of defining the structure not just on
>>>> field names but also field types. Maybe @andrew can share more on the
>> use
>>>> cases on calcite on beam steaming integration?
>>>> 
>>>> Thanks,
>>>> Rong
>>>> 
>>>> 
>>>> On Thu, Apr 5, 2018, 10:05 AM Andrew Pilloud
>> <apilloud@google.com.invalid
>>>> 
>>>> wrote:
>>>> 
>>>>> As a user of Calcite working on adding streaming SQL to Apache Beam
>> this
>>>>> sounds like a fantastic proposal. Our initial goal is to be able to
>> run
>>> SQL
>>>>> queries that transform arbitrary JSON objects. Without this syntax
>>> objects
>>>>> must be flattened when they pass through the transform. Is this
>>> something
>>>>> that might make it into 1.17?
>>>>> 
>>>>> We have also had some discussion about adding DDL to Beam so a user
>> can
>>>>> describe the schema of a stream of JSON in pure SQL. Our current
>> though
>>> is
>>>>> to use Big Query compatible STRUCT and ARRAY syntax. Big Query is a
>>> popular
>>>>> sink for our users. Syntax compatible with Big Query would be a big
>> plus
>>>>> for us.
>>>>> 
>>>>> Andrew
>>>>> 
>>>>>> On Thu, Apr 5, 2018 at 12:43 AM Shuyi Chen <su...@gmail.com>
>> wrote:
>>>>>> 
>>>>>> @Michael, @Albert, yes, I dont think it is SQL standard. But I think
>>> it's
>>>>>> very useful in the context of streaming SQL, e.g. Flink SQL, where
>> the
>>>>>> sinks can be a database or endpoints with defined protobuf/thrift
>>> schema.
>>>>>> They usually have complex structure. Supporting complex structure in
>>> SQL
>>>>>> output will make it much easier to write to different sinks with
>>>>> predefined
>>>>>> schemas in a unified way,
>>>>>> 
>>>>>> @julian, that's great suggestion, I think instead of extending the
>> ROW
>>>>>> constructor, which is not SQL standard, adding a new extension might
>> be
>>>>> the
>>>>>> right way to go. Looking at the STRUCT big query syntax, we can
>>> implement
>>>>>> something like the following:
>>>>>> 
>>>>>> SELECT STRUCT(a as first_name, b as last_name, STRUCT(c as zip code,
>> d
>>> as
>>>>>> street, e as state) as address) as record FROM example_table
>>>>>> 
>>>>>> On Wed, Apr 4, 2018 at 5:51 PM, Julian Hyde <jh...@gmail.com>
>>>>>> wrote:
>>>>>> 
>>>>>>> If I recall correctly, Google BigQuery has SELECT AS STRUCT. It’s
>> not
>>>>>>> standard, but if it does what you need we could consider adopting
>> that
>>>>>>> syntax.
>>>>>>> 
>>>>>>> Julian
>>>>>>> 
>>>>>>>> On Apr 4, 2018, at 10:23 AM, Albert <zi...@gmail.com> wrote:
>>>>>>>> 
>>>>>>>> if it is not SQL standard, it's just a matter of categorizing it to
>>>>>> some
>>>>>>>> dialect ?
>>>>>>>> 
>>>>>>>>> On Wed, Apr 4, 2018 at 10:19 AM, Michael Mior <mmior@uwaterloo.ca
>>> 
>>>>>>> wrote:
>>>>>>>>> 
>>>>>>>>> Apologies for my silence. I don't really have thoughts on the
>> matter
>>>>>> at
>>>>>>>>> this point. It might be helpful if you can give an example of what
>>>>>>> you're
>>>>>>>>> proposing. Unless I'm missing something (very possible), it's not
>>>>> part
>>>>>>> of
>>>>>>>>> the SQL standard.
>>>>>>>>> 
>>>>>>>>> --
>>>>>>>>> Michael Mior
>>>>>>>>> mmior@apache.org
>>>>>>>>> 
>>>>>>>>> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:
>>>>>>>>> 
>>>>>>>>>> Friendly ping, any thoughts? Much appreciated.
>>>>>>>>>> 
>>>>>>>>>> Shuyi
>>>>>>>>>> 
>>>>>>>>>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <
>> suez1224@gmail.com>
>>>>>>> wrote:
>>>>>>>>>>> 
>>>>>>>>>>> Hi community,
>>>>>>>>>>> 
>>>>>>>>>>> I am thinking of adding the following support in Calcite to
>>>>> support
>>>>>>>>> named
>>>>>>>>>>> row construction, e.g.
>>>>>>>>>>> 
>>>>>>>>>>> SELECT (a as first_name, b as last_name, (c as zip code, d as
>>>>>> street,
>>>>>>> e
>>>>>>>>>> as
>>>>>>>>>>> state) as address) as record FROM example_table
>>>>>>>>>>> 
>>>>>>>>>>> The output will be struct with field names specified in the SQL.
>>>>> The
>>>>>>>>>> usage
>>>>>>>>>>> scenario is that say, in streaming SQL, the downstream sink's
>>>>> schema
>>>>>>>>> can
>>>>>>>>>>> not be changed, so we will need to use SQL to construct a struct
>>>>>> with
>>>>>>>>> the
>>>>>>>>>>> proper naming according to the schema in order to write to the
>>>>>>>>> downstream
>>>>>>>>>>> sinks. Thanks a lot.
>>>>>>>>>>> 
>>>>>>>>>>> Shuyi
>>>>>>>>>>> 
>>>>>>>>>>> --
>>>>>>>>>>> "So you have to trust that the dots will somehow connect in your
>>>>>>>>> future."
>>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>> 
>>>>>>>>>> --
>>>>>>>>>> "So you have to trust that the dots will somehow connect in your
>>>>>>> future."
>>>>>>>>>> 
>>>>>>>>> 
>>>>>>>> 
>>>>>>>> 
>>>>>>>> 
>>>>>>>> --
>>>>>>>> ~~~~~~~~~~~~~~~
>>>>>>>> no mistakes
>>>>>>>> ~~~~~~~~~~~~~~~~~~
>>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> 
>>>>>> --
>>>>>> "So you have to trust that the dots will somehow connect in your
>>> future."
>>>>>> 
>>>>> 
>>> 
>> 
>> 
>> 
>> --
>> "So you have to trust that the dots will somehow connect in your future."
>> 


Re: Supporting named row construction in Calcite SQL

Posted by Michael Mior <mm...@uwaterloo.ca>.
FYI, I talked to Julian earlier this week and he will be adding Beam to the
powered by page since he has a doc for generating the image with the logos.

--
Michael Mior
mmior@apache.org

2018-04-12 4:06 GMT-04:00 Shuyi Chen <su...@gmail.com>:

> @Andrew, great to see BEAM is also using Calcite streaming SQL. Maybe you
> can help adding an entry in the Calcite powered_by page
> <https://calcite.apache.org/docs/powered_by.html> for BEAM  by editing
> site/_docs/powered_by.md
> <https://github.com/apache/calcite/pull/657#diff-
> 1aa810bc92051b46555ee4caf24bd6c3>.
> Also,
> can you explain a bit more on how you plan to use streaming SQL to
> transform
> arbitrary JSON objects with and w/o the AS STRUCT syntax?
> For adding DDL in BEAM, please take a look at the server module and the
> TYPE DDL (https://issues.apache.org/jira/browse/CALCITE-2045) I am adding.
> Let me know if you have any comments and need any help.
>
> @Rong, I think it's better to conform with the ROW SQL standard, and add
> new grammar to handle named struct construction.
> This should work with the TYPE DDL, and we should be able to CAST the
> created STRUCT as a custom type defined using DDL.
>
> @Julian, thanks for the suggestions. I think the AS STRUCT addition should
> do it.
>
> Shuyi
>
> On Mon, Apr 9, 2018 at 4:36 AM, Julian Hyde <jh...@gmail.com>
> wrote:
>
> > For what it’s worth, ROW is standard SQL. If it does what you need, we
> > should use it.
> >
> > Reading your case quickly, I perceived that you needed a concise way to
> > assign field names, and AS STRUCT seemed to do that.
> >
> > But staying within the standard is always preferred. BigQuery isn’t
> always
> > good at that.
> >
> > Julian
> >
> > > On Apr 5, 2018, at 10:18, Rong Rong <wa...@gmail.com> wrote:
> > >
> > > Thanks for the fantastic proposal @shuyi. I think the STRUCT idea is
> > great
> > > considering ROW is not standard SQL either. As a user of calcite I
> have a
> > > couple questions.
> > >
> > > Since ROW constructor is so similar with STRUCT, would it be a good
> idea
> > to
> > > consolidate the two syntax? Or have a clear distinction between?
> > >
> > > Whats the relationship going forward with DDL, for example
> CALCITE-2045.
> > > DDL seems more flexible in terms of defining the structure not just on
> > > field names but also field types. Maybe @andrew can share more on the
> use
> > > cases on calcite on beam steaming integration?
> > >
> > > Thanks,
> > > Rong
> > >
> > >
> > > On Thu, Apr 5, 2018, 10:05 AM Andrew Pilloud
> <apilloud@google.com.invalid
> > >
> > > wrote:
> > >
> > >> As a user of Calcite working on adding streaming SQL to Apache Beam
> this
> > >> sounds like a fantastic proposal. Our initial goal is to be able to
> run
> > SQL
> > >> queries that transform arbitrary JSON objects. Without this syntax
> > objects
> > >> must be flattened when they pass through the transform. Is this
> > something
> > >> that might make it into 1.17?
> > >>
> > >> We have also had some discussion about adding DDL to Beam so a user
> can
> > >> describe the schema of a stream of JSON in pure SQL. Our current
> though
> > is
> > >> to use Big Query compatible STRUCT and ARRAY syntax. Big Query is a
> > popular
> > >> sink for our users. Syntax compatible with Big Query would be a big
> plus
> > >> for us.
> > >>
> > >> Andrew
> > >>
> > >>> On Thu, Apr 5, 2018 at 12:43 AM Shuyi Chen <su...@gmail.com>
> wrote:
> > >>>
> > >>> @Michael, @Albert, yes, I dont think it is SQL standard. But I think
> > it's
> > >>> very useful in the context of streaming SQL, e.g. Flink SQL, where
> the
> > >>> sinks can be a database or endpoints with defined protobuf/thrift
> > schema.
> > >>> They usually have complex structure. Supporting complex structure in
> > SQL
> > >>> output will make it much easier to write to different sinks with
> > >> predefined
> > >>> schemas in a unified way,
> > >>>
> > >>> @julian, that's great suggestion, I think instead of extending the
> ROW
> > >>> constructor, which is not SQL standard, adding a new extension might
> be
> > >> the
> > >>> right way to go. Looking at the STRUCT big query syntax, we can
> > implement
> > >>> something like the following:
> > >>>
> > >>> SELECT STRUCT(a as first_name, b as last_name, STRUCT(c as zip code,
> d
> > as
> > >>> street, e as state) as address) as record FROM example_table
> > >>>
> > >>> On Wed, Apr 4, 2018 at 5:51 PM, Julian Hyde <jh...@gmail.com>
> > >>> wrote:
> > >>>
> > >>>> If I recall correctly, Google BigQuery has SELECT AS STRUCT. It’s
> not
> > >>>> standard, but if it does what you need we could consider adopting
> that
> > >>>> syntax.
> > >>>>
> > >>>> Julian
> > >>>>
> > >>>>> On Apr 4, 2018, at 10:23 AM, Albert <zi...@gmail.com> wrote:
> > >>>>>
> > >>>>> if it is not SQL standard, it's just a matter of categorizing it to
> > >>> some
> > >>>>> dialect ?
> > >>>>>
> > >>>>>> On Wed, Apr 4, 2018 at 10:19 AM, Michael Mior <mmior@uwaterloo.ca
> >
> > >>>> wrote:
> > >>>>>>
> > >>>>>> Apologies for my silence. I don't really have thoughts on the
> matter
> > >>> at
> > >>>>>> this point. It might be helpful if you can give an example of what
> > >>>> you're
> > >>>>>> proposing. Unless I'm missing something (very possible), it's not
> > >> part
> > >>>> of
> > >>>>>> the SQL standard.
> > >>>>>>
> > >>>>>> --
> > >>>>>> Michael Mior
> > >>>>>> mmior@apache.org
> > >>>>>>
> > >>>>>> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:
> > >>>>>>
> > >>>>>>> Friendly ping, any thoughts? Much appreciated.
> > >>>>>>>
> > >>>>>>> Shuyi
> > >>>>>>>
> > >>>>>>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <
> suez1224@gmail.com>
> > >>>> wrote:
> > >>>>>>>>
> > >>>>>>>> Hi community,
> > >>>>>>>>
> > >>>>>>>> I am thinking of adding the following support in Calcite to
> > >> support
> > >>>>>> named
> > >>>>>>>> row construction, e.g.
> > >>>>>>>>
> > >>>>>>>> SELECT (a as first_name, b as last_name, (c as zip code, d as
> > >>> street,
> > >>>> e
> > >>>>>>> as
> > >>>>>>>> state) as address) as record FROM example_table
> > >>>>>>>>
> > >>>>>>>> The output will be struct with field names specified in the SQL.
> > >> The
> > >>>>>>> usage
> > >>>>>>>> scenario is that say, in streaming SQL, the downstream sink's
> > >> schema
> > >>>>>> can
> > >>>>>>>> not be changed, so we will need to use SQL to construct a struct
> > >>> with
> > >>>>>> the
> > >>>>>>>> proper naming according to the schema in order to write to the
> > >>>>>> downstream
> > >>>>>>>> sinks. Thanks a lot.
> > >>>>>>>>
> > >>>>>>>> Shuyi
> > >>>>>>>>
> > >>>>>>>> --
> > >>>>>>>> "So you have to trust that the dots will somehow connect in your
> > >>>>>> future."
> > >>>>>>>>
> > >>>>>>>
> > >>>>>>>
> > >>>>>>>
> > >>>>>>> --
> > >>>>>>> "So you have to trust that the dots will somehow connect in your
> > >>>> future."
> > >>>>>>>
> > >>>>>>
> > >>>>>
> > >>>>>
> > >>>>>
> > >>>>> --
> > >>>>> ~~~~~~~~~~~~~~~
> > >>>>> no mistakes
> > >>>>> ~~~~~~~~~~~~~~~~~~
> > >>>>
> > >>>
> > >>>
> > >>>
> > >>> --
> > >>> "So you have to trust that the dots will somehow connect in your
> > future."
> > >>>
> > >>
> >
>
>
>
> --
> "So you have to trust that the dots will somehow connect in your future."
>

Re: Supporting named row construction in Calcite SQL

Posted by Shuyi Chen <su...@gmail.com>.
@Andrew, great to see BEAM is also using Calcite streaming SQL. Maybe you
can help adding an entry in the Calcite powered_by page
<https://calcite.apache.org/docs/powered_by.html> for BEAM  by editing
site/_docs/powered_by.md
<https://github.com/apache/calcite/pull/657#diff-1aa810bc92051b46555ee4caf24bd6c3>.
Also,
can you explain a bit more on how you plan to use streaming SQL to transform
arbitrary JSON objects with and w/o the AS STRUCT syntax?
For adding DDL in BEAM, please take a look at the server module and the
TYPE DDL (https://issues.apache.org/jira/browse/CALCITE-2045) I am adding.
Let me know if you have any comments and need any help.

@Rong, I think it's better to conform with the ROW SQL standard, and add
new grammar to handle named struct construction.
This should work with the TYPE DDL, and we should be able to CAST the
created STRUCT as a custom type defined using DDL.

@Julian, thanks for the suggestions. I think the AS STRUCT addition should
do it.

Shuyi

On Mon, Apr 9, 2018 at 4:36 AM, Julian Hyde <jh...@gmail.com> wrote:

> For what it’s worth, ROW is standard SQL. If it does what you need, we
> should use it.
>
> Reading your case quickly, I perceived that you needed a concise way to
> assign field names, and AS STRUCT seemed to do that.
>
> But staying within the standard is always preferred. BigQuery isn’t always
> good at that.
>
> Julian
>
> > On Apr 5, 2018, at 10:18, Rong Rong <wa...@gmail.com> wrote:
> >
> > Thanks for the fantastic proposal @shuyi. I think the STRUCT idea is
> great
> > considering ROW is not standard SQL either. As a user of calcite I have a
> > couple questions.
> >
> > Since ROW constructor is so similar with STRUCT, would it be a good idea
> to
> > consolidate the two syntax? Or have a clear distinction between?
> >
> > Whats the relationship going forward with DDL, for example CALCITE-2045.
> > DDL seems more flexible in terms of defining the structure not just on
> > field names but also field types. Maybe @andrew can share more on the use
> > cases on calcite on beam steaming integration?
> >
> > Thanks,
> > Rong
> >
> >
> > On Thu, Apr 5, 2018, 10:05 AM Andrew Pilloud <apilloud@google.com.invalid
> >
> > wrote:
> >
> >> As a user of Calcite working on adding streaming SQL to Apache Beam this
> >> sounds like a fantastic proposal. Our initial goal is to be able to run
> SQL
> >> queries that transform arbitrary JSON objects. Without this syntax
> objects
> >> must be flattened when they pass through the transform. Is this
> something
> >> that might make it into 1.17?
> >>
> >> We have also had some discussion about adding DDL to Beam so a user can
> >> describe the schema of a stream of JSON in pure SQL. Our current though
> is
> >> to use Big Query compatible STRUCT and ARRAY syntax. Big Query is a
> popular
> >> sink for our users. Syntax compatible with Big Query would be a big plus
> >> for us.
> >>
> >> Andrew
> >>
> >>> On Thu, Apr 5, 2018 at 12:43 AM Shuyi Chen <su...@gmail.com> wrote:
> >>>
> >>> @Michael, @Albert, yes, I dont think it is SQL standard. But I think
> it's
> >>> very useful in the context of streaming SQL, e.g. Flink SQL, where the
> >>> sinks can be a database or endpoints with defined protobuf/thrift
> schema.
> >>> They usually have complex structure. Supporting complex structure in
> SQL
> >>> output will make it much easier to write to different sinks with
> >> predefined
> >>> schemas in a unified way,
> >>>
> >>> @julian, that's great suggestion, I think instead of extending the ROW
> >>> constructor, which is not SQL standard, adding a new extension might be
> >> the
> >>> right way to go. Looking at the STRUCT big query syntax, we can
> implement
> >>> something like the following:
> >>>
> >>> SELECT STRUCT(a as first_name, b as last_name, STRUCT(c as zip code, d
> as
> >>> street, e as state) as address) as record FROM example_table
> >>>
> >>> On Wed, Apr 4, 2018 at 5:51 PM, Julian Hyde <jh...@gmail.com>
> >>> wrote:
> >>>
> >>>> If I recall correctly, Google BigQuery has SELECT AS STRUCT. It’s not
> >>>> standard, but if it does what you need we could consider adopting that
> >>>> syntax.
> >>>>
> >>>> Julian
> >>>>
> >>>>> On Apr 4, 2018, at 10:23 AM, Albert <zi...@gmail.com> wrote:
> >>>>>
> >>>>> if it is not SQL standard, it's just a matter of categorizing it to
> >>> some
> >>>>> dialect ?
> >>>>>
> >>>>>> On Wed, Apr 4, 2018 at 10:19 AM, Michael Mior <mm...@uwaterloo.ca>
> >>>> wrote:
> >>>>>>
> >>>>>> Apologies for my silence. I don't really have thoughts on the matter
> >>> at
> >>>>>> this point. It might be helpful if you can give an example of what
> >>>> you're
> >>>>>> proposing. Unless I'm missing something (very possible), it's not
> >> part
> >>>> of
> >>>>>> the SQL standard.
> >>>>>>
> >>>>>> --
> >>>>>> Michael Mior
> >>>>>> mmior@apache.org
> >>>>>>
> >>>>>> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:
> >>>>>>
> >>>>>>> Friendly ping, any thoughts? Much appreciated.
> >>>>>>>
> >>>>>>> Shuyi
> >>>>>>>
> >>>>>>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <su...@gmail.com>
> >>>> wrote:
> >>>>>>>>
> >>>>>>>> Hi community,
> >>>>>>>>
> >>>>>>>> I am thinking of adding the following support in Calcite to
> >> support
> >>>>>> named
> >>>>>>>> row construction, e.g.
> >>>>>>>>
> >>>>>>>> SELECT (a as first_name, b as last_name, (c as zip code, d as
> >>> street,
> >>>> e
> >>>>>>> as
> >>>>>>>> state) as address) as record FROM example_table
> >>>>>>>>
> >>>>>>>> The output will be struct with field names specified in the SQL.
> >> The
> >>>>>>> usage
> >>>>>>>> scenario is that say, in streaming SQL, the downstream sink's
> >> schema
> >>>>>> can
> >>>>>>>> not be changed, so we will need to use SQL to construct a struct
> >>> with
> >>>>>> the
> >>>>>>>> proper naming according to the schema in order to write to the
> >>>>>> downstream
> >>>>>>>> sinks. Thanks a lot.
> >>>>>>>>
> >>>>>>>> Shuyi
> >>>>>>>>
> >>>>>>>> --
> >>>>>>>> "So you have to trust that the dots will somehow connect in your
> >>>>>> future."
> >>>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>>
> >>>>>>> --
> >>>>>>> "So you have to trust that the dots will somehow connect in your
> >>>> future."
> >>>>>>>
> >>>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>> --
> >>>>> ~~~~~~~~~~~~~~~
> >>>>> no mistakes
> >>>>> ~~~~~~~~~~~~~~~~~~
> >>>>
> >>>
> >>>
> >>>
> >>> --
> >>> "So you have to trust that the dots will somehow connect in your
> future."
> >>>
> >>
>



-- 
"So you have to trust that the dots will somehow connect in your future."

Re: Supporting named row construction in Calcite SQL

Posted by Julian Hyde <jh...@gmail.com>.
For what it’s worth, ROW is standard SQL. If it does what you need, we should use it.

Reading your case quickly, I perceived that you needed a concise way to assign field names, and AS STRUCT seemed to do that.

But staying within the standard is always preferred. BigQuery isn’t always good at that. 

Julian

> On Apr 5, 2018, at 10:18, Rong Rong <wa...@gmail.com> wrote:
> 
> Thanks for the fantastic proposal @shuyi. I think the STRUCT idea is great
> considering ROW is not standard SQL either. As a user of calcite I have a
> couple questions.
> 
> Since ROW constructor is so similar with STRUCT, would it be a good idea to
> consolidate the two syntax? Or have a clear distinction between?
> 
> Whats the relationship going forward with DDL, for example CALCITE-2045.
> DDL seems more flexible in terms of defining the structure not just on
> field names but also field types. Maybe @andrew can share more on the use
> cases on calcite on beam steaming integration?
> 
> Thanks,
> Rong
> 
> 
> On Thu, Apr 5, 2018, 10:05 AM Andrew Pilloud <ap...@google.com.invalid>
> wrote:
> 
>> As a user of Calcite working on adding streaming SQL to Apache Beam this
>> sounds like a fantastic proposal. Our initial goal is to be able to run SQL
>> queries that transform arbitrary JSON objects. Without this syntax objects
>> must be flattened when they pass through the transform. Is this something
>> that might make it into 1.17?
>> 
>> We have also had some discussion about adding DDL to Beam so a user can
>> describe the schema of a stream of JSON in pure SQL. Our current though is
>> to use Big Query compatible STRUCT and ARRAY syntax. Big Query is a popular
>> sink for our users. Syntax compatible with Big Query would be a big plus
>> for us.
>> 
>> Andrew
>> 
>>> On Thu, Apr 5, 2018 at 12:43 AM Shuyi Chen <su...@gmail.com> wrote:
>>> 
>>> @Michael, @Albert, yes, I dont think it is SQL standard. But I think it's
>>> very useful in the context of streaming SQL, e.g. Flink SQL, where the
>>> sinks can be a database or endpoints with defined protobuf/thrift schema.
>>> They usually have complex structure. Supporting complex structure in SQL
>>> output will make it much easier to write to different sinks with
>> predefined
>>> schemas in a unified way,
>>> 
>>> @julian, that's great suggestion, I think instead of extending the ROW
>>> constructor, which is not SQL standard, adding a new extension might be
>> the
>>> right way to go. Looking at the STRUCT big query syntax, we can implement
>>> something like the following:
>>> 
>>> SELECT STRUCT(a as first_name, b as last_name, STRUCT(c as zip code, d as
>>> street, e as state) as address) as record FROM example_table
>>> 
>>> On Wed, Apr 4, 2018 at 5:51 PM, Julian Hyde <jh...@gmail.com>
>>> wrote:
>>> 
>>>> If I recall correctly, Google BigQuery has SELECT AS STRUCT. It’s not
>>>> standard, but if it does what you need we could consider adopting that
>>>> syntax.
>>>> 
>>>> Julian
>>>> 
>>>>> On Apr 4, 2018, at 10:23 AM, Albert <zi...@gmail.com> wrote:
>>>>> 
>>>>> if it is not SQL standard, it's just a matter of categorizing it to
>>> some
>>>>> dialect ?
>>>>> 
>>>>>> On Wed, Apr 4, 2018 at 10:19 AM, Michael Mior <mm...@uwaterloo.ca>
>>>> wrote:
>>>>>> 
>>>>>> Apologies for my silence. I don't really have thoughts on the matter
>>> at
>>>>>> this point. It might be helpful if you can give an example of what
>>>> you're
>>>>>> proposing. Unless I'm missing something (very possible), it's not
>> part
>>>> of
>>>>>> the SQL standard.
>>>>>> 
>>>>>> --
>>>>>> Michael Mior
>>>>>> mmior@apache.org
>>>>>> 
>>>>>> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:
>>>>>> 
>>>>>>> Friendly ping, any thoughts? Much appreciated.
>>>>>>> 
>>>>>>> Shuyi
>>>>>>> 
>>>>>>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <su...@gmail.com>
>>>> wrote:
>>>>>>>> 
>>>>>>>> Hi community,
>>>>>>>> 
>>>>>>>> I am thinking of adding the following support in Calcite to
>> support
>>>>>> named
>>>>>>>> row construction, e.g.
>>>>>>>> 
>>>>>>>> SELECT (a as first_name, b as last_name, (c as zip code, d as
>>> street,
>>>> e
>>>>>>> as
>>>>>>>> state) as address) as record FROM example_table
>>>>>>>> 
>>>>>>>> The output will be struct with field names specified in the SQL.
>> The
>>>>>>> usage
>>>>>>>> scenario is that say, in streaming SQL, the downstream sink's
>> schema
>>>>>> can
>>>>>>>> not be changed, so we will need to use SQL to construct a struct
>>> with
>>>>>> the
>>>>>>>> proper naming according to the schema in order to write to the
>>>>>> downstream
>>>>>>>> sinks. Thanks a lot.
>>>>>>>> 
>>>>>>>> Shuyi
>>>>>>>> 
>>>>>>>> --
>>>>>>>> "So you have to trust that the dots will somehow connect in your
>>>>>> future."
>>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> 
>>>>>>> --
>>>>>>> "So you have to trust that the dots will somehow connect in your
>>>> future."
>>>>>>> 
>>>>>> 
>>>>> 
>>>>> 
>>>>> 
>>>>> --
>>>>> ~~~~~~~~~~~~~~~
>>>>> no mistakes
>>>>> ~~~~~~~~~~~~~~~~~~
>>>> 
>>> 
>>> 
>>> 
>>> --
>>> "So you have to trust that the dots will somehow connect in your future."
>>> 
>> 

Re: Supporting named row construction in Calcite SQL

Posted by Rong Rong <wa...@gmail.com>.
Thanks for the fantastic proposal @shuyi. I think the STRUCT idea is great
considering ROW is not standard SQL either. As a user of calcite I have a
couple questions.

Since ROW constructor is so similar with STRUCT, would it be a good idea to
consolidate the two syntax? Or have a clear distinction between?

Whats the relationship going forward with DDL, for example CALCITE-2045.
DDL seems more flexible in terms of defining the structure not just on
field names but also field types. Maybe @andrew can share more on the use
cases on calcite on beam steaming integration?

Thanks,
Rong


On Thu, Apr 5, 2018, 10:05 AM Andrew Pilloud <ap...@google.com.invalid>
wrote:

> As a user of Calcite working on adding streaming SQL to Apache Beam this
> sounds like a fantastic proposal. Our initial goal is to be able to run SQL
> queries that transform arbitrary JSON objects. Without this syntax objects
> must be flattened when they pass through the transform. Is this something
> that might make it into 1.17?
>
> We have also had some discussion about adding DDL to Beam so a user can
> describe the schema of a stream of JSON in pure SQL. Our current though is
> to use Big Query compatible STRUCT and ARRAY syntax. Big Query is a popular
> sink for our users. Syntax compatible with Big Query would be a big plus
> for us.
>
> Andrew
>
> On Thu, Apr 5, 2018 at 12:43 AM Shuyi Chen <su...@gmail.com> wrote:
>
> > @Michael, @Albert, yes, I dont think it is SQL standard. But I think it's
> > very useful in the context of streaming SQL, e.g. Flink SQL, where the
> > sinks can be a database or endpoints with defined protobuf/thrift schema.
> > They usually have complex structure. Supporting complex structure in SQL
> > output will make it much easier to write to different sinks with
> predefined
> > schemas in a unified way,
> >
> > @julian, that's great suggestion, I think instead of extending the ROW
> > constructor, which is not SQL standard, adding a new extension might be
> the
> > right way to go. Looking at the STRUCT big query syntax, we can implement
> > something like the following:
> >
> > SELECT STRUCT(a as first_name, b as last_name, STRUCT(c as zip code, d as
> > street, e as state) as address) as record FROM example_table
> >
> > On Wed, Apr 4, 2018 at 5:51 PM, Julian Hyde <jh...@gmail.com>
> > wrote:
> >
> > > If I recall correctly, Google BigQuery has SELECT AS STRUCT. It’s not
> > > standard, but if it does what you need we could consider adopting that
> > > syntax.
> > >
> > > Julian
> > >
> > > > On Apr 4, 2018, at 10:23 AM, Albert <zi...@gmail.com> wrote:
> > > >
> > > > if it is not SQL standard, it's just a matter of categorizing it to
> > some
> > > > dialect ?
> > > >
> > > >> On Wed, Apr 4, 2018 at 10:19 AM, Michael Mior <mm...@uwaterloo.ca>
> > > wrote:
> > > >>
> > > >> Apologies for my silence. I don't really have thoughts on the matter
> > at
> > > >> this point. It might be helpful if you can give an example of what
> > > you're
> > > >> proposing. Unless I'm missing something (very possible), it's not
> part
> > > of
> > > >> the SQL standard.
> > > >>
> > > >> --
> > > >> Michael Mior
> > > >> mmior@apache.org
> > > >>
> > > >> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:
> > > >>
> > > >>> Friendly ping, any thoughts? Much appreciated.
> > > >>>
> > > >>> Shuyi
> > > >>>
> > > >>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <su...@gmail.com>
> > > wrote:
> > > >>>>
> > > >>>> Hi community,
> > > >>>>
> > > >>>> I am thinking of adding the following support in Calcite to
> support
> > > >> named
> > > >>>> row construction, e.g.
> > > >>>>
> > > >>>> SELECT (a as first_name, b as last_name, (c as zip code, d as
> > street,
> > > e
> > > >>> as
> > > >>>> state) as address) as record FROM example_table
> > > >>>>
> > > >>>> The output will be struct with field names specified in the SQL.
> The
> > > >>> usage
> > > >>>> scenario is that say, in streaming SQL, the downstream sink's
> schema
> > > >> can
> > > >>>> not be changed, so we will need to use SQL to construct a struct
> > with
> > > >> the
> > > >>>> proper naming according to the schema in order to write to the
> > > >> downstream
> > > >>>> sinks. Thanks a lot.
> > > >>>>
> > > >>>> Shuyi
> > > >>>>
> > > >>>> --
> > > >>>> "So you have to trust that the dots will somehow connect in your
> > > >> future."
> > > >>>>
> > > >>>
> > > >>>
> > > >>>
> > > >>> --
> > > >>> "So you have to trust that the dots will somehow connect in your
> > > future."
> > > >>>
> > > >>
> > > >
> > > >
> > > >
> > > > --
> > > > ~~~~~~~~~~~~~~~
> > > > no mistakes
> > > > ~~~~~~~~~~~~~~~~~~
> > >
> >
> >
> >
> > --
> > "So you have to trust that the dots will somehow connect in your future."
> >
>

Re: Supporting named row construction in Calcite SQL

Posted by Andrew Pilloud <ap...@google.com.INVALID>.
As a user of Calcite working on adding streaming SQL to Apache Beam this
sounds like a fantastic proposal. Our initial goal is to be able to run SQL
queries that transform arbitrary JSON objects. Without this syntax objects
must be flattened when they pass through the transform. Is this something
that might make it into 1.17?

We have also had some discussion about adding DDL to Beam so a user can
describe the schema of a stream of JSON in pure SQL. Our current though is
to use Big Query compatible STRUCT and ARRAY syntax. Big Query is a popular
sink for our users. Syntax compatible with Big Query would be a big plus
for us.

Andrew

On Thu, Apr 5, 2018 at 12:43 AM Shuyi Chen <su...@gmail.com> wrote:

> @Michael, @Albert, yes, I dont think it is SQL standard. But I think it's
> very useful in the context of streaming SQL, e.g. Flink SQL, where the
> sinks can be a database or endpoints with defined protobuf/thrift schema.
> They usually have complex structure. Supporting complex structure in SQL
> output will make it much easier to write to different sinks with predefined
> schemas in a unified way,
>
> @julian, that's great suggestion, I think instead of extending the ROW
> constructor, which is not SQL standard, adding a new extension might be the
> right way to go. Looking at the STRUCT big query syntax, we can implement
> something like the following:
>
> SELECT STRUCT(a as first_name, b as last_name, STRUCT(c as zip code, d as
> street, e as state) as address) as record FROM example_table
>
> On Wed, Apr 4, 2018 at 5:51 PM, Julian Hyde <jh...@gmail.com>
> wrote:
>
> > If I recall correctly, Google BigQuery has SELECT AS STRUCT. It’s not
> > standard, but if it does what you need we could consider adopting that
> > syntax.
> >
> > Julian
> >
> > > On Apr 4, 2018, at 10:23 AM, Albert <zi...@gmail.com> wrote:
> > >
> > > if it is not SQL standard, it's just a matter of categorizing it to
> some
> > > dialect ?
> > >
> > >> On Wed, Apr 4, 2018 at 10:19 AM, Michael Mior <mm...@uwaterloo.ca>
> > wrote:
> > >>
> > >> Apologies for my silence. I don't really have thoughts on the matter
> at
> > >> this point. It might be helpful if you can give an example of what
> > you're
> > >> proposing. Unless I'm missing something (very possible), it's not part
> > of
> > >> the SQL standard.
> > >>
> > >> --
> > >> Michael Mior
> > >> mmior@apache.org
> > >>
> > >> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:
> > >>
> > >>> Friendly ping, any thoughts? Much appreciated.
> > >>>
> > >>> Shuyi
> > >>>
> > >>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <su...@gmail.com>
> > wrote:
> > >>>>
> > >>>> Hi community,
> > >>>>
> > >>>> I am thinking of adding the following support in Calcite to support
> > >> named
> > >>>> row construction, e.g.
> > >>>>
> > >>>> SELECT (a as first_name, b as last_name, (c as zip code, d as
> street,
> > e
> > >>> as
> > >>>> state) as address) as record FROM example_table
> > >>>>
> > >>>> The output will be struct with field names specified in the SQL. The
> > >>> usage
> > >>>> scenario is that say, in streaming SQL, the downstream sink's schema
> > >> can
> > >>>> not be changed, so we will need to use SQL to construct a struct
> with
> > >> the
> > >>>> proper naming according to the schema in order to write to the
> > >> downstream
> > >>>> sinks. Thanks a lot.
> > >>>>
> > >>>> Shuyi
> > >>>>
> > >>>> --
> > >>>> "So you have to trust that the dots will somehow connect in your
> > >> future."
> > >>>>
> > >>>
> > >>>
> > >>>
> > >>> --
> > >>> "So you have to trust that the dots will somehow connect in your
> > future."
> > >>>
> > >>
> > >
> > >
> > >
> > > --
> > > ~~~~~~~~~~~~~~~
> > > no mistakes
> > > ~~~~~~~~~~~~~~~~~~
> >
>
>
>
> --
> "So you have to trust that the dots will somehow connect in your future."
>

Re: Supporting named row construction in Calcite SQL

Posted by Shuyi Chen <su...@gmail.com>.
@Michael, @Albert, yes, I dont think it is SQL standard. But I think it's
very useful in the context of streaming SQL, e.g. Flink SQL, where the
sinks can be a database or endpoints with defined protobuf/thrift schema.
They usually have complex structure. Supporting complex structure in SQL
output will make it much easier to write to different sinks with predefined
schemas in a unified way,

@julian, that's great suggestion, I think instead of extending the ROW
constructor, which is not SQL standard, adding a new extension might be the
right way to go. Looking at the STRUCT big query syntax, we can implement
something like the following:

SELECT STRUCT(a as first_name, b as last_name, STRUCT(c as zip code, d as
street, e as state) as address) as record FROM example_table

On Wed, Apr 4, 2018 at 5:51 PM, Julian Hyde <jh...@gmail.com> wrote:

> If I recall correctly, Google BigQuery has SELECT AS STRUCT. It’s not
> standard, but if it does what you need we could consider adopting that
> syntax.
>
> Julian
>
> > On Apr 4, 2018, at 10:23 AM, Albert <zi...@gmail.com> wrote:
> >
> > if it is not SQL standard, it's just a matter of categorizing it to some
> > dialect ?
> >
> >> On Wed, Apr 4, 2018 at 10:19 AM, Michael Mior <mm...@uwaterloo.ca>
> wrote:
> >>
> >> Apologies for my silence. I don't really have thoughts on the matter at
> >> this point. It might be helpful if you can give an example of what
> you're
> >> proposing. Unless I'm missing something (very possible), it's not part
> of
> >> the SQL standard.
> >>
> >> --
> >> Michael Mior
> >> mmior@apache.org
> >>
> >> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:
> >>
> >>> Friendly ping, any thoughts? Much appreciated.
> >>>
> >>> Shuyi
> >>>
> >>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <su...@gmail.com>
> wrote:
> >>>>
> >>>> Hi community,
> >>>>
> >>>> I am thinking of adding the following support in Calcite to support
> >> named
> >>>> row construction, e.g.
> >>>>
> >>>> SELECT (a as first_name, b as last_name, (c as zip code, d as street,
> e
> >>> as
> >>>> state) as address) as record FROM example_table
> >>>>
> >>>> The output will be struct with field names specified in the SQL. The
> >>> usage
> >>>> scenario is that say, in streaming SQL, the downstream sink's schema
> >> can
> >>>> not be changed, so we will need to use SQL to construct a struct with
> >> the
> >>>> proper naming according to the schema in order to write to the
> >> downstream
> >>>> sinks. Thanks a lot.
> >>>>
> >>>> Shuyi
> >>>>
> >>>> --
> >>>> "So you have to trust that the dots will somehow connect in your
> >> future."
> >>>>
> >>>
> >>>
> >>>
> >>> --
> >>> "So you have to trust that the dots will somehow connect in your
> future."
> >>>
> >>
> >
> >
> >
> > --
> > ~~~~~~~~~~~~~~~
> > no mistakes
> > ~~~~~~~~~~~~~~~~~~
>



-- 
"So you have to trust that the dots will somehow connect in your future."

Re: Supporting named row construction in Calcite SQL

Posted by Julian Hyde <jh...@gmail.com>.
If I recall correctly, Google BigQuery has SELECT AS STRUCT. It’s not standard, but if it does what you need we could consider adopting that syntax.  

Julian

> On Apr 4, 2018, at 10:23 AM, Albert <zi...@gmail.com> wrote:
> 
> if it is not SQL standard, it's just a matter of categorizing it to some
> dialect ?
> 
>> On Wed, Apr 4, 2018 at 10:19 AM, Michael Mior <mm...@uwaterloo.ca> wrote:
>> 
>> Apologies for my silence. I don't really have thoughts on the matter at
>> this point. It might be helpful if you can give an example of what you're
>> proposing. Unless I'm missing something (very possible), it's not part of
>> the SQL standard.
>> 
>> --
>> Michael Mior
>> mmior@apache.org
>> 
>> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:
>> 
>>> Friendly ping, any thoughts? Much appreciated.
>>> 
>>> Shuyi
>>> 
>>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <su...@gmail.com> wrote:
>>>> 
>>>> Hi community,
>>>> 
>>>> I am thinking of adding the following support in Calcite to support
>> named
>>>> row construction, e.g.
>>>> 
>>>> SELECT (a as first_name, b as last_name, (c as zip code, d as street, e
>>> as
>>>> state) as address) as record FROM example_table
>>>> 
>>>> The output will be struct with field names specified in the SQL. The
>>> usage
>>>> scenario is that say, in streaming SQL, the downstream sink's schema
>> can
>>>> not be changed, so we will need to use SQL to construct a struct with
>> the
>>>> proper naming according to the schema in order to write to the
>> downstream
>>>> sinks. Thanks a lot.
>>>> 
>>>> Shuyi
>>>> 
>>>> --
>>>> "So you have to trust that the dots will somehow connect in your
>> future."
>>>> 
>>> 
>>> 
>>> 
>>> --
>>> "So you have to trust that the dots will somehow connect in your future."
>>> 
>> 
> 
> 
> 
> -- 
> ~~~~~~~~~~~~~~~
> no mistakes
> ~~~~~~~~~~~~~~~~~~

Re: Supporting named row construction in Calcite SQL

Posted by Albert <zi...@gmail.com>.
if it is not SQL standard, it's just a matter of categorizing it to some
dialect ?

On Wed, Apr 4, 2018 at 10:19 AM, Michael Mior <mm...@uwaterloo.ca> wrote:

> Apologies for my silence. I don't really have thoughts on the matter at
> this point. It might be helpful if you can give an example of what you're
> proposing. Unless I'm missing something (very possible), it's not part of
> the SQL standard.
>
> --
> Michael Mior
> mmior@apache.org
>
> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:
>
> > Friendly ping, any thoughts? Much appreciated.
> >
> > Shuyi
> >
> > On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <su...@gmail.com> wrote:
> >
> > > Hi community,
> > >
> > > I am thinking of adding the following support in Calcite to support
> named
> > > row construction, e.g.
> > >
> > > SELECT (a as first_name, b as last_name, (c as zip code, d as street, e
> > as
> > > state) as address) as record FROM example_table
> > >
> > > The output will be struct with field names specified in the SQL. The
> > usage
> > > scenario is that say, in streaming SQL, the downstream sink's schema
> can
> > > not be changed, so we will need to use SQL to construct a struct with
> the
> > > proper naming according to the schema in order to write to the
> downstream
> > > sinks. Thanks a lot.
> > >
> > > Shuyi
> > >
> > > --
> > > "So you have to trust that the dots will somehow connect in your
> future."
> > >
> >
> >
> >
> > --
> > "So you have to trust that the dots will somehow connect in your future."
> >
>



-- 
~~~~~~~~~~~~~~~
no mistakes
~~~~~~~~~~~~~~~~~~

Re: Supporting named row construction in Calcite SQL

Posted by Michael Mior <mm...@uwaterloo.ca>.
Apologies for my silence. I don't really have thoughts on the matter at
this point. It might be helpful if you can give an example of what you're
proposing. Unless I'm missing something (very possible), it's not part of
the SQL standard.

--
Michael Mior
mmior@apache.org

2018-04-03 18:48 GMT-04:00 Shuyi Chen <su...@gmail.com>:

> Friendly ping, any thoughts? Much appreciated.
>
> Shuyi
>
> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <su...@gmail.com> wrote:
>
> > Hi community,
> >
> > I am thinking of adding the following support in Calcite to support named
> > row construction, e.g.
> >
> > SELECT (a as first_name, b as last_name, (c as zip code, d as street, e
> as
> > state) as address) as record FROM example_table
> >
> > The output will be struct with field names specified in the SQL. The
> usage
> > scenario is that say, in streaming SQL, the downstream sink's schema can
> > not be changed, so we will need to use SQL to construct a struct with the
> > proper naming according to the schema in order to write to the downstream
> > sinks. Thanks a lot.
> >
> > Shuyi
> >
> > --
> > "So you have to trust that the dots will somehow connect in your future."
> >
>
>
>
> --
> "So you have to trust that the dots will somehow connect in your future."
>

Re: Supporting named row construction in Calcite SQL

Posted by Shuyi Chen <su...@gmail.com>.
Friendly ping, any thoughts? Much appreciated.

Shuyi

On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <su...@gmail.com> wrote:

> Hi community,
>
> I am thinking of adding the following support in Calcite to support named
> row construction, e.g.
>
> SELECT (a as first_name, b as last_name, (c as zip code, d as street, e as
> state) as address) as record FROM example_table
>
> The output will be struct with field names specified in the SQL. The usage
> scenario is that say, in streaming SQL, the downstream sink's schema can
> not be changed, so we will need to use SQL to construct a struct with the
> proper naming according to the schema in order to write to the downstream
> sinks. Thanks a lot.
>
> Shuyi
>
> --
> "So you have to trust that the dots will somehow connect in your future."
>



-- 
"So you have to trust that the dots will somehow connect in your future."