Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Combine two overlapping schema?


Copy link to this message
-
Re: Combine two overlapping schema?
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 <[EMAIL PROTECTED]> 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     [EMAIL PROTECTED]     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     [EMAIL PROTECTED]     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
________________________________________________________________________________
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB