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 <walter...@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 <apill...@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 <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."
>>> 
>> 

Reply via email to