You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Keith Wiley <kw...@keithwiley.com> on 2013/03/06 19:03:20 UTC
Combine two overlapping schema?
I have two tables which have overlapping but nonidentical schema. I want to creating a new table that unions them, leaving nulls in any given row where a column name doesn't occur in the other table:
SCHEMA 1: { a, b, c, Y }
row: { 1, 2, 3, 4 }
SCHEMA 2: { a, b, c, Z }
row: { 5, 6, 7, 8 }
NEW SCHEMA: { a, b, c, Y, Z }
new row: { a:1, b:2, c:3, Y:4, Z:null }
new row: { a:5, b:6, c:7, Y:null, Z:8 }
I don't think either "full outer join" or "union all" works. I'm not sure how to do this. Any ideas?
Thanks.
________________________________________________________________________________
Keith Wiley kwiley@keithwiley.com keithwiley.com music.keithwiley.com
"You can scratch an itch, but you can't itch a scratch. Furthermore, an itch can
itch but a scratch can't scratch. Finally, a scratch can itch, but an itch can't
scratch. All together this implies: He scratched the itch from the scratch that
itched but would never itch the scratch from the itch that scratched."
-- Keith Wiley
________________________________________________________________________________
Re: Combine two overlapping schema?
Posted by Keith Wiley <kw...@keithwiley.com>.
Ah. I was stuck on the requirement that the two schema match, but I see your point. I'll see if that works.
On Mar 6, 2013, at 10:11 , Dean Wampler wrote:
> Of the top of my head, I think UNION ALL should work if you explicitly project out the missing columns with NULL or other values, e.g. using nested SELECTs, something like
>
> SELECT * FROM (
> SELECT a,b,c, Y, NULL AS Z FROM table1
> UNION ALL
> SELECT a,b,c, NULL AS Y, Z FROM table2
> ) table12;
>
>
>
> On Wed, Mar 6, 2013 at 12:03 PM, Keith Wiley <kw...@keithwiley.com> wrote:
> I have two tables which have overlapping but nonidentical schema. I want to creating a new table that unions them, leaving nulls in any given row where a column name doesn't occur in the other table:
>
> SCHEMA 1: { a, b, c, Y }
> row: { 1, 2, 3, 4 }
>
> SCHEMA 2: { a, b, c, Z }
> row: { 5, 6, 7, 8 }
>
> NEW SCHEMA: { a, b, c, Y, Z }
> new row: { a:1, b:2, c:3, Y:4, Z:null }
> new row: { a:5, b:6, c:7, Y:null, Z:8 }
>
> I don't think either "full outer join" or "union all" works. I'm not sure how to do this. Any ideas?
>
> Thanks.
>
> ________________________________________________________________________________
> Keith Wiley kwiley@keithwiley.com keithwiley.com music.keithwiley.com
>
> "You can scratch an itch, but you can't itch a scratch. Furthermore, an itch can
> itch but a scratch can't scratch. Finally, a scratch can itch, but an itch can't
> scratch. All together this implies: He scratched the itch from the scratch that
> itched but would never itch the scratch from the itch that scratched."
> -- Keith Wiley
> ________________________________________________________________________________
>
>
>
>
> --
> Dean Wampler, Ph.D.
> thinkbiganalytics.com
> +1-312-339-1330
>
________________________________________________________________________________
Keith Wiley kwiley@keithwiley.com keithwiley.com music.keithwiley.com
"I do not feel obliged to believe that the same God who has endowed us with
sense, reason, and intellect has intended us to forgo their use."
-- Galileo Galilei
________________________________________________________________________________
Re: Combine two overlapping schema?
Posted by Dean Wampler <de...@thinkbiganalytics.com>.
Of the top of my head, I think UNION ALL should work if you explicitly
project out the missing columns with NULL or other values, e.g. using
nested SELECTs, something like
SELECT * FROM (
SELECT a,b,c, Y, NULL AS Z FROM table1
UNION ALL
SELECT a,b,c, NULL AS Y, Z FROM table2
) table12;
On Wed, Mar 6, 2013 at 12:03 PM, Keith Wiley <kw...@keithwiley.com> wrote:
> I have two tables which have overlapping but nonidentical schema. I want
> to creating a new table that unions them, leaving nulls in any given row
> where a column name doesn't occur in the other table:
>
> SCHEMA 1: { a, b, c, Y }
> row: { 1, 2, 3, 4 }
>
> SCHEMA 2: { a, b, c, Z }
> row: { 5, 6, 7, 8 }
>
> NEW SCHEMA: { a, b, c, Y, Z }
> new row: { a:1, b:2, c:3, Y:4, Z:null }
> new row: { a:5, b:6, c:7, Y:null, Z:8 }
>
> I don't think either "full outer join" or "union all" works. I'm not sure
> how to do this. Any ideas?
>
> Thanks.
>
>
> ________________________________________________________________________________
> Keith Wiley kwiley@keithwiley.com keithwiley.com
> music.keithwiley.com
>
> "You can scratch an itch, but you can't itch a scratch. Furthermore, an
> itch can
> itch but a scratch can't scratch. Finally, a scratch can itch, but an itch
> can't
> scratch. All together this implies: He scratched the itch from the scratch
> that
> itched but would never itch the scratch from the itch that scratched."
> -- Keith Wiley
>
> ________________________________________________________________________________
>
>
--
*Dean Wampler, Ph.D.*
thinkbiganalytics.com
+1-312-339-1330