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 <suez1...@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.apa...@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 <zinki...@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 > > >> mm...@apache.org > > >> > > >> 2018-04-03 18:48 GMT-04:00 Shuyi Chen <suez1...@gmail.com>: > > >> > > >>> Friendly ping, any thoughts? Much appreciated. > > >>> > > >>> Shuyi > > >>> > > >>>> On Tue, Mar 27, 2018 at 11:59 PM, Shuyi Chen <suez1...@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." >
