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?
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
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