You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@samza.apache.org by Roger Hoover <ro...@gmail.com> on 2015/04/09 17:55:58 UTC
Joining Avro records
Hi Milinda and others,
This is an Avro question but since you guys are working on Avro support for
stream SQL, I thought I'd ask you for help.
If I have a two records of type A and B as below and want to join them
similar to "SELECT *" in SQL to produce a record of type AB, is there an
simple way to do this with Avro without writing code to copy each field
individually?
I appreciate any help.
Thanks,
Roger
{
"name": "A",
"type": "record",
"namespace": "fubar",
"fields": [{"name": "a", "type" : "int"}]
}
{
"name": "B",
"type": "record",
"namespace": "fubar",
"fields": [{"name": "b", "type" : "int"}]
}
{
"name": "AB",
"type": "record",
"namespace": "fubar",
"fields": [{"name": "a", "type" : "int"}, {"name": "b", "type" : "int"}]
}
Re: Joining Avro records
Posted by Julian Hyde <ju...@hydromatic.net>.
I think using nested fields would be a mistake. In SQL, you have two namespaces when joining two tables (or N namespaces when joining N tables), but what outputs the query is a flat namespace.
For example:
select emp.name
from (
select emp.*, dept.*
from emp join dept on emp.deptno = dept.deptno) AS v
is not legal SQL. Why? Because the “emp” qualifier does not come out of the sub-query. Only the column names are visible. The sub-query produces flat rows. This is good. It allows you to substitute a view:
select v.emp_name
from v
that produces the same result by other means.
Now, you can produce nested rows in modern SQL, if that is what you want. For example, an order record could contain a nested list of line-items. Nested rows are useful, especially for streaming queries. But you need to use explicit operators to create that nesting. Regular join produces flat rows.
Some systems, such as Pig and Cascading, perform an operation similar to Join called Cogroup. The nested fields have their benefits, but they make it difficult to apply the association rule. In relational algebra and SQL, when you transform "(a join b) join c” into “a join (b join c)”, it has the same schema, but “cogroup(cogroup(a, b), c)” has a different schema than “cogroup(a, cogroup(b, c))”.
Julian
On Apr 9, 2015, at 6:58 PM, Felix GV <fv...@linkedin.com.INVALID> wrote:
> I think the most straightforward mapping from SQL to Avro is probably to have nested fields in the Avro record.
>
> This is pretty similar to SQL aliases in a join query, like in Julian's example.
>
> With that strategy, the OP's schemas would be modeled as such:
>
> {
> "name": "A",
> "type": "record",
> "namespace": "fubar",
> "fields": [{"name": "a", "type" : "int"}]
> }
>
> {
> "name": "B",
> "type": "record",
> "namespace": "fubar",
> "fields": [{"name": "b", "type" : "int"}]
> }
>
> {
> "name": "A_join_B",
> "type": "record",
> "namespace": "fubar",
> "fields": [
> {"name": "A", "type" : "record", "fields": [{"name": "a", "type" : "int"}]},
> {"name": "B", "type" : "record", "fields": [{"name": "b", "type" : "int"}]}
> ]
> }
>
> --
>
> Felix GV
> Data Infrastructure Engineer
> Distributed Data Systems
> LinkedIn
>
> fgv@linkedin.com
> linkedin.com/in/felixgv
>
> ________________________________________
> From: Roger Hoover [roger.hoover@gmail.com]
> Sent: Thursday, April 09, 2015 12:56 PM
> To: dev@samza.apache.org
> Subject: Re: Joining Avro records
>
> Thanks, Julian. Good point about needing aliasing for unique names in
> SQL. I didn't know about array_agg...nice.
>
> On Thu, Apr 9, 2015 at 12:35 PM, Julian Hyde <ju...@hydromatic.net> wrote:
>
>> Much of this is about mapping from logical fields (i.e. the fields you can
>> reference in SQL) down to the Avro representation; I’m no expert on that
>> mapping, so I’ll focus on the SQL stuff.
>>
>> First, SQL doesn’t allow a record to have two fields of the same name, so
>> you wouldn’t be allowed to have two “name” fields. When you do a join, you
>> might need to alias output columns:
>>
>> select stream orders.id, products.id as productId
>> from orders
>> join products on orders.id = products.id;
>>
>> Second, JOIN isn’t the only SQL operator that combines records; GROUP BY
>> also combines records. JOIN combines records from different streams, and
>> they usually have different types (i.e. different numbers/types of fields),
>> whereas GROUP BY combines records from the same stream. Use whichever best
>> suits your purpose.
>>
>> select stream zipcode, floor(rowtime to hour), array_agg(orderid) as
>> orderIds
>> from orders
>> group by zipcode, floor(rowtime to hour)
>>
>> (array_agg is an aggregate function, recently added to the SQL standard,
>> that gathers input values into an array. See
>> http://www.craigkerstiens.com/2013/04/17/array-agg/.)
>>
>> Output:
>>
>> { zipcode: “94705”, rowtime: “2015-04-09 11:00:00”, orderIds: [123, 156,
>> 1056] },
>> { zipcode: “94117”, rowtime: “2015-04-09 11:00:00”, orderIds: [45, 777] },
>> { zipcode: “94705”, rowtime: “2015-04-09 12:00:00”, orderIds: [55] }
>>
>> Julian
>>
>>
>> On Apr 9, 2015, at 12:07 PM, Yi Pan <ni...@gmail.com> wrote:
>>
>>> Hi, Roger,
>>>
>>> Good question on that. I am actually not aware of any "automatic" way of
>>> doing this in Avro. I have tried to add generic Schema and Data interface
>>> in samza-sql branch to address the morphing of the schemas from input
>>> streams to the output streams. The basic idea is to have wrapper Schema
>> and
>>> Data classes on-top-of the deserialized objects to access the data fields
>>> according to the schema w/o changing and copying the actual data fields.
>>> Hence, when there is a need to morph the input data schemas into a new
>>> output data schema, we just need an implementation of the new output data
>>> Schema class that can read the corresponding data fields from the input
>>> data and write them out in the output schema. An interface function
>>> transform() is added in the Schema class for this exact purpose.
>> Currently,
>>> it only takes one input data and one example of "projection"
>> transformation
>>> can be found in the implementation of AvroSchema class. A join case as
>> you
>>> presented may well be a reason to have an implementation of "join" with
>>> multiple input data.
>>>
>>> All the above solution is still experimental and please feel free to
>>> provide your feedback and comments on that. If we agree that this
>> solution
>>> is good and suit for a broader use case, it can be considered to be used
>>> outside the "SQL" context as well.
>>>
>>> Best regards!
>>>
>>> -Yi
>>>
>>> On Thu, Apr 9, 2015 at 8:55 AM, Roger Hoover <ro...@gmail.com>
>> wrote:
>>>
>>>> Hi Milinda and others,
>>>>
>>>> This is an Avro question but since you guys are working on Avro support
>> for
>>>> stream SQL, I thought I'd ask you for help.
>>>>
>>>> If I have a two records of type A and B as below and want to join them
>>>> similar to "SELECT *" in SQL to produce a record of type AB, is there an
>>>> simple way to do this with Avro without writing code to copy each field
>>>> individually?
>>>>
>>>> I appreciate any help.
>>>>
>>>> Thanks,
>>>>
>>>> Roger
>>>>
>>>> {
>>>> "name": "A",
>>>> "type": "record",
>>>> "namespace": "fubar",
>>>> "fields": [{"name": "a", "type" : "int"}]
>>>> }
>>>>
>>>> {
>>>> "name": "B",
>>>> "type": "record",
>>>> "namespace": "fubar",
>>>> "fields": [{"name": "b", "type" : "int"}]
>>>> }
>>>>
>>>> {
>>>> "name": "AB",
>>>> "type": "record",
>>>> "namespace": "fubar",
>>>> "fields": [{"name": "a", "type" : "int"}, {"name": "b", "type" :
>> "int"}]
>>>> }
>>>>
>>
>>
RE: Joining Avro records
Posted by Felix GV <fv...@linkedin.com.INVALID>.
I think the most straightforward mapping from SQL to Avro is probably to have nested fields in the Avro record.
This is pretty similar to SQL aliases in a join query, like in Julian's example.
With that strategy, the OP's schemas would be modeled as such:
{
"name": "A",
"type": "record",
"namespace": "fubar",
"fields": [{"name": "a", "type" : "int"}]
}
{
"name": "B",
"type": "record",
"namespace": "fubar",
"fields": [{"name": "b", "type" : "int"}]
}
{
"name": "A_join_B",
"type": "record",
"namespace": "fubar",
"fields": [
{"name": "A", "type" : "record", "fields": [{"name": "a", "type" : "int"}]},
{"name": "B", "type" : "record", "fields": [{"name": "b", "type" : "int"}]}
]
}
--
Felix GV
Data Infrastructure Engineer
Distributed Data Systems
LinkedIn
fgv@linkedin.com
linkedin.com/in/felixgv
________________________________________
From: Roger Hoover [roger.hoover@gmail.com]
Sent: Thursday, April 09, 2015 12:56 PM
To: dev@samza.apache.org
Subject: Re: Joining Avro records
Thanks, Julian. Good point about needing aliasing for unique names in
SQL. I didn't know about array_agg...nice.
On Thu, Apr 9, 2015 at 12:35 PM, Julian Hyde <ju...@hydromatic.net> wrote:
> Much of this is about mapping from logical fields (i.e. the fields you can
> reference in SQL) down to the Avro representation; I’m no expert on that
> mapping, so I’ll focus on the SQL stuff.
>
> First, SQL doesn’t allow a record to have two fields of the same name, so
> you wouldn’t be allowed to have two “name” fields. When you do a join, you
> might need to alias output columns:
>
> select stream orders.id, products.id as productId
> from orders
> join products on orders.id = products.id;
>
> Second, JOIN isn’t the only SQL operator that combines records; GROUP BY
> also combines records. JOIN combines records from different streams, and
> they usually have different types (i.e. different numbers/types of fields),
> whereas GROUP BY combines records from the same stream. Use whichever best
> suits your purpose.
>
> select stream zipcode, floor(rowtime to hour), array_agg(orderid) as
> orderIds
> from orders
> group by zipcode, floor(rowtime to hour)
>
> (array_agg is an aggregate function, recently added to the SQL standard,
> that gathers input values into an array. See
> http://www.craigkerstiens.com/2013/04/17/array-agg/.)
>
> Output:
>
> { zipcode: “94705”, rowtime: “2015-04-09 11:00:00”, orderIds: [123, 156,
> 1056] },
> { zipcode: “94117”, rowtime: “2015-04-09 11:00:00”, orderIds: [45, 777] },
> { zipcode: “94705”, rowtime: “2015-04-09 12:00:00”, orderIds: [55] }
>
> Julian
>
>
> On Apr 9, 2015, at 12:07 PM, Yi Pan <ni...@gmail.com> wrote:
>
> > Hi, Roger,
> >
> > Good question on that. I am actually not aware of any "automatic" way of
> > doing this in Avro. I have tried to add generic Schema and Data interface
> > in samza-sql branch to address the morphing of the schemas from input
> > streams to the output streams. The basic idea is to have wrapper Schema
> and
> > Data classes on-top-of the deserialized objects to access the data fields
> > according to the schema w/o changing and copying the actual data fields.
> > Hence, when there is a need to morph the input data schemas into a new
> > output data schema, we just need an implementation of the new output data
> > Schema class that can read the corresponding data fields from the input
> > data and write them out in the output schema. An interface function
> > transform() is added in the Schema class for this exact purpose.
> Currently,
> > it only takes one input data and one example of "projection"
> transformation
> > can be found in the implementation of AvroSchema class. A join case as
> you
> > presented may well be a reason to have an implementation of "join" with
> > multiple input data.
> >
> > All the above solution is still experimental and please feel free to
> > provide your feedback and comments on that. If we agree that this
> solution
> > is good and suit for a broader use case, it can be considered to be used
> > outside the "SQL" context as well.
> >
> > Best regards!
> >
> > -Yi
> >
> > On Thu, Apr 9, 2015 at 8:55 AM, Roger Hoover <ro...@gmail.com>
> wrote:
> >
> >> Hi Milinda and others,
> >>
> >> This is an Avro question but since you guys are working on Avro support
> for
> >> stream SQL, I thought I'd ask you for help.
> >>
> >> If I have a two records of type A and B as below and want to join them
> >> similar to "SELECT *" in SQL to produce a record of type AB, is there an
> >> simple way to do this with Avro without writing code to copy each field
> >> individually?
> >>
> >> I appreciate any help.
> >>
> >> Thanks,
> >>
> >> Roger
> >>
> >> {
> >> "name": "A",
> >> "type": "record",
> >> "namespace": "fubar",
> >> "fields": [{"name": "a", "type" : "int"}]
> >> }
> >>
> >> {
> >> "name": "B",
> >> "type": "record",
> >> "namespace": "fubar",
> >> "fields": [{"name": "b", "type" : "int"}]
> >> }
> >>
> >> {
> >> "name": "AB",
> >> "type": "record",
> >> "namespace": "fubar",
> >> "fields": [{"name": "a", "type" : "int"}, {"name": "b", "type" :
> "int"}]
> >> }
> >>
>
>
Re: Joining Avro records
Posted by Roger Hoover <ro...@gmail.com>.
Thanks, Julian. Good point about needing aliasing for unique names in
SQL. I didn't know about array_agg...nice.
On Thu, Apr 9, 2015 at 12:35 PM, Julian Hyde <ju...@hydromatic.net> wrote:
> Much of this is about mapping from logical fields (i.e. the fields you can
> reference in SQL) down to the Avro representation; I’m no expert on that
> mapping, so I’ll focus on the SQL stuff.
>
> First, SQL doesn’t allow a record to have two fields of the same name, so
> you wouldn’t be allowed to have two “name” fields. When you do a join, you
> might need to alias output columns:
>
> select stream orders.id, products.id as productId
> from orders
> join products on orders.id = products.id;
>
> Second, JOIN isn’t the only SQL operator that combines records; GROUP BY
> also combines records. JOIN combines records from different streams, and
> they usually have different types (i.e. different numbers/types of fields),
> whereas GROUP BY combines records from the same stream. Use whichever best
> suits your purpose.
>
> select stream zipcode, floor(rowtime to hour), array_agg(orderid) as
> orderIds
> from orders
> group by zipcode, floor(rowtime to hour)
>
> (array_agg is an aggregate function, recently added to the SQL standard,
> that gathers input values into an array. See
> http://www.craigkerstiens.com/2013/04/17/array-agg/.)
>
> Output:
>
> { zipcode: “94705”, rowtime: “2015-04-09 11:00:00”, orderIds: [123, 156,
> 1056] },
> { zipcode: “94117”, rowtime: “2015-04-09 11:00:00”, orderIds: [45, 777] },
> { zipcode: “94705”, rowtime: “2015-04-09 12:00:00”, orderIds: [55] }
>
> Julian
>
>
> On Apr 9, 2015, at 12:07 PM, Yi Pan <ni...@gmail.com> wrote:
>
> > Hi, Roger,
> >
> > Good question on that. I am actually not aware of any "automatic" way of
> > doing this in Avro. I have tried to add generic Schema and Data interface
> > in samza-sql branch to address the morphing of the schemas from input
> > streams to the output streams. The basic idea is to have wrapper Schema
> and
> > Data classes on-top-of the deserialized objects to access the data fields
> > according to the schema w/o changing and copying the actual data fields.
> > Hence, when there is a need to morph the input data schemas into a new
> > output data schema, we just need an implementation of the new output data
> > Schema class that can read the corresponding data fields from the input
> > data and write them out in the output schema. An interface function
> > transform() is added in the Schema class for this exact purpose.
> Currently,
> > it only takes one input data and one example of "projection"
> transformation
> > can be found in the implementation of AvroSchema class. A join case as
> you
> > presented may well be a reason to have an implementation of "join" with
> > multiple input data.
> >
> > All the above solution is still experimental and please feel free to
> > provide your feedback and comments on that. If we agree that this
> solution
> > is good and suit for a broader use case, it can be considered to be used
> > outside the "SQL" context as well.
> >
> > Best regards!
> >
> > -Yi
> >
> > On Thu, Apr 9, 2015 at 8:55 AM, Roger Hoover <ro...@gmail.com>
> wrote:
> >
> >> Hi Milinda and others,
> >>
> >> This is an Avro question but since you guys are working on Avro support
> for
> >> stream SQL, I thought I'd ask you for help.
> >>
> >> If I have a two records of type A and B as below and want to join them
> >> similar to "SELECT *" in SQL to produce a record of type AB, is there an
> >> simple way to do this with Avro without writing code to copy each field
> >> individually?
> >>
> >> I appreciate any help.
> >>
> >> Thanks,
> >>
> >> Roger
> >>
> >> {
> >> "name": "A",
> >> "type": "record",
> >> "namespace": "fubar",
> >> "fields": [{"name": "a", "type" : "int"}]
> >> }
> >>
> >> {
> >> "name": "B",
> >> "type": "record",
> >> "namespace": "fubar",
> >> "fields": [{"name": "b", "type" : "int"}]
> >> }
> >>
> >> {
> >> "name": "AB",
> >> "type": "record",
> >> "namespace": "fubar",
> >> "fields": [{"name": "a", "type" : "int"}, {"name": "b", "type" :
> "int"}]
> >> }
> >>
>
>
Re: Joining Avro records
Posted by Julian Hyde <ju...@hydromatic.net>.
Much of this is about mapping from logical fields (i.e. the fields you can reference in SQL) down to the Avro representation; I’m no expert on that mapping, so I’ll focus on the SQL stuff.
First, SQL doesn’t allow a record to have two fields of the same name, so you wouldn’t be allowed to have two “name” fields. When you do a join, you might need to alias output columns:
select stream orders.id, products.id as productId
from orders
join products on orders.id = products.id;
Second, JOIN isn’t the only SQL operator that combines records; GROUP BY also combines records. JOIN combines records from different streams, and they usually have different types (i.e. different numbers/types of fields), whereas GROUP BY combines records from the same stream. Use whichever best suits your purpose.
select stream zipcode, floor(rowtime to hour), array_agg(orderid) as orderIds
from orders
group by zipcode, floor(rowtime to hour)
(array_agg is an aggregate function, recently added to the SQL standard, that gathers input values into an array. See http://www.craigkerstiens.com/2013/04/17/array-agg/.)
Output:
{ zipcode: “94705”, rowtime: “2015-04-09 11:00:00”, orderIds: [123, 156, 1056] },
{ zipcode: “94117”, rowtime: “2015-04-09 11:00:00”, orderIds: [45, 777] },
{ zipcode: “94705”, rowtime: “2015-04-09 12:00:00”, orderIds: [55] }
Julian
On Apr 9, 2015, at 12:07 PM, Yi Pan <ni...@gmail.com> wrote:
> Hi, Roger,
>
> Good question on that. I am actually not aware of any "automatic" way of
> doing this in Avro. I have tried to add generic Schema and Data interface
> in samza-sql branch to address the morphing of the schemas from input
> streams to the output streams. The basic idea is to have wrapper Schema and
> Data classes on-top-of the deserialized objects to access the data fields
> according to the schema w/o changing and copying the actual data fields.
> Hence, when there is a need to morph the input data schemas into a new
> output data schema, we just need an implementation of the new output data
> Schema class that can read the corresponding data fields from the input
> data and write them out in the output schema. An interface function
> transform() is added in the Schema class for this exact purpose. Currently,
> it only takes one input data and one example of "projection" transformation
> can be found in the implementation of AvroSchema class. A join case as you
> presented may well be a reason to have an implementation of "join" with
> multiple input data.
>
> All the above solution is still experimental and please feel free to
> provide your feedback and comments on that. If we agree that this solution
> is good and suit for a broader use case, it can be considered to be used
> outside the "SQL" context as well.
>
> Best regards!
>
> -Yi
>
> On Thu, Apr 9, 2015 at 8:55 AM, Roger Hoover <ro...@gmail.com> wrote:
>
>> Hi Milinda and others,
>>
>> This is an Avro question but since you guys are working on Avro support for
>> stream SQL, I thought I'd ask you for help.
>>
>> If I have a two records of type A and B as below and want to join them
>> similar to "SELECT *" in SQL to produce a record of type AB, is there an
>> simple way to do this with Avro without writing code to copy each field
>> individually?
>>
>> I appreciate any help.
>>
>> Thanks,
>>
>> Roger
>>
>> {
>> "name": "A",
>> "type": "record",
>> "namespace": "fubar",
>> "fields": [{"name": "a", "type" : "int"}]
>> }
>>
>> {
>> "name": "B",
>> "type": "record",
>> "namespace": "fubar",
>> "fields": [{"name": "b", "type" : "int"}]
>> }
>>
>> {
>> "name": "AB",
>> "type": "record",
>> "namespace": "fubar",
>> "fields": [{"name": "a", "type" : "int"}, {"name": "b", "type" : "int"}]
>> }
>>
Re: Joining Avro records
Posted by Roger Hoover <ro...@gmail.com>.
Hi all,
In case this helps anyone, I was able to create a simple class to do the
join and it works nicely for my use case. It assumes you have schema for
the input and output records.
Example (
https://github.com/Quantiply/rico/blob/master/avro-serde/src/test/java/com/quantiply/avro/JoinTest.java#L44-L52
):
GenericRecord in1 = getIn1();
GenericRecord in2 = getIn2();
GenericRecord joined = new Join(getJoinedSchema())
.merge(in1)
.merge(in2)
.getBuilder()
.set("charlie", "blah blah")
.build();
Class is here:
https://github.com/Quantiply/rico/blob/master/avro-serde/src/main/java/com/quantiply/avro/Join.java
Cheers,
Roger
On Thu, Apr 9, 2015 at 12:54 PM, Roger Hoover <ro...@gmail.com>
wrote:
> Yi Pan,
>
> Thanks for your response. I'm thinking that I'll iterate over the fields
> of the input schemas (similar to this
> https://github.com/apache/samza/blob/samza-sql/samza-sql/src/main/java/org/apache/samza/sql/metadata/AvroSchemaConverter.java#L58-L62),
> match them up with the output schema and then copy the values. It'll let
> you know how it goes in case it's useful.
>
> Cheers,
>
> Roger
>
> On Thu, Apr 9, 2015 at 12:07 PM, Yi Pan <ni...@gmail.com> wrote:
>
>> Hi, Roger,
>>
>> Good question on that. I am actually not aware of any "automatic" way of
>> doing this in Avro. I have tried to add generic Schema and Data interface
>> in samza-sql branch to address the morphing of the schemas from input
>> streams to the output streams. The basic idea is to have wrapper Schema
>> and
>> Data classes on-top-of the deserialized objects to access the data fields
>> according to the schema w/o changing and copying the actual data fields.
>> Hence, when there is a need to morph the input data schemas into a new
>> output data schema, we just need an implementation of the new output data
>> Schema class that can read the corresponding data fields from the input
>> data and write them out in the output schema. An interface function
>> transform() is added in the Schema class for this exact purpose.
>> Currently,
>> it only takes one input data and one example of "projection"
>> transformation
>> can be found in the implementation of AvroSchema class. A join case as you
>> presented may well be a reason to have an implementation of "join" with
>> multiple input data.
>>
>> All the above solution is still experimental and please feel free to
>> provide your feedback and comments on that. If we agree that this solution
>> is good and suit for a broader use case, it can be considered to be used
>> outside the "SQL" context as well.
>>
>> Best regards!
>>
>> -Yi
>>
>> On Thu, Apr 9, 2015 at 8:55 AM, Roger Hoover <ro...@gmail.com>
>> wrote:
>>
>> > Hi Milinda and others,
>> >
>> > This is an Avro question but since you guys are working on Avro support
>> for
>> > stream SQL, I thought I'd ask you for help.
>> >
>> > If I have a two records of type A and B as below and want to join them
>> > similar to "SELECT *" in SQL to produce a record of type AB, is there an
>> > simple way to do this with Avro without writing code to copy each field
>> > individually?
>> >
>> > I appreciate any help.
>> >
>> > Thanks,
>> >
>> > Roger
>> >
>> > {
>> > "name": "A",
>> > "type": "record",
>> > "namespace": "fubar",
>> > "fields": [{"name": "a", "type" : "int"}]
>> > }
>> >
>> > {
>> > "name": "B",
>> > "type": "record",
>> > "namespace": "fubar",
>> > "fields": [{"name": "b", "type" : "int"}]
>> > }
>> >
>> > {
>> > "name": "AB",
>> > "type": "record",
>> > "namespace": "fubar",
>> > "fields": [{"name": "a", "type" : "int"}, {"name": "b", "type" :
>> "int"}]
>> > }
>> >
>>
>
>
Re: Joining Avro records
Posted by Roger Hoover <ro...@gmail.com>.
Yi Pan,
Thanks for your response. I'm thinking that I'll iterate over the fields
of the input schemas (similar to this
https://github.com/apache/samza/blob/samza-sql/samza-sql/src/main/java/org/apache/samza/sql/metadata/AvroSchemaConverter.java#L58-L62),
match them up with the output schema and then copy the values. It'll let
you know how it goes in case it's useful.
Cheers,
Roger
On Thu, Apr 9, 2015 at 12:07 PM, Yi Pan <ni...@gmail.com> wrote:
> Hi, Roger,
>
> Good question on that. I am actually not aware of any "automatic" way of
> doing this in Avro. I have tried to add generic Schema and Data interface
> in samza-sql branch to address the morphing of the schemas from input
> streams to the output streams. The basic idea is to have wrapper Schema and
> Data classes on-top-of the deserialized objects to access the data fields
> according to the schema w/o changing and copying the actual data fields.
> Hence, when there is a need to morph the input data schemas into a new
> output data schema, we just need an implementation of the new output data
> Schema class that can read the corresponding data fields from the input
> data and write them out in the output schema. An interface function
> transform() is added in the Schema class for this exact purpose. Currently,
> it only takes one input data and one example of "projection" transformation
> can be found in the implementation of AvroSchema class. A join case as you
> presented may well be a reason to have an implementation of "join" with
> multiple input data.
>
> All the above solution is still experimental and please feel free to
> provide your feedback and comments on that. If we agree that this solution
> is good and suit for a broader use case, it can be considered to be used
> outside the "SQL" context as well.
>
> Best regards!
>
> -Yi
>
> On Thu, Apr 9, 2015 at 8:55 AM, Roger Hoover <ro...@gmail.com>
> wrote:
>
> > Hi Milinda and others,
> >
> > This is an Avro question but since you guys are working on Avro support
> for
> > stream SQL, I thought I'd ask you for help.
> >
> > If I have a two records of type A and B as below and want to join them
> > similar to "SELECT *" in SQL to produce a record of type AB, is there an
> > simple way to do this with Avro without writing code to copy each field
> > individually?
> >
> > I appreciate any help.
> >
> > Thanks,
> >
> > Roger
> >
> > {
> > "name": "A",
> > "type": "record",
> > "namespace": "fubar",
> > "fields": [{"name": "a", "type" : "int"}]
> > }
> >
> > {
> > "name": "B",
> > "type": "record",
> > "namespace": "fubar",
> > "fields": [{"name": "b", "type" : "int"}]
> > }
> >
> > {
> > "name": "AB",
> > "type": "record",
> > "namespace": "fubar",
> > "fields": [{"name": "a", "type" : "int"}, {"name": "b", "type" :
> "int"}]
> > }
> >
>
Re: Joining Avro records
Posted by Yi Pan <ni...@gmail.com>.
Hi, Roger,
Good question on that. I am actually not aware of any "automatic" way of
doing this in Avro. I have tried to add generic Schema and Data interface
in samza-sql branch to address the morphing of the schemas from input
streams to the output streams. The basic idea is to have wrapper Schema and
Data classes on-top-of the deserialized objects to access the data fields
according to the schema w/o changing and copying the actual data fields.
Hence, when there is a need to morph the input data schemas into a new
output data schema, we just need an implementation of the new output data
Schema class that can read the corresponding data fields from the input
data and write them out in the output schema. An interface function
transform() is added in the Schema class for this exact purpose. Currently,
it only takes one input data and one example of "projection" transformation
can be found in the implementation of AvroSchema class. A join case as you
presented may well be a reason to have an implementation of "join" with
multiple input data.
All the above solution is still experimental and please feel free to
provide your feedback and comments on that. If we agree that this solution
is good and suit for a broader use case, it can be considered to be used
outside the "SQL" context as well.
Best regards!
-Yi
On Thu, Apr 9, 2015 at 8:55 AM, Roger Hoover <ro...@gmail.com> wrote:
> Hi Milinda and others,
>
> This is an Avro question but since you guys are working on Avro support for
> stream SQL, I thought I'd ask you for help.
>
> If I have a two records of type A and B as below and want to join them
> similar to "SELECT *" in SQL to produce a record of type AB, is there an
> simple way to do this with Avro without writing code to copy each field
> individually?
>
> I appreciate any help.
>
> Thanks,
>
> Roger
>
> {
> "name": "A",
> "type": "record",
> "namespace": "fubar",
> "fields": [{"name": "a", "type" : "int"}]
> }
>
> {
> "name": "B",
> "type": "record",
> "namespace": "fubar",
> "fields": [{"name": "b", "type" : "int"}]
> }
>
> {
> "name": "AB",
> "type": "record",
> "namespace": "fubar",
> "fields": [{"name": "a", "type" : "int"}, {"name": "b", "type" : "int"}]
> }
>