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 <[email protected]> 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 <[email protected]> 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 <[email protected]> > > 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 <[email protected]> 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 <[email protected]> > > > 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 > > > >> [email protected] > > > >> > > > >> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <[email protected]>: > > > >> > > > >>> Friendly ping, any thoughts? Much appreciated. > > > >>> > > > >>> Shuyi > > > >>> > > > >>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <[email protected]> > > > 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." > > >
